MySQL主从(主备)搭建请点击这里。
MySQL主备基本原理
假设主备切换前,我们的主库是节点A,节点B是节点A的备库,客户端的读写都是直接访问节点A,节点B只是将A的更新同步过来然后本地执行,同步完成以后,节点AB的数据就一致了。
对于备库,建议设置成只读模式,只读模式对超级用户是无效的,用于同步更新的线程的用户就拥有超级权限,因此备库是可以正常更新的。
MySQL主备同步原理
关于redo log和binlog的详细写入过程可以看我的历史文章,这里就不再详细描述了。
Slave B和Master A直接会维持一个长连接,Master A内部有一个线程,专门用于服务Slave B的这个长连接,binlog同步的完整过程如下:
binlog的格式
binlog一共有三种格式:
CREATE TABLE `t` (
`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 t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');
statement的binlog
-- 设置binlog模式为statement
set global binlog_format = 'statement';
-- 执行删除语句
delete from t where a>=4 and t_modified<='2018-11-10' limit 1;
-- 查看当前正在写入的binlog
show master status;
-- 查看binlog中的内容
show binlog events in 'mysql-bin.000005';
-- 查看上述delete语句产生的waring
show warnings;
通过上图可以看出,delete语句生成了一个警告,原因是当前binlog设置的模式是statement,并且语句中含有limit,所以此命令是unsafe的。
为什么在statement的binlog下,有些命令是unsafe的?
这是因为在statement的binlog下,有些语句在传到备库执行以后会存在数据不一致的情况,比如上面的delete语句:
此时就可能存在在Master A上使用的是索引a,但binlog传到Slave B上在执行的时候有可能使用的是索引t_modified,因此MySQL认为这个是有风险的,给出warning提示。
row模式的binlog
-- 设置binlog模式为ROW
set global binlog_format = 'row';
-- 开启一个新的binlog文件
flush logs;
-- 执行delete语句
delete from t where a>=4 and t_modified<='2018-11-10' limit 1;
-- 查看当前正在写入的binlog
show master status;
-- 查看binlog中的内容
show binlog events in 'mysql-bin.000006';
通过上图可以看出,row模式的binlog和statement格式的binlog在BEGIN和COMMIT是一样的,但是row模式的binlog没有SQL原文,而是替换成了两个event:
如何查看binlog的详细信息
通过show binlog events是看不出详细信息的,如果需要查看详细信息,还需要借助mysqlbinlog这个工具:
mysqlbinlog -vv /var/log/mysql/mysql-bin.000006 --start-position=751
从上图中我们可以看出以下信息:
为什么会有mixed格式的binlog?
因此MySQL出现了mixed模式的binlog,MySQL会自己判断这条SQL是否可能引起主备不一致,如果是就用row格式,如果不是,就用statement格式。
-- 设置binlog模式为MIXED
set global binlog_format = 'mixed';
-- 执行插入语句
insert into t values(10, 10, now());
上述insert语句在我没有进行验证的时候我认为是会被记录为ROW模式,因为按照理解如果now函数被传到备库上执行(且binlog同步延迟比较高的话),那么主备数据肯定是不一致的,但实际上这条语句并没有如我所想记录为ROW模式,而是记录为statement模式,原因是什么呢,我们还是需要借助mysqlbinlog这个工具进行分析:
-- 首先通过下面的SQL找到binlog开始的position
show binlog events in 'mysql-bin.000006';
找到position以后我们就可以对binlog进行更进一步的分析:
mysqlbinlog -vv /var/log/mysql/mysql-bin.000006 --start-position=1022 --stop-position=1291
通过上图我们可以看出,在记录insert之前,binlog中多记录了一条SET TIMESTAMP=1645343964,用它来约定了接下来now()函数的返回时间,因此不论binlog在备库上是多久以后执行,值都是固定的。
为什么建议你将binlog设置为ROW?
首先现在随着SSD的普及,磁盘IO的性能得到大幅提升,在SSD加持下,IO成为瓶颈的可能性比较小,并且ROW模式的binlog记录了完整的变更信息,在恢复数据上面将会很容易。
即使我不消息误删了一行记录,我也可以通过binlog捞回原来的所有字段信息,然后转变成insert进行插入。
如果执行的是update语句,由于ROW模式的binlog会完整记录修改前和修改后的整行数据,所以我也可以很容易的进行恢复。
如何使用binlog恢复数据
-- 下面命令的意思,是将mysql-bin.000006文件中position在1022到1291字节之间的内容解析出来,放到MySQL中执行
mysqlbinlog /var/log/mysql/mysql-bin.000006 --start-position=1022 --stop-position=1291 | mysql -h127.0.0.1 -P3306 -u$user -p$pwd;
双主(双M)结构
在实际生产中,我们更多的是使用双Master的结构,双Master的结构和Master-Slave的结构区别只是:
节点A和节点B之间会为主备关系,这样在发生切换的时候就不需要修改主备关系了。
双M架构下的循环复制
双M架构的一个问题就是假设在节点A上进行更新,此时会发送binlog给节点B,节点B在重放这条更新以后也会生成binlog,由于节点A也是节点B的备库,因此又会把节点B的binlog拿过来进行执行,此时就会产生循环复制的问题。
如何解决循环复制问题
借助server id,在前面的实验中,我们已经知道在binlog中会记录server id。