MySQL Replication Overview, installation, failure, tips, tools _ MySQL

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 mysql management tool mysql installation

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

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

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

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.