MYSQL 基于GTID 实现主从复制

GTID 原理

 

环境

1. ubuntu 24
2. mysql 9.4.0

资料：

9.4 官方文档： https://dev.mysql.com/doc/refman/9.0/en/replication-multi-source-configuration.html

流程

1. 创建mysql 应用 （主库和从库）（这里使用docker）
2. 主库操作：
   1.  创建远程登录权限 - 这个账号用于数据读写  
   2. 创建专门用于复制的账户，授予此帐户复制所需的权限
   3. 使用创建的账号进行一次远程登录 （重要）
3. 从机中：
   1. 创建远程登录权限 - 这个账号用于数据读写
   2. 建立主从关系
   3. 开启监听

 

一些用到的命令

# 查看容器IP
docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' <container_name_or_id>

# MYSQL 中执行：

# 查看当前用户权限
SHOW GRANTS;
# 查看特定用户的权限
SHOW GRANTS FOR 'root'@'localhost';

# 在 主机上查看进程状态
SHOW PROCESSLIST;

# 查看 server_id
- SHOW VARIABLES LIKE 'server_id';

# 查看binlog 
- SHOW BINARY LOG STATUS;

# 查看GTID状态；
show variables like "%GTID%";

 

创建

1、创建容器

• mysql/master/conf 与 mysql/master/data 目录
• mysql/slave/conf 与 mysql/slave/data 目录

2、在conf 下新建 my.cnf 文件， 内容：

[mysqld]
# 服务器唯一id，默认值1
server-id=1

# 设置日志格式，默认值ROW, 开发环境使用STATEMENT
# STATEMENT: MySQL 会记录执行的 SQL 语句，而不是记录每个行的变化。这意味着在主服务器上执行的每个 SQL 语句都会被记录到二进制日志中，供从服务器重放。 缺点：不一致性风险：如果 SQL 语句依赖于非确定性函数（如 NOW()、RAND() 等），在主从服务器上执行的结果可能不同； 某些 SQL 语句无法使用 STATEMENT 格式进行复制。
# ROW：记录每一行的变化，确保在主从服务器上数据一致，但会占用更多空间。
# MIXED:自动选择使用 STATEMENT 或 ROW 格式，根据具体的 SQL 语句类型来决定。
binlog_format=ROW

# 二进制日志名，默认binlog
# log-bin=binlog
# 开启GTID
gtid-mode=on
enforce-gtid-consistency=on   # #强制gtid一致性，开启后对特定的create table不支持

# 设置需要复制的数据库，默认复制全部数据库
binlog-do-db=tfdata
# 设置不需要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema

# 设置最大连接数
max_connections = 10000

启动服务后确认GID 是否开启成功：  ON 成功；  OFF 未开启

SHOW GLOBAL VARIABLES LIKE 'gtid_mode';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+

 

3、创建应用 - 主机与从机

docker run -d -p 3307:3306 -v /home/tf/mysql/master/conf:/etc/mysql/conf.d -v /home/tf/mysql/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql-master mysql:9.4.0
docker run -d -p 3308:3306 -v /home/tf/mysql/slave/conf:/etc/mysql/conf.d -v /home/tf/mysql/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql-slave1 mysql:9.4.0

 

4、mysql -uroot -p -- 可选操作
- pwd: 123456
# caching_sha2_password 新版使用caching_sha2_password 替换了mysql_native_password
# 可以通过 show plugins; 查看当前版本自动加载的插件
- ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY '123456';

5、主机中： 创建远程登录权限 - 这个账号用于数据读写

CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'test'@'%';
FLUSH PRIVILEGES;

 

# 创建专门用于复制的账户，授予此帐户复制所需的权限

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;

 

6、从机中： 创建远程登录权限 - 这个账号用于数据读写

CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'test'@'%';
FLUSH PRIVILEGES;

 

7、一些额外操作 -- 重点

1. 主库执行 RESET BINARY LOGS AND GTIDS TO 1;    # 恢复 BINARY LOGS 到初始位置， 不然从机在创建复制通道时会引用错误位置
2. 使用test与 slave 分别进行一次远程连接 主机库，在主库中留下缓存（连接后binlog 会改变）
   1. 原因：遇到的问题：
      - Last_IO_Error: Error connecting to source 'test@172.17.0.2:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
   2. 解决：
      - 使用slave进行一次远程连接 主机库（连接后 binlog 会改变）
      - 连接之后binlog 会有所增加 通过 SHOW BINARY LOG STATUS; 可以查看到

8、建立主从关系

# 查看binlog
- SHOW MASTER STATUS; 换成了 SHOW BINARY LOG STATUS;
# 8.0 后命令变更为： FOR CHANNEL "slave" 中的slave为自定义通道名
- CHANGE REPLICATION SOURCE TO SOURCE_HOST="172.17.0.2", SOURCE_USER="slave", SOURCE_PASSWORD="123456", SOURCE_AUTO_POSITION=1 FOR CHANNEL "slave1";
- # 启动通道
- START REPLICA;
- 或
- START REPLICA FOR CHANNEL "slave";

- # 查看通道状态
- SHOW REPLICA STATUS \G;
- SHOW REPLICA STATUS FOR CHANNEL "slave"\G

 

关于REPLICA 的一些擦作：

# 停止通道
# 所有
STOP REPLICA;
# 单一
STOP REPLICA FOR CHANNEL "slave1";

 # 重置通道 GTID 与单纯的binlog 重置不同
 # 主机和从机中同时执行， 在online的项目中只需要在从机中执行 之后可以通过 SHOW BINARY LOG STATUS; 查看binlog Position位置
 - # TO 参数 指定 重置到 指定的 binary_log_file 索引文件 如： 有 binlog.000001 binlog.000002 binlog.000003 三个文件
 - RESET BINARY LOGS AND GTIDS;   # 删除所有， 只保留 binlog.000001
 - RESET BINARY LOGS AND GTIDS TO 1; # 2 和 3 将被删除 索引文件

# 查看 server_id
- SHOW VARIABLES LIKE 'server_id';

# 重置复制链接， 需要先stop replica;
RESET REPLICA ALL FOR CHANNEL "slave1";

# 删除复制链接信息： 
RESET REPLICA ALL;     旧版： RESET SLAVE ALL;

 

可能遇到的问题

1. 从库连接失败

   1.  Replica_IO_Running: Connecting
      Replica_SQL_Running: Yes

   2. Last_IO_Error: Error connecting to source 'slave@172.17.0.2:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
   3. 这个是因为你的slave 账户权限不对 或 没有执行 第7步 就先建立复制通过
   4. 解决：
      1. 检查账号权限 和 远程登录一次
      2. 从库中 重置通过， 重新建立
2. 连接成功， 但是复制通道开启失败

   1. Replica_IO_Running: Yes
      Replica_SQL_Running: No

   2. 这个基本上都是主库 binglog 索引位置发生改变
   3. 解决：
      1. SHOW BINARY LOG STATUS;
      2. 更新成最新binlog 索引位置

 

测试：

1. 主库中创建tfdata 库 之后插入数据
2. stop 从库， 主库插入数据， 之后重新启动从库， 数据自动同步