全部博文（389）
分类： Mysql/postgreSQL
2015-05-25 00:17:39
使用 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";
master>show processlist;
+------+------+-----------------------------+--------------------+-------------+-------+-----------------------------------------------------------------------+--------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+------+------+-----------------------------+--------------------+-------------+-------+-----------------------------------------------------------------------+--------------------------------+-----------+---------------+
| 316 | root | 172.28.12.23:51839 | information_schema | Sleep | 22236 | | NULL | 9 | 9 |
| 951 | root | localhost | sbtest | Query | 0 | init | show processlist | 0 | 0 |
| 1074 | root | localhost.localdomain:58644 | NULL | Binlog Dump | 533 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0 | 0 |
| 1081 | root | c12.fb.com:64791 | sbtest | Query | 2 | updating | update tt set c=c+1 where a>10 | 0 | 0 |
| 1082 | root | c12.fb.com:64792 | sbtest | Query | 2 | updating | update tt set c=c+1 where a>10 | 0 | 418402 |
| 1083 | root | c12.fb.com:64794 | sbtest | Query | 2 | updating | update tt set c=c+1 where a>10 | 0 | 0 |
| 1084 | root | c12.fb.com:64796 | sbtest | Query | 2 | updating | update tt set c=c+1 where a>10 | 0 | 0 |
| 1085 | root | c12.fb.com:64798 | sbtest | Query | 1 | updating | update tt set c=c+1 where a>10 | 0 | 0 |
| 1086 | root | c12.fb.com:64800 | sbtest | Query | 1 | updating | update tt set c=c+1 where a>10 | 0 | 0 |
+------+------+-----------------------------+--------------------+-------------+-------+-----------------------------------------------------------------------+--------------------------------+-----------+---------------+
9 rows in set (0.00 sec)
从库全部是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的读写分离.
