proxysql配置信息清理、备份、还原

1、紧接上文，配置过程请看上文
2、进入proxysql数据目录

[root@12c proxysql]# cd /var/lib/proxysql/ [root@12c proxysql]# ll total 344 -rw-rw-r-- 1 proxysql proxysql 1050 Jul 18 13:45 proxysql-ca.pem -rw-rw-r-- 1 proxysql proxysql 1058 Jul 18 13:45 proxysql-cert.pem -rw------- 1 proxysql proxysql 163840 Jul 18 14:49 proxysql.db -rw-rw-r-- 1 proxysql proxysql 1679 Jul 18 13:45 proxysql-key.pem -rw------- 1 proxysql proxysql 26369 Jul 18 16:20 proxysql.log -rw-r--r-- 1 proxysql proxysql 5 Jul 18 14:24 proxysql.pid -rw------- 1 proxysql proxysql 143360 Jul 18 16:22 proxysql_stats.db 

3、备份目录

[root@12c proxysql]# cd .. [root@12c lib]# tar -cvzf proxysql.tar.gz proxysql proxysql/ proxysql/proxysql-key.pem proxysql/proxysql-ca.pem proxysql/proxysql-cert.pem proxysql/proxysql.log proxysql/proxysql_stats.db proxysql/proxysql.db proxysql/proxysql.pid [root@12c lib]# cd proxysql/ [root@12c proxysql]# ll total 344 -rw-rw-r-- 1 proxysql proxysql 1050 Jul 18 13:45 proxysql-ca.pem -rw-rw-r-- 1 proxysql proxysql 1058 Jul 18 13:45 proxysql-cert.pem -rw------- 1 proxysql proxysql 163840 Jul 18 14:49 proxysql.db -rw-rw-r-- 1 proxysql proxysql 1679 Jul 18 13:45 proxysql-key.pem -rw------- 1 proxysql proxysql 26369 Jul 18 16:20 proxysql.log -rw-r--r-- 1 proxysql proxysql 5 Jul 18 14:24 proxysql.pid -rw------- 1 proxysql proxysql 143360 Jul 18 16:23 proxysql_stats.db

4、关闭并清空

[root@12c proxysql]# /etc/init.d/proxysql stop Shutting down ProxySQL: DONE! [root@12c proxysql]# rm *.* -f [root@12c proxysql]# ll total 0

5、启动并观察

[root@12c proxysql]# /etc/init.d/proxysql start Starting ProxySQL: 2019-07-18 16:24:16 [INFO] Using config file /etc/proxysql.cnf 2019-07-18 16:24:16 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates. DONE! [root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin@127.0.0.1 [(none)] 04:24:23>>>SELECT * FROM mysql_servers; Empty set (0.00 sec) admin@127.0.0.1 [(none)] 04:25:03>>>exit Bye 6、关闭后恢复 [root@12c proxysql]# /etc/init.d/proxysql stop Shutting down ProxySQL: DONE! [root@12c proxysql]# .. [root@12c proxysql]# ll total 308 -rw-rw-r-- 1 proxysql proxysql 1050 Jul 18 16:24 proxysql-ca.pem -rw-rw-r-- 1 proxysql proxysql 1058 Jul 18 16:24 proxysql-cert.pem -rw------- 1 proxysql proxysql 163840 Jul 18 16:24 proxysql.db -rw-rw-r-- 1 proxysql proxysql 1679 Jul 18 16:24 proxysql-key.pem -rw------- 1 proxysql proxysql 4095 Jul 18 16:25 proxysql.log -rw------- 1 proxysql proxysql 135168 Jul 18 16:24 proxysql_stats.db [root@12c proxysql]# rm *.* -f [root@12c proxysql]# cd .. [root@12c lib]# tar -xvzf proxysql.tar.gz proxysql/ proxysql/proxysql-key.pem proxysql/proxysql-ca.pem proxysql/proxysql-cert.pem proxysql/proxysql.log proxysql/proxysql_stats.db proxysql/proxysql.db proxysql/proxysql.pid

7、启动后重新观察

[root@12c lib]# /etc/init.d/proxysql start Starting ProxySQL: Removed stale pid file: /var/lib/proxysql/proxysql.pid 2019-07-18 16:25:53 [INFO] Using config file /etc/proxysql.cnf 2019-07-18 16:25:53 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them. DONE! [root@12c lib]# mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin@127.0.0.1 [(none)] 04:26:15>>>SELECT * FROM mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.56.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.56.11 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec) admin@127.0.0.1 [(none)] 04:26:16>>>exit Bye

8、查看路由规律

[root@12c lib]# mysql -urootuser -p123456 -h292.168.56.11 -P6033 -e "SELECT @@server_id;" +-------------+ | @@server_id | +-------------+ | 11 | +-------------+ [root@12c lib]# mysql -urootuser -p123456 -h292.168.56.11 -P6033 -e "BEGIN;SELECT @@server_id;commit;" +-------------+ | @@server_id | +-------------+ | 1 | +-------------+