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

Improve this question
4
  • 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, 2016 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, 2016 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.
    – AReddy
    Jan 21, 2016 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, 2016 at 11:06

2 Answers 2

Reset to default
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.

Improve this answer
0

Apparently this is an old question, but still came up top in Google search. I was facing this exact same issue before, and the looks like previous answer by Mongrel works. However, when I reinstalled my computer, I have made a dump using mysqldump --all-databases, and this error 1449 inadvertently came up, and extracting mysql database only from the formatted drive is almost certainly impossible.

So, I finally came up with a "manual" solution by editing the .sql dump: Move the restore statements of mysql database up top. Now on very long dumps (GBs size) this is error prone. So,

  1. fire up your trusted text editor (I trust vim for stuffs like this, VS Code crawls like no progress opening GB size files)
  2. search for Current Database: mysql comment / marker.
  3. select the text from that point up to the next Current Database: comment/marker.
  4. Cut it, put it before the first Current Database comment/marker.

Afterwards, I saved the modified dump, and now the dump restores without error.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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