正文

MySQL 主备的基本原理

一般要把备库设置成只读模式。主要是以下几点考虑。

1. 运营类的查询语句在备库执行，防止和 master 争抢资源。设置成只读防止误操作。

   运营类的查询通常指的是那些被用于业务运营分析、报告生成、数据审核或其他非交易性质的查询。

2. 防止切换逻辑有 BUG，比如切换过程中有双写，造成主备不一致。

3. 可以用只读判断节点角色。

主库和备库中间有个长连接，主库中有一个线程专门用于主备通讯，主备数据同步的过程如下

• 备库通过 change master 命令，设置主库的 IP、端口等等一些信息，还有要从哪里开始读取 binlog。
• 备库上执行 start slave 命令，启动两个线程 io_thread 和 sql_thread，io_thread 专门用于和主库通讯。
• 主库验证通过后发送 binlog 给备库，备库写入到中转日志文件（relay_log）。
• sql_thread 取出日志，并执行。

binlog 的三种格式

• statement：记录的是客户端输入的完整的 sql 语句。
• row：记录的是一些原始命令，比如 Table_map、Delete_rows 等等，需要用 mysqlbinlog 工具才能看到。
• mixed：这是一种混合的记录模式。

一般在生产环境是不能设置 statement 格式的，最好连 mixed 格式也不能设置。

原因是：

• statement 中记录的是完整的 sql 语句，比如执行一个删除语句用了两个条件如： a>=1 and b<='2024-03-23 limit 1' ，但这两个条件正好还是索引，那么就可能出现一个情况，主库先用 a 字段排序，而备库先用 b 字段排序，因为根据不同索引排序的顺序是不一致的，这个 limit 1 会导致误删除的问题。但是使用 row 格式，会记录完整的主键，一行一行记录的，所以 statement 会比 row 节省很多空间。
• 如果我正好入库一条数据 insert into t values(10,10, now()); 这个如果使用 mixed 会记录成 statement 格式，即使是上下文去记录这个时间戳，不会发生一致性问题， 但是有些 DBA 会直接粘贴 mysqlbinlog 解析出来的语句执行，而忽略了上下文。

循环复制的问题

这个问题主要发生在多主节点，一个复制到另一台的 binlog 会被复制回来，形成循环。

解决方式：

• 在配置双主的时候一定要将服务器 ID 配置不同。
• MySQL 还有复制过滤功能，可以配置忽略特定的事件。
• 避免在不同的主机上修改同一份数据。可以通过分区数据或者使用读写分离等方式来实现。

上节课的问题

双 1 策略一般是在业务高峰期配置，因为这样最大程度的保证主备的一致性，俗话说就是让备库紧追主库。

还有就是在批量导入的时候，因为批量导入数据了非常大，如果过程中宕机会出现大量的数据丢失，这种情况是不可接受的，双 1 每次都会写盘，就不会出现这个问题。

一般情况下，把生产库改成 “非双 1” 配置，是设置 innodb_flush_logs_at_trx_commit=2、sync_binlog=1000。