Analyze MySQL replication and tuning principles and methods

1. Introduction

MySQL comes with a replication solution, which brings the following benefits:

Data backup.

Load balancing.

Distributed data.

Concept introduction:

Master: The database being replicated.

Slave: A database that replicates the master's data.

Steps to reproduce:
(1) The master records the details of the changes and stores them in the binary log.
(2). The master sends a synchronization message to the slave.
(3) After receiving the message, the slave copies the master's binary log to the local relay log.
(4). The slave reproduces the messages in the relay log, thereby changing the database data.

Here is a classic picture to illustrate this process:

2. Implementing replication

The following steps are required to implement replication:

1. Set up the binary log and server-id of the MySQL master library

MySQL configuration files are usually stored in /etc/my.cnf

# Add configuration options [mysqld] under [mysqld]
server-id=1
log-bin=mysql-bin.log

The server-id is the unique identifier of the database in the entire database cluster and must remain unique.
Restart MySQL.

Note: If this file has already been configured in the MySQL configuration file, you can skip this step.

2. Create a new copy account

Create a new account in the master database for copying master database data from the database, and grant copy permissions.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO user_name@'host' IDENTIFIED BY 'password';

3. Set the MySQL master database server-id

As with the second step configuration, there are two points to note:

If you do not need the slave library to serve as the master library for other slave libraries, you do not need to configure binary logging. Many times replication does not require replication of all databases in the master database (especially the MySQL information configuration database). Therefore, you can configure replicate_do_db to specify the replicated database. 4. Initialize the data of the master database from the database

If the amount of data is not large, you can use the mysqldump tool to export the master database data and then import it into the slave database.

mysqldump --single-transaction --triggers --master-data databasename > data.sql

If the amount of data is large, you should use Xtrabackup to export the database, which is not introduced here.
Some students may ask, why not use binary logs directly for initialization?

If our master database has been running for a long time, it is not suitable to use the slave database to copy data according to the binary log. It will be time-consuming and performance-intensive to directly use the binary log to initialize the slave database. In most cases, the binary log configuration item of the master database is not turned on, so there is no binary log of previous operations. 5. Enable replication

Execute the following command from the library

mysql> CHANGE MASTER TO MASTER_HOST='host',
-> MASTER_USER='user',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=0;

Note the last two commands: MASTER_LOG_FILE and MASTER_LOG_POS, which indicate which binary file to start reading from the library and where the offset starts. These two parameters can be found in the SQL we imported.

Enable replication

start slave;

At this point, the replication is completed. When data is updated in the master database or new data is added, the results can be queried in the slave database.

The status of the replication thread can also be queried on the master database.

3. Replication log format

There are three log formats for MySQL replication, depending on how the master database stores data:

Before MySQL 5.7, the statement format was used by default.

The setting method can be set in the configuration file (preferred):

binlog_format=ROW

Or temporarily set global variables (current MySQL connection is valid):

View the log formatmysql > show variables like 'binlog_format';
Set log formatmysql> set binlog_format='row';

Since the two master and slave servers are usually placed in the same computer room, the synchronization speed between the two will be faster. To ensure strong consistency, the row log format should be preferred (row). To ensure the transmission speed, the mixed mode (mixed) can be selected.
The log format of the line has the following three recording modes: