MySQL二进制日志截取和恢复

MySQL二进制日志截取和恢复

罗小黑
2021-08-21 / 0 评论 / 29 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2021年08月25日,已超过34天没有更新,若内容或图片失效,请留言反馈。

作用

数据恢复和主从配置

开启二进制日志

vim /etc/my.conf

[mysqld]
server-id=1  #(1~65535)
log-bin=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
binlog_format=row 
sync_binlog=1    //每次事务提交都立即刷写binlog到磁盘


mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

PS:binlog_format=(row、statement、mixed)

statement:SBR,语句模式记录日志,做什么命令,记录什么命令.
可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况:insert into  xx values (1,'sa',now()).
row         :RBR,行模式,数据行的变化。可读性较弱,对于范围操作日志大,不会出现记录错误.对高可用环境中的新特性要依赖于RBR(5.7版本默认)
mixed     :MBR,混合模式

查看二进制日志位置:

mysql> show variables like '%log_bin%';   # flush logs; 每执行一次就多一个日志
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       154 |
+------------------+-----------+
3 rows in set (0.00 sec)

查看现存的二进制日志

show binary logs;

查看二进制日志事件

show  binlog events in 'mysql-bin.000003' limit 5;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000003 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids |         1 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

在打印出来的信息中可以看到event事件的开始和结束号码,它可以方便我们从日志中截取想要的日志事件

查看二进制日志内容

[root@cs mysql]# mysqlbinlog mysql-bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210822 10:27:13 server id 1  end_log_pos 123 CRC32 0x98688dd8  Start: binlog v 4, server v 5.7.34-log created 210822 10:27:13
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
AbYhYQ8BAAAAdwAAAHsAAAABAAQANS43LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AdiNaJg=
'/*!*/;
# at 123
#210822 10:27:13 server id 1  end_log_pos 154 CRC32 0x1ae7a1b6  Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看和分析binlog

mysql> show binary logs;
mysql> show master status ;
mysql> show binlog events in 'binlog.000003';

[root@cs mysql]# mysqlbinlog mysql-bin.000003
[root@cs mysql]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000003

模拟

数据恢复

mysql> flush logs;
mysql> create database cs;
mysql> use cs;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(3);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop cs;



开始恢复
mysql> show master status;   #当前的二进制文件
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1364 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000004';  #查看二进制日志事件(重要的是创建数据库和删除数据库的Pos值)
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql-bin.000004 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000004 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 |  219 | Query          |         1 |         307 | create database cs                    |
| mysql-bin.000004 |  307 | Anonymous_Gtid |         1 |         372 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 |  372 | Query          |         1 |         466 | use `cs`; create table t1 (id int)    |
| mysql-bin.000004 |  466 | Anonymous_Gtid |         1 |         531 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 |  531 | Query          |         1 |         601 | BEGIN                                 |
| mysql-bin.000004 |  601 | Table_map      |         1 |         644 | table_id: 115 (cs.t1)                 |
| mysql-bin.000004 |  644 | Write_rows     |         1 |         684 | table_id: 115 flags: STMT_END_F       |
| mysql-bin.000004 |  684 | Xid            |         1 |         715 | COMMIT /* xid=349 */                  |
| mysql-bin.000004 |  715 | Anonymous_Gtid |         1 |         780 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 |  780 | Query          |         1 |         850 | BEGIN                                 |
| mysql-bin.000004 |  850 | Table_map      |         1 |         893 | table_id: 115 (cs.t1)                 |
| mysql-bin.000004 |  893 | Write_rows     |         1 |         933 | table_id: 115 flags: STMT_END_F       |
| mysql-bin.000004 |  933 | Xid            |         1 |         964 | COMMIT /* xid=351 */                  |
| mysql-bin.000004 |  964 | Anonymous_Gtid |         1 |        1029 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 1029 | Query          |         1 |        1099 | BEGIN                                 |
| mysql-bin.000004 | 1099 | Table_map      |         1 |        1142 | table_id: 115 (cs.t1)                 |
| mysql-bin.000004 | 1142 | Write_rows     |         1 |        1182 | table_id: 115 flags: STMT_END_F       |
| mysql-bin.000004 | 1182 | Xid            |         1 |        1213 | COMMIT /* xid=353 */                  |
| mysql-bin.000004 | 1213 | Anonymous_Gtid |         1 |        1278 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 1278 | Query          |         1 |        1364 | drop database cs                      |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
23 rows in set (0.00 sec)

[root@cs mysql]# mysqlbinlog --start-position=219 --stop-position=1278 /var/lib/mysql/mysql-bin.000004 >/tmp/bin.sql


mysql> set sql_log_bin=0;   #下面操作不会被记录到二进制文件(慎用)
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/bin.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.01 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
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, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 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, 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> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> use cs;
Database changed
mysql> show tables;
+--------------+
| Tables_in_cs |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
0

打赏

评论 (0)

取消