Detailed analysis of MySQL master-slave replication

Preface:

In MySQL, the master-slave architecture should be the most basic and most commonly used architecture. Subsequent read-write separation, multi-active high-availability architecture, etc. mostly rely on master-slave replication. Master-slave replication is also an indispensable part of our MySQL learning process. There are many articles about master-slave replication. I would like to join in the fun and write about this aspect and share my own experience and methods.

1. Introduction and principle of master-slave replication

Master-slave replication (also known as AB replication) means that one server acts as the master database server, and another or more servers act as slave database servers. The data in the master server is automatically copied to the slave servers. With multi-level replication, a database server can act as both a master and a slave. MySQL uses asynchronous replication by default.

The process and principle of master-slave replication can be summarized as follows:

1. The master server records data changes in a binary log. When data on the master changes, the changes are written to the binary log.
2. The slave server will detect whether the master binary log has changed at a certain time interval. If it has changed, it will start an I/OThread to request the master binary event.
3. At the same time, the master node starts a dump thread for each I/O thread to send binary events to it and save them in the local relay log of the slave node. The slave node will start the SQL thread to read the binary log from the relay log and replay it locally to make its data consistent with that of the master node.

2. Configure master-slave replication based on binary file location

Master-slave replication based on binary file position can also be called traditional replication, that is, the slave server depends on the binlog file position of the master server. When the data in the master database changes, the binlog pos position will increase, and the slave database will sense the change and complete the synchronization.

To configure master-slave replication, we first need to prepare at least two MySQL instances, one as the master server and one as the slave server. Since master-slave replication depends on binlog, the master database must have binlog enabled, and the master and slave must be configured with different server_ids. The following is a detailed description of the configuration process:

2.1 Confirm the master-slave library configuration parameters

The following configuration is recommended for the MySQL master-slave server. You can confirm it first. If it is not configured, you need to modify the configuration file and restart it.

# The main library parameter configuration must have the following parameters vim /etc/my.cnf 
[mysqld] 
log-bin = binlog //Enable binary log server-id = 137 //Server unique ID, the default value is 1, usually set to the last digit of the IP address binlog_format = row //Bilog is set to row mode to prevent replication errors # The following parameters are recommended for slaves vim /etc/my.cnf 
[mysqld] 
relay-log = relay-bin
server-id = 138

2.2 Determine the binary location of the main library and create a synchronization account

If the master and slave databases have just been initialized and there is no operation on the master database, the slave database does not need to synchronize the data of the master database and can directly determine the binlog position of the master database.

# View the location of the main library binlog file show master status;

# Create a synchronization account in the main database create user 'repl'@'%' identified by '123456';
grant replication slave on *.* to 'repl'@'%';

If the master database has been running for a while and has business data, and the slave database has just been initialized, you need to back up the data in the master database and then import it into the slave database to make the master and slave data consistent.

# Create a synchronization account in the main database create user 'repl'@'%' identified by '123456';
grant replication slave on *.* to 'repl'@'%';

# mysqldump -uroot -pxxxx -A -R -E --single-transaction --master-data=2 > all_db.sql

# Restore mysql from the database -uroot -pxxxx < all_db.sql

# The binlog location of the main library can be found from the backup file

2.3 Enter the slave database and enable master-slave replication

After finding the location of the master library binary file and completing the consistency of master-slave data, we can officially start master-slave replication.

# Enter the MySQL command line from the slave library and execute the change master statement to connect to the master library # The binary file name and pos position are obtained from the above steps CHANGE MASTER TO MASTER_HOST='MySQL master server IP address',
 MASTER_PORT=3306,
 MASTER_USER='repl',
 MASTER_PASSWORD = '123456',
 MASTER_LOG_FILE='binlog.000002',
 MASTER_LOG_POS=154;
 
# Start master-slave replication and maintain the status start slave;
show slave status \G //Check the slave status to ensure Slave_IO_Running: Yes Slave_SQL_Running: Yes

