MySQL主从复制常遇到的几个坑

最新推荐文章于 2024-08-05 16:11:08 发布

源de爸讲数据库

最新推荐文章于 2024-08-05 16:11:08 发布

阅读量7k

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/leonpenn/article/details/76619042

版权

MySQL 专栏收录该内容

23 篇文章 2 订阅

订阅专栏

主从复制架构图如下：

主库有数据变更时，会通知备库的io线程，io线程建立一个TCP长连接，接下来主库的binlog dump线程会推送主库的binlog。在进行完一次HTTP操作后保持该TCP连接（HTTP/1.1起默认使用长连接）。只要Server端或Client端不提出关闭请求，或存在其他异常情况，两者间的连接将持续下去。

主库的worker线程在写binlog的时候是并发工作的，而主库的dump线程和从库的IO线程在读和收binlog的过程中是单线程工作的（5.7版本之后支持多线程）。因此高并发的情况下，从库很有可能跟不上主库的进度（异步复制）。

主从同步环境下，由于主备库搭建复制关系时，备库“不干净”，导致有可能遇到备库无法执行某个binlog event的情况，此时在确定binlog中该event的影响之后，可以采用：1）在从库上 set sql_log_bin=OFF; [创建缺失的记录;] set sql_log_bin=ON; 2）pt-slave-restart 跳过N个错误：--skip-count=N、跳过指定错误码：--error-numbers=1062; 3）set global sql_slave_skip_counter=N;跳过N个binlog event。

特别需要指出的是：set global sql_slave_skip_counter，当第N个event存在于某个事务，则该event所在的整个事务也将被跳过。

我们在交互界面内输入的一句SQL，表现在binlog中的记录就有所不同了。

如下截取了一段row格式记录的binlog：

# at 8982883
#170803 16:35:08 server id 23712  end_log_pos 8982951 	Query	thread_id=1601	exec_time=0	error_code=0
SET TIMESTAMP=1501749308/*!*/;
BEGIN
/*!*/;
# at 8982951
# at 8983010
#170803 16:35:08 server id 23712  end_log_pos 8983010 	Table_map: `demo`.`heartbeat` mapped to number 35
#170803 16:35:08 server id 23712  end_log_pos 8983156 	Update_rows: table id 35 flags: STMT_END_F


BINLOG '
POCCWROgXAAAOwAAAOIRiQAAACMAAAAAAAEABGRlbW8ACWhlYXJ0YmVhdAAGDwMPCA8IBmgA/AP8
Azw=
POCCWRigXAAAkgAAAHQSiQAAACMAAAAAAAEABv//8BoyMDE3LTA4LTAzVDE2OjM1OjA3LjAwMTQw
MKBcAAAQAG15c3FsLWJpbi4wMDAwMzE3EIkAAAAAAPAaMjAxNy0wOC0wM1QxNjozNTowOC4wMDE2
MjCgXAAAEABteXNxbC1iaW4uMDAwMDMxYxGJAAAAAAA=
'/*!*/;
# at 8983156
#170803 16:35:08 server id 23712  end_log_pos 8983183 	Xid = 158813
COMMIT/*!*/;

再看一段relay log中的binlog event group

mysql> show relaylog events in "mysql-relay-bin.000051" from 5867832 limit 5 \G
*************************** 1. row ***************************
   Log_name: mysql-relay-bin.000051
        Pos: 5867832
 Event_type: Query
  Server_id: 23712
End_log_pos: 10170351
       Info: BEGIN
*************************** 2. row ***************************
   Log_name: mysql-relay-bin.000051
        Pos: 5867900
 Event_type: Table_map
  Server_id: 23712
End_log_pos: 10170410
       Info: table_id: 35 (demo.heartbeat)
*************************** 3. row ***************************
   Log_name: mysql-relay-bin.000051
        Pos: 5867959
 Event_type: Update_rows
  Server_id: 23712
End_log_pos: 10170556
       Info: table_id: 35 flags: STMT_END_F
*************************** 4. row ***************************
   Log_name: mysql-relay-bin.000051
        Pos: 5868105
 Event_type: Xid
  Server_id: 23712
End_log_pos: 10170583
       Info: COMMIT /* xid=179683 */
*************************** 5. row ***************************
   Log_name: mysql-relay-bin.000051
        Pos: 5868132
 Event_type: Query
  Server_id: 23712
End_log_pos: 10170651
       Info: BEGIN
5 rows in set (0.00 sec)

可以看到对heartbeat表的一次update操作，对应binlog中记录有如下4条event：

1、BEGIN

2、Table_map

3、Update_rows

4、COMMIT

主从复制过滤参数优先考虑：replicate-wild-ignore-table

主库的max_allowed_packet需要小于等于备库的max_allowed_packet。

否则当binlog为ROW格式，本来数据量就大，此时又有大事务在主库执行完之后，容易出现无法传输到备库执行的情况。

提示：Slave_IO_Running: No

Got a packet bigger than 'max_allowed_packet' bytes

启用带加密连接的主从复制

https://dev.mysql.com/doc/refman/5.5/en/replication-solutions-secure-connections.html