mysqldump+-binlog增量备份

注意：二进制文件删除必须使用help purge

不可用rm -f   会崩

一、概念

增量备份：仅备份上次备份以后变化的数据

差异备份：仅备份上次完全备份以后变化的数据

完全备份：顾名思义，将数据完全备份

其中，在进行增量备份或差异备份前，必须至少有一次完全备份

二、素材准备

备份前首先我们要准备一份文件用来实验 

1.先登陆MySQL创建school数据库来存放数据

不会登陆的参考我上一篇笔记：MySQL数据库离线下载

mysql> create database school;
Query OK, 1 row affected (0.01 sec)mysql> use school
Database changed
mysql> 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`)-> ) ;
Query OK, 0 rows affected, 2 warnings (0.09 sec)mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

回到虚拟机

2.完全备份

注意：虚拟机与mysql的切换，我这里xshell同时开了两个窗口

完全备份：
[root@openeuler-1 ~]# mkdir /mysqlbak
[root@openeuler-1 ~]# mysqldump --opt -B school > /mysqlbak/school.sql插入数据：
INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');查询是否插入成功：
mysql> select * from Student;
+-----+----------+------+------+-----------------+
| Sno | Sname    | Ssex | Sage | Sdept           |
+-----+----------+------+------+-----------------+
|   1 | 陆亚     | 男   |   24 | 计算机网络      |
|   2 | tom      | 男   |   26 | 英语            |
|   3 | 张阳     | 男   |   21 | 物流管理        |
|   4 | alex     | 女   |   22 | 电子商务        |
|   5 | xumubin  | 男   |   29 | 中文专业        |
|   6 | wangzhao | 男   |   21 | 导弹专业        |
+-----+----------+------+------+-----------------+
6 rows in set (0.00 sec)

这里的school就是我们上面创建的数据库

3.开启binlog（8.0默认开启）

虚拟机里是否有binlog.000001（备份几次就有几个）

MySQL  往下翻是否有    log_bin    on  

有，说明是开启状态

4.模拟数据损坏

模拟数据损坏：
mysql> drop database school;
Query OK, 1 row affected (0.04 sec)刷新日志：
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)保存日志：
（拷贝到别的地方防止二次破坏）
mysql>  show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      2135 | No        |
| binlog.000002 |       157 | No        |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)

 选择size最多的复制查看即 binlog.000001

binlog2是一个新创建的日志文件，尚未记录任何数据库操作

虚拟机命令行复制日志：
cp /var/lib/mysql/binlog.000001 /mysqlbak/进入保护现场：
cd /mysqlbak/查看二进制日志:
mysqlbinlog binlog.000001 --base64-output=DECODE-ROWS -vv

#注意5.7版本后，insert语句已经加密，默认看不到，查看时在文件后面加上选项 --base64-output=DECODE-ROWS -vv 

我们可以看到数据清晰明了

以下是我的完全备份数据，当作参照组，可先略过看第三大点：恢复数据步骤，若找不到再回来对应找

[root@openeuler-1 mysqlbak]# mysqlbinlog binlog.000001 --base64-output=DECODE-ROWS -vv
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#250123 23:53:21 server id 1  end_log_pos 126 CRC32 0xd460830a 	Start: binlog v 4, server v 8.0.36 created 250123 23:53:21 at startup
ROLLBACK/*!*/;
# at 126
#250123 23:53:21 server id 1  end_log_pos 157 CRC32 0xaffba851 	Previous-GTIDs
# [empty]
# at 157
#250124  0:08:24 server id 1  end_log_pos 236 CRC32 0x102a2ba1 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1737648504801759	immediate_commit_timestamp=1737648504801759	transaction_length=319
# original_commit_timestamp=1737648504801759 (2025-01-24 00:08:24.801759 CST)
# immediate_commit_timestamp=1737648504801759 (2025-01-24 00:08:24.801759 CST)
/*!80001 SET @@session.original_commit_timestamp=1737648504801759*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 236
#250124  0:08:24 server id 1  end_log_pos 476 CRC32 0xe66d9908 	Query	thread_id=11	exec_time=0	error_code=0	Xid = 3
SET TIMESTAMP=1737648504.798994/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$3:PpxqXx#39zahoOesT2QGmWZQ5FU/kLsxDvsMoYG0nlD3FCBG2jOe.'
/*!*/;
# at 476
#250124  2:39:55 server id 1  end_log_pos 553 CRC32 0x73f1030b 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1737657595842327	immediate_commit_timestamp=1737657595842327	transaction_length=191
# original_commit_timestamp=1737657595842327 (2025-01-24 02:39:55.842327 CST)
# immediate_commit_timestamp=1737657595842327 (2025-01-24 02:39:55.842327 CST)
/*!80001 SET @@session.original_commit_timestamp=1737657595842327*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 553
#250124  2:39:55 server id 1  end_log_pos 667 CRC32 0x072f8920 	Query	thread_id=16	exec_time=0	error_code=0	Xid = 10
SET TIMESTAMP=1737657595/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database school
/*!*/;
# at 667
#250124  2:40:48 server id 1  end_log_pos 746 CRC32 0xb6cb6a27 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no	original_committed_timestamp=1737657648297547	immediate_commit_timestamp=1737657648297547	transaction_length=478
# original_commit_timestamp=1737657648297547 (2025-01-24 02:40:48.297547 CST)
# immediate_commit_timestamp=1737657648297547 (2025-01-24 02:40:48.297547 CST)
/*!80001 SET @@session.original_commit_timestamp=1737657648297547*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 746
#250124  2:40:48 server id 1  end_log_pos 1145 CRC32 0x8367a696  Query	thread_id=16	exec_time=0	error_code=0	Xid = 15
use `school`/*!*/;
SET TIMESTAMP=1737657648/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
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`)
)
/*!*/;
# at 1145
#250124  2:41:24 server id 1  end_log_pos 1224 CRC32 0x5bd7634a  Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=yes	original_committed_timestamp=1737657684341385	immediate_commit_timestamp=1737657684341385	transaction_length=404
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1737657684341385 (2025-01-24 02:41:24.341385 CST)
# immediate_commit_timestamp=1737657684341385 (2025-01-24 02:41:24.341385 CST)
/*!80001 SET @@session.original_commit_timestamp=1737657684341385*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1224
#250124  2:41:24 server id 1  end_log_pos 1301 CRC32 0x36afa721  Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1737657684/*!*/;
BEGIN
/*!*/;
# at 1301
#250124  2:41:24 server id 1  end_log_pos 1371 CRC32 0xe7255827  Table_map: `school`.`Student` mapped to number 112
# has_generated_invisible_primary_key=0
# at 1371
#250124  2:41:24 server id 1  end_log_pos 1518 CRC32 0x5d0c86b0  Write_rows: table id 112 flags: STMT_END_F
### INSERT INTO `school`.`Student`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='陆亚' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='男' /* STRING(8) meta=65032 nullable=0 is_null=0 */
###   @4=24 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5='计算机网络' /* VARSTRING(64) meta=64 nullable=1 is_null=0 */
### INSERT INTO `school`.`Student`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='男' /* STRING(8) meta=65032 nullable=0 is_null=0 */
###   @4=26 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5='英语' /* VARSTRING(64) meta=64 nullable=1 is_null=0 */
### INSERT INTO `school`.`Student`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='张阳' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='男' /* STRING(8) meta=65032 nullable=0 is_null=0 */
###   @4=21 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5='物流管理' /* VARSTRING(64) meta=64 nullable=1 is_null=0 */
### INSERT INTO `school`.`Student`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='alex' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='女' /* STRING(8) meta=65032 nullable=0 is_null=0 */
###   @4=22 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5='电子商务' /* VARSTRING(64) meta=64 nullable=1 is_null=0 */
# at 1518
#250124  2:41:24 server id 1  end_log_pos 1549 CRC32 0x64e26b2d  Xid = 16
COMMIT/*!*/;
# at 1549
#250124  2:42:37 server id 1  end_log_pos 1628 CRC32 0x7cd8b53b  Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=yes	original_committed_timestamp=1737657757809011	immediate_commit_timestamp=1737657757809011	transaction_length=355
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1737657757809011 (2025-01-24 02:42:37.809011 CST)
# immediate_commit_timestamp=1737657757809011 (2025-01-24 02:42:37.809011 CST)
/*!80001 SET @@session.original_commit_timestamp=1737657757809011*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1628
#250124  2:42:37 server id 1  end_log_pos 1705 CRC32 0x5c69dd71  Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1737657757/*!*/;
BEGIN
/*!*/;
# at 1705
#250124  2:42:37 server id 1  end_log_pos 1775 CRC32 0x195db6e0  Table_map: `school`.`Student` mapped to number 112
# has_generated_invisible_primary_key=0
# at 1775
#250124  2:42:37 server id 1  end_log_pos 1873 CRC32 0x82c37439  Write_rows: table id 112 flags: STMT_END_F
### INSERT INTO `school`.`Student`
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='xumubin' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='男' /* STRING(8) meta=65032 nullable=0 is_null=0 */
###   @4=29 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5='中文专业' /* VARSTRING(64) meta=64 nullable=1 is_null=0 */
### INSERT INTO `school`.`Student`
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='wangzhao' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */
###   @3='男' /* STRING(8) meta=65032 nullable=0 is_null=0 */
###   @4=21 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @5='导弹专业' /* VARSTRING(64) meta=64 nullable=1 is_null=0 */
# at 1873
#250124  2:42:37 server id 1  end_log_pos 1904 CRC32 0x8d0c2555  Xid = 48
COMMIT/*!*/;
# at 1904
#250124  2:44:47 server id 1  end_log_pos 1981 CRC32 0x7cf7fd11  Anonymous_GTID	last_committed=5	sequence_number=6	rbr_only=no	original_committed_timestamp=1737657887499936	immediate_commit_timestamp=1737657887499936	transaction_length=187
# original_commit_timestamp=1737657887499936 (2025-01-24 02:44:47.499936 CST)
# immediate_commit_timestamp=1737657887499936 (2025-01-24 02:44:47.499936 CST)
/*!80001 SET @@session.original_commit_timestamp=1737657887499936*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1981
#250124  2:44:47 server id 1  end_log_pos 2091 CRC32 0x1908e2ec  Query	thread_id=16	exec_time=0	error_code=0	Xid = 50
SET TIMESTAMP=1737657887/*!*/;
drop database school
/*!*/;
# at 2091
#250124  2:44:55 server id 1  end_log_pos 2135 CRC32 0x1e0f22c1  Rotate to binlog.000002  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@openeuler-1 mysqlbak]# 

