文档课题：rhel 7.3搭建MySQL 5.7.21(一主一从GTID半同步复制)

数据库：MySQL 5.7.21

系统：rhel 7.3

环境：

说明：在主从节点上分别安装好mysql 5.7.21.

1、理论知识

传统的基于binlog position的复制方式存在严重缺点：如果slave连接master时指定的binlog文件或position错误，会造成遗漏或重复，很多时候前后数据存在依赖性，如此便会导致数据不一致.从MYSQL5.6开始，mysql开始支持GTID复制.GTID全称是global transaction id，即全局事务ID.GTID的分配方式为uuid:trans_id，其中uuid在每个mysql服务器都唯一，记录在$datadir/auto.cnf中.若主从复制结构中任意两台服务器uuid重复(比如直接冷备份时，auto.conf中的内容是一致的)，在启动复制功能时会报错.此时可以删除auto.conf文件再重启mysqld.

基于GTID主从复制的优点：

-保证同一个事务在某slave上绝对只执行一次，没有执行过的gtid事务总会被执行；

-不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标；

-故障转移到新的master时很方便，简化很多任务；

-很容易判断master和slave的数据是否一致，只要master上提交的事务在slave上也提交过，那么一定是一致的；

-MySQL提供可以控制跳过某些gtid事务的选项，防止slave第一次启动复制时执行master上的所有事务而导致耗时过久；

-虽然对于row-based和statement-based的格式都能进行gtid复制，但建议采用row-based格式.

 

2、基于GTID主从复制环境部署

2.1、主节点操作

2.1.1、修改主库my.cnf文件

--在my.cnf文件中配置GTID主从复制.

[root@leo-mysql-master ~]# cp /etc/my.cnf /etc/my.cnf.bak

[mysql@mysql-leo-master etc]$ vi my.cnf

[mysql]

 

#CLIENT#

port                                      = 3306

socket                                    = /mysql/data/mysql.sock

 

[mysqld]

basedir                                   = /usr/local/mysql

datadir                                   = /mysql/data/

server_id                                 = 1

port                                      = 3306

sql_mode                                  = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character_set_server                      = utf8mb4

socket                                    = /mysql/data/mysql.sock

secure-file-priv                          = /mysql/bak/file

log-bin                                   = /mysql/binlog/mysql-bin

log-bin-index                             = /mysql/data/mysql-bin.index

relay-log                                 = /mysql/data/relay-bin

relay-log-index                           = /mysql/data/relay-bin.index

 

#GTID

gtid_mode                                 = on

enforce_gtid_consistency                  = on

innodb_buffer_pool_size                   = 2G

 

[mysqld_safe]

pid_file                                  = /mysql/data/mysql.pid

log_error                                 = /mysql/data/mysql_error.log

 

说明：蓝颜色高亮部分为新增配置.

 

2.1.2、重启mysql进程

--配置完my.cnf后重启mysql服务.

[mysql@mysql-leo-master ~]$ 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.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3

 

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

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> shutdown;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[mysql@mysql-leo-master ~]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &

 

2.1.3、确认相关参数

--登录mysql查看master状态，多出一项"Executed_Gtid_Set".

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      154 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

 

mysql> show global variables like '%uuid%';

+---------------+--------------------------------------+

| Variable_name | Value                                |

+---------------+--------------------------------------+

| server_uuid   | d0dddbd8-43bd-11ee-ac2f-000c29763af3 |

+---------------+--------------------------------------+

1 row in set (0.01 sec)

 

--确认gtid功能是否打开.

mysql> show global variables like '%gtid%';

+----------------------------------+-------+

| Variable_name                    | Value |

+----------------------------------+-------+

| binlog_gtid_simple_recovery      | ON    |

| enforce_gtid_consistency         | ON    |

| gtid_executed                    |       |

| gtid_executed_compression_period | 1000  |

| gtid_mode                        | ON    |

| gtid_owned                       |       |

