mysql主从查看sbr rbr_MySQL主从

介绍

主从复制(也称 AB 复制)允许将来自一个MySQL数据库服务器(主服务器)的数据复制到一个或多个MySQL数据库服务器(从服务器)。复制是异步的从服务器不需要永久连接以接收来自主节点的更新。复制可以指定数据库和指定表进行复制。

MySQL主从带来优势：

横向扩展：实现读写分离，降低主库访问压力，提高整体服务吞吐量；

数据安全：实现实时备份数据，减少数据丢失风险，同时控制从库只读给只需要查询的用户使用，增加数据安全性；

灾备：当主库出现问题，可以切换从库提供服务；

分析：可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能；

远程数据分发：可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

主从复制原理

主节点开启Binary log后， 从节点便可以同步相应操作到本地并执行更新以实现主从同步。同步流程如下图：

1. 主服务器上面的所有变更保存在二进制日志Binary log里面。

2. 从服务器上面启动一个I/O线程，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，主库随后创建Binlog Dump线程读取数据库事件并发给I/O线程， I/O线程然后把读取到的二进制日志写到本地的一个Realy log(中继日志)里面。

3. 从服务器上面同时开启一个SQL线程定时检查Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

每个从服务器都会收到主服务器二进制日志的全部内容的副本。从服务器设备负责决定应该执行二进制日志中的哪些语句。除非另行指定，否则主从二进制日志中的所有事件都在从节点上执行。每个从节点都会记录二进制日志坐标(文件名、文件读取位置)。由于每个从服务器都分别记录了自己当前处理二进制日志中的位置，因此可以断开从服务器的连接，重新连接然后恢复继续处理。

复制方式

MySQL主从复制主要有基于SQL复制、基于行复制、混合模式复制等复制方式。

基于SQL复制(statement-based replication, SBR)

基于SQL复制是目前默认方式。

优点：

历史悠久，技术成熟，binlog 文件较小；

binlog 中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况；

binlog 可以用于实时的还原，而不仅仅用于复制；

主从版本可以不一样，从服务器版本可以比主服务器版本高。

缺点：

不是所有的 UPDATE 语句都能被复制，尤其是包含不确定操作的时候；

复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁；

对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响。数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错；

执行复杂语句如果出错的话，会消耗更多资源。

基于行复制(row-based replication, RBR)

优点：

任何情况都可以被复制，这对复制来说是最安全可靠的；

和其他大多数数据库系统的复制技术一样；

多数情况下，从服务器上的表如果有主键的话，复制就会快了很多。

缺点：

binlog 大了很多；

复杂的回滚时 binlog 中会包含大量的数据；

主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题；

无法从 binlog 中看到都复制了写什么语句。

主从操作过程

主节点操作

在配置文设置如下配置修改：

server-id=1log-bin="D:/MySQL/data/bin-log"binlog-ignore-db=mysqlbinlog-do-db=test

授权从节点用户复制，如：

GRANT REPLICATION slave,reload,super ON *.* TO 'user'@'IP' IDENTIFIED BY 'user';flush privileges;

查看主节点状态：show master status;

从节点操作

在配置文设置如下配置修改：

server-id=2replicate-do-db=test

设置主节点，主节点信息在主节点上查看：

CHANGE MASTER TOMASTER_HOST='',MASTER_USER='',MASTER_PASSWORD='',MASTER_PORT=3306,master_log_file='mysql-bing.00001',master_log_pos=1;

开启主从同步，从节点上执行：start slave；

检查同步状态：show slave status;

暂停同步：stop slave;

参考：

https://www.jianshu.com/p/faf0127f1cb2