Deep parsing of semi-synchronous and asynchronous Mysql master-slave replication configuration _mysql

In simple terms, the master and slave copy of MySQL is a C/s architecture application. Master can be considered to be a client in our usual sense of server,slave. The I/O thread on the slave requests master data, and master verifies that the slave information allows slave access and then sends the data change information.
first, the principle of MySQL master and slave copying
here I take MySQL5.5 as an example of MySQL's principal and subordinate copying principle:

First, the standby node's I/O thread is responsible for requesting data from the master node, and the master node is validated to send the data to the standby node by the dump thread. When the standby node's I/O thread receives the resource, it writes the data to the relay log, and the standby node's SQL thread detects the relay log changes and immediately follows the contents of the new repository based on the contents of the relay log. This completes the synchronization process.

II. Common replication Models
1, one main one from the model

The advantage of this architecture is relatively simple, the construction and maintenance are relatively easy, the cost is relatively low. This model is fully available for situations where the load is not particularly large and the reliability requirements are not particularly high. However, for some of the larger sites, and for the high availability requirements of the occasion, this architecture is not very applicable. Because if the traffic is larger, the pressure on the master node will be compared, and if master crashes, it will cause the business to terminate.
2, one main from the model

In most scenarios, our applications are read-and-write. Using this architecture, we can effectively reduce the pressure on master reading by reading and writing separate technology. We can do some data backup, data mining and so on on the back-end slave. However, if there are more repositories, and the main library is responsible for other requests, the main library pressure will increase significantly, at this time the main library will become the overall system performance bottlenecks.

Of course, there are other replication models, such as multilevel relaying, and circular replication, which are similar to the above, and are no longer explained in detail.
3. Configure Master-slave replication
(1), asynchronous replication
conditions for Master-slave synchronization:
Master:
A: Enable binary logging;
B: Choose a Server-id
C: Create a user with replication permissions
Slave:
A: Enable relay logging
B: Choose a unique Server-id
C: Connect the primary server and start copying data
A, the user who first establishes the minimum permissions for replication on the main library

Mysql> Grant replication Slave,replication client on *.* to repl@ ' 10.12.% '

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

B, connect the main library from the library

 mysql> change MASTER to master_host= ' 10.12.128.19 ', master_port=3306,master_user= '

Repl ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000006 ', master_log_pos=451; #查看复制的状态 mysql> show slave status\g 

1. Row *************************** slave_io_state:waiting for master to send event master_host:10.12.128.19 M Aster_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_I O_running:yes Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicat e_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: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: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:0 1 row in Set (0.00 sec)

C, and then execute from the library:

#启动复制
mysql> start slave;

You can also start the IO thread and the SQL thread separately.
(If the IO thread state from the library has been a connecting state, possibly the cause of the firewall, it is generally possible to shut down the firewall or configure the firewall rules)
(2), semi-synchronous replication
semi-synchronous replication is based on a semi-synchronous replication plug-in that Google has developed for MySQL. The principle of semi-synchronous replication is that once a transaction is performed on the primary server, the transaction must be at least guaranteed to be successful after at least one execution from the server has been completed. If there is no response from the server for a certain amount of time, it is automatically demoted to asynchronous replication.
This semi synchronous replication is built on the basis of asynchronous replication.
First you need to install Google's semi-sync plugin:

Master

Install plugin rpl_semi_sync_master soname ' semisync_master.so ';

Slave

Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';

Then turn on the Half sync feature

Master

Set global rpl_semi_sync_master_enabled = 1;
Set global rpl_semi_sync_master_timeout = 100; Calculated in milliseconds

Slave

Set global rpl_semi_sync_slave_enabled = on;

You will also need to restart the IO thread from the library:

Stop slave io_thread;
Start slave io_thread;

View the status of the semi-synchronous plug-in running on the main and standby units separately:

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  |
+--------------------------------------------+-------+
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 primary and standby half-sync plug-ins are already enabled.
This completes the asynchronous master-slave configuration.