Allow backup of runtime configuration using mysqldump

[renecannao]

Why

There is not an easy way to export configuration . This feature should be useful for various reasons, like:

• backup
• troubleshooting
• configuration of other systems with identical settings

What

• admin module should be able to answer all/most of the commands sent from mysqldump , even if just answering OK to some of them
• admin module should be able to process a dump file generated by mysqldump
• document this feature

[renecannao]

Some example.

• example 1 : dump just one table

mysqldump -u admin -padmin -h 127.0.0.1 -P6032 --no-tablespaces --replace --skip-triggers -t main mysql_servers > /tmp/dump_servers.sql
mysql -u admin -padmin -h 127.0.0.1 -P6032 main < /tmp/dump_servers.sql

• example 2 : dump all tables

mysqldump -u admin -padmin -h 127.0.0.1 -P6032 --no-tablespaces --replace --skip-triggers main > /tmp/dump.sql
mysql -u admin -padmin -h 127.0.0.1 -P6032 main < /tmp/dump.sql

Limitations to document:

• --skip-triggers is required
• --replace is recommended , or won't be possible to import table global_variables
• works only with "main" database , that is the in-memory configuration

Further note:

• it is able to dump and reload any tables in "main" database, even the one created by the users

[birladeanuadrian]

WHen trying to run mysql -u admin -padmin -h 127.0.0.1 -P6032 main < /tmp/dump.sql on debian 8 with proxysql 1.4.9, I get the following error: ERROR 2013 (HY000) at line 315: Lost connection to MySQL server during query

[Reiner030]

Here also same but with different Error:

root@debian8 ~ # mysqldump -u admin -padmin -h 127.0.0.1 -P6032 --no-tablespaces --replace --skip-triggers -t main mysql_servers > /tmp/dump_servers.sql
mysqldump: Couldn't execute 'SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'mysql_servers'': no such table: INFORMATION_SCHEMA.TABLES (1045)
root@debian8 ~ # mysqldump -V
mysqldump  Ver 10.16 Distrib 10.1.32-MariaDB, for debian-linux-gnu (x86_64)
root@debian8 ~ # proxysql -V
ProxySQL version 1.4.12-9-g216b872, codename Truls

On Debian Stretch/) with MariaDB client it also didn't work:

root@debian9 ~ # mysqldump -u admin -padmin -h 127.0.0.1 -P6032 --no-tablespaces --replace --skip-triggers -t main mysql_servers > /tmp/dump_servers.sql
mysqldump: Couldn't execute 'SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'mysql_servers'': no such table: INFORMATION_SCHEMA.TABLES (1045)
root@debian9 ~ # mysqldump -V
mysqldump  Ver 10.16 Distrib 10.2.14-MariaDB, for debian-linux-gnu (x86_64)
root@debian9 ~ # proxysql -V
ProxySQL version 1.4.12-9-g216b872, codename Truls

[adamthedeveloper]

@birladeanuadrian Same exact error here as well. Did you ever find a way to get around it?

[Sputnick85]

the problem with the import is that you can't import some of the runtime tables. The solution for me was to exclude these tables from the dump, import the dump and load the configuration in ProxySQL to runtime.

root@ubuntu:~# mysqldump -u admin -p -h 127.0.0.1 -P6032 --no-tablespaces --replace --skip-triggers main --ignore-table={main.runtime_checksums_values,main.runtime_global_variables,main.runtime_mysql_aws_aurora_hostgroups,main.runtime_mysql_galera_hostgroups,main.runtime_mysql_group_replication_hostgroups,main.runtime_mysql_query_rules,main.runtime_mysql_query_rules_fast_routing,main.runtime_mysql_replication_hostgroups,main.runtime_mysql_servers,main.runtime_mysql_users,main.runtime_proxysql_servers,main.runtime_scheduler} > /tmp/dump.sql
root@ubuntu:~# mysql -u admin -p -h 127.0.0.1 -P6032 main < /tmp/dump.sql
root@ubuntu:~# mysql -v -u admin -p -h 127.0.0.1 -P6032 main -e "LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;LOAD MYSQL SERVERS TO RUNTIME;SAVE MYSQL SERVERS TO DISK;LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;LOAD MYSQL VARIABLES TO RUNTIME;SAVE MYSQL VARIABLES TO DISK;LOAD ADMIN VARIABLES TO RUNTIME;SAVE ADMIN VARIABLES TO DISK;"
root@ubuntu:~#

[leo-bacchi]

The feature got broken on mysqldump 5.7.30:

shell> ./mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.30-33, for Linux (x86_64)
shell> ./mysqldump  -u admin -padmin -h 127.0.0.1 -P6032 --no-tablespaces --skip-triggers -t main mysql_servers  > /tmp/dump_servers.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SHOW STATUS LIKE 'binlog_snapshot_gtid_executed'': ProxySQL Admin Error: near "SHOW": syntax error (1045)

It works normally on 5.7.29:

shell> ./mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.29-32, for Linux (x86_64)
shell> ./mysqldump  -u admin -padmin -h 127.0.0.1 -P6032 --no-tablespaces --skip-triggers -t main mysql_servers  > /tmp/dump_servers.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[yakirgb]

@leeclemens you have two options:

1. Add --force
2. Execute mysqldump of vanilla mysql and not Percona