记一次mysql的数据恢复

记一次mysql完备+binlog日志恢复

一.准备工作

首先创建一个测试库，然后插入一部分数据

root@localhost:(none) 14:06:13>create database test;
Query OK, 1 row affected (0.00 sec)

root@localhost:(none) 14:07:58>create table test.t1(id int,name char(10),sex enum("boy","girl"));
Query OK, 0 rows affected (0.01 sec)

root@localhost:(none) 14:08:58>insert into test.t1 values(1,"haha","boy");
Query OK, 1 row affected (0.00 sec)

root@localhost:(none) 14:09:06>insert into test.t1 values(2,"hehe","girl");
Query OK, 1 row affected (0.00 sec)

root@localhost:(none) 14:09:18>insert into test.t1 values(3,"xixi","boy");
Query OK, 1 row affected (0.00 sec)

root@localhost:(none) 14:09:29>select * from test.t1;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | haha | boy  |
|    2 | hehe | girl |
|    3 | xixi | boy  |
+------+------+------+
3 rows in set (0.00 sec)

二. 做一次完整的备份

加上–master-data选项可以记录偏移量，方便我们使用binlog日志恢复

[root@master ~]# mysqldump --master-data -A -p > all.sql
Enter password: 

三.进入数据库插入一部分数据

root@localhost:test 14:16:12>insert into test.t1 values(4,"aa","boy");
Query OK, 1 row affected (0.00 sec)

root@localhost:test 14:16:27>insert into test.t1 values(5,"bb","girl");
Query OK, 1 row affected (0.00 sec)

刷新下binlog日志然后在插入一些数据

root@localhost:test 14:16:39>flush logs;
Query OK, 0 rows affected (0.03 sec)

root@localhost:test 14:17:16>insert into test.t1 values(6,"cc","girl");
Query OK, 1 row affected (0.00 sec)

root@localhost:test 14:17:25>insert into test.t1 values(7,"dd","boy");
Query OK, 1 row affected (0.01 sec)

现在test数据库总共有7条记录

四.模拟误操作删除了数据库

root@localhost:test 14:17:34>drop database test;
Query OK, 1 row affected (0.00 sec)
root@localhost:(none) 14:20:17>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

五.开始恢复操作

首先恢复我们首次的完整备份

[root@master ~]# mysql -p < all.sql 
root@localhost:(none) 14:21:16>select * from test.t1;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | haha | boy  |
|    2 | hehe | girl |
|    3 | xixi | boy  |
+------+------+------+
3 rows in set (0.00 sec)

可以看到我们首次备份的三条数据已经还原了，然后我们使用binlog日志恢复
查看我们首次完整备份记录的binlog日志和偏移量

[root@master ~]# grep "CHANGE MASTER TO" all.sql  | head -1
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1532629;

查看我们的binlog日志存放路径，把需要恢复的binlog日志复制到临时恢复目录，注意一定要复制出去

[root@master ~]# cd /data/mysql/log/
[root@master log]# ls
error.log  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.index   relay.000001   relay.index  slow.log  test1.sql
[root@master log]# cp mysql-bin.000003 mysql-bin.000004 /tmp/

然后使用binlog日志恢复数据

[root@master tmp]# mysqlbinlog --start-position='1532629'  /tmp/mysql-bin.000003 | mysql -p
root@localhost:(none) 14:33:28>select * from test.t1;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | haha | boy  |
|    2 | hehe | girl |
|    3 | xixi | boy  |
|    4 | aa   | boy  |
|    5 | bb   | girl |
+------+------+------+
5 rows in set (0.01 sec)

然后我们查看下我们之前删除数据库的pos点

[root@master tmp]# mysqlbinlog  mysql-bin.000004 | grep -5 "drop database"
#200520 14:18:16 server id 1  end_log_pos 874 CRC32 0x060a83aa 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 874
#200520 14:18:16 server id 1  end_log_pos 966 CRC32 0x9cdc4cbc 	Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1589955496/*!*/;
drop database test
/*!*/;
# at 966
#200520 14:20:51 server id 1  end_log_pos 1031 CRC32 0x726d7410 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1031

从这里可以看出我们删除数据之前最后的pos点是874，然后只用最后的binlog日志恢复数据

[root@master tmp]# mysqlbinlog --stop-position='874' mysql-bin.000004 | mysql -p
root@localhost:(none) 14:37:26>select * from test.t1;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | haha | boy  |
|    2 | hehe | girl |
|    3 | xixi | boy  |
|    4 | aa   | boy  |
|    5 | bb   | girl |
|    6 | cc   | girl |
|    7 | dd   | boy  |
+------+------+------+
7 rows in set (0.00 sec)

到此我们可以看到所有的7条数据已全部恢复