当前位置：首页 > 文章列表 > 数据库 > MySQL > Windows环境下MySQL8.0的binlog恢复

Windows环境下MySQL8.0的binlog恢复

来源：SegmentFault 2023-01-24 10:14:03 0浏览收藏

在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《Windows环境下MySQL8.0的binlog恢复》，聊聊MySQL、Windows、数据恢复，希望可以帮助到正在努力赚钱的你。

前言：数据库被误删过，想着学习
mysql
，将其整理出来，才有本次记录，本文为自己学习mysql记录，
线上环境慎用
，本人也不是专业的DBA。

一、MySQL日志

```
mysql
```
有两个重要的日志模块
```
redo log
```
(重做日志) 和
```
binlog
```
(归档日志)，

当有一条数据需要更新时，InnoDB引擎会在系统空闲的时候，将记录更新到磁盘中。会先将记录存放到
```
redo log
```
里，在更新内存，这个时候更新就完成了，InnoDB引擎会在系统空闲的时候，将记录更新到磁盘中，
```
redo log
```
是物理日志，记录在某个数据做了什么修改，
```
redo log
```
是InnoDB引擎中特有的日志，换句话说
```
redo log
```
是InnoDB引擎为了提高系统效率，用来做日志缓冲的。
```
binlog
```
是记录所有数据库表结构变更以及表数据修改的二进制日志（不会记录
```
SELECT
```
和
```
SHOW
```
这类操作），
```
binlog
```
是逻辑日志,记录语句的原始逻辑。
数据恢复: 可以通过
```
mysqlbinlog
```
工具进行数据恢复。

二、数据恢复

现在数据库（MySQL小黑窗口）执行下面命令,看MySQL日志是否开启

mysql> show variables like 'log_bin%';
+---------------------------------+----------------------------------------------------------------------+
| Variable_name                   | Value                                                                |
+---------------------------------+----------------------------------------------------------------------+
| log_bin                         | ON                                                                   |
| log_bin_basename                | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-RGVMCFU-bin       |
| log_bin_index                   | C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-RGVMCFU-bin.index |
| log_bin_trust_function_creators | OFF                                                                  |
| log_bin_use_v1_row_events       | OFF                                                                  |
+---------------------------------+----------------------------------------------------------------------+

这里显示的ON为开启,如果显示OFF为关闭,打开下面路径,编辑

my.ini

C:\ProgramData\MySQL\MySQL Server 8.0

添加下面属性,大概127行，

并重启 MySQL服务

# 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了
binlog_format = ROW
# binlog过期清理时间；
expire_logs_days= 7 
# binlog每个日志文件大小；
max_binlog_size = 512m 
# binlog缓存大小；
binlog_cache_size = 128m 
# 最大binlog缓存大小。
max_binlog_cache_size = 512m

准备数据

-- --------------------------------------------------------
-- 主机:                           127.0.0.1
-- 服务器版本:                        8.0.19 - MySQL Community Server - GPL
-- 服务器操作系统:                      Win64
-- HeidiSQL 版本:                  11.2.0.6213
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- 导出 mp_test 的数据库结构
CREATE DATABASE IF NOT EXISTS `mp_test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mp_test`;

