Menu
To identify which DEFINER values exist in each table, use these queries: SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA. EVENTS; SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA. ROUTINES; SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.
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 DEFINER characteristic.
mysqlpump is the 4th fastest followed closer by mydumper when using gzip. mysqldump is the classic old-school style to perform dumps and is the slowest of the four tools. In a server with more CPUs, the potential parallelism increases, giving even more advantage to the tools that can benefit from multiple threads.
The mysqlpump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.
I don't think there is a way to ignore adding DEFINERs to the dump. But there are ways to remove them after the dump file is created.
Open the dump file in a text editor and replace all occurrences of DEFINER=root@localhost with an empty string ""
Edit the dump (or pipe the output) using perl:
perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" mydatabase.sql
Pipe the output through sed:
mysqldump ... | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql
You can remove using SED
sed -i 's/DEFINER=[^*]*\*/\*/g' mydump.sql
In MacOS:
sed -i '' 's/DEFINER=[^*]*\*/\*/g' mydump.sql
Since mysql version 5.7.8 you can use the --skip-definer option with mysqlpump, e.g.:
mysqlpump --skip-definer -h localhost -u user -p yourdatabase
See updated mysql manual at http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer
I used these ideas to strip the DEFINER clause from my own mysqldump output, but I took a simpler approach:
Just remove the ! before the code and DEFINER, and the rest of the comment becomes a regular comment.
Example:
/*!50017 DEFINER=`user`@`111.22.33.44`*/
is rendered helpless, as little as doing this ..
/* 50017 DEFINER=`user`@`111.22.33.44`*/
The easiest regexp, though, is to remove the ! and the numbers
mysqldump | /usr/bin/perl -pe 's/\!\d+ DEFINER/DEFINER/' > dumpfile.sql
That removes !#### DEFINER and replaces with DEFINER ... you could remove DEFINER too, it doesn't really matter - once the "!" is gone
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With