技术分享 | MaxScale 实现 MySQL读写分离

部署拓扑

部署后端 MySQL 一主两从半同步复制，部署步骤略，状态如下：

## 一主两从
mysql> show slave hosts;
+-----------+---------------+------+-----------+--------------------------------------+
| Server_id | Host          | Port | Master_id | Slave_UUID                           |
+-----------+---------------+------+-----------+--------------------------------------+
| 737716692 | 10.186.61.192 | 3306 | 622227692 | d121bf0f-1922-11ed-86d9-02000aba3dc0 |
| 534997148 | 10.186.63.64  | 3306 | 622227692 | bb3d53a9-1940-11ed-a059-02000aba3f40 |
+-----------+---------------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
## 半同步复制
mysql> show global status like 'Rpl_semi_sync_master_clients';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_master_clients | 2     |
+------------------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE USER 'maxscale'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on mysql.* to maxscale@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
Query OK, 0 rows affected (0.00 sec)

创建监控用户并授权

mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION CLIENT on *.* to 'monitor'@'%';
Query OK, 0 rows affected (0.00 sec)

安装 MaxScale

## 安装依赖包
yum -y install libcurl libaio openssl gnutls libatomic

## 下载MaxScale安装包
wget https://dlm.mariadb.com/1864578/MaxScale/6.1.4/bintar/rhel/7/x86_64/maxscale-6.1.4.rhel.7.tar.gz

## 添加MaxScale运行用户
groupadd maxscale
useradd -g maxscale maxscale

## 解压安装包并授权
mkdir data/maxscale
tar xf maxscale-6.1.4.rhel.7.tar.gz -C data/maxscale --strip-components=1
chown maxscale.maxscale data/maxscale/ -R

## 配置环境变量
echo "export PATH=$PATH:/data/maxscale/bin/" >> etc/profile
source etc/profile

# maxscale --version
MaxScale 6.1.4

对前面创建的数据库的 maxscale 用户和监控用户的密码加密

## 生成密钥
# data/maxscale/bin/maxkeys data/maxscale/var/lib/maxscale
Permissions of '/data/maxscale/var/lib/maxscale/.secrets' set to owner:read.
Ownership of '/data/maxscale/var/lib/maxscale/.secrets' given to maxscale.
 
## 使用密钥对明文密码加密
# data/maxscale/bin/maxpasswd data/maxscale/var/lib/maxscale/ 123
F270B97B3D4D37BC619EB02304A02C1456F41B05CF2D7E5F37BA48CC7646C2E4

MaxScale 也支持 Web GUI ，如果要开启 Web GUI（本文档未开启），需要生成自签名证书

# mkdir data/maxscale/ssl

## 一直Enter即可
# openssl req -x509 -nodes -days 36500 -newkey rsa:2048 -keyout data/maxscale/ssl/mariadb.key -out data/maxscale/ssl/mariadb.crt
Generating a 2048 bit RSA private key
..................+++
..............................................+++
writing new private key to '/data/maxscale/ssl/mariadb.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:

# chown maxscale.maxscale data/maxscale/ssl/ -R

配置 MaxScale 配置文件

# vim data/maxscale/etc/maxscale.cnf
[maxscale]
threads=auto
 
# Server definitions
[dbserv1]
type=server
address=10.186.61.191
port=3306
protocol=MariaDBBackend #Not Modified
 
[dbserv2]
type=server
address=10.186.61.192
port=3306
protocol=MariaDBBackend #Not Modified
 
[dbserv3]
type=server
address=10.186.63.64
port=3306
protocol=MariaDBBackend #Not Modified
 
# Monitor for the servers
[Replication-Monitor]
type=monitor
module=mariadbmon
servers=dbserv1, dbserv2, dbserv3
user=monitor
password=F270B97B3D4D37BC619EB02304A02C1456F41B05CF2D7E5F37BA48CC7646C2E4
monitor_interval=2000ms
 
# Service definitions
[Read-Write-Service]
type=service
router=readwritesplit
servers=dbserv1, dbserv2, dbserv3
user=maxscale
password=F270B97B3D4D37BC619EB02304A02C1456F41B05CF2D7E5F37BA48CC7646C2E4
max_slave_connections=2
master_accept_reads=false
max_connections=0
# Ensure read and write consistency
max_slave_replication_lag=1s
#causal_reads=local
#causal_reads_timeout=10
 
# Listener definitions for the services
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=33060

配置 MaxScale 启动服务

# vim usr/lib/systemd/system/maxscale.service
[Unit]
Description=MariaDB MaxScale Database Proxy
After=network.target
 
[Service]
Type=forking
Restart=on-abort
 
PermissionsStartOnly=true
 
# Use the default user and group
User=maxscale
Group=maxscale
 
# Start MaxScale
ExecStart=/data/maxscale/bin/maxscale --user=maxscale --basedir=/data/maxscale/ --config=/data/maxscale/etc/maxscale.cnf
TimeoutStartSec=120
LimitNOFILE=65535
StartLimitBurst=0
WatchdogSec=60s
NotifyAccess=all
 
# MaxScale should be restarted if it exits with 75 (BSD's EX_TEMPFAIL)
RestartForceExitStatus=75
 
# Only relevant when MaxScale is linked with -fsanitize=address
Environment=ASAN_OPTIONS=abort_on_error=1
 
[Install]
WantedBy=multi-user.target

启动 MaxScale

# systemctl daemon-reload
# systemctl start maxscale

确认读写分离效果

# data/mysql/base/5.7.25/bin/mysql -umaxscale -p -h 10.186.63.98 -P 33060
Enter password: 
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| node3      |
+------------+
1 row in set (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| node2      |
+------------+
1 row in set (0.00 sec)

检查 MaxScale 后端状态

# maxctrl list servers
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬──────┐
│ Server  │ Address       │ Port │ Connections │ State           │ GTID │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────┤
│ dbserv1 │ 10.186.61.191 │ 3306 │ 1           │ Master, Running │      │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────┤
│ dbserv2 │ 10.186.61.192 │ 3306 │ 1           │ Slave, Running  │      │
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼──────┤
│ dbserv3 │ 10.186.63.64  │ 3306 │ 1           │ Slave, Running  │      │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────┘