MySQL manual version 5.0.20-mysql sync (i)

Mysql

6 MySQL Sync

The sync feature is introduced in MySQL 3.23.15, which can replicate data from a MySQL server to another server. This chapter describes the various replication features of MySQL. Describes the concept of synchronization, how to set up a synchronization server, and the references to available servers. It also provides a list of common questions and answers, troubleshooting.

Synchronization-related SQL statement syntax is described in "14.6 Replication statements".

We recommend frequent visits to "http://www.mysql.com" to regularly read the latest content in this chapter. Synchronization has been improving, and we often update this part of the manual to the most current content.

6.1 Introduction to Synchronization

MySQL 3.23.15 and higher versions support one-way synchronization. One server as master (primary server), one or more servers as slave (from 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 updated portions of these logs are sent to the slave server. After a slave is connected to master, it notifies Master of the last successful incremental update of the log location. Slave will find all the updates that start at that moment, and then block and wait for master to send a new update operation.

If you want to make a sync server chain, slave can also be master.

Note that when synchronization is enabled, all update operations to be synchronized must be performed on master. Otherwise, you must be careful not to cause conflicts between the user's update on master and the update on slave.

The benefits of one-way synchronization are robust, high-speed, system easy to manage:

With the master/slave mechanism, it is more robust. When a problem occurs on master, you can switch the slave as an alternate.

You can share some queries between slave and master, which can speed up response time. The SELECT query can be executed on the slave to reduce the load on master. Statements that update data are executed on the Mater to keep master and slave synchronized. Load balancing works well when the non-update operation is the majority, but this is only normal.

Another benefit is that you can back up your data on slave without interfering with master. Master continues to operate as the data is backed up. Please see "5.7.1 Database Backups" for details.

6.2 Synchronization Mechanism Implementation overview

The MySQL synchronization mechanism is based on master to record all updates, deletes, etc. of the database in the binary log. Therefore, to enable the synchronization mechanism, you must enable binary logging in master. Please see "5.9.4 the Binary Log" for details.

Each slave accepts an update operation that was recorded in the binary log from master, so a copy of this operation was performed on slave.

It should be very important to realize that the binary log only records the update operation at a time when the binary log was enabled. All slave must copy the data already existing on master when the binary log is enabled. If the data on the slave when the synchronization is running is inconsistent with the data on master when the binary log is enabled, then slave synchronization fails.

One way to copy data from Master is to execute the LOAD data from master statement on slave. Note, however, that the LOAD DATA from MASTER is available only after the MySQL 4.0.0 and supports only the MyISAM type tables on MASTER. Similarly, this operation requires a global read lock, so that when the log is sent to slave, there will be no update on master. When a free lock table hot backup is implemented (in MySQL 5.0), the global read lock is not necessary.

Because of these limitations, we recommend that the LOAD data from master statement be executed only when the relevant data is relatively small in master, or that a long read lock is allowed on master. Since the speed of LOAD data from MASTER is different between each system, a better measure is to copy 1MB of data per second. This is just a rough estimate, but Master and slave are Pentium 700MHz machines and can achieve this speed when connected to a 100mbit/s network.

Once the master data has been fully copied on the slave, you can connect to master and wait for the update to be processed. If master or slave connection is disconnected, slave periodically attempts to connect to master until it is reconnected and waits for an update. The time interval for retries is controlled by the--master-connect-retry option, which defaults to 60 seconds.

Each slave records the log location when it is closed. Msater is not sure how many slave connections come up or which slave to start updating.

6.3 Sync Implementation Details

The MySQL sync feature is implemented by 3 threads (1 on Master, 2 on slave). After the START SLAVE statement is executed, SLAVE creates an I/O thread. The I/O line Chenglian to master and requests master to send the statements in the binary log. Master creates a thread to send the contents of the log to the slave. The Binlog Dump thread is the result of this thread executing the show Processlist statement on Master. The I/O thread on the slave reads the statements sent by Master's Binlog Dump thread and copies them to the relay log (relay logs) in its data directory. The third is the SQL thread, which salve use to read the relay logs and then execute them to update the data.

As mentioned above, there are 3 threads on each mster/slave. Each master has multiple threads that create a thread for each slave connection, each slave with only I/O and SQL threads.

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

The advantage of using 2 threads on Slave is that it separates read logs from execution into 2 separate tasks. If you perform a task slowly, the read log task will not slow down. For example, if slave stops for a while, the I/O thread can quickly read all the logs from Master after the slave startup, even though the SQL thread may be behind the I/O thread for several hours. If the slave is stopped when the SQL thread is not fully executed, the I/O thread has read all the update logs and saved them in the local relay log, so they will continue to execute after slave starts again. This allows the binary log to be purged on master because slave no longer has to read the update log to master.

Executing the show Processlist statement tells us what is happening on master and slave.

The following example shows the 3 threads in the show processlist result. This is the result of performing show Processlist on MySQL 4.0.15 and updates, and the state field is more meaningful than the old version.

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

In this case, thread 2 is created for a slave connection. The results indicate that all outstanding update logs have been sent to slave, and master is waiting for a new update log to occur.

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 that is connecting to master, and thread 11 is an SQL thread that performs the update operation in the relay log. These 2 threads are now idle and are waiting for a new update log.

Note that the value of the Time field tells us how long the log on the slave is later than master. Please see "6.9 Replication FAQ" for details.

6.3.1 Master Sync Thread state

The following is a list of the most common states in Master's Binlog Dump thread state field. If there is no Binlog Dump thread on master, then synchronization is not running. In other words, there is no slave connection.

Sending Binlog event to Slave

Events are made up of binary logs, and an event is usually added with additional information by an UPDATE statement. The thread reads to an event and is sending it to the slave.

Finished reading one binlog; Switching to next Binlog

Read a binary log and are switching to the next.

has sent all binlog to slave; Waiting for Binlog to is updated

The full part of the update log has been read and is all sent to slave. It is idle, waiting to perform a new update operation on Master to generate new events in the binary log and then read them.

Waiting to finalize termination

The current thread has stopped, this time is very short.

6.3.2 slave I/O thread state

The following is a list of the most common states in the slave I/O thread state field. Starting with the MySQL 4.1.1, this state is also present in the Slave_io_state field that executes the results of the show SLAVE status statement. This means that more information can be learned only by executing the show SLAVE STATUS statement.

Connecting to Master

The thread ID attempts to connect to master.

Checking Master Version

Determines a transient state that occurs after the connection to master.

Registering slave on Master

Determines a transient state that occurs after the connection to master.

Requesting Binlog Dump

Determines a transient state that occurs after the connection to master. The thread sends a request to master telling it which binaries to request and where to start.

Waiting to reconnect after a failed Binlog dump request

If the binary log dump (binary log dump) request fails (due to a disconnect), the thread enters this state during hibernation and is periodically reconnected. The time interval for the connection is specified by the--master-connect-retry option.

Reconnecting after a failed Binlog dump request

The thread is trying to connect to master.

Waiting for master to send event

is already connected to master and is waiting for it to send a binary log. If Master is idle, this state may continue for a long time, and if it waits more than slave_read_timeout seconds, a timeout will occur. At this point, it will consider disconnecting, and then try to reconnect.

Queueing master event to the relay log

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

Waiting to reconnect after a failed master event read

An error occurred while reading the log (due to a disconnected connection). The thread sleeps master-connect-retry seconds before the connection is reset.

Reconnecting after a failed master event read

Trying to connect to master again. When the connection is determined, the status becomes waiting for master to send event.

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

The value of the Relay_log_space_limit is Non-zero, and the size of the relay log exceeds this value. The I/O thread waits for the SQL line enters upgradeable to process the relay logs and then deletes them to free up enough space.

Waiting for slave mutexes on exit

The current thread has stopped, this time is very short.

6.3.3 Slave SQL Thread State

The following lists the most common states in the slave SQL thread State field:

Reading event from the relay log

Read an event from the secondary log for execution.

has read all relay log; Waiting for the slave I/O thread to update it

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

Waiting for slave mutexes on exit

The current thread has stopped, this time is very short.

The state field of an SQL thread can sometimes also be an SQL statement. This means that it reads an event from the relay log, extracts the SQL statement from it, and executes it.

6.3.4 relay logs and status files

By default, the name of the relay log is ' host_name-relay-bin.nnn ', HOST_NAME is the host name of the server, and the nnn is the ordinal. The relay logs are created by ordinal sequence, starting with 000001 (MySQL 4.0 and older, 001). Use an index file on the slave to track which relay logs are currently in use. 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 filename can be replaced by the system options--relay-log and--relay-log-index. Please see "6.8 Replication Startup Options" for details.

Relay logs are the same format as binary logs, so they can also be read with Mysqlbinlog. When the SQL thread reads through all the events in the relay log, it is no longer needed, and it is automatically deleted. There is no explicit deletion mechanism for the relay log because the SQL thread will automatically focus on this. However, starting with the MySQL 4.0.14, the execution of FLUSH LOGS will rotate (rotate) the relay log, allowing the SQL thread to delete them.

A new relay log is created in the following conditions:

When the slave starts, the I/O thread starts the first time (in MySQL 5.0, a new relay log is created each time I/O thread starts, not just the first time it is started).

When the log is refreshed, for example, execute the FLUSH LOGS statement or run the Mysqladmin flush-logs command (a new relay log is created from the MySQL 4.0.14).

The current relay log size is too large; "Too big" is how it's judged:

Max_relay_log_size, if max_relay_log_size > 0.

max_binlog_size if max_relay_log_size = 0 or MySQL is below 4.0.14

Slave will create two additional files in the data file directory. They are state files, and the name defaults to ' Master.info ' and ' relay-log.info '. Their content is similar to the result of the Execute show SLAVE STATUS statement. For more information, see "14.6.2 SQL statements for controlling Slave Servers". Because they are files on disk, they remain after the slave is closed. The next time slave starts, it reads the two files to determine where to read the binary log from master, and where to process the relay logs.

The ' master.info ' file is an I/O thread update. Before MySQL 4.1, the contents of the file were the same as the field values corresponding to the results of the show SLAVE STATUS statement, as follows:

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

Starting with MySQL 4.1, the contents of the file also include the SSL option:

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 shown by SHOW 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 contents of the file are the same as the field values corresponding to the results of 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, back up the two files, along with the relay log. You can use them when you want to restore slave. If you lose the relay log, but the ' relay-log.info ' file still exists, you can tell how many master binary logs the SQL thread has executed. Then execute the change MASTER to statement with the Master_log_file and master_log_pos options to tell slave which location to reread from MASTER's binary log. Of course, this requires that the binary logs associated with master be kept.

If Slav plans to synchronize the LOAD DATA INFILE statement, then also back up any ' sql_load-* ' files in the corresponding directory. This can continue to remain synchronized 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.