读写分离

使用maxscale实现读写分离

环境

[root@muban mnt]# cat /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 
[root@muban mnt]# uname -a
Linux muban 3.10.0-862.el7.x86_64 #1 SMP Fri Apr 20 16:44:24 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

主机	IP	服务
server1	192.168.40.141	master
server2	192.168.40.142	slave1
server3	192.168.40.143	slave2
maxscale	192.168.40.133	maxscale

前提：已完成主从复制搭建
主从使用账户为：slave，与本次读写分离无关

安装maxscale

下载地址：https://downloads.mariadb.com/MaxScale/2.0.4/centos/7/x86_64/maxscale-2.0.4-1.centos.7.x86_64.rpm
yum install -y libaio libaio-devel
rpm -ivh https://downloads.mariadb.com/MaxScale/2.0.4/centos/7/x86_64/maxscale-2.0.4-1.centos.7.x86_64.rpm

配置用户

在主库中创建一个maxscale用于监控的账号
创建监控用户：

grant replication slave, replication client on *.* to scalemon@'%' identified by '123456';

创建路由用户：

grant select on mysql.* to maxscale@'%' identified by '123456';

修改配置文件

cat /etc/maxscale.cnf |egrep -v "^#"

[maxscale]
threads=1  #线程数，一般与cpu核数相同 或者直接写成threads=auto


[server1]
type=server
address=192.168.40.141 #master主IP
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.40.142 #slave1从IP
port=3306
protocol=MySQLBackend

[server3]
type=server
address=192.168.40.143 #slave2从IP
port=3306
protocol=MySQLBackend

[MySQL Monitor] #监控模块
type=monitor
module=mysqlmon
servers=server1,server2,server3 #需要监控的库
user=scalemon #监控用户
passwd=123456 #监控密码
monitor_interval=10000 #监控时间间隔，单位毫秒



[Read-Only Service] #读负载模块
type=service
router=readconnroute
servers=server2,server3 #负责读功能的库
user=maxscale #路由账号
passwd=123456 #路由账号密码
router_options=slave


[Read-Write Service] #负责写服务的模块
type=service
router=readwritesplit
servers=server1 #负责写功能的库
user=maxscale #路由账号
passwd=123456 #路由账号密码
max_slave_connections=100% # 默认所有的从服务器都提供读服务


[MaxAdmin Service]
type=service
router=cli



[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
#socket=default #注释掉
port=6603 #添加一个maxadmin端口

启动服务

systemctl start maxscale

默认用户名与密码：admin mariadb

maxadmin -uadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.40.141  |  3306 |           0 | Master, Running
server2            | 192.168.40.142  |  3306 |           0 | Slave, Running
server3            | 192.168.40.143  |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

测试读写分离是否成功

自行测试

使用mycat实现读写分离

待实现

使用mysql-proxy实现读写分离

安装mysql-proxy

实现读写分离是有lua脚本实现的，现在新版本mysql-proxy里面已经集成，无需再安装
指定下载：mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz
yum也可直接下载

tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit /usr/local/mysql-proxy

创建配置文件

准备好目录与lua管理脚本和文件

cd /usr/local/mysql-proxy
mkdir lua #创建脚本存放目录
mkdir logs #创建日志目录
cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本

创建配置文件

vim /etc/mysql-proxy.conf   #创建配置文件
[mysql-proxy]
user=root #运行mysql-proxy用户
admin-username=test #主从mysql共有的用户
admin-password=123456 #用户的密码
proxy-address=192.168.40.133:4040 #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.40.142,192.168.40.143 #指定后端从slave读取数据
proxy-backend-addresses=192.168.40.141 #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true    #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启

chmod 660 /etc/mysql-proxy.conf
修改读写分离配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.lua
.....
if not proxy.global.config.rwsplit then
 proxy.global.config.rwsplit = {
  min_idle_connections = 1, #默认超过4个连接数时，才开始读写分离，改为1
  max_idle_connections = 1, #默认8，改为1
  is_debug = false
 }
.....

启动服务

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.conf

关闭mysql-proxy使用 killall -9 mysql-proxy

检查端口

[root@muban mysql-proxy]# netstat -lntup |grep 4040
tcp        0      0 192.168.40.133:4040     0.0.0.0:*               LISTEN      1667/mysql-proxy 

创建mysql-proxy登录用户：
GRANT ALL ON . TO 'test'@'%' IDENTIFIED BY '123456';

测试读写分离

使用客户端连接mysql-proxy
mysql -utest -p123456 -h192.168.40.133 -P4040
测试写

MariaDB [(none)]> create database master;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use master
Database changed
MariaDB [master]> create table test (id int);
Query OK, 0 rows affected (0.01 sec)

MariaDB [master]> insert into test value (1);
Query OK, 1 row affected (0.01 sec)

在从服务器中查看

Database changed
MariaDB [master]> select * from test;
+------+
| id   |
+------+
|    1 |
+------+

测试读
分别查询主从服务器上查询语句条数
master:15条

MariaDB [(none)]> show global status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

slave1:6条

MariaDB [master]> show global status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 6     |
+---------------+-------+
1 row in set (0.00 sec)

slave2:3条

MariaDB [(none)]> show global status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 3     |
+---------------+-------+
1 row in set (0.00 sec)

在mysql-proxy客户端执行查询
MariaDB [master]> select * from test;
再分别查看主从服务器上查询语句条数

尝试好几遍，都是在master上执行读操作，很奇怪，待排查

使用ameoba实现读写分离

待实现