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 > 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 < 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.
Best Answer
In my opinion, the logic is right in your way. I will add a suggestion to make it more practical.
--skip-triggers
, and don't add -R is it is false by default--no-create-info --no-data --no-create-db -R