MySQL通过二进制日志恢复数据
配置
开启二进制日志
[root@lyucan ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
sync-binlog=1
innodb_support_xa=1
binlog_format=statement
[root@lyucan ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
上面的 binlog_format=statement 参数默认是row，表示用行来记录，这里为了方便观察，直接使用statement模式，这个参数可以动态修改
查看二进制日志是否开启
mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/mysql-bin |
| log_bin_index | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-----------------------------+
常用binlog操作命令
1、查看所有binlog列表
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2052 |
| mysql-bin.000002 | 943 |
| mysql-bin.000003 | 154 |
+------------------+-----------+
3 rows in set (0.00 sec)
2、查看master状态，即最后（最新）的一个binlog日志的编号及最后一个position的值
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3、flush logs刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2052 |
| mysql-bin.000002 | 943 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
4 rows in set (0.00 sec)
注意：当mysql服务器重启时，自动刷新binlog；
4、清空所有日志
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
查看二进制日志的方法
1、使用mysqlbinlog工具来查看二进制日志；
[root@lyucan ~]# mysqlbinlog /data/mysql/mysql-bin.000002
......
# at 484 ##position号
#180410 15:51:27 server id 1 end_log_pos 484 CRC32 0xb489f444 IntvarSET INSERT_ID=1/*!*/;
#180410 15:51:27 server id 1 end_log_pos 606 CRC32 0xdbf27bfd Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1523346687/*!*/;
insert into t_user (user_name) values ('张三') ##当binlog_format设置为statement时，可以看到明文的DML语句；
/*!*/;
# at 606
......
[root@lyucan ~]# mysqlbinlog -v -v /data/mysql/mysql-bin.000001
......
# at 343
#180410 15:35:16 server id 1 end_log_pos 390 CRC32 0x554a373c Write_rows: table id 141 flags: STMT_END_F
BINLOG '
NGnMWhMBAAAANAAAAFcBAAAAAI0AAAAAAAEABHRlc3QABnRfdXNlcgACAw8CWgAAt/Mk7w==
NGnMWh4BAAAALwAAAIYBAAAAAI0AAAAAAAEAAgAC//wEAAAABuW8oOS4iTw3SlU=
'/*!*/;
### INSERT INTO `test`.`t_user` ##当binlog_format设置为row时，需要加上-v -v 才能看到明文的DML语句；
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='张三' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
# at 390
......
2、在数据库里查；
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
## FROM pos：起始position号，不指定就是当前文件的第一个开始；
## LIMIT offset：偏移量，就是从起始position跳过几个再开始
## row_count：查询多少行，不指定就查到文件尾行；
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 1 | 308 | use `test`; truncate t_user |
| mysql-bin.000002 | 308 | Anonymous_Gtid | 1 | 373 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 373 | Query | 1 | 452 | BEGIN |
| mysql-bin.000002 | 452 | Intvar | 1 | 484 | INSERT_ID=1 |
| mysql-bin.000002 | 484 | Query | 1 | 606 | use `test`; insert into t_user (user_name) values ('张三') |
| mysql-bin.000002 | 606 | Xid | 1 | 637 | COMMIT /* xid=18 */ |
| mysql-bin.000002 | 637 | Anonymous_Gtid | 1 | 702 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 702 | Query | 1 | 781 | BEGIN |
| mysql-bin.000002 | 781 | Query | 1 | 889 | use `test`; delete from t_user where user_id=1 |
| mysql-bin.000002 | 889 | Xid | 1 | 920 | COMMIT /* xid=21 */ |
| mysql-bin.000002 | 920 | Stop | 1 | 943 | |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------+
14 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000002' from 154 limit 5,5;
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------+
| mysql-bin.000002 | 484 | Query | 1 | 606 | use `test`; insert into t_user (user_name) values ('张三') |
| mysql-bin.000002 | 606 | Xid | 1 | 637 | COMMIT /* xid=18 */ |
| mysql-bin.000002 | 637 | Anonymous_Gtid | 1 | 702 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 702 | Query | 1 | 781 | BEGIN |
| mysql-bin.000002 | 781 | Query | 1 | 889 | use `test`; delete from t_user where user_id=1 |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------+
5 rows in set (0.00 sec)
恢复数据一定要和备份一起操作，否则即使有二进制日志，也不能完全恢复数据；
恢复二进制日志的方法
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
常用参数选项解释：
–start-position=123 起始pos点
–stop-position=456 结束pos点
–start-datetime=”2016-9-25 22:01:08” 起始时间点
–stop-datetime=”2019-9-25 22:09:46” 结束时间点
–database=test1 指定只恢复test1数据库(一台主机上往往有多个数据库，只限本地log日志)
不常用选项：
-u –user=name 连接到远程主机的用户名
-p –password[=name] 连接到远程主机的密码
-h –host=name 从远程主机上获取binlog日志
–read-from-remote-server 从某个MySQL服务器上读取binlog日志
例子：
假设t1表里有这几个字段，我们误将第四行记录delete掉了，再通过binlog找回；
mysql> select * from t1;
+----+--------+-----+--------+
| id | name | age | addr |
+----+--------+-----+--------+
| 1 | 张三 | 12 | 南京 |
| 2 | 李四 | 21 | 北京 |
| 3 | 王五 | 23 | 上海 |
| 4 | 二蛋 | 10 | 农村 |
+----+--------+-----+--------+
4 rows in set (0.00 sec)
mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)
mysql> show binlog events in 'mysql-bin.000004'\G;
......
*************************** 75. row ***************************
Log_name: mysql-bin.000004
Pos: 6566
Event_type: Query
Server_id: 1
End_log_pos: 6685
Info: use `test`; insert into t1 values(4,'二蛋',10,'农村')
*************************** 76. row ***************************
......
恢复这条数据
[root@lyucan mysql]# mysqlbinlog --start-position=6566 --stop-position=6685 mysql-bin.000004 | mysql -uroot -p test
上面的例子可能举得不是很恰当，因为我们都知道已经丢失的数据是什么了，但是在实际情况中，我们往往不知道丢失了哪些数据，万一drop掉了整个数据库，难道要一条条去恢复吗，显然不可能，这就需要我们定时做好备份工作，一个完整的备份往往是全量备份+增量备份+二进制日志备份，在进行全量备份的时候，我们需要知道当前二进制日志的position号，这样就可以进行完全恢复；
在进行mysqldump的时候，我们可以指定一个-F参数，在备份的时候刷新下binlog日志，这样新的操作都会记录到另一个新的binlog文件里面去，便于我们进行数据恢复，而不用费时费力去找要从哪里进行我二进制日志的恢复；
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com