MySQL主从同步--主从复制进阶

MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

1、MySQL支持的binlog二进制日志复制类型

- 基于语句（statement）的复制

  在主服务器上执行SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。

- 基于行（row）的复制
把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从MySQL 5.0开始支持。

- 混合型（mixed）的复制
默认采用基于语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制。

主从复制原理

• 主节点

  1、当主节点上进行 insert、update、delete 操作时，会按照时间先后顺序写入到 binlog 中； 2、当从节点连接到主节点时，主节点会创建一个叫做 binlog dump 的线程；一个主节点有多少个从节点，就会创建多少个 binlog dump 线程；

  3、当主节点的 binlog 发生变化的时候，也就是进行了更改操作，binlog dump 线程就会通知从节点 (Push模式)，并将相应的binlog 内容发送给从节点。

• 从节点

  当开启主从同步的时候，从节点会创建两个线程用来完成数据同步的工作。

  I/O线程： 此线程连接到主节点，主节点上的 binlog dump 线程会将 binlog 的内容发送给此线程。此线程接收到 binlog 内容后，再将内容写入到本地的 relay log。

  SQL线程： 该线程读取 I/O 线程写入的 relay log，并且根据 relay log 的内容对从数据库做对应的操作。

为什么要做主从复制

实时灾备，用于故障切换 ​ 负载平衡 ​ 读写分离，提高并发能力

配置主从同步

准备两台机器--已经部署好MySQL

首先配置好两台主机的/etc/my.cnf

注意：server_id不能一样

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=129
log-bin=binlog

启动MySQL

/etc/init.d/mysqld start

• 查看server_id的命令

select @@server_id

给master配置同步账号并授予权限

 create user 'rep'@'%' identified by '123';

grant replication slave on *.* to 'rep'@'%';

• 查看权限

show grants for 'rep'@'%';

• 锁表设置只读

为后面备份准备，注意生产环境要提前申请停机时间；

注意：测试锁表后不可以可以创建数据库

如果超过设置时间不操作会自动解锁。

flush tables with read lock;

unlock tables;----解锁命令

• 查看主库状态

注意记住file文件的编号以及position的编号

配置从主机：

设定从库向主库同步

change replication source to source_host='192.168.1.128',source_user='rep',source_password='123',source_log_file='binlog.000003',source_log_pos=1024,source_ssl=1;

启动从库同步开关

start replica;

查看状态

start replica;

测试：

在主库中创建数据库 从库中查看

从库

查看主库有多少从库

show replicas;

主从复制进阶

延时同步

通过人为配置从库和主库延时N小时可以实现延时同步，延时同步可以解决数据库故障出现的数据丢失问题（物理损坏如直接使用rm删除数据库数据和逻辑损坏如使用drop命令删除数据库）。

注意延时同步是通过配置从库延时来达到延时同步

配置从库延时同步，设置sql线程延迟300秒后读取relay log

首先关闭从库开关

stop replica;

配置从库延时同步，设置sql线程延迟300秒后读取relay log

change replication source to source_delay=300;

开启从库同步

start replica;

进行延时测试

在主库中创建relay_test的库，relay_test的库中创建t1的库表插入数据

现在在主库中删除relay数据库，模式误触删除。测试延时同步的作用

在sql线程延迟300秒时间内发现误删除数据库则立马停止从库的sql线程

stop slave sql_thread;

找到误删前relay log的起点和终点

找到relay_log文件的位置

show replica status\G;

输入以下命令，找到误删数据的位置

show relaylog events in "slave-relay-bin.000002";

将relay log进行备份

 mysqlbinlog --start-position=584 --stop-position=1962 /data/mysql/slave-relay-bin.000002 > /tmp/relay.sql

恢复误删除的数据库

source /tmp/relay.sql;

在从库中查看被主库删除的relay_log

GTID同步

