Yesterday, a customer suddenly said that he had deleted a large amount of data by mistake. The CTO directly pulled me into a discussion group and said that he would help them recover their data. They dug the hole themselves, and planned to let the development side restore it based on the business logs. They were told that only information such as deletion of the primary key was recorded, and physical deletion was impossible.
I looked at the recorded logs on the server and found that several of them had accidentally deleted record output. Although Alibaba RDS can clone an instance and restore it to the time before deletion, it is difficult to find the tens of thousands of scattered IDs, and the data associated with several tables must also be restored, which is troublesome.
Think of MySQL’s flashback solution. I have read several related articles before, and even almost used one to parse binlog and reverse it to get the rollback sql. I really don’t have time, so I need to use it urgently now. I quickly looked for "ready-made solutions" online. Text begins
MySQL (including Alibaba RDS) fast flashback can be said to be the antidote for database misoperations. The flashback function can return the database to before the misoperation. But even Oracle database only supports flashback within a short period of time.The existing open source MySQL flashback implementation on the Internet uses the principle of parsing binlog and generating reverse sql: (must be in row mode)
to obtain the binlog, initiate a request to synchronize the binlog, and then parse EVENT. However, after the binlog of Alibaba Cloud RDS was synchronized to the slave library,
it was quickly purged . If you want to restore some of the data of yesterday's , both options will not get the binlog. In other words, the flashback time is limited.
binlog-rollback.pl. I have tried it, but the speed is too slow.
information_schema.columns Obtain metadata information
mysql-bin.index to ensure that the file name can still be recognized by mysqld
show binary logs and see if In the list
python mysqlbinlog_back.py --host="localhost" --username="ecuser" --password="ecuser" --port=3306 \ --schema=dbname --tables="t_xx1,t_xx2,t_xx3" -S "mysql-bin.000019" -E "2017-03-02 13:00:00" -N "2017-03-02 14:09:00" -I -U ===log will also write to .//mysqlbinlog_flashback.log=== parameter={'start_binlog_file': 'mysql-bin.000019', 'stream': None, 'keep_data': True, 'file': {'data_create': None, 'flashback': None, 'data': None}, 'add_schema_name': False, 'start_time': None, 'keep_current_data': False, 'start_to_timestamp': 1488430800, 'mysql_setting': {'passwd': 'ecuser', 'host': 'localhost', 'charset': 'utf8', 'port': 3306, 'user': 'ecuser'}, 'table_name': 't_xx1,t_xx2,t_xx3', 'skip_delete': False, 'schema': 'dbname', 'stat': {'flash_sql': {}}, 'table_name_array': ['t_xx1', 't_xx2', 't_xx3'], 'one_binlog_file': False, 'output_file_path': './log', 'start_position': 4, 'skip_update': True, 'dump_event': False, 'end_to_timestamp': 1488434940, 'skip_insert': True, 'schema_array': ['dbname'] } scan 10000 events ....from binlogfile=mysql-bin.000019,timestamp=2017-03-02T11:42:14 scan 20000 events ....from binlogfile=mysql-bin.000019,timestamp=2017-03-02T11:42:29 ...
Tips:
binlog is in ROW format, which is affected by dml Each row records two events: Table_map and Row_log. The table_id in table_map does not affect which instance it is applied to. This id can be considered as a logical mechanism to record the table structure version - when it does not find the table definition in table_definition_cache, the id is incremented by 1 and assigned to the target. Record to binlog table.:
The above is the detailed content of Detailed introduction to MySQL’s fast “flashback” based on offline binlog. For more information, please follow other related articles on the PHP Chinese website!