MariaDB生产级别高可用

此方案只适合 mariadb,MySQL 请不要使用此方案
此方案实现功能

自动故障转移

自动故障恢复,如 master 宕掉后重启自动加入集群成为 slave

读写分离

binlog 日志备份与解析

1 环境规划#

ip		端口	名称	binlog 路径
10.0.16.12	10.2.41	11	Master	/var/log/mysql
10.0.16.12	10.2.41	12	Slave1	/var/log/mysql
10.0.16.12	10.2.41	13	Slave2	/var/log/mysql
10.0.16.12	10.2.41	14	Slave3	/var/log/mysql
10.0.16.12	6.1.4-1	5506	maxscale	/data/maxscale1_binlog
10.0.16.12	6.1.4-1	5507	maxscale	/data/maxscale2_binlog
10.0.16.12	1.20.2	3306	nginx

2 简单部署 mariadb#

2.1 修改配置文件#

配置基本一样,server_id 不同就行

1
[mysqld]
2
expire_logs_days       = 7    # binlog保存时间
3
server_id              = 1
4
binlog_format          = ROW  # 记录方式
5
gtid_ignore_duplicates = on
6
gtid_strict_mode       = on
7
wsrep_gtid_mode        = on
8
log_slave_updates      = 1  # 即可以当从也可以当主
9
log_bin                = /var/log/mysql/mariadb-bin
10
log_bin_index          = /var/log/mysql/mariadb-bin.index
11
sync_binlog            = 1
12
relay_log              = /var/log/mysql/relay-bin
13
relay_log_index        = /var/log/mysql/relay-bin.index
14
relay_log_info_file    = /var/log/mysql/relay-bin.info

docker run -d --name m3 -p 13:3306 -v /home/ops/my3.cnf:/etc/mysql/my.cnf -v /etc/localtime:/etc/localtime -e MYSQL_ROOT_PASSWORD=qweasd mariadb:10.2.41

2.2 配置账号#

添加同步 binlog 账户 CREATE USER 'maxscale_binlog'@'%' IDENTIFIED by '09EtpqiIJbsscpuX'; GRANT REPLICATION SLAVE ON *.* TO 'maxscale_binlog'@'%'; GRANT SELECT ON *.* TO maxscale_binlog@'%';

添加监控账号

CREATE USER 'maxscale_monitor'@'%' IDENTIFIED BY 'P7fHcxCkPYJYdGZY'; GRANT SUPER, RELOAD on *.* to 'maxscale_monitor'@'%'; GRANT REPLICATION CLIENT on *.* to 'maxscale_monitor'@'%';

刷新权限

FLUSH PRIVILEGES;

2.3 配置主从#

1
CHANGE MASTER TO master_host="10.0.16.12", master_port=11,master_user="maxscale_binlog", master_password="qweasd", master_use_gtid=slave_pos;
2
START SLAVE;

查看

1
SHOW SLAVE STATUS\G
2
*************************** 1. row ***************************
3
               Slave_IO_State: Waiting for master to send event
4
                  Master_Host: 10.0.16.12
5
                  Master_User: maxscale_binlog
6
                  Master_Port: 11
7
                Connect_Retry: 60
8
              Master_Log_File: mariadb-bin.000004
9
          Read_Master_Log_Pos: 1358
10
               Relay_Log_File: relay-bin.000002
11
                Relay_Log_Pos: 704
12
        Relay_Master_Log_File: mariadb-bin.000004
13
             Slave_IO_Running: Yes
14
            Slave_SQL_Running: Yes

3 部署 maxscale#

sudo yum localinstall -y maxscale-6.1.4-1.rhel.7.x86_64.rpm

3.1 配置 maxscale#

maxscale 是通过 monitor 组来监控 mariadb 状态的,如果有问题会自动切换 master,同时 binlogrouter 会自动切换 master 节点

3.2 配置 maxscale 文件#

cat /etc/maxscale.cnf

1
[maxscale]
2
threads=1
3
ms_timestamp=1
4
log_info=1
5
log_augmentation=1
6
admin_host=0.0.0.0
7
admin_secure_gui=false
8

9
[test-master]
10
type=server
11
address=10.0.16.12
12
port=11
13

14
[test-slave]
15
type=server
16
address=10.0.16.12
17
port=12
18

19
[test-binlog]
20
type=server
21
address=10.0.16.12
22
port=3306
23

24
[test-binlog-slave-1]
25
type=server
26
address=10.0.16.12
27
port=13
28

29
[test-binlog-slave-2]
30
type=server
31
address=10.0.16.12
32
port=14
33

