主从同步分析参考: MySQL 主从同步分析 | 云原生基站

因为我们环境 MySQL 是 5.6 的,主从同步还是会有延迟的,5.7 会解决这问题

1 环境规划

ip 端口 名称
10.1.1.1 5.6.51 3306 Master /var/lib/mysql/binlog/
10.1.1.2 5.6.51 3306 Slave1 /var/lib/mysql/binlog/
10.1.1.3 5.6.51 3306 Slave2 /var/lib/mysql/binlog/

2 搭建 mysql

1
2
5.6yum源:rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
5.7yum源:rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

这里注意下,mysql 版本

  • 5.6: I/O thread 同步并发线程是以库级别并行的,也就是说两个库可以并行两个线程,三个库可以并行三个线程,但是注意一个库不要开启并行,影响性能.而且就算开启也会存在 slave 同步延迟
  • 5.7: I/O thread 同步并发线程可以做到按行级别并行,线程数量一般与 CPU 数量一样,可以做到 slave 节点无延迟,slave_parallel_workers=4,slave_parallel_type=LOGICAL_CLOCK,这两个参数一定要加上

2.1 安装 mysql

1
2
sudo yum clean all
sudo yum -y install mysql-community-server

2.2 添加 binlog 目录

1
2
mkdir -p /var/lib/mysql/binlog/
sudo chown mysql:mysql /var/lib/mysql/binlog/

2.3 修改配置文件

配置特殊说明：如果 MySQL5.7 启用并行复制(MTS)却没开启事务 ID(GTID)需要开启 slave_preserve_commit_order 来保证从库与主库提交的顺序完全一致。但是使用 xtrabackup 备份可能会造成备份锁

Master1：（因为要切换主从，配置基本跟 slave 配置一样）

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
# 服务配置
serveMTSr-id=1 # 要唯一slave_preserve_commit_order
log_slave_updates=1 # 开启此参数才可以切换master
report-host=10.122.132.124   # 返回给master的地址
report_port=3306       # 返回给master的端口
slave_net_timeout=8  # 与master握手超时时间
master_info_repository=table  # 主节点信息写表里
relay_log_info_repository=table  # slave信息写表里

# binlog配置
binlog_row_image=FULL
binlog_format=ROW # binlog日志格式
log-bin=/var/lib/mysql/binlog/mysql-bin.log # binlog日志文件
expire_logs_days=30 # binlog过期清理时间
max_binlog_size=300m # binlog每个日志文件大小
binlog_cache_size=12m # binlog缓存大小
max_binlog_cache_size=512m # 最大binlog缓存大小

# 事务ID(GTID)配置
gtid-mode = ON
enforce-gtid-consistency = ON

# 主从并行复制(MTS)配置
relay_log_recovery=1 # 数据库启动后立即启动自动relay log恢复
slave_parallel_workers=4 # 设置I/O thread同步并发线程数量,一般与CPU数量一样 
innodb_flush_log_at_trx_commit=2 # 每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。
sync_binlog=20 # 当每进行20次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
slave_parallel_type=LOGICAL_CLOCK   # 设置并行同步粒度,库级别或者表级别,MySQL5.7支持的参数

2.3.1 mysql slave 配置文件

Slave1,Slave2 上执行

Slvae2：（因为要切换主从，配置基本跟 master 配置一样）

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
# 服务配置
serveMTSr-id=2 # 要唯一slave_preserve_commit_order
log_slave_updates=1 # 开启此参数才可以切换master
report-host=10.122.132.124   # 返回给master的地址
report_port=3306       # 返回给master的端口
slave_net_timeout=8  # 与master握手超时时间
master_info_repository=table  # 主节点信息写表里
relay_log_info_repository=table  # slave信息写表里

# binlog配置
binlog_row_image=FULL
binlog_format=ROW # binlog日志格式
log-bin=/var/lib/mysql/binlog/mysql-bin.log # binlog日志文件
expire_logs_days=30 # binlog过期清理时间
max_binlog_size=300m # binlog每个日志文件大小
binlog_cache_size=12m # binlog缓存大小
max_binlog_cache_size=512m # 最大binlog缓存大小
# 事务ID(GTID)配置
gtid-mode = ON
enforce-gtid-consistency = ON

