MaxScale读写分离
1.简介
maxscale是mariadb公司开发的一套数据库中间件，可以很方便的实现读写分离方案；并且提供了读写分离的负载均衡和高可用性保障。另外maxscale对于前端应用而言是透明的，我们可以很方便的将应用迁移到maxscale中实现读写分离方案，来分担主库的压力。maxscale也提供了sql语句的解析过滤功能。这里我们主要讲解maxscale的安装、配置以及注意事项。
2.安装环境
CentOS 6.5 x86_64：10.10.214.18 MaxScale
CentOS 6.5 x86_64：10.10.214.88 Master MySQL-5.6.19
CentOS 6.5 x86_64：10.10.214.89 Slave MySQL-5.6.19
CentOS 6.5 x86_64：10.10.214.90 Slave MySQL-5.6.19
配置好主从
在开始配置之前，需要在 master和slave中为 MaxScale 创建两个用户，用于监控模块和路由模块。
创建监控用户
mysql> create user scalemon@'%' identified by "123456";
mysql> grant replication slave, replication client on *.* to scalemon@'%';
mysql> grant select on test.* to scaleroute@'%';
mysql> grant select on mysql.* to scaleroute@'%';
创建路由用户
mysql> create user scaleroute@'%' identified by "123456";
mysql> grant select on mysql.* to scaleroute@'%';
flush privileges;
3.MaxScale安装、配置和启动
下载rpm包maxscale-2.0.1-2.centos.6.x86_64.rpm
rpm -ivh maxscale-2.0.1-2.centos.6.x86_64.rpm
cat /etc/maxscale.cnf
[maxscale] threads=1 log_info=1 logdir=/tmp/ [server1] type=server address=10.10.214.88 port=3306 protocol=MySQLBackend serv_weight=1 [server2] type=server address=10.10.214.89 port=3306 protocol=MySQLBackend serv_weight=3 [server3] type=server address=10.10.214.90 port=3306 protocol=MySQLBackend serv_weight=3 [MySQL Monitor] type=monitor module=mysqlmon servers=server1,server2,server3 user=scalemon passwd=123456 monitor_interval=10000 #默认每隔10秒执行监控检查 [Read-Write Service] type=service router=readwritesplit servers=server1,server2,server3 user=scaleroute passwd=123456 max_slave_connections=100% [Read Connection Router] type=service router=readconnroute router_options=slave servers=server1,server2,server3,server3 user=scalemon passwd=123456 [MaxAdmin Service] type=service router=cli [Read-Write Listener] type=listener service=Read-Write Service protocol=MySQLClient port=4006 #读/写分离的端口，应用连接这个端口 [Read Connection Listener] type=listener service=Read Connection Router protocol=MySQLClient port=4008 #slave负载均衡的端口，应用连接这个端口 [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled socket=default
启动MaxScale
/etc/init.d/maxscale restart
查看具体的启动日志，有报错可以根据日志调试
tail -f /tmp/maxscale1.log
读写分离验证
mysql -h10.10.214.18 -P4006 -uscalemon -p123456 test
mysql> insert into t1 values(1,'aa');
Query OK, 1 row affected (0.12 sec)
mysql>
mysql> insert into t1 values(2,'bb');
Query OK, 1 row affected (0.15 sec)
mysql> insert into t1 values(3,'cc');
Query OK, 1 row affected (0.12 sec)
可以看到写的全部落到了88上
mysql> select count(*) from t1;
可以看到读全部落到了89上
负载均衡验证：
mysql -h10.10.214.18 -P4008 -uscalemon -p123456 test
mysql> select count(*) from t1;
可以看到读平均落到了89和90上
maxadmin -S /tmp/maxadmin.sock
MaxScale> list services
Services.
--------------------------+----------------------+--------+---------------
Service Name | Router Module | #Users | Total Sessions
--------------------------+----------------------+--------+---------------
Read-Write Service | readwritesplit | 1 | 4
Read Connection Router | readconnroute | 2 | 7
MaxAdmin Service | cli | 2 | 2
--------------------------+----------------------+--------+---------------
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 10.10.214.88 | 3306 | 0 | Master, Running
server2 | 10.10.214.89 | 3306 | 0 | Slave, Running
server3 | 10.10.214.90 | 3306 | 1 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
