MySql通过binlog恢复数据
binlog恢复误删数据✨
TIP
MySQL 中的日志比较重要的有 binlog（归档日志）、redo log（重做日志）以及 undo log
1、详细概述🎇
binlog 是 MySQL Server 层的日志，而不是存储引擎自带的日志，它记录了所有的 DDL 和 DML(不包含数据查询语句)语句，而且是以事件形式记录，还包含语句所执行的消耗的时间等
需要注意的是：
1、binlog 是一种逻辑日志，他里边所记录的是一条 SQL 语句的原始逻辑，例如给某一个字段 +1，注意这个区别于 redo log 的物理日志（在某个数据页上做了什么修改）
2、binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的
3、一般来说，我们在配置 binlog 的时候，可以指定 binlog 文件的有效期，这样在到期后，日志文件会自动删除，这样避免占用较多存储空间
根据 MySQL 官方文档的介绍，开启 binlog 之后，大概会有 1% 的性能损耗，不过这还是可以接受的，一般来说，binlog 有两个重要的使用场景：
- MySQL 主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通过 binlog 来同步数据，进而实现主机和从机的数据同步。
- MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。
2、开启binlog🌋
-- 登录mysql查看binlog日志的状态，off为关闭状态
SHOW VARIABLES LIKE 'log_bin%';
开启 binlog : vim /etc/my.cnf🛻
# 开启，并且可以将mysql-bin改为其它的日志名
# log-bin=mysql-bin
log-bin=my-bin
# 超过200M将生产新的文件，最大和默认值是1GB
# 设置最大 100MB
max_binlog_size=104857600
# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 7
# 此参数配置binlog的日志格式，默认为mixed。
binlog_format=mixed
# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
#binlog-do-db=my_db
# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
#binlog-ignore-db=my_no_db
# 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=0
# 添加id号，如果做主从，就不能一样
server-id=1
配置完成后，重启 mysql ，重启之后，再次执行 show variables like 'log_bin%'; 即可看到 binlog 已经开启了。
mysql> show variables like 'log_bin%';
+---------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | G:\java\sql\MySQL Server 8.0\Data\LAPTOP-RDCIFBH0-bin |
| log_bin_index | G:\java\sql\MySQL Server 8.0\Data\LAPTOP-RDCIFBH0-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
- log_bin_basename：这个是将来产生的 binlog 日志文件的名称前缀，换句话说，根据大家目前所看到的配置，将来产生的 binlog 日志文件名为 my-bin.xxx，这个文件中将会用来记录所有的 DDL 和 DML 语句事件。
- log_bin_index：这个是 binlog 的索引文件，保存了所有 binlog 的目录，因为 binlog 可能会有多个。
3. 常见 binlog 操作👍
-- 查看所有binlog日志列表
show master logs;
-- 查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
show master status;
-- 刷新 binlog:正常来说，一个 binlog 写满之后，会自动切换到下一个 binlog 开始写，不过我们也可以执行一个 flush logs 命令来手动刷新 binlog;
-- 手动刷新 binlog 之后，就会产生一个新的 binlog 日志文件，接下来所有的 binlog 日志都将记录到新的文件中。
flush logs
-- 重置 binlog
reset master
-- 查看 binlog
-- 为了查看 binlog，MySQL 为我们提供了两个官方工具，我们一个一个来看，首先是 mysqlbinlog 命令
mysqlbinlog /var/lib/mysql/my-bin.00001
-- 方法2：这个表示以事件的方式来查看 binlog
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
-- log_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件。
-- pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看。
-- offset：这是是偏移量，不指定默认就是 0。
-- row_count：查看多少行记录，不指定就是查看所有。
4、使用binlog恢复数据🧐
注意点：
恢复的时候要配合全备份，先进行全备份，在用 mysqldump 全备时添加-F刷新binlog，这时候mysqldump备份的是最新的binlog日志之前的内容了。
先进行全备份恢复，再将最新的binlog文件用mysqlbinlog进行查看，grep或者其他方式过滤，找到有问题的sql语句，记录下当时的pos点或者时间。只恢复出问题之前得时间点即可。
mysqldump -uroot -p --flush-logs --lock-tables -B mydb>/root/my.bak.sql
-- --flush-logs：这个表示在导出之前先刷新 binlog，刷新 binlog 之后将会产生新的 binlog 文件，后续的操作都存在新的 binlog 中。
-- --lock-tables：这个表示开始导出前，锁定所有表。需要注意的是当导出多个数据库时，lock-tables 分别为每个数据库锁定表
-- 因此这个选项不能保证导出文件中的表在数据库之间的逻辑一致性，不同数据库表的导出状态可以完全不同。
-- -B：这个表示指定导出的数据库名称，如果使用 `--all-databases` 或者 `-A` 代替 `-B` 表示导出所有的数据库。
通过binlog还原数据库
mysqlbinlog /var/lib/mysql/my-bin.000002 --stop-position=764 --database=mydb | mysql -uroot -p
常用参数选项解释：
--start-position= 875 起始pos点
--stop-position= 954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=mydb 指定只恢复mydb数据库(一台主机上往往有多个数据库，只限本地log日志)