系列文章： 1.MySQL主从复制 2.OneProxy实现MySQL读写分离
读写分离方案，其实我们可以通过配置动态数据源来实现。也可以通过一些中间件来实现，比如OneProxy，MaxScale，MysqlProxy来实现。下面我们要讲的是如何通过OneProxy来实现MySQL的读写分离。
从OneProxy的官网上，我们能看到OneProxy的介绍：
MySQL的逻辑复制技术可轻松构建多个数据副本来提供服务，并可以消除数据库单点，但需要应用作出相应的代码调整，才能充分利用它的优势。而网络交换机/路由器在理解TCP协议和目的IP地址的情况下，可以帮助人们轻松地组建大大小小的网络， OneProxy for MySQL在理解MySQL通信协议和SQL语句分析的基础上，可以帮助轻松组建数据库集群，避免代价昂贵的应用代码调整。
OneProxy for MySQL可以复用不同应用到后端数据库的连接，有效降低数据库的并发连接数；可以即时踢除不可用的节点，将应用请求转发到其他可用节点，保证业务服务的稳定性。 可透明地将查询语句分发到多个MySQL备库执行，用读写分离方案支持上千万的并发访问；也可以根据SQL语句中的值进行分库分表路由， 均匀分散到多个MySQL主库上，以支持每秒上百万个小事务的并发执行；可实时透明地分析流量数据，统计SQL和事务的运行时间，分析事务的结构，得到各种不同维度的实时性能报告； 还可以进行流理QoS控制，作为数据库防火墙抵挡SQL注入式攻击；根据分片的SQL并行执行，解决了大数据量下的汇总统计性能问题；跨多分片的结果集合并， 极大地简化了应用程序的开发工作量。
更多介绍可以看Oneproxy介绍
首先，搭好Mysql的主从复制结构： master 192.168.10.21 slave 192.168.10.6
1.在master和slave中创建test用户并且给其分配权限。
grant all privileges on *.* to test@'%' identified by 'test'
2.修改demo.sh
image.png
3.给demo.sh赋权限
chmod 777 demo.sh
4.修改启动的脚本，将ONEPROXY_HOME设置你安装的oneproxy的路径
vim oneproxy.service
image.png
5.调用mysqlpwd进行密码加密
[root@localhost oneproxy]# ls bin demo.sh oneproxy.service sql testautocommit.sql trantest.sql conf log README testadmin.sql testproxy.sql [root@localhost oneproxy]# cd bin [root@localhost bin]# ls mysqlpwd oneproxy [root@localhost bin]# ./mysqlpwd test 1378F6CC3A8E8A43CA388193FBED5405982FBBD3
6.配置oneproxy
[oneproxy] keepalive = 1 event-threads = 4 log-file = log/oneproxy.log pid-file = log/oneproxy.pid lck-file = log/oneproxy.lck proxy-auto-readonly = 1 proxy-forward-clientip = 1 proxy-trans-debug = 1 mysql-version = 5.7.17 admin-address = 0.0.0.0:4041 proxy-address = 0.0.0.0:3307 proxy-master-addresses = 192.168.10.21:3306@oneproxy proxy-slave-addresses = 192.168.10.6:3306@oneproxy proxy-user-list.1 = oneproxy:test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@rap_test proxy-part-template = conf/template.txt proxy-part-tables = conf/part.txt proxy-charset = utf8mb4_general_ci proxy-secure-client = 127.0.0.1 proxy-license = A2FF461456A67F28,D2F6A5AD70C9042D proxy-httpserver = 0.0.0.0:8080 proxy-httpauth = admin:admin proxy-httptitle = oneProxy[cmazxiaoma] proxy-group-security = oneproxy:0 proxy-group-policy = oneproxy:2 proxy-sequence-group = oneproxy proxy-sequence.1 = seq1 network-blocking= 0
配置参数说明：
[oneproxy] keepalive = 1 event-threads = 4 #指定日志文件路径 log-file = log/oneproxy.log #指定PID文件路径 pid-file = log/oneproxy.pid #指定LCK文件路径 lck-file = log/oneproxy.lck proxy-auto-readonly = 1 proxy-forward-clientip = 1 proxy-trans-debug = 1 #MySQL服务版本 mysql-version = 5.7.17 admin-address = 0.0.0.0:4041 proxy-address = 0.0.0.0:3307 #指定主服务器的IP地址 格式：IP地址:端口@oneproxy组 proxy-master-addresses = 192.168.10.21:3306@oneproxy #指定从服务器的IP地址 格式：IP地址:端口@oneproxy组 proxy-slave-addresses = 192.168.10.6:3306@oneproxy #用户列表 格式：用户名/密文密码@数据库名称 proxy-user-list.1 = oneproxy:test/1378F6CC3A8E8A43CA388193FBED5405982FBBD3@rap_test proxy-part-template = conf/template.txt #指定分表分库的配置文件 proxy-part-tables = conf/part.txt #指定数据库字符集 proxy-charset = utf8mb4_general_ci proxy-secure-client = 127.0.0.1 proxy-license = A2FF461456A67F28,D2F6A5AD70C9042D 指定Web服务的监听端口 proxy-httpserver = 0.0.0.0:8080 #指定Web访问认证信息 格式：用户名:密码 proxy-httpauth = admin:admin #指定Web页面名称 proxy-httptitle = oneProxy[cmazxiaoma] #设定安全级别，0默认值，1禁止DDL，2禁止不带条件的查询语句,3只允许SELECT proxy-group-security = oneproxy:0 #设定预定义策略 #0代表由Lua Script来决定 1代表Read Failover 2代表主节点不参与读 3代表双主结构 4代表主节点参与读操作 5代表随机读取 proxy-group-policy = oneproxy:2 proxy-sequence-group = oneproxy proxy-sequence.1 = seq1 network-blocking = 0
关于proxy-group-policy这个参数我要重点提一下， 它是用来指定MySQL实例的流量切换和分担的策略，具体如下：
- master_only:master进行读写操作。
- read_failover：写流量同“master-only”，针对读流量，如果Master节点可用则从Master节点访问，如果Master节点不可用，则从Slave节点访问。此策略常用于关键配置数据的高可用。
- read_slave：写流量同“master-only”，针对读流量，先从Slave节点读取，如果没有Slave可用，则从Master节点访问。此策略即一主多备情况下的读写分离策略。
- read_balance：写流量同“master-only”，针对读流量，先从任一可用节点读取，包括Master和Slave类型。此策略即一主一备情况下的读写分离策略。
- big_slave：写流量和简单SQL查询流量同“master-only”，针对复杂的SQL语句，先从Slave节点读取，如果没有Slave可用，则从Master节点访问。此策略为一主多备情况下的复杂SQL语句读写分离。
- big_balance：写流量和简单SQL查询同“master-only”，针对复杂的SQL语句，先从任一可用节点读取，包括Master和Slave类型。此策略为一主一备情况下的复杂SQL语句读写分离。
- write_failover：写流量同“master-only”，但通常配有多个Master类型节点，可以预防写操作失败；针对读流量，先从任一可用节点读取。
- write_balance：针对每次写操作，任挑一台Master节点提供服务；针对读流量，先从任一可用节点读取。
如果配置有问题的话是启动不了的，而且oneproxy.log也不会输出任何异常。
7.启动oneproxy，可以看到3307,8080,4041端口信息，说明启动成功了。
[root@localhost oneproxy]# ./demo.sh [root@localhost oneproxy]# netstat -ntlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 5693/oneproxy tcp 0 0 0.0.0.0:9100 0.0.0.0:* LISTEN 2115/grunt tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN 5693/oneproxy tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 979/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1193/master tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 5693/oneproxy tcp6 0 0 :::22 :::* LISTEN 979/sshd tcp6 0 0 ::1:25 :::* LISTEN 1193/master tcp6 0 0 :::3306 :::* LISTEN 4739/mysqld [root@localhost oneproxy]#
8.我们可以进入OneProxy的admin模式。默认账号是admin，密码是OneProxy
image.png
- 查看读写分离状态。
mysql> list backend\g
+------+--------------------+------+--------+--------+----------+----------+---------+------------------+ | INDX | ADDRESS | TYPE | STATUS | MARKUP | REQUESTS | GROUP | Seconds | SyncTime | +------+--------------------+------+--------+--------+----------+----------+---------+------------------+ | 2 | 192.168.10.21:3306 | RW | UP | 1 | 0 | oneproxy | 22639 | 1540957748819753 | | 3 | 192.168.10.6:3306 | RW | UP | 1 | 0 | oneproxy | 0 | 1540980388023589 | +------+--------------------+------+--------+--------+----------+----------+---------+------------------+ 2 rows in set (0.00 sec) mysql> list pool\g +------+--------------------+------+--------+------+---------+---------+----------+ | INDX | ADDRESS | USER | LENGTH | SIZE | MINIDLE | MAXIDLE | REQUESTS | +------+--------------------+------+--------+------+---------+---------+----------+ | 2 | 192.168.10.21:3306 | test | 20 | 20 | 20 | 200 | 0 | | 3 | 192.168.10.6:3306 | test | 20 | 20 | 20 | 200 | 40 | +------+--------------------+------+--------+------+---------+---------+----------+ 2 rows in set (0.00 sec) mysql>
10.通过访问8080端口，我们可以通过Web界面可视化数据，更好的观察读写分离状态。
image.png
11.访问3307端口，也就是oneProxy代理的地址。rap_test库和date_demo是我们之前测试MySQL主从复制建立的数据库和表。我们可以看到多了一个oneproxy_replication_timestamp。在我们的master和slave中的rap_test库也可以看到这张表。这张表用来检测读节点的复制延迟。
image.png
master.png
slave.png
12.我们通过web界面和oneProxy.log发现延迟了。也就是oneproxy_replication_timestamp里面的时间戳不一致。
image.png
image.png
13.我们进入3307端口，看一下master和slave的状态。slave的状态是ok的。
mysql> show slave status\G
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.21 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 178305 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 589 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 178305 Relay_Log_Space: 800 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 21 Master_UUID: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026 Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
查看master的状态,我们发现OneProxy依赖的是master的mysql-bin.000001文件，而slave复制依赖的是master的mysql-bin.000002文件。
mysql> ^C mysql> show master status\g +------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+-----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 129901019 | | | | +------------------+-----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
14.我们在master上，查看master 的状态。发现master的binlog文件时mysql-bin.000002文件。
image.png
15.查看master的所有binlog文件
mysql> show binary logs\g
+------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 201 | | mysql-bin.000002 | 178305 | +------------------+-----------+ 2 rows in set (0.00 sec)
16.查看binlog文件中的事件
mysql> show binlog events in 'mysql-bin.000001'\g +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 21 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 21 | 154 | | | mysql-bin.000001 | 154 | Rotate | 21 | 201 | mysql-bin.000002;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000002' limit 10\g +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 21 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 21 | 154 | | | mysql-bin.000002 | 154 | Anonymous_Gtid | 21 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 219 | Query | 21 | 353 | use `rap_master`; DROP TABLE `date_demo` /* generated by server */ | | mysql-bin.000002 | 353 | Anonymous_Gtid | 21 | 418 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 418 | Query | 21 | 517 | drop database `rap_master` | | mysql-bin.000002 | 517 | Anonymous_Gtid | 21 | 582 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 582 | Query | 21 | 738 | create database `rap_test`character set utf8mb4 collate utf8mb4_general_ci | | mysql-bin.000002 | 738 | Anonymous_Gtid | 21 | 803 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000002 | 803 | Query | 21 | 898 | drop database `rap_test` | +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+ 10 rows in set (0.00 sec)
17.重置master的binlog
mysql> reset master\g
Query OK, 0 rows affected (0.82 sec)
mysql> show binary logs\g
+------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 154 | +------------------+-----------+ 1 row in set (0.00 sec) mysql>
18.重新配置slave
mysql> reset slave\g
Query OK, 0 rows affected (0.01 sec) mysql> change master to -> master_host="192.168.10.21", -> master_user="sync", -> master_password="sync", -> master_log_file="mysql-bin.000001", -> master_log_pos=154\g Query OK, 0 rows affected, 2 warnings (0.10 sec)
19.重启oneproxy，通过web界面查看还是存在延迟。我们还忽略了一个点，master地址竟然是192.168.10.6。master地址应该是192.168.10.21。
image.png
20.我们连接192.168.10.6:3306，查看是否配置过master。难怪，oneproxy显示master的binlog一直是mysql-bin.000001(这个binlog是slave开启master模式所产生的binglog)。slave竟然是slave还是master，雌雄同体，WTF。
mysql> show master status\g
+------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+-----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 138280148 | | | | +------------------+-----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
21.感觉这是oneproxy的一个bug,我重启oneProxy，再通过web界面查看，一切正常。
image.png
22.我们连接OneProxy的3307端，调用select @@server_id语句，返回的是6，证明查询语句是在slave端执行。因为之前配置主从复制的时候，master的server_id是21，slave的server_id是6.
mysql> show master status\g
+------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+-----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 141160929 | | | | +------------------+-----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> select @@server_id\g +-------------+ | @@server_id | +-------------+ | 6 | +-------------+ 1 row in set (0.00 sec) mysql>
23.我们在3307端口插入2条记录。然后我们在master和slave查看是否成功插入这2条记录。
mysql> select * from oneproxy_replication_timestamp \g +--------------------------+------------------+ | proxy_uuid | proxy_stamp | +--------------------------+------------------+ | AYCE-ZUSM-OIIN-UYAW-CZEG | 1541038193999142 | +--------------------------+------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO date_demo VALUES(NULL,NOW(),NOW(),NOW(),NOW(),NOW())\g Query OK, 1 row affected, 1 warning (1.06 sec) mysql> INSERT INTO date_demo VALUES(NULL,NOW(),NOW(),NOW(),NOW(),NOW())\g Query OK, 1 row affected, 1 warning (0.46 sec) mysql> select * from date_demo\g +----+----------+---------------------+---------------------+------------+----------------+ | id | time | timestamp | datetime | date | int_date | +----+----------+---------------------+---------------------+------------+----------------+ | 1 | 10:18:07 | 2018-10-30 10:18:07 | 2018-10-30 10:18:07 | 2018-10-30 | 20181030101807 | | 2 | 11:18:57 | 2018-10-30 11:18:57 | 2018-10-30 11:18:57 | 2018-10-30 | 20181030111857 | | 3 | 18:45:40 | 2018-10-31 18:45:40 | 2018-10-31 18:45:40 | 2018-10-31 | 20181031184540 | | 4 | 10:10:08 | 2018-11-01 10:10:08 | 2018-11-01 10:10:08 | 2018-11-01 | 20181101101008 | | 5 | 10:10:36 | 2018-11-01 10:10:36 | 2018-11-01 10:10:36 | 2018-11-01 | 20181101101036 | +----+----------+---------------------+---------------------+------------+----------------+ 5 rows in set (0.00 sec)
master.png
slave.png
24.我们可以用OneProxy Web界面查看SQLS、TableS、DMLS可视化数据统计。可以发现date_demo执行Insert语句有3次。
image.png
image.png
25.将binlog转换成SQL语句,我们在output.sql可以看到我们刚才插入的sql语句。
#mysqlbinlog -d rap_test mysql-bin.000001 -r output.sql
image.png
image.png
26.最后记录一下MySQL性能监控的一些参数。
SHOW GLOBAL VARIABLES LIKE '%max_connections%'
SHOW GLOBAL STATUS LIKE '%Threads_created%' SHOW GLOBAL STATUS LIKE '%threads_running%' SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table' SHOW GLOBAL VARIABLES LIKE 'show_query_log' SHOW VARIABLES LIKE '%partition%' SHOW VARIABLES LIKE "%innodb_buffer_pool_size%";