Restore data using Mysql-bin logs

This is the case: Due to my carelessness, I helped other departments to update some data in batches on October 13, July 30. Who knows that the global update is complete, and I was notified of this problem only on October 10, September 26. They requested that the data with the updated error be restored to the status before January 1, July 30, and the added, deleted, and modified operations during the period from January 1, July 30-9 to April 26 should also be updated. Because I have no experience, I have no idea at all, but I can't do it myself. I am responsible for doing anything wrong.

Practice: import the backup data to the test database, and then extract the binlog log between April 26 and April 26 to the SQL statement used to operate the table. Then, import the data to the test database.

The hard-pressed restoration process began .........

1. Fortunately, I have developed a good habit. No matter the size of the changes, I will back up a copy of data first.

-Rw-r -- 1 root 2473664 07-30 terminfo-bak0730. SQL

I found it, and it was backed up at around a.m. on April 9. Fortunately, there was a backup, or else it would have been miserable ....... first, import the backup data to the test database, change the table name to terminfo0730, and then export the current production data. Change the table name to terminfo0926, this approach is to check whether the data is correct after the data is restored.

Mysql> use reed
Database changed
Mysql> show tables;
+ ---------------- +
| Tables_in_reed |
+ ---------------- +
| Terminfo0730 |
| Terminfo0926 |
+ ---------------- +

2. The most important step is to extract binlog logs. Because it was backed up on April 1, July 30, we need to find the binlog from April 1, July 30 to April 1, September 26.

-Rw ---- 1 mysql 107374208408-0920: 03 mysql-bin.000086
-Rw ---- 1 mysql 107374189408-2604: 51 mysql-bin.000087
-Rw ---- 1 mysql 107374207809-mysql-bin.000088
-Rw ---- 1 mysql 107663780509-2611: 55 mysql-bin.000089
-Rw ---- 1 mysql 4533942009-2618: 50 mysql-bin.000090

Use the mysqlbinlog command to extract the first round of SQL statements

# Mysqlbinlog -- no-defaults -- database = ecard -- start-datetime = '2017-07-30 09:38:00 'mysql-bin.000086> log86.txt # Set the start time here
# Mysqlbinlog -- no-defaults -- database = ecard mysql-bin.000087> log87.txt
# Mysqlbinlog -- no-defaults -- database = ecard mysql-bin.000088> log88.txt
# Mysqlbinlog -- no-defaults -- database = ecard mysql-bin.000089> log89.txt
# Mysqlbinlog -- no-defaults -- database = ecard mysql-bin.000090> log90.txt

# Ls-l

-Rw-r -- 1 root 1553740972 09-26 :38 log86.txt
-Rw-r -- 1 root 153286277909-2619: 52 log87.txt
-Rw-r -- 1 root 157780920009-2619: 55 log88.txt
-Rw-r -- 1 root 158045208209-2619: 57 log89.txt
-Rw-r -- 1 root 6494588409-2619: 58 log90.txt

After extraction, all SQL statements are used. What I need is only SQL statements for the terminfo operation, so the second round of extraction is required.

# Grep terminfo log86.txt> log86-terminfo.txt # grep out

[Root @ localhost txt] # ll
Total 264
-Rw-r -- 1 root 2020609-2619: 50 log86-terminfo.txt
-Rw-r -- 1 root 7874009-2619: 59 log87-terminfo.txt
-Rw-r -- 1 root 6542909-2619: 59 log88-terminfo.txt
-Rw-r -- 1 root 6529409-2619: 59 log89-terminfo.txt
-Rw-r -- 1 root 94109-2620: 00 log90-terminfo.txt

After extraction, it contains all the SQL statements for terminfo. Import the data to the terminfo0730 table of the test database.

Mysql> source log86-terminfo.txt; # in turn source into, be sure to pay attention to the order of the problem !!!

After the import, compare the number of data in tables terminfo0730 and terminfo0926, and ask the Department colleagues to verify the data correctness.

At this point, the data is restored successfully. An unforgettable experience. I also warned myself that I should be careful and careful !!