During one of my last MySQL mission, I encountered a “security error” with mysqldump. The mission was about migrating a MySQL database from one box to another MySQL version and from Windows to Linux with different users. In order to do that, I used mysqldump. During the import of the data, I got the following warning:
ERROR 1449 (HY000) at line 1860: The user specified as a definer (‘cdadmin’@’%’) does not exist
Following the migration, I did the backup strategy. During the first mysql dump, I got the following error message:
mysql@mysql001: [mysqld2] mysqldump test
Running mysqldump for instance mysqld2 to /u99/mysqlbackup/mysqld2/dump/2012-11-14_15-48-23, please wait…
MySQL dump of instance mysqld2 to /u99/mysqlbackup/mysqld2/dump/2012-11-14_15-48-23 has failed.
mysqldump: Couldn’t execute ‘show table status like ‘cdr_app_browser_v”: SELECT command denied to user ”@’%’ for column ‘APPLICATION’ in table ‘cd_app_browser’ (1143)
Msqldump exited without finishing to dump the mysql database. Obvisously, both errors had the same root issue.
My initial dump file contained a DEFINER clause and this was the reason of these error messages. As specified in the MySQL documentation, the DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINERcharacteristics. When you dump the data structure for views, triggers, and stored routines, you also dump the permissions related to such objects, with the DEFINER clause.
The MySQL Bug 50594 (http://bugs.mysql.com/bug.php?id=50594) is closely related to this issue. In order to remove the DEFINER clause from the dump file – which can be time consuming – some tools such as MySQL dump filter may help you. For more information regarding this tool, please have a look at this blog posting: http://datacharmer.blogspot.ch/2009/12/filtering-mysqldump-output.html