MySQL replication overview, installation, failure, tips, and tools)

Compared with the replication of NoSQL databases such as MongoDB and Redis, MySQL replication is quite complex!

Overview

First, the master server records data changes to the master log, then reads the master log on the master server from the server through the I/O thread, and writes it to the slave server's relay log, then, the SQL thread reads the relay log and replays it on the slave server to achieve MySQL replication. As shown in:

The whole process is reflected on the slave server. You can view three sets of log information on the slave server with the following command:Copy codeThe Code is as follows: mysql> show slave status;

Master_Log_File & Read_Master_Log_Pos: information of the next master log.
Relay_Master_Log_File & Exec_Master_Log_Pos: information about the main log of the next execution.
Relay_Log_File & Relay_Log_Pos: information about the next relay log.
Understanding the meaning of the log information is essential for fault resolution, which will be described in detail later.
Install
Create a copy account on the master server:Copy codeThe Code is as follows: mysql> grant replication slave on *.*
TO '<SLAVE_USER>' @ '<SLAVE_HOST>'
Identified by '<SLAVE_PASSWORD> ';

NOTE: For the sake of security and flexibility, do not use a user with SUPER permissions such as root as a copy account. Then set the configuration file of the master server (default:/etc/my. cnf ):Copy codeThe Code is as follows: [mysqld]

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: ensure that the server_id of the master and slave servers is unique to avoid conflicts.

NOTE: If log_bin is not specified, the host name will be used by default. As a result, once the host name changes, a problem may occur, therefore, we recommend that you specify log_bin (the relay_log of the slave server has the same problem ).

Note: sync_binlog, innodb_flush_log_at_trx_commit, and innodb_support_xa are all set for security purposes and are not required for replication.

Then set the slave server configuration file (default:/etc/my. cnf ):

Copy codeThe Code is as follows: [mysqld]
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 the SUPER permission, read_only is invalid.

NOTE: With skip_slave_start, replication will not START on the server unless the start slave command is used.

Note: Setting log_slave_updates allows the slave server to record logs, which helps switch the slave to the master when necessary.

The following most important step is to clone the data of the master server:

If the database uses the MyISAM Table type, perform the following operations:

Copy codeThe Code is as follows: shell> mysqldump -- all-databases -- master-data = 1> data. SQL

Note: by default, the master-data option enables lock-all-tables and writes the change master to statement.

If the database uses the InnoDB table type, use single-transcation:

Copy codeThe Code is as follows: shell> mysqldump -- all-databases -- single-transaction -- master-data = 1> data. SQL

With the data file, transfer it to the slave server and import it:Copy codeThe Code is as follows: shell> mysql <data. SQL

If the data volume is large, mysqldump will be very slow. Copying data files directly saves a lot of time:
You must lock the data before obtaining the relevant log information (FILE & POSITION ):Copy codeThe Code is as follows: mysql> flush tables with read lock;
Mysql> show master status;

Next, copy the data file directly if it is of the MyISAM Table type. If it is of the InnoDB table type, stop the MySQL service before copying it. Otherwise, the copy file may not be available. Copy the copied data file directly to the data directory of the slave server.
Finally, you need to specify the log information:Copy codeThe Code is as follows: mysql> CHANGE MASTER
MASTER_HOST = '<MASTER_HOST> ',
MASTER_USER = '<SLAVE_USER> ',
MASTER_PASSWORD = '<SLAVE_PASSWORD> ',
MASTER_LOG_FILE = '<FILE> ',
MASTER_LOG_POS = <POSITION>;

Note: Do not set MASTER_USER and MASTER_PASSWORD in the my. cnf configuration file, because the final effect is the information in the master.info file generated by the change master.

Although copying data files directly on the master server is fast, you need to lock the table or stop the service, which affects online services. If you already have a slave server, you can use the old slave server as the master slave server to create a new slave server:

First, query the log information on the old slave server:Copy codeThe Code is as follows: mysql> show slave status;

What we need is Relay_Master_Log_File & Exec_Master_Log_Pos.

Then, the old slave server obtains the data according to the previous method and restores the data on the new slave server.

