Maxscale是Mariadb公司开发的一套数据库中间件，可以很方便的实现读写分离方案；并且提供了读写分离的负载均衡和高可用性保障。另外maxscale对于前端应用而言是透明的，我们可以很方便的将应用迁移到maxscale中实现读写分离方案，来分担主库的压力。maxscale也提供了sql语句的解析过滤功能。
这里我们主要讲解maxscale的安装、配置以及注意事项。
目录
Mysql读写分离-Maxscale
Maxscale特性：
1.带权重的读写分离(负载均衡)
2.SQL防火墙
3.多种路由策略(Connection based， Statement based，Schema based)
4.自动检测MySQL master Failover (配合MHA或者MRM)
5.检测主从延时
6.多租户sharding架构
| 角色 |
系统 |
服务 |
IP地址 |
| master |
centos7 |
mysql5.7 |
192.168.100.10 |
| slave |
centos7 |
mysql5.7 |
192.168.100.20 |
| proxy |
centos7 |
Maxscale |
192.168.100.30 |
搭建主从
1.安装mysql5.7并配置主从
请参考Mysql主从复制
2.主从创建proxy用户
在主库操作即可，从库自动同步
1
2
|
> grant all on *.* to 'proxy'@'%' identified by '123456.Com';
> flush privilegse;
|
安装Maxscale
3.下载rpm安装包
1
|
$ wget https://downloads.mariadb.com/MaxScale/centos/7/x86_64/maxscale-2.3.7-1.centos.7.x86_64.rpm
|
4.安装rpm安装包
1
|
$ rpm -ivh maxscale-2.3.7-1.centos.7.x86_64.rpm
|
配置Maxsacle
5.创建密钥
1
|
$ maxkeys /var/lib/maxscale/
|
6.加密密码
1
2
|
$ maxpasswd /var/lib/maxscale 123456.Com
7B31670430456C6C186113E5EEAE5F15
|
7.修改配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
|
$ vim /etc/maxscale.cnf
[maxscale]
#开启线程个数，默认为1.设置为auto会同cpu核数相同
threads=auto
#将日志写入到maxscale的日志文件中
maxlog=1
#记录info
log_info=1
[server1]
type=server
address=192.168.100.10
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=192.168.100.20
port=3306
protocol=MariaDBBackend
[server3]
type=server
address=192.168.100.30
port=3306
protocol=MariaDBBackend
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=proxy
password=7B31670430456C6C186113E5EEAE5F15
#监控心跳为5秒
monitor_interval=5000
#监控主从复制延迟
#detect_replication_lag=true
#控制maxscale运行的最大延迟
#max_slave_replication_lag
#当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点
detect_stale_master=true
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=proxy
password=7B31670430456C6C186113E5EEAE5F15
max_slave_connections=100%
#sql语句中的存在变量只指向master中执行
use_sql_variables_in=master
#允许root用户登录执行
enable_root_user=1
#master节点也可以转发读请求
#master_accept_reads=true
[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
port=6603
|
8.启动Maxscale
9.登录管理接口
1
2
3
4
5
6
7
8
9
10
|
$ maxadmin --user=admin --password=mariadb
> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.100.10 | 3306 | 1 | Master, Running
server2 | 192.168.100.20 | 3306 | 1 | Slave, Running
server3 | 192.168.100.30 | 3306 | 1 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
|
##验证读写分离
10.登录工作端口
查询两次student表，然后查看maxscale日志
1
2
3
4
5
6
7
|
$ tail -f /var/log/maxscale/maxscale.log
2018-08-02 15:54:37 info : (5) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 31, type: QUERY_TYPE_READ, stmt: select * from test.student
2019-08-02 15:54:37 info : (5) Route query to slave: server2 [192.168.100.20]:3306 <
2019-08-02 15:54:37 info : (5) Reply complete, last reply from server3
2019-08-02 15:54:37 info : (5) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 31, type: QUERY_TYPE_READ, stmt: select * from test.student
2019-08-02 15:54:37 info : (5) Route query to slave: server3 [192.168.100.30]:3306 <
2019-08-02 15:54:37 info : (5) Reply complete, last reply from server3
|
添加数据，再次查看
1
2
3
4
5
6
|
$ tail -f /var/log/maxscale/maxscale.log
2018-08-02 15:58:16 info : (5) Route query to master: server1 [192.168.100.10]:3306 <
2018-08-02 15:58:16 info : (5) Reply complete, last reply from server1
2018-08-02 15:58:28 info : (5) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 78, type: QUERY_TYPE_WRITE, stmt: insert into test.student values(3,'王五',20,'男'),(4,'赵六','21','男')
2018-08-02 15:58:28 info : (5) Route query to master: server1 [192.168.100.10]:3306 <
2018-08-02 15:58:28 info : (5) Reply complete, last reply from server1
|
读写分离成功