3. Master-slave replication based on GTID

GTID is a new feature of MySQL 5.6. Its full name is Global Transaction Identifier, which can simplify MySQL master-slave switching and failover. GTID is used to uniquely identify a transaction in the binlog. When a transaction is committed, MySQL Server will first write a special Binlog Event of type GTID_Event, specifying the GTID of the next transaction, and then write the Binlog of the transaction.

In GTID-based replication, the slave server will first tell the master server the GTID values ​​of the transactions that have been executed on the slave server. Then the master database will send all transactions that have not been executed on the slave database to the slave database for execution. Replication using GTID can ensure that the same transaction is only executed once on the specified slave database, thus avoiding data inconsistency due to offset problems. In other words, whether it is a cascading situation or a one-master-multiple-slave situation, the position can be automatically found through GTID, without having to find the main library binlog position through File_name and File_position as before.

The master-slave replication based on GTID is similar to the master-slave replication based on binary file location above. The following is a brief demonstration of the setup process:

3.1 Confirm the master-slave library configuration and enable GTID

# The main library parameter configuration must have the following parameters vim /etc/my.cnf 
[mysqld] 
server-id = 137
log-bin = binlog 
binlog_format = row 
gtid-mode = ON //Turn on gtid mode enforce-gtid-consistency = ON //Enforce gtid consistency to ensure the security of transactions after starting gitd # It is recommended to configure the following parameters from the library vim /etc/my.cnf 
[mysqld] 
server-id = 138
log-bin = binlog 
binlog_format = row 
gtid-mode = ON 
enforce-gtid-consistency = ON 
relay-log = relay-bin

3.2 Create a synchronization account to keep the master and slave database data consistent

If the master database has just been initialized or all binary files are retained on the master database, there is no need to manually synchronize data with the slave database. Otherwise, you need to manually synchronize data to make the master and slave consistent.

# Create a synchronization account in the main database create user 'repl'@'%' identified by '123456';
grant replication slave on *.* to 'repl'@'%';

# If the master database has just been initialized or has complete binary files, you do not need to perform the following steps # Full backup master database data mysqldump -uroot -pxxxx -A -R -E --single-transaction > all_db.sql
# Restore mysql from the database -uroot -pxxxx < all_db.sql

3.3 Enter the slave database and enable master-slave replication

# Enter the MySQL command line from the slave library and execute the change master statement to connect to the master library. CHANGE MASTER TO MASTER_HOST='MySQL master server IP address',
 MASTER_PORT=3306,
 MASTER_USER='repl',
 MASTER_PASSWORD = '123456',
 MASTER_AUTO_POSITION = 1;
 
# Start master-slave replication and maintain the status start slave;
show slave status \G

4. Some experience and suggestions

In the process of daily study and work, I have also accumulated some experience in master-slave replication. Here are a few simple points to share. I hope you can avoid pitfalls.

• The database versions on both the master and slave ends should be kept consistent as much as possible.
• It is recommended that the master and slave database parameters be the same, such as character set and sql_mode.
• The performance of the slave server cannot lag too far behind the master server, so as to avoid master-slave delays due to server performance.
• All tables are required to have a primary key, because synchronizing a table without a primary key to a slave database is very likely to cause master-slave delays.
• It is recommended to set the slave database to read only to prevent human error in operating the slave database data.
• Monitor the master-slave delay and status, and resolve synchronization interruptions or delays in a timely manner.
  

Summarize:

This article introduces the principle and construction process of master-slave replication. In fact, there is still a lot of content about master-slave replication, which requires continuous learning. Here I recommend that you use the GTID mode to build master-slave replication. The experiences I share later are also my daily accumulation. I hope they will be helpful to you. Writing is not easy. If you think it is good, please share it.

The above is a detailed analysis of MySQL master-slave replication. For more information about MySQL master-slave replication, please pay attention to other related articles on 123WORDPRESS.COM!