MySQL-5.7.20二进制主从复制实战(半同步复制、过滤复制、GTID模式)
一、半同步复制
半同步复制的原理
半同步复制时,为了保证主库上的每一个Binlog事务都能够被可靠的复制到从库上,主库在每次事务成功提交时,并不及时反馈给前端应用用户,而是等待其中的一个从库也接收到Binlog事务并成功写入中继日志后,出库才返回commit操作成功给客户端。半同步复制保证了事务成功提交后,至少有两份日志记录,一份在主库的Binlog日志上,另一份在至少一个从库的中继日志Relay log上,从而更近一步保证了数据的完整性。
半同步复制先决条件
前文已经有二进制多实例主从复制的文档,本文档不再从新开启主从复制,如果没有开启请点击:二进制主从复制
在做半同步复制前请保证开启了主从同步
半同步复制实战
加载sync插件
[mark_d]注意:主库与从库加载的插件不是同一个!!![/mark_d]
[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "install plugin rpl_semi_sync_master soname 'semisync_master.so';"
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "install plugin rpl_semi_sync_slave soname 'semisync_slave.so';"
查看插件
[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "show plugins" |grep rpl_semi_sync
rpl_semi_sync_master ACTIVE REPLICATION semisync_master.so GPL
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "show plugins" |grep rpl_semi_sync
rpl_semi_sync_slave ACTIVE REPLICATION semisync_slave.so GPL
启动半同步
注意: 插件名称不一样
[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "set global rpl_semi_sync_master_enabled = 1;"
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "set global rpl_semi_sync_slave_enabled= 1 ;"
重启从库IO线程
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "stop slave io_thread;"
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "start slave io_thread;"
查看是否运行
[root@mysql-manager ~]# mysql -S /opstands/mysql-3307/mysql.sock -e "show status like 'Rpl_semi_sync_master_status';"
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "show status like 'Rpl_semi_sync_slave_status';"
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
二、过滤复制
复制的过滤主要有2种方式
- 在主服务器在把事件从进二制日志中过滤掉,相关的参数是:binlog_do_db和binlog_ignore_db。
- 在从服务器上把事件从中继日志中过滤掉,相关的参数是replicate_*。
- 复制只能扩展读取,不能扩展写入,对数据进行分区可以进行扩展写入。
复制的优化:
在mysql复制环境中,有8个参数可以让我们控制,需要复制或需要忽略不进行复制的DB或table。
下面二项需要在Master上设置:
- Binlog_Do_DB:设定哪些数据库需要记录Binlog
- Binlog_Ignore_DB:设定哪里数据库不需要记录Binlog
- 优点是Master端的Binlog记录所带来的Io量减少,网络IO减少,还会让slave端的IO线程,SQL线程减少,从而大幅提高复制性能,
- 缺点是mysql判断是否需要复制某个事件不是根据产生该事件的查询所在的DB,而是根据执行查询时刻所在的默认数据库(也就是登录时指定的库名或运行”use database”中指定的DB),只有当前默认DB和配置中所设定的DB完全吻合时IO线程才会将该事件读取给slave的IO线程.所以,如果在默认DB和设定须要复制的DB不一样的情况下改变了须要复制的DB中某个Table中的数据,该事件是不会被复制到Slave中去的,这样就会造成Slave端的数据和Master的数据不一致.同样,在默认的数据库下更改了不须要复制的数据库中的数据,则会被复制到slave端,当slave端并没有该数据库时,则会造成复制出错而停止。
下面六项需要在slave上设置:
- Replicate_Do_DB:设定需要复制的数据库,多个DB用逗号分隔
- Replicate_Ignore_DB:设定可以忽略的数据库.
- Replicate_Do_Table:设定需要复制的Table
- Replicate_Ignore_Table:设定可以忽略的Table
- Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以带通配符来进行设置。
- Replicate_Wild_Ignore_Table:功能同Replicate_Do_Table,功能同Replicate_Ignore_Table,可以带通配符。
- 优点是在slave端设置复制过滤机制,可以保证不会出现因为默认的数据库问题而造成Slave和Master数据不一致或复制出错的问题.
- 缺点是性能方面比在Master端差一些.原因在于:不管是否须要复制,事件都会被IO线程读取到Slave端,这样不仅增加了网络IO量,也给Slave端的IO线程增加了Relay Log的写入量。
主从过滤的注意事项:
- 使用replicate_do_db和replicate_ignore_db时有一个隐患,跨库更新时会出错。
- 如在Master(主)服务器上设置 replicate_do_db=test(my.conf中设置)
主从复制过滤复制先决条件
前文已经有二进制多实例主从复制的文档,本文档不再从新开启主从复制,如果没有开启请点击:二进制主从复制
在做过滤复制前请保证开启了主从同步
主从复制过滤复制实战
添加配置文件白名单参数
[root@mysql-manager ~]# echo 'replicate_do_db=olda' >> /opstands/mysql-3308/my.cnf
# -----------------------------------------------------
# replicate_ignore_db是黑名单,用法和白名单一样
重启3308实例
[root@mysql-manager ~]# systemctl restart mysqld-3308.service
查看过滤复制的信息
[root@mysql-manager ~]# mysql -S /opstands/mysql-3308/mysql.sock -e "show slave status\G" |egrep 'Replicate_Do_DB:|Replicate_Ignore_DB:'
Replicate_Do_DB: olda
Replicate_Ignore_DB:
现在主从复制只会同步olda库的数据
三、GTID模式
GTID优势
- 更简单的实现 failover,不用以前那样在需要找 log_file 和 log_Pos。
- 更简单的搭建主从复制。
- 复制集群有一个统一的方式识别复制位置,给集群管理带来了便利。
- 正常情况下,GTID 是连续没有空洞的,因此主从库出现数据冲突时,可以用添加空事物的方式进行跳过。
GTID的限制
- 在一个事务里面混合使用引擎,如 Innodb(支持事务)、MyISAM(不支持事务), 造成多个 GTIDs 和同一个事务相关联出错
- CREATE TABLE……SELECT 不能使用,该语句产生的两个 event 在某一情况 会使用同一个 GTID(同一个 GTID 在 slave 只能被使用一次)
1th event:创建表语句 create table
2th event:插入数据语句 insert - CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE 不能在事务内使用 (启用了–enforce-gtid-consistency 参数)。
GTID的工作原理
1、master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog,写入到本地的relay log中。
3、sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录。
4、如果有记录,说明该GTID的事务已经执行,slave会忽略。
5、如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
6、在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描。
- server_uuid是MySQL Server的只读变量,保存在数据目录下的auto.cnf中,可直接通过cat命令查看。MySQL第一次启动时候创建auto.cnf文件,并生成server_uuid(MySQL使用机器网卡,当前时间,随机数等拼接成一个128bit的uuid,可认为在全宇宙都是唯一的,在未来一百年,使用同样的算法生成的uuid是不会冲突的)。之后MySQL再启动时不会重复生成uuid,而是使用auto.cnf中的uuid。
- 在同一个集群内,每个MySQL实例的server_uuid必须唯一,否则同步时,会造成IO线程不停的中断,重连。在通过备份恢复数据时,一定要将var目录中的auto.cnf删掉,让MySQL启动时自己生成uuid
- GTID中还有一部分是transaction_id,同一个server_uuid下的transaction_id一般是递增的。如果一个事务是通过用户线程执行,那么MySQL在生成的GTID时,会使用它自己的server_uuid,然后再递增一个transaction_id作为该事务的GTID。当然,如果事务是通过SQL线程回放relay-log时产生,那么GTID就直接使用binlog里的了。在MySQL 5.6中不用担心binlog里没有GTID,因为如果从库开启了GTID模式,主库也必须开启,否则IO线程在建立连接的时候就中断了。5.6的GTID对MySQL的集群环境要求是非常严格的,要么主从全部开启GTID模式,要么全部关闭GTID模式。
- 使用server_uuid:transaction_id共同组成一个GTID的好处是,由于server_uuid唯一,即使一个集群内多个节点同时有写入,也不会造成GTID冲突。
GTID的使用
- MySQL通过全局变量gtid_mode控制开启/关闭GTID模式。但是gtid_mode是只读的,可添加到配置文件中,然后重启mysqld来开启GTID模式。
GTID原理
- 我们知道,在未开启GTID模式的情况下,从库用(File_name和File_pos)二元组标识执行到的位置。START SLAVE时,从库会先向主库发送一个BINLOG_DUMP命令,在BINLOG_DUMP命令中指定File_name和File_pos,主库就从这个位置开始发送binlog。
- 在开启GTID模式的情况下,如果指定MASTER_AUTO_POSITION=1。START SLAVE时,从库会计算Retrieved_Gtid_Set和Executed_Gtid_Set的并集(通过SHOW SLAVE STATUS可以查看),然后把这个GTID并集发送给主库。主库会使用从库请求的GTID集合和自己的gtid_executed比较,把从库GTID集合里缺失的事务全都发送给从库。如果从库缺失的GTID,已经被主库pruge了呢?从库报1236错误,IO线程中断。
- 通过GTID找到点儿的原理还是比较奇怪的,它过于强调主从binlog中GTID集合的一致性,弱化了Binlog执行的顺序性。
pos 与 GTID 有什么区别?
- 两者都是日志文件里事件的一个标志,如果将整个 mysql 集群看作一个整体,pos就是局部的,GTID就是全局的.
- 一主两从,在 master,slave1,slave2 日志文件里的 pos,都各不相同,就是一个 event,在 master 的日志里,pos 可能是 700,而在 slave1,slave2 里,pos 可能就是 300,400 了,因为众多 slave 也可能不是同时加入集群的,不是从同一个位置进行同步.
- 而 GTID,在 master,slave1,slave2 各自的日志文件里,同一个 event 的 GTID 值都是一样的.
GTID模式主从复制先决条件
前文已经有二进制多实例安装的文档,本文档不再从新安装,如果没有安装请点击:二进制多实例安装
[root@mysql-manager ~]# mkdir -p /opstands/mysql-5.7.20/binlog
GTID模式主从复制实战
本文将使用MySQL-3306实例为主节点,MySQL3309实例为从节点。
开启binlog日志与GTID
[root@mysql-manager ~]# sed -i '7a log_bin=/opstands/mysql-5.7.20/binlog/mysql-bin' /etc/my.cnf
[root@mysql-manager ~]# sed -i '8a binlog_format=row' /etc/my.cnf
[root@mysql-manager ~]# sed -i '9a sync_binlog=1' /etc/my.cnf
[root@mysql-manager ~]# sed -i '10a gtid_mode=ON' /etc/my.cnf
[root@mysql-manager ~]# sed -i '11a enforce_gtid_consistency' /etc/my.cnf
[root@mysql-manager ~]# cat >> /opstands/mysql-3309/my.cnf <<EOF
gtid_mode=ON
enforce_gtid_consistency
EOF
授权
[root@mysql-manager ~]# chown -R mysql.mysql /opstands/mysql-*
重启3306与3309实例
[root@mysql-manager ~]# systemctl restart mysqld.service
[root@mysql-manager ~]# systemctl restart mysqld-3309.service
查看是否开启GTID
[root@mysql-manager ~]# mysql -uroot -predhat -e "show variables like '%gtid_%';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON | <------- 是否开启GTID
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON | <-------是否启用GTID模块
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
[root@mysql-manager ~]# mysql -S /opstands/mysql-3309/mysql.sock -e "show variables like '%gtid_%';"
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON | <------- 是否开启GTID
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON | <-------是否启用GTID模块
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
创建主从复制用户
[root@mysql-manager ~]# mysql -uroot -predhat -e "grant replication slave on *.* to rep@'%' identified by 'redhat';"
开启GTID主从复制
# 进入3309实例数据库内开启GTID连接到3306实例
[root@mysql-manager ~]# mysql -S /opstands/mysql-3309/mysql.sock
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.18.1.79',
-> MASTER_USER='rep',
-> MASTER_PASSWORD='redhat',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看主从复制状态
[root@mysql-manager mysql-3309]# mysql -S /opstands/mysql-3309/mysql.sock -e "show slave status\G;" |egrep 'Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
测试GTID模式主从复制效果
可以看到当前两个实例都没有olda库,接下来在3306库创建olda库,然后查看3309库是否有同步。
[root@mysql-manager ~]# mysql -uroot -predhat -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@mysql-manager mysql-3309]# mysql -S /opstands/mysql-3309/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
查看结果
可以看到3309实例从库也同步创建了olda库
[root@mysql-manager ~]# mysql -uroot -predhat -e "create database olda charset utf8mb4;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-manager ~]# mysql -uroot -predhat -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| olda |
| performance_schema |
| sys |
+--------------------+
[root@mysql-manager mysql-3309]# mysql -S /opstands/mysql-3309/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| olda |
| performance_schema |
| sys |
+--------------------+
sys |
+--------------------+
[root@mysql-manager mysql-3309]# mysql -S /opstands/mysql-3309/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| olda |
| performance_schema |
| sys |
+--------------------+