MySQL的主从备份（可靠性）

一、备库的备份

MySQL依赖bin log进行备库的备份（bin log既支持归档，又支持备份）

MySQL支持主备切换，作为备库要设置成readonly（这样可以避免sql语句对备库的误操作），而且因为备库从主库的bin log读取数据进行备份时，操作者为超级用户，read only对超级用户无效，所以仍然可以写入备份数据。

二、主备的执行过程：

1. 主库与从库建立一个长连接，主库安排一个线程负责维护来连接;
2. 备库执行start slave命令，建立一个io_thread线程负责维护与主库的链接，一个sql_thread线程负责将从主库的bin log中读到的数据写入备库;
3. 备库向主库发送请求备份指令（包含从主库的bin log文件的那个位置开始拷贝）;
4. 主库验证通过备库的请求，从指定位置读取bin log的一部分（relay log）发送给备库;
5. 备库sql_thread线程执行relay log日志进行备份操作。

三、MySQL如何保证主备一致性：

主要根据bin log的格式，bin log存在三种格式（安全性：statement < mixed<row，一般推荐至少使用mixed，目前基本使用的是row）

1. statement：
statement中记录的是主库执行的sql语句的原句，当主库与备库使用的索引不一致（主库与备库可以使用不同的索引）的时候可能会导致对数据的操作不一致。如以下语句：

mysql> delete from table where a>=4 and b<=10 limit 1;

主库使用索引a，那么优先找到a = 4这行数据，由于limit为1，只删除一行数据，那么就删除该行；但是备库如果使用索引b那么优先找到该字段下满足条件的一行数据，b = 10，删除该行；然而以上两行数据不一定是同一行，所以主备删除的数据不同。

2. row：
采用row格式时，bin log里面记录的是数据库中行记录变化的每一行的主键id，所以备库备份操作的时候更新的肯定是同一行，但是其缺点就是需要大量的存储空间，比如statement只要记录一条语句就可以表示对多行的数据改动，row需要记录每一行数据的变动情况。

3. mixed:
所以为了优化statement和row，MySQL安排了第三种mixed格式的bin log，这种格式下MySQL会自动判断主库的操作是否存在安全隐患，存在就使用row格式的bin log，否则使用statement格式。

总结一下bin log的三种格式：

• statement记录主库的sql语句的原话，会导致主备数据不一致，不安全。
• row记录主库sql语句造成数据有改动的数据行的主键id，保证主备数据一致，安全但占用内存。
• mixed则是混合使用statement和row格式的bin log，MySQL会自动判断使用哪种格式。

目前使用row格式的bin log较多，因为可以方便对执行错误的insert，update 以及delete操作进行回滚。

参考文献：

https://time.geekbang.org/column/article/76446