Detailed explanation of real-time backup knowledge points of MySQL database

Preface

The need for real-time database backup is very common. MySQL itself provides a Replication mechanism. The official introduction is as follows:

MySQL Replication can synchronize data from a master database to one or more slave databases. And this synchronization process works asynchronously by default, and there is no need to maintain a real-time connection between the master and slave databases (that is, connection interruption is allowed). It also allows custom configuration of databases and data tables that need to be synchronized.

The advantages and application scenarios of MySQL Replication are as follows:

1. Use MySQL Replication to achieve load balancing and read-write separation (the master database is only updated, and the slave database is only read) to improve database performance.

2. Real-time data backup is achieved through MySQL Replication to ensure data security.

3. Implement offline data analysis through MySQL Replication (the master database generates data, and the analysis and calculation of the slave database does not affect the performance of the master database).

4. Data distribution.

For the complete official documentation of MySQL Replication, please refer to: https://dev.mysql.com/doc/refman/5.7/en/replication.html

How it works

1111

1. All database change events in the Master are written to the Binary Log file

2. When the "SLAVE START" command is executed in the Slave, the Slave I/O Thread is started and connected to the Master

3. The Master detects the connection of the Slave I/O Thread and opens the Log Jump Thread to respond

4. The Master Binary Log is transmitted to the Slave Relay Log via the Master Log Jump Thread and the Slave I/O Thread.

5. Slave SQL Thread restores the Relay Log to the data and the synchronization is completed

Note: You can use the "SHOW PROCESSLIST" command to view the running status of the corresponding threads in the Master and Slave

Configuring the Master

Enable Binary Log and set ServerID. ServerID must be unique and can be in the range of 1 to 232-1.

[mysqld]
# Enable Binary Log
log-bin=mysql-bin
# Set the global ID
server-id=1

# Specify the databases to be synchronized (because the database name may contain commas, multiple databases must be configured multiple times instead of separated by commas)
binlog-do-db=database_name
#Specify the database that is prohibited from synchronization binlog-ignore-db=database_name
# Specify Binary Log format binlog_format=MIXED

Create a sync account

Because each Slave needs to use an account and password to connect to the master database, an account must be provided on the master database. It is recommended to use an independent account and only authorize data synchronization permissions.

CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

Get Binary Log Information

When the Slave starts the I/O Thread, it needs to pass in some information from the Binary Log, so it is necessary to obtain the following Binary Log information:

SHOW MASTER STATUS;

Run the SHOW MASTER STATUS command to obtain Binary Log information and record the values ​​of the File and Position fields.

Ensure that the data of Master and Slave are consistent before synchronization

Before the Slave starts the I/O Thread, it is necessary to ensure that the data of the Master and Slave are consistent. Therefore, the Master is first locked (to prevent data changes), and then manually synchronized and unlocked after ensuring data consistency.

FLUSH TABLES WITH READ LOCK;

Manual data synchronization related operations are briefly described...

UNLOCK TABLES;

Configuring Slave

To set ServerID, you don't need to enable BinLog:

[mysqld]
# Set the global ID
server-id=2

# Specify the synchronized database replicate-do-db=database_name
#Specify the database that is prohibited from synchronization replicate_ignore_db=database_name

To set the Master information, execute the following command:

mysql> CHANGE MASTER TO
  -> MASTER_HOST='master_host_name',
  -> MASTER_PORT='master_host_port',
  -> MASTER_USER='replication_user_name',
  -> MASTER_PASSWORD = 'replication_password',
  -> MASTER_LOG_FILE='recorded_log_file_name',
  -> MASTER_LOG_POS=recorded_log_position;

Start I/O Thread

START SLAVE;

Check the synchronization status:

SHOW SLAVE STATUS;

Master's binlog_format parameter

binlog_format is used to configure the format of Binary Log and supports the following three types:

Row

Record changes based on data rows. This mode has nothing to do with SQL statements, stored procedures, functions, triggers, etc. It only cares whether the data in each row has changed. If so, it will be recorded. Therefore, Row mode has the highest accuracy. But its disadvantage is that in some cases it will generate a lot of content and lead to reduced efficiency, such as when the table structure changes.

Statement

Recording by SQL statement obviously solves the shortcomings of the Row mode, but the problem is that the accuracy is not high enough because SQL statements can be very complex and prone to unexpected situations.

Mixed

Row and Statement mixed mode, MySQL automatically decides when to use Row and when to use Statement. This is also the default mode.

Replicate-do-db Notes

When using the replicate-do-db and replicate-ignore-db configuration items in the Slave, please note that SQL statements across databases will not be synchronized, such as:

replicate-do-db=a
use b;
update a.some_table set some_field = 'some value';

The solution is to use replicate_wild_do_table and replicate_wild_ignore_table, like:

replicate_wild_do_table=database_name.%
replicate_wild_ignore_table=database_name.%