1
  1. I create a mysqldump on all the databases on the source server with this command:

mysqldump -h mysql-01 -u root -p --all-databases > newdump.sql

This takes about an hour, but runs fine.

  1. Now, to migrate the data of the dump file on the destination server I run this:

mysql -h mysql-vm-01 -u root -p < newdump.sql

all seems to be going fine for like 30 minutes and then I get this error

ERROR 1449 (HY000) at line 100083 : The user specified as a definer ('dc_reporter'@'%') does not exist.

What should I do ??

  • Try the answers posed in this other question: stackoverflow.com/questions/10169960/… – Thronk Jul 29 '15 at 21:04
  • I have already tried what the others posts have suggested. But with no luck . So now I am posting my question here. – user71696 Jul 29 '15 at 21:07
  • Run suitable GRANTs before trying to reload. – Rick James Aug 1 '15 at 1:13
1

Perhaps you should dump the users and load the users into the VM

Here is my post : Export all MySQL users

After running the code in it and saving it to a file called MyDatabaseUSers.sql, you log into mysql on the VM and run this:

mysql> source MyDatabaseUSers.sql

This will create all the users with the same grants and passwords. Don't worry: Passwords are encrypted.

After importing the users, you should be able to reload the mysqldump.

GIVE IT A TRY !!!

NOTE: If you loaded all your data successfully, the error might have occurred loading a MySQL object that has a DEFINER associated with it

  • stored procedures
  • triggers
  • views
  • events.

Do the following:

cat newdump.sql | grep -A 2 -n "dc_reporter"

This will show you what might not have loaded. Your data may all be there.

You could try dumping your stored procedures and triggers and reloading them instead of reloading everything. Please read my old post Can mysqldump dump triggers and procedures?, go dump your triggers and stored procedures, and reload them into the VM. If you have views to import, please read my old post Modify DEFINER on Many Views and use the code to mysqldump just the views.

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.