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 Replication mechanism. The official introduction of excerpt is as follows:

MySQL Replication can synchronize data from one master database to one or more slave databases. And this synchronization process works asynchronously by default, without maintaining a real-time connection between the master and slave databases (that is, allowing the connection to be broken). At the same time, it allows custom configuration of databases and data tables to be synchronized.

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

1. Realize load balancing and separation of reading and writing through MySQL Replication (the master database is only updated, and the slave database is only read), so as to improve the database performance.

2. Realize real-time backup of data through MySQL Replication to ensure data security.

3. Off-line analysis of data is realized through MySQL Replication (the main database generates data, and the analysis and calculation of the slave database does not affect the performance of the main database).

4. Data distribution.

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

Working principle

1111

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

2. When executing the command "SLAVE START" in Slave, turn on Slave I/O Thread and connect Master

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

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

5. Slave SQL Thread restores Relay Log to data and completes synchronously

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

Configuring Master

Turn on Binary Log and set ServerID, ServerID must be only 1, with values ranging from 1 to 232-1

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

#  Specify which databases to synchronize (because database names may contain commas, multiple databases must be configured repeatedly and cannot be separated by commas) 
binlog-do-db=database_name
#  Specify databases that are prohibited from synchronization 
binlog-ignore-db=database_name
#  Specify Binary Log Format 
binlog_format=MIXED

Create Synchronization Account

Because each Slave requires an account password to connect to the primary database, an account must be provided on the primary database. It is recommended to use a separate account, which only grants data synchronization rights.

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

Get Binary Log information

When Slave starts I/O Thread, it needs to pass in some information of Binary Log, so it is necessary to obtain Binary Log information under 1:

SHOW MASTER STATUS;

Use the "SHOW MASTER STATUS" command to obtain Binary Log information and record the File and Position field values.

Ensure the data of Master and Slave before synchronization

Before Slave starts I/O Thread, it is necessary to ensure that the data of Master and Slave is 1, so lock Master first (to prevent data change), synchronize manually and ensure that the data is 1 before unlocking.

FLUSH TABLES WITH READ LOCK;

Manual data synchronization related operations slightly...

UNLOCK TABLES;

Configuring Slave

Set ServerID without having to turn on BinLog:

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

#  Specify synchronized databases 
replicate-do-db=database_name
#  Specify databases that are prohibited from synchronization 
replicate_ignore_db=database_name

Set the Master information and 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;

View synchronization status:

SHOW SLAVE STATUS;

binlog_format Parameter for Master

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

Row

Record according to the change of data rows. This mode has nothing to do with SQL statements, stored procedures, functions, triggers, etc. It only cares about whether the data of every row changes, and records if it changes, so Row mode is the most accurate. However, its disadvantage is that in some cases, it will produce a large amount of content, which will lead to a decrease in efficiency, such as when the table structure changes.

Statement

Recording according to SQL statement obviously solves the shortcoming of Row mode, but the problem is that the accuracy is not high enough, because SQL statement 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, which is also the default mode.

replicate-do-db Considerations

When using replicate-do-db and replicate-ignore-db configuration items in Slave, it is important to 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, such as:

replicate_wild_do_table=database_name.%
replicate_wild_ignore_table=database_name.%