Mysql master-slave replication semi-synchronous replication (based on Gtid)

MySQL Master-slave replication
MySQL master-slave replication principle:
There are two thread IO threads and SQL threads from the library
1. From the IO thread of the library sends the request to the main library main process, the main library verifies from the library, to the main library IO thread is responsible for the data transmission;
2. The main library IO thread compares the information in the Master.info sent from the library, sends the Binlog file information, the offset and the Binlog file name, etc. to the slave library
3. After receiving the information from the library, save the Binlog information to Relay-bin, while updating the master.info offset and Binlog file name
4. The SQL thread from the library constantly reads the Relay-bin information, writes the read offset and the file name to the Relay-log.info file, binlog the information into its own database, and completes the synchronization operation.
5. After the last synchronization, the Shang IO thread from the library IO thread is going to binlog the information
6. From the library if you also want to master the library, also open the Log_bin and log-slave-update parameters
To configure the read and write MySQL master-slave replication steps:
1. Install the MySQL database in both the main library and the slave library
2. Configure Server-id and Log-bin in the main library's configuration file (/ETC/MY.CNF)
3. Create an authenticated user and authorize it after logging in to the main library.
4. Configure the Server-id in the configuration file (/ETC/MY.CNF) from the library
5. After logging in from the Vault, specify master and turn on the sync switch.
It is important to note that the configuration of the Server-id master and slave libraries is not the same.
Server-id the existence of the function:
MySQL synchronizes data with Server-id, and Server-id is used to identify which server the statement was originally written from. So Server-id must have it.
Server-id can not be the same reason: Each synchronization of the slave on Master has a master thread, which is identified by slave Server-id, each slave on the master side has a maximum master thread, If the server-id of the two slave is the same, then when the latter connection succeeds, slave actively connects to master, if slave stop is performed on slave above, the connection is disconnected but the corresponding thread on master does not exit; when slave After start, master can no longer create a thread and retain the original thread, so synchronization can be problematic;
In MySQL master synchronization, a number of main needs to form a ring, but when synchronizing to ensure that a data does not fall into the dead loop, here is to rely on server-id to achieve;
MySQL Master-slave Replication (Gtid) experiment configuration:

Configuration environment: rhel6.5 iptables Selinx down
172.25.40.1 server1.example.com Master
172.25.40.2 server2.example.com Slave
Installation package: Mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
Install MySQL database after unzipping on master and slave:

tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.taryum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm /etc/init.d/mysqld start grep ‘password‘ /var/log/mysqld.log       ```      #筛选初始化是数据库登陆密码登陆数据库后修改密码：`alter user [email protected] identified by ‘xxxxxxx‘;` ##修改本地用户密码1.Master端的操作：

Vim/etc/my.cnf
[Mysqld]
Server-id=1
Log-bin=mysql-bin
Gtid_mode=on #使用gtid模式
Enforce-gtid-consistency=true #强制使用gtid

Slave端的操作：

[Mysqld]
server-id=2
Gtid_mode=on
Enforce-gtid-consistency=true

注意：在修改两台服务器的mysql配置文件时，一定要保证server-id必须是不同的，server-id的取值范围是：2^32-12.重新启动两台服务器上的mysql` /etc/init.d/mysqld restart`3.进入主库创建认证用户并授权`grant replication slave on *.* to [email protected]‘172.25.40.%‘ identified by ‘xxxxxxxxxx‘;`4.进入slave端指定master

Stop slave;
Change Master to master_host= ' 172.25.40.1 ', master_user= ' Redhat ', master_password= ' xxxxxxxxxx ', master_auto_position = 1;
Start slave;

5.在设置半同步复制master端的操作

mysql> Install plugin rpl_semi_sync_master soname ' semisync_master.so ';
Query OK, 0 rows affected (0.50 sec)

mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows Affected (0.00 sec)

Mysql> show global variables like '%rpl_semi% ';
+-------------------------------------------+------------+
| 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.01 sec)

6.5.在设置半同步复制slave端的操作

mysql> Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';
Query OK, 0 rows affected (0.14 sec)

mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows Affected (0.00 sec)

Mysql> show global variables like '%rpl_semi% ';
+---------------------------------+-------+
| variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | On |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in Set (0.00 sec)

  If the IO thread is turned on at the slave end, it will automatically turn over to the semi-synchronous mode for data transfer![] (Http://i2.51cto.com/images/blog/201805/02/7687411e99cbec08048536a5589ece39.png?x-oss-process=image/watermark, size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Close IO thread A transaction on Master waits for 10s to become asynchronous from the semi-synchronous state. [] (http://i2.51cto.com/images/blog/201805/02/3f2914d044166abddd25f04bacc788e1.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) When the data is inserted for the second time, it becomes asynchronous synchronization! [] (http://i2.51cto.com/images/blog/201805/02/21df15b4774e91b721b9856d9355ea15.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Mysql parallel copy multi-threaded work slave Modify profile:  

[Mysqld]
Slave-parallel-type=logical_clock
Slave-parallel-workers=16 #开启16个线程工作
Master_info_repository=table #以表的形式存放master_info的信息
Relay_log_info_repository=table #以表的形式存放relay_log_info的信息
Relay_log_recovery=on #

重启服务`/etc/init.d/mysqld restart`当进入数据库的mysql库中，可以执行sql语句：`select * from slave_master_info;`可以看到原本在/var/lib/mysql下的relay-log.info的文件已经转为数据库中的表。

Mysql master-slave replication semi-synchronous replication (based on Gtid)