本文介绍了如何在误删除MySQL数据库后，通过binlog日志恢复到最新数据。详细步骤包括查看binlog信息、删除数据库、利用备份及binlog恢复数据，并验证恢复结果，强调了binlog在数据恢复中的关键作用。
MySQL的binlog日志是MySQL日志中非常重要的一种日志，记录了数据库所有的DML操作。通过binlog日志我们可以进行数据库的读写分离、数据增量备份以及服务器宕机时的数据恢复。
每个业务数据库必然都会定期做备份。可以在误删除或磁盘故障的时候将丢失的数据快速恢复。但是可以想想备份都是在固定时间点。不能完全恢复到最新的数据，这是不能接受的。
试想一下，在主从架构中误操作把主库的数据库直接干掉了，而备份的数据还是几个小时前的。这几个小时内插入了很多数据 这些数据怎么恢复呢？ 这时候就体现binlog日志的重要性了。
实践开始
MySQL 版本
[root@docker ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86_64) using EditLine wrapper
系统版本
[root@docker ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
查看当前所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| devops |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
用devops库来做这次的验证
对现有数据库做个备份
# 看一下数据库有哪些数据
mysql> show tables;
+------------------+
| Tables_in_devops |
+------------------+
| ops_user |
+------------------+
1 row in set (0.00 sec)
mysql> select * from ops_user;
+----+--------------+----------+-----------------------+------+--------+------------+
| id | username | password | duties | age | gender | entry_time |
+----+--------------+----------+-----------------------+------+--------+------------+
| 2 | 贝克汉姆 | 123456 | 自动化运维 | 43 | 男 | 20190313 |
| 3 | 维多利亚 | 123456 | 自动化运维 | 43 | 女 | 20190313 |
| 4 | 罗密欧 | 123456 | 运维先生 | 28 | 男 | 20190313 |
| 5 | 朱丽叶 | 123456 | 先生情人 | 36 | 女 | 20190313 |
| 6 | 小丸子 | 123456 | 先生女友 | 18 | 女 | 20190313 |
+----+--------------+----------+-----------------------+------+--------+------------+
整库做个备份
# mysqldump -uroot -p123456 -B devops > /root/devops.sql
登录到数据库刷新binlog日志
每次刷新和服务重启的时候，都会生成一个binlog日志文件。刷新binlog日志，生成一个新的日志，那么我们之后所要操做的内容都会被记录到新的日志文件中。
# 阶段binlog日志
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
# 查看状态
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql3306-bin.000009 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
mysql> use devops;
# 插入一条数据 作为备份时间后期间的新增数据
mysql> insert into ops_user(username,password,duties,age,gender,entry_time) values('花木兰','123456','自动化运维','30','女','20190313');
# 检查现在的数据
mysql> select * from ops_user;
+----+--------------+----------+-----------------------+------+--------+------------+
| id | username | password | duties | age | gender | entry_time |
+----+--------------+----------+-----------------------+------+--------+------------+
| 2 | 贝克汉姆 | 123456 | 自动化运维 | 43 | 男 | 20190313 |
| 3 | 维多利亚 | 123456 | 自动化运维媳妇 | 43 | 女 | 20190313 |
| 4 | 罗密欧 | 123456 | 运维先生 | 28 | 男 | 20190313 |
| 5 | 朱丽叶 | 123456 | 先生情人 | 36 | 女 | 20190313 |
| 6 | 小丸子 | 123456 | 先生女友 | 18 | 女 | 20190313 |
| 7 | 花木兰 | 123456 | 先生情妇 | 30 | 女 | 20190313 |
+----+--------------+----------+-----------------------+------+--------+------------+
6 rows in set (0.00 sec)
直接删库
mysql> drop database devops;
Query OK, 1 row affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
可以看到devops库已经被删掉，在生产环境中一定要禁止这类操作，太危险了。如果发生了，立即使用备份数据进行恢复。
mysql> source /root/devops.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
......
Database changed
Query OK, 0 rows affected (0.01 sec)
......
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
查看一下是否恢复成功
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| devops |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use devops;
Database changed
mysql> select * from ops_user;
+----+--------------+----------+-----------------------+------+--------+------------+
| id | username | password | duties | age | gender | entry_time |
+----+--------------+----------+-----------------------+------+--------+------------+
| 2 | 贝克汉姆 | 123456 | 自动化运维 | 43 | 男 | 20190313 |
| 3 | 维多利亚 | 123456 | 自动化运维媳妇 | 43 | 女 | 20190313 |
| 4 | 罗密欧 | 123456 | 运维先生 | 28 | 男 | 20190313 |
| 5 | 朱丽叶 | 123456 | 先生情人 | 36 | 女 | 20190313 |
| 6 | 小丸子 | 123456 | 先生女友 | 18 | 女 | 20190313 |
+----+--------------+----------+-----------------------+------+--------+------------+
5 rows in set (0.00 sec)
可以看到恢复了备份的数据，而备份后新增的一条数据没有。
查看binlog 信息
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql3306-bin.000009 | 2563 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql3306-bin.000009';
+----------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql3306-bin.000009 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql3306-bin.000009 | 123 | Previous_gtids | 1 | 154 | |
| mysql3306-bin.000009 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql3306-bin.000009 | 219 | Query | 1 | 293 | BEGIN |
| mysql3306-bin.000009 | 293 | Table_map | 1 | 364 | table_id: 118 (devops.ops_user) |
| mysql3306-bin.000009 | 364 | Write_rows | 1 | 450 | table_id: 118 flags: STMT_END_F |
| mysql3306-bin.000009 | 450 | Xid | 1 | 481 | COMMIT /* xid=214 */ |
| mysql3306-bin.000009 | 481 | Anonymous_Gtid | 1 | 546 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql3306-bin.000009 | 546 | Query | 1 | 644 | drop database devops |
| mysql3306-bin.000009 | 644 | Anonymous_Gtid | 1 | 709 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql3306-bin.000009 | 709 | Query | 1 | 877 | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `devops` /*!40100 DEFAULT CHARACTER SET latin1 */ |
| mysql3306-bin.000009 | 877 | Anonymous_Gtid | 1 | 942 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql3306-bin.000009 | 942 | Query | 1 | 1077 | use `devops`; DROP TABLE IF EXISTS `ops_user` /* generated by server */ |
| mysql3306-bin.000009 | 1077 | Anonymous_Gtid | 1 | 1142 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql3306-bin.000009 | 1142 | Query | 1 | 1635 | use `devops`; CREATE TABLE `ops_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`password` char(20) CHARACTER SET latin1 DEFAULT NULL,
`duties` char(20) DEFAULT NULL,
`age` char(20) CHARACTER SET latin1 DEFAULT NULL,
`gender` char(20) DEFAULT NULL,
`entry_time` char(20) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
| mysql3306-bin.000009 | 1635 | Anonymous_Gtid | 1 | 1700 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql3306-bin.000009 | 1700 | Query | 1 | 1825 | use `devops`; /*!40000 ALTER TABLE `ops_user` DISABLE KEYS */ |
| mysql3306-bin.000009 | 1825 | Anonymous_Gtid | 1 | 1890 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql3306-bin.000009 | 1890 | Query | 1 | 1964 | BEGIN |
| mysql3306-bin.000009 | 1964 | Table_map | 1 | 2035 | table_id: 120 (devops.ops_user) |
| mysql3306-bin.000009 | 2035 | Write_rows | 1 | 2343 | table_id: 120 flags: STMT_END_F |
| mysql3306-bin.000009 | 2343 | Xid | 1 | 2374 | COMMIT /* xid=242 */ |
| mysql3306-bin.000009 | 2374 | Anonymous_Gtid | 1 | 2439 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql3306-bin.000009 | 2439 | Query | 1 | 2563 | use `devops`; /*!40000 ALTER TABLE `ops_user` ENABLE KEYS */ |
+----------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set (0.00 sec)
从binlog的事件中可以看出执行drop databases 语句前的position节点为 546。因为在执行前我们截断了日志所以恢复开始的pos节点为154，停止的pos节点为546 。
[root@docker ~]# mysqlbinlog /usr/local/mysql/data/mysql_data1/mysql3306-bin.000009 --start-position 154 --stop-position 546 | mysql -uroot -p123456
查看是否恢复
mysql> use devops;
Database changed
mysql> select * from ops_user;
+----+--------------+----------+-----------------------+------+--------+------------+
| id | username | password | duties | age | gender | entry_time |
+----+--------------+----------+-----------------------+------+--------+------------+
| 2 | 贝克汉姆 | 123456 | 自动化运维 | 43 | 男 | 20190313 |
| 3 | 维多利亚 | 123456 | 自动化运维媳妇 | 43 | 女 | 20190313 |
| 4 | 罗密欧 | 123456 | 运维先生 | 28 | 男 | 20190313 |
| 5 | 朱丽叶 | 123456 | 先生情人 | 36 | 女 | 20190313 |
| 6 | 小丸子 | 123456 | 先生女友 | 18 | 女 | 20190313 |
| 7 | 花木兰 | 123456 | 先生情妇 | 30 | 女 | 20190313 |
+----+--------------+----------+-----------------------+------+--------+------------+
6 rows in set (0.00 sec)
查看master 状态
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql3306-bin.000009 | 2884 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库上看一下
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave01
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000009
Read_Master_Log_Pos: 2884
Relay_Log_File: docker-relay-bin.000022
Relay_Log_Pos: 3105
Relay_Master_Log_File: mysql3306-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
Exec_Master_Log_Pos: 2884
Relay_Log_Space: 3364
Until_Condition: None
......
Master_Server_Id: 1
Master_UUID: 0274f44c-9ca3-11e9-93c8-000c290f3e17
Master_Info_File: /usr/local/mysql-5.7.24-linux-glibc2.12-x86_64/data/mysql_data2/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
......
1 row in set (0.00 sec)
mysql> use devops;
Database changed
mysql> select * from ops_user;
+----+--------------+----------+-----------------------+------+--------+------------+
| id | username | password | duties | age | gender | entry_time |
+----+--------------+----------+-----------------------+------+--------+------------+
| 2 | 贝克汉姆 | 123456 | 自动化运维 | 43 | 男 | 20190313 |
| 3 | 维多利亚 | 123456 | 自动化运维媳妇 | 43 | 女 | 20190313 |
| 4 | 罗密欧 | 123456 | 运维先生 | 28 | 男 | 20190313 |
| 5 | 朱丽叶 | 123456 | 先生情人 | 36 | 女 | 20190313 |
| 6 | 小丸子 | 123456 | 先生女友 | 18 | 女 | 20190313 |
| 7 | 花木兰 | 123456 | 先生情妇 | 30 | 女 | 20190313 |
+----+--------------+----------+-----------------------+------+--------+------------+
6 rows in set (0.00 sec)
从结果看出不管是主库还是从库数据都恢复到了最新的。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
