在MySQL9.2.0版本的mysql shell中，可以对已备份的文件进行增量同步。比如，在通过mysql shell一次性从实例A备份数据后，将数据再导入到另一个实例B中，但实例A的增量数据是不会同步的。如果过了一段时间后，再想同步A实例的数据，在没有这个方法前，只能全量再同步一次。如果数据量比较小时，一般没有问题，但如果数据量非常大，这种操作就非常消耗时间。
而接下来要讲的这个功能，就能很好的解决这个备份数据增量同步的问题。
但如果只想导出自上次导出以来发生的数据和架构变更，我们该怎么办呢？在本文中，我们将展示如何使用 MySQL Shell 和 util.dump_binlogs() 来实现这一目标。
版本清单：
MySQL: 9.2.0
MySQL shell 9.2.0
一、部署两个不同端口的沙盒实例：
1.启动mysqlsh
[root@centos7 ~]# mysqlsh --py
MySQL Shell 9.2.0
2.创建端口号为3300的沙盒实例：
[root@centos7 ~]# mysqlsh --py
MySQL Shell 9.2.0
Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL Py > dba.deploy_sandbox_instance(3300);
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3300
Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.
Please enter a MySQL root password for the new instance: ***********
Deploying new MySQL instance...
Instance localhost:3300 successfully deployed and started.
Use shell.connect('root@localhost:3300') to connect to the instance.
3.连接已创建的实例：
MySQL Py > \connect root@localhost:3300
Creating a session to 'root@localhost:3300'
Please provide the password for 'root@localhost:3300': ***********
Save password for 'root@localhost:3300'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 12
Server version: 9.2.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3300 ssl Py > \s
MySQL Shell version 9.2.0
Connection Id: 12
Current schema:
Current user: root@localhost
SSL: Cipher in use: ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2
Using delimiter: ;
Server version: 9.2.0 MySQL Community Server - GPL
Protocol version: Classic 10
Client library: 9.2.0
Connection: localhost via TCP/IP
TCP port: 3300
Server characterset: utf8mb4
Schema characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
Result characterset: utf8mb4
Compression: Disabled
Uptime: 29.0000 sec
Threads: 2 Questions: 34 Slow queries: 0 Opens: 197 Flush tables: 3 Open tables: 113 Queries per second avg: 1.172
4.查看mysql-shell创建的相关目录和文件：
[root@centos7 3300]# pwd
/root/mysql-sandboxes/3300
[root@centos7 3300]# ls
3300.pid bin lib64 my.cnf mysql-files sandboxdata start.sh stop.sh
4.实例创建完成后，通过mysql-shell登录到新创建的实例中
[root@centos7 ~]# mysqlsh --py
MySQL Shell 9.2.0
Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL Py > \connect root@localhost:3300
Creating a session to 'root@localhost:3300'
Please provide the password for 'root@localhost:3300': ***********
Save password for 'root@localhost:3300'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 13
Server version: 9.2.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
6.查看数据库版本：
MySQL localhost:3300 ssl Py > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
MySQL localhost:3300 ssl SQL > select version();
+-----------+
| version() |
+-----------+
| 9.2.0 |
+-----------+
1 row in set (0.0006 sec)
7.创建另一个端口号为3301的沙盒实例：
[root@centos7 ~]# mysqlsh --py
MySQL Shell 9.2.0
Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL Py > dba.deploy_sandbox_instance(3301);
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3301
Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.
Please enter a MySQL root password for the new instance: ***********
Deploying new MySQL instance...
Instance localhost:3301 successfully deployed and started.
Use shell.connect('root@localhost:3301') to connect to the instance.
MySQL Py > \connect root@localhost:3301
Creating a session to 'root@localhost:3301'
Please provide the password for 'root@localhost:3301': ***********
Save password for 'root@localhost:3301'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 12
Server version: 9.2.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
8.查看mysql-shell创建的相关目录和文件：
[root@centos7 3301]# pwd
/root/mysql-sandboxes/3301
[root@centos7 3301]# ls
3301.pid bin lib64 my.cnf mysql-files sandboxdata start.sh stop.sh
9.实例创建完成后，通过mysql-shell登录到新创建的实例中
MySQL Py > \connect root@localhost:3301
Creating a session to 'root@localhost:3301'
Please provide the password for 'root@localhost:3301': ***********
Save password for 'root@localhost:3301'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 13
Server version: 9.2.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3301 ssl Py >
10.通过以上配置，就配置好了两个MySQL沙盒实例。
二、下面来验证MySQL9.2 Shell 进行增量导出功能：
1.在端口号为3300的实例上操作：
[root@centos7 ~]# mysqlsh --py
MySQL Shell 9.2.0
Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL Py > \connect root@localhost:3300
Creating a session to 'root@localhost:3300'
Please provide the password for 'root@localhost:3300': ***********
Save password for 'root@localhost:3300'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 14
Server version: 9.2.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3300 ssl Py > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
MySQL localhost:3300 ssl SQL >
2.创建库表数据：
MySQL localhost:3300 ssl SQL > create database demo;
Query OK, 1 row affected (0.0042 sec)
MySQL localhost:3300 ssl SQL > create table demo.progress( id int auto_increment primary key, name varchar(50) not null, score int not null);
Query OK, 0 rows affected (0.0209 sec)
MySQL localhost:3300 ssl SQL >
insert into demo.progress(name,score) values('Sullivan',99479),('Randall',99351),('Sanderson',58986);
Query OK, 3 rows affected (0.0174 sec)
Records: 3 Duplicates: 0 Warnings: 0
MySQL localhost:3300 ssl SQL > select * from demo.progress;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | Sullivan | 99479 |
| 2 | Randall | 99351 |
| 3 | Sanderson | 58986 |
+----+-----------+-------+
3 rows in set (0.0008 sec)
3.创建备份目录：
[root@centos7 ~]# mkdir -p /root/dumps/binlog_setup
4.在端口号为3300的实例上导出 上面已创建的库表和数据信息：
MySQL localhost:3300 ssl Py > util.dump_instance('/root/dumps/binlog_setup');
Acquiring global read lock
Global read lock acquired
Initializing - done
1 out of 5 schemas will be dumped and within them 1 table, 0 views.
2 out of 5 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
100% (3 rows / ~3 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 51 bytes
Compressed data size: 60 bytes
Compression ratio: 0.8
Rows written: 3
Bytes written: 60 bytes
Average uncompressed throughput: 51.00 B/s
Average compressed throughput: 60.00 B/s
5.登录到端口号为3301的数据库实例中，将3300已备份的数据导入到3301数据库中
[root@centos7 ~]# mysqlsh --py
MySQL Shell 9.2.0
Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL Py > \connect root@localhost:3301
Creating a session to 'root@localhost:3301'
Please provide the password for 'root@localhost:3301': ***********
Save password for 'root@localhost:3301'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 14
Server version: 9.2.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
6.切换为SQL模式，修改local_infile参数为ON:
MySQL localhost:3301 ssl Py > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:3301 ssl SQL > set global local_infile=on;
Query OK, 0 rows affected (0.0008 sec)
7.切换为PY模式，将3300已备份的数据导入到3301数据库中：
MySQL localhost:3301 ssl SQL > \py
Switching to Python mode...
MySQL localhost:3301 ssl Py > util.load_dump('/root/dumps/binlog_setup')
Loading DDL and Data from '/root/dumps/binlog_setup' using 4 threads.
Opening dump - done
Target is MySQL 9.2.0. Dump was produced from MySQL 9.2.0
Scanning metadata - done
Checking for pre-existing objects - done
Executing common preamble SQL - done
Executing DDL - done
Executing view DDL - done
Starting data load
100% (51 bytes / 51 bytes), 0.00 B/s (0.00 rows/s), 1 / 1 tables done
Building indexes - done
Executing common postamble SQL - done
1 chunks (3 rows, 51 bytes) for 1 tables in 1 schemas were loaded in 0 sec (avg throughput 51.00 B/s, 3.00 rows/s)
2 DDL files were executed in 0 sec.
Data load duration: 0 sec
Total duration: 0 sec
0 warnings were reported during the load.
8.切换为SQL模式，查看已导入的数据：
MySQL localhost:3301 ssl Py > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:3301 ssl SQL > show databases;
+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.0012 sec)
MySQL localhost:3301 ssl SQL > use demo;
Default schema set to `demo`.
MySQL localhost:3301 ssl demo SQL > show tables;
+----------------+
| Tables_in_demo |
+----------------+
| progress |
+----------------+
1 row in set (0.0017 sec)
MySQL localhost:3301 ssl demo SQL > select * from progress;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | Sullivan | 99479 |
| 2 | Randall | 99351 |
| 3 | Sanderson | 58986 |
+----+-----------+-------+
3 rows in set (0.0007 sec)
9.现在3300实例和3301实例的数据是一样的。
10.登录到3300实例中，做些表结构变更的操作：
[root@centos7 ~]# mysqlsh --py
MySQL Shell 9.2.0
Copyright (c) 2016, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL Py > \connect root@localhost:3300
Creating a session to 'root@localhost:3300'
Please provide the password for 'root@localhost:3300': ***********
Save password for 'root@localhost:3300'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 21
Server version: 9.2.0 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:3300 ssl Py > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:3300 ssl SQL > use demo;
Default schema set to `demo`.
Fetching global names, object names from `demo` for auto-completion... Press ^C to stop.
11.插入几条数据：
MySQL localhost:3300 ssl demo SQL >
insert into demo.progress(name,score) values('Lucky',68245),('Peterson',67236);
Query OK, 2 rows affected (0.0032 sec)
12.查看现在表中的数据：
Records: 2 Duplicates: 0 Warnings: 0
MySQL localhost:3300 ssl demo SQL > select * from demo.progress;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | Sullivan | 99479 |
| 2 | Randall | 99351 |
| 3 | Sanderson | 58986 |
| 4 | Lucky | 68245 |
| 5 | Peterson | 67236 |
+----+-----------+-------+
5 rows in set (0.0006 sec)
MySQL localhost:3300 ssl demo SQL >
13现在3300商品与3301端口的数据不一样
14在端口号为3300的实例上：
导出 Binlog
现在，我们已经更改了数据和架构，可以使用 util.dump_binlogs() 导出 Binlog。
创建一个文件夹，用于存放增量的binlog数据：
# mkdir -p /root/dumps/binlog_dump
以下是语法：
MySQL localhost:3300 ssl Py >
util.dump_binlogs("/root/dumps/binlog_dump", {"since":"/root/dumps/binlog_setup"} )
Starting from previous dump: /root/dumps/binlog_setup, created at: 2025-04-01 07:30:01 UTC
Starting from binary log file: centos7-bin.000002:948
Will finish at binary log file: centos7-bin.000002:1264
Dumping 1 binlogs (316 bytes of data) using 4 threads
140% (443 bytes / 316 bytes), 0.00 B/s, 0.00 B/s compressed, 1 / 1 binlogs done
Dump was written to: /root/dumps/binlog_dump/2025-04-01-08-47-02
Total duration: 00:00:00s
Binlogs dumped: 1
GTID set dumped: 71a0af43-0ec8-11f0-ac5a-000c29dfb7db:4
Uncompressed data size: 443 bytes
Compressed data size: 344 bytes
Compression ratio: 1.3
Events written: 6
Bytes written: 344 bytes
Average uncompressed throughput: 443.00 B/s
Average compressed throughput: 344.00 B/s
这个命令告诉MySQL Shell将二进制日志（binlogs）导出到文件夹下的/root/dumps/binlog_dump目录中。since选项告诉MySQL Shell只导出自上次导出以来发生的二进制日志。在这种情况下，上次的导出保存在dumps/binlog_setup目录中。这个位置是我在运行util.dumpInstance('/root/dumps/binlog_setup')时创建的导出文件。since的值可以是之前调用util.dumpInstance()或util.dumpBinlogs()的结果。
15.加载二进制日志
在我们的更改被导出后，我们现在可以将这些二进制日志加载到运行在端口3301上的实例中。以下是我们可以使用的语法：
MySQL localhost:3301 ssl Py > util.load_binlogs('/root/dumps/binlog_dump',{"ignoreGtidGap":"true"})
WARNING: The target instance is missing some transactions which are not available in the
dump: 71a0af43-0ec8-11f0-ac5a-000c29dfb7db:1-3.
NOTE: The 'ignoreGtidGap' option is set, continuing.
Loading 1 binlogs, 443 bytes of data
Opening dump '/root/dumps/binlog_dump'
Loading dump '2025-04-01-08-47-02' created at 2025-04-01 08:47:02 UTC
Loading binary log file 'centos7-bin.000002', GTID set: 71a0af43-0ec8-11f0-ac5a-000c29dfb7db:4 (443 bytes)
Found starting GTID: 71a0af43-0ec8-11f0-ac5a-000c29dfb7db:4
100% (443 bytes / 443 bytes), 0.00 B/s, 0.00 B/s compressed, 0.00 stmts/s, 1 / 1 binlogs done
Total duration: 00:00:00s
Binlogs loaded: 1
Uncompressed data size: 443 bytes
Compressed data size: 344 bytes
Statements executed: 25
Average uncompressed throughput: 443.00 B/s
Average compressed throughput: 344.00 B/s
Average statement throughput: 25.00 B/s
这个命令告诉MySQL Shell从文件夹/root/dumps/binlog_dump目录加载二进制日志。ignoreGtidGap选项告诉MySQL Shell忽略源实例和目标实例之间的任何GTID间隙。我不确定为什么会出现GTID序列间隙的错误（这可能是另一个话题），但使用此选项让我能够绕过这个问题。
16.检查更改
在加载二进制日志后，我们现在可以检查数据和架构更改是否正确应用
查看端口号为3301实例的数据：
MySQL localhost:3301 ssl Py > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:3301 ssl SQL > show databases;
+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.0013 sec)
MySQL localhost:3301 ssl SQL > use demo
Default schema set to `demo`.
Fetching global names, object names from `demo` for auto-completion... Press ^C to stop.
MySQL localhost:3301 ssl demo SQL > show tables;
+----------------+
| Tables_in_demo |
+----------------+
| progress |
+----------------+
1 row in set (0.0015 sec)
MySQL localhost:3301 ssl demo SQL > select * from progress;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | Sullivan | 99479 |
| 2 | Randall | 99351 |
| 3 | Sanderson | 58986 |
| 4 | Lucky | 68245 |
| 5 | Peterson | 67236 |
+----+-----------+-------+
5 rows in set (0.0010 sec)
select * from demo.progress;
查询结果表明，Lucky和Peterson的记录已成功添加到表中。
总结：
使用本文介绍的功能，在做两个不同实例进行离线同步时，可以避免每次都做全量数据同步。尤其对于数据量非常大的数据库来说，这个功能非常实用。
本文引用ORACLE官方文档：https://blogs.oracle.com/mysql/post/doing-a-incremental-dump-with-mysql-shell
