【MySQL 学习】MySQL Binlog 详细使用教程与数据备份、恢复

【MySQL 学习】MySQL 开启 Binlog 并实现每天备份数据库

1、binlog 概述

二进制日志记录对数据发生或潜在发生更改的 SQL 语句，并以二进制的形式保存在磁盘中；

MySQL 的作用类似于 Oracle 的归档日志，可以用来查看数据库的变更历史（具体的时间点所有的 SQL 操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、Mysql 的复制（主主数据库的复制、主从数据库的复制）

2、开启 binlog

检查 binlog 状态

show variables like 'log_%';

OFF 为关，ON 为开

在/etc/my.cnf 文件中增加 binlog 配置，开启 binlog

# 开启binglog
server_id=2
log_bin=mysql-bin
binlog_format=ROW
expire_logs_days=30

保存之后重启 mysql

systemctl restart mysqld

再次执行命令查看 binlog 状态，bog_bin_basename 是 binlog 保存文件的目录

进入目录查看 binlog 有哪些文件

cd /usr/local/mysql/mysqldb

在这个目录下生成的 mysql-bin.000001 格式的目录，都是备份文件

查看当前 binlog 日志

show master status;

查看 binlog 日志文件明细

show binlog events in 'mysql-bin.000002';

3、模拟删库跑路

首先删除数据库，一般都不会有什么删除表之类的，都是直接删除所有数据库，然后再创建一个空的数据库，里面一张表，表里面有个一个字段，写着一句话：您的数据库已经被删除，若想恢复数据，请支付 0.00003 个比特币到 xxxxxxxx 支付地址！

OK，我已经直接把业务数据库删了，使用命令查看 mysqlbinlog 当前的备份文件是哪个

show master status;

查看文件内容，最后的提交日志

show binlog events in 'mysql-bin.000002';

通过分析，造成数据库破坏的 pos 点区间是介于 59556-59813 之间（这是按照日志区间的 pos 节点算的）。所以只要恢复到 59525 就可以了，后面的 59556 之后的都是删除数据库的操作

恢复命令的语法格式

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

常用参数选项解释

--start-position=387 起始pos点
--stop-position=1393 结束pos点
--start-datetime="2022-03-15 10:43:53" 起始时间点
--stop-datetime="2022-03-15 10:44:35" 结束时间点
--database=my_test 指定只恢复my_test数据库(一台主机上往往有多个数据库，只限本地log日志)

我们只需要从创建表节点 59525，恢复到 drop 之前就好了，完整的命令如下

我的执行报错了，看下面的报错提示
sudo mysqlbinlog mysql-bin.000002 --start-position 59525 | sudo mysql -uroot -p

• 报错：sudo: mysqlbinlog：找不到命令
  解决办法：在/usr/local/mysql/bin 目录下执行，或者执行下面的命令把 mysqlbinlog 添加到可执行

 ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin

• 报错：mysqlbinlog: [ERROR] unknown variable ‘default-character-set=utf8’
  一是在 MySQL 的配置/etc/my.cnf 中将 default-character-set=utf8 修改为 character-set-server = utf8，但是需要重启 MySQL
  二是用 mysqlbinlog --no-defaults mysql-bin.000002 命令打开

接下来修改命令，再次执行

sudo mysqlbinlog --no-defaults mysql-bin.000002 --start-position=60011 --stop-position=149887 | sudo mysql -uroot -p

OK，到此数据库删除恢复成功！

微信公众号