# 主从并行复制(MTS)配置
relay_log_recovery=1 # 数据库启动后立即启动自动relay log恢复
slave_parallel_workers=4 # 设置I/O thread同步并发线程数量,一般与CPU数量一样 
innodb_flush_log_at_trx_commit=2 # 每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。
sync_binlog=20 # 当每进行20次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
slave_parallel_type=LOGICAL_CLOCK   # 设置并行同步粒度,库级别或者表级别,MySQL5.7支持的参数

Slvae3：（不需要主从切换）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 服务配置
serveMTSr-id=3 # 要唯一slave_preserve_commit_order
log_slave_updates=1 # 开启此参数才可以切换master
report-host=10.122.132.124   # 返回给master的地址
report_port=3306       # 返回给master的端口
slave_net_timeout=8  # 与master握手超时时间
master_info_repository=table  # 主节点信息写表里
relay_log_info_repository=table  # slave信息写表里

# binlog配置
binlog_row_image=FULL
binlog_format=ROW # binlog日志格式
log-bin=/var/lib/mysql/binlog/mysql-bin.log # binlog日志文件
expire_logs_days=30 # binlog过期清理时间
max_binlog_size=300m # binlog每个日志文件大小
binlog_cache_size=12m # binlog缓存大小
max_binlog_cache_size=512m # 最大binlog缓存大小

启动

1
sudo systemctl start mysqld

初始化账户

1
mysql_secure_installation

2.4 添加账户

1
2
3
4
5
6
7
8
所有mysql实例添加的账户
CREATE USER 'orchestrator'@'%' IDENTIFIED BY 'qweasd';
GRANT SUPER, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT, RELOAD ON *.* TO 'orchestrator'@'%';
GRANT SELECT ON meta.* TO 'orchestrator'@'%';   #如果需要伪GTID,需要导入sql
GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'%';   # 允许读取mysql主从信息,提前设置把信息存到表里
GRANT DROP ON _pseudo_gtid_.* to 'orchestrator'@'%';   #增加更新伪gtid权限
刷新权限
FLUSH PRIVILEGES;

