使用 MaxScale实现读写分离-oracle狂热分子-ChinaUnix博客

使用 MaxScale实现读写分离

MaxScale是maridb开发的一个mysql数据中间件，相关的功能和特性介绍，可以自行到官网查看相关文档
作者在此不再重复，本文的目标是通过maxscal来实现读写分离的功能.

假设:
master为172.28.10.150
slave为172.28.10.145
mysql的版本是5.6.14

安装
[root@c12 soft]# rpm -ivh configure-maxscale-repo-0.1.2.rpm
Preparing... ########################################### [100%]
1:configure-maxscale-repo########################################### [100%]

[root@c12 soft]# yum install maxscale

拷贝配置文件
[root@c12 etc]# pwd
/usr/local/mariadb-maxscale/etc
[root@c12 etc]# cp MaxScale_template.cnf maxscale.cnf
[root@c12 etc]#

定义MAXSCALE_HOME环境变量，或是加到/etc/profile文件中

[root@c12 etc]# export MAXSCALE_HOME=/usr/local/mariadb-maxscale

编辑配置文件maxscale.cnf
内容如下

定义读写路由器
[RW Split Router]
type=service
router=readwritesplit
servers=server2,server1
user=root
passwd=50514A05CE2C0909BA630B29A29D620D
enable_root_user=1
#use_sql_variables_in=
#max_slave_connections=100%
#max_slave_replication_lag=21
#router_options=slave_selection_criteria=
#filters=fetch|qla

为该路由器定义一个listener

[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=4007
#socket=/tmp/rwsplit.sock

定义服务器成员

[server1]
type=server
address=172.28.10.145
port=3307
protocol=MySQLBackend

[server2]
type=server
address=172.28.10.150
port=3306
protocol=MySQLBackend

password是一个加密的密文，maxscal使用该用户连接数据库

生成密码文件，再加密
[root@c12 bin]# /usr/local/mariadb-maxscale/bin/maxkeys /usr/local/mariadb-maxscale/etc/.secrets

[root@c12 bin]# /usr/local/mariadb-maxscale/bin/maxpasswd root
50514A05CE2C0909BA630B29A29D620D

启动maxscale,并以后台方式运行,查看端口是否正常打开

[root@c12 bin]# /usr/local/mariadb-maxscale/bin/maxscale --config=/usr/local/mariadb-maxscale/etc/maxscale.cnf
[root@c12 etc]# netstat -nltp | grep max
tcp        0      0 0.0.0.0:4007                0.0.0.0:*                   LISTEN      15153/maxscale
tcp        0      0 0.0.0.0:6603                0.0.0.0:*                   LISTEN      15153/maxscale
tcp        0      0 0.0.0.0:4442                0.0.0.0:*                   LISTEN      15153/maxscale

使用管理工具连接，设置服务器状态

[root@c12 bin]# /usr/local/mariadb-maxscale/bin/maxadmin --user=admin --password=mariadb --host=127.0.0.1

手动设定手服务器的状态

MaxScale> set server server1 slave
MaxScale> set server server2 master
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 172.28.10.145   | 3307 |           0 | Slave, Running
server2            | 172.28.10.150   | 3306 |           0 | Master, Running
-------------------+-----------------+-------+-------------+--------------------

通过一个并发脚本测试，可以发现读全部路由到从库，而写被路由到主库上了.

两条测试SQL

$arra[0]="select count(*) from tt";
$arra[1]="update tt set c=c+1 where a>10";

从库全部是select操作.
slave>show processlist;
+----+-------------+------------------+--------+---------+------+-----------------------------------------------------------------------------+-------------------------+-----------+---------------+
| Id | User        | Host             | db     | Command | Time | State                                                                       | Info                    | Rows_sent | Rows_examined |
+----+-------------+------------------+--------+---------+------+-----------------------------------------------------------------------------+-------------------------+-----------+---------------+
| 1 | system user |                  | NULL   | Connect | 381 | Waiting for master to send event                                            | NULL                    |         0 |             0 |
| 2 | system user |                  | NULL   | Connect |    0 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL                    |         0 |             0 |
| 12 | root        | localhost        | NULL   | Sleep   | 267 |                                                                             | NULL                    |         0 |             0 |
| 20 | root        | c12.fb.com:45015 | sbtest | Query   |    0 | Sending data                                                                | select count(*) from tt |         0 |             0 |
| 21 | root        | c12.fb.com:45018 | sbtest | Query   |    1 | Sending data                                                                | select count(*) from tt |         0 |             0 |
| 22 | root        | c12.fb.com:45020 | sbtest | Query   |    1 | Sending data                                                                | select count(*) from tt |         0 |             0 |
| 23 | root        | c12.fb.com:45022 | sbtest | Query   |    1 | Sending data                                                                | select count(*) from tt |         0 |             0 |
| 24 | root        | c12.fb.com:45024 | sbtest | Sleep   |    2 |                                                                             | NULL                    |         1 |      10000000 |
| 25 | root        | c12.fb.com:45026 | sbtest | Query   |    0 | Sending data                                                                | select count(*) from tt |         0 |             0 |
| 26 | root        | localhost        | NULL   | Query   |    0 | init                                                                        | show processlist        |         0 |             0 |
+----+-------------+------------------+--------+---------+------+-----------------------------------------------------------------------------+-------------------------+-----------+---------------+

通过配置MaxScal成功的实了MySQL的读写分离.