| gtid_purged                      |       |

| session_track_gtids              | OFF   |

+----------------------------------+-------+

8 rows in set (0.01 sec)

 

--确认binlog日志功能是否打开.

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

1 row in set (0.00 sec)

 

2.1.4、创建复制用户

--创建repl复制用户，并刷新权限.

mysql> grant replication slave,replication client on *.* to 'repl'@'192.168.%' identified by "repl@12345";

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show grants for 'repl'@'192.168.%';

+--------------------------------------------------------------------------+

| Grants for repl@192.168.%                                                |

+--------------------------------------------------------------------------+

| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.%' |

+--------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

--再次查看master状态

mysql> show master status;

+------------------+----------+--------------+------------------+------------------------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |

+------------------+----------+--------------+------------------+------------------------------------------+

| mysql-bin.000004 |      617 |              |                  | d0dddbd8-43bd-11ee-ac2f-000c29763af3:1-2 |

+------------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.00 sec)

 

注意：启动配置之前，同样需要对从节点进行初始化.对从节点初始化的方法基本和基于日志点相同，只不过在启动GTID模式后，在备份中所记录的就不是备份时的二进制日志文件名和偏移量，而是备份时最后的GTID值.

 

2.1.5、建测试库

--在主节点创建booksDB库，此后备份该库.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

 

mysql> create database booksDB character set utf8 collate utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

 

mysql> use booksDB;

Database changed

mysql> create table books

    -> (

    -> bk_id int not null primary key,

    -> bk_title varchar(50) not null,

    -> copyright year not null

    -> );

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into books values

    -> (11078,'Learning MYSQL',2010),

    -> (11033,'Study Html',2011),

    -> (11035,'How to use php',2003),

    -> (11072,'Teach yourself javascript',2005),

    -> (11028,'Learning C++',2005),

    -> (11069,'MYSQL professional',2009),

    -> (11026,'Guide to MySQL 5.7',2008),

    -> (11041,'Inside VC++',2011);

Query OK, 8 rows affected (0.00 sec)

Records: 8  Duplicates: 0  Warnings: 0

 

mysql> create table authors

    -> (

    -> auth_id int not null primary key,

    -> auth_name varchar(20),

    -> auth_gender char(1)

    -> );

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into authors values

    -> (1001,'WriterX','f'),

    -> (1002,'WriterA','f'),

    -> (1003,'WriterB','m'),

    -> (1004,'WriterC','f'),

    -> (1011,'WriterD','f'),

    -> (1012,'WriterE','m'),

    -> (1013,'WriterF','m'),

    -> (1014,'WriterG','f'),

    -> (1015,'WriterH','f');

Query OK, 9 rows affected (0.00 sec)

Records: 9  Duplicates: 0  Warnings: 0

 

mysql> create table authorbook

    -> (

    -> auth_id int not null,

    -> bk_id int not null,

    -> primary key (auth_id,bk_id),

    -> foreign key (auth_id) references authors (auth_id),

    -> foreign key (bk_id) references books (bk_id)

    -> );

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into authorbook values

    -> (1001,11033),(1002,11035),(1003,11072),(1004,11028),

    -> (1011,11078),(1012,11026),(1012,11041),(1014,11069);

Query OK, 8 rows affected (0.00 sec)

Records: 8  Duplicates: 0  Warnings: 0

 

mysql> show tables;

+-------------------+

| Tables_in_booksDB |

+-------------------+

| authorbook        |

| authors           |

| books             |

+-------------------+

3 rows in set (0.00 sec)

 

2.1.6、全库备份

--使用mysqldump备份全库.

[mysql@mysql-leo-master tmp]$ mysqldump -uroot -h127.0.0.1 -p -P3306 --max_allowed_packet=1G --master-data=2 --single-transaction  -A -ER > /tmp/full_backupdb_full.sql

Enter password:

