Mysql数据丢失分析与数据恢复

Mysql数据丢失分析

1.通过日志查找丢失原因

1.1.查看二进制日志文件

# 查看二进制日志是否打开及日志路径
show variables like 'log_bin%';
# 名词解释
log_bin 二进制日志是否开启状态
log_bin_basename 二进制日志路径

+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log_bin                         | ON                                |
| log_bin_basename                | /data/mysqldb/log/mysql-bin       |
| log_bin_index                   | /data/mysqldb/log/mysql-bin.index |
| log_bin_trust_function_creators | OFF                               |
| log_bin_use_v1_row_events       | OFF                               |
+---------------------------------+-----------------------------------+


# 查看二进制日志文件列表
show binary logs;

+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| binlog.000001   |       409 |
| binlog.000002   |       363 |
+-----------------+-----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

1.2.查看二进制日志文件内容

mysql二进制日志内容可通过mysql自带的binlog和mysqlbinlog命令查看，下面介绍下查看日志的具体操作。

1.binlog查看二进制日志内容

binlog查看Mysql二进制日志优点就是操作简单方便，可以直接SQL语句查询日志内容。缺点是不显示sql执行的日期，如果需要定位sql执行的日期就需要用到mysqlbinlog命令。

【最简单的查询日志语法】

show binlog events IN 'binlog.000002';

- binlog.000002：二进制日志文件名称

# 查询结果
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| binlog.000002    |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| binlog.000002    | 123 | Previous_gtids |         1 |         154 |                                       |
| binlog.000002    | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| binlog.000002    | 219 | Query          |         1 |         291 | BEGIN                                 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
- Info：操作数据库的sql语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

【完整的查询日志语法】

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

- IN ‘log_name’：指定要查询的binlog文件名（如果省略此参数，则默认指定第一个binlog文件）；
- FROM pos：指定从哪个pos起始点开始查起（如果省略此参数，则从整个文件的第一个pos点开始算）；
- LIMIT【offset】：偏移量（默认为0）；
- row_count：查询总条数（如果省略，则显示所有行）。
1
2
3
4
5
6

2.mysqlbinlog查看二进制日志内容

mysqlbinlog查看日志的优点可以将二进制日志文件重定向为sql格式文件，并下载到本机进行查看，同时显示sql执行日期方便根据日期来定位sql进行分析和恢复数据操作。

mysqlbinlog命令是Mysql自带的一个linux命令，注意它不是sql语句命令因此在sql窗口执行是无法识别的。

在linux上进入mysql二进制日志文件目录，然后在执行下面的命令。

【查看二进制日志内容】

mysqlbinlog --no-defaults binlog.000002
1

【二进制日志下载并重定向为sql文件】

上面查看日志是在linux环境查看，如果日志内容很多查看起来不是很方便，可以将日志下载到本地查看。并将它转为sql文件，可以直接打开查看其内容。

# 查看日志时如果报错 mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'
# 加上--no-defaults参数
mysqlbinlog --no-defaults binlog.000002 > ./binlog02.sql

1
2
3
4

2.数据恢复

登录mysql所在的服务器，进入mysql二进制文件目录，然后执行下面的命令恢复数据。

mysqlbinlog --no-defaults binlog.000002 |mysql -uqalight -p
1