Use binlog to restore the database

Http://bbs.kaspersky.com.cn/thread-287149-1-1.html

There are many differences between the Development Library and online table structures, and the number of table shards is also different. I need to reorganize them to ensure that the development library is consistent with the online one. I have already told you whether development data can be lost, the developer said that the data can be discarded and lost directly. After I have done a good job, the developer told me that some data (ring) cannot be deleted. I was dizzy and had to find a way to restore the data, there is no backup, but fortunately there are all binlogs.

1. parse all the SQL statements of all ring databases based on binlog
[Mysql @ DEV_SNS1 log] $ mysqlbinlog -- no-defaults -- database = ring -- start-datetime = "9:55:00" -- stop-datetim = "08:05:00"/u01/mysql/log /mysql-bin.000005>/u01/mysql/log/mysql_restore5. SQL

[Mysql @ DEV_SNS1 log] $ ls-l/u01/mysql/log/mysql_restore5. SQL
-Rw-r -- 1 mysql dba 407 Apr 8/u01/mysql/log/mysql_restore5. SQL
-- Start-datetime = start of datetime Parsing
-- Stop-datetim = start of datetime to stop Parsing
-- Database = ring specifies the database to be parsed and extracts only the SQL statements of a database.
-- If there are multiple binlogs, You need to parse them multiple times and all binlogs need to be parsed.
2. Run the command again in the database.
[Mysql @ DEV_SNS1 log] $ mysql-u root <mysql_restore5. SQL
ERROR 1062 (23000) at line 2559580: Duplicate entry 175754263-140 for key 1 -- a primary key conflict occurs during recovery and the Import fails.
3. Delete the recovered data and restore it again.
[Mysql @ DEV_SNS1 log] $ mysql-u root
Welcome to the MySQL monitor. Commands end with; or g.
Your MySQL connection id is 7647
Server version: 5.0.67-log Source distribution
Type help; or h for help. Type c to clear the buffer.
Root @ (none) 02:55:41> drop database ring;
Query OK, 8 rows affected (0.04 sec)
[Mysql @ DEV_SNS1 log] $ mysql-u root-f <mysql_restore5. SQL --- f: Ignore the error and continue
ERROR 1062 (23000) at line 2559580: Duplicate entry 175754263-140 for key 1
ERROR 1062 (23000) at line 2564671: Duplicate entry 138 for key 1
ERROR 1062 (23000) at line 2566216: Duplicate entry 139 for key 1
ERROR 1062 (23000) at line 2566224: Duplicate entry 140 for key 1
ERROR 1062 (23000) at line 2566232: Duplicate entry 141 for key 1
ERROR 1062 (23000) at line 2566240: Duplicate entry 142 for key 1
ERROR 1062 (23000) at line 2648410: Duplicate entry 143 for key 1
ERROR 1062 (23000) at line 2648418: Duplicate entry 144 for key 1
ERROR 1062 (23000) at line 2648581: Duplicate entry 145 for key 1
ERROR 1062 (23000) at line 2648589: Duplicate entry 146 for key 1
ERROR 1062 (23000) at line 2648597: Duplicate entry 147 for key 1
ERROR 1062 (23000) at line 2648605: Duplicate entry 148 for key 1
ERROR 1062 (23000) at line 2649279: Duplicate entry 149 for key 1
ERROR 1062 (23000) at line 2649287: Duplicate entry 150 for key 1
ERROR 1062 (23000) at line 2649295: Duplicate entry 151 for key 1
ERROR 1062 (23000) at line 2649303: Duplicate entry 152 for key 1
ERROR 1062 (23000) at line 2649311: Duplicate entry 153 for key 1
ERROR 1062 (23000) at line 2649319: Duplicate entry 154 for key 1
ERROR 1062 (23000) at line 2649327: Duplicate entry 155 for key 1
ERROR 1062 (23000) at line 2649335: Duplicate entry 156 for key 1
 
After the recovery is successful, some data conflicts need to be handled by developers themselves.
In general, no matter whether it is important or not, because we cannot assess the importance of it most of the time. We must back up the data before performing a major operation. Backup is king.