三、恢复数据步骤

1.还原思路

前面我们已经将文件完全备份了，但是后面新增的数据没有备份，怎样从日志中找到新增的数据进行增量备份并还原呢？

首先，由于上面我们先插入4个数据 （如左侧图）后进行的完全备份，所以这时我们直接还原完全备份只能得到四个数据，那么我们就需要在日志中找到新增的数据（5、6如右图）在哪

增量恢复有两种方法：基于时间点恢复  基于位置点恢复

 找到时间点或者地点后，将5、6数据对应的时间/地点导出放至time文件，就可以还原增量备份了

2.具体操作 

按照思路，先恢复完全备份

还原完全备份
mysql> source /mysqlbak/school.sql
mysql> select * from school.Student;
+-----+--------+------+------+-----------------+
| Sno | Sname  | Ssex | Sage | Sdept           |
+-----+--------+------+------+-----------------+
|   1 | 陆亚   | 男   |   24 | 计算机网络      |
|   2 | tom    | 男   |   26 | 英语            |
|   3 | 张阳   | 男   |   21 | 物流管理        |
|   4 | alex   | 女   |   22 | 电子商务        |
+-----+--------+------+------+-----------------+
4 rows in set (0.01 sec)

3.增量备份——时间点

导出时间点

找到开始和结束的时间

