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