1

I took hot backup (dump) from my Master MySQL DB with below command

mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql

Master Version: MySQL-server-5.5.41-1.el6.x86_64

At the time of restoration on slave we are getting this error, have I done something wrong.

[root@Slave ~]$ mysql -u root -p < dump.sql
Enter password:
ERROR 1449 (HY000) at line 150536: The user specified as a definer ('lipl_ga_app'@'%') does not exist

Slave Version: mysql-community-server-5.6.28-2.el6.x86_64

The DB is huge and we don't want to start the restoration process again.

@ Nawaz Sohail I have taken backup of mysql user from below command

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

Restored

mysql -u root -p mysql < user_table_dump.sql

  • it seems backup is restored till line 150536 so split file by lines with patcch of 150536 and then delete the first file and the re combine all files back to one file to avoid restoring full file. Now to resolve the issue simple fix could be make user on the target database with same privileges on source or try restoring all users from source database to target first and then try restoring backup. Do use flush privileges after restoring mysql database to target instance..Hope it helps – Nawaz Sohail Jan 21 '16 at 9:32
  • The dump contains only your data, no system tables, so original users are not restored - and some stored program (procedure, trigger etc.) has a definer which does not exist on the target server - create user 'lipl_ga_app'@'%' and grant it proper permissions. – jkavalik Jan 21 '16 at 9:42
  • how can we take hot backup of MySQL users @Nawaz Sohail can you let me know how can we split files and combine the backup. – Mongrel Jan 21 '16 at 10:31
  • Now either remove the lines till which backup is restored or do as suggested to split backup to multiple files. – Nawaz Sohail Jan 21 '16 at 11:06
0

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.

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.