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

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';"
1
2

查看插件

[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
1
2
3
4

启动半同步

注意： 插件名称不一样

[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 ;"
1
2
3

重启从库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;"
1
2

查看是否运行

[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    |
+----------------------------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13

二、过滤复制

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

1. 在主服务器在把事件从进二制日志中过滤掉，相关的参数是:binlog_do_db和binlog_ignore_db。
2. 在从服务器上把事件从中继日志中过滤掉，相关的参数是replicate_*。

• 复制只能扩展读取，不能扩展写入，对数据进行分区可以进行扩展写入。

复制的优化：

在mysql复制环境中,有8个参数可以让我们控制,需要复制或需要忽略不进行复制的DB或table。

下面二项需要在Master上设置：

1. Binlog_Do_DB:设定哪些数据库需要记录Binlog
2. 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上设置：

1. Replicate_Do_DB:设定需要复制的数据库,多个DB用逗号分隔
2. Replicate_Ignore_DB:设定可以忽略的数据库.
3. Replicate_Do_Table:设定需要复制的Table
4. Replicate_Ignore_Table:设定可以忽略的Table
5. Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以带通配符来进行设置。
6. 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是黑名单，用法和白名单一样
1
2
3
4

重启3308实例

[root@mysql-manager ~]# systemctl restart mysqld-3308.service
1

查看过滤复制的信息

[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:
1
2
3

现在主从复制只会同步olda库的数据

三、GTID模式

GTID优势

• 更简单的实现 failover，不用以前那样在需要找 log_file 和 log_Pos。
• 更简单的搭建主从复制。
• 复制集群有一个统一的方式识别复制位置，给集群管理带来了便利。
• 正常情况下，GTID 是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。

GTID的限制

1. 在一个事务里面混合使用引擎,如 Innodb(支持事务)、MyISAM(不支持事务)， 造成多个 GTIDs 和同一个事务相关联出错
2. CREATE TABLE……SELECT 不能使用，该语句产生的两个 event 在某一情况 会使用同一个 GTID(同一个 GTID 在 slave 只能被使用一次)
   1th event:创建表语句 create table
   2th event:插入数据语句 insert
3. 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
1

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
1
2
3
4
5
6
7
8
9

授权

[root@mysql-manager ~]# chown -R mysql.mysql /opstands/mysql-*
1

重启3306与3309实例

[root@mysql-manager ~]# systemctl restart mysqld.service
[root@mysql-manager ~]# systemctl restart mysqld-3309.service
1
2

查看是否开启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       |
+----------------------------------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

创建主从复制用户

[root@mysql-manager ~]# mysql -uroot -predhat -e "grant replication slave on *.* to rep@'%' identified by 'redhat';"
1

开启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)
1
2
3
4
5
6
7
8
9
10
11
12

查看主从复制状态

[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
1
2
3
4

测试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                |
+--------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

查看结果

可以看到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                |
+--------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35