MySQL中怎么通过binlog日志恢复数据

本篇文章为大家展示了MySQL中怎么通过binlog日志恢复数据，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

一、数据备份

操作的前一天晚上进行了日常逻辑备份

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

二、模拟事故

模拟事故发生前后的业务情况

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 (0.17 sec)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;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 (0.03 sec)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 t8;Query OK, 0 rows affected (0.10 sec)

三、查看当前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)

四、恢复数据

拷贝生产库前一天晚上的备份文件以及备份到事故期间的binlog至临时库

scp alldb_bak.sql 192.168.8.32:/mysql/backup/dump/

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

在临时库创建出现事故的database

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)

从备份中恢复test数据库

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.01 sec)mysql> 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中怎么通过binlog日志恢复数据，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。