利用Maxscale实现MySQL读写分离
一、Maxscale简介
MaxScale 是 MariaDB 开发的一个数据库智能代理服务，允许根据数据库 SQL 语句将请求转向目标一个到多个服务器，可设定各种复杂程度的转向规则。MaxScale 设计用于透明的提供数据库的负载均衡和高可用性，同时提供高度可伸缩和灵活的架构，支持不同的协议和路由决策。MaxScale 使用 C 语言开发，利用 Linux 下的异步 I/O 功能。使用 epoll 作为事件驱动框架。
Maxscale有两种方式实现读/写分离。一种是基于connect的，类似于Haproxy，不解析SQL语句，可以通过PHP Yii框架或Java Mybatis框架实现。在此方式中，用Maxscale做多台slave的负载均衡，并且支持主从同步延迟检测功能。
另一种是基于statement的，要解析SQL语句。在这种方式里，前端程序不需要修改，通过Maxscale对SQL语句进行解析，把读/写请求自动路由到后端数据库节点上，从而实现读/写分离。商业软件OneProxy中间件也是基于statement方式实现读/写分离的。这种方式的好处是不修改程序代码，减少了复杂度，可平滑迁移，无感知；缺点是解析SQL势必会增加CPU的性能损耗，性能没有基于connect的方式好。
二、实验环境
本文利用一主两从的复制架构介绍Maxscale读写分离的实现，环境如下：
| 角色 | 主机名 | IP地址 | 操作系统 | 软件版本 | 备注 |
| Maxscale | maxscale.ueb.cn | 172.16.4.101 | CentOS release 6.8 (Final) x86_64 | maxscale-2.1.4 | 读写分离中间件 |
| MasterDB | masterdb.ueb.cn | 172.16.4.212 | CentOS release 6.8 (Final) x86_64 | MariaDB-10.1.24 | 写入操作 |
| SlaveDB01 | slavedb01.ueb.cn | 172.16.4.214 | CentOS release 6.8 (Final) x86_64 | MariaDB-10.1.24 | 读取操作 |
| SlaveDB02 | slavedb02.ueb.cn | 172.16.4.215 | CentOS release 6.8 (Final) x86_64 | MariaDB-10.1.24 | 读取操作 |
三、主从配置
略过。参见《MySQL5.6：基于GTID的主从复制》
四、下载安装
官方下载地址:https://mariadb.com/downloads/mariadb-tx/maxscale 可根据操作系统类型，选择合适的版本。
1 [root@maxscale local]# wget https://downloads.mariadb.com/MaxScale/2.1.4/rhel/6/x86_64/maxscale-2.1.4-1.rhel.6.x86_64.rpm 2 [root@maxscale ~]# rpm -ivh maxscale-2.1.4-1.rhel.6.x86_64.rpm 3 warning: maxscale-2.1.4-1.rhel.6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 8167ee24: NOKEY 4 Preparing... ########################################### [100%] 5 1:maxscale ########################################### [100%] 6 [root@maxscale ~]#
五、配置MaxScale实现读写分离
5.1 创建加密文件
1 [root@maxscale local]# maxkeys /var/lib/maxscale/
5.2 创建加密密码
1 [root@maxscale local]# maxpasswd /var/lib/maxscale/ 2wsx#EDC 2 C100888B653922DAEBBB26540D504250 3 [root@maxscale local]#
5.3 修改文件描述符65535以达到tcp的最大连接数
1 [root@maxscale local]# vim /etc/security/limits.conf 2 * soft nofile 65535 3 * hard nofile 65535 4 * soft nproc 65535 5 * hard nproc 65535 6 [root@maxscale local]# vim /etc/sysctl.conf 7 fs.file-max=65535 8 net.ipv4.tcp_fin_timeout = 30 9 net.ipv4.tcp_tw_reuse = 1 10 net.ipv4.tcp_tw_recycle = 1 11 net.ipv4.ip_local_port_range = 1024 65000 12 net.ipv4.tcp_max_syn_backlog = 65536 13 net.ipv4.tcp_max_tw_buckets = 20000 14 net.ipv4.route.gc_timeout = 100 15 net.ipv4.tcp_syn_retries = 1 16 net.ipv4.tcp_synack_retries = 1 17 net.core.somaxconn = 65535 18 net.core.netdev_max_backlog = 262144 19 net.ipv4.tcp_timestamps = 0 20 net.ipv4.tcp_max_orphans = 262144 21 [root@maxscale local]# 22 [root@maxscale local]# sysctl -p 23 net.ipv4.ip_forward = 0 24 net.ipv4.conf.default.rp_filter = 1 25 net.ipv4.conf.default.accept_source_route = 0 26 kernel.sysrq = 0 27 kernel.core_uses_pid = 1 28 net.ipv4.tcp_syncookies = 1 29 kernel.msgmnb = 65536 30 kernel.msgmax = 65536 31 kernel.shmmax = 68719476736 32 kernel.shmall = 4294967296 33 fs.file-max = 65535 34 net.ipv4.tcp_fin_timeout = 30 35 net.ipv4.tcp_tw_reuse = 1 36 net.ipv4.tcp_tw_recycle = 1 37 net.ipv4.ip_local_port_range = 1024 65000 38 net.ipv4.tcp_max_syn_backlog = 65536 39 net.ipv4.tcp_max_tw_buckets = 20000 40 net.ipv4.route.gc_timeout = 100 41 net.ipv4.tcp_syn_retries = 1 42 net.ipv4.tcp_synack_retries = 1 43 net.core.somaxconn = 65535 44 net.core.netdev_max_backlog = 262144 45 net.ipv4.tcp_timestamps = 0 46 net.ipv4.tcp_max_orphans = 262144 47 [root@maxscale local]#
5.4 配置Maxscale服务，配置文件如下：
1 [root@maxscale local]# cat /etc/maxscale.cnf 2 [maxscale] 3 threads=4 4 auth_connect_timeout=10 5 auth_read_timeout =10 6 auth_write_timeout=10 7 ms_timestamp=1 8 skip_permission_checks =false 9 syslog=1 10 maxlog=1 11 log_notice=1 12 log_throttling=10,1000,10000 13 14 # Server definitions 15 [server1] 16 type=server 17 address=172.16.4.212 18 port=3306 19 protocol=MySQLBackend 20 21 [server2] 22 type=server 23 address=172.16.4.214 24 port=3306 25 protocol=MySQLBackend 26 27 [server3] 28 type=server 29 address=172.16.4.215 30 port=3306 31 protocol=MySQLBackend 32 33 34 [MySQL Monitor] 35 type=monitor 36 module=mysqlmon 37 servers=server1,server2,server3 38 user=monitor 39 passwd=C100888B653922DAEBBB26540D504250 40 monitor_interval=10000 41 detect_stale_master=1 42 detect_replication_lag=1 43 44 [Read-Only Service] 45 type=service 46 router=readconnroute 47 servers=server2,server3 48 user=super 49 passwd=C100888B653922DAEBBB26540D504250 50 router_options=slave 51 52 [Read-Write Service] 53 type=service 54 router=readwritesplit 55 servers=server1,server2,server3 56 user=super 57 passwd=C100888B653922DAEBBB26540D504250 58 max_slave_replication_lag=5 59 enable_root_user=1 60 version_string=5.5.37-MariaDB-RWsplit 61 connection_timeout=300 62 max_connections=2000 63 log_auth_warnings=1 64 auth_all_servers=1 65 strip_db_esc=1 66 retry_on_failure=1 67 max_slave_connections=100% 68 69 [MaxAdmin Service] 70 type=service 71 router=cli 72 73 [Read-Only Listener] 74 type=listener 75 service=Read-Only Service 76 protocol=MySQLClient 77 port=4008 78 79 [Read-Write Listener] 80 type=listener 81 service=Read-Write Service 82 protocol=MySQLClient 83 port=4006 84 85 [MaxAdmin Listener] 86 type=listener 87 service=MaxAdmin Service 88 protocol=maxscaled 89 socket=default
5.5 启动MaxScale服务：
1 [root@maxscale maxscale]# /etc/init.d/maxscale start 2 Starting MaxScale: maxscale (pid 3489) is running... [ OK ] 3 [root@maxscale maxscale]# netstat -tnlp 4 Active Internet connections (only servers) 5 Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name 6 tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1871/sshd 7 tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 2035/sshd 8 tcp 0 0 127.0.0.1:6011 0.0.0.0:* LISTEN 2891/sshd 9 tcp 0 0 :::4006 :::* LISTEN 3489/maxscale 10 tcp 0 0 :::4008 :::* LISTEN 3489/maxscale 11 tcp 0 0 :::22 :::* LISTEN 1871/sshd 12 tcp 0 0 ::1:6010 :::* LISTEN 2035/sshd 13 tcp 0 0 ::1:6011 :::* LISTEN 2891/sshd 14 [root@maxscale maxscale]#
可以看到读写分离端口4006和只读端口4008已经启动。
六、测试验证读写分离
6.1 基于connect方式的测试
6.1.1 从库进行Load Balance负载均衡测试
测试之前，在slavedb01(172.16.4.214)、slavedb02(172.16.4.215)上打开General Log，命令：set global general_log=1;
通过客户端连接Maxscale 4008端口，命令如下：
1 [root@maxscale maxscale]# mysql -usuper -p -h 172.16.4.101 -P4008 2 Enter password: 3 Welcome to the MariaDB monitor. Commands end with ; or \g. 4 Your MariaDB connection id is 3493 5 Server version: 5.5.37-MariaDB-RWsplit MariaDB Server 6 7 Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 8 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 10 11 MariaDB [(none)]> select user,host,password from mysql.user; 12 +---------+------------------+-------------------------------------------+ 13 | user | host | password | 14 +---------+------------------+-------------------------------------------+ 15 | root | localhost | | 16 | root | slavedb01.ueb.cn | | 17 | root | 127.0.0.1 | | 18 | root | ::1 | | 19 | | localhost | | 20 | | slavedb01.ueb.cn | | 21 | super | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 22 | monitor | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 23 +---------+------------------+-------------------------------------------+ 24 8 rows in set (0.00 sec) 25 26 MySQL [(none)]>
在slavedb01和slavedb02上查看General Log，得知把请求轮训转发给了slavedb01和slavedb02
1 [root@slavedb01 mysqllogs]# tail -f general.log 2 .......................省略N行............................ 3 170725 14:29:32 9 Query select user,host,password from mysql.user 4 170725 14:29:40 5 Query SELECT @@server_id, @@read_only 5 ...............省略N行.........................
6.1.2 模拟slave延迟
在slavedb01(172.16.4.214)上执行flush tables with read lock全局读锁
1 MariaDB [(none)]> show slave status\G; 2 MariaDB [(none)]> show slave status\G; 3 *************************** 1. row *************************** 4 Slave_IO_State: Waiting for master to send event 5 Master_Host: 172.16.4.212 6 Master_User: rpl_user 7 Master_Port: 3306 8 Connect_Retry: 60 9 Master_Log_File: mysql-bin.000003 10 Read_Master_Log_Pos: 321335 11 Relay_Log_File: slavedb01-relay-bin.000010 12 Relay_Log_Pos: 300281 13 Relay_Master_Log_File: mysql-bin.000003 14 Slave_IO_Running: Yes 15 Slave_SQL_Running: Yes 16 Replicate_Do_DB: 17 Replicate_Ignore_DB: 18 Replicate_Do_Table: 19 Replicate_Ignore_Table: 20 Replicate_Wild_Do_Table: 21 Replicate_Wild_Ignore_Table: 22 Last_Errno: 0 23 Last_Error: 24 Skip_Counter: 0 25 Exec_Master_Log_Pos: 320871 26 Relay_Log_Space: 301047 27 Until_Condition: None 28 Until_Log_File: 29 Until_Log_Pos: 0 30 Master_SSL_Allowed: No 31 Master_SSL_CA_File: 32 Master_SSL_CA_Path: 33 Master_SSL_Cert: 34 Master_SSL_Cipher: 35 Master_SSL_Key: 36 Seconds_Behind_Master: 12 37 Master_SSL_Verify_Server_Cert: No 38 Last_IO_Errno: 0 39 Last_IO_Error: 40 Last_SQL_Errno: 0 41 Last_SQL_Error: 42 Replicate_Ignore_Server_Ids: 43 Master_Server_Id: 212 44 Master_SSL_Crl: 45 Master_SSL_Crlpath: 46 Using_Gtid: No 47 Gtid_IO_Pos: 48 Replicate_Do_Domain_Ids: 49 Replicate_Ignore_Domain_Ids: 50 Parallel_Mode: conservative 51 1 row in set (0.00 sec) 52 53 ERROR: No query specified 54 55 MariaDB [(none)]>
通过查看复制状态可以得知已经延迟12秒。那么现在通过客户端连接Maxscale 4008端口
1 [root@maxscale ~]# mysql -usuper -p -h 172.16.4.101 -P4008 2 Enter password: 3 Welcome to the MariaDB monitor. Commands end with ; or \g. 4 Your MySQL connection id is 3493 5 Server version: 10.0.0 2.1.4-maxscale MariaDB Server 6 7 Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 8 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 10 11 MySQL [(none)]> select user,host,password from mysql.user; 12 +---------+------------------+-------------------------------------------+ 13 | user | host | password | 14 +---------+------------------+-------------------------------------------+ 15 | root | localhost | | 16 | root | slavedb01.ueb.cn | | 17 | root | 127.0.0.1 | | 18 | root | ::1 | | 19 | | localhost | | 20 | | slavedb01.ueb.cn | | 21 | super | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 22 | monitor | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 23 +---------+------------------+-------------------------------------------+ 24 8 rows in set (0.00 sec) 25 26 MySQL [(none)]>
在slavedb01(172.16.4.214)上查看General Log，可以得知复制延迟超过5秒的客户端仍旧会转发到slavedb01上。基于connect方式在maxscale2.1.4版本里，无法实现延迟检测功能。
1 [root@slavedb01 mysqllogs]# tail -f general.log 2 ......................省略N行....................... 3 170725 14:45:30 9 Query select user,host,password from mysql.user 4 170725 14:45:36 5 Query SELECT @@server_id, @@read_only 5 5 Query SHOW ALL SLAVES STATUS 6 5 Query SELECT master_timestamp FROM maxscale_schema.replication_heartbeat WHERE maxscale_id = 0 AND master_server_id = 212 7 ....................省略N行........................
6.1.3 模拟1台slave故障
在slavedb01(172.16.4.214)上执行stop slave io_thread或 stop slave sql_thread，模拟slavedb01复制故障
1 MariaDB [(none)]> stop slave io_thread; 2 Query OK, 0 rows affected (0.05 sec) 3 MariaDB [(none)]>
通过客户端连接maxscale 4008端口，并执行查询操作：
1 [root@maxscale ~]# mysql -usuper -p -h 172.16.4.101 -P4008 2 Enter password: 3 Welcome to the MariaDB monitor. Commands end with ; or \g. 4 Your MySQL connection id is 3493 5 Server version: 10.0.0 2.1.4-maxscale MariaDB Server 6 7 Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 8 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 10 11 MySQL [(none)]> select user,host,password from mysql.user; 12 +---------+------------------+-------------------------------------------+ 13 | user | host | password | 14 +---------+------------------+-------------------------------------------+ 15 | root | localhost | | 16 | root | slavedb01.ueb.cn | | 17 | root | 127.0.0.1 | | 18 | root | ::1 | | 19 | | localhost | | 20 | | slavedb01.ueb.cn | | 21 | super | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 22 | monitor | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 23 +---------+------------------+-------------------------------------------+ 24 8 rows in set (0.00 sec) 25 26 MySQL [(none)]>
分别在slavedb01和slavedb02上查看General Log，可以得知这次查询操作被转发到slavedb02(172.16.4.215)上
1 [root@slavedb02 mysqllogs]# tail -f general.log 2 170725 14:56:59 23 Connect super@172.16.4.101 as anonymous on 3 23 Query select user,host,password from mysql.user 4 170725 14:57:00 17 Query SELECT @@server_id, @@read_only 5 17 Query SHOW ALL SLAVES STATUS 6 17 Query SELECT master_timestamp FROM maxscale_schema.replication_heartbeat WHERE maxscale_id = 0 AND master_server_id = 212 7 7 Query BEGIN 8 .....................省略N行.....................
6.1.4 模拟全部的slave故障
在slavedb02(172.16.4.214)和slavedb02(172.16.4.215)上执行stop slave;
1 MariaDB [(none)]> stop salve;
通过客户端连接maxscale的4008端口，并执行查询操作
1 [root@maxscale ~]# mysql -usuper -p -h 172.16.4.101 -P4008 2 Enter password: 3 Welcome to the MariaDB monitor. Commands end with ; or \g. 4 Your MySQL connection id is 4534 5 Server version: 10.0.0 2.1.4-maxscale MariaDB Server 6 7 Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 8 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 10 11 MySQL [(none)]> select user,host,password from mysql.user; 12 +----------+-----------------+-------------------------------------------+ 13 | user | host | password | 14 +----------+-----------------+-------------------------------------------+ 15 | root | localhost | | 16 | root | masterdb.ueb.cn | | 17 | root | 127.0.0.1 | | 18 | root | ::1 | | 19 | | localhost | | 20 | | masterdb.ueb.cn | | 21 | rpl_user | 172.16.4.214 | *C674C5817CE4A2679CCFE1F603339AE0BEB5FD0A | 22 | rpl_user | 172.16.4.215 | *C674C5817CE4A2679CCFE1F603339AE0BEB5FD0A | 23 | super | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 24 | monitor | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 25 +----------+-----------------+-------------------------------------------+ 26 10 rows in set (0.00 sec) 27 28 MySQL [(none)]>
在masterdb(172.16.4.212)上查看General Log，可以得知所有的请求将只转发给masterdb(172.16.4.212)，slave上并不会有请求转发，实现了平滑故障转移。
1 [root@masterdb ~]# tail -f /data/mysqllogs/general.log 2 ...................省略N行................... 3 170725 15:15:22 32 Query SELECT @@server_id, @@read_only 4 32 Query SHOW ALL SLAVES STATUS 5 170725 15:15:30 37 Query select user,host,password from mysql.user 6 170725 15:15:32 32 Query SELECT @@server_id, @@read_only 7 32 Query SHOW ALL SLAVES STATUS 8 170725 15:15:42 32 Query SELECT @@server_id, @@read_only 9 32 Query SHOW ALL SLAVES STATUS 10 .......................省略N行......................
6.2 基于statement方式(SQL解析)的测试
测试之前，在masterdb(172.16.4.212)、slavedb01(172.16.4.214)、slavedb02(172.16.4.215)上打开General Log，命令：set global general_log=1;
6.2.1 读写分离测试
通过客户端连接Maxscale 4006端口
1 [root@maxscale ~]# mysql -usuper -p -h 172.16.4.101 -P4006 2 Enter password: 3 Welcome to the MariaDB monitor. Commands end with ; or \g. 4 Your MariaDB connection id is 4634 5 Server version: 5.5.37-MariaDB-RWsplit MariaDB Server 6 7 Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 8 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 10 11 MariaDB [(none)]>
执行以下写操作：
1 MariaDB [(none)]> CREATE DATABASE ueb_cmdb; 2 ERROR 2006 (HY000): MySQL server has gone away 3 No connection. Trying to reconnect... 4 Connection id: 4634 5 Current database: *** NONE *** 6 7 ` varchar(32) DEFAULT NULL, 8 `cpu_info` varchar(32) NOT NULL, 9 `mem_info` varchar(32) NOT NULL, 10 `disk_info` varchar(32) NOT NULL, 11 `status` int(11) NOT NULL, 12 `add_time` date NOT NULL, 13 PRIMARY KEY (`id`) 14 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;Query OK, 1 row affected (0.06 sec) 15 16 MariaDB [(none)]> USE ueb_cmdb; 17 Database changed 18 MariaDB [ueb_cmdb]> CREATE TABLE `cmdb_hostsinfo` ( 19 -> `id` int(11) NOT NULL AUTO_INCREMENT, 20 -> `host_id` int(11) NOT NULL, 21 -> `host_name` varchar(64) NOT NULL, 22 -> `ip_addr` varchar(64) NOT NULL, 23 -> `brand_name` varchar(10) NOT NULL, 24 -> `type` varchar(32) DEFAULT NULL, 25 -> `cpu_info` varchar(32) NOT NULL, 26 -> `mem_info` varchar(32) NOT NULL, 27 -> `disk_info` varchar(32) NOT NULL, 28 -> `status` int(11) NOT NULL, 29 -> `add_time` date NOT NULL, 30 -> PRIMARY KEY (`id`) 31 -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4; 32 Query OK, 0 rows affected (0.32 sec) 33 34 MariaDB [ueb_cmdb]> INSERT INTO `cmdb_hostsinfo` VALUES ('2', '2', 'slavedb02.ueboms.com', '172.16.2.236', 'lenovo', 'x3650 m5', 'Xeon(R) CPU E5-2620 v2', '128g', '1024g', '2', '2017-04-11'); 35 Query OK, 1 row affected (0.06 sec) 36 MariaDB [ueb_cmdb]>
通过General Log得知，MaxScale会把create、insert语句的请求转发到masterdb上
1 [root@masterdb ~]# tail -f /data/mysqllogs/general.log 2 ...............省略N行........................ 3 170725 17:00:51 46 Connect super@172.16.4.101 as anonymous on 4 46 Query CREATE DATABASE ueb_cmdb 5 46 Init DB ueb_cmdb 6 170725 17:00:55 40 Query SELECT @@server_id, @@read_only 7 40 Query SHOW ALL SLAVES STATUS 8 40 Query SELECT table_name FROM information_schema.tables WHERE table_schema = 'maxscale_schema' AND table_name = 'replication_heartbeat' 9 40 Query DELETE FROM maxscale_schema.replication_heartbeat WHERE master_timestamp < 1500800448 10 40 Query UPDATE maxscale_schema.replication_heartbeat SET master_timestamp = 1500973248 WHERE master_server_id = 212 AND maxscale_id = 0 11 170725 17:00:59 46 Query CREATE TABLE `cmdb_hostsinfo` ( 12 `id` int(11) NOT NULL AUTO_INCREMENT, 13 `host_id` int(11) NOT NULL, 14 `host_name` varchar(64) NOT NULL, 15 `ip_addr` varchar(64) NOT NULL, 16 `brand_name` varchar(10) NOT NULL, 17 `type` varchar(32) DEFAULT NULL, 18 `cpu_info` varchar(32) NOT NULL, 19 `mem_info` varchar(32) NOT NULL, 20 `disk_info` varchar(32) NOT NULL, 21 `status` int(11) NOT NULL, 22 `add_time` date NOT NULL, 23 PRIMARY KEY (`id`) 24 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 25 26 170725 17:29:04 52 Query INSERT INTO `cmdb_hostsinfo`('host_id','host_name','ip_addr','brand_name','type','cpu_info','mem_info','disk_info','status','add_time') values(2,'bbs.ueb.cn','172.16.4.201','lenovo','server','Intel Xeon CPU E5-2640v42.40GHz','DDR316008G','1000G',1,'2017-7-25') 27 .................省略N行............................
执行查询语句：select host_id,host_name,ip_addr,brand_name,cpu_info,mem_info from cmdb_hostsinfo;
1 MariaDB [ueb_cmdb]> select host_id,host_name,ip_addr,brand_name,cpu_info,mem_info from cmdb_hostsinfo; 2 +---------+----------------------+--------------+------------+------------------------+----------+ 3 | host_id | host_name | ip_addr | brand_name | cpu_info | mem_info | 4 +---------+----------------------+--------------+------------+------------------------+----------+ 5 | 2 | slavedb02.ueboms.com | 172.16.2.236 | lenovo | Xeon(R) CPU E5-2620 v2 | 128g | 6 +---------+----------------------+--------------+------------+------------------------+----------+ 7 1 row in set (0.00 sec) 8 9 MariaDB [ueb_cmdb]>
通过General Log得知，MaxScale会把select语句的请求转发到slavedb01(172.16.4.214)上
1 [root@slavedb01 mysqllogs]# tail -f general.log 2 ..............省略N行....................... 3 170725 17:55:42 44 Query select host_id,host_name,ip_addr,brand_name,cpu_info,mem_info from cmdb_hostsinfo 4 170725 17:55:51 27 Query SELECT @@server_id, @@read_only 5 27 Query SHOW ALL SLAVES STATUS 6 27 Query SELECT master_timestamp FROM maxscale_schema.replication_heartbeat WHERE maxscale_id = 0 AND master_server_id = 212 7 26 Query BEGIN 8 26 Query COMMIT 9 ...................省略N行...................
5.2.2 模拟slave延迟
在slavedb01(172.16.4.214)上执行锁表语句 flush tables with read lock；
1 MariaDB [(none)]> flush tables with read lock; 2 Query OK, 0 rows affected (0.00 sec) 3 4 MariaDB [(none)]> show slave status\G; 5 *************************** 1. row *************************** 6 Slave_IO_State: Waiting for master to send event 7 Master_Host: 172.16.4.212 8 Master_User: rpl_user 9 Master_Port: 3306 10 Connect_Retry: 60 11 Master_Log_File: mysql-bin.000003 12 Read_Master_Log_Pos: 628831 13 Relay_Log_File: slavedb01-relay-bin.000012 14 Relay_Log_Pos: 273929 15 Relay_Master_Log_File: mysql-bin.000003 16 Slave_IO_Running: Yes 17 Slave_SQL_Running: Yes 18 Replicate_Do_DB: 19 Replicate_Ignore_DB: 20 Replicate_Do_Table: 21 Replicate_Ignore_Table: 22 Replicate_Wild_Do_Table: 23 Replicate_Wild_Ignore_Table: 24 Last_Errno: 0 25 Last_Error: 26 Skip_Counter: 0 27 Exec_Master_Log_Pos: 628599 28 Relay_Log_Space: 291919 29 Until_Condition: None 30 Until_Log_File: 31 Until_Log_Pos: 0 32 Master_SSL_Allowed: No 33 Master_SSL_CA_File: 34 Master_SSL_CA_Path: 35 Master_SSL_Cert: 36 Master_SSL_Cipher: 37 Master_SSL_Key: 38 Seconds_Behind_Master: 6 39 Master_SSL_Verify_Server_Cert: No 40 Last_IO_Errno: 0 41 Last_IO_Error: 42 Last_SQL_Errno: 0 43 Last_SQL_Error: 44 Replicate_Ignore_Server_Ids: 45 Master_Server_Id: 212 46 Master_SSL_Crl: 47 Master_SSL_Crlpath: 48 Using_Gtid: No 49 Gtid_IO_Pos: 50 Replicate_Do_Domain_Ids: 51 Replicate_Ignore_Domain_Ids: 52 Parallel_Mode: conservative 53 1 row in set (0.00 sec) 54 55 ERROR: No query specified 56 57 MariaDB [(none)]>
通过客户端连接maxscale 4006端口，执行查询语句 select user,host,password from mysql.user;
1 [root@maxscale ~]# mysql -usuper -p -h 172.16.4.101 -P4006 2 Enter password: 3 Welcome to the MariaDB monitor. Commands end with ; or \g. 4 Your MariaDB connection id is 4634 5 Server version: 5.5.37-MariaDB-RWsplit MariaDB Server 6 7 Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 8 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 10 11 MariaDB [(none)]> select user,host,password from mysql.user; 12 +---------+------------------+-------------------------------------------+ 13 | user | host | password | 14 +---------+------------------+-------------------------------------------+ 15 | root | localhost | | 16 | root | slavedb02.ueb.cn | | 17 | root | 127.0.0.1 | | 18 | root | ::1 | | 19 | | localhost | | 20 | | slavedb02.ueb.cn | | 21 | super | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 22 | monitor | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 23 +---------+------------------+-------------------------------------------+ 24 8 rows in set (0.00 sec) 25 26 MariaDB [(none)]>
通过查看General Log可以得知，select请求被转发到没有延迟的slavedb02(172.16.4.215)上
1 [root@slavedb02 scripts]# tail -f /data/mysqllogs/general.log 2 ....................省略N行................... 3 70725 18:41:00 38 Query COMMIT 4 170725 18:41:04 56 Query select user,host,password from mysql.user 5 170725 18:41:09 39 Query SELECT @@server_id, @@read_only 6 39 Query SHOW ALL SLAVES STATUS 7 170725 18:41:10 38 Query BEGIN 8 39 Query SELECT master_timestamp FROM maxscale_schema.replication_heartbeat WHERE maxscale_id = 0 AND master_server_id = 212 9 38 Query COMMIT 10 ....................省略N行......................
5.2.3 模拟一台slave故障
在slavedb01(172.16.4.214)上执行 stop slave；模拟slave故障
1 MariaDB [(none)]> stop slave; 2 Query OK, 0 rows affected (0.04 sec) 3 4 MariaDB [(none)]>
通过客户端连接到MaxScale 4006端口上执行 select user,host,password from mysql.user;
1 [root@maxscale ~]# mysql -usuper -p -h 172.16.4.101 -P4006 2 Enter password: 3 Welcome to the MariaDB monitor. Commands end with ; or \g. 4 Your MariaDB connection id is 4634 5 Server version: 5.5.37-MariaDB-RWsplit MariaDB Server 6 7 Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 8 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 10 11 MariaDB [(none)]>select user,host,password from mysql.user; 12 ERROR 2006 (HY000): MySQL server has gone away 13 No connection. Trying to reconnect... 14 Connection id: 4641 15 Current database: *** NONE *** 16 17 +---------+------------------+-------------------------------------------+ 18 | user | host | password | 19 +---------+------------------+-------------------------------------------+ 20 | root | localhost | | 21 | root | slavedb02.ueb.cn | | 22 | root | 127.0.0.1 | | 23 | root | ::1 | | 24 | | localhost | | 25 | | slavedb02.ueb.cn | | 26 | super | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 27 | monitor | 172.16.4.101 | *A13D037357AEF8C9BAC95CB6BC39B4862A606D4B | 28 +---------+------------------+-------------------------------------------+ 29 8 rows in set (0.00 sec) 30 31 MariaDB [(none)]>
通过查看General Log可以得知，select查询请求不再被转发到slavedb01上，二是被转发到slavedb02(172.16.4.215)上
1 [root@slavedb02 scripts]# tail -f /data/mysqllogs/general.log 2 ................省略N行........................ 3 170727 10:06:20 57 Connect super@172.16.4.101 as anonymous on 4 57 Query select user,host,password from mysql.user 5 170727 10:06:22 39 Query SELECT @@server_id, @@read_only 6 39 Query SHOW ALL SLAVES STATUS 7 39 Query SELECT master_timestamp FROM maxscale_schema.replication_heartbeat WHERE maxscale_id = 0 AND master_server_id = 212 8 38 Query BEGIN 9 38 Query COMMIT 10 ................省略N..........................
6.2.4 模拟全部slave故障
在slavedb01(172.16.4.214)和slavedb02(172.16.4.215)上执行stop slave；模拟全部slave故障
1 MariaDB [(none)]> stop slave; 2 Query OK, 0 rows affected (0.02 sec) 3 4 MariaDB [(none)]>
通过查看General Log可以得知，select查询请求不再被转发到任何slave上，而是被转发到masterdb(172.16.4.212)上
1 [root@masterdb ~]# tail -f /data/mysqllogs/general.log 2 .................省略N行...................... 3 170727 10:16:48 60 Connect super@172.16.4.101 as anonymous on 4 60 Query select user,host,password from mysql.user 5 170727 10:16:57 57 Query SELECT @@server_id, @@read_only 6 .................省略N行...................
七、PHP/JAVA程序接入注意事项：
在以下情况下，select查询将会在master上执行：
- 当事务里有SQL语句(begin;select;commit)时。
- 在java预编译语句prepared statement中执行SQL时。
- 执行存储过程或者函数时。
- 如果在某个service中添加use_sql_variables_in=master参数，当执行带有变量的语句时会转发到master上。
附、配置文件参数详解
MaxScale配置文件内容一共分为五个部分，每个部分使用[]标记，典型的ini文件格式：
[maxscale]
在名为[MaxScale]的部分中，全局设置允许对MariaDB MaxScale进行整体调优的各种参数。
[Service]
Service表示MariaDB MaxScale为客户提供的数据库服务。一般来说，服务由一组后端数据库服务器和一种路由算法组成，它决定了MariaDB MaxScale如何决定发送语句或路由到这些后端服务器的连接。
[Server]
Server段用于定义可以提供服务的后端数据库服务器。server可以是MariaDB MaxScale中的一个或多个服务的成员。服务器由服务器名标识，服务器名是配置文件中的部分名称。服务器有一个服务器的类型参数，加上地址端口和协议参数。
[Listener]
侦听器定义了用于侦听服务连接的端口和协议对。服务可能有多个与之相关的侦听器，以支持多个协议或多个端口。与配置的其他元素一样，section名称是侦听器名称，可以自由选择。类型参数用于标识作为侦听器定义的部分。
[Monitor]
Monitor模块被MariaDB MaxScale用于内部监控后台数据库的状态，以便为这些服务器设置服务器标志。然后，路由器模块使用这些标志来确定特定的服务器是否适合用于特定查询分类的路由连接。这些监视器运行在MariaDB MaxScale的单独线程中，不影响MariaDB MaxScale的路由性能。
下面就对各个模块进行参数的解释：
全局模块[maxscale]
1 [maxscale] 2 threads=1 3 auth_connect_timeout=10 4 auth_read_timeout =10 5 auth_write_timeout=10 6 ms_timestamp=<0|1> #启用或禁用日志文件中的高精度时间戳。这样可以为所有的日志文件时间戳增加毫秒的精度。 7 skip_permission_checks =<true|false> #跳过服务和监视用户权限检查。当你知道权限是可以的，你想加快启动进程时，这是很有用的。这个参数需要一个布尔值，默认情况下是禁用的。建议不要禁用权限检查，这样当maxscale启动时就会检测到任何缺失的特权。如果您正在经历一个缓慢启动的MaxScale，因为在检查权限时大量的连接超时，禁用权限检查可以加快启动进程。 8 syslog=<0|1> #启用或禁用对syslog的消息记录。 9 maxlog=<0|1> #允许禁用向MariaDB MaxScale的日志文件记录消息。默认的日志记录是启用的。 10 log_to_shm=<0|1> #启用或禁用maxscale.log日志文件的共享内存。如果启用，那么将在/dev/ shm下创建实际的日志文件，并在MaxScale日志目录中创建一个指向该文件的符号链接。如果启用log_info和/或log_debug，日志记录到共享内存可能是合适的，因为日志记录到一个常规文件可能会导致性能下降，因为记录的数据量是这样的。但是，由于共享内存是一种稀缺资源，所以对共享内存的日志记录应该只是临时的，而不是定期的。由于MariaDB MaxScale可以记录到文件和syslog，因此提供最大灵活性的方法是启用syslog和log_to_shm，以及禁用maxlog。这样，消息通常会登录到syslog，但是如果有什么要调查的话，则可以从maxadmin启用log_info和maxlog，在这种情况下，信息消息将被记录到maxscale。共享内存中的日志文件。 11 log_warning=<0|1> #启用或禁用syslog优先级警告的日志记录。默认情况下启用此优先级的消息。 12 log_notice=<0|1> #启用或禁用syslog优先级通知的日志记录。这个优先级的消息提供了关于MariaDB MaxScale功能的信息，默认情况下是启用的。 13 log_info=<0|1> #启用或禁用syslog优先级信息的日志记录。这些消息提供了关于MariaDB MaxScale内部工作的详细信息，不应该由于它们的频率而启用，除非有特定的原因。例如，从这些信息中可以明显看出。为什么一个特定的查询被路由到主服务器而不是一个奴隶。这些信息在默认情况下是禁用的。 14 log_augmentation=<0|1> #启用或禁用消息的增强。如果启用了这个操作，那么每个已登录的消息将附加到日志记录的函数的名称。这主要用于开发目的，因此在默认情况下是禁用的。 15 log_throttling=X,Y,Z #如果持续存在错误的原因，可能会一次又一次地记录一个特定的错误(或警告)。为了防止日志被洪水淹没，可以指定一个特定的错误可能在一个时间段内记录多少次，在这个错误的日志记录被抑制一段时间之前。默认值是10、1000、10000，这意味着如果同样的错误在1秒内被记录10次，那么在接下来的10秒中，记录错误的日志就会被抑制。要禁用log throttling，添加一个带有空值的条目:log_throttling= 16 logdir=path #设置日志文件存储的目录。该文件夹需要由运行MariaDB MaxScale的用户可读和写。 17 datadir=path #设置MariaDB MaxScale使用的数据文件存储的目录。模块可以写入这个目录，例如binlogrouter使用这个文件夹作为存储二进制日志的默认位置。 18 libdir=/home/user/lib64/ #设置MariaDB MaxScale寻找模块的目录。库目录是MariaDB MaxScale在搜索模块时使用的唯一目录。如果您有MariaDB MaxScale的自定义模块，请确保将它们放在这个文件夹中。 19 cachedir=/tmp/maxscale_cache/ #配置MariaDB MaxScale用于存储缓存数据的目录。缓存数据的一个示例是从后端服务器获取的身份验证数据。如果连接到后端服务器是不可能的，那么MariaDB MaxScale存储这些数据。 20 piddir=/tmp/maxscale_cache/ #为MariaDB MaxScale配置PID文件的目录。这个文件包含运行的MariaDB MaxScale进程的进程ID。 21 execdir=/usr/local/bin/ #配置可执行文件所在的目录。启动的所有内部流程将使用此目录查找可执行文件。 22 connector_plugindir=/usr/lib/plugin/ #MariaDB connector-c插件目录的位置。在MaxScale中使用的MariaDB connector - c可以使用此目录加载身份验证插件。插件的版本必须与MaxScale所构建的连接器版本兼容。 23 persistdir=/var/lib/maxscale/maxscale.cnf.d/ #配置保存持久配置的目录。当通过MaxAdmin创建新服务器时，它将存储在这个目录中。不要使用或修改该目录的内容，使用/etc/maxscale.cnf.d /代替。 24 module_configdir=/var/lib/maxscale/ #配置存储模块配置的目录。路径参数解析相对于这个目录。该目录应该用于存储特定于模块的配置，例如dbfwfilter规则文件。任何非绝对路径的配置参数都将被解释为相对路径。相对路径使用模块配置目录作为工作目录。例如，配置参数file= my_file.txt将被解释为/etc/maxscale.cnf.d/my_file.txt，而file=/home/user/my_file.txt将被解释为/home/user/my_file.txt。
Server模块[Server]
1 [server1] 2 type=server 3 address=IP 4 port=3306 5 protocol=MySQLBackend
Service模块[Service]
1 [Read-Write Service] 2 type=service 3 router=readwritesplit | readconnroute #服务的路由器参数定义了路由器模块的名称，该模块将用于实现MariaDB MaxScale客户机和后端数据库之间的路由算法。另外，路由器也可以通过一个逗号分隔的选项列表来控制路由算法的行为。控制路由选择的两个参数是router和router_options。路由器选项是特定于特定路由器的，并被用来修改路由器的行为。读取的连接路由器可以通过master、slave或synced的选项，例如配置一个服务来使用此路由器，并限制服务器对处于从属状态的服务器的选择。 4 router_options=slave | master,slave 5 servers=server1 #服务定义中的servers参数提供了包含服务的后端服务器的逗号分隔列表。服务器名是使用服务器类型参数的块的名称部分使用的名称(见下文)。 6 user=myuser 7 passwd=mypwd 8 filters=counter | QLA 9 enable_root_user=[0|1] #此参数控制root连接到MariaDB MaxScale的能力，并通过MariaDB MaxScale将其转到后端服务器。默认值为0，禁用root连接到MariaDB MaxScale。 10 localhost_match_wildcard_host=[0|1] #该参数使“127.0.0.1”(localhost)与“%”通配符匹配，用于MySQL协议验证。默认值为0，因此为了验证与MariaDB MaxScale正在运行的同一机器的连接，MySQL用户表中需要一个显式user@localhost条目。 11 version_string=5.5.37-MariaDB-RWsplit #这个参数设置了一个自定义的版本字符串，它是由MariaDB MaxScale向客户端发送的。 12 connection_timeout=300 #connection_timeout参数用于将会话与已经闲置太久的MariaDB MaxScale断开连接。默认情况下，会话超时是禁用的。要启用它们，请在服务的配置部分中以秒为单位定义超时。 13 max_connections=100 #最大同时连接MaxScale应该允许该服务。如果参数为零或省略，则没有限制。在到达极限后，任何试图建立更多连接的尝试都会导致返回“太多的连接”错误。 14 log_auth_warnings=[0|1] #启用或禁用认证失败和警告的日志记录。该参数采用布尔值。MariaDB MaxScale通常会抑制关于失败身份验证的警告消息。启用此选项将把这些消息记录到消息日志中，并详细介绍谁尝试连接到MariaDB MaxScale和从何处连接。 15 auth_all_servers=[0|1] #此参数控制在从后端服务器加载用户时仅使用单个服务器或所有服务器。这需要一个布尔值，当启用时，将在所有服务器上创建所有用户和授予的联合。 16 strip_db_esc=[0|1] #当从后端服务器加载用户时，strip_db_esc参数从数据库名称中删除字符。该参数采用布尔值，当启用时，将从数据库名称中删除所有反斜杠(\)字符。自MaxScale 2.0.1以来，该参数的默认值为true。在以前的版本中，默认值为false。一些可视化数据库管理工具会自动地避开一些字符，这可能会导致当MariaDB MaxScale试图对用户进行身份验证时发生冲突。 17 retry_on_failure=[0|1] # retry_on_failure参数控制MariaDB MaxScale是否尝试重新启动失败的服务并接受布尔值。此功能默认启用，以防止由于网络中断导致服务启动失败而导致永久禁用服务。如果在启动MariaDB MaxScale时不能启动服务，禁用重新启动失败的服务将使它们永久禁用。 18 max_slave_connections=100%