34
[MariaDB-Monitor]
35
type=monitor
36
module=mariadbmon
37
servers=test-master,test-slave
38
user=maxscale
39
password=09EtpqiIJbsscpuX
40
monitor_interval=3s
41
auto_failover=true
42
auto_rejoin=true
43

44
[MariaDB-Monitor-Binlog]
45
type=monitor
46
module=mariadbmon
47
servers=test-binlog,test-binlog-slave-1,test-binlog-slave-2
48
user=maxscale
49
password=09EtpqiIJbsscpuX
50
monitor_interval=3s
51
auto_failover=true
52

53
[Replication-Binlog-Server-1]
54
type=service
55
router=binlogrouter
56
user=maxscale
57
password=09EtpqiIJbsscpuX
58
server_id=11
59
net_timeout=5s
60
datadir=/data/maxscale1_binlog
61
cluster=MariaDB-Monitor
62
select_master=true
63
expire_log_duration=0h
64
expire_log_minimum_files=3
65

66
[Replication-Binlog-Listener-1]
67
type=listener
68
service=Replication-Binlog-Server-1
69
protocol=MariaDBClient
70
address=0.0.0.0
71
port=3306
72

73
[Replication-Binlog-Server-2]
74
type=service
75
router=binlogrouter
76
user=maxscale
77
password=09EtpqiIJbsscpuX
78
server_id=12
79
datadir=/data/maxscale2_binlog
80
cluster=MariaDB-Monitor
81
select_master=true
82
expire_log_duration=5h
83
expire_log_minimum_files=3
84

85
[Replication-Binlog-Listener-2]
86
type=listener
87
service=Replication-Binlog-Server-2
88
protocol=MariaDBClient
89
address=0.0.0.0
90
port=5507

3.3 创建 binlog 存储路径#

mkdir /data/maxscale2_binlog mkdir /data/maxscale1_binlog

chown -R maxscale.maxscale /data/maxscale1_binlog chown -R maxscale.maxscale /data/maxscale2_binlog

3.3.1 启动#

systemctl start maxscale

systemctl enable maxscale

3.4 启动报错查看日志#

less /var/log/maxscale/maxscale.log

3.5 查看状态#

maxctrl list servers,这里只有 11 与 12 创建 monitor 账号了,是有数据的,而 13 与 14 是没数据的

1
┌─────────────────────┬────────────┬──────┬─────────────┬──────────────────────┬──────────┐
2
│ Server              │ Address    │ Port │ Connections │ State                │ GTID     │
3
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
4
│ test-master         │ 10.0.16.12 │ 11   │ 0           │ Master, Running      │ 0-1-7346 │
5
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
6
│ test-slave          │ 10.0.16.12 │ 12   │ 0           │ Slave, Running       │ 0-1-7346 │
7
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
8
│ test-binlog         │ 10.0.16.12 │ 3306 │ 0           │ Auth Error, Runnin   │          │
9
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
10
│ test-binlog-slave-1 │ 10.0.16.12 │ 13   │ 0           │ Auth Error, Down     │          │
11
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
12
│ test-binlog-slave-2 │ 10.0.16.12 │ 14   │ 0           │ Auth Error, Down     │          │
13
└─────────────────────┴────────────┴──────┴─────────────┴──────────────────────┴──────────

maxctrl list services

1
│ Service                     │ Router       │ Connections │ Total Connections │ Servers │
2
├─────────────────────────────┼──────────────┼─────────────┼───────────────────┼─────────┤
3
│ Replication-Binlog-Server-2 │ binlogrouter │ 0           │ 0                 │         │
4
├─────────────────────────────┼──────────────┼─────────────┼───────────────────┼─────────┤
5
│ Replication-Binlog-Server-1 │ binlogrouter │ 0           │ 0                 │

4 同步 master binlog#

sudo mysql -h 10.1.132.124 -P3306 -umaxscale_binlog -p09EtpqiIJbsscpuX

这里可能登录不上去,原因是配置有问题,获取不到 binlog

查看状态

SHOW SLAVE STATUS\G

如果有问题

STOP SLAVE;

CHANGE MASTER TO master_host="10.0.16.12", master_port=11,master_user="maxscale_binlog", master_password="qweassd", master_use_gtid=slave_pos;

START SLAVE;

以下为正常信息

SHOW SLAVE STATUS\G

1
*************************** 1. row ***************************
2
                Slave_IO_State: Waiting for master to send event
3
                   Master_Host: 10.0.16.12
4
                   Master_User: maxscale_binlog
5
                   Master_Port: 11
6
                 Connect_Retry: 1
7
               Master_Log_File: mariadb-bin.000004
8
           Read_Master_Log_Pos: 768
9
                Relay_Log_File:
10
                 Relay_Log_Pos:
