Mysql 集群技术
一 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 --listNote: This output shows SysV services only and does not include nativesystemd services. SysV configuration data might be overridden by nativesystemd 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 -predhatmysql: [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 8Server version: 5.7.44 Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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/mysqlsocket=/data/mysql/mysql.socksymbolic-links=0server-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 masterMaster_Host: 172.25.254.10Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1245Relay_Log_File: mysql-node3-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: ConnectingSlave_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-000c29dbd129Master_Info_File: /data/mysql/master.infoSQL_Delay: 60SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_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 eventMaster_Host: 172.25.254.10Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 154Relay_Log_File: mysql-node2-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_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)