在美团点评dbproxy读写分离使用说明文章中已经说明了使用目的，本文介绍proxysql的使用方法以及和dbproxy的性能差异。具体的介绍可以看官网的相关说明，并且这个中间件也是percona推的一款中间件。其特性和其他读写分离的中间件差距不大，具体的会在文中介绍。本文大致简单的介绍在使用过程中的一些说明，也可以看官方的wiki获得使用帮助。
环境：
distributor id： ubuntu
description： ubuntu 14.04.5 lts
release： 14.04codename： trusty
下载
percona站点：
https：//www.percona.com/downloads/proxysql/
github/官网：
https：//github.com/sysown/proxysql/releases
我们首先看一下自己的环境：
mha已经搭建：
master：172.16.16.35:3306slave：172.16.16.35:3307slave：172.16.16.34:3307
mha manager在172.16.16.34，配置文件如下：
[root@localhost bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456ping_interval=1remote_workdir=/tmp
repl_password=123456repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=ssh_user=root
user=root
[server1]
hostname=172.16.16.35port=3306[server2]
candidate_master=1check_repl_delay=0hostname=172.16.16.34port=3306[server3]
hostname=172.16.16.35port=3307
下面我们基于这样一套mha环境搭建读写分离。
1：安装proxysql软件，这个我们部署到172.16.16.34上
[root@localhost bin]# sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm[root@localhost bin]# yum install proxysql
最后有以下提示：
installed:
proxysql.x86_64 0:1.3.7-1.1.el6
complete!
也就是安装完成了。然后查一下具体的文件：
[root@localhost bin]# find / -name proxysql/var/lib/proxysql/var/run/proxysql/etc/rc.d/init.d/proxysql/usr/bin/proxysql
发现确实已经将proxysql安装成功了
2：启动配置proxysql
看一下配置文件：
[root@localhost bin]# cat /etc/proxysql-admin.cnf
# proxysql admin interface credentials.
export proxysql_username=adminexport proxysql_password=adminexport proxysql_hostname=localhostexport proxysql_port=6032
# pxc admin credentials for connecting to pxc-cluster-node.
export cluster_username=adminexport cluster_password=adminexport cluster_hostname=localhostexport cluster_port=3306
# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export monitor_username=monitorexport monitor_password=monit0r
# application user to connect to pxc-node through proxysql
export cluster_app_username=proxysql_userexport cluster_app_password=passw0rd
# proxysql read/write hostgroup
export write_hostgroup_id=10export read_hostgroup_id=11
# proxysql read/write configuration mode.
export mode=singlewrite
启动：
[root@localhost bin]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
this script will assist with configuring proxysql (currently only percona xtradb cluster in combination with proxysql is supported)
proxysql read/write configuration mode is singlewrite
proxysql is not running; please start the proxysql service
现在来说proxysql 的路由已经启动，提示我们要启动proxysql service
[root@localhost bin]# service proxy
proxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor
[root@localhost bin]# service proxysql start
starting proxysql: done![root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -p6032
mysql: [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 1server version: 5.7.14 (proxysql admin module)
copyright (c) 2000, 2016, 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.
mysql>
可以看到我们已经登录成功了，这里要说明的是 proxysql的默认配置文件是在：
[root@localhost bin]# find / -name proxysql.cnf/etc/proxysql.cnf
接下来我们开始配置proxysql：
[root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -p6032
mysql: [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 2server version: 5.7.14 (proxysql admin module)
copyright (c) 2000, 2016, 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.
mysql> show databases;+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+4 rows in set (0.00 sec)
mysql> use admin
database changed
mysql> show tables;+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| mysql_collations |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| runtime_global_variables |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_scheduler |
| scheduler |
+--------------------------------------+13 rows in set (0.00 sec)
下面加入主从信息：
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'172.16.16.35',3306,1,1000,10,'test');
query ok, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.34',3306,1,1000,10,'test');
query ok, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.35',3307,1,1000,10,'test');
query ok, 1 row affected (0.00 sec)
mysql> select * from mysql_servers;+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 172.16.16.35 | 3306 | online | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.34 | 3306 | online | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.35 | 3307 | online | 1 | 0 | 1000 | 10 | 0 | 0 | test |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)
然后添加读写映射（主要是mha后端切换的时候保证proxysql也能够自动切换）：
mysql> insert into mysql_replication_hostgroups values(100,101,'masterha') ;
query ok, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+----------+
| 100 | 101 | masterha |
+------------------+------------------+----------+1 row in set (0.00 sec)
为proxysql添加监控账号：
mysql> grant super, replication client on *.* to 'proxysql'@'172.16.16.%' identified by 'proxysql';
query ok, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges;
query ok, 0 rows affected (0.10 sec)
配置监控账号（在proxysql当中进行配置）：
mysql> set mysql-monitor_username='proxysql';
query ok, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='proxysql';
query ok, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
query ok, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
query ok, 74 rows affected (0.02 sec)
ps:有时候runtime_mysql_servers的status不为online状态的话可以通过查看monitor.mysql_server_ping_log这个表来查看具体的报错信息。
mysql> select * from monitor.mysql_server_ping_log;
然后配置程序账号，简单点统一使用root：123456最高权限来配置：
mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('root','123456',1,100,1);
query ok, 1 row affected (0.00 sec)
mysql> select * from mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123456 | 1 | 0 | 100 | null | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (0.00 sec)
配置完成以后开始重新加载和保存我们的配置：
mysql> load mysql servers to runtime;
query ok, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
query ok, 0 rows affected (0.08 sec)
mysql> load mysql users to runtime;
query ok, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
query ok, 0 rows affected (0.03 sec)
接下来开始配置路由规则：
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select.*for update$',100,1);
query ok, 1 row affected (0.01 sec)
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select',101,1);
query ok, 1 row affected (0.00 sec)
mysql> load mysql query rules to runtime;
query ok, 0 rows affected (0.00 sec)
mysql> save mysql query rules to disk;
query ok, 0 rows affected (0.04 sec)
至此配置已经完成了
3：测试读写分离
在172.16.16.35上链接proxysql端口6033，并且做简单的select操作：
[root@localhost ~]# mysql -uroot -p123456 -h172.16.16.34 -p6033
mysql: [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 22server version: 5.7.14 (proxysql)
copyright (c) 2000, 2016, 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.
mysql> select * from maxiangqian.test;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.04 sec)
然后在进行查询看一下：
mysql> select * from stats_mysql_query_digest;
可以看到已经完成了读写分离了。
mysql> select @@server_id;+-------------+
| @@server_id |
+-------------+
| 353307 |
+-------------+1 row in set (0.01 sec)
查看一下server id已经路由到了172.16.16.35:3307这个从库上了。
测一下for update：
mysql> select * from maxiangqian.test for update;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.00 sec)
再查看
mysql> select * from stats_mysql_query_digest;
已经自动路由到了主库。
至此proxysql+mysql mha读写分离测试完成。。。未完待续
5：关于proxysql的思考以及简单的命令
proxysql通过以上方式是可以实现读写分离，但是这种方式真的就没有问题了吗，如果是一些比如查询订单状态的这种要求实时性非常高的sql的话，似乎被路由到了从库就会出现bug。我们可以选择在程序端控制这些参数，proxysql只作为一个负载均衡来使用，给proxysql创建多个账号，一个读写，一个只读。然后程序去实现读写分离。
proxysql是分三层来设计运行的，分别为runtime ，memory ，disk ：
runtime 代表的是proxysql当前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置，必须要从下一层load进来。
memory 是平时在mysql命令行修改的 main 里头配置，可以认为是sqlite数据库在内存的镜像
disk / config file 持久存储的那份配置，一般在$(datadir)/proxysql.db，在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到，完了后，如果要修改监听端口，还是需要在管理命令行里修改，再 save 到硬盘
常用命令：
load mysql servers to runtime -- 让修改的配置生效，也就是从memory 把参数load过来，等价于load mysql users from memory，这个语句的语法比较单间，from代表从上层load过来，to代表从本层到某一个层。比如前面我们设置了mysql的监控账号，但是还是要执行load和save保存变量并且使变量生效。
以上就是proxysql读写分离从配置到使用的详细内容。