[mysql@mysql-leo-master tmp]$ ls -ltr full_backupdb_full.sql

-rw-rw-r--. 1 mysql mysql 791494 Aug 26 12:03 full_backupdb_full.sql

 

注意：

mysql5.6使用mysqldump备份时，指定备份的具体库，使用--database

mysql5.7使用mysqldump备份时，指定备份的具体库，使用--databases

 

2.1.7、拷贝备份集

--将full_backupdb_full.sql文件拷贝到mysql-leo-slave从节点.

[mysql@mysql-leo-master tmp]$ rsync -e "ssh -p22" -avpgolr /tmp/full_backupdb_full.sql mysql@192.168.133.112:/tmp

The authenticity of host '192.168.133.112 (192.168.133.112)' can't be established.

ECDSA key fingerprint is de:f3:3d:b0:c0:7b:25:e1:a0:d0:f0:c6:19:d2:35:b8.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.133.112' (ECDSA) to the list of known hosts.

mysql@192.168.133.112's password:

sending incremental file list

full_backupdb_full.sql

 

sent 791695 bytes  received 31 bytes  121804.00 bytes/sec

total size is 791494  speedup is 1.00

 

参数说明：

-e:指定使用rsh、ssh方式进行数据同步，一般使用ssh

-a:表示以递归方式传输文件，并保持所有文件属性

-v:输出详细过程

-p:保持文件权限

-g:保持文件属组信息

-o:保持文件属主信息

-l:保留软连接

-r:对子目录以递归模式处理

-p22:22表示端口

 

2.1.8、关闭防火墙

[root@mysql-leo-master ~]# systemctl stop firewalld

[root@mysql-leo-master ~]# systemctl disable firewalld

 

2.2、从节点操作

2.2.1、修改从库my.cnf文件

[mysql@mysql-leo-slave ~]$ cp /home/mysql/etc/my.cnf /home/mysql/etc/my.cnf.bak

[mysql@mysql-leo-slave ~]$ >/home/mysql/etc/my.cnf

 

添加如下：

[mysql]

 

#CLIENT#

port                                      = 3306

socket                                    = /mysql/data/mysql.sock

 

[mysqld]

basedir                                   = /usr/local/mysql

datadir                                   = /mysql/data/

server_id                                 = 2

port                                      = 3306

sql_mode                                  = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character_set_server                      = utf8mb4

socket                                    = /mysql/data/mysql.sock

secure-file-priv                          = /mysql/bak/file

log-bin                                   = /mysql/binlog/mysql-bin

log-bin-index                             = /mysql/data/mysql-bin.index

relay-log                                 = /mysql/data/relay-bin

relay-log-index                           = /mysql/data/relay-bin.index

 

#GTID

gtid_mode                                 = on

enforce_gtid_consistency                  = on

innodb_buffer_pool_size                   = 2G

 

read_only                                 = on

 

[mysqld_safe]

pid_file                                  = /mysql/data/mysql.pid

log_error                                 = /mysql/data/mysql_error.log

 

说明：从节点在my.cnf文件中配置GTID主从复制，除server_id不同外，其余与主节点配置相同.从节点在配置文件还需添加"read_only＝on"，使从节点只能读，此配置不影响从节点复制，不过此参数对超级用户无效.

 

2.2.2、重启mysql

--配置完成my.cnf后，重启mysql服务.

[mysql@mysql-leo-slave ~]$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3

 

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

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> shutdown;

Query OK, 0 rows affected (0.00 sec)

 

mysql> exit

Bye

[mysql@mysql-leo-slave ~]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &

 

2.2.3、导入数据

--将主节点备份的数据导入从节点中.

[mysql@mysql-leo-slave ~]$ ls -ltr /tmp/full_backupdb_full.sql

-rw-rw-r--. 1 mysql mysql 791494 Aug 26 12:03 /tmp/full_backupdb_full.sql

