MySQL-5.7.28二进制主从复制（半同步复制）

最新推荐文章于 2024-08-29 16:43:19 发布

不想敲代码的运维

最新推荐文章于 2024-08-29 16:43:19 发布

阅读量310

点赞数

分类专栏：数据库DBA 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_16942727/article/details/104269358

版权

数据库DBA 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

在MySQL5.5之前的版本中，MySQL的复制是异步复制，主库和从库的数据之间存在一定的延迟，比如网络故障等各种愿意，这样容易存在隐患就是当在主库写入一个事务成功后并提交了，但由于从库延迟没有及时得到主库推送的binlog日志时，主库突然宕机了，那么此时从库就可能损失这个事务，从而造成主从数据不一致的情况。
因此MySQL5.5版本之后引入了半同步复制的概念。

半同步复制原理

半同步复制时，为了保证主库上的每一个binlog事务都能够被可靠的复制到从库上，主库在每次事务成功提交时，并不及时反馈给前端应用用户，而是等待其中的一个从库也接收到binlog事务并成功写入中继日志后，出库才返回commit操作成功给客户端。半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的binlog日志上，另一份在至少一个从库的中继日志relay log上，从而更进一步保证了数据的完整性。
在这里插入图片描述
事务在主库执行完binlog后接收到从库的ACK，才会回复客户端。所以相比而言，性能有所下降。

半同步复制特点

确保事务提交后binlog至少传输到一个从库
不保证从库应用完成这个事务的binlog
网络异常或从库宕机，卡主库，直到超时或从库恢复

半同步复制实战

前文已经有二进制多实例主从复制的实验，本次实验不再重复操作，如果没有开启主从复制请跳转到：二进制主从复制
在做半同步复制之前请确认开启了主从同步。

加载sync插件

[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3307/mysql.sock -e "install plugin rpl_semi_sync_master soname 'semisync_master.so';"
Enter password: 000000

注意：主库与从库加载的插件不是同一个！

[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3308/mysql.sock -e "install plugin rpl_semi_sync_slave soname 'semisync_slave.so';"
Enter password: 000000

查看插件

[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3307/mysql.sock -e "show plugins" |grep rpl_semi_sync
Enter password: 
rpl_semi_sync_master    ACTIVE  REPLICATION     semisync_master.so      GPL

[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3308/mysql.sock -e "show plugins" |grep rpl_semi_sync
Enter password: 
rpl_semi_sync_slave     ACTIVE  REPLICATION     semisync_slave.so       GPL

启动半同步

[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3307/mysql.sock -e "set global rpl_semi_sync_master_enabled=1;"
Enter password: 
[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3308/mysql.sock -e "set global rpl_semi_sync_slave_enabled=1;"
Enter password: 
#注意：插件名称不一样

以上的启动方式在登录mysql后的命令行操作，也可写在my.cnf配置文件中（推荐这种启动方式）。

#主库的my.cnf配置文件中添加：
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
#从库的my.cnf配置文件中添加：
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

重启从库IO线程

[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3308/mysql.sock -e "stop slave io_thread;"
Enter password: 
[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3308/mysql.sock -e "start slave io_thread;"
Enter password:

查看是否运行

[root@localhost ~]# mysql -uroot -p000000 -S /usr/local/mysql-3307/mysql.sock -e "show status like 'Rpl_semi_sync_master_status';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON    |
+-----------------------------+-------+

[root@localhost ~]# mysql -uroot -p000000 -S /usr/local/mysql-3308/mysql.sock -e "show status like 'Rpl_semi_sync_slave_status';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

需要注意一个问题，就是MySQL半同步复制不是严格意义上的半同步复制。当半同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。当master dump线程发送完一个事务的所有时间之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制。
一旦有一次超时自动降级为异步

[root@localhost ~]# mysql -uroot -p000000 -S /usr/local/mysql-3307/mysql.sock -e "show variables like 'rpl_semi_sync_master_timeout';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+

接下来可以测试下：

1）主数据库（从数据在执行“stop slave”之前）
[root@localhost ~]# mysql -uroot -p -S /usr/local/mysql-3307/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.10 sec)
mysql> use test;
Database changed
mysql> create table ceshi(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into ceshi values(1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from ceshi;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

2）从数据库执行"stop slave"
[root@localhost ~]# mysql -uroot -p000000 -S /usr/local/mysql-3308/mysql.sock -e "stop slave;"

再观察主数据库

[root@localhost ~]# mysql -uroot -p000000 -S /usr/local/mysql-3307/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> insert into test.ceshi values(2);
Query OK, 1 row affected (0.00 sec)

mysql> show status like "Rpl_semi_sync_master_status";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF   |
+-----------------------------+-------+
1 row in set (0.01 sec)

查看从数据库

[root@localhost ~]# mysql -uroot -p000000 -S /usr/local/mysql-3308/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

接着再在从数据库执行"start slave"

mysql> start slave;

再观察主数据库

mysql> insert into test.ceshi values(3);
Query OK, 1 row affected (0.00 sec)

mysql> show status like "Rpl_semi_sync_master_status";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON   |
+-----------------------------+-------+
1 row in set (0.00 sec)

查看从数据库

mysql> show status like 'Rpl_semi_sync_slave_status';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_slave_status  | ON   |
+-----------------------------+-------+
1 row in set (0.00 sec)

在slave执行stop slave之前，主的insert操作很快就能返回。
在slave执行stop slave之后，主的insert操作需要10.01s才返回，而这与rpl_semi_sync_master_timeout参数的时间相吻合。这时，查看两个状态的值，均为OFF。
在Slave执行start slave后，主的inset操作很快就能返回，此时，两个状态的值也变为ON了。