Shulou(Shulou.com)05/31 Report--

This article shows you how to recover data through the binlog log in MySQL. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

I. data backup

Daily logical backup was carried out the night before the operation.

Mysqldump-uroot-pmysql-P3306-- all-databases > / mysql/backup/dump/alldb_bak.sql

Second, simulated accident

Simulate the business situation before and after the accident

Mysql > show tables +-+ | Tables_in_test | +-+ | kk | | T1 | | T2 | | T3 | | T4 | | T5 | | T6 | | T7 | +- -+ 8 rows in set (0.00 sec) mysql > desc T7 +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | id | int (11) | NO | PRI | NULL | auto_increment | | name | varchar (30) | YES | | NULL | | +-+- -+ 2 rows in set (0.00 sec) mysql > create table T8 as select * from T7 Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0mysql > select * from t8 + | id | name | +-- +-+ | 1 | steven | 3 | steven | | 4 | steven | +-+-+ 3 rows in set (0.00 sec) mysql > insert into t8 select * from t7 position query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from t8 +-- +-- +-+ | id | name | +-+-+ | 1 | steven | 3 | steven | 4 | steven | | 1 | steven | | 3 | steven | 4 | steven | +-- + 6 rows in set (0.00 sec) mysql > update T8 set id=2 where id=3;Query OK, 2 rows affected (0.33 sec) Rows matched: 2 Changed: 2 Warnings: 0mysql > update T8 set id=3 where id=4 Query OK, 2 rows affected Rows matched: 2 Changed: 2 Warnings: 0mysql > select * from t8 + | id | name | +-- +-+ | 1 | steven | | 2 | steven | 3 | steven | 1 | steven | 2 | steven | 3 | steven | +-+-+ 6 rows in set (0.00 sec) mysql > drop table t8scape query OK, 0 rows affected (0.10 sec)

3. View current binlog

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000001 | 1344 | +- -+ 1 row in set (0.00 sec)

IV. Restore data

Copy the backup files from the previous night in the production library and the binlog to the temporary library during the accident

Scp alldb_bak.sql 192.168.8.32:/mysql/backup/dump/scp / mysql/data/mysql-bin.000001 192.168.8.32:/mysql/backup/dump/

Create an accident database in the temporary library

Mysql > create database test;Query OK, 1 row affected (0.03 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | | testdb13 | | testdb14 | | testdb15 | | testdb16 | | testdb17 | | testdb18 | | testdb19 | | testdb20 | | testdb21 | | testdb22 | | testdb23 | | testdb24 | +-+ 17 rows in set (0.00 sec) |

Restore the test database from a backup

Mysql-uroot-pmysql-P3306-o test

< alldb_bak.sql -o是指单独恢复test库，忽略其他数据库 从mysql-bin.000001中查看到drop table t8之前的pos是1164 update t8 set id=3 where id=4/*!*/;# at 1133#181127 14:12:41 server id 330631 end_log_pos 1164 CRC32 0x1203751c Xid = 1661COMMIT/*!*/;# at 1164#181127 14:12:53 server id 330631 end_log_pos 1229 CRC32 0x48fad728 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 1229#181127 14:12:53 server id 330631 end_log_pos 1344 CRC32 0x2a7eb0d7 Query thread_id=3 exec_time=1 error_code=0SET TIMESTAMP=1543299173/*!*/;DROP TABLE `t8` /* generated by server *//*!*/;mysqlbinlog --no-defaults --stop-position=1164 --database=test mysql-bin.000001 |mysql -uroot -p test 五、根据临时库的数据，将该表恢复至生产库 六、数据验证 mysql>

Use test;Database changedmysql > show tables +-+ | Tables_in_test | +-+ | kk | | T1 | | T2 | | T3 | | T4 | | T5 | | T6 | | T7 | | T8 | +- -+ 9 rows in set (0.01sec) mysql > select * from T8 +-have you learned any knowledge or skills about how to recover data from binlog logs in MySQL? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.