MySQL replication overview, installation, failure, skills, 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 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.

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.

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:

MySQL Replication

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:

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:

mysql> GRANT REPLICATION SLAVE ON *.*       TO '
 
  '@'
  
   '       IDENTIFIED BY '
   
    ';
   
  
 

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 ):

[mysqld]server_id = 100log_bin = mysql-binlog_bin_index = mysql-bin.indexsync_binlog = 1innodb_flush_log_at_trx_commit = 1innodb_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. If they are not set, once the master server goes down, data may be too late to be written to the disk, resulting in an error similar to the following during the replication process on the slave server:

• Client requested master to start replication from impossible position

Because the log data has been lost, this type of problem cannot be solved. You can only reinstall the synchronization slave server.

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

[mysqld]server_id = 200log_bin = mysql-binlog_bin_index = mysql-bin.indexrelay_log = mysql-relay-binrelay_log_index = mysql-relay-bin.indexread_only = 1skip_slave_start = 1log_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:

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:

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

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

shell> mysql < data.sql

Of course, pipeline operators can also be used for one-time operations throughout the process:

shell> mysqldump --host=
 
   ... | mysql --host=
  
    ...
  
 

Because we set master-data = 1, the system will automatically set the parameters MASTER_LOG_FILE and MASTER_LOG_POS. We also need to set the remaining parameters:

mysql> CHANGE MASTER TO       MASTER_HOST='
 
  ',       MASTER_USER='
  
   ',       MASTER_PASSWORD='
   
    ';
   
  
 

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 ):

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:

mysql> CHANGE MASTER TO       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:

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:

mysql> CHANGE MASTER TO       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:

mysql> START SLAVE;mysql> SHOW SLAVE STATUS;

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

• Slave_IO_Running: Yes
• Slave_ SQL _Running: Yes

If No is displayed, some configuration steps are incorrect. Repeat the preceding steps.

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:

shell> mysqlbinlog 
 
   > /dev/nullshell> mysqlbinlog 
  
    > /dev/null
  
 

If no error exists, no output is displayed. Otherwise, an error is displayed.

If the primary log is incorrect, you must manually find the correct log information and CHANGE the master to again:

mysql> CHANGE MASTER TO       MASTER_LOG_FILE='
 
  ',       MASTER_LOG_POS=
  
   ;mysql> START SLAVE;
  
 

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:

mysql> CHANGE MASTER TO       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.

Sometimes a duplicate key error may occur due to a BUG or a write operation performed on the slave server. The error message is as follows:

• Error 'duplicate entry... 'For key... On query

In this case, it is best to manually confirm and delete the invalid data on the slave server, and then copy the correct data from the master server. If the error persists, you can use set global SQL _slave_skip_counter on the slave server, as shown below:

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, in this case, you can use pt-table-checksum and pt-table-sync to check and repair slave server data ).

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 Server2 and Server3 finish executing all 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 not be added. The slave server is used to cope with read operations, therefore, you can set indexes for query features, or even different slave servers can set different indexes for different queries.

Tools

There are some excellent tools that can help you get twice the result with half the effort. For details, please refer to their respective documents:

• Multi-Master Replication Manager for MySQL
• Percona XtraBackup
• Openark Kit
• Percona Toolkit
• Tunsten-replicator

Percona and SkySQL both provide MySQL online configuration tools, which are very convenient to use.

Supplement: Yoshinori releases the MySQL-MHA project, which helps improve the reliability of MySQL master-slave replication.

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

I hope that my summary will save you some detours.

Original article: http://huoding.com/2011/04/05/59

Original article address: MySQL replication overview, installation, failure, skills, tools, thanks to the original author for sharing.