2.5 创建拓扑信息库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP TABLE IF EXISTS `cluster`;
CREATE TABLE `cluster`  (
  `anchor` tinyint(4) NOT NULL,
  `cluster_name` varchar(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `cluster_domain` varchar(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `data_center` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`anchor`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `cluster` VALUES (1, 'test', 'cc', 'TJ');  # 根据需求自定义
SET FOREIGN_KEY_CHECKS = 1;

DROP TABLE IF EXISTS `heartbeat_view`;
CREATE TABLE `heartbeat_view`  (
  `absolute_lag` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;
INSERT INTO `heartbeat_view` VALUES (1);
SET FOREIGN_KEY_CHECKS = 1;

如果开启伪 GTID 导入下面的 sql

https://github.com/caixin-ops/ops/blob/master/Orchestrator/pseudo-gtid.sql

source pseudo-gtid.sql

配置主从

查看 master 节点 binlog 文件名与位置

1
SHOW MASTER STATUS;
1
2
3
4
5
+------------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set              |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000005 | `2309  `  |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

两个 slave 节点执行:

1
CHANGE MASTER TO MASTER_HOST='10.1.1.1',MASTER_USER='orchestrator',MASTER_PASSWORD='qweasd',MASTER_PORT=3306,MASTER_LOG_FILE='mmysql-bin.000005',MASTER_LOG_POS=2309;

如果使用 gtid：CHANGE MASTER TO MASTER_HOST='10.1.1.1',MASTER_USER='orchestrator',MASTER_PASSWORD='qweasd',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;

查看状态:

1
SHOW SLAVE STATUS\G

3 添加新的 slave 节点

3.1 获取最新备份

因为每个公司项目环境不一样，此步骤掠过，备份与工具安装参考如何优雅的备份 MySQL 数据 | 云原生基站

3.1.1 解压

保证目录干净：

rm -rf /var/lib/mysql/*

cat bak_20220106.xb | xbstream -x -v -C /var/lib/mysql

innobackupex --decompress --remove-original /var/lib/mysql/

ll /var/lib/mysql

1
2
3
4
5
6
7
8
9
10
-rw-r--r-- 1 xopsadmin wheel 481 Dec 30 14:05 backup-my.cnf
-rw-r--r-- 1 xopsadmin wheel 76M Dec 30 14:05 ibdata1
drwxr-x--- 2 xopsadmin wheel 170 Dec 30 14:05 meta
drwxr-x--- 2 xopsadmin wheel 4.0K Dec 30 14:05 mysql
drwxr-x--- 2 xopsadmin wheel 4.0K Dec 30 14:05 performance_schema
drwxr-x--- 2 xopsadmin wheel  52 Dec 30 14:05 test
-rw-r--r-- 1 xopsadmin wheel  25 Dec 30 14:05 xtrabackup_binlog_info
-rw-r----- 1 xopsadmin wheel 141 Dec 30 13:58 xtrabackup_checkpoints
-rw-r--r-- 1 xopsadmin wheel 544 Dec 30 14:05 xtrabackup_info
-rw-r--r-- 1 xopsadmin wheel 2.5K Dec 30 14:05 xtrabackup_logfile

3.1.2 配置新实例

cat /etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
server-id=1 # 要唯一
binlog_row_image=FULL
binlog_format=ROW # binlog日志格式
log-bin=/var/lib/mysql/binlog/mysql-bin.log # binlog日志文件
max_binlog_size = 300m # binlog每个日志文件大小
binlog_cache_size = 12m # binlog缓存大小
max_binlog_cache_size = 512m # 最大binlog缓存大小
slave_sql_verify_checksum=0
log_slave_updates= 2 # 开启此参数才可以切换master
report-host=10.122.133.211  # 返回给master的地址
report_port=3306    # 返回给master的端口
slave_net_timeout=8 # 与master握手超时时间
master_info_repository=table  # 主节点信息写表里
relay_log_info_repository=table  # slave信息写表里
relay_log_recovery=1 # 数据库启动后立即启动自动relay log恢复
slave_parallel_workers=4 # 设置I/O thread同步并发线程数量,一般与CPU数量一样
innodb_flush_log_at_trx_commit=2 # 每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。
sync_binlog=20 # 当每进行20次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
\#slave_parallel_type=LOGICAL_CLOCK  # 设置并行同步粒度,库级别或者表级别,MySQL5.7支持的参数

恢复数据

innobackupex --defaults-file=/etc/my.cnf --apply-log /var/lib/mysql/

看到211230 14:06:41 completed OK!说明还原成功

修改权限

mkdir -p /var/lib/mysql/binlog/

chown mysql:mysql -R /var/lib/mysql/

启动

指定配置文件与数据库目录mysqld_safe --defaults-extra-file=/etc/my.cnf --datadir=/root/test/ --user=mysql

systemctl start mysqld

3.2 查看 binlog 位置

如果备份的是 master：

cat xtrabackup_binlog_info
mysql-bin.000009 79303734

如果备份的是 salve

cat xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000011’, MASTER_LOG_POS=55452573;

如果开启伪 gtid 执行：

mysql -uroot -pqweasd -e "DROP TABLE meta.pseudo_gtid_status;"

mysql -uroot -pqweasd -e "SOURCE pseudo-gtid.sql"

新 slave 节点执行:

1
CHANGE MASTER TO MASTER_HOST='10.1.1.1',MASTER_USER='orchestrator',MASTER_PASSWORD='qweasd',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=55452573;

如果使用 gtid：

1
CHANGE MASTER TO MASTER_HOST='10.1.1.1',MASTER_USER='orchestrator',MASTER_PASSWORD='qweasd',MASTER_PORT=3306,`MASTER_AUTO_POSITION=1;

启动主从同步

mysql -uroot -pqweasd -e "START SLAVE;"

查看状态:

SHOW SLAVE STATUS\G