使用冷备和binlog恢复误删数据库

最新推荐文章于 2024-08-06 00:15:00 发布

congqie7381

最新推荐文章于 2024-08-06 00:15:00 发布

阅读量103

点赞数

背景描述：
原库，用来模拟生产库。
mysql安装方式：源码安装。
mysql安装家目录：/home/mysql/mysql
测试库：
用来恢复数据。
mysql安装方式：源码安装。
mysql安装家目录：/home/mysql/testdb
由于是做测试，故原库和测试库在一台机器上。
[mysql@MYSQL-SVR1 mysql]$ pwd
/home/mysql/mysql
--停库
[mysql@MYSQL-SVR1 mysql]$ ./bin/mysql.server stop
Shutting down MySQL.. [确定]
--冷备
[mysql@MYSQL-SVR1 mysql]$ cp -r var/ /home/mysql/backup/var-20150728.bak
--重新启动数据库
[mysql@MYSQL-SVR1 mysql]$ ./bin/mysql.server start
Starting MySQL... [确定]
--使用sysbench打上流量，目的是为了模拟生产库有数据写入
./sysbench --test=oltp --mysql-user=root --mysql-password=123 --mysql-db=test1 --oltp-table-name=t3 prepare
./sysbench --test=oltp --mysql-user=root --mysql-password=123 --mysql-db=test1 --oltp-table-name=t3 --num-threads=3 run
--手动切换binlog日志
mysql> flush logs;
Query OK, 0 rows affected (0.17 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
--模拟删除数据库
mysql> drop database sakila;
Query OK, 23 rows affected (0.31 sec)
--继续切换binlog日志
mysql> flush logs;
Query OK, 0 rows affected (0.27 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.16 sec)
--下面开始恢复
--先用全备启动测试库
--将备份替换到测试库的var目录下
rm -rf /home/mysql/testdb/var
mv /home/mysql/backup/var-20150728.bak /home/mysql/testdb/var
--启动测试库
[mysql@MYSQL-SVR1 var]$ cd ..
[mysql@MYSQL-SVR1 testdb]$ ./bin/mysql.server start
Starting MySQL.. [确定]
恢复全量之后：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| employees |
| mysql |
| sakila |
| test |
| test1 |
+--------------------+
7 rows in set (0.07 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000040 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
--接下来使用binlog将数据库恢复到删除sakila库的前一刻，这就需要找到binlog的起始点和结束点
--在备份中找到增量恢复的起始点，也就是备份中的最后一个position.
--在原库线上binlog中找到恢复的结束点，也就是删除sakila库前的一个position.
--找起始点，也就是查询备份中的最后一个binlog
[mysql@MYSQL-SVR1 var]$ tail -1 mysql-bin.index
./mysql-bin.000039
--查看备份中的最后一个position,这里为mysql-bin.000039的106，查看整个binlog,可发现，里面没有什么实质内容，也就是说可以从mysql-bin.000040做增量恢复
[mysql@MYSQL-SVR1 var]$ mysqlbinlog mysql-bin.000039|tail
BINLOG '
Wxy3VQ8JVk99ZgAAAGoAAAAAAAQANS4xLjcyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABbHLdVEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#150728 14:08:49 server id 2102351369 end_log_pos 125 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
--在原库中找结束点
--线上的binlog
-rw-rw---- 1 mysql mysql 603958 07-28 14:06 mysql-bin.000037
-rw-rw---- 1 mysql mysql 4002437 07-28 14:07 mysql-bin.000038
-rw-rw---- 1 mysql mysql 125 07-28 14:08 mysql-bin.000039
-rw-rw---- 1 mysql mysql 856802 07-28 14:10 mysql-bin.000040
-rw-rw---- 1 mysql mysql 707848 07-28 14:10 mysql-bin.000041
-rw-rw---- 1 mysql mysql 281308 07-28 14:10 mysql-bin.000042
-rw-rw---- 1 mysql mysql 1124324 07-28 14:11 mysql-bin.000043
-rw-rw---- 1 mysql mysql 108424 07-28 14:11 mysql-bin.000044
-rw-rw---- 1 mysql mysql 69159 07-28 14:11 mysql-bin.000045
-rw-rw---- 1 mysql mysql 4119553 07-28 14:12 mysql-bin.000046
-rw-rw---- 1 mysql mysql 608 07-28 14:11 mysql-bin.index
--14:10 左右发现删除日志
[mysql@MYSQL-SVR1 var]$ mysqlbinlog mysql-bin.000041 | grep "drop database"
[mysql@MYSQL-SVR1 var]$ mysqlbinlog mysql-bin.000042 | grep "drop database"
[mysql@MYSQL-SVR1 var]$ mysqlbinlog mysql-bin.000043 | grep "drop database"
drop database sakila
//找到了，在mysql-bin.000043中
--使用mysqlbinlog查看 mysql-bin.000043,找到删除语句前的position，这里是979589
# at 979562
#150728 14:11:22 server id 2102351369 end_log_pos 979589 Xid = 81689
COMMIT/*!*/;
# at 979589
#150728 14:11:22 server id 2102351369 end_log_pos 979674 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1438063882/*!*/;
drop database sakila
/*!*/;
# at 979674
#150728 14:11:22 server id 2102351369 end_log_pos 979743 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1438063882/*!*/;
BEGIN
/*!*/;
--需要应用的日志
mysql-bin.000040
mysql-bin.000041
mysql-bin.000042
mysql-bin.000043 从其实4到 979589
--将binlog导入到文本文件
[mysql@MYSQL-SVR1 var]$ mysqlbinlog mysql-bin.000040 > /home/mysql/backup/binlog-40.sql
[mysql@MYSQL-SVR1 var]$ mysqlbinlog mysql-bin.000041 > /home/mysql/backup/binlog-41.sql
[mysql@MYSQL-SVR1 var]$ mysqlbinlog mysql-bin.000042 > /home/mysql/backup/binlog-42.sql
[mysql@MYSQL-SVR1 var]$ mysqlbinlog --stop-position=979589 mysql-bin.000043 > /home/mysql/backup/binlog-43.sql
--在测试数据库中应用
mysql> source /home/mysql/backup/binlog-40.sql
mysql> source /home/mysql/backup/binlog-41.sql
mysql> source /home/mysql/backup/binlog-42.sql
mysql> source /home/mysql/backup/binlog-43.sql
--可以看到position增大
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000040 | 2825100 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
--在测试库中导出sakila库
[mysql@MYSQL-SVR1 testdb]$ ./bin/mysqldump -uroot -p123 --default-character-set=utf8 sakila > /home/mysql/backup/sakila.20150728.sql
--在原库中创建sakila库，否则无法导入表
mysql> create database sakila;
--在原库中导入
mysql> source /home/mysql/backup/sakila.20150728.sql
mysql> use sakila;
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)