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

Editor to share with you the mysql master-slave synchronization principle, configuration and delay example analysis, I hope you will learn something after reading this article, let's discuss it together!

Mysql master-slave synchronization principle, master-slave synchronization configuration, master-slave synchronization delay, first of all, let's understand what is master-slave synchronization, master-slave synchronization, as the name implies, also known as master-slave replication, used to establish a database environment exactly the same as the master database. Master-slave synchronization enables data to be copied from one database server to other servers to keep the data of the master database consistent with that of the slave database.

The cluster is shared storage and is data-sharing. There is no share in master-slave replication. Each machine is an independent and complete system, which is nothing-sharing.

The principle of master-slave synchronization

There are three main ways to implement master-slave replication after slave mysql5.6:

1. Asynchronous replication

two。 Full synchronous replication

3. Semi-synchronous replication

Master-slave synchronization schematic diagram

1. When the update events of the main database (update, insert, delete) are written to binary-log.

two。 From the library, create an Ibig O thread that connects to the master library and requests the master library to send update records in binlog to the slave library. The master library creates a binlog dump thread thread, sends the contents of the binlog to the slave library, reads the updates sent by the output thread of the master library from the library's I / O thread, and copies these updates to the local relay log file.

3. Create a SQL thread from the library that reads and executes the update event written from the library I _ ram O thread to relay log.

Implementation of master-slave synchronization (asynchronous replication, databases on different servers) 1. Configure the main database to open binary-logvim / etc/my.cnf add server-id=1 (used to identify different databases) log-bin=master-bin (open bin-log and configure file name master-bin) log-bin-index=master-bin.index (distinguish between different log-bin files) under [mysqld]

Restart the database: systemctl restart mariadb.service

two。 Configure to open relay-logvim / etc/my.cnf from database and add server-id=2relay-log=slave-relay-bin under [mysqld] (open relay-log and configure file name as slave-relay-bin) relay-log-index=slave-relay-bin.index

Restart the database: systemctl restart mariadb.service

3. Connect two databases

In the master database: to create a user repl, each slave server needs an account name and password to connect to the master server.

CREATE USER 'repl'@'114.116.77.213' IDENTIFIED BY' 12312 repl'@'114.116.77.213' IDENTIFIED BY Grant REPLICATION SLAVE ON *. * TO '12312

In the slave database:

Change master to master_host='47.106.78.106',master_user='repl',master_password='12312',master_log_file='master-bin.000001',master_log_pos=0

Start synchronization: start slave

4. Verification

Create a database in the master database, and then view it from the database

The role of master-slave synchronization

1. Do the hot backup of data, as a backup database, after the failure of the master database server, you can switch to the slave database to continue to work to avoid data loss.

two。 Read and write separation, so that the database can support greater concurrency.

Matters needing attention of master-slave synchronization

The master library can read and write data, while the slave library can only read the data, because the positon of the data written from the slave library will change, but the position of the master library will not change, and there may be conflicts when the data of the master database changes position.

When the binatylog file of the main library stores a lot of data, that is, when the position is very large, a new binarylog file will be split, and position will be set to 0

The mysql version of the master and slave library may be different, but the mysql version of the slave library is higher than that of the master library. If not, the statement of the master library may not be executed.

Because mysql is backward compatible, that is, lower versions of statements are supported in higher versions, but higher versions of some statements are not supported in lower versions.

Interview related

(if you ask the database master-slave question, you must ask the following questions):

What are the advantages of being a master and follower?

What is the principle of master and slave?

Do you understand the delay in reading from the database? How to solve?

What if the master server hangs up after the master?

The reason for the delay of master-slave synchronization

The reason for the delay of master-slave synchronization

Master-slave synchronization delay problem

1. The reason for the delay of master-slave synchronization

We know that a server opens N links to the client to connect, so there will be large concurrent update operations, but there is only one thread reading binlog from the server. When a SQL is executed on the slave server for a longer time or because a SQL has to lock the table, the SQL of the master server has a large backlog and has not been synchronized to the slave server. This leads to master inconsistency, that is, master-slave delay.

two。 The solution of master-slave synchronization delay

In fact, there is no single way to control the master-slave synchronization delay, because all SQL must be executed in the slave server, but if the master server keeps writing updates, then once there is a delay, the more likely the delay will increase. Of course, we can take some mitigation measures.

a. We know that because the master server is responsible for the update operation, it has higher security requirements than the slave server. All settings can be modified, such as sync_binlog=1,innodb_flush_log_at_trx_commit = 1, while slave does not need such a high level of data security. It is possible to set sync_binlog to 0 or turn off binlog,innodb_flushlog. Innodb_flush_log_at_trx_commit can also be set to 0 to improve the efficiency of sql execution, which can greatly improve efficiency. The other thing is to use a better hardware device than the main library as the slave.

b. That is, a slave server is used as a backup instead of providing a query, where his load comes down, and the efficiency of executing SQL in relay log is naturally high.

c. Increase the slave server, this purpose is to spread the pressure of reading, thereby reducing the load on the server.

After reading this article, I believe you have some understanding of "sample Analysis of mysql Master-Slave synchronization principle, configuration and delay". If you want to know more about it, please follow the industry information channel. Thank you for reading!

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.