Mysqlbinglog based on Instant point restore

Tag: res stop PNG date P12 set VAR data number

Mysqlbinlog Introduction

To recover data from a binary log, you need to know the path and file name of the current binary log file.
You can typically find the path from the options file (that is, my.cnf or My.ini, depending on your system).

(mysql5.7 need to set Server-id when Binglog is turned on otherwise it will not start)

To determine the file name of the current binary log file, enter the following MySQL statement:

1. Specify the recovery time
For example, suppose at 10:00 today (today is 20,016 April 22), execute the SQL statement to delete a large table. To restore the table and data,
You can restore the backup of the previous night and enter:

Mysqlbinlog--stop-date= "2005-04-22 9:59:59"/var/log/mysql/mysql.123456 mysql-u root-pmypwd
This command restores all data as of the date and time given in datetime format in the--stop-date option.
If you do not detect a few hours after entering the wrong SQL statement, you may want to restore the activity that occurs later. Based on these, you can use the date and time to run Mysqlbinlog again:

Mysqlbinlog--start-date= "2005-04-22 10:01:00"/var/log/mysql/mysql.123456 mysql-u root-pmypwd
In that row, the SQL statement that is logged in from 10:01 runs. Combined execution of the previous night's dump file and two lines of Mysqlbinlog can restore all data to one second before 10:00. You should check the logs to make sure the time is correct.

2. Specify the recovery location
They function the same as the start and End Date option, and the difference is that the location number from the log is given. Using the log location is a more accurate method of recovery, especially when many transactions occur simultaneously due to destructive SQL statements.
To determine the location number, you can run Mysqlbinlog to find the time range for the transaction that you did not expect, but you should re-point the result to a text file for review. How to do this:

Mysqlbinlog--start-date= "2005-04-22 9:55:00"--stop-date= "2005-04-22 10:05:00"/var/log/mysql/mysql.123456 >/tmp /mysql_restore.sql
The command will create a small text file in the/tmp directory, which will display the SQL statement when the wrong SQL statement was executed. You can open the file with a text editor and look for statements that you don't want to repeat.
If the location number in the binary log is used to stop and resume the recovery operation, comments should be made. Use Log_pos plus a number to mark the position. After recovering the previous backup file using the location number, you should enter the following from the command line:
Mysqlbinlog--stop-position= "368312"/var/log/mysql/mysql.123456 mysql-u root-pmypwd
Mysqlbinlog--start-position= "368315"/var/log/mysql/bin.123456 mysql-u root-pmypwd
The 1th row above reverts to all transactions until the stop location.
The next line restores all transactions from the given starting position until the end of the binary log.

Give me a chestnut:

1. Create Data:

mysql> flush logs;
Insert data:
Mysql> INSERT into students values (3, ' TT ', ' nine ');
Query OK, 1 row affected (0.01 sec)

Mysql> INSERT into students (Name,class) VALUES (' TT ', ' nine ');
Query OK, 1 row affected, 1 warning (0.01 sec)

Mysql> SELECT * from students;
+----+------+-------+
| ID | name | Class |
+----+------+-------+
| 1 | Hui | Ten |
| 2 | haha | Four |
| 3 | tt | Nine |
| 0 | tt | Nine |
+----+------+-------+
4 rows in Set (0.00 sec)

2. Delete data:
Mysql> Delete from students where id=0;
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from students;
+----+------+-------+
| ID | name | Class |
+----+------+-------+
| 1 | Hui | Ten |
| 2 | haha | Four |
| 3 | tt | Nine |
+----+------+-------+
3 Rows in Set (0.00 sec)

3. Data recovery:

Convert the binary log to text:
[Email protected] ~]# mysqlbinlog/var/lib/mysql/mysqllog.000003 >003.txt

To edit a statement that deletes a text by mistake:
[Email protected] ~]# vim 003.txt
Delete the statement that was mistakenly deleted

Import binary log data:
[Email protected] ~]# mysql-uroot-p123456 <003.txt

View data (already restored):
Mysql> SELECT * from students;
+----+------+-------+
| ID | name | Class |
+----+------+-------+
| 1 | Hui | Ten |
| 2 | haha | Four |
| 3 | tt | Nine |
| 3 | tt | Nine |
| 0 | tt | Nine |
+----+------+-------+
5 rows in Set (0.00 sec)

Mysqlbinglog based on Instant point restore