MySQL performs logical backup and Restoration Based on mysqldump and binary log-bi, and mysqldumplog-bi

MySQL performs logical backup and Restoration Based on mysqldump and binary log-bi, and mysqldumplog-bi

 

Source: http://www.cnblogs.com/wy123/p/6956464.html

 

 

This article only simulates the use of mysqldump and log-bin binary logs for a simple test, only as personal study notes, there may be a large gap from the actual application, for reference only.

 

Enable BINLOG binary log of MySQL

Simulated restoration requires mysqldump files and log-bin. Therefore, you need to start the log-bin binary log.
In addition to setting the location of log-bin when enabling binary logs, mysql5.7.18 also needs to set a server-id. This setting is not required for earlier MySQL versions.

Open-source software. Basically, each version has something different from the previous version. It is often difficult to query information on the Internet. In different versions, a lot of things are different, note This.

　　

Query the log_bin related variables after the restart.

　　

 

Basic use of mysqldump backup (export) data

The mysqldump command has many parameters. You can simply record Common commands and use mysqldump backup (strictly speaking, export data) and binary log-bin to restore the database.

-- Back up the entire database of testdb.-l indicates that a read lock is applied to all tables.-F (F indicates that the read locks must be capitalized, and the error page is invalid when no error is reported by the Consumers Association) indicates that a new log file is generated by scrolling.
Mysqldump-u root-p-l-F-h localhost testdb> usr/local/mysqlbak/test20170607_data. SQL

The files backed up are the scripts for create table and insert into table.

-- Back up two tables test_table1 test_table2 in the testdb database. Adding -- no-create-info indicates that the file backed up does not contain the create table script, but only the insert into table information.
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 Table test2 in the testdb database, that is, back up the data that conforms to the id <1000 in Table test_table1
Mysqldump-uroot-p-h localhost testdb test_table1 -- where "id <1000" type = "codeph" text = "codeph"> usr/local/mysqlbak/test20170606_2. SQL

 

For more information about mysqldump parameters, see: http://www.cnblogs.com/xuejie/archive/2013/01/11/2856911.html

In addition, the mysql log-bin replacement policy is as follows:

Use indexes to loop files. The following conditions will be used to loop to the next index.
1. Server restart
2. Server Updated
3. The maximum log length is max_binlog_size.
4. Log refreshed mysql> flush logs;

 

Restore the file backed up by mysqldump and the log-bin binary log

First, back up data in a table.

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

　　

Here we add the -- master-data = 2 Option to note the current log_bin file in the backup file,
As to why I want to add this command, many blogs have recorded how to use mysqldump to back up a file, modify the data, and simulate database accidental deletion or downtime restoration, after restoring the mysqldump file, use log-bin to restore the file.
Although they are all test simulations, there is an obvious problem. How do I know if the log has been rolled several times after mysqldump?
If the log does not scroll, You need to restore the log-binfile by time or position. If the log is rolled, how do you know that several log files have been rolled?
When executing mysqldump, You need to record the newly generated log-binfile after the log is refreshed. When using log restoration, you can determine which logs are used for restoration.

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

Then insert 10 data records into the table.

　　

Then, when the data is accidentally deleted at a certain time point, the truncate test table is empty.

　　

First, use mysqldump files to restore the database. mysqldump files are backed up to 100 rows of data.
Because the data in the backup file is 100 rows, the restored data is 100 rows, no problem.

　　

Then use log-bin to restore the file by time. As mentioned above, the file generated by mysqldump records the name of the log-bin file after the log is refreshed,
Then you can determine whether the log is rolled. If the log is not rolled, It is restored according to the latest log-bin according to the time point.

　　

Perform a mysqldump backup restoration
Mysql-u root-p testdb <usr/local/mysqlbak/test20170607_data. SQL
And then restore the time point based on the bin-log.
Mysqlbinlog -- stop-datetime = "21:45:00"/var/lib/mysql/mysql-bin.000022 | mysql-u root-p testdb
Then the data is returned.

　　

 

 

Of course, this is only a simulated operation. Of course, there are still many details that have not yet been determined. If a log rolling occurs, we need to restore the Log Based on the time point and determine the log file based on the time point.

 

Summary:

This document only uses a simple example to restore a database,
The mysqldump backup mode is simple and crude. It is only used to export data as an insert script, which may cause performance problems when large data is restored, and may be unsuitable for mysqldump, xtrabackup is more efficient for backup and restoration.