Then, set the log information on the new slave server:Copy codeThe Code is as follows: mysql> CHANGE MASTER
MASTER_HOST = '<MASTER_HOST> ',
MASTER_USER = '<SLAVE_USER> ',
MASTER_PASSWORD = '<SLAVE_PASSWORD> ',
MASTER_LOG_FILE = '<Relay_Master_Log_File> ',
MASTER_LOG_POS = <Exec_Master_Log_Pos>;

Regardless of the method, remember to start the replication on the slave server and check whether the operation is normal:Copy codeThe Code is as follows: mysql> start slave;
Mysql> show slave status;

If the IO thread and SQL thread both show Yes, thank God:

Slave_IO_Running: Master_Log_File & Read_Master_Log_Pos

Slave_ SQL _Running: Relay_Master_Log_File & Exec_Master_Log_Pos

If No is displayed, the preceding configuration steps are incorrect or the corresponding log file is faulty.

Fault

Q: Why does the master-slave replication stop? What should I do?

Answer: Most of the replication errors are caused by log errors. First, you need to identify whether the primary log error is a relay log error or a relay log error. Generally, you can determine from the error information, if the following mysqlbinlog command cannot be used:Copy codeThe Code is as follows: shell> mysqlbinlog <MASTER_BINLOG_FILE>/dev/null
Shell> mysqlbinlog <SLAVE_BINLOG_FILE>/dev/null

If no error exists, no output is displayed. Otherwise, an error is displayed.
If a primary log error occurs, use set global SQL _slave_skip_counter on the slave server as follows:Copy codeThe Code is as follows: mysql> set global SQL _slave_skip_counter = 1;
Mysql> start slave;

NOTE: If there are multiple errors, you may need to execute them multiple times (Note: The Master/Slave server data may be inconsistent ).
If a relay log error occurs, you only need TO use the log information in the show slave status result on the SLAVE server to change the master to again. The system will discard the current relay log and download it again:Copy codeThe Code is as follows: mysql> CHANGE MASTER
MASTER_LOG_FILE = '<Relay_Master_Log_File> ',
MASTER_LOG_POS = <Exec_Master_Log_Pos>;
Mysql> start slave;

For details about why Relay_Master_Log_File & Exec_Master_Log_Pos is used, see the overview.
Q: When the master server goes down, how can I upgrade the slave server to the master server?
Answer: In a master-slave environment, you need to select the slave server with the latest data to create a new master server. As shown in:

Upgrade slave server to master server

In a single-master (Server1) and two-slave (Server2, and Server3) environments, after Server1 goes down, wait until Server1 and Server2 finish executing the logs synchronized before the downtime, compared with Master_Log_File and Read_Master_Log_Pos, you can determine who is going to be slow, because the data synchronized from Server1 (1582) is newer than the data synchronized from Server3 from Server1 (1493, so we should upgrade Server2 TO a new MASTER server. What parameters should Server3 use when changing master to Server2? 1582-1493 = 89, while the last binary log location of Server2 is 8167, so the answer is 8167-89 = 8078.

Tips

Tables on the Master/Slave server can use different table types. For example, the master server can use the InnoDB table type to provide advanced features such as transactions and row locks. The slave server can use the MyISAM Table type, with fewer memory consumption and ease of backup. Another example is that if a master server carries many slave servers at the same time, it will inevitably affect its performance. In this case, you can use a server as the slave server proxy and use the BLACKHOLE table type, it only records logs and does not write data. It carries multiple slave servers to improve performance.

Tables on the Master/Slave server can use different key types. For example, if the master server uses InnoDB and VARCHAR, the slave server uses MyISAM and CHAR to increase the speed, because MyISAM has a static table.

Tables on the Master/Slave server can use different indexes. The master server is mainly used to cope with write operations. Therefore, except for primary keys and unique indexes, indexes that ensure data relations can generally be left blank. slave servers are generally used to cope with read operations, therefore, you can set indexes for query features. Further, different slave servers can set different indexes for different queries.

Tools

There are some excellent tools to make your copy work more efficient. For details, refer to their respective documents:

Multi-Master Replication Manager for MySQL

Percona XtraBackup

Maatkit

Tunsten-replicator

In addition, there are many interesting projects in the Google Project Hosting, which can be searched using the mysql + replication tag.

Note: This article provides reference to the books listed below:

High Performance MySQL: Optimization, Backups, Replication, and More

MySQL High Availability: Tools for Building Robust Data Centers

(Source: huoding notes)