Mysql主备的基本原理
在状态1中，客户端直接访问节点A，而节点B只是备库，只是将A的binlog全部同步过来并应用到本地，这样可以保持节点B和节点A的数据是相同的。
当需要切换的时候，就变成状态2，这时候客户端读取的是节点B，而A变成B的备库。
在状态1中，B节点没有被直接访问，建议设置为readonly状态。
1 有时候一些运营类的查询会放到备库去上去查询，设置为只读可以防止误操作
2 防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致
3 可以用readonly状态，来判断主备角色
设置为readonly状态，不会对复制线程有影响，因为复制的用户拥有super权限
接下来，我们看节点A到B的内部流程，用一个update语句来执行
主库接收到客户端的更新操作请求后，执行内部的更新逻辑，并写binlog。
备库B跟主库A之间维持了一个长连接，主库A内部一个dump线程，专门用于服务B备库的这个长连接，一个事务日志同步的完整过程如下：
1 在备库B上通过change master命令，指定主库A的IP,端口，用户名，密码，以及开启请求的binlog（包含文件名和偏移位置）
2 在备库B上执行start slave，这时候备库启动2个线程，io_thread和sql_thread，前者专门负责与主库的连接
3 主库A效验完用户和密码后，开始按照B库传过来的文件名和位置点，从本地读取binlog，发给B库
4 备库B拿到binlog后，写本地文件（relay log）
5 sql_thread线程读取relay log，解析出日志的命令并执行
后面引入多线程复制方案，所以sql_thread也变成个线程
Binlog的三个格式对比
In MySQL 5.6, the default binary logging format is STATEMENT
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
==
mysql> SET GLOBAL binlog_format = 'STATEMENT';
([email protected]:3306) [(none)]> show variables like '%bin%';
| binlog_format | STATEMENT
([email protected]:3306) [(none)]> use test;
CREATE TABLE `t23` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;
insert into t23 values(1,1,'2018-11-13');
insert into t23 values(2,2,'2018-11-12');
insert into t23 values(3,3,'2018-11-11');
insert into t23 values(4,4,'2018-11-10');
insert into t23 values(5,5,'2018-11-09');
([email protected]:3306) [test]> delete from t23 /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
([email protected]:3306) [test]> show binlog events in 'mysql-bin.000022';
+------------------+------+-------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000022 | 4 | Format_desc | 2018091901 | 120 | Server ver: 5.6.15-log, Binlog ver: 4 |
从表中删除一条记录，可以看到binlog中是怎么记录的
第一行和第三行begin和commit对应，是一个事务，中间是一个执行语句，在真实的delete语句前面，有一个use test的命令。这条命令是mysql自动添加的，保证传到备库执行的时候，能够正取的执行到test库的t23表
说明statement与row格式的区别，执行完delete命令后，有一个warnings
([email protected]:3306) [test]> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
原因是这个delete的binlog的格式是statement，并且语句中有limit，所以这个命令可能是unsafe的。
比如上面这个例子：
1 如果delete语句是使用的索引a，那么会根据索引a找到第一个满足条件的行，也就说删除a=4这一行
2 但如果使用的是索引t_modified，那么删除的就是t_modified='2018-11-09'这一行，也就是说是a=5的这一行
由于在statement格式下，记录到binlog里面的语句是语句原文，因此可能会出现这样一种状况，在主库执行这条sql语句，用到的是索引a，而在备库用的是索引t_modified，因此，这样写是有风险的。
如果更换为row格式的，我们看一看
mysql> SET GLOBAL binlog_format = 'ROW';
([email protected]:3306) [test]> delete from t23 /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
Query OK, 1 row affected (0.00 sec)
这个时候看binlog的内容，与statement相比，前后的begin和commit是一样，但是row格式在binlog里面不是sql原文，而是替换为Table_map和Delete_rows
1 Table_map event，用于说明接下来要操作的是表是test库的表t23
2 Delete_rows event，用于定义删除的行为
要查看详细的binlog，要借助mysql自身的命令mysqlbinlog
[mysql@mysqlhq binlog]$ mysqlbinlog -vv mysql-bin.000023 --start-position=408
1 server id 2018091901，表示这个事务是server id =2018091901的库上执行的。
2 每个event都有CRC32的值，是因为把binlog_checksum 设置了CRC32
([email protected]:3306) [test]> show variables like 'binlog_checksum';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| binlog_checksum | CRC32 |
+-----------------+-------+
3 Table_map event 显示了接下来要展开的表，map到数字table_id: 2425，现在这个sql只操作了一个表，如果多个表，每个表对应的Table_map event，每个map到一个单独的数字，用于分区不同的表
4 在mysqlbinlog命令中，使用-vv参数是为了把内容都解析出来，从结果看到各个字段的值（@1=4，@2=4，@3=1541779200）
5 binlog_row_image的默认配置是FULL，因此delete_event里面，包含了删掉的所有行的所有字段，如果把该参数设置为MININAL，则只会记录必要的信息，在这个例子中，记录的就是id=4的这个信息
([email protected]:3306) [test]> show variables like 'binlog_row_image';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| binlog_row_image | FULL |
+------------------+-------+
6 最后的Xid = 3777647 event，表示事务被正确的提交了
为什么会有mixed格式的binlog
1 因为有些statement格式的binlog可能胡导致主备不一致，所以要使用row格式
2 但row格式的缺点是很占用空间，比如一个delete语句删掉10w行数据，用statement语句的话就是一个sql语句记录到binlog中，占用比较小的空间，但是如果是row格式的binlog，就要把这10w条记录都写到binlog中，这样不仅会占用更大的空间，还会消费io资源，影响执行速度
3 所以mysql取了一个折中的方案，就是mixed格式，mysql自己会判断这条sql语句是否引起主备不一致，如果可能，就用row格式，否则就用statement格式
所以线上的binlog格式至少应该是mixed和row格式
设置为row格式的好处就是容易恢复数据--flushback之类
比如dml，insert，update，delete之类的语句，row格式的binlog在恢复的时候，可以利用binlog进行转换，比如insert转换delete，delete转换insert，目前有一些第三方工具--MariaDBD Flushback工具，美团的Myflush等。--参考https://www.cnblogs.com/yhq1314/p/10179306.html
现在看一下mixed格式的binlog
mysql> SET GLOBAL binlog_format = 'MIXED';
mysql> insert into t values(10,10, now());
看一下执行效果，还是now()变量，如果传到备库，是否会不一致呢
解析一下binlog
[mysql@mysqlhq binlog]$ mysqlbinlog -vv mysql-bin.000023 --start-position=623
从图中看到，在binlog记录的时候，多写了一个set timestamp的记录
SET TIMESTAMP=1547022196/*!*/; 约定了接下来now()函数返回的时间
在用binlog来恢复数据的标准做法，用mysqlbinlog解析出来，把整个结果集发给mysql执行
mysqlbinlog mysql-bin.000023 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
循环复制问题
通过上面知道在mysql中，binlog的特性确保了在备库执行相同的binlog，可以得到主库的状态
因此，我们认为正常情况下主备的数据是一致的。
Mysql在复制中
1 规定在同一复制环境中的server_id不能相同，如果相同，则不能成为主从关系
2 一个备库接到主库的binlog并在重放的过程中， 生成与原来binlog的server_id相同的binlog
3 每个库在收到从自己的主库发过来的日志后，先判断server_id，如果跟自己的相同，表示这个是自己生成的，就直接丢弃这个日志。