MySQL master-slave synchronization and principle

MySQL Master-slave Replication Introduction

In the current production work, most of the MySQL master-slave synchronization is asynchronous replication mode, that is, not strictly real-time data synchronization.

Real-time and asynchronous:

: Refers to the client connecting to the mysql Primary server write a piece of data,   mysql Primary server synchronize to mysql from the server You need to wait for a response from the server to complete the synchronization to return the client OK, This is where the process of waiting for synchronization is blocked, if there are N stations from the server, the efficiency is very low  

: Refers to the client connecting to the mysql Primary server write a piece of data,   mysql Primary server Send the data written to mysql from the server and then directly back to the client OK, the data from the server may be inconsistent with the primary service  

semi-synchronous replication : Refers to a client connecting to a MySQL master server write a piece of data, MySQL master server Replicate data only to one of the slave servers, semi-synchronous to the other from the server, to achieve a full synchronization from the server effect

Master-Slave replication principle:

From the library requires two threads to complete, one called I/O thread, one called SQL thread

The main library requires a thread called I/O thread

The main library must open the Binlog log to complete the master-slave synchronization, when the user requests to the main library, will be added to the deletion of the Binlog log, the master copy is from the library to Brahma Library, when established, we use change master from the library to specify the master IP, port, Binary file name, pos,master password and other information. And starting the start slave from the library will turn on synchronization.

When synchronization is turned on, the request is initiated from the primary. Then the main library to verify from the library is normal, after verification, the main library will send the log from the library according to the information, the place to store the log from the library is called the Relay log (relay log), in fact, there is a master info from the library, which is recorded in the Change master information, Every time you take the log back will be the master info from the library to update, and then from the library based on the master info binlog information to the main library is taking the new binlog information,

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

When the Lord Binlog sends it over to the trunk log, the SQL thread translates the statements into the library.

Attention

1. Turn on Binlog log

2.change Master (on from library)

3. Before opening the start slave from the library . The data for the two libraries must be the same, so the master library's data is backed up using Master-data=1, which is recorded in the location. Then from the library you will specify the backup point from the main library backup.

4. To establish a dedicated account for master-slave synchronization before the switch

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

6.start slave

In addition to the Binlog log content returned by the main library, there is a new Binlog file name on the master server side and the location of the next specified update in Binlog after the log content is returned. This location is stored in the master info from the library.

>flush tables with read lock, execute plus reading lock on the main library

Then open another window to connect to the main MySQL

>show Master status; View the Binlog and POS values for the main library.

Down is to back up the main library data and execute the backup data from the library. The goal is to get the same data from the library and the main library.

After importing the backup data of the main library from the library, you will call the lock:

>unlocks tables;

The Change master statement is then executed from the library. This information is recorded in the Master.info information from the library (if you specify master-data=1 when you back up)

The last step opens the start slave;

See if show slave status is used successfully; see if two processes are yes.

Relay-log.info from inside the library: logs that are read from the SQL thread.

Describes MySQL master-slave replication principle

Self-summary: The IO thread from the library reads the updated content in real time according to the Binlog log of the Master.info information to the main library, takes the updated content back to its own relay log, and updates the Master.info information, where the SQL thread reads and executes the SQL statements from the log in real time.

Describes MySQL master-slave synchronization deployment

Self-Summary:

1. Back up the data of the main library, master-data=1 the time when the backup is specified. Then import the backup data from the library

2. Bin-log function on the main library to open the main library, and Service-id

Mysql> GRANT REPLICATION SLAVE on * * to ' slave_account ' @ ' 10.121.0.220 ' identified by ' 123456 '

This means that you are allowed to use ' slave-account ' and ' 123456 ' This account password from server ' 10.121.0.220 ' to perform a master-slave copy of all databases (*. *) on the primary server (' REPLICATION slave ').

3. Execute flush tables with relay locks on the main library, read and write locks. Then the collar opens a window

4. Then only execute the change master from the library and specify the user and password you just created.

5. Execution of start slave;

6.show slave status; see if two threads start

7. Unlock on the main library.

MySQL master-slave synchronization and principle