mysql数据库重配置主从配置

背景

线上一套环境之前配置了主从从配置，因为机器故障，进行了一次迁移，之前配置主从配置的时候，从节点配置的时候没有配置gtid模式，采用的是position模式，这个有个问题就是主从每次切换需要重新找到position，否则就会丢失切换区间的数据

本次故障后，进行一次完整导入，然后配置gtid模式，后续再切换的时候，就直接配置即可

注意这个是需要全量重配置的时候需要操作的步骤，如果配置了gtid模式的主备，后续再切换的时候，直接重新修改change master即可进行同步了，如果出现无法同步的情况，才需要本文档内的操作

配置方法

主数据库导出

1
mysqldump -u root  -p  --single-transaction    --master-data=2 --all-databases  --set-gtid-purged=ON|gzip > all.sql.gz

参数说明：

  • single-transaction 这个是避免锁表，备份的时候能够不影响正常的写入
  • master-data 这个会记录position和transation，这样万一gtid auto模式配置不起来的时候，也可以回退为position模式进行配置主备
  • set-gtid-purged 设置为ON 是在导出的里面记录gtid的信息，如果不记录的话，mysql就不知道从哪里开始同步了，就会缺失数据
  • gzip 这个是开启了管道模式，这个边导出边压缩，这个会节省非常多的时间和空间，这里一次就压缩成了压缩文件，直接scp其它机器也快

上面的命令就完整的导出了一份主数据库

导出的时候观察过对性能的影响，这个影响很小，在线可以直接操作

备数据库导入

备份的节点的数据库进行一个备份

1
2
3
4
mv /share/mysql /share/mysql20250603
mkdir /share/mysql/data
mkdir /share/mysql/log
chown mysql:mysql -R /share/mysql

修改mysql的配置文件

1
2
3
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
skip-grant-tables

这里添加skip-grant-tables就是忽略了了权限，不用密码也可以操作mysql，这个时候我们的数据库是空数据库，我们导入的是全量mysql数据库，包括用户名密码等等，所以用这个模式下导入，等完全导入后，再去掉这个配置，重启下mysql

1
systemctl start mysqld

导入完整数据库

1
2
gunzip -d all.sql.gz
mysql -uroot -p < full.sql

导入完成后，就注释掉数据库skip-grant-tables这个配置然后重启mysqld进程

配置主从同步

1
2
change master to master_host='192.168.0.101',master_port=3306,master_user='username',master_password='password',master_auto_position=1;
start slave;

这里就配置了gtid模式的同步，正常情况下这里是没问题的，这里我们备选一个postion模式的方法

1
tail -n 35  full.sql

这个会记录地址

1
2
change master to master_host='192.168.0.101',master_port=3306,master_user='username',master_password='password',master_log_file='master-bin.000003',master_log_pos=7354;
start slave;

上面的position就是从上面的sql语句里面拿到的，注意这个只有在上面的配置失败的情况下，才启用这个模式的

注意配置同步后，内部会根据gtid进行一个追赶，慢慢的把数据同步过来，我们通过查询状态看是否同步了

1
show slave status \G;
  • Master_Log_File 和 Relay_Master_Log_File 所指向的文件必须一致
  • Read_Master_Log_Pos 和 Exec_Master_Log_Pos 位置也要一致才行
  • Slave_SQL_Running_State: 显示为wait 中继日志的sql语句已经全部执行完毕
    检查完上面三项配置以后，我们可以判断是否同步了，注意因为数据库持续在写，所以，看到接近即可

上面的检查完成后，我们就能判断完成了同步配置，但是数据是否真的一致，还需要再用工具确认下

数据比对

下载比对工具

我们需要一个工具，这个工具从tidb的工具库里面提取的，这个是另外一套数据库里面的工具，用于比较tidb或者mysql的，这个比pt-table-sync这个工具更好操作，也可以控制线程的压力

1
2
3
4
5
6
7
8
9
10
11
[root@lab102 ~]# curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 5781k  100 5781k    0     0   9.8M      0 --:--:-- --:--:-- --:--:--  9.8M
Successfully set mirror to https://tiup-mirrors.pingcap.com
Detected shell: bash
Shell profile:  /root/.bash_profile
Installed path: /root/.tiup/bin/tiup
===============================================
Have a try:     tiup playground
===============================================

随便找一台机器安装下这个tiup的工具

1
2
[root@lab102 ~]# tiup  install sync-diff-inspector
download https://tiup-mirrors.pingcap.com/sync-diff-inspector-v9.0.0-beta.1-linux-amd64.tar.gz 59.07 MiB / 59.07 MiB 100.00% 46.96 MiB/s

安装sync-diff-inspector这个工具

1
2
[root@lab102 ~]# ll -hl /root/.tiup/components/sync-diff-inspector/v9.0.0-beta.1/sync_diff_inspector
-rwxr-xr-x 1 root root 145M Jun  3 10:12 /root/.tiup/components/sync-diff-inspector/v9.0.0-beta.1/sync_diff_inspector

安装完成后，从这个路径把这个二进制拷贝出来，这个是直接可以运行的二进制

