Open renecannao opened 8 years ago
Some example.
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
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:
Further note:
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
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
@birladeanuadrian Same exact error here as well. Did you ever find a way to get around it?
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:~#
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.
@leeclemens you have two options:
--force
Why
There is not an easy way to export configuration . This feature should be useful for various reasons, like:
What