MySQL-24：MySQL主从同步

binlog是归档日志，可以用主从同步， 但它的内容是什么样的呢？为什么备库执行了 binlog 就可以跟主库保持一致了呢？

24.MySQL主备的基本原理

如图就是主备切换流程：

在状态1时，客户端访问节点A，节点B为备库，将节点A的binlog同步到节点B上执行。当需要切换的时候，就切换状态2。

备库建议设置为只读模式(readonly)，理由有三：

1. 根据readonly判断角色
2. 查询类的语句可以去备库上查询
3. 防止切换过程中出现双写问题

readonly对超级用户是无效的，负责主备库之间的同步线程就拥有超级权限。

节点A同步到节点B的内部流程：

主库A和备库B之间会维护一个长连接，流程如下：

1. 备库B通过 change master 命令来指定主库A的IP，端口，用户名，密码以及binlog同步的开始位置
2. 备库B执行start slave命令，会启动两个线程io-thread和sql-thread，io-thread负责与主库建立链接，sql-thread负责解析relay log。
3. 主库校验完用户名和密码，从指定的binlog发送给备库B，备库B写入本地文件，称为中转文件(relay log)。
4. sql-thread解析relay log并执行。

24.2binlog的三种格式

binlog有三种格式：statement，row和mixed。

现在有表如下：

不过在这之前，我们需要做个设置：

1. 开启binlog，开启binlog

# 通过以下指令看查看是否开启
show VARIABLES like '%log_bin%';
# 如果没有开启，则在my.ini中进行配置
# 启动MySQL的二进制文件，指定binlog文件路径，它会在data目录下生成binlog文件，也可以使用绝对路径
log_bin = mysql_bin
# 设置binlog的模式
binlog_format="STATEMENT"  
# binlog_format="ROW"  
# binlog_format="MIXED" 

2. 设置binlog的记录模式

# 参数binlog_format：表示binlog的内容格式
# 通过以下语句查询参数设置
show variables like '%binlog_format%'
# 通过以下参数进行设置
set global binlog_format='STATEMENT';

我们先来看下在statement模式下的binlog内容：

# 我们先执行一条修改语句
update t set a = 10 where id =1;

binlog查询指令

# 用下面指令查看binlog的总文件 因为binlog值追加写的，所以它必定写在号数最大的binlog文件下。
show BINARY logs ;
# 用下面指令来查看binlog
show binlog EVENTS in 'mysql_bin.000012';

我们来看下上面binlog的日志内容，从Pos154~514，共4行：

1. 第一行：SET @@SESSION.GTID_NEXT= 'ANONYMOUS’先过
2. 第二行：begin对应第四行的commit，表示这是一个事务
3. 第三行：有着use 库名，后面跟着我们执行的语句，真实记录
4. 第四行：commit后面跟着一个xid，这个xid是用来关联binlog和redolog，用来确认binlog是否已经写入，存在则提交，不存在则回滚。

为了看出statement和row的区别，我们在执行 如下语句：

delete from t where a = 10 limit 1;

我们就可以发现，MySQL报了一个警告，如果我们用了limit，binlog_format = statement的话，这是不安全的：

现在我们将binlog_format = row，情况如下所示，

和statement模式下的binlog记录进行对比，发现前后是一样的，中间记录的sql语句换成了event：Table_map和Update_rows。

1. Table_map：表示操作的为blog库的表t
2. Update_rows：表示update操作行为，如果是delete，那它就是Delete_rows

不过从上面看不出什么，我们需要借助mysqlbinlog工具，mysqlbinlog工具的使用，查看的结果如下：

从上面的结果，我们可以发现几个信息：

• server id 1：表示这个事务是在 server_id=1 的这个库上执行的。
• Table_map：依旧还是显示打开的库和表
• 下面有记录着哪个字段从什么数值改变位什么数值
• 最后的Xid=32表示已提交的事务

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

24.3 binlog的mixed

statement格式因为会出现主备不一致的问题，row格式记录比较占空间，同步的时候浪费IO资源，所以出现了mixed格式，MySQL根据自己的判断来决定已什么格式写入binlog。

也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

**恢复数据：**因为row模式的binlog记录的内容详细，无论是执行insert，update，detele语句，我们都能很容易地恢复。

MariaDB 的Flashback工具就是基于上面介绍的原理来回滚数据的。

24.4 循环复制问题

一开始的图模式为M-S模式，下面的M-M模式，两个服务器互为主备库。

不过，有一个问题，如果业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog， 然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了 ，怎么解决？

满足两点即可，每台服务器的binlog必须有自己的id；备库在执行binlog前需要对id进行验证，查看是不是自己的，具体如下：

1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。