-- 导出  表 mp_test.user 结构
CREATE TABLE IF NOT EXISTS `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) DEFAULT NULL COMMENT '名字',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 正在导出表  mp_test.user 的数据：~0 rows (大约)
DELETE FROM `user`;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`id`, `name`, `create_time`) VALUES
 (1, '王天风', '2021-09-17 16:15:49'),
 (2, '小南', '2021-09-14 19:16:04'),
 (3, '张强', '2021-09-16 16:16:17');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

这个时候不小心运行了
```
drop TABLE user
```
数据和表都没了,别慌

找到当前

MySQL

记录的

binlog

文件,在

C:\ProgramData\MySQL\MySQL Server 8.0\Data

目录,这里看到当前的日志是

binlog.000001

这个文件

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     2246 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

在看一下所有的

binlog

,我这里清空过

binlog

,剩下一个

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      2246 | No        |
+---------------+-----------+-----------+

查看

binlog

，定位误操作的pos或者时间段,我这里是在

binlog.000001

阶段删除的,注意下面的

Pos：

开始节点

Event_type：

类型

End_log_pos：

结束节点

Info：

描述

mysql> show binlog events in 'binlog.000001';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                             |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000001 |    4 | Format_desc    |         1 |         124 | Server ver: 8.0.19, Binlog ver: 4                                                                                                                                                                                                                |
| binlog.000001 |  124 | Previous_gtids |         1 |         155 |                                                                                                                                                                                                                                                  |
| binlog.000001 |  155 | Anonymous_Gtid |         1 |         232 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
| binlog.000001 |  232 | Query          |         1 |         351 | CREATE DATABASE `mp_test` /* xid=288 */                                                                                                                                                                                                          |
| binlog.000001 |  351 | Anonymous_Gtid |         1 |         430 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
| binlog.000001 |  430 | Query          |         1 |         735 | use `mp_test`; CREATE TABLE `mp_test`.`user`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) NULL COMMENT '名字',
  `create_time` datetime NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) /* xid=320 */       |
| binlog.000001 |  735 | Anonymous_Gtid |         1 |         814 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
| binlog.000001 |  814 | Query          |         1 |         900 | BEGIN                                                                                                                                                                                                                                            |
| binlog.000001 |  900 | Table_map      |         1 |         963 | table_id: 208 (mp_test.user)                                                                                                                                                                                                                     |
| binlog.000001 |  963 | Write_rows     |         1 |        1023 | table_id: 208 flags: STMT_END_F                                                                                                                                                                                                                  |
| binlog.000001 | 1023 | Xid            |         1 |        1054 | COMMIT /* xid=371 */                                                                                                                                                                                                                             |
| binlog.000001 | 1054 | Anonymous_Gtid |         1 |        1133 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
| binlog.000001 | 1133 | Query          |         1 |        1219 | BEGIN                                                                                                                                                                                                                                            |
| binlog.000001 | 1219 | Table_map      |         1 |        1282 | table_id: 208 (mp_test.user)                                                                                                                                                                                                                     |
| binlog.000001 | 1282 | Write_rows     |         1 |        1339 | table_id: 208 flags: STMT_END_F                                                                                                                                                                                                                  |
| binlog.000001 | 1339 | Xid            |         1 |        1370 | COMMIT /* xid=374 */                                                                                                                                                                                                                             |
| binlog.000001 | 1370 | Anonymous_Gtid |         1 |        1449 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
| binlog.000001 | 1449 | Query          |         1 |        1535 | BEGIN                                                                                                                                                                                                                                            |
| binlog.000001 | 1535 | Table_map      |         1 |        1598 | table_id: 208 (mp_test.user)                                                                                                                                                                                                                     |
| binlog.000001 | 1598 | Write_rows     |         1 |        1655 | table_id: 208 flags: STMT_END_F                                                                                                                                                                                                                  |
| binlog.000001 | 1655 | Xid            |         1 |        1686 | COMMIT /* xid=377 */                                                                                                                                                                                                                             |
| binlog.000001 | 1686 | Anonymous_Gtid |         1 |        1765 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
| binlog.000001 | 1765 | Query          |         1 |        1860 | BEGIN                                                                                                                                                                                                                                            |
| binlog.000001 | 1860 | Table_map      |         1 |        1923 | table_id: 208 (mp_test.user)                                                                                                                                                                                                                     |
| binlog.000001 | 1923 | Update_rows    |         1 |        2003 | table_id: 208 flags: STMT_END_F                                                                                                                                                                                                                  |
| binlog.000001 | 2003 | Xid            |         1 |        2034 | COMMIT /* xid=380 */                                                                                                                                                                                                                             |
| binlog.000001 | 2034 | Anonymous_Gtid |         1 |        2111 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
| binlog.000001 | 2111 | Query          |         1 |        2246 | use `mp_test`; DROP TABLE `user` /* generated by server */ /* xid=454 */                                                                                                                                                                         |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

通过

info

看到我在

Pos

2034

End_los_pos

2246 删除了

user

表

先来到MySQL下的bin目录,这个目录有个
```
mysqlbinlog.exe
```
,后面我们可以通过它来进行恢复,打开cmd进入下面目录
```
C:\Program Files\MySQL\MySQL Server 8.0\bin
```

在

C:\Program Files\MySQL\MySQL Server 8.0\bin

目录, cmd 运行下面命令,读取

binlog.000001

从开始位置351,到结束位置2034,并输出到

F:

盘下

0917.sql

这个文件，

binlog

在

C:\ProgramData\MySQL\MySQL Server 8.0\Data\

目录里面

mysqlbinlog --start-position=351 --stop-position=2034 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\0917.sql

打开

MySQL 8.0 Command Line Client

,就是

MySQL

自带的小黑窗口,运行下面命令

 source f:0917.sql

查看数据是否恢复

三、其他参数

cmd在

C:\ProgramData\MySQL\MySQL Server 8.0

路径下运行

mysqlbinlog --help

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client.

Usage: mysqlbinlog [options] log-files
-?, --help Display this help and exit.
--base64-output=name
Determine when the output statements should be
base64-encoded BINLOG statements: 'never' disables it and
works only for binlogs without row-based events;
'decode-rows' decodes row events into commented
pseudo-SQL statements if the --verbose option is also
given; 'auto' prints base64 only when necessary (i.e.,
for row-based events and format description events). If
no --base64-output[=name] option is given at all, the
default is 'auto'.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
-d, --database=name List entries for just this database (local log only).
--rewrite-db=name Rewrite the row event to point so that it can be applied
to a new database
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check This is a non-debug version. Catch this and exit.
--debug-info This is a non-debug version. Catch this and exit.
--default-auth=name Default authentication client-side plugin to use.
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-F, --force-if-open Force if binlog was not closed properly.
(Defaults to on; use --skip-force-if-open to disable.)
-f, --force-read Force reading unknown binlog events.
-H, --hexdump Augment output with hexadecimal and ASCII event dump.
-h, --host=name Get the binlog from server.
-i, --idempotent Notify the server to use idempotent mode before applying
Row Events
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-R, --read-from-remote-server
Read binary logs from a MySQL server. This is an alias
for read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
--read-from-remote-master=name
Read binary logs from a MySQL server through the
COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by
setting the option to either BINLOG-DUMP-NON-GTIDS or
BINLOG-DUMP-GTIDS, respectively. If
--read-from-remote-master=BINLOG-DUMP-GTIDS is combined
with --exclude-gtids, transactions can be filtered out on
the master avoiding unnecessary network traffic.
--raw Requires -R. Output raw binlog data instead of SQL
statements, output is to log files.
-r, --result-file=name
Direct output to a given file. With --raw this is a
prefix for the file names.
--server-id=# Extract only binlog entries created by the server having
the given id.
--server-id-bits=# Set number of significant bits in server-id
--set-charset=name Add 'SET NAMES character_set' to the output.
--shared-memory-base-name=name
Base name of shared memory.
-s, --short-form Just show regular queries: no extra info and no row-based
events. This is for testing only, and should not be used
in production systems. If you want to suppress
base64-output, consider using --base64-output=never
instead.
-S, --socket=name The socket file to use for connection.
--server-public-key-path=name
File path to the server public RSA key in PEM format.
--get-server-public-key
Get server public key
--ssl-mode=name SSL connection mode.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-key=name X509 key in PEM format.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1,
TLSv1.2, TLSv1.3
--ssl-fips-mode=name
SSL FIPS mode (applies only for OpenSSL); permitted
values are: OFF, ON, STRICT
--tls-ciphersuites=name
TLS v1.3 cipher to use.
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
-j, --start-position=#
Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-never Wait for more data from the server instead of stopping at
the end of the last log. Implicitly sets --to-last-log
but instead of stopping at the end of the last log it
continues to wait till the server disconnects.
--stop-never-slave-server-id=#
The slave server_id used for --read-from-remote-server
--stop-never. This option cannot be used together with
connection-server-id.
--connection-server-id=#
The slave server_id used for --read-from-remote-server.
This option cannot be used together with
stop-never-slave-server-id.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username.
-v, --verbose Reconstruct pseudo-SQL statements out of row events. -v
-v adds comments on column data types.
-V, --version Print version and exit.
--open-files-limit=#
Used to reserve file descriptors for use by this program.
-c, --verify-binlog-checksum
Verify checksum binlog events.
--binlog-row-event-max-size=#
The maximum size of a row-based binary log event in
bytes. Rows will be grouped into events smaller than this
size if possible. This value must be a multiple of 256.
--skip-gtids Do not preserve Global Transaction Identifiers; instead
make the server execute the transactions as if they were
new.
--include-gtids=name
Print events whose Global Transaction Identifiers were
provided.
--exclude-gtids=name
Print all events but those whose Global Transaction
Identifiers were provided.
--print-table-metadata
Print metadata stored in Table_map_log_event
-C, --compress Use compression in server/client protocol.
--compression-algorithms=name
Use compression algorithm in server/client protocol.
Valid values are any combination of
'zstd','zlib','uncompressed'.
--zstd-compression-level=#
Use this compression level in the client/server protocol,
in case --compression-algorithms=zstd. Valid range is
between 1 and 22, inclusive. Default is 3.
--require-row-format
Fail when printing an event that was not logged using row
format or other forbidden events like Load instructions
or the creation/deletion of temporary tables.

Default options are read from the following files in the given order:
C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 8.0\my.ini C:\Program Files\MySQL\MySQL Server 8.0\my.cnf
The following groups are read: mysqlbinlog client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file,
except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
base64-output decode-rows
bind-address (No default value)
character-sets-dir (No default value)
database (No default value)
rewrite-db (No default value)
default-auth (No default value)
disable-log-bin FALSE
force-if-open TRUE
force-read FALSE
hexdump FALSE
host (No default value)
idempotent FALSE
local-load (No default value)
offset 0
plugin-dir (No default value)
port 0
read-from-remote-server FALSE
read-from-remote-master (No default value)
raw FALSE
result-file (No default value)
server-id 0
server-id-bits 32
set-charset (No default value)
shared-memory-base-name (No default value)
short-form FALSE
socket (No default value)
server-public-key-path (No default value)
get-server-public-key FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
tls-ciphersuites (No default value)
start-datetime (No default value)
start-position 4
stop-datetime (No default value)
stop-never FALSE
stop-never-slave-server-id -1
connection-server-id -1
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
open-files-limit 18432
verify-binlog-checksum FALSE
binlog-row-event-max-size 4294967040
skip-gtids FALSE
include-gtids (No default value)
exclude-gtids (No default value)
print-table-metadata FALSE
compress FALSE
compression-algorithms (No default value)
zstd-compression-level 3
require-row-format FALSE

四、其他命令记录(慎用)

-- 是否开启binlog
show variables like 'log_bin%';

-- binlog过期时间
show variables like '%expire_logs_days%';

-- 找到当前mysql记录的binlog文件
show master status;

-- 查看binlog日志
show binary logs;

-- binlog最大存储值
show variables like 'max_binlog_size';

-- 查看binlog，定位误操作的pos或者时间段
show binlog events in 'binlog.000001';

-- 清理3天前binlog日志(慎用)
PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

-- 删除指定日期以前的日志索引中binlog日志文件(慎用)
purge master logs before "2021-05-27 14:16:00";

-- 删除某个日志文件之前的所有日志文件(慎用)
purge binary logs to 'binlog.000003';

-- 重置所有的日志(慎用)
reset master;

-- 手动切换binlog日志
flush logs; 
-- 或者
flush binary logs;


-- 按开始和结束位置恢复
mysqlbinlog --start-position=351 --stop-position=2034 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\0917.sql

mysqlbinlog --start-position=2882 --stop-position=3187 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\05271.sql

-- 按时间段恢复
mysqlbinlog --start-datetime="2021-05-27 00:00:00" --stop-datetime="2021-05-27 10:01:59" "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\05276.sql

-- 按结束时间恢复
mysqlbinlog --stop-datetime="2021-05-27 10:08:59" "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\05277.sql

-- 解析日志
mysqlbinlog --start-position=33839226 --stop-position=33839672 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000002"

-- mysqlbinlog 可视化查看sql语句
mysqlbinlog --base64-output=decode-rows -v "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000002" >f:\\0607.sql

-- 指定库名--database,-d
mysqlbinlog --start-position=155 --stop-position=1086 --database mp "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000003">f:\\test5.sql