MySQL ProxySQL相关维护说明

iojsioi

背景：
　　前面的2篇文章MySQL ProxySQL读写分离使用初探和MySQL ProxySQL读写分离实践大致介绍了ProxySQL的使用说明，从文章的测试的例子中看到ProxySQL使用SQLITE来进行配置的持久化，以及ProxySQL是一个CPU密集型的中间价，如果CPU比较空闲，可以像MySQL一样安装多个实例的ProxySQL，充分利用资源。下面介绍下如何备份持久化的ProxySQL配置和多实例ProxySQL的创建。

多实例建立：
　　假设默认的实例已经装好了（6032、6033），现在需要新增一个新实例（ProxySQL2）：7032，7033
　　1，添加配置文件：
　　

cp /etc/proxysql.cnf /etc/proxysql2.cnf　　

　　2，修改配置文件：修改3行
　　

datadir="/var/lib/proxysql2"　　
...
　　
...
　　
mysql_ifaces
="127.0.0.1:7032;/tmp/proxysql_admin2.sock"　　
...
　　
...
　　
interfaces
="0.0.0.0:7033;/tmp/proxysql2.sock"　　
...
　　

　　3，添加启动文件：
　　

cp /etc/init.d/proxysql /etc/init.d/proxysql2　　

　　4，修改启动文件：
　　

OLDDATADIR="/var/run/proxysql2"　　
DATADIR
="/var/lib/proxysql2"　　
OPTS
="-c /etc/proxysql2.cnf -D $DATADIR"　　

　　
修改成新的启动
　　

sudo -u proxysql /usr/bin/proxysql2 $OPTS　　

　　
修改2行：
　　

for i in `pgrep proxysql` ; do　　
替换为：
　　

for i in `pidof proxysql2` ; do　　

　　
最后修改一些输出：
　　
把echo输出的proxysql改成proxysql2
　　

　　5，复制执行文件（懒的修改脚本了，直接复制新起一个执行文件...）：
　　

cp /usr/bin/proxysql /usr/bin/proxysql2　　

　　这里需要注意的是：需要修改默认实例的启动文件（/etc/init.d/proxysql）
　　

修改2行：　　

for i in `pgrep proxysql` ; do　　
替换为：
　　

for i in `pidof proxysql` ; do　　

　　即把pgrep改成pidof，这是因为通过pgrep找到了所有的proxysql实例的进程号，会引起误操作，单实例ProxySQL可以不需要考虑。
　　6，修改权限：
　　

chown -R proxysql.proxysql proxysql2.cnf　　

chown -R proxysql.proxysql proxysql2/　　

　　7，开启实例：
　　

root@dbproxy:/var/lib# /etc/init.d/proxysql2 start　　
Starting ProxySQL2: DONE
!　　
root@dbproxy:
/var/lib# ps -ef| grep proxy　　
proxysql   
5324      1  0 May27 ?        00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql　　
proxysql   
5325   5324  0 May27 ?        00:20:29 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql　　
proxysql   
6877      1  0 17:19 ?        00:00:00 /usr/bin/proxysql2 -c /etc/proxysql2.cnf -D /var/lib/proxysql2　　
proxysql   
6878   6877  0 17:19 ?        00:00:00 /usr/bin/proxysql2 -c /etc/proxysql2.cnf -D /var/lib/proxysql2　　

　　查看端口信息：
　　

root@dbproxy2:/var/lib# netstat -nltp　　
Proto Recv
-Q Send-Q Local Address           Foreign Address         State       PID/Program name　　
tcp        
0      0 127.0.0.1:6032          0.0.0.0:*               LISTEN      5325/proxysql　　
tcp        
0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      5325/proxysql　　
tcp        
0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      5325/proxysql　　
tcp        
0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      5325/proxysql　　
tcp        
0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      5325/proxysql　　
tcp        
0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1773/sshd　　
tcp        
0      0 127.0.0.1:7032          0.0.0.0:*               LISTEN      6878/proxysql2　　
tcp        
0      0 0.0.0.0:7033            0.0.0.0:*               LISTEN      6878/proxysql2　　
tcp        
0      0 0.0.0.0:7033            0.0.0.0:*               LISTEN      6878/proxysql2　　
tcp        
0      0 0.0.0.0:7033            0.0.0.0:*               LISTEN      6878/proxysql2　　
tcp        
0      0 0.0.0.0:7033            0.0.0.0:*               LISTEN      6878/proxysql2　　
tcp6      
0      0 :::22                   :::*                    LISTEN      1773/sshd       　　

