Detailed explanation of MySQL replication

Source: Internet
Author: User
Tags copy key log sql mysql query thread

MySQL replication is quite complex compared to the replication of NoSQL databases such as MongoDB,redis !


The primary server first logs data changes to the primary log, then reads the master log on the primary server through the I/O thread from the server, writes it to the relay log from the server, and then the SQL thread reads the relay log and replays it from the server to achieve MySQL replication. As shown in the following illustration:

MySQL Replication

The entire process is reflected from the server, corresponding to three sets of log information, can be viewed from the server with the following command:


Master_log_file & Read_master_log_pos: The next transmitted primary log information.

Relay_master_log_file & Exec_master_log_pos: The next execution of the primary log information.

Relay_log_file & relay_log_pos: Next relay log information executed.

Understanding the meaning of these log messages is critical to resolving the problem, which is explained in detail later in this article.


Create a replication account on the primary server first:


Note: For security and flexibility, do not use the SUPER privilege user as root as the replication account.

Then set the primary server configuration file (default:/ETC/MY.CNF):


server_id = 100
log_bin = mysql-bin
log_bin_index = mysql-bin.index
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1

Note: Be sure to ensure that the principal and subordinate server server_id unique, avoid conflict.

Note: If you do not specify Log_bin , the lack of the provincial capital using the hostname as the name, so that once the hostname has changed, it will be problematic, so the recommended designation Log_bin (from the server relay_log the same problem).

Note:sync_binlog,innodb_flush_log_at_trx_commit,innodb_support_xa Three options are set for security purposes, is not a required option for replication.

Then set the From server configuration file (default:/ETC/MY.CNF):


server_id = 200
log_bin = mysql-bin
log_bin_index = mysql-bin.index
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
read_only = 1
skip_slave_start = 1
log_slave_updates = 1

Note: If the user has Super permission, read_only is invalid.

Note: If you have skip_slave_start, replication will not start from the server unless you use the start slave command.

Note: setting log_slave_updatesto log logs from the server helps to switch from the master to the main when necessary.

The most important step below is how to clone a copy of the primary server's data:

If the database is using a MyISAM table type, you can do the following:

shell> mysqldump --all-databases --master-data=1 > data.sql

Note: Themaster-data option lacks the capital to open lock-all-tablesand writes the change master to statement.

If the database is using a InnoDB table type, you should use single-transcation:

shell> mysqldump --all-databases --single-transaction --master-data=1 > data.sql

With the data file, transfer to the server and import:

shell> mysql < data.sql

If the volume of data is very large, the mysqldump will be very slow, then directly copy data files can save a lot of time:

Before copying, lock the data before obtaining the relevant log information (FILE & POSITION):



Next copy the data file, if it is MyISAM table type, direct copy can be, if it is InnoDB table type, must stop the MySQL service copy, otherwise the copy file may not be used. Copy the data file directly to the data directory from the server.

Finally, you will need to specify the log information again:


Note: Do not set Master_user and Master_password in the MY.CNF configuration file because the information in the file that the change MASTER will generate is ultimately in effect.

Copying data files directly on the primary server is fast, but requires locking the table or stopping the service, which can affect online services. If you have previously had a from server, then you can use the old server as a parent to clone new from the server:

First query log information from the old server:


What we need is one of the Relay_master_log_file & Exec_master_log_pos.

The data is then obtained from the previous method on the old server and restored on the new server.

Next, set the log information on the new server:


Whichever way you do it, remember to start replication from the server and check to see if it's working correctly:



If both IO threads and SQL threads Show Yes, you can thank God:

Slave_io_running correspondence: Master_log_file & Read_master_log_pos

Slave_sql_running correspondence: Relay_master_log_file & Exec_master_log_pos

If no is displayed, some previous configuration steps have been faulted, or there is a problem with the corresponding log file.


Question: Master-slave replication has more than a stop, what should I do?

Answer: Replication errors are mostly due to log errors, so first of all to find out whether the primary log error or relay log error, from the error message generally can be judged, if you can not use similar to the following Mysqlbinlog command:

shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null

shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null

If there are no errors, there will be no output, and conversely, if there is an error, it will be displayed.

If this is a primary log error, you will need to use SET GLOBAL sql_slave_skip_counterfrom the server as follows:

mysql> SET GLOBAL sql_slave_skip_counter = 1;


Note: If you have multiple errors, you may need to perform multiple times (reminders: the master-slave server data may be inconsistent).

If it is a relay log error, as long as the log information in the show SLAVE status result is reset from the server to the MASTER to, the system discards the current relay log and downloads it again:


As for why Relay_master_log_file & Exec_master_log_pos are used, see Overview.

Problem: Primary server down, how to upgrade from the server to the main server?

Answer: In a master multiple from the environment total, you need to choose the latest data from the server to do the new master server. As shown in the following illustration:

Promote from server to primary server

In a primary (Server1) two-(Server2, SERVER3) environment, Server1 downtime, wait until Server1 and Server2 synchronize the logs before they are down, comparing Master_log_file and Read_master_ Log_pos can tell who is fast and who is slow, because Server2 data synchronized from Server1 (1582) is newer than SERVER3 data synchronized from Server1 (1493), so the Server2 should be promoted as the new primary server, so Server3 What parameters should be used when the change MASTER to Server2? 1582-1493=89, and Server2 's last binary log position is 8167, so the answer is 8167-89=8078.


Tables in the master-slave server can use different table types. For example, the main server can use the InnoDB table type, providing transaction, row lock and other advanced features, from the server can use the MyISAM table type, less memory consumption, easy backup advantages. There is also an example of if a host server with many from the server, it is bound to affect its performance, at this time can take out a server as a proxy from the server, using the Blackhole table type, only log, do not write data, by it with more than one from the server, thereby enhancing performance.

Tables in the master-slave server can use different key types. For example, the main server with InnoDB, the key with varchar words to save space, using MyISAM from the server, the key with char to improve speed, because the MyISAM has a static table said.

Tables in the master-slave server can use different indexes. Primary server is mainly used to deal with write operations, so in addition to primary key and unique index, such as the guarantee data relationship index can not be added, from the server generally used to cope with read operations, so you can set the index for query characteristics, and further, different from the server can be different from the query set different indexes


There are some excellent tools to make your copy work more effective, please refer to the respective documentation for details:

Multi-master Replication Manager for MySQL

Percona Xtrabackup



In addition, there are many interesting items in Google project hosting that can be searched with mysql+replication tags.

Note: This article refers to the relevant content in the books listed below:

High performance mysql:optimization, Backups, Replication, and more

MySQL high availability:tools for building robust Data Centers

(Source: Fire Ding notes )

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.