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

• 一、前提
• 二、准备环境
• • sql脚本
• 三、开启binlog
• • 1、 查看状态
  • 2、开启
  • 3、重启mysql
  • 4、验证是否开启binlog
• 三、备份数据
• 五、插入新的数据
• • 1、原备份中数据
  • 2、插入新数据
• 六、测试删库
• 七、还原备份
• • 1、创建库
  • 2、还原备份文件
  • 3、查看结果
• 八、执行恢复
• • 1、查看binlog日志
  • 2、恢复语法格式：
  • 3、执行恢复

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

-- ----------------------------
-- 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;

show variables like '%log_bin%';

说明没有开启

修改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

docker restart mysql

show variables like ‘log_bin’; 

show binary logs

show master status;

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

drop database db_test;

参考之前建库语句

mysql -uroot -h127.0.0.1 -p --default-character-set=utf8 db_test 
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 信息在一个数据库只能执行一次,所以不会恢复成功。
 
 
此时发现成功了