GTID是全局事务ID（global transaction identifier），其保证为每一个提交的事务可以生成一个唯一的ID。当事务提交时，MySQL Server在写binlog的时候，会先写一个类型为GTID_Event的特殊Binlog Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog 都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。也就是说，无论是级联情况，还是一主多从情况，都可以通过GTID自动找同步位置，而无需像之前那样通过File_name和File_position找同步位置了。

首先配置数据库的主库和从库/etc/my.cnf文件

vim /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server_id=128
log-bin=binlog
enforce_gtid_consistency=on
gtid_mode=ON

重新启动数据库

/etc/init.d/mysqld restart

在主库的配置

建立同步账号

create user 'rep'@'%' identified by '123';

grant replication slave on *.* to 'rep'@'%';

在从库中的配置

设定从库向主库同步

change replication source to source_host='192.168.1.128',source_user='rep',source_password='123',source_auto_position=1, source_ssl=1;

开启从库的同步

start replica;

检查从库状态

show replica status\G;

测试：

在主库中创建test数据库，在库中创建t1的表

在从库中查看

半同步复制

半同步复制的特性：

• 半同步复制必须是在主库和从库两端都开启时才行，从库会在连接到主库时告诉主库它是不是配置了半同步。
• 如果半同步复制在主库端是开启了的，并且至少有一个半同步复制的从库节点，那么此时主库的事务线程在提交时会被阻塞并等待，等待的结果有两种，第一种是至少一个从库节点通知主库自己已经收到了所有这个事务的binlog事件；第二种就是主库一直等待直到超过配置的某一个时间点为止，此种情况下半同步复制将自动关闭，转换为异步复制。
• 从库节点只有在接收到某一个事务的所有binlog并将其写入relay log文件之后才会通知主库

在mysql5.5-5.6使用after_commit的模式下，客户端事务在存储引擎层提交后，在得到从库确认的过程中，主库宕机了，此时主库在等待slave ACK的时候，虽然没有返回到当前客户端但是事务已经提交，其他客户端会读取到已经提交的事务，如果slave端还没有读到该事务的events，同时主库发生了crash，然后切换到备库，那么之前读到的事务就不见了。如果主库永远启动不了，那么实际上在主库已经成功提交的事务在从库上是找不到的，也就是数据丢失了。

为了解决上述问题，在mysql5.7版本增加了after_sync（无损复制）参数，并将其设置为默认的半同步方式。在mysql5.7.2引入的Loss-less Semi-Synchronous（无损复制）通过添加参数after_sync，使得主库在commit事务之前等待slave的ACK，只有slave回复了ACK，主库才会提交事务，这样就保证了master和slave数据的一致性。

在mysql5.7.17又引入了**全同步**技术，全同步复制就是当主库提交事务之后，所有的从库节点必须收到并且apply这些事务，然后主库线程才能继续后续操作。

配置半同步复制

主的配置，加载插件

install plugin rpl_semi_sync_master soname 'semisync_master.so';

查看插件

show plugins;

开启此开关，可写入配置文件

set global rpl_semi_sync_master_enabled=1;

设置主等待从回复ACK的超时时间为3秒，默认是10秒，可写入配置文件

set global rpl_semi_sync_master_timeout=3000;

从的配置

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

开启此开关，可写入配置文件

set global rpl_semi_sync_slave_enabled=1;

查看状态

show status like 'rpl_semi_sync_slave_status';

发现还是关闭状态此时---关闭io进程---开启io进程刷新一下再次查看

测试：

在主库中创建表

查看master成功接收到slave的回复的次数

 show status like 'Rpl_semi_sync_master_yes_tx';

模拟从故障

停止从库的io进程

stop slave io_thread;

关闭半同步开关

set global rpl_semi_sync_slave_enabled=0;

在主库中插入t2表的数据看一下主库反应---可以看到会等待一个超时时间

发现主关闭了半同步复制

show status like 'Rpl_semi_sync_master_status';