主从复制原理

微信图片_20210914213653.png

binlog和relay日志

bin log：

bin log 记录所有数据的更改，可用于本机数据恢复和主从同步
relay（中继） log：
- Mysql 主节点将binlog写入本地，从节点请求增量binlog，主节点将binlog同步到从节点
- 从节点单独进程会将binlog 拷贝至本地 relaylog中
- 从节点定时重放relay log

binlog的三种模式

statement level模式

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行

优点：

statement level下的优点，首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约io，提高性能。因为他只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息
缺点：

由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制

rowlevel模式

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改

优点：

bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row level的日志的内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function,以及trigger的调用和触发无法被正确复制的问题
缺点：

row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改记录，这样可能会产生大量的日志内容，比如有这样一条update语句：update product set owner_member_id='d'where owner_member_id='a',执行之后，日志中记录的不是这条update语句所对应的事件(mysql是以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多事件。自然，bin-log日志的量会很大

mixed模式

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更

开启binlog

修改my.cnf文件

在[mysqld]段下添加

#binlog刷盘策略 
sync_binlog=1 
#需要备份的数据库 
binlog-do-db=hello 
#不需要备份的数据库 
binlog-ignore-db=mysql 
#启动二进制文件 
log-bin=mysql-bin 
#服务器ID 
server-id=132

sync_binlog参数

0 ：存储引擎不进行binlog的刷新到磁盘，而由操作系统的文件系统控制缓存刷新

1：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新，这种方式最安全，但性能较低

n：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新

sync_binlog=0或sync_binlog大于1，事务被提交，而尚未同步到磁盘。因此，在电源故障或操作系统崩溃时有可能服务器已承诺尚未同步一些事务到二进制日志。因此它是不可能执行例行程序恢复这些事务，他们将会丢失二进制日志

调整binlog日志模式

查看binlog的日志模式

show variables like 'binlog_format';
调整binlog的日志模式

binlog的三种格式： STATEMENT 、 ROW 、 MIXED

set binlog_format=STATEMENT;

查看bin log和relay log日志

因为binlog日志文件：mysql-bin.000005是二进制文件，没法用vi等打开，这时就需要mysql的自带的mysqlbinlog工具进行解码，执行： mysqlbinlog mysql-bin.000005 可以将二进制文件转为可阅读的sql语句

mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000058 >binlog

使用命令查看binlog

show master logs ，查看所有二进制日志列表，和 show binary logs 同义

show binlog events 命令可以以列表的形式显示日志中的事件信息

show binlog events命令的格式

show binlog events [IN 'log_name'] [FROM pos] [LIMIT[offset,]row_count];

说明：

IN ‘log_name’：指定要查询的binlog文件名（如果省略此参数，则默认指定第一个binlog文件）
FROM pos：指定从哪个pos起始点开始查起（如果省略此参数，则从整个文件的第一个pos点开始算）
LIMIT【offset】：偏移量（默认为0）
row_count：查询总条数（如果省略，则显示所有行）

切换binlog文件

flush logs;

会生成一个新的日志文件

重启mysql服务也会生成新的日志文件

基于binlog主从复制

关闭主从机器的防火墙

systemctl stop iptables（需要安装iptables服务） 
systemctl stop firewalld（默认） 
systemctl disable firewalld.service（设置开启不启动）

主服务器配置

查看binlog是否开启可以使用命令

show variables like 'log_bin%';

log_bin如果是 OFF 代表是未开启状态

主服务器配置

修改my.cnf文件

在[mysqld]段下添加

#binlog刷盘策略 
sync_binlog=1 
#需要备份的数据库 
binlog-do-db=hello 
#不需要备份的数据库 
binlog-ignore-db=mysql 
#启动二进制文件 
log-bin=mysql-bin 
#服务器ID 
server-id=132

重启mysql服务

systemctl restart mysqld
主机给从机授备份权限

注意：先要登录到MySQL命令客户端

GRANT REPLICATION SLAVE ON *.* TO '从机MySQL用户名'@'从机IP' identified by '从机MySQL密码';

注意事项：

一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替

刷新权限

FLUSH PRIVILEGES;
查询master的状态

show master status;

从服务器配置

修改my.conf文件
```
[mysqld] 
server-id=133
```
进行配置从服务器
```
change master to 
master_host='192.168.68.132',
master_port=3306, 
master_user='root', 
master_password='root', 
master_log_file='mysql-bin.000002', 
master_log_pos=1190, 
MASTER_AUTO_POSITION=0;
```
注意：

语句中间不要断开， master_port 为mysql服务器端口号(无引号)， master_user 为执行同步操作的数据库账户， “410” 无单引号(此处的 410 就是 show master status 中看到的 position 的值，这里的mysql-bin.000001 就是 file 对应的值
启动从服务器复制功能

start slave;
检查从服务器复制功能状态

show slave status \G；

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)

搭建成功之后，往主机中插入数据，看看从机中是否有数据

基于GTID的主从复制

GTID介绍

GTID就是全局事务标识符（Global Transaction Identifiers），基于事务的复制。使用 GTID 时，可以识别和跟踪每个事务，因为它在原始服务器上提交并由任何从属服务器应用;这意味着在启动新从站或故障到新主服务器时，使用 GTID 引用日志文件或这些文件中的位置时，不需要引用日志文件或位置，这大大简化了这些任务。由于基于 GTID 的复制完全基于事务，因此只需确定主复制和从级复制是否一致;只要在主主机上提交的所有事务也都提交到从站上，就保证两者之间的一致性

GTID = server_uuid:transaction_id

server_uuid 来源于 auto.cnf

GTID和Binlog的关系

GTID在binlog中的结构

微信截图_20210916170828.png

GTID event 结构

Previous_gtid_log_event：

Previous_gtid_log_event 在每个binlog 头部都会有
每次binlog rotate的时候存储在binlog头部
Previous-GTIDs在binlog中只会存储在这台机器上执行过的所有binlog，不包括手动设置gtid_purged值
换句话说，如果你手动set global gtid_purged=xx；那么xx是不会记录在Previous_gtid_log_event中的

配置GTID主从复制

修改master、slave服务器的my.cnf文件

#开启GTID模式（必选）
gtid_mode=ON 
#强制gtid一致性（必选） 
enforce-gtid-consistency=true

重启mysql

systemctl restart mysqld

从服务器中执行change master

   change master to 
   master_host='192.168.68.132', 
   master_port=3306, 
   master_user='root', 
   master_password='root', 
   master_auto_position = 1;

开启同步

START SLAVE;

主从同步延迟的原因及解决办法

主从同步的延迟的原因

一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个，当某个SQL在从服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致，也就是主从延迟

主从同步延迟的解决办法

实际上主从同步延迟根本没有什么一招制敌的办法，因为所有的SQL必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入，那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。当然我们可以做一些缓解的措施

主服务器要负责更新操作，他对安全性的要求比从服务器高，所有有些设置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog，innodb_flush_log_at_trx_commit 也可以设置为0来提高sql的执行效率这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为slave
就是把，一台从服务器当度作为备份使用，而不提供查询，那边他的负载下来了，执行relay log 里面的SQL效率自然就高了
增加从服务器喽，这个目的还是分散读的压力，从而降低服务器负载

判断主从延迟的方法

MySQL提供了从服务器状态命令，可以通过 show slave status 进行查看，比如可以看看Seconds_Behind_Master参数的值来判断，是否有发生主从延时

其值有这么几种：

NULL ：表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes.
0 ：该值为零，是我们极为渴望看到的情况，表示主从复制状态正常

MySQL（十）MYSQL集群---主从复制

主从复制原理

binlog和relay日志

binlog的三种模式

statement level模式

rowlevel模式

mixed模式

开启binlog

sync_binlog参数

调整binlog日志模式

查看bin log和relay log日志

使用命令查看binlog

切换binlog文件

基于binlog主从复制

主服务器配置

从服务器配置

基于GTID的主从复制

GTID介绍

GTID和Binlog的关系

配置GTID主从复制

主从同步延迟的原因及解决办法

主从同步的延迟的原因

主从同步延迟的解决办法

判断主从延迟的方法