11
         Relay_Master_Log_File:
12
              Slave_IO_Running: Yes
13
             Slave_SQL_Running: Yes

4.1 访问 ui#

ip:8989

user=admin

password=mariadb

5 配置负载均衡#

使用 nginx tcp 负载均衡要有 nginx-mod-stream 模块

tail -n 16 /etc/nginx/nginx.conf

1
stream {
2
    upstream binlog {
3
        hash $remote_addr consistent;   #负载方法
4
        server 10.0.16.12:3306 max_fails=5 fail_timeout=30s;
5
        server 10.0.16.12:5507 max_fails=5 fail_timeout=30s;
6

7
    }
8
    server {
9
        listen 3306;   #服务器监听端口
10
        proxy_connect_timeout 60;
11
        proxy_timeout 300s;    #设置客户端和代理服务之间的超时时间，如果5分钟内没操作将自动断开。
12
        proxy_pass binlog;
13
   }
14

15
}

6 从节点同步 binlog server#

STOP SLAVE;

CHANGE MASTER TO master_host="10.0.16.12", master_port=3306,master_user="maxscale_binlog", master_password="qweasd", master_use_gtid=slave_pos;

START SLAVE;

查看

1
*************************** 1. row ***************************
2
               Slave_IO_State: Waiting for master to send event
3
                  Master_Host: 10.0.16.12
4
                  Master_User: maxscale_binlog
5
                  Master_Port: 3306
6
                Connect_Retry: 60
7
              Master_Log_File: mariadb-bin.000004
8
          Read_Master_Log_Pos: 299
9
               Relay_Log_File: relay-bin.000002
10
                Relay_Log_Pos: 600
11
        Relay_Master_Log_File: mariadb-bin.000004
12
             Slave_IO_Running: Yes
13
            Slave_SQL_Running: Yes

maxctrl list services,可以看到平均分配到每个 binlogserver 节点

1
│ Service                     │ Router       │ Connections │ Total Connections │ Servers │
2
├─────────────────────────────┼──────────────┼─────────────┼───────────────────┼─────────┤
3
│ Replication-Binlog-Server-2 │ binlogrouter │ 2           │ 3                 │         │
4
├─────────────────────────────┼──────────────┼─────────────┼───────────────────┼─────────┤
5
│ Replication-Binlog-Server-1 │ binlogrouter │ 1           │ 2                 │         │
6
└─────────────────────────────┴──────────────┴─────────────┴───────────────────┴─────────

6.1 问题#

1,数据冲突

1
            Last_Errno: 1062
2
                   Last_Error: Could not execute Write_rows_v1 event on table mysql.proxies_priv; Duplicate entry 'localhost-root--' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mariadb-bin.000001, end_log_pos 25393
3

4
server1 │ 10.0.16.12 │ 11   │ 0         │ Master, Running                   │ 0-1-7259
5

6
STOP SLAVE;
7
SET @@global.gtid_slave_pos = "0-1-7259";
8
START SLAVE;

1,GTID 冲突

1
                   Last_Errno: 1950
2
                   Last_Error: An attempt was made to binlog GTID 0-1-35 which would create an out-of-order sequence number with existing GTID 0-3-35, and gtid strict mode is enabled
3

4
RESET MASTER;
5
STOP SLAVE
6
CHANGE MASTER TO master_host="10.0.16.12", master_port=3306,master_user="maxscale_binlog", master_password="qweasd", master_use_gtid=slave_pos;
7
START SLAVE

7 测试高可用#

停掉 master,发现 slav1 会自动切换成 master

1
┌─────────────────────┬────────────┬──────┬─────────────┬──────────────────────┬──────────┐
2
│ Server              │ Address    │ Port │ Connections │ State                │ GTID     │
3
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
4
│ test-master         │ 10.0.16.12 │ 11   │ 0           │ Down      │ 0-1-7346 │
5
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
6
│ test-slave          │ 10.0.16.12 │ 12   │ 0           │ Master, Running       │ 0-1-7346 │
7
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
8
│ test-binlog         │ 10.0.16.12 │ 3306 │ 0           │ Binlog Relay, Runnin │ 0-1-7346 │
9
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
10
│ test-binlog-slave-1 │ 10.0.16.12 │ 13   │ 0           │ Slave, Running       │ 0-1-7346 │
11
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
12
│ test-binlog-slave-2 │ 10.0.16.12 │ 14   │ 0           │ Slave, Running       │ 0-1-7346 │
13
└─────────────────────┴────────────┴──────┴─────────────┴──────────────────────┴──────────

查看 slave2 信息

1
SHOW SLAVE STATUS\G
2
*************************** 1. row ***************************
3
               Slave_IO_State: Waiting for master to send event