配置比对工具

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
[root@localhost sync]# cat config.toml
# Diff Configuration.

######################### Global config #########################

# 检查数据的线程数量，上下游数据库的连接数会略大于该值。
check-thread-count = 1

# 如果开启，若表存在不一致，则输出用于修复的 SQL 语句。
export-fix-sql = true

# 只对比数据而不对比表结构，该配置项目前为实验特性，不建议在生产环境中使用。
check-data-only = false

# 只对比表结构而不对比数据。
check-struct-only = false

# 如果开启，会跳过校验上游或下游不存在的表。
skip-non-existing-table = true

######################### Datasource config #########################
[data-sources]
[data-sources.master] # master 是该数据库实例唯一标识的自定义 id，用于下面 task.source-instances/task.target-instance 中
    host = "192.168.0.101"
    port = 3306
    user = "username"
    password = "password" # 设置连接上游数据库的密码，可为明文或 Base64 编码。

[data-sources.slave]
    host = "192.168.0.102"
    port = 3306
    user = "username"
    password = "password" # 设置连接下游数据库的密码，可为明文或 Base64 编码。

######################### Task config #########################
# 配置需要对比的*目标数据库*中的表
[task]
    # output-dir 会保存如下信息
    # 1 sql：检查出错误后生成的修复 SQL 文件，并且一个 chunk 对应一个文件
    # 2 log：sync-diff.log 保存日志信息
    # 3 summary：summary.txt 保存总结
    # 4 checkpoint：a dir 保存断点续传信息
    output-dir = "./output"
    # 上游数据库，内容是 data-sources 声明的唯一标识 id
    source-instances = ["master"]
    # 下游数据库，内容是 data-sources 声明的唯一标识 id
    target-instance = "slave"
    # 需要比对的下游数据库的表，每个表需要包含数据库名和表名，两者由 `.` 隔开
    # 使用 ? 来匹配任意一个字符；使用 * 来匹配任意；详细匹配规则参考 golang regexp pkg: https://github.com/google/re2/wiki/Syntax
    target-check-tables = ["mydata*.*"]

配置说明：
配置一个主的，配置一个备的，然后target-check-tables里面是配置的数据库表，这样的配置，支持通配符，check-thread-count这个是比较的线程，如果是一个没有访问的数据库，我们可以配置高一些，如果线上运行的环境，配置1即可，默认值是4，上面的配置很简单，复制过去修改即可

运行比对工具

1
sync_diff_inspector --config config.toml

运行这个就开始比较了，比较会实时输出日志，完成后有报告

1
2
3
summary.txt 里面是本次比对的总结
Time Cost: 3h51m14.039624976s
线上的200G左右的数据库比对一轮完成花费的是这个时间

还有个fix-on-name的目录，这个里面会把差异的sql预计输出的，把这个语句在备节点上面执行，就可以把差异的数据补上
如果按照上面的正常的操作下来，这个fix-on目录里面是空的，也就是完全一致的

上面的操作完成后，就完成了主备的重配置并且进行了检查

备注

基础mysql配置文件

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
[root@lab101 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]

#skip_grant_tables=1
pid-file        = /share/mysql/data/mysqld.pid  # pid文件所在目录
socket          = /share/mysql/data/mysqld.sock # 用于本地连接的socket套接字
datadir         = /share/mysql/data             # 数据文件存放的目录
#----------------General----------------
user                   = mysql
port                   = 3306
log-error              = /share/mysql/log/mysqld.log
default_authentication_plugin = mysql_native_password
# character_set_server   = utf8
max_allowed_packet     = 200000M
sort_buffer_size       = 4M
long_query_time        = 3
skip_name_resolve      = on
lower_case_table_names = 1
max_connections        = 6000

#----------------Replication----------------
# 设置MySQL服务器ID
server_id             = 1
# 开启二进制日志
log-bin                = master-bin
# 中继日志
relay_log=relay-log
# 二进制日志的格式为混合模式
binlog_format=mixed
# 忽略binlog
binlog-ignore-db=mysql,sys,information_schema,performance_schema
# 开启全局事务标识
gtid_mode=on
# 强制全局事务标识的一致性
enforce_gtid_consistency=on
# 半同步复制
#plugin-load-add=rpl_semi_sync_source=semisync_source.so
#rpl_semi_sync_source_enabled=1
# 启用只读属性
#read_only=ON

#----------------InnoDB Tablespaces----------------
innodb_file_per_table = 1

#----------------InnoDB Memory Allocation----------------
innodb_buffer_pool_size             = 6G
innodb_buffer_pool_instances        = 2

#慢查询sql日志设置
slow_query_log=1
slow_query_log_file=/share/mysql/log/mysqld_slow.log

[mysql]
socket=/share/mysql/data/mysqld.sock

[client]
socket=/share/mysql/data/mysqld.sock
系统管理
#数据库
mysql数据库重配置主从配置
https://zphj1987.com/2025/06/04/mysql数据库重配置主从配置/
作者
zphj1987
发布于
2025年6月4日
许可协议