MySQL-主从复制

https://www.hyouit.com?aff=418

主从复制模式包括异步复制、半同步复制、增强半同步复制、同步复制和组复制等。主从复制的前提至少两台同版本的数据库实例，具有不同的server_id 和 server_uuid，时间同步，主库开启 binlog 日志，从库开启relaylog 日志。

1、异步复制

MYSQL 默认的复制方式，就是主库写入binlog日志后即可成功返回客户端，无须等待binlog日志传递给从库的过程。但这样一旦主库发生宕机，从库就有可能出现数据丢失的情况。

搭建过程：

1.1 主从环境

数据库主 master：

  • 数据库版本：8.0.24

  • IP：192.168.3.105

  • server_id=1

  • binlog 日志

数据库从 slave：

  • 数据库版本：8.0.24

  • IP：192.168.3.104

  • server_id=2

  • relaylog 日志

1.2 数据库主 master 配置

my.cnf 配置文件

[mysql@dbs-master ~]$ more .my.cnf 
[mysqld]
server_id=1
log_bin=/opt/mysql/binlog/mysql-bin
binlog_format=row

创建复制用户

mysql> create user repl@'%' identified with mysql_native_password by 'repl';
mysql> grant replication slave on *.* to repl@'%';

查看master记录的binlog日志文件和position

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      659 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1.3 数据库从 slave 配置

my.cnf 配置文件

server_id=2
relay_log=/opt/mysql/relaylog/mysql-relay
log_bin=/opt/mysql/binlog/mysql-bin
binlog_format=row
# 1、从库只开启log-bin功能，不添加log-slave-updates参数，从库从主库复制的数据不会写入log-bin日志文件里。
# 2、直接向从库写入数据时，是会写入log-bin日志的。
# 3、开启log-slave-updates参数后，从库从主库复制的数据会写入log-bin日志文件里。这也是该参数的功能。
log_slave_updates=1

从主库导入数据到从库，方法可参照数据备份恢复

启动主从复制

mysql> change master to master_host='192.168.3.105',master_user='repl',master_password='repl',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=659,master_connect_retry=10;

mysql> start slave;

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1.4 测试验证

在主库进行DDL和DML操作，在从库上验证数据是否同步

2、传统复制转换GTID复制

从库直接修改为GTID复制方式是不行的，因为gtid_mode参数值为off。

mysql> change master to master_host='192.168.3.105',master_user='repl',master_password='repl',master_port=3306,master_auto_position=1;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 cannot be executed because @@GLOBAL.GTID_MODE = OFF.

设置gtid_mode参数值为on，直接修改该参数为on也是不行的

在整个切换的过程中，需要注意两个参数：

第一个是gtid_mode参数

MySQL 5.7.6及更高版本中记录的事务可以是匿名的，也可以使用GTID。匿名事务依赖于二进制日志文件和位置来标识特定事务。GTID事务具有用于引用事务的唯一标识符。MySQL 5.7.6中添加的 OFF_PERMISSIVE和 ON_PERMISSIVE模式允许在拓扑中混合使用这些事务类型。现在有不同的模式：

  • OFF：新事务和复制事务都必须是匿名的。

  • OFF_PERMISSIVE：新交易是匿名的。复制的事务可以是匿名或GTID事务。

  • ON_PERMISSIVE：新交易是GTID交易。复制的事务可以是匿名或GTID事务。

  • ON：新事务和复制事务都必须是GTID事务。

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.

第二个参数是enforce_gtid_consistency。

enforce_gtid_consistency 可以配置 的值 是：

  • OFF：允许所有交易违反GTID一致性。

  • ON：不允许任何交易违反GTID一致性。

  • WARN：在MySQL 5.7.6中添加，允许所有事务违反GTID一致性，但在这种情况下会生成警告。

enforce_gtid_consistency 仅在语句进行二进制日志记录时才生效。如果在服务器上禁用了二进制日志记录，或者由于过滤器删除了语句而未将语句写入二进制日志，则不会对未记录的语句检查或强制执行GTID一致性。

传统复制转换为GTID复制方式配置过程：

主从库都需要做的配置

# 这个参数可以使得所有事物都允许违反GTID的一致性，但是会生成警告，通过这个方法，把那些不符合GTID一致性的事务给找出来，并修复。
mysql> set global enforce_gtid_consistency=warn;
# 这一步操作需要在处理完上面一步操作的基础上执行，从而确保所有的事务都不能违反GTID的一致性。
mysql> set global enforce_gtid_consistency=on;
# 表示新的事务是匿名的，同时允许复制的事务为GTID或者匿名事务
mysql> set global gtid_mode=off_permissive;
# 表示新的事务使用gtid，同时允许复制的事务为GTID或者匿名事务
mysql> set global gtid_mode=on_permissive;
# 在所有的从库上查询该状态，必须要为0才能开始下一步。表示的是已经标记为匿名的正在进行的事务数量，如果状态值为0，说明当前没有事务在等待被处理。如果这个值没有为0就开始操作，就会报错
mysql> show status like 'ongoing_anonymous_transaction_count';
# 开启GTID
mysql> set global gtid_mode=on;

