Mysql 集群技术-CSDN博客

本文链接：https://blog.csdn.net/luohailin_/article/details/141463759

一 Mysql 在服务器中的部署方法

在企业中90%的服务器操作系统均为Linux

在企业中对于Mysql的安装通常用源码编译的方式来进行

1.1 在Linux下部署mysql

1.1.1 安装依赖性并解压源码包，源码编译安装mysql：

注意：当cmake出错后如果想重新检测，删除 mysql-5.7.44 中 CMakeCache.txt即可

[root@mysql-node2 ~]# tar zxf mysql-boost-5.7.44.tar.gz
[root@mysql-node2 ~]# cd mysql-5.7.44/

[root@mysql-node2 mysql-5.7.44]# yum  install cmake gcc-c++ openssl-devel  ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64 -y

[root@mysql-node2 mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/

[root@mysql-node2 mysql-5.7.44]# make -j2                         
[root@mysql-node2 mysql-5.7.44]# make install

1.1.2 部署mysql

[root@mysql-node1 mysql-5.7.44]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 ~]# cd /usr/local/mysql/
[root@mysql-node1 mysql]# mkdir /data/mysql -p
[root@mysql-node1 mysql]# chown mysql.mysql -R /data/mysql/

[root@mysql-node1 mysql]# cd support-files/
[root@mysql-node1 support-files]# cp  mysql.server /etc/init.d/mysqld
[root@mysql-node1 support-files]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0

[root@mysql-node1 support-files]# vim ~/.bash_profile 
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

[root@mysql-node1 support-files]# source ~/.bash_profile 


[root@mysql-node1 support-files]# mysqld --user mysql --initialize
2024-08-22T02:37:04.001552Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-22T02:37:04.805970Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-22T02:37:04.914701Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-22T02:37:04.986356Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6b30c927-602f-11ef-a32a-000c29dbd129.
2024-08-22T02:37:04.987245Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-22T02:37:05.136107Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T02:37:05.136123Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T02:37:05.136851Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-22T02:37:05.213971Z 1 [Note] A temporary password is generated for root@localhost: uHbVyl,xw2d?

[root@mysql-node1 ~]# vim passwd
[root@mysql-node1 ~]# echo uHbVyl,xw2d? > passwd

