Patch mysqldump at home

Backups are good, and even better when they work as they should when they are needed. On one of the projects, it was necessary to restore a dump of 745 triggers and roll them onto a working MySQL database.

MySQL allows you to use any of the names of the triggers, including using dots ( for example : analitica.cron.indeg.y.run.a_insert ). And when creating a dump, mysqldump does not take into account this circumstance and adds a structure for their drop of the following form:

 /*!50032 DROP TRIGGER IF EXISTS analitica.cron.indeg.y.run.a_insert */; 

The catch awaits when you try to dump these dumps to the base where these triggers are already created. From the point of view of MySQL, this query will not drop the trigger, because it will not find a trigger with that name. For proper operation, the trigger name must be enclosed in apostrophes.

In an attempt to find a workaround, he wrote the corresponding report to the Percona bug tracker , and they had already duplicated the same report into the official MySQL tracker .
')
Since this bug will be fixed for a long time, I need the direct line now. I decided to solve this problem in the most straightforward way, namely, by self-patching mysqldump . For this, the official repository of the MySQL 5.6 perkonovskogo ditributiv from GitHub was inclined.

 git clone --recursive --depth 1 https://github.com/percona/percona-server/ 

Opened the client/mysqldump.c file and added apostrophes to the DROP TRIGGER IF EXISTS construct in a couple of places. If you look diff , it turns out this patch:

 @@ -3517,7 +3517,7 @@ static void dump_trigger_old(FILE *sql_file, MYSQL_RES *show_triggers_rs, fprintf(sql_file, "/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;\n"); if (opt_drop_trigger) - fprintf(sql_file, "/*!50032 DROP TRIGGER IF EXISTS %s */;\n", (*show_trigger_row)[0]); + fprintf(sql_file, "/*!50032 DROP TRIGGER IF EXISTS `%s` */;\n", (*show_trigger_row)[0]); fprintf(sql_file, "DELIMITER ;;\n" @@ -3604,7 +3604,7 @@ static int dump_trigger(FILE *sql_file, MYSQL_RES *show_create_trigger_rs, switch_sql_mode(sql_file, ";", row[1]); if (opt_drop_trigger) - fprintf(sql_file, "/*!50032 DROP TRIGGER IF EXISTS %s */;\n", row[0]); + fprintf(sql_file, "/*!50032 DROP TRIGGER IF EXISTS `%s` */;\n", row[0]); 

To build the patched version, you need to put a couple of packages on Ubuntu / Debian, run cmake with the parameters from the Percona documentation, and then make 'gather only mysqldump .

 apt-get install build-essential cmake bison libaio-dev libncurses5-dev libreadline-dev cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF make mysqldump 

After compilation we get the patched version of mysqldump , in which the names of the triggers are escaped correctly. You can dump a new dumper:

 ./percona-server/client/mysqldump \ --socket=/var/run/mysqld/mysqld.sock \ -uroot -p --routines --events --triggers \ --add-drop-trigger --quote-names \ --no-create-info --no-data --no-create-db --skip-opt \ database_name | sed -r 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump.sql