[root@leo-mysql-slave1 ~]# mysql -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.42-log MySQL Community Server (GPL)

 

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.01 sec)

 

mysql> source /tmp/full_backupdb_full.sql

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| booksDB            |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

 

mysql> use booksDB;

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> show tables;

+-------------------+

| Tables_in_booksDB |

+-------------------+

| authorbook        |

| authors           |

| books             |

+-------------------+

3 rows in set (0.00 sec)

 

2.2.4、关闭防火墙

[root@mysql-leo-slave ~]# systemctl stop firewalld

[root@mysql-leo-slave ~]# systemctl disable firewalld

 

2.2.5、配置主从复制

--在从节点使用change master配置主从复制.

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> change master to master_host='192.168.133.111',master_user='repl',master_password='repl@12345',master_auto_position=1;            

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

 

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

 

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.133.111

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 2842

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 414

        Relay_Master_Log_File: mysql-bin.000004

             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: 2842

              Relay_Log_Space: 615

              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: d0dddbd8-43bd-11ee-ac2f-000c29763af3

             Master_Info_File: /mysql/data/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: d0dddbd8-43bd-11ee-ac2f-000c29763af3:1-9

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

说明：如上所示，主从同步关系配置成功.

 

2.3、数据同步测试

2.3.1、主节点更新数据

--主节点进行数据更新.

[mysql@mysql-leo-master tmp]$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3

 

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

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 master status \G

*************************** 1. row ***************************

             File: mysql-bin.000004

         Position: 2842

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set: d0dddbd8-43bd-11ee-ac2f-000c29763af3:1-9

1 row in set (0.00 sec)

 

mysql> show slave hosts;

+-----------+------+------+-----------+--------------------------------------+

| Server_id | Host | Port | Master_id | Slave_UUID                           |

+-----------+------+------+-----------+--------------------------------------+

|         2 |      | 3306 |         1 | 26d80a96-43be-11ee-a4ec-0050563efe7b |

+-----------+------+------+-----------+--------------------------------------+

1 row in set (0.00 sec)

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| booksDB            |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.01 sec)

 

mysql> create database fruitsDB;

Query OK, 1 row affected (0.00 sec)

 

mysql> use fruitsDB

Database changed

mysql> create table fruits

    -> (

    -> f_id char(10) not null,

    -> s_id int not null,

    -> f_name char(255) not null,

    -> f_price decimal(8,2) not null,

    -> primary key(f_id)

-> ) ENGINE=innodb default charset=utf8 auto_increment=1;

 

mysql> select * from fruits;

+------+------+------------+---------+

| f_id | s_id | f_name     | f_price |

+------+------+------------+---------+

| a1   |  101 | apple      |    5.20 |

| a2   |  103 | apricot    |    2.20 |

| b1   |  101 | blackberry |   10.20 |

| b2   |  104 | berry      |    7.60 |

| b5   |  107 | xxxx       |    3.60 |

| bs1  |  102 | orange     |   11.20 |

| bs2  |  105 | melon      |    8.20 |

| c0   |  101 | cherry     |    3.20 |

| l2   |  104 | lemon      |    6.40 |

| m1   |  106 | mango      |   15.70 |

| m2   |  105 | xbabay     |    2.60 |

| m3   |  105 | xxtt       |   11.60 |

| o2   |  103 | coconut    |    9.20 |

| t1   |  102 | banana     |   10.30 |

| t2   |  102 | grape      |    5.30 |

| t4   |  107 | xbababa    |    3.60 |

+------+------+------------+---------+

16 rows in set (0.00 sec)

 

2.3.2、从节点确认

[mysql@mysql-leo-slave ~]$ 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.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3

 

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

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 databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| booksDB            |

| fruitsDB           |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

6 rows in set (0.01 sec)

 

mysql> use fruitsDB;

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 fruits;

+------+------+------------+---------+

| f_id | s_id | f_name     | f_price |

+------+------+------------+---------+

| a1   |  101 | apple      |    5.20 |

