MySql Moving from development to production into a different user I have a project that need to be moved to production (for the first time). The database includes few tens of tables and procedures/functions. They were all created under the ROOT user (yes, bad practice I know).

Now, I need to move the whole thing to production and obviously NOT under the MySQL ROOT user.

I thought to do the following, but would appreciate an expert opinion before I engage into it:


Get a backup using mysqldump -u root -p -R MySB &gt; dump.sql,
Edit the file and replace all instances of root with correct_user (where correct_user would be the MySQL user owning all the tables and routines),
Create the same user within the production DB,
Restore from the dump file using mysqldump -u correct_user -p -R MySB &lt; dump.sql.


Can anyone tell me if my plan is correct or if there is a better (and more formal) way to do it?

Many thanks.
In my opinion, the logic is right in your way. I will add a suggestion to make it more practical.


Get the backup without routins. --skip-triggers, and don't add -R is it is false by default
Make a backup of routines only: --no-create-info --no-data --no-create-db -R
Edit the backup file of the routine, and create the user in the new DB
Restore the data backup file
Restore the routines backup file.