关于读写分离:技术分享-MaxScale-实现-MySQL读写分离

作者：李鹏博

爱可生 DBA 团队成员，次要负责 MySQL 故障解决和 SQL 审核优化。对技术执着，为客户负责。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

MaxScale 是由 MariaDB 官网出品的一款开源数据库中间件，其插件是插拔式的，而且能够定制化开发属于本人的插件，应用十分的灵便自在，目前官网提供了例如监控、高可用、读写拆散、防火墙等插件。其中高可用和监控插件相互配合能够实现 MariaDB 的 Failover、Switchover、autoRejoin 性能，并在故障转移时能够主动进行数据弥补，不过遗憾的是因为 MySQL 的 GTID 形成形式和 MariaDB 的差异性，目前 MySQL 无奈应用其高可用性能。不过能够应用其读写拆散性能。

提到数据库的读写拆散，其中须要解决的问题就是别离在主从实例上进行读写操作时如何保障在从实例读取的数据的正确性，个别咱们有如下几种做法，比方：提早读取，就是在读取前期待一段时间；转发须要数据正确性较高的查问到主实例；借助 MySQL 自身的半同步复制保障主从数据的一致性，并转发查问到无提早或提早较小的从实例上。第一种做法会人为的拉大查问的返回工夫；第二种则配置及保护起来较为艰难；第三种则看起来 ” 针不戳 ” 的样子。而 MaxScale 的实现形式就是第三种，通过指定读取时可能容忍的最大延迟时间，当从实例延迟时间超过该工夫后，读操作就不会被路由到该节点，如果切实没有可用从节点，读操作就会被路由到主节点。而且 MaxScale 还反对因果读取，通过配置 causal_reads=local 和 causal_reads_timeout 参数来实现，成果就是在从实例进行查问时，如果实例提早较大，会期待 causal_reads_timeout 超时，默认 10s，超时后就将查问路由到主节点。当然，也并不是说这种实现形式就是最完满的，思考一种场景，如果所有的从实例都提早较高，在进行查问时没有可用从实例，这时主实例就要承当所有的读写压力，这时候负载会不会将主实例压死也是一个须要思考的问题。所以没有最完满的计划，只有最适宜本人的。接下来让咱们瞅瞅如何配置 MaxScale 实现 MySQL 数据库的读写拆散。

主机名	IP	角色
node4	10.186.63.88	Maxscale
node1	10.186.61.191	MySQL Master
node2	10.186.61.192	MySQL Slave
node3	10.186.63.64	MySQL Slave

部署后端 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)

创立 MaxScale 用户并受权

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

mysql> grant select on mysql.* to [email protected]'%';
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  │      │
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴──────┘

部署拓扑