4
                  Master_Host: 10.0.16.12
5
                  Master_User: maxscale_binlog
6
                  Master_Port: 3306
7
                Connect_Retry: 60
8
              Master_Log_File: mariadb-bin.000004
9
          Read_Master_Log_Pos: 768
10
               Relay_Log_File: relay-bin.000003
11
                Relay_Log_Pos: 379
12
        Relay_Master_Log_File: mariadb-bin.000004
13
             Slave_IO_Running: Yes
14
            Slave_SQL_Running: Yes

查看 maxscale 信息

1
*************************** 1. row ***************************
2
                Slave_IO_State: Waiting for master to send event
3
                   Master_Host: 10.0.16.12
4
                   Master_User: maxscale_binlog
5
                   Master_Port: 12
6
                 Connect_Retry: 1
7
               Master_Log_File: mariadb-bin.000004
8
           Read_Master_Log_Pos: 768
9
                Relay_Log_File:
10
                 Relay_Log_Pos:
11
         Relay_Master_Log_File:
12
              Slave_IO_Running: Yes
13
             Slave_SQL_Running: Yes

从 slave1 写入数据后查看

slave2

1
*************************** 1. row ***************************
2
               Slave_IO_State: Waiting for master to send event
3
                  Master_Host: 10.0.16.12
4
                  Master_User: maxscale_binlog
5
                  Master_Port: 3306
6
                Connect_Retry: 60
7
              Master_Log_File: mariadb-bin.000004
8
          Read_Master_Log_Pos: 987
9
               Relay_Log_File: relay-bin.000003
10
                Relay_Log_Pos: 379
11
        Relay_Master_Log_File: mariadb-bin.000004
12
             Slave_IO_Running: Yes
13
            Slave_SQL_Running: Yes

maxscale

1
*************************** 1. row ***************************
2
                Slave_IO_State: Waiting for master to send event
3
                   Master_Host: 10.0.16.12
4
                   Master_User: maxscale_binlog
5
                   Master_Port: 12
6
                 Connect_Retry: 1
7
               Master_Log_File: mariadb-bin.000004
8
           Read_Master_Log_Pos: 987
9
                Relay_Log_File:
10
                 Relay_Log_Pos:
11
         Relay_Master_Log_File:
12
              Slave_IO_Running: Yes
13
             Slave_SQL_Running: Yes

可以发现完全实现自动切换,并且 slave 都通过 maxscal 同步 binlog

7.1 master 自动恢复#

启动 master

1
┌─────────────────────┬────────────┬──────┬─────────────┬──────────────────────┬──────────┐
2
│ Server              │ Address    │ Port │ Connections │ State                │ GTID     │
3
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
4
│ test-master         │ 10.0.16.12 │ 11   │ 0           │ Slave, Running       │ 0-1-7346 │
5
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
6
│ test-slave          │ 10.0.16.12 │ 12   │ 0           │ Master, Running      │ 0-1-7346 │
7
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
8
│ test-binlog         │ 10.0.16.12 │ 3306 │ 0           │ Binlog Relay, Runnin │ 0-1-7346 │
9
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
10
│ test-binlog-slave-1 │ 10.0.16.12 │ 13   │ 0           │ Slave, Running       │ 0-1-7346 │
11
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
12
│ test-binlog-slave-2 │ 10.0.16.12 │ 14   │ 0           │ Slave, Running       │ 0-1-7346 │
13
└─────────────────────┴────────────┴──────┴─────────────┴──────────────────────┴──────────

切换节点

maxctrl call command mariadbmon switchover MariaDB-Monitor test-master test-slave

1
"\u001b[32mOK\u001b[39m"

1
┌─────────────────────┬────────────┬──────┬─────────────┬──────────────────────┬──────────┐
2
│ Server              │ Address    │ Port │ Connections │ State                │ GTID     │
3
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
4
│ test-master         │ 10.0.16.12 │ 11   │ 0           │ Master, Running      │ 0-1-7346 │
5
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
6
│ test-slave          │ 10.0.16.12 │ 12   │ 0           │ Slave, Running       │ 0-1-7346 │
7
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
8
│ test-binlog         │ 10.0.16.12 │ 3306 │ 0           │ Binlog Relay, Runnin │ 0-1-7346 │
9
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
10
│ test-binlog-slave-1 │ 10.0.16.12 │ 13   │ 0           │ Slave, Running       │ 0-1-7346 │
11
├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤
12
│ test-binlog-slave-2 │ 10.0.16.12 │ 14   │ 0           │ Slave, Running       │ 0-1-7346 │
13
└─────────────────────┴────────────┴──────┴─────────────┴──────────────────────┴──────────