MySQL semi-synchronous replication principle configuration and introduction detailed explanation

Environment Introduction:

Ubuntu Server 16.04.2+MySQL 5.7.17 Community Server (GPL)

MySQL Installation

Install via APT, official guide document address:
https://dev.mysql.com/downloads/repo/apt/

1. Download mysql-apt-config_0.8.3-1_all.deb

2. Install deb

A Quick Guide to Using the MySQL APT Repository:
https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

>sudo dpkg -i mysql-apt-config_0.8.3-1_all.deb

3. Update source

>sudo apt-get update

4. Install MySQL

>sudo apt-get install mysql-server

5. Choose to install and set the password according to the prompts.

In addition, you also need to set it up, bind the IP and open port 3306 of the firewall. Here we mainly learn MySQL semi-synchronous replication, and will not explain much about the installation of MySQL. If you have any questions, please leave a message.

Introduction to MySQL semi-synchronous replication

(1) By default, MySQL's replication function is asynchronous, which can provide the best performance. When the master database sends the binlog log to the slave database, the action is completed, and it does not verify whether the slave database has received the log. This process means that when the master server or the slave server fails, the slave server may not receive the binlog log sent by the master server, which will cause data inconsistency between the master server and the slave server, and even cause data loss during recovery.

Notice:

The semi-synchronous replication mode must be enabled on both the master and slave servers, otherwise the master server uses the asynchronous replication mode by default.

(2) Asynchronous replication case:

MySQL5.6 database master/slave synchronization installation and configuration details

(3) To address the possible errors mentioned above, MySQL 5.5 introduced a semi-synchronous replication mode. This mode ensures that the slave server receives the binlog log file sent by the master server and writes it to its own relay log . It then gives feedback to the master server to tell it that it has been received. Only then does the main service thread return to the current session to inform it that the operation is complete.

(4) When a timeout occurs, the master server will temporarily switch to asynchronous replication mode until at least one slave server receives the information in time.

(5) Self-repair of relay logs:

Starting from MySQL 5.5.X version, the relay_log_recovery parameter has been added. The function of this parameter is: when the slave slave database crashes, if relay.log is damaged, resulting in some relay logs not being processed, all unexecuted relay-log will be automatically abandoned and the logs will be obtained from master again, thus ensuring the integrity of relay-log . This feature is disabled by default. When the value of relay_log_recovery is set to 1, this feature can be enabled on the slave database. It is recommended to enable it.

(6) Switching between semi-synchronous replication and asynchronous replication:

The working principle of semi-synchronous replication is that when the slave IO_Thread thread receives the binlog log, it must give the master a confirmation. If rpl_semi_sync_master_timeout=10000 (10 seconds) and no acceptance confirmation signal is received from the slave for more than 10 seconds, it will automatically switch to the traditional asynchronous replication mode.

MySQL semi-synchronous replication configuration

First, you need to install two MySQLs, here they are:

• Master: 192.168.1.227
• Slave: 192.168.1.224

The original database looks like this:

1. Master Configuration

(1) Install the semi-synchronous replication plug-in in the Master database:

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

(2) Enable semi-synchronous replication on the Master:

mysql>SET GLOBAL rpl_semi_sync_master_enabled = 1; 

(3) Modify mysqld.cnf configuration file:

The above image indicates the file path for MySQL configuration.

[mysqld]

log-bin=mysql-bin
server_id = 10086
server_id_bits = 33
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 

(4) Modify the MySQL server-uuid configuration file:

root@xuliugen:/var/lib/mysql# pwd
/var/lib/mysql

Modify the auto.cnf file. The value format of server-uuid is fixed to distinguish it from the Slave.

[auto]
server-uuid=8d90feb7-1a88-11e7-9d11-000c298a546f

(5) Check whether the configuration is successful:

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

(6) Parameter description:

1. rpl_semi_sync_master_enabled = 1 , indicating that semi-synchronous replication mode has been enabled on the master;

2. rpl_semi_sync_master_timeout = 10000 , which means that if the waiting time of the master database in a transaction exceeds 10000 milliseconds, it will be downgraded to asynchronous replication mode and will not wait for the slave database. If the master database detects that the slave has recovered again, it will automatically switch back to semi-synchronous replication mode;

3. rpl_semi_sync_master_wait_no_slave , indicating whether the master is allowed to wait for the slave's receipt confirmation signal after each transaction is committed. The default is ON, which means that every transaction will wait. If it is OFF, the semi-synchronous replication mode will not be enabled after the slave catches up, and needs to be enabled manually;

4. rpl_semi_sync_master_trace_level = 32 , which refers to the debugging level used when semi-synchronous replication mode is enabled. The default value is 32.

It can be seen that when configuring the Master, only 1 was set, and the others were set to the default settings.

2. Slave Configuration

(1) Install the semi-synchronous replication plug-in in the Slave database:

mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

(2) Enable semi-synchronous replication on the slave:

