MySQL synchronization (I) Basic knowledge

From MySQL Manual
 

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. The SELECT 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.

It should be very important to realize that binary logs only record update operations at the beginning of enabling binary logs. All slave data must be copied from the master when binary logs are enabled. If the data on the slave is inconsistent with that on the master node when binary logs are enabled during synchronization, the slave synchronization will fail.

One of the methods to copy DATA on the master is to execute the load data from master Statement on slave. However, you must note that load data from master is available only after MySQL 4.0.0, and only MyISAM tables on the master are supported. 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 load data from master statement only when the DATA on the MASTER is small, or allow a long read lock on the master. Since each system loads data from the MASTER at different speeds, 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. The retry Interval is controlled by the -- master-connect-retry option. The 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. After the start slave statement is executed, 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. The Binlog Dump thread in the result of executing the show processlist Statement on the master is. The I/O thread on the slave reads the statements sent by the Binlog Dump thread of the master and copies 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 slave is stopped 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 the slave is started again, it will continue to execute them. 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.

The execution of the show processlist statement will tell us what happens on the master and slave.

The following example shows what the three threads in the show processlist result are. This is the result of executing show processlist on MySQL 4.0.15 and the update. The content of the State field is more meaningful than that displayed in the old version.

On the master, the result of show processlist 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

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, the result of show processlist 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 that the value of the Time field 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 lists the most common statuses in the Binlog Dump thread State field of the master. If there is no Binlog Dump thread on the master, the 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 most common states in the slave I/O thread State field. Starting from MySQL 4.1.1, The Slave_IO_State field in the result of executing the show slave status statement also appears. This means you can only execute the show slave status statement 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 specified by the -- master-connect-retry option.

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 is idle, the status may last for a long time. If it waits for more than slave_read_timeout seconds, a timeout occurs. 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 for master-connect-retry seconds before re-connection.

Reconnecting after a failed master event read

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

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

The relay_log_space_limit value is non-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 following lists the most common statuses in the slave SQL thread State 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.

The State field of the SQL thread 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_name is the host name of the server, and nnn is the serial number. Relay logs are created based on the sequence number, starting from 000001 (MySQL 4.0 and 001. 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. The default file name can be replaced by the system options -- relay-log and -- relay-log-index. For details, see "6.8 Replication Startup Options ".

The relay log and binary log are in the same format, so you can use mysqlbinlog to read them. 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.14, If you execute flush logs, the 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 ).

During log refreshing, for example, execute the flush logs statement or run the mysqladmin flush-logs command (new relay LOGS will be created starting from MySQL 4.0.14 ).

The current relay log size is too large; "too large" is determined in this way:

Max_relay_log_size, if max_relay_log_size> 0

Max_binlog_size, if max_relay_log_size = 0 or MySQL is lower 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 is similar to the result of executing the show slave status statement. 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.

The 'master. info' file is updated by the I/O thread. Before MySQL 4.1, the file content is the same as the field value in the result of executing the show slave status statement, 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

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

The 'relay -log.info 'file is updated by the SQL thread. The file content is the same as the field value in the result of executing the show slave status statement:

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, but the 'relay -log.info 'file still exists, you can determine how many master binary logs are executed by the SQL thread. Then execute the change master to statement with the MASTER_LOG_FILE and MASTER_LOG_POS options 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 synchronize the load data infile statement, it also needs to back up any 'SQL _ LOAD-*' file in the corresponding directory. This can be used to continue Synchronization After the load data infile is interrupted. This directory is specified by the -- slave-load-tmpdir option. By default, if not specified, its value is the value of the variable tmpdir.

Reference:

Http://database.ccidnet.com/art/1105/20060810/791491_1.html