Mysql – Migrating all MySQL databases from from one server another. Getting error during import

backupMySQLmysqldumprestore

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.

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 ??

Best Answer

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.

Try This #1

SHOW GRANTS FOR CURRENT_USER();

Given the output of that query:

Copy the output of that query to a Text File
Change the host portion of the userhost to '%';
Copy and Paste into a MySQL Session to create the View.

Try This #2

CREATE TABLE mysql.lumicrm LIKE mysql.user;
INSERT INTO mysql.lumicrm SELECT * FROM mysql.user WHERE user='lumicrm';
UPDATE mysql.lumicrm SET host='%';
INSERT INTO mysql.user SELECT * FROM mysql.lumicrm;
FLUSH PRIVILEGES;
DROP TABLE mysql.lumicrm;

Now you have the MySQL user 'lumicrm'@'localhost' hacked in

Try This #3

Dump the Views to a Text File. Here are my posts on how to do this:

Jun 26, 2011 : Modify DEFINER on Many Views
Jan 19, 2012 : Will mysqldump command also backup all the views that are present in DB or there is a special command for it?
May 02, 2012 : Problem with View When Restoring a MySQL Backup

Edit the user for the View with the appropriate username. Copy the View Creation Code and Paste into a MySQL Session to create the View.

Give it a Try !!!

MySQL – Fix ERROR 1449 When Restoring Dump

I tried to solve this in many ways but, didn't find any workaround. then I tried to redo the whole thing again with the below steps.

I took a backup of mysql user from Master Host,

mysqldump -u root -p mysql user > user_table_dump.sql

and restored mysql user on slave,

mysql -u root -p mysql < user_table_dump.sql

then I restored the master dump on slave.

[root@Slave ~]$ mysql -u root -p < dump.sql

The master dump restored without any error.

Best Answer

Related Solutions

Mysql – Database user specified as a definer

Try This #1

Try This #2

Try This #3

Give it a Try !!!

MySQL – Fix ERROR 1449 When Restoring Dump

Related Question