Mysql配置binlog并实现数据库备份恢复

一、前提

前提是有一次完整的备份文件，先通过还原工具把备份文件还原回去，然后再通过bin-log日志恢复上次备份之后发生的数据。

二、准备环境

sql脚本

-- ----------------------------
-- database
-- ----------------------------
drop database if exists `db_test`;
create database `db_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
-- ----------------------------
-- Table structure for t_test
-- ----------------------------
DROP TABLE IF EXISTS `t_test`;
CREATE TABLE `t_test`  (
  `ID_` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `NAME_` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_test
-- ----------------------------
INSERT INTO `t_test` VALUES ('1', '张三');
INSERT INTO `t_test` VALUES ('2', '李四');

SET FOREIGN_KEY_CHECKS = 1;

三、开启binlog

1、 查看状态

show variables like '%log_bin%';

说明没有开启

2、开启

修改MySQL配置（my.cnf）
vi /etc/my.cnf

配置如下：

# log-bin日志文件的名字为mysql-heima-bin，以及其存储路径
log-bin=/var/lib/mysql/mysql-heima-bin
# 指定需要记录的库
binlog-do-db=heima
# 指定需要记录的库，如果多个库换行执行
binlog-do-db=db_test

3、重启mysql

docker restart mysql

4、验证是否开启binlog

show variables like ‘log_bin’; 

show binary logs

show master status;

三、备份数据

参考：docker定时备份mysql-定时备份docker中mysql的数据

五、插入新的数据

1、原备份中数据

2、插入新数据

六、测试删库

drop database db_test;

七、还原备份

1、创建库

参考之前建库语句

2、还原备份文件

mysql -uroot -h127.0.0.1 -p --default-character-set=utf8 db_test</usr/local/mysql/db_20220624.sql
# docker中执行
## 先把备份文件拷贝到容器
docker cp /usr/local/mysql/backup/db_test_backup-202206241450.sql mysql:/usr/local/
## 进入容器
docker exec -it mysql bash
## 执行
mysql -hlocalhost -P3306 -uroot -p123 --default-character-set=utf8 db_test< /usr/local/db_test_backup-202206241423.sql

3、查看结果

发现只有两条，少了第三条

八、执行恢复

1、查看binlog日志

查看binlog日志，文件通过show master status;获取
show binlog events in 'mysql-heima-bin.000002'

我们已经确定了529点的操作导致了删除数据库，所以我们要开始恢复的点是449至以前。接下来我们就可以从binlog日志恢复数据了。

2、恢复语法格式：

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

常用选项：
–start-position=55 起始pos点
–stop-position=555 结束pos点
–start-datetime=“2019-11-29 13:00:00” 起始时间点
–stop-datetime=“2019-11-29 17:00:00” 结束时间点
–database=douyin 指定只恢复douyin数据库(一台主机上往往有多个数据库，只限本地log日志)

不常用选项：
-u --user=name Connect to the remote server as username.连接到远程主机的用户名
-p --password[=name] Password to connect to remote server.连接到远程主机的密码
-h --host=name Get the binlog from server.从远程主机上获取binlog日志
–read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志

3、执行恢复

先进入mysql的data目录

# docker在如下目录。映射宿主机的/usr/local/mysql/data
cd /var/lib/mysql
mysqlbinlog --stop-position=449 --skip-gtids=true --database=db_test mysql-heima-bin.000002 | mysql -uroot -p123

如果我们是要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例，那么就要使用–skip-gtids=true参数。如果不带该参数的话，是无法恢复成功的。因为包含的 GTID 已经在源数据库执行过了,根据 GTID 特性,一个 GTID 信息在一个数据库只能执行一次,所以不会恢复成功。

此时发现成功了