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:

  1. Get a backup using mysqldump -u root -p -R MySB > dump.sql,
  2. 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),
  3. Create the same user within the production DB,
  4. 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.

  • Sounds reasonable (don't have machine to hand) - but can't you test this before actually implementing it on your production server? – Vérace Jun 8 '16 at 14:50

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.