Logical backup and point-in-time restore in MySQL based on mysqldump and binary log log-bin

Source of this article:

This article only simulates the use of mysqldump and log-bin binary logs for simple testing, and is only used as personal study notes , which may still be far from practical application, and is only for reference.

Enable MySQL's bin-log binary log

Simulated restore requires the files and log-bin from mysqldump, so you need to start log- bin binary log.
When opening the binary log in mysql5.7.18, in addition to setting the location of log-bin, you also need to set a server-id. Previous versions of MySQL should not require this setting.

Let’s complain about open source software. Basically, each version has some differences from the previous version. It is often difficult to search for information online. Many things are different in different versions. This One thing to note.

After restarting, query the variables related to log_bin

mysqldump backup ( The basic use of export) data

Mysqldump command parameters are quite many. Let’s briefly record the commonly used commands, and use mysqldump backup (strictly speaking, export data) and binary log log-bin to perform database restoration operations.

-- Back up the entire testdb database, -l means add a read lock to all tables, -F (F must be uppercase, lowercase does not report an error and a single page is invalid) means rolling to generate a new log file
mysqldump -u root -p -l -F -h localhost testdb > usr/local/mysqlbak/test20170607_data.sql

The backed up file is create table And the script of insert into table

--Back up the two tables test_table1 test_table2 in the testdb database, adding --no-create-info means this backed up file The script without create table is just the information of insert into table
mysqldump -uroot -p -h localhost testdb test_table1 test_table2 --no-create-info> usr/local/mysqlbak/test20170606_1. sql

-- Back up part of the data in the test2 table in the testdb database, that is, back up the data in the test_table1 table that matches id<1000
mysqldump -uroot -p -h localhost testdb test_table1 - -where "id<1000" > usr/local/mysqlbak/test20170606_2.sql

For more mysqldump parameters, refer to:

In addition, mysql Log-bin replacement strategy:

Use index to cycle files, and will cycle to the next index
1 under the following conditions. Server restart
2. The server was updated
3. The log has reached the maximum log length max_binlog_size
4. The log is flushed mysql> flush logs;

Use the files backed up by mysqldump and the log-bin binary log to restore

First, in the following table Back up if there is data

Execute mysqldump -u root -p -l -F -h localhost testdb --master-data=2 > usr/local/mysqlbak/test20170607_data.sql

An option --master-data=2 is added here to note the current data in the backup file. log_bin file,
As for why this command is added, many blogs record that after using mysqldump to back up a file, modify the data, and then simulate the accidental deletion or downtime restoration of the database, and then use mysqldump to get the result. After the file is restored, then use log-bin to restore
Although they are all test simulations, there is an obvious problem. How do you know whether the log has scrolled after mysqldump and how many times it has scrolled?
If the log is not scrolled, restore the log-bin file according to the time point or position. If it is scrolled, how do you know how many log files have been scrolled?
You need to record it when mysqldump is executed and refresh the log. When using the newly generated log-bin file to restore the log, you can determine which logs to use for restoration.

With the --master-data=2 option, you can know the log_file location during mysqldump backup

Then continue to insert 10 pieces of data into the table

　Then simulate the situation of accidentally deleting data at a certain point in time, truncate the test table, and the test table is empty at this time

First, use the file from mysqldump to restore the database. The file backup from mysqldump contains 100 rows of data.
Because the data in the backup file at the time of backup is 100 rows, after the restore, it is 100 rows, so there is no problem.

Then use log-bin to restore according to time points. As mentioned above, the file produced by mysqldump records the file name of log-bin after the log is refreshed,
Then you can judge whether the log has scrolled. If it has not scrolled, restore it according to the time point according to the latest log-bin in the figure below.

Perform a mysqldump backup and restore
mysql -u root -p testdb < usr/local/mysqlbak/test20170607_data.sql
Perform then perform another based on Time point restoration of bin-log
Mysqlbinlog --stop-datetime="2017-6-7 21:45:00" /var/lib/mysql/mysql-bin.000022 | mysql -u root -p testdb
Then the data comes back.

 Restoration based on time point also needs to find out which log file is based on the time point.

Summary:

The mysqldump backup mode is relatively simple and crude. Just exporting the data as an insert script will cause performance problems when restoring larger data. Mysqldump may not be suitable, and a more efficient xtrabackup is needed for backup and restoration.

The above is the detailed content of Logical backup and point-in-time restore in MySQL based on mysqldump and binary log log-bin. For more information, please follow other related articles on the PHP Chinese website!