| a2   |  103 | apricot    |    2.20 |

| b1   |  101 | blackberry |   10.20 |

| b2   |  104 | berry      |    7.60 |

| b5   |  107 | xxxx       |    3.60 |

| bs1  |  102 | orange     |   11.20 |

| bs2  |  105 | melon      |    8.20 |

| c0   |  101 | cherry     |    3.20 |

| l2   |  104 | lemon      |    6.40 |

| m1   |  106 | mango      |   15.70 |

| m2   |  105 | xbabay     |    2.60 |

| m3   |  105 | xxtt       |   11.60 |

| o2   |  103 | coconut    |    9.20 |

| t1   |  102 | banana     |   10.30 |

| t2   |  102 | grape      |    5.30 |

| t4   |  107 | xbababa    |    3.60 |

+------+------+------------+---------+

16 rows in set (0.00 sec)

 

小结：如上所示，主从同步正常，至此基于GTID的主从同步复制架构成功部署完毕.

 

3、半同步复制配置

3.1、理论知识

默认情况下MySQL的复制是异步的，master将新生成的binlog发送给各slave后，无需等待slave的ack回复(slave将接收到的binlog写进relay log后才会回复ack)，直接认为此次DDL/DML成功.半同步复制(semi-synchronous replication)是指master将新生成的binlog发送给各slave时, 只需等待一个(默认)slave返回的ack信息就返回成功.

MySQL 5.7对半同步复制作了大改进，新增了一个master线程.在MySQL 5.7以前，master上的binlog dump线程负责两件事：

a、 dump日志给slave的io_thread；

b、 接收来自slave的ack消息，它们是串行的工作方式.在MySQL 5.7中新增一个专门负责接收ack消息的线程ack collector thread，因此master上有两个线程独立工作，可以同时发送binlog到slave和接收slave的ack.还新增了几个变量，其中最重要的是rpl_semi_sync_master_wait_point，其使得MySQL半同步复制有两种工作模型.

半同步复制的两种类型

从MySQL 5.7.2开始，MySQL支持两种类型的半同步复制.这两种类型由变量 rpl_semi_sync_master_wait_point (MySQL 5.7.2之前没有该变量)控制，其有两个取值：AFTER_SYNC和AFTER_COMMIT.

在MySQL 5.7.2之后，默认值为AFTER_SYNC，在此版本之前，等价的类型为AFTER_COMMIT.该变量控制master何时提交、何时接收ack以及何时回复成功信息给客户端.

AFTER_SYNC模式：master将新事务写进binlog(buffer)后发送给slave，再sync到自己的binlog file(disk), 之后才允许接收slave的ack回复，接收到ack之后才会提交事务，并返回成功信息给客户端.

AFTER_COMMIT模式：master将新事务写进binlog(buffer)后发送给slave，再sync到自己的binlog file(disk)，然后直接提交事务.之后才允许接收slave的ack回复，然后再返回成功信息给客户端.

3.2、基于GTID的半同步复制环境部署

MySQL半同步复制配置可以参考: https://www.cnblogs.com/kevingrace/p/10228694.html

开启半同步复制的方法有两种：a、mysql命令行启动；b、my.cnf文件里添加启动配置；

说明：推荐在my.cnf文件里添加半同步启动配置方式.

3.2.1、主节点加载插件

--在主节点上加载插件(前提semisync_master.so文件存在，一般mysql安装后就默认产生)

[root@mysql-leo-master ~]# find / -name semisync_master.so

/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/plugin/semisync_master.so

[root@mysql-leo-master ~]# find / -name semisync_slave.so

/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/plugin/semisync_slave.so

 

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

Empty set (0.01 sec)

 

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.00 sec)

 

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.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        |

| rpl_semi_sync_slave  | ACTIVE        |

+----------------------+---------------+

2 rows in set (0.00 sec)

 

--查看半同步参数情况.