[root@mysql-node1 ~]# chkconfig mysqld on
[root@mysql-node1 ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off
rhnsd          	0:off	1:off	2:on	3:on	4:on	5:on	6:off

[root@mysql-node1 ~]#  /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node1.exam.com.err'.
 SUCCESS! 
[root@mysql-node1 ~]# mysql_secure_installation

测试：

[root@node10 ~]# mysql -uroot -predhat
 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 8
 Server version: 5.7.44 Source distribution
 Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 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                |
 +--------------------+
 4 rows in set (0.00 sec)

二 mysql的组从复制

2.1 配置mastesr和salve

[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
log-bin=mysql-bin

[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

[root@mysql-node1 ~]# mysql -uroot -predhat
mysql> create user repl@'%' identified by 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20

[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 

[root@mysql-node2 ~]# mysql -predhat
mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='test,',master_log_file='mysql-bin.000001',master_log_pos=595;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

测试结果

[root@mysql-node1 ~]# mysql -predhat
mysql> create database test;
Query OK, 1 row affected (0.02 sec)

mysql> create table test.userlist (
    -> username varchar(20) not null,
    -> password varchar(50) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test.userlist value ('test','123');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| test     | 123      |
+----------+----------+
1 row in set (0.00 sec)

[root@mysql-node2 ~]# mysql -predhat
mysql> select * from test.userlist;
+----------+----------+
| username | password |
+----------+----------+
| test     | 123      |
+----------+----------+
1 row in set (0.00 sec)

2.2 当有数据时添加slave2

完成基础配置

[root@mysql-node3 ~]# vim /etc/my.cnf
 [mysqld]
 datadir=/data/mysql
 socket=/data/mysql/mysql.sock
 symbolic-links=0
 server-id=30

 [root@mysql-node3 ~]# /etc/init.d/mysqld restart

生产环境中备份时需要锁表，保证备份前后的数据一致

mysql> FLUSH TABLES WITH READ LOCK;

备份后再解锁

mysql> UNLOCK TABLES;

mysqldump命令备份的数据文件，在还原时先DROP TABLE，需要合并数据时需要删除此语句

[root@mysql-node1 ~]# mysql -uroot -predhat
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

[root@mysql-node1 ~]# mysqldump -uroot -predhat test > test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@mysql-node1 ~]# scp test.sql root@172.25.254.30:/root/
root@172.25.254.30's password: 
test.sql                                                  100% 1945     1.7MB/s   00:00    

[root@mysql-node1 ~]# mysql -uroot -predhat -e "SHOW MASTER STATUS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1245 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+




[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30


[root@mysql-node3 ~]# mysql -uroot -predhat
mysql> change master to master_host='172.25.254.10',master_user='repl',master_password='test,',master_log_file='mysql-bin.000001',master_log_pos=1245;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1245
               Relay_Log_File: mysql-node3-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes

2.3 延迟复制

延迟复制时用来控制sql线程的，和i/o线程无关

这个延迟复制不是i/o线程过段时间来复制，i/o是正常工作的

是日志已经保存在slave端了，那个sql要等多久进行回放

在master中写入数据后过了延迟时间才能被查询到

mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_DELAY=60;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
                 Master_UUID: 6b30c927-602f-11ef-a32a-000c29dbd129
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 60
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400

2.4 慢查询日志

慢查询，顾名思义，执行很慢的查询
当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个 SQL语句就是需要优化的
慢查询被记录在慢查询日志里
慢查询日志默认是不开启的
如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set long_query_time=4;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like "long%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

mysql> show variables like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | ON                               |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.01 sec)

[root@mysql-node1 ~]# cat  /data/mysql/mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument

测试结果

mysql> slect sleep(10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slect sleep(10)' at line 1
mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.01 sec)

[root@mysql-node1 ~]# cat  /data/mysql/mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2024-08-22T08:38:31.989061Z
# User@Host: root[root] @ localhost []  Id:    43
# Query_time: 10.011055  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1724315911;
select sleep(10);

2.5 mysql的并行复制

默认情况下slave中使用的是sql单线程回放

在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重

开启MySQL的多线程回放可以解决上述问题

注意：MySQL 组提交（Group commit）是一个性能优化特性，它允许在一个事务日志同步操作中将多个事务的日志记录一起写入。这样做可以减少磁盘I/O的次数，从而提高数据库的整体性能。

[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

[root@mysql-node2 ~]# /etc/init.d/mysqld restart

[root@mysql-node2 ~]# mysql -uroot -predhat
mysql> show processlist;

此时sql线程转化为协调线程，16个worker负责处理sql协调线程发送过来的处理请求

2.6 原理刨析

三个线程

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，一个主库线程，两个从库线程。

二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志（Relay log）。
从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。
步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；
步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

具体操作

1.slaves端中设置了master端的ip，用户，日志，和日志的Position，通过这些信息取得master的认证及信息

2.master端在设定好binlog启动后会开启binlog dump的线程

3.master端的binlog dump把二进制的更新发送到slave端的

4.slave端开启两个线程，一个是I/O线程，一个是sql线程，

i/o线程用于接收master端的二进制日志，此线程会在本地打开relaylog中继日志，并且保存到本地磁盘
sql线程读取本地relog中继日志进行回放

5.什么时候我们需要多个slave？

当读取的而操作远远高与写操作时。我们采用一主多从架构

数据库外层接入负载均衡层并搭配高可用机制

2.7 架构缺陷

主从架构采用的是异步机制
master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测
master端直接保存二进制日志到磁盘
当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slave
master出现问题slave端接管master，这个过程中数据就丢失了
这样的问题出现就无法达到数据的强一致性，零数据丢失

三半同步模式

3.1半同步模式原理

1.用户线程写入完成后master中的dump会把日志推送到slave端
2.slave中的io线程接收后保存到relaylog中继日志
3.保存完成后slave向master端返回ack
4.在未接受到slave的ack时master端时不做提交的，一直处于等待当收到ack后提交到存储引擎
5.在5.6版本中用到的时after_commit模式，after_commit模式时先提交在等待ack返回后输出ok

3.2 gtid模式

[root@mysql-node1 ~]# mysql -uroot -predhat
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[root@mysql-node1 ~]# mysqlbinlog -vv /data/mysql/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240822 21:35:12 server id 10  end_log_pos 123 CRC32 0xf5eb61b6 	Start: binlog v 4, server v 5.7.44-log created 240822 21:35:12 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
kD7HZg8KAAAAdwAAAHsAAAABAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACQPsdmEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AbZh6/U=
'/*!*/;
# at 123
#240822 21:35:12 server id 10  end_log_pos 154 CRC32 0xcf149075 	Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node2 ~]# /etc/init.d/mysqld restart

[root@mysql-node2 ~]# mysql -uroot -predhat
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',MASTER_PASSWORD='test', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.254.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-node2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3.3.启用半同步模式

[root@mysql-node1 ~]#  vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 

[root@mysql-node1 ~]# mysql -uroot -predhat
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';	 	#安装半同步插件
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->  FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME LIKE '%semi%';		#查看插件情况
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;		 #打开半同步功能
Query OK, 0 rows affected (0.00 sec)

mysql>  SHOW VARIABLES LIKE 'rpl_semi_sync%';			#查看半同步功能状态
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

mysql>  SHOW STATUS LIKE 'Rpl_semi_sync%';		 
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)



[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 

[root@mysql-node2 ~]# mysql -uroot -predhat
mysql>  INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

mysql>  SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)

mysql>  STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql>  START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

3.4.测试在master端写入数据

[root@mysql-node2 ~]# mysql -uroot -predhat
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 1     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 710   |
| Rpl_semi_sync_master_tx_wait_time          | 710   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

模拟故障：

#在slave端
mysql>  STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

mysql>  STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

#在master端插入数据
mysql> insert into test.userlist value ('test111','123');
Query OK, 1 row affected (10.00 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 3     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 3     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 710   |
| Rpl_semi_sync_master_tx_wait_time          | 710   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)