MySQL（三）MySQL-5.7.20 主从复制实战（半同步复制、过滤复制、GTID模式）

最新推荐文章于 2023-11-08 17:22:25 发布

正在输入中…………

最新推荐文章于 2023-11-08 17:22:25 发布

阅读量998

点赞数

分类专栏：数据库&消息队列文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_23435961/article/details/112349198

版权

数据库&消息队列专栏收录该内容

51 篇文章 6 订阅

订阅专栏

本文详细介绍了MySQL 5.7.20的主从复制配置，包括半同步复制确保数据完整性，过滤复制实现特定库的同步，以及GTID模式的优势和使用。在半同步复制中，主库等待至少一个从库确认接收事务。过滤复制允许在主库或从库层面选择复制的数据库和表。GTID模式通过全局事务ID确保复制的精确性和简化故障转移。最后，文中展示了如何开启和验证GTID模式的主从复制。

摘要由CSDN通过智能技术生成

MySQL-5.7.20二进制主从复制实战（半同步复制、过滤复制、GTID模式）

文章目录

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    |
+----------------------------+-------+

二、过滤复制

复制的过滤主要有２种方式

在主服务器在把事件从进二制日志中过滤掉，相关的参数是: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                |
+--------------------+

正在输入中…………

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
1
评论
MySQL（三）MySQL-5.7.20 主从复制实战（半同步复制、过滤复制、GTID模式）

MySQL-5.7.20二进制主从复制实战（半同步复制、过滤复制、GTID模式）文章目录MySQL-5.7.20二进制主从复制实战（半同步复制、过滤复制、GTID模式）一、半同步复制半同步复制的原理半同步复制先决条件半同步复制实战加载sync插件查看插件启动半同步重启从库IO线程**查看是否运行**二、过滤复制复制的过滤主要有２种方式复制的优化：下面二项需要在Master上设置：下面六项需要在slave上设置：主从过滤的注意事项：主从复制过滤复制先决条件主从复制过滤复制实战添加配置文件白名单参数重启330
复制链接

扫一扫