　　8：完成
　　

root@dbproxy:/var/lib/proxysql2# /etc/init.d/proxysql2 stop　　
Shutting down ProxySQL2: DONE
!　　
root@dbproxy:
/var/lib/proxysql2# /etc/init.d/proxysql2 start　　
Starting ProxySQL2: DONE
!　　
root@dbproxy:
/var/lib/proxysql2# /etc/init.d/proxysql2 status　　
ProxySQL2 is running (
9164).　　

　　

root@dbproxy2:/var/lib/proxysql2#mysql -uadmin -padmin -h127.0.0.1 -P7032　　
Warning: Using a password on the command line interface can be insecure.
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　
Your MySQL connection
id is 1　　
Server version:
5.5.30 (ProxySQL Admin Module)　　

　　
Copyright (c)
2009-2017 Percona LLC and/or its affiliates　　
Copyright (c)
2000, 2017, Oracle and/or its affiliates. All rights reserved.　　

　　
Oracle is a registered trademark of Oracle Corporation and
/or its　　
affiliates. Other names may be trademarks of their respective
　　
owners.
　　

　　
Type
'help;' or '\h' for help. Type '\c' to clear the current input statement.　　

　　
admin@
127.0.0.1 : (none) 05:50:40>select *from mysql_servers;　　
Empty set (
0.00 sec)　　

　　
admin@
127.0.0.1 : (none) 05:50:42>　　

　　到此，多实例已经建立成功，接着说明下如何对持久化的配置进行备份。

持久化配置备份
　　上面已经提到，ProxySQL的持久化是通过SQLITE实现的，对于备份就是对SQLITE的备份，关于如何使用SQLITE可以查看官方文档和SQLite教程。这里大致的说明下sqlite以 SQL 文本格式转储数据。
　　1：先到指定的数据db目录
　　

root@dbproxy:/var/lib/proxysql# ls -lh　　

-rw------- 1 proxysql proxysql  33K May 31 17:44 proxysql.db　　

-rw------- 1 proxysql proxysql 142K May 31 17:44 proxysql.log　　

-rw-r--r-- 1 proxysql proxysql    5 May 31 17:44 proxysql.pid　　

　　2：备份，执行.dump
　　

root@dbproxy:/var/lib/proxysql# sqlite3 proxysql.db .dump > /home/dxy/proxysql.sql 　　

