目录
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 </mnt/t.sql -v查看导入的详细信息 -f是当中间遇到错误时，可以skip过去，继续执行下面的语句
恢复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。
到此这篇关于如何利用mysql的binlog恢复误删数据库的文章就介绍到这了,更多相关mysql binlog恢复误删数据库内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！