MySQL数据库备份&恢复（备份恢复）【通过binlog进行增量恢复】

素材

#创建库
create database it DEFAULT CHARACTER SET utf8;

/*=======================================================*/
/*创建学生表：Student（Sno,Sname,Ssex,Ssage,Ssdept）
-------------------(学号-主键，姓名，性别，年龄，所在系)*/
/*=======================================================*/
CREATE TABLE `Student` (
  `Sno` int(10) NOT NULL COMMENT '学号',  `Sname` varchar(16) NOT NULL COMMENT '姓名',
  `Ssex` char(2) NOT NULL COMMENT '性别',  `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
  `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别',  PRIMARY KEY (`Sno`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#表中插入数据
INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');

#模拟半夜零点手工全备
mysqldump -uroot -pMySql@123  -F -B it --single-transaction > /backup/mysql_full.sql
#注：备份文件名以及路径自定义

#备份后继续插入数据：
INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');

#模拟用户破坏数据：
drop database it;

恢复过程

1. 检查凌晨完全备份

[root@localhost ~]# ll /backup/mysql_full.sql 
-rw-r--r-- 1 root root 2328 May  1 11:25 /backup/mysql_full.sql

2. 检查完全备份后的所有binlog

[root@localhost ~]# ll /var/lib/mysql/mysql-bin.*
-rw-r----- 1 mysql mysql  177 May  1 11:09 /var/lib/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql  177 May  1 11:09 /var/lib/mysql/mysql-bin.000002
-rw-r----- 1 mysql mysql 1392 May  1 11:24 /var/lib/mysql/mysql-bin.000003
-rw-r----- 1 mysql mysql  341 May  1 11:24 /var/lib/mysql/mysql-bin.000004
-rw-r----- 1 mysql mysql  341 May  1 11:25 /var/lib/mysql/mysql-bin.000005
-rw-r----- 1 mysql mysql  674 May  1 11:27 /var/lib/mysql/mysql-bin.000006
-rw-r----- 1 mysql mysql  224 May  1 11:27 /var/lib/mysql/mysql-bin.index

3. 立即刷新并备份出binlog

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-