MYSQL主从同步

MYSQL主从复制是通过Binlog进行的，Binlog会记录MYSQL的所有修改操作，无论是DML还是DDL都必须提交事务才会记录在Binlog中。

MYSQL主从复制过程：

• master当事务提交时，会将数据变更存在master的binlog文件中。
• Slave节点通过I/O线程，从master上拉取binlog文件到slave服务器，并存储在本地的relayLog中。
• slave节点的SQL线程，通过回放relayLog中的内容，完成主从数据的同步。

Binlog日志格式

使用下面命令可以查询：binlog格式

SELECT @@binlog_format

ROW（row-based replication）

row基于行的复制，不记录每条SQL的上下文，只记录哪条数据被修改了，修改成了什么样子；若一条SQL修改了100条记录，那么row格式会记录这100行数据的变更，而statement格式仅记录一条SQL。

优点：

• 主从复制安全，可以保证主备服务器数据完全一致。
• 若误删除操作了数据，可以通过分析二进制文件，通过记录做反向操作可以恢复数据。

Statement（statement-based replication）

statement基于SQL的复制，会将每一次数据变更的SQL记录到binlog中。

优点：

• 不需要记录每一条SQL语句与每一行数据的变化，可以极大的减少Binlog的日志量，减少磁盘占用。

缺点：

例如下面的例子：

CREATE TABLE `TEST` (
  `id` bigint(20) NOT NULL DEFAULT 0,
  `a` int(11) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_create_time`(`create_time`)
) ENGINE=InnoDB;

INSERT INTO TEST VALUES(1,1,'2024-06-06');
INSERT INTO TEST VALUES(2,2,'2024-06-05');
INSERT INTO TEST VALUES(3,3,'2024-06-04');
INSERT INTO TEST VALUES(4,4,'2024-06-03');
INSERT INTO TEST VALUES(5,5,'2024-06-02');

DELETE FROM TEST WHERE a>=4 AND create_time<='2024-06-10' LIMIT 1;

• 在主库上：delete如果走的idx_a索引，那么将会删除id=4的数据。
• 在从库上：delete如果走的idx_create_time索引，将会删除create_time = '2024-06-02' id = 5的数据；
• 所以使用Statement格式复制：会存在主从复制安全问题。