　　3，还原，若还原到另一个ProxySQL（上面安装的实例proxysql2）中，先保证目标proxysql.db文件不存在(需要关闭ProxySQL，删除数据目录里的所有文件）再执行：
　　

root@dbproxy:~# /etc/init.d/proxysql2 stop　　
Shutting down ProxySQL2: DONE
!　　

　　
root@dbproxy:
/var/lib/proxysql2# ls -lh　　
total
0　　

　　在目标实例目录中执行：
　　

root@dbproxy:/var/lib/proxysql2# sqlite3 proxysql.db < /home/dxy/proxysql.sql　　
root@dbproxy:
/var/lib/proxysql2# ls -lh　　
total 32K
　　

-rw-r--r-- 1 root root 32K May 31 18:17 proxysql.db　　

　　开启实例：
　　

root@dbproxy:/var/lib/proxysql2# /etc/init.d/proxysql2 start　　
Starting ProxySQL: DONE
!　　
root@dbproxy:
/var/lib/proxysql2# ls -lh　　
total 60K
　　

-rw-r--r-- 1 root     root     32K May 31 18:17 proxysql.db　　

-rw------- 1 proxysql proxysql 21K May 31 18:20 proxysql.log　　

-rw-r--r-- 1 proxysql proxysql   5 May 31 18:20 proxysql.pid　　

　　
#修改权限
　　
root@dbproxy:
/var/lib/proxysql2# chown -R proxysql.proxysql proxysql.db　　

　　说明：如果备份的实例和还原的实例使用的端口全部一致，则直接开启ProxySQL就可以用了。但本文的测试备份的实例的端口是6032和6033，还原的目标实例端口是7032和7033，即使还原了也不能使用，所以需要修改db文件，用sqlite连接修改：
　　

#连接　　
root@dbproxy:
/var/lib/proxysql2#sqlite3 proxysql.db　　

　　
#修改显示
　　
sqlite
> .header on　　
sqlite
> .mode column　　

　　
#查看需要更新的变量
　　
sqlite
> select * from global_variables;　　
...
　　
admin
-mysql_ifaces   127.0.0.1:6032　　
...
　　
mysql
-interfaces     0.0.0.0:6033;/　　

　　
#更新
　　
sqlite
> update global_variables set variable_value='127.0.0.1:7032' where variable_name = 'admin-mysql_ifaces';　　
sqlite
> update global_variables set variable_value='0.0.0.0:7033;/' where variable_name = 'mysql-interfaces';　　

　　上面操作完成之后，再开启ProxySQL验证：
　　

root@dbproxy:/var/lib/proxysql2# /etc/init.d/proxysql2 start　　
Starting ProxySQL2: DONE
!　　

　　
oot@dbproxy2:
/var/lib/proxysql2# mysql -uadmin -padmin -h127.0.0.1 -P7032　　
Warning: Using a password on the command line interface can be insecure.
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　
Your MySQL connection
id is 2　　
Server version:
5.5.30 (ProxySQL Admin Module)　　

　　
Copyright (c)
2009-2017 Percona LLC and/or its affiliates　　
Copyright (c)
2000, 2017, Oracle and/or its affiliates. All rights reserved.　　

　　
Oracle is a registered trademark of Oracle Corporation and
/or its　　
affiliates. Other names may be trademarks of their respective
　　
owners.
　　

　　
Type
'help;' or '\h' for help. Type '\c' to clear the current input statement.　　

　　
admin@
127.0.0.1 : (none) 06:40:08>select * from mysql_users;　　

+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+　　
| username  | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
　　
+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
　　
| abc  | *1AF30A6370D8959926FC3BD6158C9C551D0DBA28 | 1      | 0       | 0                 |                | 0             | 1                      | 0            | 1       | 1        | 10000           |
　　
| bcd | *F8258EA2FA1D7FE2B55DA522BFCC87B93CC63ADF | 1      | 0       | 0                 |                | 0             | 1                      | 0            | 1       | 1        | 10000           |
　　
+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
　　
2 rows in set (0.00 sec)
　　

　　
admin@127.0.0.1 : (none) 06:40:11>select * from mysql_servers;
　　
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+
　　
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment   |
　　
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+
　　
| 100          | 192.168.20.123 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | ReadWrite |
　　
| 1000         | 192.168.20.12 | 3306 | ONLINE | 9      | 0           | 1000            | 10                  | 0       | 0              | ReadOnly  |
　　
| 1000         | 192.168.20.123 | 3306 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 0              | ReadWrite |
　　
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+
　　
3 rows in set (0.00 sec)
　　

　　从上面的结果看到，持久化备份和还原成功。注意：因为sqlite不支持远程备份，需要在本地备份好之后，再传到备份中心进行保存。

总结：
　　到此，关于ProxySQL的介绍告已段落，ProxySQL作者的更新还是比较勤快的，若后续有新功能和特性等，再进行补充说明，关于ProxySQL的文档请见：https://github.com/sysown/ProxySQL。