mysql> show status like '%rpl_semi%';

+--------------------------------------------+-------+

| 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                | 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     |

| Rpl_semi_sync_slave_status                 | OFF   |

+--------------------------------------------+-------+

15 rows in set (0.00 sec)

 

3.2.2、修改主库my.cnf文件

--配置主节点上my.cnf文件，添加启动半同步复制的配置.

[mysql@mysql-leo-master etc]$ vi my.cnf

[mysql]

#CLIENT#

port                                      = 3306

socket                                    = /mysql/data/mysql.sock

 

[mysqld]

basedir                                   = /usr/local/mysql

datadir                                   = /mysql/data/

server_id                                 = 1

port                                      = 3306

sql_mode                                  = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character_set_server                      = utf8mb4

socket                                    = /mysql/data/mysql.sock

secure-file-priv                          = /mysql/bak/file

log-bin                                   = /mysql/binlog/mysql-bin

log-bin-index                             = /mysql/data/mysql-bin.index

relay-log                                 = /mysql/data/relay-bin

relay-log-index                           = /mysql/data/relay-bin.index

 

#GTID

gtid_mode                                 = on

enforce_gtid_consistency                  = on

innodb_buffer_pool_size                   = 2G

 

#开启半同步复制

plugin-load=rpl_semi_sync_master=semisync_master.so

rpl_semi_sync_master_wait_no_slave        = 0

rpl_semi_sync_master_enabled              = 1

rpl_semi_sync_master_timeout              = 10000

rpl_semi_sync_slave_enabled               = 0

rpl_semi_sync_master_wait_for_slave_count = 1

rpl_semi_sync_master_wait_point           = AFTER_SYNC

 

 

[mysqld_safe]

pid_file                                  = /mysql/data/mysql.pid

log_error                                 = /mysql/data/mysql_error.log

 

说明：蓝色高亮部分为添加内容.

 

3.2.3、重启主节点mysql进程

--重启主节点mysql服务.

[mysql@mysql-leo-master etc]$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3

 

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

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> shutdown;

Query OK, 0 rows affected (0.00 sec)

 

mysql> exit

Bye

[mysql@mysql-leo-master etc]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &

 

3.2.4、从节点加载插件

在从节点上加载插件(前提是/usr/lib64/mysql/plugin/semisync_slave.so 文件存在，一般mysql安装后就默认产生)

[root@mysql-leo-slave local]# find / -name semisync_master.so

/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/plugin/semisync_master.so

[root@mysql-leo-slave local]# find / -name semisync_slave.so

/usr/local/Percona-Server-5.7.21-20-Linux.x86_64.ssl101/lib/mysql/plugin/semisync_slave.so

[mysql@mysql-leo-slave ~]$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.21-20-log Percona Server (GPL), Release 20, Revision ed217b06ca3

 

Copyright (c) 2009-2018 Percona LLC and/or its affiliates

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> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

Empty set (0.00 sec)

 

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.00 sec)

 

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.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        |

| rpl_semi_sync_slave  | ACTIVE        |

+----------------------+---------------+

2 rows in set (0.00 sec)

 

--查看半同步参数情况.

mysql> show status like '%rpl_semi%';

+--------------------------------------------+-------+

| 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                | 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     |

| Rpl_semi_sync_slave_status                 | OFF   |

+--------------------------------------------+-------+

15 rows in set (0.00 sec)

 

3.2.5、修改从节点my.cnf文件

--修改从节点my.cnf文件，添加启动半同步复制的配置.

[mysql@mysql-leo-slave etc]$ vi my.cnf

--按如下修改

[mysql]

 

#CLIENT#

port                                      = 3306

socket                                    = /mysql/data/mysql.sock

 

[mysqld]

basedir                                   = /usr/local/mysql

datadir                                   = /mysql/data/

server_id                                 = 2

port                                      = 3306

sql_mode                                  = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character_set_server                      = utf8mb4

