MySQL Data Restore

1.1The syntax for restoring a database backed up using the mysqldump command is as follows:

Mysql-u root-p [dbname] < BACKUP.SQ

Example:

Mysql-u Root-p < C:\backup.sql

1.2 Restoring a backup of a direct copy directory

　　When restoring in this way, you must ensure that the version number of the two MySQL databases is the same. Tables of type MyISAM are valid, table spaces for InnoDB tables cannot be copied directly for tables with INNODB type not available

2. Ways to restore incremental binlog

2.1 Point-in-time recovery is based on the following rules:
1 MySQL server needs to open binary log--log-bin
See all Binlog files

1. mysql> SHOW BINARY LOGS;

View current Binlog file information (including file name, current location, etc.)

1. mysql> SHOW MASTER STATUS;

The 2 Mysqlbinlog tool converts binary log files from binary to readable text files, and you can select events based on time or location.
3 executing an event from a binary log file causes the data to be modified, indicating that the data is being re-made. The command is:

1. [Email protected]/]# Mysqlbinlog binlog_files | Mysql-u root-p

4 View Binlog time and location information we can talk about Binlog content output to a terminal or text file

1. [Email protected]/]# Mysqlbinlog binlog_files | More
2. [Email protected]/]# Mysqlbinlog binlog_files > Tmpfile

5 It is useful to output binary logs to a file, for example, we want to delete some statements that we do not want to execute.
Importing to a database from a text file

1. [Email protected]/]# Mysql-u root-p < tmpfile

Restore of more than 2.2 Binlog log logs It is best to use one connection for all files, which can sometimes lead to unsafe use if different connections are used
For example:

1. [Email protected]/]# Mysqlbinlog binlog.000001 | Mysql-u Root-p # danger!!
2. [Email protected]/]# Mysqlbinlog binlog.000002 | Mysql-u Root-p # danger!!

If the first log contains the Create temporary table statement created temporary table, the second log to use the temporary table, the first process to import the Binlog log exits after the temporary table is deleted, and the second log file to use the temporary table will not be found when the report "Unknown Table. "
Recommended method:
Method 1:
All binary files are placed in a single connection

1. [Email protected]/]# Mysqlbinlog binlog.000001 binlog.000002 | Mysql-u root-p

Method 2:
Write all binaries in one file to execute

1. [Email protected]/]# Mysqlbinlog binlog.000001 >/tmp/statements.sql
2. [Email protected]/]# mysqlbinlog binlog.000002 >>/tmp/statements.sql
3. [Email protected]/]# mysql-u root-p-E "Source/tmp/statements.sql"

Use method Two if the binary file contains gtid information needs to be filtered out

1. [Email protected]/]# mysqlbinlog --skip-gtids binlog.000001 >/tmp/dump.sql
2. [Email protected]/]# mysqlbinlog --skip-gtids binlog.000002 >>/tmp/dump.sql
3. [Email protected]/]# mysql-u root-p-E "Source/tmp/dump.sql"

3 recovery by point in time or location
If we want to skip a time period or position segment, we need to specify the start and end time or location information
3.1 Recover by time of event
We can specify the starting and ending points of the recovery Binlog log by using the parameters--start-datetime and--stop-datetime, and the time in datetime format.
For example, at the point of time 2005-04-20 10:00:00 we delete a library, we want to restore all the logs before that point in time

1. [Email protected]/]# mysqlbinlog --stop-datetime= "2005-04-20 9:59:59"/usr/local/mysql/data/binlog.123456 | Mysql-u root-p

We may find this error a few hours later, there are a series of additions and deletions and other operations, we also need to restore the subsequent binlog, we can specify the starting time

1. [Email protected]/]# mysqlbinlog --start-datetime= "2005-04-20 10:01:00"/usr/local/mysql/data/binlog.123456 | Mysql-u root-p

This method restores the exact point in time that we need to see the Binlog log knowing that the error occurred, and we can first output the log to the text.

1. [Email protected]/]# mysqlbinlog/usr/local/mysql/data/binlog.123456 >/tmp/mysql_restore.sql

3.2 Recovering by the location of the event
We can specify the starting and ending points of the recovery Binlog log through the parameters--start-position and--stop-position, which requires more granular operations, such as when we execute the wrong statement at a certain point in time, And there are large concurrent operations around this point in time, to determine the point-in-time of the destructive SQL, we can first export the log-to-file of the approximate time period to narrow the scope of the search, and then analyze and determine

1. [[email protected]/]# mysqlbinlog --start-datetime= "2005-04-20 9:55:00"--stop-datetime= "2005-04-20 10:05:00"/ usr/local/mysql/data/binlog.123456 >/tmp/mysql_restore.sql

Once we've determined where we need to skip, we're ready to recover.

1. [Email protected]/]# mysqlbinlog --stop-position=368312/usr/local/mysql/data/binlog.123456 | mysql-u root-p
2. [Email protected]/]# mysqlbinlog --start-position=368315/usr/local/mysql/data/binlog.123456 | mysql-u root-p

Note: The output of the Mysqlbinlog tool increments the SET timestamp statement before each SQL statement, and the recovered data and MySQL logs reflect the current time.

MySQL Data Restore