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

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

[root@12c proxysql]# cd /var/lib/proxysql/[root@12c proxysql]# lltotal 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 proxysqlproxysql/proxysql/proxysql-key.pemproxysql/proxysql-ca.pemproxysql/proxysql-cert.pemproxysql/proxysql.logproxysql/proxysql_stats.dbproxysql/proxysql.dbproxysql/proxysql.pid[root@12c lib]# cd  proxysql/[root@12c proxysql]# lltotal 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 stopShutting down ProxySQL: DONE![root@12c proxysql]# rm *.* -f[root@12c proxysql]# lltotal 0

5、启动并观察

[root@12c proxysql]# /etc/init.d/proxysql startStarting ProxySQL: 2019-07-18 16:24:16 [INFO] Using config file /etc/proxysql.cnf2019-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 1Server 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>>>exitBye6、关闭后恢复[root@12c proxysql]# /etc/init.d/proxysql stopShutting down ProxySQL: DONE![root@12c proxysql]# ..[root@12c proxysql]# lltotal 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.pemproxysql/proxysql-ca.pemproxysql/proxysql-cert.pemproxysql/proxysql.logproxysql/proxysql_stats.dbproxysql/proxysql.dbproxysql/proxysql.pid

7、启动后重新观察

[root@12c lib]# /etc/init.d/proxysql startStarting ProxySQL: Removed stale pid file: /var/lib/proxysql/proxysql.pid2019-07-18 16:25:53 [INFO] Using config file /etc/proxysql.cnf2019-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 1Server 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>>>exitBye

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 |+-------------+