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

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 IO 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.

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.

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:
The 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:
The Code is as follows:
Mysql> grant replication slave on *.*
TO' '@' '
Identified' ';

Note: For security and flexibility, do not use root and other authorized users as copy accounts. Then set the configuration file of the master server (default:/etc/my. cnf ):
The 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 this parameter is not specified, the host name is used by default. Once the host name changes, a problem occurs. Therefore, we recommend that you specify log_bin (the relay_log of the slave server has the same problem ).

Note: The three options are set for security purposes and are not required for replication.

Then set the slave server configuration file (default:/etc/my. cnf ):
The 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, it is invalid.

Note: If yes, the replication will not start from the server unless the command is used.

Note: setting for the slave server to record logs helps switch the slave server 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:
The Code is as follows:
Shell> mysqldump -- all-databases -- master-data = 1> data. SQL

Note: The option is opened by default and the statement is written.

If the database uses the InnoDB table type, use:
The 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:
The 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 ):
The 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:
The Code is as follows:
Mysql> CHANGE MASTER
MASTER_HOST =' ',
MASTER_USER =' ',
MASTER_PASSWORD =' ',
MASTER_LOG_FILE =' ',
MASTER_LOG_POS = ;



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:
The 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:
The Code is as follows:
Mysql> CHANGE MASTER
MASTER_HOST =' ',
MASTER_USER =' ',
MASTER_PASSWORD =' ',
MASTER_LOG_FILE =' ',
MASTER_LOG_POS = ;


Regardless of the method, remember to start the replication on the slave server and check whether the operation is normal:
The 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:
The Code is as follows:
Shell> mysqlbinlog >/Dev/null
Shell> mysqlbinlog >/Dev/null

If no error exists, no output is displayed. Otherwise, an error is displayed.
If the primary log is incorrect, use it on the slave server as follows:
The 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:
The Code is as follows:
Mysql> CHANGE MASTER
MASTER_LOG_FILE =' ',
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:

In addition, there are many interesting projects in Google Project Hosting that can be searched by TAG.

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

(Source :)