MySQL主从复制，基于GTID的主从复制，半同步复制，并行复制

实验开始前规定server3(172.25.34.4)作为主库，server4(172.25.34.5)作为从库

mysql主从复制

原理图：

主从复制的要求：
（1）主库开启binlog日志（设置log-bin参数）
（2）主从server-id不同
（3）从库服务器能连同主库
主从复制原理：
　　mysql的主从配置又叫replication，AB复制，基于binlog二进制日志，主数据库必须开启binlog二进制日志才能进行复制。
（1）主数据库将更改操作记录到binlog二进制日志（主数据库有log dump线程和从数据库的i/o线程传递binlog）。
（2）从库生成两个线程，一个i/o线程，一个SQL线程
（3）i/o线程去请求主库的binlog，并且得到的binlog日志写到relay log(中继日志)文件中
（4）然后主库会生成一个log dump线程，用来给从库的i/o线程传binlog；SQL线程，会读取中继日志文件，并解析成具体的操作执行，这样主从的操作就一致了，而最终的数据也就一致了。
　　作为异步复制，其主库将事件写入binlog二进制文件，dump线程将binlog文件发送出去，不保证其他从节点是否会收到binlog二进制文件。

server3(主库上）:

安装mysql

[root@server3 ~]# cd mysql
[root@server3 mysql]# ls
master_ip_failover       mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar      sysbench
master_ip_online_change  mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
MHA-7                    send_report
[root@server3 mysql]# tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar 
[root@server4 mysql]# ls
master_ip_failover
master_ip_online_change
MHA-7
mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-devel-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.24-1.el7.x86_64.rpm
mysql-community-test-5.7.24-1.el7.x86_64.rpm
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
send_report
sysbench
[root@server3 mysql]# yum install -y mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm

启动数据库

[root@server3 mysql]# systemctl start mysqld

安全初始化

[root@server3 mysql]# cat /var/log/mysqld.log | grep password ##查看初始密码
2019-10-20T03:53:55.370433Z 1 [Note] A temporary password is generated for root@localhost: +jz>*=q%p84Q ##安装数据库时生成的密码使用初始密码进入数据库时，会提示让重制密码
[root@server3 mysql]# mysql_secure_installation  #进行安全初始化，输入刚查看密码，重新设置密码，设定新密码完成后，一路回车。
Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : 

 ... skipping.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 

 ... skipping.
All done! 

注意：密码必须大于8位，字母大小写结合，使用数字以及特殊字符。

编辑配置文件

[root@server3 mysql]# vim /etc/my.cnf ##mysql配置文件
log-bin=mysql-bin #开启二进制日志，相当于主库的日记本记录数据，从库不能直接向主库要数据，会增加主库的负担，从库应该从主库的二进制日志中读取数据信息。
server-id=1 ##服务器ID

使用数据库

[root@server3 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 database;
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 'database' at line 1
mysql> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> grant replication slave on *.* to root@'172.25.34.%' identified by 'Westos+001';
Query OK, 0 rows affected, 1 warning (0.01 sec) ##创建用户并授权
mysql> show variables like 'log_%'; ##查看二进制日志是否开启
+----------------------------------------+--------------------------------+
| Variable_name                          | Value                          |
+----------------------------------------+--------------------------------+
| log_bin                                | ON                             | ##二进制文件开启
| log_bin_basename                       | /var/lib/mysql/mysql-bin       |
| log_bin_index                          | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators        | OFF                            |
| log_bin_use_v1_row_events              | OFF                            |
| log_builtin_as_identified_by_password  | OFF                            |
| log_error                              | /var/log/mysqld.log            |
| log_error_verbosity                    | 3                              |
| log_output                             | FILE                           |
| log_queries_not_using_indexes          | OFF                            |
| log_slave_updates                      | OFF                            |
| log_slow_admin_statements              | OFF                            |
| log_slow_slave_statements              | OFF                            |
| log_statements_unsafe_for_binlog       | ON                             |
| log_syslog                             | OFF                            |
| log_syslog_facility                    | daemon                         |
| log_syslog_include_pid                 | ON                             |
| log_syslog_tag                         |                                |
| log_throttle_queries_not_using_indexes | 0                              |
| log_timestamps                         | UTC                            |
| log_warnings                           | 2                              |
+----------------------------------------+--------------------------------+
21 rows in set (0.00 sec)
mysql> show master status; ##查看主库状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      398 |              |                  |                   | ##posi始终在变化
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> quit;
Bye
[root@server3 ~]# systemctl start mysqld

解析一下：
mysql> grant replication slave on . to root@‘172.25.34.%’ identified by ‘Westos+001’;
Query OK, 0 rows affected, 1 warning (0.01 sec) ##创建用户并授权

replication	表示授权复制的权限
.	表示所有数据库可以进行同步
root	表示授权名，可以随意填写
‘172.25.34.%’	表示授权172.25.34.0/24的网段所有服务器可以同步， %表示任意

server4(从库）:

从库的前几步和主库相同，都是先安装数据库，在进行安全初始化

编辑配置文件，从库不用开启二进制文件

[root@server4 mysql]# vim /etc/my.cnf
server-id=2

启动mysql，加载配置文件

[root@server4 mysql]# systemctl start mysqld

进入数据库

[root@server4 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> change master to
master_host='172.25.34.4',master_user='root',master_password='Westos+001',master_log_file='mysql-bin.000002',master_log_pos=398; ##设定从设备,注意：master_log_file 和master_log_pos 的值都要依照主设备状态中的值来设定

Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> show slave status\G;  ##查看从设备状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.34.4
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 691
               Relay_Log_File: server4-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes ##必须要是开启
            Slave_SQL_Running: Yes

测试：

server3（主库）:

在主库上创建数据库和表，检测从库是否能同步主库信息。

mysql> create database westos; 
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)
mysql> use westos;
Database changed
mysql> create table userlist(
    -> username varchar(10) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> desc userlist;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO   |     | NULL    |       |
| password | varchar(15) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into userlist values ('user1','123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

server4(从库）:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.00 sec)
mysql> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

在从库上查看发现有之前的数据库，成功。

备库中继日志

[root@server4 mysql]# cd /var/lib/mysql
[root@server4 mysql]# ls
auto.cnf         ib_logfile0      performance_schema        server-cert.pem
ca-key.pem       ib_logfile1      private_key.pem           server-key.pem
ca.pem           ibtmp1           public_key.pem            sys
client-cert.pem  master.info      relay-log.info            westos
client-key.pem   mysql            server4-relay-bin.000001
ib_buffer_pool   mysql.sock       server4-relay-bin.000002
ibdata1          mysql.sock.lock  server4-relay-bin.index
[root@server4 mysql]# cat  relay-log.info ##中继日志
7
./server4-relay-bin.000002
641
mysql-bin.000003
428
0
0
1

基于GTID的主从复制（热储备）

mysql数据库从5.6.5开始新增一种基于GDIT的复制方式。GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 GTID 实际上 是由 UUID+TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。
主从复制,默认是通过pos复制(postion),就是说在日志文档里,将用户进行的每一项操作都进行编号(pos),每一个event都有一个起始编号,一个终止编号,我们在配置主从复制时从节点时,要输入master的log_pos值就是这个原因,要求它从哪个pos开始同步数据库里的数据,这也是传统复制技术。

MySQL5.6增加了GTID复制,GTID就是类似于pos的一个作用,不过它是整个mysql复制架构全局通用的,就是说在这整个mysql冗余架构中,它们的日志文件里事件的GTID值是一致的.
GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 GTID 实际上 是由 UUID+TID 组成的。其中 UUID 是一个 MySQL 实例的唯一标识。TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
通过GDIT保证每个主库上提交的事务在集群中有一个唯一的ID.这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

pos与GTID有什么区别?
两者都是日志文件里事件的一个标志,如果将整个mysql集群看作一个整体,pos就是局部的,GTID就是全局的.

server3(主库）:

[root@server3 ~]# vim /etc/my.cnf
gtid_mode=ON #开启gtid模式
enforce-gtid-consistency=true #强制gtid一直性，用于保证启动gitd后事务的安全
[root@server3 ~]# systemctl restart mysqld ##重启服务

server4（从库）：

[root@server4 ~]# vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=true
[root@server4 ~]# systemctl restart mysqld

从库端先停掉slave，然后重新创建连接

mysql> stop slave; ##停掉slave
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.25.34.4
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: server4-relay-bin.000006
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No  ##关闭
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 742
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 3cd7bcf6-f2ed-11e9-8496-5254006dc583
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
mysql> change master to  ##重新连接
    -> master_host = '172.25.34.4',
    -> master_user = 'root',
    -> master_password = 'Westos+001',
    -> master_auto_position = 1; ##slave连接master将使用基于GTID的复制协议。
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; ##开启slave
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G  ##查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.34.4
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: server4-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 576
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 3cd7bcf6-f2ed-11e9-8496-5254006dc583
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates ##
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

测试：

server3(主库）:

在主库添加数据进行测试

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)
mysql> insert into userlist values ('user2','456');
Query OK, 1 row affected (0.00 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)

进入mysql数据库

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from gtid_executed;
Empty set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000003 |      428 |              |                  | 3cd7bcf6-f2ed-11e9-8496-5254006dc583:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
##记录下了事务UUID

server4（从库）:

检测是否进行了数据的同步

mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)

数据同步成功

进入数据库，查看从库接受事务

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 3cd7bcf6-f2ed-11e9-8496-5254006dc583 |              1 |            1 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)

GTID半同步复制

异步复制 mysql默认，主库仅仅只是将数据信息记录在二进制文件中，不会去管备库的复制情况，用户体验好
全同步复制 主库会等备库同步信息
半同步复制（5.5以后版本支持）
半同步复制有等待时间间断，如果超过时间间断，没有返回ACK（成功同步信号），转为异步复制，以后都是异步复制。

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
半同步复制就是为了解决数据丢失的问题

server3（主库）:

安装服务插件，开启半同步复制

[root@server3 ~]# mysql -uroot -pWestos+001
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 10
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; ##安装服务插件
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled =1; ##开启半同步复制

Query OK, 0 rows affected (0.00 sec)
mysql> show status like '%rpl%'; ##查看状态变量
+--------------------------------------------+-------+
| 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.00 sec)
mysql> show variables like '%rpl%'; ##查看环境变量
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      | ##默认等待10s,如果不返回ACK,切换为异步复制
| 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 |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)

server4(从库）:

从端也安装插件，开启半同步复制

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec) ##安装插件

mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1; ##开启半同步复制

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled     | ON       |
| rpl_semi_sync_slave_trace_level | 32       |
| rpl_stop_slave_timeout          | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.35 sec)

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

注意：要重启从库上的IO线程,如果没有重启，则默认还是异步复制，重启后，slave会在master上注册为半同步复制的slave角色
测试1：

半同步失败的情况

server4(从库）:

mysql>  stop slave io_thread; ##停掉IO进程，模拟故障
Query OK, 0 rows affected (0.01 sec)

server3（主库）:

传入数据

mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into userlist values ('usr5','555') ##这里传入数据时，会卡在这里，这是因为在等待备库的ACK回应
    -> ;
Query OK, 1 row affected (10.01 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| usr3     | 789      |
| usr4     | 444      |
| usr5     | 555      |
+----------+----------+
5 rows in set (0.00 sec)
mysql> show status like '%rpl%'; 
+--------------------------------------------+-------+
| 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              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     | #半同步失败的次数
| Rpl_semi_sync_master_status                | OFF   | ##主库上半同步复制是开启的
| 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.00 sec)

此时在主端发现半同步失败次数+1
（1）Rpl_semi_sync_master_no_tx
表示没有成功接收slave提交的次数，也就是使用半同步失败的次数，10s后没有得到反馈信息，会转为异步复制
（2）Rpl_semi_sync_master_yes_tx
使用半同步成功的次数，数据的一致性能提高

server4（从库）:

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| usr3     | 789      |
| usr4     | 444      |
+----------+----------+
4 rows in set (0.00 sec)
mysql>  start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like '%rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from userlist; ##开启后同步
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| usr3     | 789      |
| usr4     | 444      |
| usr5     | 555      |
+----------+----------+

然后从端会发现没有同步过来，再次打开IO线程后，数据才能同步过来，此时复制过来的是异步复制的结果,然后再次在主库上创建数据，此时备库上可以查看到数据，这时同步数据的方式就是半同步复制

测试2：

半同步正常情况

server3（主库）:

mysql> insert into userlist values ('usr6','666')
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| usr3     | 789      |
| usr4     | 444      |
| usr5     | 555      |
| usr6     | 666      |
+----------+----------+
6 rows in set (0.00 sec)

mysql> show status like '%rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 588   |
| Rpl_semi_sync_master_tx_wait_time          | 588   |
| 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     | #Rpl_semi_sync_master_yes_tx 变为1，成功了一次
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

查看线程信息

mysql> show processlist;
+----+------+-------------------+--------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host              | db     | Command          | Time | State                                                         | Info             |
+----+------+-------------------+--------+------------------+------+---------------------------------------------------------------+------------------+
|  6 | dd   | 172.25.34.6:52642 | westos | Sleep            | 2976 |                                                               | NULL             |
|  7 | dd   | 172.25.34.6:52644 | NULL   | Sleep            | 3196 |                                                               | NULL             |
| 10 | root | localhost         | westos | Query            |    0 | starting                                                      | show processlist |
| 12 | root | 172.25.34.5:49894 | NULL   | Binlog Dump GTID |   48 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+------+-------------------+--------+------------------+------+---------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)