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?