MySQL主从

本文为极客时间专栏《MySQL实战45讲》笔记，文中部分图文来自该专栏。附上专栏链接，有兴趣可点击订阅：https://time.geekbang.org/column/intro/139

昨天的文章我们操作了主从复制，那么你有仔细想一想工作原理吗？

主从同步复制原理

复制简单分成三步：

1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
2. slave将master的binary log events拷贝到它的中继日志(relay log)；
3. slave重做中继日志中的事件，将改变反映它自己的数据。

当然，其中的细节是很复杂的，我们可以看下极客时间《MySQL实战45讲》专栏的图：

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。

而备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的：

在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。

在备库B上执行start slave命令，这时候备库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接。

主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。

备库B拿到binlog后，写到本地文件，称为中转日志（relay log）。

sql_thread读取中转日志，解析出日志里的命令，并执行。

binlog里记录的是什么？

那么介绍了工作原理，之前也有文章介绍过binlog，那么binlog里到底记录的是什么呢？

binlog根据配置记录的内容是不一样的，我们看表格：

format	定义	优点	缺点
statement	记录的是修改SQL语句	日志文件小，节约IO，提高性能	准确性差，对一些系统函数不能准确复制或不能复制，如now()、uuid()等
row	记录的是每行实际数据的变更	准确性强，能准确复制数据的变更	日志文件大，较大的网络IO和磁盘IO
mixed	statement和row模式的混合	准确性强，文件大小适中	有可能发生主从不一致问题

推荐使用的是row模式，准确性高，虽然说文件大，但是现在有SSD和万兆光纤网络，这些磁盘IO和网络IO都是可以接受的。

mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。
也就是说，mixed格式可以利用statment格式的优点，同时又避免了数据不一致的风险。那么，为什么不推荐使用mixed模式，我们可以举例说明（数据恢复）：

我们就分别从delete、insert和update这三种SQL语句的角度，来看看数据恢复的问题。

即使执行的是delete语句，row格式的binlog也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条delete语句以后，发现删错数据了，可以直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了。

如果你是执行错了insert语句呢？那就更直接了。row格式下，insert语句的binlog里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把insert语句转成delete语句，删除掉这被误插入的一行数据就可以了。

如果执行的是update语句的话，binlog里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了update语句的话，只需要把这个event前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

其实，由delete、insert或者update语句导致的数据操作错误，需要恢复到操作之前状态的情况，也时有发生。

查看binlog

binlog本身是一类二进制文件。二进制文件更省空间，写入速度更快，是无法直接打开来查看的。
因此mysql提供了命令mysqlbinlog进行查看。
一般的statement格式的二进制文件，用下面命令就可以

mysqlbinlog mysql-bin.000001

如果是row格式，加上-v或者-vv参数就行，如

mysqlbinlog -vv mysql-bin.000001

配置参数

参数名	含义
log_bin = {on | off | base_name}	指定是否启用记录二进制日志或者指定一个日志路径
sql_log_bin ={ on | off }	指定是否启用记录二进制日志
expire_logs_days	指定自动删除二进制日志的时间，即日志过期时间
log_bin_index	指定mysql-bin.index文件的路径
binlog_format = { mixed | row | statement }	指定二进制日志基于什么模式记录
max_binlog_size	指定二进制日志文件最大值
binlog_cache_size	指定事务日志缓存区大小
max_binlog_cache_size	指定二进制日志缓存最大大小
sync_binlog = { 0 | n }	指定写缓冲多少次，刷一次盘

---

之前是介绍一些概念的东西，今天我们来换换口味，实际操作一下啊，操作内容是搭建我们经常用到的MySQL主从。

前提

已经安装好了主数据库和从数据库，并新建database为test，假如一些数据。（因为是测试，单机器上开两个MySQL示例也可以）

master

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO repl@'127.0.0.1' IDENTIFIED BY '111111'; //分配账号 

grant all on . to repl@'127.0.0.1'; //授权

查看状态

SHOW MASTER STATUS;
//记住这里的file和position。

修改配置

[mysqld] 
log-bin=mysql-bin 
server-id=1

重启

slave

修改配置，注意server_id要与主库，其他从库不一样，可以设置成ip

[mysqld] 
log_bin = mysql-bin 
server_id = 2 
relay_log = mysql-relay-bin 
log_slave_updates = 1 
read_only = 1

重启后，建议先进行重置操作。

reset slave

连接master

CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_USER='repl', MASTER_PASSWORD='111111', MASTER_LOG_FILE='mysql-bin.000051',master_log_pos=8694;

master_log_file和master_log_pos就是上面记住的file和position，具体自行修改。

开启并查看状态

start slave show slave status

如果Slave_IO_Running和Slave_SQL_Running都显示Yes，一般就成功了。

失败一般就是master_log_pos，master_log_file配置有问题，重新配置下就行了。

可以在master插入修改数据，测试slave是否相应变化。

注意

如果master已有数据，可以锁定master（flush tables with read lock），然后导出同步到slave，配置完成后释放master（unlock tables）。

参考文章

• 【原创】研发应该懂的binlog知识(上) https://www.cnblogs.com/rjzheng/p/9721765.html