In-depth analysis of semi-synchronous and asynchronous MySQL master-slave replication configurations,

In-depth analysis of semi-synchronous and asynchronous MySQL master-slave replication configurations,

In short, MySQL master-slave replication is a C/S architecture application. The master can be considered as the server in the general sense, and the slave can be considered as a client. The I/O thread on the slave requests data on the master. After the master verifies the information of the slave, it allows the slave to access and then sends the data change information.
I. Principle of MySQL master-slave Replication
Here I take MySQL5.5 as an example to illustrate the principle of MySQL master-slave replication:

First, the I/O thread of the slave node requests data from the master node. After the master node passes the verification, the dump thread sends the data to the slave node. The I/O thread of the slave node writes the data to the relay log after receiving the resource, after the SQL thread of the slave node detects the change of the relay log, it will immediately follow the relay log Content and the content of the new slave database. This completes the synchronization process.

Ii. Common replication Models
1. One master and one slave model

The advantage of this architecture is that it is relatively simple, easy to build and maintain, and low cost. This model can be used for scenarios where the load volume is not particularly large and the reliability requirements are not very high. However, this architecture is not suitable for some websites with relatively high load and high availability requirements. If the traffic volume is large, the pressure on the Master node will be relatively high, and if the Master node crashes, it will also lead to Business Termination.
2. One-master, multiple-slave model

In most scenarios, our applications use read and write operations. We use this architecture and read/write splitting technology to effectively reduce the read Pressure on the Master. We can perform data backup and data mining on the backend slave. However, if there are many slave databases and the master database is responsible for other requests, the pressure on the master database will increase significantly. At this time, the master database will become the performance bottleneck of the entire system.
 
Of course, there are other replication models, such as multi-level relay and loop replication. The basic principles of these replication models are similar to those mentioned above. I will not explain them in detail here.
3. Configure master-slave Replication
(1) asynchronous replication
Master-slave synchronization conditions:
Master:
A: Enable binary logs;
B: select a server-id.
C: create a user with the copy permission
Slave:
A: Enable relay logs.
B: select a unique server-id.
C: connect to the master server and Start copying data
A. First, create A user with the minimum permission for replication on the master database.

mysql> grant replication slave,replication client on *.* to repl@'10.12.%'

 -> identified by '123456';Query OK, 0 rows affected (0.03 sec)

B. Connect to the master database on the slave Database

Mysql> change master to MASTER_HOST = '10. 12.128.19 ', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysql-bin.000006', MASTER_LOG_POS = 123456; # view the replication status mysql> show slave status \ G

*************************** 1. row ***************************    Slave_IO_State: Waiting for master to send event     Master_Host: 10.12.128.19     Master_User: repl     Master_Port: 3306    Connect_Retry: 60    Master_Log_File: mysql-bin.000006   Read_Master_Log_Pos: 1512    Relay_Log_File: relay_index.000002    Relay_Log_Pos: 283  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: 1512    Relay_Log_Space: 452    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: 0Master_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: 3306     Master_UUID: 97f33396-ed12-11e4-921a-000c29e8ee06    Master_Info_File: /mydata/data5.6/master.info     SQL_Delay: 0   SQL_Remaining_Delay: NULL  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it   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: 01 row in set (0.00 sec)

C. Run the following command from the database:

# Start copying mysql> start slave;

You can also start the IO and SQL threads separately.
(If the IO thread status of the slave database is always connecting, it may be the reason for the firewall. Generally, you can disable the firewall or configure firewall rules)
(2) semi-synchronous Replication
Semi-synchronous replication is a plug-in for semi-synchronous replication developed for MySQL based on Google. The principle of semi-synchronous replication is that after a transaction is executed on the master server, it must at least ensure that the transaction is committed successfully after at least one slave server is executed. If the slave server does not respond within a certain period of time, it will be automatically downgraded to asynchronous replication.
This semi-synchronous replication is based on asynchronous replication.
First, install Google's semi-sync plug-in:

Master:

install plugin rpl_semi_sync_master soname 'semisync_master.so';

 
Slave:

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

Then enable the semi-sync function.

Master:

Set global rpl_semi_sync_master_enabled = 1; set global rpl_semi_sync_master_timeout = 100; // in milliseconds

Slave:

set global rpl_semi_sync_slave_enabled = ON;

You also need to restart the IO thread on the slave database:

stop slave IO_thread;start slave IO_thread;

Check the running status of the semi-sync plug-in on the master database and slave database respectively:

mysql> show global 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    | 0  || Rpl_semi_sync_master_no_times    | 1  || Rpl_semi_sync_master_no_tx     | 8  || 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.04 sec)

mysql> show global status like 'rpl%';

+----------------------------+-------+| Variable_name    | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | ON |+----------------------------+-------+1 row in set (0.04 sec)

As you can see, the semi-sync plug-in of the master and slave databases is enabled.
Now, the asynchronous master-slave configuration is complete.

Articles you may be interested in:

• How to generate a key using ssl in mysql
• Smooth and seamless installation of Apache, SSL, MySQL, and PHP
• Full installation of apache + mysql + php + ssl servers
• Detailed operation tutorial on MySQL master-slave Replication Based on the SSL protocol