开始：5、6条数据前  250124  2:42:37

结束：找到第6条commit之后的时间，导出来 250124  2:44:47

 

根据时间导出数据到time1.sql文件：
[root@openeuler-1 mysqlbak]# mysqlbinlog binlog.000001 --start-datetime="2025-01-24 2:42:37" --stop-datetime="2025-01-24 2:44:47" -r time1.sql查看是否有数据：
[root@openeuler-1 mysqlbak]# vim time1.sql 将数据传到数据库：
[root@openeuler-1 mysqlbak]# mysql < school.sql 

 还原增量备份

还原增量备份
mysql> source /mysqlbak/time1.sql
mysql> select * from school.Student;
+-----+----------+------+------+-----------------+
| Sno | Sname    | Ssex | Sage | Sdept           |
+-----+----------+------+------+-----------------+
|   1 | 陆亚     | 男   |   24 | 计算机网络      |
|   2 | tom      | 男   |   26 | 英语            |
|   3 | 张阳     | 男   |   21 | 物流管理        |
|   4 | alex     | 女   |   22 | 电子商务        |
|   5 | xumubin  | 男   |   29 | 中文专业        |
|   6 | wangzhao | 男   |   21 | 导弹专业        |
+-----+----------+------+------+-----------------+
6 rows in set (0.00 sec)

4.增量备份——位置点

和前面步骤差不多，先把数据库删除

mysql> drop database school;
Query OK, 1 row affected (0.04 sec)还原完全备份
mysql> source /mysqlbak/school.sql
mysql> select * from school.Student;
+-----+--------+------+------+-----------------+
| Sno | Sname  | Ssex | Sage | Sdept           |
+-----+--------+------+------+-----------------+
|   1 | 陆亚   | 男   |   24 | 计算机网络      |
|   2 | tom    | 男   |   26 | 英语            |
|   3 | 张阳   | 男   |   21 | 物流管理        |
|   4 | alex   | 女   |   22 | 电子商务        |
+-----+--------+------+------+-----------------+
4 rows in set (0.01 sec)

开始位置

 

结束位置点 

 

位置点恢复
[root@localhost mysqlbak]# mysqlbinlog binlog.000001 --start-position=1775 --stop-position=1904 -r pos1.sql将数据传到数据库：
[root@openeuler-1 mysqlbak]# mysql < school.sql增量还原恢复
mysql> source /mysqlbak/pos1.sql
mysql> select * from school.Student;

ps：如果还原数据缺失，可能是结束位置点有问题，把位置点再往下挪一个