Shulou(Shulou.com)05/31 Report--

This article mainly introduces the semi-synchronous and asynchronous MySQL master-slave replication how to configure, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

First, the principle of MySQL master-slave replication

Here I will take MySQL5.5 as an example to talk about the principle of master-slave replication of MySQL:

First of all, the I / O thread of the standby node is responsible for requesting data from the primary node. After the verification of the primary node, the dump thread will send the data to the standby node. After receiving the resource, the I / O thread of the standby node will write the data to the relay log. When the SQL thread of the standby node detects the change of the relay log, it will immediately follow the contents of the new database according to the contents of the relay log. This completes the process of synchronization.

Second, 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. For some occasions where the load is not very large and the requirement of reliability is not very high, this model can be used. However, for some sites with high load and high availability requirements, this architecture is not very suitable. Because if the visit volume is relatively large, the pressure on the Master node will be compared. In addition, if the Master crashes, it will also lead to the termination of the business.

2. One master and multiple slaves model

In most scenarios, our application is to read and write more. We use this architecture, through the read-write separation technology, can effectively reduce the pressure of reading on the Master. We can do some data backup, data mining and other work on the back-end slave. However, if there are many spare libraries, and the main library is responsible for other requests, the pressure on the main library will increase obviously, and the main library will become the performance bottleneck of the whole system.

Of course, there are other replication models, such as multi-level relay and ring replication, the basic principles of which are similar to those above, which will not be explained in detail here.

3. Configure master-slave replication

(1) Asynchronous replication

Conditions for master-slave synchronization:

Master:

A: enable binary logging

B: select a server-id

C: create a user with replication permissions

Slave:

A: enable relay log

B: select a unique server-id

C: connect to the primary server and start replicating data

A. the user who first establishes the minimum permissions for replication on the main library

Grant replication slave,replication client on *. * to repl@'10.12.%' identified by '123456'

B. Connect the master library on the slave library

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

# View the status of replication

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

Start replication

Start slave

You can also start IO threads and SQL threads separately.

(if the IO thread state of the slave library has always been the state of connecting, which may be due to the firewall, generally speaking, it is OK to turn off the firewall or configure firewall rules.)

(2) semi-synchronous replication

Semi-synchronous replication is a plug-in for semi-synchronous replication developed for MySQL by Google. The principle of semi-synchronous replication is that after a transaction is completed on the master server, it must ensure that at least one slave server is completed before the transaction is considered to be successful. If there is no response from the server within a certain period of time, it is automatically degraded to asynchronous replication.

This semi-synchronous replication is based on asynchronous replication.

First, you need to install the semi-synchronous plug-in for Google:

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 function of semi-synchronization.

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 also need to restart the IO thread on the slave library:

Stop slave IO_thread

Start slave IO_thread

View the running status of the semi-synchronous plug-in on the main 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-synchronous plug-ins for both the main library and the standby library are already enabled.

Thank you for reading this article carefully. I hope the article "how to configure semi-synchronous and asynchronous MySQL master-slave replication" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.