记录一次MYSQL 的删库不跑路【学习】

哈噻gay
110 阅读5分钟

小插曲:

  7月的广州是真的难受，28年的西北方汉子一下踏入这个地方还真真真有点受不了，再加上接连面试不顺，心态有点小炸裂，but为了爱情...哈哈哈 有点扯题了

  闲来没事，来掘金取取经，刚好刷到mysql binlog恢复数据.ps:自己曾经确实删过库,且不止一次，哈哈哈... 咳咳 咱之前都是独立开发 所以就没...

前言

  本片文章主要内容转载原创,本人只是作记录学习，加深印象（怪自己太笨，记不住，那就手打一遍）。

1. binlog

  • binlog 是一种逻辑日志，他里边所记录的是一条 SQL 语句的原始逻辑，例如给某一个字段 +1，注意这个区别于 redo log 的物理日志（在某个数据页上做了什么修改）。

  • binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。

  • 一般来说，我们在配置 binlog 的时候，可以指定 binlog 文件的有效期，这样在到期后，日志文件会自动删除，这样避免占用较多存储空间

根据 MySQL 官方文档的介绍，开启 binlog 之后，大概会有 1% 的性能损耗，不过这还是可以接受的，一般来说，binlog 有两个重要的使用场景：

  • MySQL 主从复制时：在主机上开启 binlog，主机将 binlog 同步给从机，从机通过 binlog 来同步数据，进而实现主机和从机的数据同步。
  • MySQL 数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。

2.开启binlog

我本地用的phpstudy集成环境，一键启动服务，然后直接cmdmysql安装目录bin目录

1658379432010.jpg

进入mysql 命令行执行以下命令： 查看 binlog 是否开启：

show variables like 'log_bin%';

1658380183392.jpg 这个 ON 就表示 binlog 是一个开启状态，如果没有开启，就去修改配置my.ini或mysqld.cnf   配置如下：

# 这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
log-bin=zhijun_logbin

# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size=104857600

# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 7

# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
#binlog-do-db=javaboy_db

# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
#binlog-ignore-db=javaboy_no_db

# 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=0

# 为当前服务取一个唯一的 id（MySQL5.7 之后需要配置）
server-id=1

然后重启mysql服务,配置正确后，重新执行上面的show variables like 'log_bin%';,咦，出来啦 哈哈哈

常见binlog操作

  • 查看所有 binlog 日志
 show master logs;

image.png 可以看到我们这有四个日志文件

  • 查看master状态
show master status;

image.png

当下咱们只关注File 和 position 的涵义

  • 刷新binlog

正常来说，一个 binlog 写满之后，会自动切换到下一个 binlog 开始写，不过我们也可以执行一个 flush logs 命令来手动刷新 binlog，手动刷新 binlog 之后，就会产生一个新的 binlog 日志文件，接下来所有的 binlog 日志都将记录到新的文件中。如下：

image.png

  • 查看 binlog
mysqlbinlog --no-defaults [binlog文件]

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

这个表示以事件的方式来查看 binlog，这里涉及到几个参数：

  • log_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件。
  • pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看。
  • offset：这是是偏移量，不指定默认就是 0。
  • row_count：查看多少行记录，不指定就是查看所有。

示例：

show binlog events in 'zhijun_logbin.000004';

image.png

3.实战演练

  • 创建数据库zhijun 创建表test

image.png

  • 对当前数据库做备份导出
mysqldump -u root -p --flush-logs --lock-tables -B zhijun > D:\phpstudy_pro\Extensions\MySQL5.7.26\test2.sql

  • --flush-logs：这个表示在导出之前先刷新 binlog，刷新 binlog 之后将会产生新的 binlog 文件，后续的操作都存在新的 binlog 中。

  • --lock-tables：这个表示开始导出前，锁定所有表。需要注意的是当导出多个数据库时，--lock-tables 分别为每个数据库锁定表，因此这个选项不能保证导出文件中的表在数据库之间的逻辑一致性，不同数据库表的导出状态可以完全不同。

  • -B：这个表示指定导出的数据库名称，如果使用 --all-databases 或者 -A 代替 -B 表示导出所有的数据库。 image.png

  • 向test表 里面添加新数据

image.png

  • 删除zhijun数据库

image.png

  • 根据备份的test2.sql 恢复数据
mysql -uroot -p < D:\phpstudy_pro\Extensions\MySQL5.7.26\test2.sql

image.png

image.png

  • 从binlog 日志文件提取未恢复的数据

前面备份数据库备份 加了一个 --flush-logs 强制记录到新的binlog文件

image.png

show binlog events in 'zhijun_logbin.000006';

image.png

image.png

mysqlbinlog /var/lib/mysql/javaboy_logbin.000002 --stop-position=6862 --database=javaboy | mysql -uroot -p

4. 学习小记

这个只是简单的示例，实际数据比这个复杂许多，多多学习 加油！奥里给

avatar
文章
阅读
粉丝