此方案只适合 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 2 3 4 5 6 7 8 9 10 11 12 13 14 [mysqld] expire_logs_days = 7 # binlog保存时间 server_id = 1 binlog_format = ROW # 记录方式 gtid_ignore_duplicates = on gtid_strict_mode = on wsrep_gtid_mode = on log_slave_updates = 1 # 即可以当从也可以当主 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index sync_binlog = 1 relay_log = /var/log/mysql/relay-bin relay_log_index = /var/log/mysql/relay-bin.index 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 2 CHANGE MASTER TO master_host="10.0.16.12", master_port=11,master_user="maxscale_binlog", master_password="qweasd", master_use_gtid=slave_pos; START SLAVE;
查看
1 2 3 4 5 6 7 8 9 10 11 12 13 14 SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.16.12 Master_User: maxscale_binlog Master_Port: 11 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 1358 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 704 Relay_Master_Log_File: mariadb-bin.000004 Slave_IO_Running: Yes 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 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 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 [maxscale ] threads=1 ms_timestamp=1 log_info=1 log_augmentation=1 admin_host=0.0.0.0 admin_secure_gui=false [test-master ] type=server address=10.0.16.12 port=11 [test-slave ] type=server address=10.0.16.12 port=12 [test-binlog ] type=server address=10.0.16.12 port=3306 [test-binlog-slave-1 ] type=server address=10.0.16.12 port=13 [test-binlog-slave-2 ] type=server address=10.0.16.12 port=14 [MariaDB-Monitor ] type=monitor module=mariadbmon servers=test-master,test-slave user=maxscale password=09EtpqiIJbsscpuX monitor_interval=3s auto_failover=true auto_rejoin=true [MariaDB-Monitor-Binlog ] type=monitor module=mariadbmon servers=test-binlog,test-binlog-slave-1,test-binlog-slave-2 user=maxscale password=09EtpqiIJbsscpuX monitor_interval=3s auto_failover=true [Replication-Binlog-Server-1 ] type=service router=binlogrouter user=maxscale password=09EtpqiIJbsscpuX server_id=11 net_timeout=5s datadir=/data/maxscale1_binlog cluster=MariaDB-Monitor select_master=true expire_log_duration=0h expire_log_minimum_files=3 [Replication-Binlog-Listener-1 ] type=listener service=Replication-Binlog-Server-1 protocol=MariaDBClient address=0.0.0.0 port=3306 [Replication-Binlog-Server-2 ] type=service router=binlogrouter user=maxscale password=09EtpqiIJbsscpuX server_id=12 datadir=/data/maxscale2_binlog cluster=MariaDB-Monitor select_master=true expire_log_duration=5h expire_log_minimum_files=3 [Replication-Binlog-Listener-2 ] type=listener service=Replication-Binlog-Server-2 protocol=MariaDBClient address=0.0.0.0 port=5507
3.3 创建 binlog 存储路径 mkdir /data/maxscale2_binlogmkdir /data/maxscale1_binlog
chown -R maxscale.maxscale /data/maxscale1_binlogchown -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 3 4 5 6 7 8 9 10 11 12 13 ┌─────────────────────┬────────────┬──────┬─────────────┬──────────────────────┬──────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-master │ 10.0.16.12 │ 11 │ 0 │ Master, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-slave │ 10.0.16.12 │ 12 │ 0 │ Slave, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog │ 10.0.16.12 │ 3306 │ 0 │ Auth Error, Runnin │ │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog-slave-1 │ 10.0.16.12 │ 13 │ 0 │ Auth Error, Down │ │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog-slave-2 │ 10.0.16.12 │ 14 │ 0 │ Auth Error, Down │ │ └─────────────────────┴────────────┴──────┴─────────────┴──────────────────────┴──────────
maxctrl list services
1 2 3 4 5 │ Service │ Router │ Connections │ Total Connections │ Servers │ ├─────────────────────────────┼──────────────┼─────────────┼───────────────────┼─────────┤ │ Replication-Binlog-Server-2 │ binlogrouter │ 0 │ 0 │ │ ├─────────────────────────────┼──────────────┼─────────────┼───────────────────┼─────────┤ │ Replication-Binlog-Server-1 │ binlogrouter │ 0 │ 0 │
4 同步 master binlog sudo mysql -h 10.122.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 2 3 4 5 6 7 8 9 10 11 12 13 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.16.12 Master_User: maxscale_binlog Master_Port: 11 Connect_Retry: 1 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 768 Relay_Log_File: Relay_Log_Pos: Relay_Master_Log_File: Slave_IO_Running: Yes 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 stream { upstream binlog { hash $remote_addr consistent; #负载方法 server 10.0.16.12:3306 max_fails=5 fail_timeout=30s; server 10.0.16.12:5507 max_fails=5 fail_timeout=30s; } server { listen 3306; #服务器监听端口 proxy_connect_timeout 60; proxy_timeout 300s; #设置客户端和代理服务之间的超时时间，如果5分钟内没操作将自动断开。 proxy_pass binlog; } }
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 2 3 4 5 6 7 8 9 10 11 12 13 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.16.12 Master_User: maxscale_binlog Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 299 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 600 Relay_Master_Log_File: mariadb-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
maxctrl list services,可以看到平均分配到每个 binlogserver 节点
1 2 3 4 5 6 │ Service │ Router │ Connections │ Total Connections │ Servers │ ├─────────────────────────────┼──────────────┼─────────────┼───────────────────┼─────────┤ │ Replication-Binlog-Server-2 │ binlogrouter │ 2 │ 3 │ │ ├─────────────────────────────┼──────────────┼─────────────┼───────────────────┼─────────┤ │ Replication-Binlog-Server-1 │ binlogrouter │ 1 │ 2 │ │ └─────────────────────────────┴──────────────┴─────────────┴───────────────────┴─────────
6.1 问题 1,数据冲突
1 2 3 4 5 6 7 8 Last_Errno: 1062 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 server1 │ 10.0.16.12 │ 11 │ 0 │ Master, Running │ 0-1-7259 STOP SLAVE; SET @@global.gtid_slave_pos = "0-1-7259"; START SLAVE;
1,GTID 冲突
1 2 3 4 5 6 7 Last_Errno: 1950 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 RESET MASTER; 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
7 测试高可用 停掉 master,发现 slav1 会自动切换成 master
1 2 3 4 5 6 7 8 9 10 11 12 13 ┌─────────────────────┬────────────┬──────┬─────────────┬──────────────────────┬──────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-master │ 10.0.16.12 │ 11 │ 0 │ Down │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-slave │ 10.0.16.12 │ 12 │ 0 │ Master, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog │ 10.0.16.12 │ 3306 │ 0 │ Binlog Relay, Runnin │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog-slave-1 │ 10.0.16.12 │ 13 │ 0 │ Slave, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog-slave-2 │ 10.0.16.12 │ 14 │ 0 │ Slave, Running │ 0-1-7346 │ └─────────────────────┴────────────┴──────┴─────────────┴──────────────────────┴──────────
查看 slave2 信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.16.12 Master_User: maxscale_binlog Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 768 Relay_Log_File: relay-bin.000003 Relay_Log_Pos: 379 Relay_Master_Log_File: mariadb-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
查看 maxscale 信息
1 2 3 4 5 6 7 8 9 10 11 12 13 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.16.12 Master_User: maxscale_binlog Master_Port: 12 Connect_Retry: 1 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 768 Relay_Log_File: Relay_Log_Pos: Relay_Master_Log_File: Slave_IO_Running: Yes Slave_SQL_Running: Yes
从 slave1 写入数据后查看
slave2
1 2 3 4 5 6 7 8 9 10 11 12 13 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.16.12 Master_User: maxscale_binlog Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 987 Relay_Log_File: relay-bin.000003 Relay_Log_Pos: 379 Relay_Master_Log_File: mariadb-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
maxscale
1 2 3 4 5 6 7 8 9 10 11 12 13 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.16.12 Master_User: maxscale_binlog Master_Port: 12 Connect_Retry: 1 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 987 Relay_Log_File: Relay_Log_Pos: Relay_Master_Log_File: Slave_IO_Running: Yes Slave_SQL_Running: Yes
可以发现完全实现自动切换,并且 slave 都通过 maxscal 同步 binlog
7.1 master 自动恢复 启动 master
1 2 3 4 5 6 7 8 9 10 11 12 13 ┌─────────────────────┬────────────┬──────┬─────────────┬──────────────────────┬──────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-master │ 10.0.16.12 │ 11 │ 0 │ Slave, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-slave │ 10.0.16.12 │ 12 │ 0 │ Master, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog │ 10.0.16.12 │ 3306 │ 0 │ Binlog Relay, Runnin │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog-slave-1 │ 10.0.16.12 │ 13 │ 0 │ Slave, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog-slave-2 │ 10.0.16.12 │ 14 │ 0 │ Slave, Running │ 0-1-7346 │ └─────────────────────┴────────────┴──────┴─────────────┴──────────────────────┴──────────
切换节点
maxctrl call command mariadbmon switchover MariaDB-Monitor test-master test-slave
1 "\u001b[32mOK\u001b[39m"
1 2 3 4 5 6 7 8 9 10 11 12 13 ┌─────────────────────┬────────────┬──────┬─────────────┬──────────────────────┬──────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-master │ 10.0.16.12 │ 11 │ 0 │ Master, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-slave │ 10.0.16.12 │ 12 │ 0 │ Slave, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog │ 10.0.16.12 │ 3306 │ 0 │ Binlog Relay, Runnin │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog-slave-1 │ 10.0.16.12 │ 13 │ 0 │ Slave, Running │ 0-1-7346 │ ├─────────────────────┼────────────┼──────┼─────────────┼──────────────────────┼──────────┤ │ test-binlog-slave-2 │ 10.0.16.12 │ 14 │ 0 │ Slave, Running │ 0-1-7346 │ └─────────────────────┴────────────┴──────┴─────────────┴──────────────────────┴──────────
7.2 尝试断开 slave 节点后添加数据后也会自动同步,因为简单就不演示了 
