MySQL5.7.43 GTID主从复制配置

引言

环境配置

主库 master1 配置

设置主库主配置文件

重启MySQL

**[

创建用户和授权并刷新

从库 slave2 配置

测试master1是否可以连接

设置从库主配置文件

重启MySQL

**[重启MySQL后

删除从库日志

配置连接主库

启动slave和查看slave

验证主从复制

总结

引言

为什么需要主从复制

  • 在业务复杂的系统中,有这么一个情景,有一句sql语句需要锁表,导致暂时不能使用读的服务,那么就很影响运行中的业务,使用主从复制,让主库负责写,从库负责读,这样,即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运作。
  • 做数据的热备
  • 架构的扩展。业务量越来越大,I/O访问频率过高,单机无法满足,此时做多库的存储,降低磁盘I/O访问的频率,提高单个机器的I/O性能。

什么是主从复制

​ MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。

主从复制原理

  • 在主库上把数据更改(DDL DML DCL)记录到二进制日志(Binary Log)中。
  • 备库I/O线程将主库上的日志复制到自己的中继日志(Re’lay Log)中。
  • 备库SQL线程读取中继日志的事件,将其重放到备库的数据库上。

主从复制方式,M是master主库,S是slave从库

  • M-S
  • M-M
  • M-S-S
  • M-M-S-S

接下来文章中主要介绍的是M-S GTID ,除了M-M-S-S和其他复制方式的时候有点区别,其他的的复制方式大同小异

GTID是一个基于原始mysql服务器生成的一个已经被成功执行的全局事务ID,它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器器上唯一,在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单,以及数据库一致性更可靠。

GTID复制模式原理参考文章:MySQL 基于GTID复制模式-运维小结 (完整篇)

环境配置

  • 准备好2台在Centos7中安装了MySQL 5.7.43的操作系统
  • 2台操作系统提前关闭防火墙和SElinux
  • 提前配置好/etc/hosts文件
  • 修改Centos7的主机名

参考文章

主库 master1 配置

设置主库主配置文件

1
2
3
4
5
6
vim /etc/my.cnf
在文件的末尾加入下面设置
log-bin
server-id 1
gtid_mode ON
enforce_gtid_consistency 1

重启MySQL

1
2
systemctl restart mysqld
重启失败的话就是上面配置文件的修改出错了,好好检查

1
mysql -uroot -p'密码'

创建用户和授权并刷新

这里的授权语句在MySQL5和MySQL8不同版本中会有变化,大家根据自己的MySQL版本选择正确的sql语句去授权

1
2
3
create user 'rep' '192.168.159.%' identified by '密码';
grant replication slave on *.* to ‘rep’ '192.168.159.%' identified by '密码';
flush privileges;

从库 slave2 配置

测试master1是否可以连接

1
2
mysql -hmaster1 -urep -p'123456'

连接失败的话可能是防火墙和selinux没有关闭,关闭后重新连接

设置从库主配置文件

1
2
3
4
5
6
vim /etc/my.cnf
将下面设置添加到文件末尾
log-bin
server-id 2
gtid_mode ON
enforce_gtid_consistency 1

重启MySQL

1
systemctl restart mysqld

重启MySQL后

1
2
3
systemctl restart mysqld
mysql -uroot -p'123456'

删除从库日志

1
reset slave all;

配置连接主库

当然通常在完成change master to命令初始化后是复制配置是自动管理的,会有自动恢复复制机制。在开源的低版本的MySQL中,从服务器在启动时,会检查 master.info 用于存储复制配置 和 relay-log.info 用于存储当前的复制状态 文件。如果此前配置过主从复制,并且还未执行 RESET SLAVE,从服务器就会根据这些信息自动尝试恢复复制。

但是在高版本的MySQL中例如MySQL8.0.36中,主从复制的状态信息默认存储在两个系统表中,而不再是之前版本里的文件 master.info 和 relay-log.info 。

这两个系统表分别是:

  • slave_master_info: 用于存储有关主服务器的连接信息。这些信息通常会在执行了 CHANGE MASTER TO 命令以定义主服务器的连接参数后被填写到此表中。

  • slave_relay_log_info: 存储从服务器当前复制状态的信息,包括当前正在应用的relay日志文件的名称和位置。

1
2
3
4
5
6
`-- 查询有关主服务器连接的信息
SELECT * FROM mysql.slave_master_info;

-- 查询有关当前从服务器复制状态的信息
SELECT * FROM mysql.slave_relay_log_info;
`

重要的是,这些表是内部维护的,通常你不需要直接操作或修改它们,因为这可能会导致复制的配置和状态不一致。如果复制出现问题,MySQL 提供了包括 SHOW SLAVE STATUS 在内的多个诊断命令,以及用于修复和配置复制环境的 CHANGE MASTER TO、START SLAVE、STOP SLAVE 等命令。

另外,了解存储复制信息对预防灾难发生是很重要的,这意味着如果你要迁移或备份从服务器的状态,你也需要考虑备份 mysql 数据库中的这些表。不过一般情况下,全数据库备份 例如使用 mysqldump --all-databases 会包含 mysql 数据库和相应的系统表。

1
2
3
4
5
 change master to
master_host 'master1',
master_user 'rep',
master_password '密码',
master_auto_position 1;

启动slave和查看slave

1
2
start slave;
show slave status\G

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
Replica_IO_State: Waiting for source to send event # 复制状态:等待主库发送事件
Source_Host: 192.168.0.104 # 主库地址
Source_User: repl # 主库复制用户
Source_Port: 3306 # 主库端口
Connect_Retry: 60 # 连接重试次数
Source_Log_File: 87503f057dd8-bin.000004 # 主库当前正在读取的日志文件
Read_Source_Log_Pos: 157 # 主库当前正在读取的日志位置
Relay_Log_File: e7df40b28cde-relay-bin.000005 # 从库当前正在写入的中继日志文件
Relay_Log_Pos: 387 # 从库当前正在写入的中继日志位置
Relay_Source_Log_File: 87503f057dd8-bin.000004 # 从库中继的主库日志文件名
Replica_IO_Running: Yes # 复制 I/O 进程是否正在运行
Replica_SQL_Running: Yes # 复制 SQL 进程是否正在运行
Replicate_Do_DB: # 需要复制的数据库
Replicate_Ignore_DB: # 需要忽略复制的数据库
Replicate_Do_Table: # 需要复制的表
Replicate_Ignore_Table: # 需要忽略复制的表
Replicate_Wild_Do_Table: # 需要通配符匹配复制的表
Replicate_Wild_Ignore_Table: # 需要通配符匹配忽略复制的表
Last_Errno: 0 # 最后一个错误编号
Last_Error: # 最后一个错误信息
Skip_Counter: 0 # 跳过的事件数量
Exec_Source_Log_Pos: 157 # 执行主库日志的位置
Relay_Log_Space: 1014 # 中继日志占用空间
Until_Condition: None # 复制停止条件
Until_Log_File: # 复制停止的日志文件
Until_Log_Pos: 0 # 复制停止的日志位置
Source_SSL_Allowed: No # 主库是否允许 SSL 连接
Source_SSL_CA_File: # 主库 SSL CA 文件路径
Source_SSL_CA_Path: # 主库 SSL CA 路径
Source_SSL_Cert: # 主库 SSL 证书
Source_SSL_Cipher: # 主库 SSL 加密方式
Source_SSL_Key: # 主库 SSL 密钥
Seconds_Behind_Source: 0 # 从库与主库之间的延迟
Source_SSL_Verify_Server_Cert: No # 主库 SSL 服务器证书验证方式
Last_IO_Errno: 0 # 最后一个 I/O 错误编号
Last_IO_Error: # 最后一个 I/O 错误信息
Last_SQL_Errno: 0 # 最后一个 SQL 错误编号
Last_SQL_Error: # 最后一个 SQL 错误信息
Replicate_Ignore_Server_Ids: # 忽略的主库 ID
Source_Server_Id: 1 # 主库服务器 ID
Source_UUID: 254752fe-eff5-11ee-92a5-0242c0a80068 # 主库 UUID
Source_Info_File: mysql.slave_master_info # 主库信息文件
SQL_Delay: 0 # SQL 延迟
SQL_Remaining_Delay: NULL # 剩余的 SQL 延迟
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates # 复制 SQL 进程状态
Source_Retry_Count: 86400 # 重试计数
Source_Bind: # 主库绑定地址
Last_IO_Error_Timestamp: # 最后一个 I/O 错误时间戳
Last_SQL_Error_Timestamp: # 最后一个 SQL 错误时间戳
Source_SSL_Crl: # 主库 SSL CRL 文件路径
Source_SSL_Crlpath: # 主库 SSL CRL 路径
Retrieved_Gtid_Set: # 检索到的 GTID 集合
Executed_Gtid_Set: # 执行的 GTID 集合
Auto_Position: 1 # 是否启用自动位置定位
Replicate_Rewrite_DB: # 重写复制数据库
Channel_Name: # 通道名称
Source_TLS_Version: # 主库 TLS 版本
Source_public_key_path: # 主库公钥路径
Get_Source_public_key: 1 # 获取主库公钥
Network_Namespace: # 网络命名空间

验证主从复制

总结

  • 主库和从库的主配置文件中2个server-id “不同的数字“
  • 做主从架构的时候若是使用克隆虚拟机需要在/var/lib/mysql/auto.cnf下面修改uuid,在MySQL中查看uuid show variables like ‘%server_uuid%’;
  • MySQL数据库的版本不同也会有问题,MySQL 8 的版本安全性相对较高,在做主从复制的时候要去修改一个表的数据,这里就不作过多介绍了
  • Centos7的防火墙和SELinux一定要关闭

更多关于MySQL主从复制的教程可以去从看看这篇文章MySQL主从复制保姆级教程