Shulou(Shulou.com)06/01 Report--

Following the author to understand a simple understanding of mysql master-slave synchronization principle, I believe you will benefit a lot after reading, the text in the essence is not much, I hope a simple understanding of mysql master-slave synchronization principle of this short content is what you want.

Introduction to mysql Master-Slave replication

In the current production work, the mysql master-slave synchronization of most applications is asynchronous replication, that is, not strict real-time data synchronization.

Real-time and asynchronous:

Synchronous replication: it means that the client connects to the MySQL master CVM to write a piece of data, and the MySQL master CVM synchronizes to the MySQL. The slave CVM needs to wait for the response to be synchronized from the CVM before returning to the client OK. The process of waiting for synchronization is blocked. If there are N slave CVMs, the efficiency is very low.

Asynchronous replication: it means that the client connects to the MySQL master cloud server and writes a piece of data. The MySQL master cloud server sends the written data to the MySQL slave cloud server, and then returns directly to the client OK. The data from the slave cloud server may be inconsistent with the master service.

Semi-synchronous replication: it means that the client connects to the MySQL master CVM to write a piece of data. The MySQL master CVM only replicates the data synchronously to one of the slave CVMs, and semi-synchronously replicates to the other slave CVMs to achieve the effect of complete synchronization from one of the slave CVMs.

Principle of master-slave replication:

It takes two threads to complete the slave library, one is called Igamo thread, and the other is called sql thread.

The main library requires a thread called the Ithumb O thread

The master database must open the binlog log to complete master-slave synchronization. When a user requests to the master library, the additions and deletions will be recorded in the binlog log. Master-slave replication is to find the master database from the slave library. When establishing, we use change master to specify master's ip, port, binary file name, pos,master password and other information on the slave database. When you open start slave on the slave library, synchronization will be enabled.

After synchronization is enabled, the request is first initiated from the slave to the master. Then the master library verifies whether the slave database is normal. After verification, the master library will send logs to the slave database according to the information. The place where the logs are stored in the slave library is called relay log. In fact, there is also a master info information in the slave library, which records the change master information. Every time the log is retrieved, the master info information of the slave library will be updated. Next, according to the binlog information of master info, the slave library goes to the main library to fetch the new binlog information.

Put it in the relay log, how do we update it?

When the master sends the binlog to the relay log, the sql thread will convert the statement and write it to the slave library.

Be careful

1. Open binlog log

2.change master (on slave library)

3. Before opening start slave from the library. The data of the two libraries must be the same, so using master-data=1 to back up the data of the main library will record the location. Then the backup point is specified from the master backup from the library.

4. An account dedicated to master-slave synchronization should be established before the switch.

5. The process of opening the switch from the library is actually the process of calling two threads from the library.

6.start slave

In addition to the binlog log content, the main database returns the new binlog file name on the Master CVM and the next specified update location in the binlog after the log content is returned this time. This location will be saved in the master info of the slave library.

Flush tables with read lock; executes read locking on the main library

Then open another window and connect to the main mysql

> show master status; to check the binlog and posvalue of the main library.

The next step is to back up the master database data, and the backup data will be executed on the slave database. The goal is to make the data of the slave database the same as the master database.

After importing the backup data of the master library from the slave library, you should call the lock:

> unlocks tables

Then execute the change master statement on the slave library. This information will be recorded in the master.info of the slave library; (if you specify master-data=1 when you back up)

The last step is to open start slave.

Check to see if you have successfully used show slave status; to see if both processes are yes.

From the relay-log.info in the library: record the logs read by the sql thread from the relay.

After reading this article about the principle of master-slave synchronization in mysql, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.