搭建MariaDB读写分离、高可用
前言
在做基础脚手架开发的过程中涉及到代码要不要支持读写分离，支持的话的需要一定的工作量，虽然代码不多，也比较容易实现，但毕竟需要维护。为了避免重新写轮子，先查询。还有真有不少解决方案，第一种就是基于应用的，自己在应用程序中编码实现；第二种就是基于中间件，有MySQL Proxy、MaxScale和HAProxy等，MySQL Proxy已经不维护了；HAProxy本身不支持读写分离，还需要搭配其他才能实现；MaxScale 文档比较全，并且是MariaDB 开发的，所以先用起来看看效果。
环境
- Linux rocky 8
- MariaDB 10.3
- MariaDB MaxScale 6.4
- Keepalived 2.1.5
环境规划
| 序号 | 应用 | hostname | IP | 端口 |
|---|---|---|---|---|
| 1 | MariaDB Master | mariadb_master | 192.168.56.116 | 3306 |
| 2 | MariaDB Slave | mariadb_slave | 192.168.56.117 | 3306 |
| 3 | MaxScale Master 和 Keepalived Master | msk_master | 192.168.56.118 | 4006/8989 |
| 4 | MaxScale Backup 和 Keepalived Backup | msk_backup | 192.168.56.119 | 4006/8989 |
| 5 | VIP | 192.168.56.120 |
MariaDB 主从搭建
Master 安装
检查是否之前是否有安装，有删除
1
2yum list installed | grep -i maria
yum list installed | grep -i mysql安装
1
yum install mariadb-server
修改默认数据目录，
1
2
3
4
5
6
7
8
9
10
11
12
13
14# 1. 创建目录
mkdir /home/mariadb_data
# 2. 拷贝目录数据目录
cp -a /var/lib/mysql /home/mariadb_data/
# 3. 修改mariadb server 配置文件
vi /etc/my.cnf.d/mariadb-server.cnf
# 需要修改的内容
datadir=/home/mariadb_data/mysql
socket=/home/mariadb_data/mysql/mysql.sock
# 4. 修改客户端配置文件
vi /etc/my.cnf.d/client.cnf
# 需要修改的内容
[client]
socket=/home/mariadb_data/mysql/mysql.sock修改SELinux对MySQL默认数据目录的保护
1
2
3
4
5
6# 1. 打开/etc/selinux/targeted/contexts/files/file_contexts.local
vi /etc/selinux/targeted/contexts/files/file_contexts.local
# 2. 添加以下内容
/home/mariadb_data(/.*)? system_u:object_r:mysqld_db_t:s0
# 如果已执行过启动执行命令请重新授权
chcon -R -t mysqld_db_t /home/mariadb_data/启动MySQL
1
2
3
4# 启动
systemctl start mariadb
# 开启自启动
systemctl enable mariadb数据库初始化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16# 1. 临时设置MYSQL_UNIX_PORT变量
export MYSQL_UNIX_PORT=/home/mariadb_data/mysql/mysql.sock
# 2. 执行命令
mysql_secure_installation
# 说明
# 首先是设置密码，会提示先输入密码:
Enter current password for root (enter for none):<–初次运行直接回车
# 设置密码
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
# 其它配置
Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,
Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车测试登陆
1
2# 登陆到MySQL Server
mysql -uroot -p
Slave 安装
同master
搭建配置
修改master的
my.cnf配置文件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# server id 唯一
server_id=116
# GTID 模式
gtid_strict_mode=1
# 是否记录从服务器同步数据动作
log-slave-updates=ON
# 开启及设置二进制日志文件名称
log_bin=mysql-bin
# 设置binlog文件格式
binlog_format=MIXED
# 记录IO线程读取已经读取到的master binlog位置，用于slave宕机后IO线程根据文件中的POS点重新拉取binlog日志
master-info-repository = TABLE
# 记录SQL线程读取Master binlog的位置，用于Slave 宕机后根据文件中记录的pos点恢复Sql线程
relay-log-info-repository = TABLE
# 启用确保无信息丢失；任何一个事务提交后, 将二进制日志的文件名及事件位置记录到文件中
sync-master-info = 1
# 设定从服务器的复制线程数；0表示关闭多线程复制功能
slave-parallel-workers = 2
# 设置binlog校验算法（循环冗余校验码）
binlog-checksum = CRC32
# 设置主服务器是否校验
master-verify-checksum = 1
# 设置从服务器是否校验
slave-sql-verify-checksum = 1
# 用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度
binlog-rows-query-log_events = 1
# 保证master crash safe，该参数必须设置为1
sync_binlog = 1
# 保证master crash safe，该参数必须设置为1
innodb_flush_log_at_trx_commit = 1重启MariaDB Server
添加同步用户
1
2GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO replication@'192.168.56.%' IDENTIFIED BY '1234@abCD';
FLUSH PRIVILEGES;查看master 状态并记录下位置信息
1
2
3
4
5
6SELECT @@GLOBAL.gtid_current_pos;
+---------------------------+
| @@GLOBAL.gtid_current_pos |
+---------------------------+
| 0-116-12 |
+---------------------------+修改slave的
my.cnf配置文件1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23# server id 唯一
server-id=117
# GTID 模式
gtid_strict_mode=1
# 是否记录从服务器同步数据动作
log-slave-updates=ON
# 开启及设置二进制日志文件名称
log_bin=mysql-bin
# 设置binlog文件格式
binlog_format=MIXED
# 设置一般用户为只读模式
read-only=1
master-info-repository = TABLE
relay-log-info-repository = TABLE
sync-master-info = 1
slave-parallel-workers = 4
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log_events = 1
# crash safe slave
relay_log_recovery = 1重启MariaDB Server
打开master 防火墙的3306 端口
1
2firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload配置从主库同步数据
1
2
3
4
5
6
7
8
9
10
11# 1. 登陆MySQL slave Server
mysql -uroot -p
set global gtid_slave_pos = "0-116-12";
# 2. 配置
# MASTER_LOG_FILE 和 MASTER_LOG_POS 的值就是从 主库 show master status 命令中获取的信息
mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.116',
MASTER_USER='replication',
MASTER_PASSWORD='1234@abCD',
master_use_gtid=slave_pos;
# 3. 开启同步
mysql> start slave;查看同步情况
1
2
3
4
5mysql> show slave status\G
# 检查以下参数都是 Yes 代表正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes测试，通过创建数据库、表，添加数据来看同步是否正常
1
2
3
4
5
6
7
8
9
10
11
12# 1. 创建测试库和测试用户
CREATE DATABASE ivy_base CHARACTER SET utf8mb4;
grant all on ivy_base.* to 'ivy_base'@'%' identified by '1234@abCD';
flush privileges;
# 2. 执行创建表、添加数据、查询数据等操作
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;
INSERT INTO test (id, NAME) VALUES ('2', '1');
select * from test;
MaxScale 搭建
Master 安装配置
通过官网下载安装包
1
wget https://dlm.mariadb.com/2717592/MaxScale/6.4.5/yum/centos/8/x86_64/maxscale-6.4.5-1.rhel.8.x86_64.rpm
安装
1
yum install maxscale-6.4.5-1.rhel.8.x86_64.rpm
在MySQL Master 上添加监控和路由用户
1
2
3
4
5
6
7
8
9# 1. 连接MySQL Server
mysql -uroot -p
# 2. 添加监控用户
grant Replication slave,Replication client,Super,Reload on *.* to 'maxmon'@'192.168.56.%' identified by '1234@abCD';
# 3. 添加路由用户
grant select on mysql.* to 'maxrou'@'192.168.56.%' identified by '1234@abCD';
grant show databases on *.* to 'maxrou'@'192.168.56.%';
# 4. 刷新权限信息
flush privileges;读写分离配置，故障自动转移。具体可阅读官方配置
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# 1. 打开配置文件
vim /etc/maxscale.cnf
# 2. 配置
# 基础配置
[maxscale]
threads=auto
admin_host=0.0.0.0
admin_secure_gui=false
# MySQL Server 配置
[server1]
type=server
address=192.168.56.116
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=192.168.56.117
port=3306
protocol=MariaDBBackend
# 监控配置
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxmon
password=password
monitor_interval=2000
# 用来执行CHANGE MASTER TO 命令的
replication_user=replication
replication_password=1234@abCD
# 打开自动故障转移
auto_failover=true
# 打开自动重新加入
auto_rejoin=true
# slave 全部失效时 master 支撑全部请求
detect_stale_master=true
failover_timeout=5
failcount=5
master_failure_timeout=2
verify_master_failure=true
switchover_timeout=90
# 过滤器配置
# 非必须配置，是我使用的客户端工具默认会执行解释计划，但默认explain plan 会被路由到从库
[Named-Server-Filter]
type=filter
module=namedserverfilter
match01=^explain.*
target01=server1
# 读写分离服务配置
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxrou
password=password
filters=Named-Server-Filter
# 读写分离监听配置
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MySQLClient
port=4006启动MaxScale
1
2
3
4# 启动
systemctl start maxscale
# 加入开机自启动
systemctl enable maxscale测试读写分离
用MySQL客户端或者命令连接上MaxScale，对数据库的表进行添加和查询数据，观察read/write值的变化
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# 1. 通过命令监控 read/write 的数量变化
maxctrl show services
# 结果
│ Router Diagnostics │ { │
│ │ "avg_sescmd_history_length": 0, │
│ │ "max_sescmd_history_length": 32, │
│ │ "queries": 117, │
│ │ "replayed_transactions": 0, │
│ │ "ro_transactions": 0, │
│ │ "route_all": 33, │
│ │ "route_master": 9, │
│ │ "route_slave": 75, │
│ │ "rw_transactions": 0, │
│ │ "server_query_statistics": [ │
│ │ { │
│ │ "avg_selects_per_session": 0, │
│ │ "avg_sess_duration": "0ns", │
│ │ "id": "server1", │
│ │ "read": 33, │
│ │ "total": 42, │
│ │ "write": 9 │
│ │ }, │
│ │ { │
│ │ "avg_selects_per_session": 0, │
│ │ "avg_sess_duration": "0ns", │
│ │ "id": "server2", │
│ │ "read": 108, │
│ │ "total": 108, │
│ │ "write": 0 │
│ │ } │
│ │ ] │
│ │ }
# 2. 通过MaxGUI监控 http://127.0.0.1:8989测试故障转移
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# 1. 查看server 情况
maxctrl list servers
┌─────────┬────────────────┬──────┬─────────────┬──────────────────────────────┬──────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼────────────────┼──────┼─────────────┼──────────────────────────────┼──────────┤
│ server1 │ 192.168.56.116 │ 3306 │ 0 │ Master, Running │ 0-117-20 │
├─────────┼────────────────┼──────┼─────────────┼──────────────────────────────┼──────────┤
│ server2 │ 192.168.56.117 │ 3306 │ 0 │ Relay Master, Slave, Running │ 0-117-20 │
└─────────┴────────────────┴──────┴─────────────┴──────────────────────────────┴──────────┘
# 2. 登陆到master 服务器上停掉MariaDB
systemctl stop MariaDB
# 3. 再次查看server情况
maxctrl list servers
┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬──────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server1 │ 192.168.56.116 │ 3306 │ 0 │ Down │ 0-117-20 │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server2 │ 192.168.56.117 │ 3306 │ 0 │ Master, Running │ 0-117-20 │
└─────────┴────────────────┴──────┴─────────────┴─────────────────┴──────────┘
# 4. 再次启动停掉的MariaDB
systemctl start mariadb
# 5. 再次查看server情况，启动的节点已经重新加入
maxctrl list servers
┌─────────┬────────────────┬──────┬─────────────┬─────────────────┬──────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server1 │ 192.168.56.116 │ 3306 │ 0 │ Slave, Running │ 0-117-20 │
├─────────┼────────────────┼──────┼─────────────┼─────────────────┼──────────┤
│ server2 │ 192.168.56.117 │ 3306 │ 0 │ Master, Running │ 0-117-20 │
└─────────┴────────────────┴──────┴─────────────┴─────────────────┴──────────┘
Slave 安装配置
同master安装配置
Keepalived 搭建
Master 安装配置
安装
1
yum install keepalived
配置
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# 1.先备份原来的配置文件，然后开始配置
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
# 全局配置
global_defs {
router_id LVS_DEVEL_MASTER
script_user root
enable_script_security
}
# 健康检查脚本
vrrp_script chk_maxscale {
script "/usr/bin/pidof maxscale"
interval 2
}
# 实例
vrrp_instance VI_1 {
state MASTER
interface enp0s3
virtual_router_id 120
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.56.120/24
}
track_script {
chk_maxscale
}
# 调试用,配置好可删除
debug 1
# 通知脚本，用来改变MaxScale的passive的状态，防止多个MaxScale进行故障自动转移的操作
notify /etc/keepalived/scripts/notify_script.sh
}通知脚本，该脚本最好放在
/etc/keepalived/目录下，其他目录需要额外处理权限
1 |
|
/etc/keepalived/scripts/notify_script.sh脚本SELinux 权限配置网上很多方法是关闭SELinux ，实在解决不了可以关闭掉SELinux ，最好是不关闭，多一层防护
1 | chcon -t keepalived_unconfined_script_exec_t /etc/keepalived/scripts/notify_script.sh |
- 开通防火墙
1 | firewall-cmd --add-protocol=vrrp --permanent |
- 启动
1 | # 启动 |
Backup 安装配置
和Master唯一不同是第5点，直接通过下面的配置代码介绍
- backup的配置
1 | global_defs { |
测试
- 首先分别监控keepalived master和backup 的日志
1 | journalctl -fu keepalived.service |
监控MaxScale Master 和 backup的 passive 值
master：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21# 执行命令
maxctrl show maxscale
# 结果中的关键信息
┌──────────────┬──────────────────────────────────────────────────────────┐
│ Version │ 6.4.5 │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Commit │ e716c9cfc5f68f2e4ffada46c2d145918e7433bc │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Started At │ Fri, 17 Mar 2023 14:07:19 GMT │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Activated At │ Fri, 17 Mar 2023 14:07:19 GMT │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Uptime │ 1827 │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Config Sync │ null │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Parameters │ { │
│ │ "passive": false, │
│ │ "writeq_low_water": 8192 │
│ │ } │
└──────────────┴──────────────────────────────────────────────────────────┘backup:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21# 执行命令
maxctrl show maxscale
# 结果中的关键信息
┌──────────────┬──────────────────────────────────────────────────────────┐
│ Version │ 6.4.5 │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Commit │ e716c9cfc5f68f2e4ffada46c2d145918e7433bc │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Started At │ Fri, 17 Mar 2023 14:07:19 GMT │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Activated At │ Fri, 17 Mar 2023 14:07:19 GMT │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Uptime │ 1827 │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Config Sync │ null │
├──────────────┼──────────────────────────────────────────────────────────┤
│ Parameters │ { │
│ │ "passive": true , │
│ │ "writeq_low_water": 8192 │
│ │ } │
└──────────────┴──────────────────────────────────────────────────────────┘停掉Master 机器上的MaxScale 服务，观察keepalived master和backup 的日志情况
1 | systemctl stop maxscale |
keepalived master日志：结果显示进入了Entering FAULT STATE
1 | Mar 17 22:42:38 msk_master Keepalived_vrrp[2597]: Script `chk_maxscale` now returning 1 |
keepalived backup 日志：结果显示已经进入Entering MASTER STATE
1 | Mar 17 22:42:38 msk_slave Keepalived_vrrp[4215]: (VI_1) Backup received priority 0 advertisement |
- 再查看MaxScale backup的状态
1 | # 执行命令 |
再启动MaxScale Master服务，继续观察日志，可以看到之前的keepalived master 又恢复到了master状态，keepalived backup 也恢复到back状态
keepalived master日志：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22Mar 17 22:48:46 msk_master Keepalived_vrrp[2597]: Script `chk_maxscale` now returning 0
Mar 17 22:48:46 msk_master Keepalived_vrrp[2597]: VRRP_Script(chk_maxscale) succeeded
Mar 17 22:48:46 msk_master Keepalived_vrrp[2597]: (VI_1) Entering BACKUP STATE
Mar 17 22:48:47 msk_master Keepalived_vrrp[2597]: (VI_1) received lower priority (100) advert from 192.168.56.119 - discarding
Mar 17 22:48:48 msk_master Keepalived_vrrp[2597]: (VI_1) received lower priority (100) advert from 192.168.56.119 - discarding
Mar 17 22:48:49 msk_master Keepalived_vrrp[2597]: (VI_1) received lower priority (100) advert from 192.168.56.119 - discarding
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: (VI_1) received lower priority (100) advert from 192.168.56.119 - discarding
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: (VI_1) Receive advertisement timeout
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: (VI_1) Entering MASTER STATE
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: (VI_1) setting VIPs.
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: (VI_1) Sending/queueing gratuitous ARPs on enp0s3 for 192.168.56.120
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:50 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:55 msk_master Keepalived_vrrp[2597]: (VI_1) Sending/queueing gratuitous ARPs on enp0s3 for 192.168.56.120
Mar 17 22:48:55 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:55 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:55 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:55 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120
Mar 17 22:48:55 msk_master Keepalived_vrrp[2597]: Sending gratuitous ARP on enp0s3 for 192.168.56.120keepalived backup 日志：
1
2
3Mar 17 22:48:50 msk_slave Keepalived_vrrp[4215]: (VI_1) Master received advert from 192.168.56.118 with higher priority 150, ours 100
Mar 17 22:48:50 msk_slave Keepalived_vrrp[4215]: (VI_1) Entering BACKUP STATE
Mar 17 22:48:50 msk_slave Keepalived_vrrp[4215]: (VI_1) removing VIPs.
结语
算算刚刚起步，开始用起来！