socket                                    = /mysql/data/mysql.sock

secure-file-priv                          = /mysql/bak/file

log-bin                                   = /mysql/binlog/mysql-bin

log-bin-index                             = /mysql/data/mysql-bin.index

relay-log                                 = /mysql/data/relay-bin

relay-log-index                           = /mysql/data/relay-bin.index

 

#GTID

gtid_mode                                 = on

enforce_gtid_consistency                  = on

innodb_buffer_pool_size                   = 2G

 

read_only                                 = on

 

#开启半同步复制

plugin-load=rpl_semi_sync_master=semisync_master.so

rpl_semi_sync_master_wait_no_slave        = 0

rpl_semi_sync_master_enabled              = 0

rpl_semi_sync_master_timeout              = 10000

rpl_semi_sync_slave_enabled               = 1

rpl_semi_sync_master_wait_for_slave_count = 1

rpl_semi_sync_master_wait_point           = AFTER_SYNC

 

[mysqld_safe]

pid_file                                  = /mysql/data/mysql.pid

log_error                                 = /mysql/data/mysql_error.log

 

说明：蓝色高亮为新增部分.

 

3.2.6、重启从库mysql进程

mysql> shutdown;

Query OK, 0 rows affected (0.00 sec)

[mysql@mysql-leo-slave etc]$ /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &

 

3.2.7、开启slave进程

--从节点开启slave进程

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.01 sec)

 

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.133.111

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 194

               Relay_Log_File: relay-bin.000006

                Relay_Log_Pos: 407

        Relay_Master_Log_File: mysql-bin.000006

             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: 194

              Relay_Log_Space: 648

              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: d0dddbd8-43bd-11ee-ac2f-000c29763af3

             Master_Info_File: /mysql/data/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: d0dddbd8-43bd-11ee-ac2f-000c29763af3:10-12

            Executed_Gtid_Set: d0dddbd8-43bd-11ee-ac2f-000c29763af3:1-12

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

3.2.8、半同步验证

--从节点情况

mysql> show status like '%rpl_semi%';

+--------------------------------------------+-------+

| 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                | 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     |

| Rpl_semi_sync_slave_status                 | ON    |

+--------------------------------------------+-------+

15 rows in set (0.00 sec)

 

--主节点情况.

mysql> show status like '%rpl_semi%';

+--------------------------------------------+-------+

| 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             | 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     |

| Rpl_semi_sync_slave_status                 | OFF   |

+--------------------------------------------+-------+

15 rows in set (0.00 sec)

 

说明：Rpl_semi_sync_master_clients数值为1，说明此时主库存在一个半同步复制的从节点.Rpl_semi_sync_master_yes_tx数值为0，说明此时还没有半同步复制的sql语句被执行.

 

4、主从半同步测试

4.1、主节点更新数据

--接着在主库删除数据，测试半同步复制.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| booksDB            |

| fruitsDB           |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

6 rows in set (0.00 sec)

 

--删除booksDB

mysql> drop database booksDB;

Query OK, 3 rows affected (0.02 sec)

 

4.2、从节点查看数据

--mysql-slave1从节点1查看，发现新数据已经同步过来.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| fruitsDB           |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

 

4.3、主节点再次确认半同步从节点数据

--接着再去mysql-master主节点查看.

mysql> show status like '%rpl_semi%';

+--------------------------------------------+-------+

| 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             | 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      | 869   |

| Rpl_semi_sync_master_tx_wait_time          | 869   |

| 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_slave_status                 | OFF   |

+--------------------------------------------+-------+

15 rows in set (0.01 sec)

说明：如上所示Rpl_semi_sync_master_yes_tx的数值更新为1，即发生过一条半同步复制的sql语句.因每个从库都有可能成为主库，所以每个节点都开启binlog日志，并创建replication账号以及semisync的so文件安装及相关配置

参考网址：https://blog.51cto.com/u_6215974/4938547