mysql主从查看sbr rbr_MySQL主从复制

1. 简介

随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：

在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。

1.1 为什么要做主从复制？

读写分离。在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

做数据的热备

架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

1.2 主从复制的原理是什么？

MySQL的主从复制是一个异步的复制过程(虽然一般情况下感觉是实时的)，数据将从一个MySQL数据库(Master)复制到另一个MySQL数据库(Slave)，在Master和Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中两个线程(SQL线程和IO线程)在Slave端，另一个线程(I/O线程)在Master端。

要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。binlog: binary log，是主库中保存所有更新事件日志的二进制文件。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后在Slave上以相同顺序执行获取的binlog日志中的记录的各种SQL操作。

我们根据上图来分析一下整个主从复制的过程：

(1)在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制。

(2)此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接Master服务器，并请求从执行binlog日志文件中的指定位置(日志文件名和位置就是在配置主从复制服务时执行change master命令指定的)之后开始发送binlog日志内容。

(3)Master服务器接收来自Slave服务器的IO线程的请求后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。

(4)当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log(即中继日志)文件(Mysql-relay-bin.xxx)的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容

(5)Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

1.3 复制的基本原则

每个Slave只有一个Master

每个Slave只能有一个唯一的服务ID

每个Master可以有多个Slave

1.4 复制的最大问题

延时

主从同步延迟原理和解决方案：

https://www.cnblogs.com/cnmenglang/p/6393769.html

2. 实战MySQL主从复制

2.1 环境说明

两个CentOS7虚拟机

MySQL 5.6.4

Master_IP:192.168.131.140

Slave_IP:192.168.131.141

注意：MySQL版本号最好一致，为了方便学习测试，建议关闭防火墙

2.2 故障避免

我的mysql安装过程是在一台虚拟机上安装好MySQL后，克隆虚拟机得到的两个环境，所以在后面会报一个错：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server

原因是：mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接copy data文件夹后server_uuid是相同的，show variables like ‘%server_uuid%’;

解决方法：

mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到server_uuid是相同的，

show variables like '%server_uuid%';

1

找到/var/lib/mysql文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可

systemctl restart mysqld.service

1

https://blog.csdn.net/cug_jiang126com/article/details/46846031

2.3 MySQL主从复制的复制方式

MySQL的主从复制并不完美，存在着几个由来已久的问题，首先一个问题是复制方式：

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

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

混合模式复制(mixed-based replication,MBR)

全局事务标识符 GTID(Global Transaction Identifier,GTID)

基于SQL语句的方式是最古老的方式，也是目前默认的复制方式，后来的三种是MySQL 5以后才出现的复制方式。

2.3.1 SBR方式的优缺点

SBR的优点

历史悠久，技术成熟

binlog文件较小

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

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

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

SBR的缺点：

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

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

对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记

录产生影响

数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错

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

2.3.2 RBR方式的优缺点

RBR的优点

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

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

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

RBR 的缺点：

binlog 大了很多

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

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

导致频繁发生 binlog 的并发写问题

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

2.3.3 混合方式

混合方式就是有mysql自动选择RBR方式和SBR方式，能够充分发挥两种方式的优点，一般情况下都使用该种方式实现主从复制

2.3.4 全局事务标识符 GTID

这种方式虽然能够大大提高主从复制的效率，减小主从复制的延时，但也存在问题，具体请参看下面的博客。

https://blog.csdn.net/guotao521/article/details/45483833

http://hamilton.duapp.com/detail?articleId=47

2.4 实现MySQL主从复制需要进行的配置

主服务器Master

开启二进制日志 binlog

配置唯一的server-id

获得master二进制文件名及位置

创建一个用于slave和master通信的用户账号

1

2

3

4

从服务器Slave

配置唯一的server-id

使用master分配的用户账号读取master二进制日志

启动slave服务

1

2

3

2.5 修改master配置

找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/my.cnf,在[mysqld]部分插入如下：

[mysqld]

#开启二进制日志

log-bin=mysql-bin

#设置server-id，建议使用ip最后3位

server-id=140

1

2

3

4

5

找到从数据库的配置文件my.cnf(或者my.ini)，我的在/etc/my.cnf,在[mysqld]部分插入如下：

#开启中继日志

relay-log=mysql-relay

#设置server-id，建议使用ip最后3位

server-id=141

1

2

3

4

重启mysql服务

systemctl restart mysqld.service

1

2.6 在主机上建立账户并授权slave

GRANT REPLICATION SLAVE ON *.* TO 'mysql141'@'192.168.131.141' IDENTIFIED BY 'mysql141';

flush privileges;

--查询master的状态

show master status\G

1

2

3

4

5

6

记录上图结果中File和Position的值。

注意：执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态发生状态值变化。

2.7 告知从服务器二进制文件名与位置

这里要根据上面主服务器的状态来填写，不要直接用下面的SQL，需要根据实际值修改。

CHANGE MASTER TO master_host = '192.168.131.140',

master_user = 'mysql141',

master_password = 'mysql141',

master_log_file = 'mysql-bin.000001',

master_log_pos = 120;

1

2

3

4

5

2.8 查看从服务器状态

//开启复制

start slave;

//查看主从复制是否配置成功

SHOW SLAVE STATUS\G

1

2

3

4

5

当看到Slave_IO_State:Waiting for master ot send event 、Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常。

2.9 测试主从复制是否成功

Master中和Slave中执行SQL

SHOW DATABASES;

1

在Master中创建数据库并创建数据表并插入一条数据

create database test;

use test;

create table tab1(id int auto_increment,name varchar(10),primary key(id));

insert into tab1(id,name) values (1,'why');

1

2

3

4

在Slave中查询这条数据

至此，MySQL主从复制就实现了。

原文链接：https://blog.csdn.net/why15732625998/article/details/80463041