本篇文章小编给大家分享一下利用MySQL的binlog恢复误删数据库代码示例解析,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
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 -A Database 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恢复binlog-file二进制日志文件
mysqlbinlog --no-defaults binlog-file | mysql -uroot -p t从某一(367)点开始恢复
mysqlbinlog --no-defaults --stop-position="367" mysql-bin.000001| mysql -uroot -p t先查好那一点,用more来查看
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | more然后恢复
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | /usr/bin/mysql -uroot -p t重置binlog日志
mysql> 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。