MySQL synchronization (1)

Translator: ye Jinrong (Email: 6MySQL synchronization function was introduced in MySQL3.23.15, which can copy data from one MySQL server to another. This chapter describes various replication features of MySQL. This section describes the concept of synchronization, how to set the synchronization server, and the reference for available servers. Also provides a series of common questions

Translator: ye Jinrong (Email: 6 MySQL synchronization function was introduced in MySQL 3.23.15, which can copy data from one MySQL server to another. This chapter describes various replication features of MySQL. This section describes the concept of synchronization, how to set the synchronization server, and the reference for available servers. Also provides a series of common questions

Translator: ye Jinrong (Email:

6. MySQL Synchronization

The synchronization function was introduced in MySQL 3.23.15. It can copy data from one MySQL server to another. This chapter describes various replication features of MySQL. This section describes the concept of synchronization, how to set the synchronization server, and the reference for available servers. It also provides a series of frequently asked questions, answers, and answers.

"14.6 Replication Statements" describes SQL statement syntaxes related to synchronization.

We recommend that you always visit "http://www.mysql.com" to read the latest in this chapter. The synchronization function has been improving. We often update this manual to the latest content.

6.1 synchronization Introduction

MySQL 3.23.15 and later support unidirectional synchronization. One server acts as the master server, and one or more servers act as the slave server ). The master server writes the updated content to the binary log (binary log or binlog) and maintains an index file to record the log loop. The updates in these logs are sent to the slave server. After a Server Load balancer instance is connected to the master, it notifies the master of the log location for the last successful incremental update. Slave will find all the update operations starting from that time, and then block and wait for the master to send new update operations.

If you want to create a synchronization server chain, slave can also be used as a master.

Note: after synchronization is enabled, all update operations to be synchronized must be performed on the master. Otherwise, do not cause conflicts between updates on the master node and those on the slave node.

One-way synchronization provides robust, high-speed, and easy System Management:

• With the master/slave mechanism, it becomes more stable. When a problem occurs on the master node, you can use slave as a backup switch.
• You can share some queries between the slave and the master to accelerate the response time.SELECTThe query can be executed on the slave to reduce the load on the master. The statement for updating data should be executed on the mater to keep the master and slave synchronized. When the majority of non-update operations are performed, Server Load balancer is very effective, but this is only common.
• Another benefit is that data can be backed up on the slave without interfering with the master. The master continues to operate during data backup. For details, see "5.7.1 Database Backups ".
  

6.2 overview of synchronization mechanism implementation

The MySQL synchronization mechanism records all database updates and deletions Based on the master node in binary logs. Therefore, to enable the synchronization mechanism, binary logs must be enabled on the master node. For details, see "5.9.4 The Binary Log ".

Each slave receives an update operation recorded in the binary log on the master. Therefore, a copy of this operation is executed on the slave.

ShouldVery importantIt is realized that binary logs only record update operations from the time when binary logs are enabled. All slave data must be copied from the master when binary logs are enabled. If the data on the slave and the masterWhen binary logs are enabledIf the data is inconsistent, the slave synchronization will fail.

One of the methods to copy the data on the master is actually executed on slave.LOAD DATA FROM MASTERStatement. Note that,LOAD DATA FROM MASTERIt can be used only after MySQL 4.0.0 and only on the masterMyISAMType table. Similarly, this operation requires a global read lock, so that no update operation will be performed on the master when the log is transferred to the slave. When the free lock table hot backup is implemented (in MySQL 5.0), the global read lock is unnecessary.

Because of these restrictions, we recommend that you run the command only when the data on the master is small.LOAD DATA FROM MASTERStatement, or a long read lock is allowed on the master. BecauseLOAD DATA FROM MASTERThe speed varies. A good measurement rule is that 1 MB of data can be copied per second. This is just a rough estimate, but the master and slave are both Pentium 100 MHz machines and can achieve this speed when connected with a MBit/s network.

After the master data has been fully copied on the slave, you can connect to the master and wait for updates. If the master is disconnected from the server or slave, slave periodically tries to connect to the master until it can be reconnected and waits for updates. Retry Interval--master-connect-retryThe default value is 60 seconds.

Each slave records the log location when it is disabled. Msater does not know how many slave connections are established or when the slave is updated.

6.3 synchronization Implementation Details

The MySQL synchronization function is implemented by three threads (one on the master node and two on the slave node. RunSTART SLAVEThen, slave creates an I/O thread. The I/O thread connects to the master and requests the master to send statements in binary logs. The master creates a thread to send the log content to the slave. This thread is executed on the master.SHOW PROCESSLISTIn the result after the statementBinlog DumpThread is. The I/O thread on the slave readsBinlog DumpStatement sent by the thread, and copy them to the relay logs in the data directory. The third is the SQL thread, which salve uses to read relay logs and then execute them to update data.

As mentioned above, each mster/slave has three threads. Each master has multiple threads. It creates a thread for each slave connection, and each slave has only I/O and SQL threads.

Before MySQL 4.0.2, only two threads (one master and one slave) are required for synchronization ). The I/O and SQL threads on the slave are merged into one. It does not use relay logs.

The advantage of using two threads on slave is to split the read log and execution into two independent tasks. If the task is executed slowly, the log reading task will not be slowed down. For example, if the slave stops for a period of time, the I/O thread can quickly read all the logs from the master after the slave is started, although the SQL thread may lag behind the I/O thread for several hours. If slave stops running all the SQL threads, but the I/O thread has read all the update logs and saved them in the local relay log, therefore, after slave is started again, it will continue to be executed. This allows you to clear the binary log on the master, because the slave does not need to read the Update log from the master.

RunSHOW PROCESSLISTThe statement will tell us what happens on the master and slave.

The following example showsSHOW PROCESSLISTWhat are the three threads in the result. This is executed on MySQL 4.0.15 and the update.SHOW PROCESSLISTResults,StateThe field content is more meaningful than that displayed in the earlier version.

On the master,SHOW PROCESSLISTThe result is as follows:

mysql> SHOW PROCESSLIST/G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost:32931
     db: NULL
Command: Binlog Dump
   Time: 94
  State: Has sent all binlog to slave; waiting for binlog to
         be updated
   Info: NULL

Here, thread 2 is created for an slave connection. The results show that all the unfinished update logs have been sent to slave, and the master is waiting for the new update log to happen.

On slave,SHOW PROCESSLISTThe result is as follows:

mysql> SHOW PROCESSLIST/G
*************************** 1. row ***************************
     Id: 10
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 11
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 11
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 11
  State: Has read all relay log; waiting for the slave I/O
         thread to update it
   Info: NULL

This indicates that thread 10 is an I/O thread and is being connected to the master; thread 11 is an SQL thread that performs the update operation in the relay log. Now, both threads are idle and are waiting for new update logs.

Note,TimeThe field value tells us how long the log on the slave is later than the master. For details, see "6.9 Replication FAQ ".

6.3.1 Master synchronization thread status

The following listsBinlog DumpThreadStateThe most common status of a field. If noBinlog DumpThe synchronization is not running. That is to say, there is no slave connection.

Sending binlog event to slave

An event is composed of binary logs. An event is usually composed of an update statement and other information. The thread reads an event and sends it to slave.

Finished reading one binlog; switching to next binlog

After reading a binary log, switch to the next one.

Has sent all binlog to slave; waiting for binlog to be updated

You have read all the incomplete update logs and sent them to slave. It is idle and is waiting to execute new update operations on the master to generate new events in binary logs and then read them.

Waiting to finalize termination

The current thread has stopped. This time is short.

6.3.2 Slave I/O thread status

The following lists the slave I/O threads.StateThe most common status of a field. Starting from MySQL 4.1.1, this status is being executedSHOW SLAVE STATUSStatement resultSlave_IO_StateFields will also appear. This means that you can only executeSHOW SLAVE STATUSStatement to learn more.

Connecting to master

This thread certificate tries to connect to the master.

Checking master version

Determine a transient status after the master node is connected.

Registering slave on master

Determine a transient status after the master node is connected.

Requesting binlog dump

Determine a transient status after the master node is connected. This thread sends a request to the master to inform it of the binary file to be requested and the starting position.

Waiting to reconnect after a failed binlog dump request

If a binary log dump Request fails (due to disconnection), the thread enters this state during sleep and reconnects regularly. The reconnection interval is --master-connect-retryOption.

Reconnecting after a failed binlog dump request

This thread is trying to reconnect to the master.

Waiting for master to send event

Connected to the master, waiting for it to send binary logs. If the master node is idle, the status may last for a long time. slave_read_timeoutSeconds. In this case, it will consider disconnecting and then trying to reconnect.

Queueing master event to the relay log

An event has been read and is being copied to the relay log for processing by the SQL thread.

Waiting to reconnect after a failed master event read

An error occurred while reading the log (due to disconnection ). This thread sleep before Reconnection master-connect-retrySeconds.

Reconnecting after a failed master event read

Attempting to reconnect to the master. After the connection is confirmed, the status changes Waiting for master to send event.

Waiting for the slave SQL thread to free enough relay log space

relay_log_space_limitThe value is not zero. The total size of the relay log exceeds this value. The I/O thread waits for the SQL thread to process the relay logs and then delete them to free up enough space.

Waiting for slave mutex on exit

The current thread has stopped. This time is short.

6.3.3 Slave SQL thread status

The SQL threads of slave are listed belowStateThe most common status of a field:

Reading event from the relay log

Read an event from the relay log for execution.

Has read all relay log; waiting for the slave I/O thread to update it

All the events in the relay log have been processed and are waiting for the I/O thread to write the updated log.

Waiting for slave mutex on exit

The current thread has stopped. This time is short.

SQL threadStateA field may also be an SQL statement. This means that it reads an event from the relay log, extracts the SQL statement from it, and runs it.

6.3.4 relay logs and status files

By default, the relay log name format is`Host_name-Relay-bin.Nnn`,Host_nameIs the Host Name of the server,NnnYes. Relay logs are created based on the sequence number.000001(MySQL 4.0 and old is001. Slave uses an index file to track the currently used relay logs. The default relay log index file name is`Host_name-Relay-bin.index'. By default, this file is located in the slave data file directory. Default file names can be based on System Options--relay-logAnd--relay-log-indexTo replace. For details, see "6.8 Replication Startup Options ".

The relay log and binary log are in the same format, so you can usemysqlbinlog. After the SQL thread reads all the events in the relay log, it no longer needs it and will be automatically deleted. The relay log does not have an explicit deletion mechanism because the SQL thread automatically follows this mechanism. However, starting from MySQL 4.0.14FLUSH LOGSThe rotate relay logs will be rotated and the SQL thread will delete them.

A new relay log is created under the following conditions:

• After slave is started, the I/O thread starts for the first time (in MySQL 5.0, a new relay log is created every time the I/O thread starts, not just when it starts for the first time ).
• When refreshing logs; for example, executeFLUSH LOGSStatement or runmysqladmin flush-logsCommand (the new relay log will be created from MySQL 4.0.14 ).
• The current relay log size is too large; "too large" is determined in this way:
  
  
  
  • max_relay_log_size, Ifmax_relay_log_size> 0
    
  • max_binlog_size, Ifmax_relay_log_size= 0 or MySQL is earlier than 4.0.14
    
  
  

Slave creates two additional files in the data file directory. They are status files. The default names are'Master. info'And'Relay -log.info'. Their content and executionSHOW SLAVE STATUSStatement results are similar. For details, see "14.6.2 SQL Statements for Controlling Slave Servers ". Because they are files on the disk, they will be retained after slave is disabled. When the next slave is started, the two files will be read to determine where the binary log is read from the master and where the relay log is processed.

'Master. info'File Source I/O thread update. Before MySQL 4.1, file content and executionSHOW SLAVE STATUSThe corresponding field values in the statement results are the same, as shown below:

Line	Description
1	Master_Log_File
2	Read_Master_Log_Pos
3	Master_Host
4	Master_User
5	Password (not shownSHOW SLAVE STATUS)
6	Master_Port
7	Connect_Retry

Starting from MySQL 4.1, the file content also includes SSL options:

Line	Description
1	Number of lines in the file
2	Master_Log_File
3	Read_Master_Log_Pos
4	Master_Host
5	Master_User
6	Password (not shownSHOW SLAVE STATUS)
7	Master_Port
8	Connect_Retry
9	Master_SSL_Allowed
10	Master_SSL_CA_File
11	Master_SSL_CA_Path
12	Master_SSL_Cert
13	Master_SSL_Cipher
14	Master_SSL_Key

'Relay -log.info'Files are updated by SQL threads. File Content and executionSHOW SLAVE STATUSThe corresponding field values in the statement results are the same:

Line	Description
1	Relay_Log_File
2	Relay_Log_Pos
3	Relay_Master_Log_File
4	Exec_Master_Log_Pos

When backing up slave Data, you need to back up the two files together with the relay log. They are used when you want to restore the slave. If the relay log is lost'Relay -log.info'If the file still exists, you can determine the number of master binary logs executed by the SQL thread. Then executeCHANGE MASTER TOStatementMASTER_LOG_FILEAndMASTER_LOG_POSOption to tell slave where to re-read the binary log of the master. Of course, this requires that binary logs on the master still exist.

If slav intends to synchronizeLOAD DATA INFILETo back up any'SQL _ LOAD -*'File. This can be found inLOAD DATA INFILEAfter being interrupted, the synchronization continues. This directory is composed--slave-load-tmpdirOption. By default, if not specified, its value is a variable.tmpdir.

• MySQL synchronization (2)
• MySQL synchronization (III)
• MySQL synchronization (4)
• MySQL synchronization (5)

MySQL synchronization (ii )›