my.cnf 中添加参数，永久生效

gtid_mode=on
enforce_gtid_consistency=on

开启gtid复制

mysql> stop slave;
mysql> change master to master_host='192.168.3.105',master_user='repl',master_password='repl',master_port=3306,master_auto_position=1;
mysql> start slave;

已经更新的gtid会记录系统表中

mysql> select * from gtid_executed;

3、半同步和增强半同步复制

MySQL5.5开始推出半同步复制，配置半同步复制需要先配置异步复制，超时后，切换回异步复制。rpl_semi_sync_master_wait_point 参数启动半同步复制，当值为AFTER_COMMIT时，为半同步复制，5.6版本默认值；当值为AFTER_SYNC时，为增强半同步复制，5.7版本默认值，5.6中无此模式。

  • 半同步复制，master将每个事务写入binlog ,传递到slave 刷新到磁盘(relay log)，同时主库提交事务。master等待slave 反馈收到relay log，只有收到ACK后master才将commit OK结果反馈给客户端。
    即主库commit时,先保证了binlog日志写入了从库中继日志后主库才提交binlog落盘OK给客户端.此方法影响了主库性能.

  • 增强半同步复制，master 将每个事务写入binlog , 传递到slave 刷新到磁盘(relay log)。master等待slave 反馈接收到relay log的ack之后，再提交事务并且返回commit OK结果给客户端。
    即使主库crash，所有在主库上已经提交的事务都能保证已经同步到slave的relay log中。此方法中存储引擎可以批量提交,降低了对主库性能的影响。

半同步复制配置过程

# 前提需要配置好异步主从复制，通过安装插件，设置参数，重启IO线程启动半同步复制
# 1、安装插件
# 主库：
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
# 从库：
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

# 2、设置参数
# 主库
mysql> set global rpl_semi_sync_master_enabled=on;
# 从库
mysql> set global rpl_semi_sync_slave_enabled=on;

添加到my.cnf配置文件中
[mysqld]
plugin_dir=/opt/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_master_wait_for_slave_count=1

# 3、重启从库的IO线程
mysql> stop slave io_thread;
mysql> start slave io_thread;

# 4、查看半同步复制配置状态
# 主库
mysql> show status like 'rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+
# 从库
mysql> show status like 'rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

# 5、主库监控半同步复制相关参数信息
mysql> show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
# Rpl_semi_sync_master_status：指示主服务器使用的是异步复制模式，还是半同步复制模式。
# Rpl_semi_sync_master_clients：显示有多少个从服务器配置成了半同步复制模式。
# Rpl_semi_sync_master_yes_tx：显示从服务器确认的成功提交数量。
# Rpl_semi_sync_master_no_tx：显示从服务器确认的不成功提交数量。

4、主从复制故障处理

# 主库
mysql> show processlist;
+----+-----------------+---------------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User            | Host                | db   | Command          | Time | State                                                         | Info             |
+----+-----------------+---------------------+------+------------------+------+---------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost           | NULL | Daemon           | 1517 | Waiting on empty queue                                        | NULL             |
|  8 | root            | localhost           | NULL | Query            |    0 | init                                                          | show processlist |
| 11 | repl            | 192.168.3.104:52912 | NULL | Binlog Dump GTID |  587 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+-----------------+---------------------+------+------------------+------+---------------------------------------------------------------+------------------+
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 9369f8c5-cf04-11ed-adc6-000c29c69312 |
+-----------+------+------+-----------+--------------------------------------+

# 从库
mysql> show slave status\G

# sql线程回放relaylog日志事件错误处理
# 1、跳过单个错误
stop slave;
set global sql_slave_skip_counter = 1;
start slave;

# 2、my.cnf 指定跳过错误
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

# 3、gtid 方式处理
Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1
注入空事物的方法：
stop slave;
set gtid_next='71bfa52e-4aae-11e9-ab8c-000c293b577e:3';
begin;commit;
set gtid_next='AUTOMATIC';
posted @   原来是你~~~  阅读(228)  评论(0)  编辑  收藏  举报
相关博文：
· MySQL-MGR组复制
· MySQL-MHA搭建
· Mysql主从
· 14-Mysql主从复制
· mysql主从复制
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 单线程的Redis速度为什么快？
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 展开说说关于C#中ORM框架的用法！
点击右上角即可分享
微信分享提示