How to Remove Definer from mysqldump

Mysqldump Remove Definer

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.

In my case, I removed them either by "vi" or "sed":

• In vi editor, issuing the following command:
:%s//*!50017 DEFINER=`w*`@`10.1.23.%`*///g

• Under root's prompt, please use sed 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

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.

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