首页 / 日志 / MySQL二进制日志管理和数据恢复

MySQL二进制日志管理和数据恢复

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL二进制日志管理和数据恢复，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11294字，纯文字阅读大概需要17分钟。

内容图文

一、二进制日志的作用

  • 备份恢复
  • 主从复制

二、启用二进制日志

查看当前MySQL是否启用二进制日志：

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

通过配置开启二进制日志：

server_id=1             #设定server_id
log_bin=mysql-bin       #开启二进制日志，并设置文件名称前缀为mysql-bin
binlog_format=row       #二进制日志格式

修改之后重启MySQL。在MySQL的数据目录下可以看到两个文件：

[root@server01 ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-rw---- 1 mysql mysql 120 12月 12 19:35 /usr/local/mysql/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql  19 12月 12 19:35 /usr/local/mysql/data/mysql-bin.index

mysql-bin.index文件中记录了当前正在使用的二进制日志文件。
mysql-bin.000001是记录二进制日志内容的文件。

三、二进制日志文件记录的内容

二进制日志是SQL层的功能，记录的是数据变更类的SQL语句，DCL、DDL和已经提交事务的DML语句。
二进制日志文件的格式：

  • statement：语句模式。是MySQL5.6的默认格式，会将DML语句原封不动的记录；
  • row：行模式。是MySQL5.7的默认格式，记录数据行的变化；
  • mixed：以上两种模式的混合模式。

statement和row的对比：

  • statement可读性很高，因为是将DML语句原封不动的记录。日志量少，但不够严谨。
  • row的日志量很大，可读性低。但足够严谨。

四、event事件

4.1、概念

event事件是二进制日志记录的最小单元。一个DCL或DDL是一个事件，而一个DML语句则被分成四个events：

BEGIN;
DML1;
DML2;
COMMIT;

4.2、查看event

4.2.1、查看有效的二进制日志文件

查看当前正在使用的二进制日志文件：SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
  • File：当前使用的二进制日志文件的名称；
  • Position：最后一个事件的结束位置。

查看所有有效的二进制日志文件：SHOW BINARY LOGS;

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       167 |
| mysql-bin.000002 |       167 |
| mysql-bin.000003 |       167 |
| mysql-bin.000004 |       167 |
| mysql-bin.000005 |       167 |
| mysql-bin.000006 |       120 |
+------------------+-----------+

4.2.3、查看所有event

通过SHOW BINLOG EVENTS IN命令查看指定二进制日志文件中的event：

mysql> SHOW BINLOG EVENTS IN "mysql-bin.000006";
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                     |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| mysql-bin.000006 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.50-log, Binlog ver: 4                    |
| mysql-bin.000006 | 120 | Query       |         1 |         240 | use `school`; create table test1(id int(11) primary key) |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
  • Log_name：使用的二进制日志文件的名称；
  • Pos：事件的起始位置；
  • Event_type：事件类型。“Format_desc”是每个二进制日志文件的第一个事件。是MySQL识别二进制日志文件的必要信息；
  • Server_id：MySQL实例编号；
  • Info：事件的内容。

4.2.4、查看二进制日志文件内容

通过MySQL自带的mysqlbinlog命令可以查看二进制日志文件中的内容：

[root@server01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000006

输出的内容是经过编码后的，通过mysqlbinlog命令的--base64-output对内容解码：

[root@server01 ~]# mysqlbinlog --base64-output=decode-rows -vvvv /usr/local/mysql/data/mysql-bin.000006 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201212 20:01:16 server id 1  end_log_pos 120 CRC32 0x6132ceb3 	Start: binlog v 4, server v 5.6.50-log created 201212 20:01:16
# Warning: this binlog is either in use or was not closed properly.
# at 120
#201212 20:04:38 server id 1  end_log_pos 240 CRC32 0x907a552f 	Query	thread_id=1	exec_time=0	error_code=0
use `school`/*!*/;
……
create table test1(id int(11) primary key)
……

从上面的输出中，能够找出事件的：

  • 开始位置：“at 120”；
  • 开始时间：“201212 20:04:38”；
  • MySQL实例号：“server id 1”；
  • 事件结束位置：“end_log_pos 240 ”
  • DDL语句的内容：“create table ……”。

同时，通过-d选项可以只查看指定库名的二进制日志：

[root@server01 ~]# mysqlbinlog -d school /usr/local/mysql/data/mysql-bin.000006

五、通过二进制日志恢复数据

5.1、应用场景

  • 物理损坏：硬盘、文件系统、数据文件损坏；
  • 逻辑损坏：DELETE、TRUNCATE、DROP、UPDATE等语句。

5.2、恢复步骤

二进制日志文件截取：

  • 查看正在使用的二进制日志文件：SHOW MASTER STATUS;；
  • 确定起止位置：SHOW BINLOG EVENTS IN "mysql-bin.000006;"；
  • 通过mysqlbinlog命令导出为文件：mysqlbinlog --start-position=120 --stop-position=520 /usr/local/mysql/data/mysql-bin.000001 > /tmp/binlog.sql。

通过source恢复：

  • 关闭当前会话的二进制日志的生成：SET sql_log_bin=0。目的是为了在二进制日志文件中不产生重复的记录；
  • 导入sql文件：source /tmp/binlog.sql；
  • 重新打开当前会话的二进制日志的生成：SET sql_log_bin=1。

5.3、案例

mysql> CREATE DATABASE school CHARSET utf8mb4;
mysql> USE school;
mysql> CREATE TABLE students(id int(11) primary key);
mysql> INSERT INTO students VALUES(1),(2),(3);
mysql> INSERT INTO students VALUES(4),(5),(6);
mysql> DELETE FROM students WHERE id=4;
mysql> INSERT INTO students VALUES(7),(8),(9);
mysql> DROP TABLE students;

现在要恢复表“students”。假如，“DELETE”和“DROP”是误操作。
1）首先确定当前使用的二进制日志文件为“mysql-bin.000001”：

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1306 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2）因为在INSERT语句中穿插了DELETE语句，所以起止位置不是连续的，那也就需要确定两段起止位置。通过SHOW BINLOG EVENTS IN “mysql-bin.000001”命令查看event事件：

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 |         120 | Server ver: 5.6.50-log, Binlog ver: 4                         |
| mysql-bin.000001 |  120 | Query       |         1 |         236 | CREATE DATABASE school CHARSET utf8mb4                        |
| mysql-bin.000001 |  236 | Query       |         1 |         359 | use `school`; CREATE TABLE students(id int(11) primary key)   |
| mysql-bin.000001 |  359 | Query       |         1 |         433 | BEGIN                                                         |
| mysql-bin.000001 |  433 | Table_map   |         1 |         486 | table_id: 76 (school.students)                                |
| mysql-bin.000001 |  486 | Write_rows  |         1 |         536 | table_id: 76 flags: STMT_END_F                                |
| mysql-bin.000001 |  536 | Xid         |         1 |         567 | COMMIT /* xid=122 */                                          |
| mysql-bin.000001 |  567 | Query       |         1 |         641 | BEGIN                                                         |
| mysql-bin.000001 |  641 | Table_map   |         1 |         694 | table_id: 76 (school.students)                                |
| mysql-bin.000001 |  694 | Write_rows  |         1 |         744 | table_id: 76 flags: STMT_END_F                                |
| mysql-bin.000001 |  744 | Xid         |         1 |         775 | COMMIT /* xid=123 */                                          |
| mysql-bin.000001 |  775 | Query       |         1 |         849 | BEGIN                                                         |
| mysql-bin.000001 |  849 | Table_map   |         1 |         902 | table_id: 76 (school.students)                                |
| mysql-bin.000001 |  902 | Delete_rows |         1 |         942 | table_id: 76 flags: STMT_END_F                                |
| mysql-bin.000001 |  942 | Xid         |         1 |         973 | COMMIT /* xid=124 */                                          |
| mysql-bin.000001 |  973 | Query       |         1 |        1047 | BEGIN                                                         |
| mysql-bin.000001 | 1047 | Table_map   |         1 |        1100 | table_id: 76 (school.students)                                |
| mysql-bin.000001 | 1100 | Write_rows  |         1 |        1150 | table_id: 76 flags: STMT_END_F                                |
| mysql-bin.000001 | 1150 | Xid         |         1 |        1181 | COMMIT /* xid=125 */                                          |
| mysql-bin.000001 | 1181 | Query       |         1 |        1306 | use `school`; DROP TABLE `students` /* generated by server */ |
+------------------+------+-------------+-----------+-------------+---------------------------------------------------------------+

3）从event事件中，确定了创建表的起始位置是“236”，到DELETE语句执行之前的终止位置是“775”。
4）从“902”位置的事件类型“Delete_rows”可以确定该事件为DELETE语句，“BEGIN”到“COMMIT”是一个完整的事务事件。
5）DROP语句之前INSERT语句的起始位置是“973”，终止位置是“1181”。
6）通过mysqlbinlog命令导出两段数据到sql文件中：

[root@server01 ~]# mysqlbinlog --start-position=236 --stop-position=775 /usr/local/mysql/data/mysql-bin.000001 > /tmp/binlog1.sql
[root@server01 ~]# mysqlbinlog --start-position=973 --stop-position=1181 /usr/local/mysql/data/mysql-bin.000001 > /tmp/binlog2.sql

7）通过source命令导入：

mysql> FLUSH LOGS;                 #启用新的二进制日志文件
mysql> SET sql_log_bin=0;          #关闭当前会话的二进制日志的生成
mysql> SOURCE /tmp/binlog1.sql;    #导入第一个SQL文件
mysql> SOURCE /tmp/binlog2.sql;    #导入第二个SQL文件
mysql> SET sql_log_bin=1;          #重新打开当前会话的二进制日志的生成
mysql> SELECT * FROM students;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+

六、清理二进制日志文件

通过参数配置定期自动清理

通过expire_logs_days设置日志文件过期时间。

手动清理

不能使用rm命令直接删除二进制日志文件。
通过PURGE语句清理：

  • PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;：删除今天之前，再往前推3天的日志文件；
  • PURGE BINARY LOGS TO "mysql-bin.000005;"：删除“mysql-bin.000005”文件之前的日志文件。

通过RESET MASTER重置二进制日志文件，即重新从“mysql-bin.000001”文件开始。

二进制文件滚动

二进制文件滚动，指将二进制日志写入到新的二进制日志文件中：

  • 重启MySQL；
  • 通过“FLUSH LOGS”语句；
  • 日志文件达到参数“expire_logs_days”设定的文件大小；
  • 在备份时，加入参数。

内容总结

以上是互联网集市为您收集整理的MySQL二进制日志管理和数据恢复全部内容，希望文章能够帮你解决MySQL二进制日志管理和数据恢复所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/867658.html

来源：【匿名】