MYSQL主从备份相关（别人的经验之谈）

一、binlog 的三种格式对比
【statement】

　　由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：插入的自增主键 id，在数据不同步时使用相同语句，会导致插入的数据不一致。因此，MySQL 认为这样写是有风险的。

【row】

　　当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。

【mixed】

　　因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。

　　但 row 格式的缺点是，很占空间。比如用 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

　　所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，举一个可以直接看出来的好处：恢复数据。

二、解决双M结构的循环复制问题：

建议把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog。

规定两个库的 server id 必须不同，每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

三、主备延迟

1、主备延迟的来源

第一种：备库所在机器的性能要比主库所在的机器性能差。——对称部署

第二种常见的可能：备库的压力大。

一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。

通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。

第三种可能：大事务。

控制每个事务删除的数据量，分成多次删除。

第四种：备库的并行复制能力。

2、备份策略：【可靠性优先策略】

在图双 M 结构下，从状态 1 到状态 2 切换的详细过程是这样的：

1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；


2. 把主库 A 改成只读状态，即把 readonly 设置为 true；


3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；


4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；


5. 把业务请求切到备库 B。

　　切换期间存在：系统的不可用时间。如果系统的不可用时间太长，对业务太说是不可接受的。

所以有了【可用性优先策略】

　　不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写。代价是可能出现数据不一致的情况。

　　同步时，binlog_format使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。

【可用性优先策略】会导致数据不一致，所以建议使用【可靠性优先策略】，对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

参考资料：

https://blog.csdn.net/weixin_41605937/article/details/122262362  MYSQL——数据库主从备份原理