189 8069 5689
下文给大家带来关于利用MySQLbinlog日志恢复数据库数据的操作指引，感兴趣的话就一起来看看这篇文章吧，相信看完利用Mysqlbinlog日志恢复数据库数据的操作指引对大家多少有点帮助吧。
成都创新互联公司是一家集网站建设,汉源企业网站建设,汉源品牌网站建设,网站定制,汉源网站建设报价,网络营销,网络优化,汉源网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。
一、开启mysql-binlog日志
在mysql配置文件my.cnf加上如下配置
[mysqld]
log-bin=mysql-bin
binlog_do_db=bin_test
重启mysql
service mysqld restart
二、备份数据库
1）先查看一下当前数据库情况及binlog日志情况：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | bin_test | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2）创建库及数据：bin_test
mysql> create database bin_test;
Query OK, 1 row affected (0.00 sec)
mysql> use bin_test;
Database changed
mysql> create table abc(id int(10) primary key auto_increment,name varchar(255));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into abc(name) value('zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into abc(name) value('lisi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into abc(name) value('wangwu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from abc;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
3 rows in set (0.00 sec)
3）备份数据到/tmp/test.sql
[root@localhost ~]# whereis mysqldump
mysqldump: /usr/bin/mysqldump /usr/share/man/man1/mysqldump.1.gz
[root@localhost ~]# /usr/bin/mysqldump -uroot -p123456 bin_test > /tmp/test.sql
4）查看下binlog日志情况：
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Query | 1 | 197 | create database bin_test |
| mysql-bin.000001 | 197 | Query | 1 | 337 | use `bin_test`; create table abc(id int(10) primary key auto_increment,name varchar(255)) |
| mysql-bin.000001 | 337 | Intvar | 1 | 365 | INSERT_ID=1 |
| mysql-bin.000001 | 365 | Query | 1 | 471 | use `bin_test`; insert into abc(name) value('zhangsan') |
| mysql-bin.000001 | 471 | Intvar | 1 | 499 | INSERT_ID=2 |
| mysql-bin.000001 | 499 | Query | 1 | 601 | use `bin_test`; insert into abc(name) value('lisi') |
| mysql-bin.000001 | 601 | Intvar | 1 | 629 | INSERT_ID=3 |
| mysql-bin.000001 | 629 | Query | 1 | 733 | use `bin_test`; insert into abc(name) value('wangwu') |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
三、这时模拟误操作（删除数据库）
此时突然数据库损坏或者人为删除
mysql> drop database bin_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
再查看下binlog日志有没有记录删库的操作：
mysql> show binlog events in "mysql-bin.000001";
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Query | 1 | 197 | create database bin_test |
| mysql-bin.000001 | 197 | Query | 1 | 337 | use `bin_test`; create table abc(id int(10) primary key auto_increment,name varchar(255)) |
| mysql-bin.000001 | 337 | Intvar | 1 | 365 | INSERT_ID=1 |
| mysql-bin.000001 | 365 | Query | 1 | 471 | use `bin_test`; insert into abc(name) value('zhangsan') |
| mysql-bin.000001 | 471 | Intvar | 1 | 499 | INSERT_ID=2 |
| mysql-bin.000001 | 499 | Query | 1 | 601 | use `bin_test`; insert into abc(name) value('lisi') |
| mysql-bin.000001 | 601 | Intvar | 1 | 629 | INSERT_ID=3 |
| mysql-bin.000001 | 629 | Query | 1 | 733 | use `bin_test`; insert into abc(name) value('wangwu') |
| mysql-bin.000001 | 733 | Query | 1 | 822 | drop database bin_test |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
可以看到第20条的操作为删除数据库的操作。
四、此时数据库已经被完全破坏
1）使用mysqlbinlog命令进行恢复；首先在恢复前要确认正确的position起始值与终止值：
mysql> show binlog events in "mysql-bin.000001";
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Query | 1 | 197 | create database bin_test |
| mysql-bin.000001 | 197 | Query | 1 | 337 | use `bin_test`; create table abc(id int(10) primary key auto_increment,name varchar(255)) |
| mysql-bin.000001 | 337 | Intvar | 1 | 365 | INSERT_ID=1 |
| mysql-bin.000001 | 365 | Query | 1 | 471 | use `bin_test`; insert into abc(name) value('zhangsan') |
| mysql-bin.000001 | 471 | Intvar | 1 | 499 | INSERT_ID=2 |
| mysql-bin.000001 | 499 | Query | 1 | 601 | use `bin_test`; insert into abc(name) value('lisi') |
| mysql-bin.000001 | 601 | Intvar | 1 | 629 | INSERT_ID=3 |
| mysql-bin.000001 | 629 | Query | 1 | 733 | use `bin_test`; insert into abc(name) value('wangwu') |
| mysql-bin.000001 | 733 | Query | 1 | 822 | drop database bin_test |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
此时我们已经确认“起始值”为创建库时的起始：106；"终止值"为drop库时的前一句的结束：733
2）使用mysqlbinlog进行恢复：
mysqlbinlog --no-defaults --start-position="106" --stop-position="733" /var/lib/mysql/mysql-bin.000001 |mysql -uroot -p
Enter password:
3）查看下数据是否恢复成功：
11mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bin_test |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
11mysql> select * from bin_test.abc;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+----+----------+
3 rows in set (0.00 sec)
3）恢复成功
【总结】：mysql备份和bin-log日志
备份数据：
mysqldump -uroot -p123456 bin_test -l -F '/tmp/test.sql'
-l:读锁（只能读取，不能更新）
-F:即flush logs，可以重新生成新的日志文件，当然包括log-bin日志
查看binlog日志：
mysql>show master status
如果数据较多，先导入之前备份数据：
mysql -uroot -p123456 bin_test -v -f
-v查看导入的详细信息
-f是当中间遇到错误时，可以skip过去，继续执行下面的语句
恢复binlog-file二进制日志文件：
mysqlbinlog --no-defaults binlog-file | mysql -uroot -p123456
从某一(567)点开始恢复：
mysqlbinlog --no-defaults --start-position="567" mysql-bin.000001| mysql -uroot -p123456 test
先查好那一点（用more来查看）
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000001 --start-position="794" --stop-position="1055" | more
然后恢复：
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000001 --start-position="794" --stop-position="1055" | /usr/bin/mysql -uroot -p123456 test
重置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。
看了以上关于利用Mysqlbinlog日志恢复数据库数据的操作指引详细内容，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。