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

The source of this article: http://www.cnblogs.com/wy123/p/6956464.html

This article only simulates using mysqldump and Log-bin binary logs for simple testing, only as a personal learning note, there may be a large gap from the actual application, only reference.

Turn on the MySQL bin-log binary log

Analog restores are files and log-bin that need to be mysqldump, so you need to start Log-bin binary logs.
mysql5.7.18 when opening the binary log, in addition to setting the location of the log-bin, you need to set a server-id,mysql before the version should not need this setting.

Spit out the open source software, basically each version has some different from the previous version of the place, online search information, the case of a variety of versions of things are not the same, this needs to be noted.

　　

After restarting, query Log_bin related variables

　　

Mysqldump Basic use of Backup (export) data

The mysqldump command has quite a few parameters, a simple record of commonly used commands, and a database restore operation using mysqldump backup (which is strictly export data) and binary log log-bin.

---Backup TestDB entire database,-l for all tables to add a read lock,-F (f to uppercase, association not error single page invalid) means to scroll to generate a new log file
Mysqldump-u root-p-l-f-h localhost testdb > Usr/local/mysqlbak/test20170607_data.sql

The backup file is the script for create table and insert into table

--Backing up the Test_table1 test_table2 two tables in the TestDB database, plus--no-create-info means that the backed-up file does not have a script for create TABLE, just insert into table information
Mysqldump-uroot-p-h localhost testdb test_table1 test_table2 --no-create-info> usr/local/mysqlbak/ Test20170606_1.sql

--Backs up part of the data in the Test2 table in the TestDB database, which is the data in the Test_table1 table that conforms to 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: http://www.cnblogs.com/xuejie/archive/2013/01/11/2856911.html

Restore with mysqldump backed-up files and log-bin binary logs

First, back up with data in the table

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

　　

A--master-data=2 option is added here to comment out the current Log_bin file in the backup file.
As for why to add this command, many blogs are recorded with mysqldump back up a file, modify the data, and then simulate the database mistakenly deleted or the downtime of the restore, and then after the use of mysqldump out of the file restore, then use Log-bin restore
Although it is a test simulation, but there is an obvious problem ah, how to know mysqldump after the log has been scrolling, scroll a few times?
If the log does not scroll also, according to the point of time or location to restore the Log-bin file, if scrolling, how to know how to scroll a few log files?
It is necessary to record the newly generated log-bin file after the log is refreshed when Mysqldump executes, and to determine which logs to use for restore when using the log restore.

With the--master-data=2 option, we know the log_file location of mysqldump backup.

Then continue inserting 10 data into the table

　　

Then simulate the case where the data was mistakenly deleted at a certain point in time, truncate the test table, the test table is now empty

　　

First use mysqldump out of the file to restore the database, mysqldump out of the file backup is 100 rows of data
Because the backup file backup in the time of the data is 100 rows, here after the restoration is 100 rows, no problem.

　　

Then the use of Log-bin according to the point of time restore, said, mysqldump out of the file records the log after the Log-bin file name,
Then you can determine whether the log has been scrolled, and if not scrolling, follow the latest log-bin to restore at a point in time.

　　

Make a mysqldump backup restore
Mysql-u root-p TestDB < Usr/local/mysqlbak/test20170607_data.sql
Go in and do it again. Point-in-time restore based on 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.

　　

Of course, this is only a simulation operation, of course, there are many details have not been determined, if the log rolling, to do based on the point-in-time restore, but also to find out which log file is based on the point in time.

Summarize:

This article only takes a simple example to restore the schema database,
Mysqldump Backup mode is simple and rough, just to export data as Insert script, when restoring large data, there will be performance problems, maybe mysqldump is not suitable, you need more efficient xtrabackup to do backup restore.

　　

　　

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