mysql>SET GLOBAL rpl_semi_sync_slave_enabled = 1; 

(3) Modify mysqld.cnf configuration file:

[mysqld]

log-bin=mysql-bin
server_id=10089
server_id_bits = 32
rpl_semi_sync_slave_enabled = 1 

(4) Modify the MySQL server-uuid configuration file:

root@xuliugen:/var/lib/mysql# pwd
/var/lib/mysql

Modify the auto.cnf file. The value format of server-uuid is fixed to distinguish it from the Slave.

[auto]
server-uuid=8d90feb7-1a88-11e7-9d11-000c298a123f

Make sure it is different from the Master's server-uuid !

(5) Check whether the configuration is successful:

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

(6) Parameter description:

1. rpl_semi_sync_slave_enabled = 1 , indicating that the semi-synchronous replication mode has been enabled on the slave;

2. rpl_semi_sync_slave_trace_level = 32 , which refers to the debugging level used when semi-synchronous replication mode is enabled. The default value is 32.

3. Specifying a Master for a Slave

(1) Restart the Master in command line mode

root@xuliugen:~# service mysql restart

(2) Restarting the Slave in command line mode

root@xuliugen:~# service mysql restart

(3) Check the Master status:

mysql> show master status\G;
*************************** 1. row ***************************
       File:mysql-bin.000004
     Position: 154
   Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.02 sec)

mysql> 

Notice:

File:mysql-bin.000004
Position: 154

Very important, you will need it later!

(4) Specify the Master for the Slave:

1. First, turn off the Slave's semi-synchronous replication

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

2. Specify Master for Slave

mysql>change master to master_host='192.168.1.227',master_user='root',master_password='123456',master_log_file='mysql-bin.000004', master_log_pos=154;

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

in:

master_host='192.168.1.227', Master database IP addressmaster_user='root', Master database accountmaster_password='123456', Master database root user passwordmaster_log_file='mysql-bin.000004', Master database binlog filemaster_log_pos=154, Master database binlog file position

Notice:

After MySQL version 5.6, you can use the GTID method when performing master-slave replication. There is no need to find binglog and pos points. You only need to know the IP, port, account, and password of the master server to automatically find the point for synchronization. When the GTID function is enabled, there is no need to use binlog and pos. For more information, please refer to the relevant materials for study.

3. Enable semi-synchronous replication of Slave

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

4. Test whether semi-synchronous replication is successful

5. How to gracefully close the Slave synchronization information

At some point, a server is no longer used as a slave, so we need to clear its synchronization information, usually using:

mysql> stop slave #Shut down firstmysql> reset slave #Clear Slave synchronization information

However, when displayed through show master status\G:

mysql> show master status\G;

The synchronization information will still appear. This is because executing reset slave only deletes the master.info and relay-log.info files, but the synchronization information is still there. If someone executes the start slave command to turn on the synchronization function, the synchronization will start from the beginning again, which may cause data loss.

How to make it clearer? Please use the following command:

mysql> stop slave #Shut down firstmysql> reset slave all#Clear Slave synchronization information

Executing show master status\G again will not display any information.

VI. Simulation of some abnormal scenarios

(1) Switching between semi-synchronous replication and asynchronous replication:

The above has introduced why the switch from semi-synchronous replication to asynchronous replication occurs. Now let's simulate a scenario for demonstration.

The scenario is as follows:

1. Close slave synchronization and stop IO receiving binlog logs

mysql> stop slave;

This operation shuts down the IO thread and waits for 10 seconds. If the master does not receive confirmation from the slave, it switches to asynchronous replication mode:

In the figure above, we can see that the slave has turned off the semi-synchronous replication mode and turned it on again:

mysql> start slave; 

At this point, the semi-synchronous replication mode has been restored.

(2) Synchronous error case demonstration:

We first delete the database table ufind from the slave library, and then delete the library ufind again on the master. The synchronization will report an error:

Last_Error: Error 'Can't drop database 'ufind'; database doesn't exist' on query. Default database: 'ufind'. Query: 'DROP DATABASE `ufind`'

At this point, check the semi-synchronous status:

It is enabled, so the semi-synchronous replication mode is not converted to asynchronous replication mode. It can be seen that the semi-synchronous replication mode is directly related to IO_Thread , but has nothing to do with SQL_THREAD .

That is to say, after receiving the binary log from the slave library, it gives the master library a confirmation, but it does not care whether relay-log relay log is executed.

6. Performance analysis, advantages and disadvantages of semi-synchronous replication mode

Under normal circumstances, since the asynchronous replication mode does not need to wait for the response from the server, its speed is faster than the semi-synchronous replication mode and its throughput is higher. When updating, inserting, and deleting data, its speed is higher than the semi-synchronous replication mode. However, the semi-synchronous replication mode is beneficial to data consistency. For some data with high consistency requirements and small network fluctuations, the semi-synchronous replication mode can be used.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.