MySQL主从复制进阶（GTID复制，半同步复制）-EW帮帮网

1. GTID复制

主机名	ip	操作系统	mysql版本
mysql-master171	192.168.75.171/24	rhel7.9	mysql8.0.40
mysql-master172	192.168.75.172/24	rhel7.9	mysql8.0.40

1.1 GTID复制概念及核心特点

1.1.1 概念

GTID是MySQL用于主从复制的全局唯一事务标识机制，核心作用是简化复制拓扑管理，自定位同步位置，避免事务重复执行

1.1.2 GTID格式

server_uuid:     transation_id (服务器唯一UUID + 事务序号)

1.1.3 GTID特点

a.自动定位:无需手动指定binlog文件名及偏移量，复杂的拓扑(一主多从，级联)可以自动找到同步位置

b.避免重复执行:从库通过gtid_executed集合记录已执行事务，相同GTID直接忽略，保证数据一致

c.流程简短:主库提交事务时生成GTID并写入binlog，从库同步后校验GTID，执行未完成的事务，全程自动化

1.2 配置GTID复制

1.2.1 主库准备工作

a. 设置server-id值并开启binlog参数

[root@mysql-master171 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql   
socket=/data/mysql/mysql.sock  
server_id=171
log-bin=binlog

b. 重启主的数据库

[root@mysql-master171 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!

c. 查看数据库的编号

[root@mysql-master171 ~]# mysql -uroot -p123 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         171 |
+-------------+

d. 建立同步账号

[root@mysql-master171 ~]# mysql -uroot -p123
#创建mysql用户为rep，密码为rep123，并允许任何主机登录
mysql> create user 'rep'@'%' identified  by 'rep123';
#给rep用户设置权限
mysql> grant  replication slave on *.* to 'rep'@'%';

e. 查看权限

mysql>  show grants for 'rep'@'%';
+---------------------------------------------+
| Grants for rep@%                            |
+---------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rep`@`%` |
+---------------------------------------------+

1.2.2 从库准备工作

a. 设置server-id值

[root@mysql-master172]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=172

b. 重启数据库并登录

[root@mysql-master172 ~]# /etc/init.d/mysqld restart
#登录mysql
[root@mysql-master172 ~]# mysql -uroot -p123

c. 设定从库向主库同步

mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.75.171',   #指定主库的IP地址
SOURCE_USER='rep',              #从库连接主库的复制账号
SOURCE_PASSWORD='rep123',       #从库连接主库的复制账号密码
SOURCE_AUTO_POSITION=1,         #启动GTID自动定位同步位置
SOURCE_SSL=1;                   #启用SSL加密复制

d. 启动从库复制线程

mysql> start replica;

e. 检查状态

mysql> show replica status\G
Slave_IO_Running: Yes    #IO线程是否打开
Slave_SQL_Running: Yes   #SQL线程是否打开

注意:停止从库命令可以使用stop replica

1.2.3 主库和从库上启用GTID模式

a. 检查GTID复制的核心前置开关

mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| OFF                        |
+----------------------------+

b. 确定GTID模式复制的兼容性

#查看日志中是否有警告，如果有警告说明应用和GTID复制不兼容
mysql> set global enforce_gtid_consistency=warn;

c. 启用GTID强制性检查，防止GTID不兼容的语句导致复制失败

mysql> set global enforce_gtid_consistency=on;

d. 查看GTID复制功能是否启用

mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF         |
+-------------+

f. 将主和从的gtid_mode一步一步从OFF到OFF_PERMISSIVE到ON_PERMISSIVE到ON

mysql> set global gtid_mode=OFF_PERMISSIVE;
mysql> set global gtid_mode=ON_PERMISSIVE;

注意:不能一步到位，这样子会报错

mysql> set global gtid_mode=on;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

g. 在主库和从库上等待所有匿名事务复制完成(该变量为0)

mysql> show status like 'Ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+

h. 启用GTID模式

mysql> set global gtid_mode=ON;

1.2.4 测试

a. 主库插入数据

mysql> create database jeams;
mysql> use jeams;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> show master status \G
*************************** 1. row ***************************
             File: binlog.000005
         Position: 811
     Binlog_Do_DB:
 Binlog_Ignore_DB: information_schema,mysql,test
Executed_Gtid_Set: a235ccb1-732b-11f0-971f-000c29d6adc7:1-3

b. 查看主库的日志信息

mysql> show binlog events in "binlog.000005"\G

e. 查看主库已经执行过的GTID

mysql> select @@gtid_executed;
+------------------------------------------+
| @@gtid_executed                          |
+------------------------------------------+
| 84322256-b4e4-11ee-a11b-000c29f9fc20:1-3 |
+------------------------------------------+

f. 查看从库复制的完整状态信息

mysql> show replica status \G

g. 查看从库上的relaylog日志

mysql> show relaylog events in "slave1-relay-bin.000002"\G

h. 查看从库的数据是否同步

mysql> select * from jeams.t1;
+------+
| id   |
+------+
|    1 |
+------+

注意:如果需要永久添加GTID模式，要在主库和从库的/etc/my.cnf文件中写入，防止配置丢失

主库配置:

[root@mysql-mster171 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=171
log-bin=binlog
enforce_gtid_consistency=on
gtid_mode=ON

从库配置:

[root@mysql-mster172 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=172
log-bin=binlog
enforce_gtid_consistency=on
gtid_mode=ON

2. 半同步复制

主机名	ip	操作系统版本	mysql版本
mysql-master171	192.168.75.171	rhel7.9	mysql8.0.40
mysql-master172	192.168.75.172	rhel7.9	mysql8.0.40

2.1 概念及核心特点

2.1.1 概念

主库向客户端返回事务提交成功的响应前，必须要保证至少一个从库已接收并写入该事务的binlog日志到本地relay log 中

具体流程：主库执行事务并写入binlog中，会堵塞等待从库的确认(ACK),直到至少一个从库发送“已接收并写入该事务的所有binlog到relay log”，主库才向客户端返回“提交成功”；若等待超时，则会自动降级为异步复制，待异常修复后恢复半同步模式

2.1.2 核心特点

a.双向启用:主库和从库必须同时开启半同步复制功能，从库会主动告知主库自己的半同步配置状态

b.提交堵塞和等待:主库提交事务时，会堵塞等待至少一个从库的确认，确认内容为从库已接受该事务得所有binlog并写入relay log

c.超时降级:若主库等待超时一直未收到确认，会自动降级为异步复制，待异常修复后恢复半同步

d.数据一致性:MySQL5.7及以上默认得after_sync模式下，主库会在事务提交前等待从库确认，减少主库崩溃时的数据丢失风险

2.2 半同步复制配置

2.2.1 主库准备工作

a. 设置server-id值并开启binlog参数

[root@mysql-master171 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql   
socket=/data/mysql/mysql.sock  
server_id=171
log-bin=binlog

b. 重启数据库

[root@mysql-master171 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!

c. 查看数据库的编号

[root@mysql-master171 ~]# mysql -uroot -p123 -e 'select @@server_id;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         171 |
+-------------+

d. 建立同步账号

[root@mysql-master171 ~]# mysql -uroot -p123
#创建mysql用户为rep，密码为rep123，并允许任何主机登录
mysql> create user 'rep'@'%' identified  by 'rep123';
#给rep用户设置权限
mysql> grant  replication slave on *.* to 'rep'@'%';

e. 查看权限

mysql>  show grants for 'rep'@'%';
+---------------------------------------------+
| Grants for rep@%                            |
+---------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `rep`@`%` |
+---------------------------------------------+

2.2.2 从库准备工作

a.设置server-id值

[root@mysql-master172]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=172

b. 重启数据库

[root@mysql-master172 ~]# /etc/init.d/mysqld restart
#登录mysql
[root@mysql-master172 ~]# mysql -uroot -p123

c. 设定从库向主库同步

mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.75.171',   #指定主库的IP地址
SOURCE_USER='rep',              #从库连接主库的复制账号
SOURCE_PASSWORD='rep123',       #从库连接主库的复制账号密码
SOURCE_AUTO_POSITION=1,         #启动GTID自动定位同步位置
SOURCE_SSL=1;                   #启用SSL加密复制

d. 启用从库复制线程

mysql> start replica;

e. 检查状态

mysql> show replica status\G
Slave_IO_Running: Yes    #IO线程是否打开
Slave_SQL_Running: Yes   #SQL线程是否打开

2.2.3 主库半同步复制配置

a. 安装半同步复制的主库插件

mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';

b. 查看插件是否成功

mysql> show plugins;

c. 开启此开关

mysql> set global rpl_semi_sync_source_enabled=1;

d. 设置主等待从回复ACK超时时间为3秒，默认是10秒

mysql> set global rpl_semi_sync_source_timeout=3000;

e. 查看半同步复制主库的配置参数

mysql> show variables like 'rpl_semi_sync_source%';
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_source_enabled                | ON         |  #半同步复制是否启动
| rpl_semi_sync_source_timeout                | 3000       |
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1          |  #指主等待几个slave返回同步复制的ACK信号后提交
| rpl_semi_sync_source_wait_no_replica        | ON         |  #为off时，只要主库发现Rpl_semi_sync_master_clients小于rpl_semi_sync_master_wait_for_slave_count，则半同步立即转为异步模式；为on时，在无事务提交的空闲时间里，即使主库发现Rpl_semi_sync_master_clients小于rpl_semi_sync_master_wait_for_slave_count也不会做任何调整，只要保证在事务超时之前，主库收到大于等于rpl_semi_sync_master_wait_for_slave_count值的ACK应答数量，主库就一直保持在半同步模式，如果在事务提交阶段超时，半同步才会转为异步模式。
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+

f. 查看半同步复制主库的运行状态

mysql> show status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_source_clients               | 1     | #支持和注册半同步复制的已连slave数
| Rpl_semi_sync_source_net_avg_wait_time     | 0     | #废弃
| Rpl_semi_sync_source_net_wait_time         | 0     | #废弃
| Rpl_semi_sync_source_net_waits             | 0     | #废弃
| Rpl_semi_sync_source_no_times              | 0     | #master关闭半同步复制的次数
| Rpl_semi_sync_source_no_tx                 | 0     | #master等待超时的次数
| Rpl_semi_sync_source_status                | ON    | #半同步复制是否开启
| Rpl_semi_sync_source_timefunc_failures     | 0     | #调用gettimeofday()等时间函数失败的次数
| Rpl_semi_sync_source_tx_avg_wait_time      | 0     | #master花在每个事务上的平均等待时间
| Rpl_semi_sync_source_tx_wait_time          | 0     | #事务总的提交等待时间
| Rpl_semi_sync_source_tx_waits              | 0     | #master成功提交的次数
| Rpl_semi_sync_source_wait_pos_backtraverse | 0     | #主库event binary位置低于之前等待event的总次数，当事务开始等待回复的顺序与其二进制日志事件的写入顺序不同时就会发生该情况
| Rpl_semi_sync_source_wait_sessions         | 0     | #当前会话等待从库确认接受的个数，可以用来确认分析会话是否存在由于等待从库确认而堵塞的情况
| Rpl_semi_sync_source_yes_tx                | 0     | #master成功接收到slave的回复的次数
+--------------------------------------------+-------+

2.2.4 从库的半同步复制配置

a. 安装半同步复制的从库插件

mysql> INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';

b. 从库上启用半同步复制功能

mysql> SET GLOBAL rpl_semi_sync_replica_enabled = 1;

c. 查看半同步复制从库的配置参数

mysql> show variables like 'rpl_semi%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| rpl_semi_sync_replica_enabled     | ON    |
| rpl_semi_sync_replica_trace_level | 32    |
+-----------------------------------+-------+

d. 查看插件是否加载成功

mysql> show plugins;

e. 重启从库的IO线程

mysql> STOP REPLICA IO_THREAD;
mysql> START REPLICA IO_THREAD;

f. 查看半同步复制从库的状态

mysql> show status like 'rpl_semi%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_replica_status | ON    |
+------------------------------+-------+

2.2.5 测试

a. 主上创建一张表

mysql> use jeams
mysql> create table t1(id int);

b. 查看主库通过半同步复制成功提交的事务总数

mysql> show status like 'Rpl_semi_sync_source_yes_tx';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_source_yes_tx | 1     | #master成功接收到slave的回复的次数
+-----------------------------+-------+

c. 模拟故障

#停止从库的IO线程
mysql> stop REPLICA io_thread;
#从库上全局关闭半同步复制功能
mysql> set global rpl_semi_sync_replica_enabled=0;
#查看从库当前半同步复制运行状态
mysql>  show status like 'rpl_semi_sync_replica_status';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_replica_status | OFF   |
+------------------------------+-------+

d. 主库上创建表，可以看到一个等大的超时时间

mysql> insert into t1 values(1);
Query OK, 1 row affected (3.00 sec)

e. 发现主库关闭了半同步复制

mysql> show status like 'rpl_semi_sync_source_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_source_status | OFF   |
+-----------------------------+-------+

注意:如果需要永久添加半同步复制，请在主库和从库的/etc/my.cnf文件中写入，以防配置丢失

主库配置：

[root@mysql-mster171 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=171
log-bin=binlog
#启用半同步主库插件
plugin-load = "rpl_semi_sync_master=semisync_master.so"
#开启主库半同步功能
rpl_semi_sync_master_enabled = 1
#设置主库等待从库ACK的超时时间为3秒
rpl_semi_sync_master_timeout = 3000

从库配置：

[root@mysql-mster172 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=172
log-bin=binlog
#启用半同步主库插件
plugin-load = "rpl_semi_sync_master=semisync_master.so"
#开启主库半同步功能
rpl_semi_sync_master_enabled = 1
#设置主库等待从库ACK的超时时间为3秒
rpl_semi_sync_master_timeout = 3000

3. Mysql初始化

3.1 停止mysql

[root@mysql-master171 ~]# /etc/init.d/mysqld  stop

3.2 删除之前的mysql主从配置

[root@mysql-master171 ~]# rm -rf /data/mysql/*

3.3 数据库初始化建立mysql基本数据，注意在输出中查看临时密码

[root@mysql-master171 ~]# mysqld --initialize --user=mysql

3.4 启动数据库

[root@mysql-master171 ~]# /etc/init.d/mysqld  start
Starting MySQL.Logging to '/data/mysql/master.err'.
. SUCCESS!

3.5 修改数据库密码，新密码为123

[root@mysql-master171 ~]# mysqladmin -uroot -p password  '123'

3.6 使用新密码登录数据库

[root@mysql-master171 ~]# mysql -uroot -p123
mysql>

3.7 验证

#在两个库查看有没有从库，验证一下
mysql> show replicas;

MySQL主从复制进阶（GTID复制，半同步复制）

1. GTID复制

1.1 GTID复制概念及核心特点

1.1.1 概念

1.1.2 GTID格式

1.1.3 GTID特点

1.2 配置GTID复制

1.2.1 主库准备工作

a. 设置server-id值并开启binlog参数

b. 重启主的数据库

c. 查看数据库的编号

d. 建立同步账号

e. 查看权限

1.2.2 从库准备工作

a. 设置server-id值

b. 重启数据库并登录

c. 设定从库向主库同步

d. 启动从库复制线程

e. 检查状态

1.2.3 主库和从库上启用GTID模式

a. 检查GTID复制的核心前置开关

b. 确定GTID模式复制的兼容性

c. 启用GTID强制性检查，防止GTID不兼容的语句导致复制失败

d. 查看GTID复制功能是否启用

f. 将主和从的gtid_mode一步一步从OFF到OFF_PERMISSIVE到ON_PERMISSIVE到ON

g. 在主库和从库上等待所有匿名事务复制完成(该变量为0)

h. 启用GTID模式

1.2.4 测试

a. 主库插入数据

b. 查看主库的日志信息

e. 查看主库已经执行过的GTID

f. 查看从库复制的完整状态信息

g. 查看从库上的relaylog日志

h. 查看从库的数据是否同步

2. 半同步复制

2.1 概念及核心特点

2.1.1 概念

2.1.2 核心特点

2.2 半同步复制配置

2.2.1 主库准备工作

a. 设置server-id值并开启binlog参数

b. 重启数据库

c. 查看数据库的编号

d. 建立同步账号

e. 查看权限

2.2.2 从库准备工作

a.设置server-id值

b. 重启数据库

c. 设定从库向主库同步

d. 启用从库复制线程

e. 检查状态

2.2.3 主库半同步复制配置

a. 安装半同步复制的主库插件

b. 查看插件是否成功

c. 开启此开关

d. 设置主等待从回复ACK超时时间为3秒，默认是10秒

e. 查看半同步复制主库的配置参数

f. 查看半同步复制主库的运行状态

2.2.4 从库的半同步复制配置

a. 安装半同步复制的从库插件

b. 从库上启用半同步复制功能

c. 查看半同步复制从库的配置参数

d. 查看插件是否加载成功

e. 重启从库的IO线程

f. 查看半同步复制从库的状态

2.2.5 测试

a. 主上创建一张表

b. 查看主库通过半同步复制成功提交的事务总数

c. 模拟故障

d. 主库上创建表，可以看到一个等大的超时时间

e. 发现主库关闭了半同步复制

3. Mysql初始化

3.1 停止mysql

3.2 删除之前的mysql主从配置

3.3 数据库初始化建立mysql基本数据，注意在输出中查看临时密码

3.4 启动数据库

3.5 修改数据库密码，新密码为123

3.6 使用新密码登录数据库

3.7 验证

网站公告