背景:
前面的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.cnfchown -R proxysql.proxysql proxysql2/
7,开启实例:
root@dbproxy:/var/lib# /etc/init.d/proxysql2 start
Starting ProxySQL2: DONE!root@dbproxy:/var/lib# ps -ef| grepproxy
proxysql5324 1 0 May27 ? 00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
proxysql5325 5324 0 May27 ? 00:20:29 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
proxysql6877 1 0 17:19 ? 00:00:00 /usr/bin/proxysql2 -c /etc/proxysql2.cnf -D /var/lib/proxysql2
proxysql6878 6877 0 17:19 ? 00:00:00 /usr/bin/proxysql2 -c /etc/proxysql2.cnf -D /var/lib/proxysql2
查看端口信息:
root@dbproxy2:/var/lib# netstat -nltpProto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp0 0 127.0.0.1:6032 0.0.0.0:* LISTEN 5325/proxysql
tcp0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 5325/proxysql
tcp0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 5325/proxysql
tcp0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 5325/proxysql
tcp0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 5325/proxysql
tcp0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1773/sshd
tcp0 0 127.0.0.1:7032 0.0.0.0:* LISTEN 6878/proxysql2
tcp0 0 0.0.0.0:7033 0.0.0.0:* LISTEN 6878/proxysql2
tcp0 0 0.0.0.0:7033 0.0.0.0:* LISTEN 6878/proxysql2
tcp0 0 0.0.0.0:7033 0.0.0.0:* LISTEN 6878/proxysql2
tcp0 0 0.0.0.0:7033 0.0.0.0:* LISTEN 6878/proxysql2
tcp60 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 connectionid is 1Server 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 clearthe current input statement.
admin@127.0.0.1 : (none) 05:50:40>select *from mysql_servers;
Empty set (0.00sec)
admin@127.0.0.1 : (none) 05:50:42>
到此,多实例已经建立成功,接着说明下如何对持久化的配置进行备份。
持久化配置备份
上面已经提到,ProxySQL的持久化是通过SQLITE实现的,对于备份就是对SQLITE的备份,关于如何使用SQLITE可以查看官方文档和教程。这里大致的说明下sqlite以 SQL 文本格式转储数据。
1:先到指定的数据db目录
root@dbproxy:/var/lib/proxysql# ls -lh-rw------- 1 proxysql proxysql 33K May 31 17:44proxysql.db-rw------- 1 proxysql proxysql 142K May 31 17:44proxysql.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
total0
在目标实例目录中执行:
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:17proxysql.db-rw------- 1 proxysql proxysql 21K May 31 18:20proxysql.log-rw-r--r-- 1 proxysql proxysql 5 May 31 18:20proxysql.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 connectionid is 2Server 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 clearthe 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.00sec)
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。