如何利用MySQL的binlog恢复误删数据库详解 |
您所在的位置:网站首页 › binlog-ignore-db=mysql › 如何利用MySQL的binlog恢复误删数据库详解 |
文章来源: 学习通http://www.bdgxy.com/ 目录 1 查看当前数据库内容并备份数据库 2 开启bin_log功能 3 模拟误操作(插入3条数据,删除数据库) 4 数据恢复 5 总结 1 查看当前数据库内容并备份数据库 查看数据库信息: 备份数据库: [root@localhost ~]# mysqldump -u root -p t > /mnt/t.sql Enter password: [root@localhost ~]# ll /mnt/t.sql -rw-r--r-- 1 root root 1771 Aug 25 11:56 /mnt/t.sql 2 开启bin_log功能首先查看数据库是否开启bin_log功能 mysql> show variables like "%log_bin%";需要修改mysql的配置文件,/etc/的my.cnf,添加一句log_bin = mysql_bin即可 3 模拟误操作(插入3条数据,删除数据库) mysql> insert into t1 values (3); Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values (4); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values (5); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; ±-----+ | id | ±-----+ | 1 | | 2 | | 5 | | 4 | | 3 | ±-----+ 5 rows in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.00 sec) mysql> show master status; ±-----------------±---------±-------------±-----------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ±-----------------±---------±-------------±-----------------+ | mysql_bin.000003 | 106 | | | ±-----------------±---------±-------------±-----------------+ 1 row in set (0.00 sec) 删除数据: mysql> truncate t1; Query OK, 0 rows affected (0.00 sec)mysql> select * from t1; Empty set (0.00 sec) 此时突然数据库损坏或者人为删除 mysql> drop table t1; Query OK, 0 rows affected (0.00 sec)mysql> show tables; Empty set (0.00 sec) 4 数据恢复1 用已经备份的/mnt/t.sql来恢复数据 mysql> source /mnt/t.sql; Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> show tables; ±------------+ | Tables_in_t | ±------------+ | t1 | ±------------+ 1 row in set (0.00 sec) mysql> select * from t1; ±-----+ | id | ±-----+ | 1 | | 2 | ±-----+ 2 rows in set (0.00 sec) 2 还有三条数据没有恢复,怎么办。只能用bin-log来恢复 [root@localhost ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000002 | mysql -u root -p123.com t mysql> use t; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysql> select * from t1; ±-----+ | id | ±-----+ | 1 | | 2 | | 3 | | 4 | | 5 | ±-----+ 5 rows in set (0.00 sec) mysql> 5 总结备份数据 mysqldump -uroot -p123456 test -l -F '/tmp/test.sql' -l:读锁(只能读取,不能更新) -F:即flush logs,可以重新生成新的日志文件,当然包括log-bin日志查看binlog日志 mysql>show master status;导入之前备份数据 mysql -uroot -p t -v -f reset master; Query OK, 0 rows affected (0.01 sec)mysql> show master status; ±-----------------±---------±-------------±-----------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ±-----------------±---------±-------------±-----------------+ | mysql-bin.000001 | 106 | | | ±-----------------±---------±-------------±-----------------+ mysql> flush logs;#关闭当前的二进制日志文件并创建一个新文件,新的二进制日志文件的名字在当前的二进制文件的编号上加1。到此这篇关于如何利用MySQL的binlog恢复误删数据库的文章就介绍到这了,更多相关MySQL binlog恢复误删数据库内容请搜索菜鸟教程www.piaodoo.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持菜鸟教程www.piaodoo.com! |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |