前言：数据库被误删过，想着学习
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> show variables like 'log_bin%'; +---------------------------------+---------------------------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------------------------+ | log_bin | ON | | log_bin_basename | C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog | | log_bin_index | C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+---------------------------------------------------------+这里显示的 ON 为开启, 如果显示 OFF 为敞开, 关上上面门路, 编辑
my.iniC:\ProgramData\MySQL\MySQL Server 8.0增加上面属性, 大略 127 行
# 不记录每条 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文件, 这里看到以后的日志是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看到 我在Pos2034End_los_pos2246 删除了user表 -
先来到 MySQL 下的 bin 目录, 这个目录有个
mysqlbinlog.exe, 前面咱们能够通过它来进行复原, 关上 cmd 进入上面目录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 --helpmysqlbinlog Ver 8.0.19 for Win64 on x86_64 (MySQL Community Server - GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 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
五、总结
数据的重要性，不必多说。在数据库中的一条数据，对应着事实中的业务，数据将虚拟世界和事实工夫相连接，对后续的业务产生重大影响。
如果是明天中午误删的表，拿到做昨天备份的数据库，在拿到明天的 binlog 就能够复原回来, 缩小损失。由此可见 定期备份数据库的重要性，如果备份的周期短，复原的工夫也快，数据失落也能及时找回，不至于删库跑路。