Error 1449
There was an error occurred during a migration:
Got error: 1449: The user specified as a definer ('user'@'10.1.23.%') does not exist when using LOCK TABLES
It's because your target database has no account called user'@'10.1.23.%, you should change or remove the definer clauses in the dump file before import.
Solutions
I know you can change the definer to an existing user or create the missing account instead. But I decide to remove DEFINER clause from the dumping, either by vi editor or sed command.
vi Editor
In vi editor, issuing the following command:
:%s//*!50017 DEFINER=`w*`@`10.1.23.%`*///g
sed Filter
Under root's prompt, please use sed command to filter out unnecessary strings and export to a SQL file:
[root@localhost ~]# mysqldump -u'root' -p dbname1 | sed -e 's//*!50017 DEFINER=`w*`@`10.1.23.%`*///g' > mysqldump.sql
The way we remove DEFINER clause from dumping output by sed command is extremely flexible for later operations.
Piping Output
Since the synchronization between source and target database could be a routine operation, you can pipe the output to the target database instead of a dump file for convenience, which can be executed without asking:
[root@localhost ~]# mysqldump -u'root' -p'password' dbname1 | sed -e 's//*!50017 DEFINER=`w*`@`10.1.23.%`*///g' | mysql -u'user' -p'password' -h 'db.example.com' dbname2
With the sanitized result, the definer will be switched to the importer's account during migration.
Self-Import
It's also useful to correct the definitions in the same database by self-import. For example, if you'd like to change the definer from 'root' to 'user', you can filter out the definer clause and then import the sanitized dump result by 'user'.
[root@localhost ~]# mysqldump -u'root' -p'password' dbname1 | sed -e 's//*!50017 DEFINER=`w*`@`10.1.23.%`*///g' | mysql -u'user' -p'password' dbname1