Skip or ignore definers in Mysqldump

mysql mysqldump database-backups
Emilio Nicolás picture Emilio Nicolás · Aug 10, 2012 · Viewed 8.2k times · Source

I was wondering if I can prevent mysqldump inserting this commands

/*!50017 DEFINER=`root`@`localhost`*/

Or if I have to do it afterwards with sed, for example

Thanks!

Answer

NickJ picture NickJ · Oct 21, 2013

This issue has been around since 2006 with no sign of ever being fixed.

I have, however, piped it through grep (linux only) to trim out the definer lines before writing the dump file:

mysqldump -u dbuser -p dbname | grep -v 'SQL SECURITY DEFINER' > dump.sql

A bit of a mouthful (or keyboardful?) but I think it's the only way.

Related questions

Minimum GRANTs needed by mysqldump for dumping a full schema? (TRIGGERs are missing!!)

I have a MySQL user called dump with the following perms: GRANT USAGE ON *.* TO 'dump'@'%' IDENTIFIED BY ... GRANT SELECT, LOCK TABLES ON `mysql`.* TO 'dump'@'%' GRANT SELECT, LOCK TABLES ON `myschema`.* TO 'dump'@'%' …

Read More
mysqldump extracting data without table structure

I'm trying to dump all of my mysql data for one database into a single file. That said, I don't want to include the table structures in this file (there is another file which will have the structure of the …

Read More
why is the `tcgetattr` error seen when ssh is used for dumping the backup file on another server?

I want to dump a tables backup on another server and I am using ssh for doing it. when I run the below command, it gives an error but dump file is copied to destination. mysqldump -u username -ppassword dbname …

Read More