1.首先确认mysqlsh 版本在MySQL Shell 9.2.0以后
[root@mysql8_3 bin]# mysqlsh
MySQL Shell 9.3.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.
2.确认配置文件里添加访问本地文件权限
[mysqld]
local_infile=on
3.创建初始化导出和binlog导出的文件夹
[root@mysql8_3 u01]# mkdir dump/{binlog_dump,binlog_setup}
4.导出数据库测试
[root@mysql8_3 binlog_setup]# mysqlsh --mysql --user=root --password=123456 --socket=/tmp/mysql3308.sock
MySQL Shell 9.3.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.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@/tmp%2Fmysql3308.sock'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 5240
Server version: 8.4.4-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL localhost SQL > \js
Switching to JavaScript mode...
MySQL localhost JS > util.dumpInstance('/u01/dump/binlog_dump',{dryRun: true, ocimds:true})
5.导出dump的起始点
MySQL localhost test JS > util.dumpInstance('/u01/dump/binlog_setup')
Acquiring global read lock
Global read lock acquired
Initializing - done
3 out of 7 schemas will be dumped and within them 19 tables, 7 views, 6 routines, 6 triggers.
15 out of 18 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% (52.57K rows / ~52.36K rows), 32.64K rows/s, 139.12 KB/s uncompressed, 131.14 KB/s compressed
Dump duration: 00:00:01s
Total duration: 00:00:01s
Schemas dumped: 3
Tables dumped: 19
Uncompressed data size: 3.23 MB
Compressed data size: 802.75 KB
Compression ratio: 4.0
Rows written: 52570
Bytes written: 802.75 KB
Average uncompressed throughput: 3.20 MB/s
Average compressed throughput: 794.87 KB/s
6.创建表和写入数据,然后根据前面导出的起始点,导出之后的binlog差额
MySQL localhost test JS > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost test SQL > create table t1 as select 1 id,'t1' t;
Query OK, 1 row affected (0.3358 sec)
Records: 1
Duplicates: 0
Warnings: 0
MySQL localhost test SQL > insert into t1 select 2 id,'t2' t;
Query OK, 1 row affected (0.1960 sec)
Records: 1
Duplicates: 0
Warnings: 0
MySQL localhost test SQL > select * from t1;
+----+----+
| id | t|
+----+----+
|1 | t1 |
|2 | t2 |
+----+----+
2 rows in set (0.0006 sec)
MySQL localhost test SQL > \js
Switching to JavaScript mode...
MySQL localhost test JS > util.dumpBinlogs('/u01/dump/binlog_dump',{since:'/u01/dump/binlog_setup'});
Starting from previous dump: /u01/dump/binlog_setup, created at: 2025-04-27 09:49:23 UTC
Starting from binary log file: binlog.000129:3514
Will finish at binary log file: binlog.000129:4263
Dumping 1 binlogs (749 bytes of data) using 4 threads
116% (876 bytes / 749 bytes), 0.00 B/s, 0.00 B/s compressed, 1 / 1 binlogs done
Dump was written to: /u01/dump/binlog_dump/2025-04-27-09-54-44
Total duration: 00:00:00s
Binlogs dumped: 1
GTID set dumped: 4966325d-1509-11f0-a15f-525400381583:2493-2494
Uncompressed data size: 876 bytes
Compressed data size: 514 bytes
Compression ratio: 1.7
Events written: 12
Bytes written: 514 bytes
Average uncompressed throughput: 876.00 B/s
Average compressed throughput: 514.00 B/s
MySQL localhost test JS >
7.另一个MySQL实例test数据库里没有t1表,然后导入前一个MySQL实例的binlog差额
[root@mysql8_3 mysql3309]# mysqlsh --mysql --user=root --password=123456 --socket=/tmp/mysql3309.sock
MySQL Shell 8.4.5
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.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@/tmp%2Fmysql3309.sock'
Fetching global names for auto-completion... Press ^C to stop.
Your MySQL connection id is 61
Server version: 8.4.4-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL localhost test SQL > show databases;
+--------------------+
| Database|
+--------------------+
| information_schema |
| mysql|
| performance_schema |
| sakila|
| sys|
| test|
| world|
+--------------------+
7 rows in set (0.0015 sec)
MySQL localhost test SQL > use test;
Default schema set to `test`.
Fetching global names, object names from `test` for auto-completion... Press ^C to stop.
MySQL localhost test SQL > show tables;
Empty set (0.0020 sec)
MySQL localhost test JS > util.loadBinlogs('/u01/dump/binlog_dump',{ignoreGtidGap: true});
WARNING: The target instance is missing some transactions which are not available in the dump: 4966325d-1509-11f0-a15f-525400381583:2487-2488:2491-2492.
NOTE: The 'ignoreGtidGap' option is set, continuing.
Loading 1 binlogs, 876 bytes of data
Opening dump '/u01/dump/binlog_dump'
Loading dump '2025-04-27-09-54-44' created at 2025-04-27 09:54:44 UTC
Loading binary log file 'binlog.000129', GTID set: 4966325d-1509-11f0-a15f-525400381583:2493-2494 (876 bytes)
Found starting GTID: 4966325d-1509-11f0-a15f-525400381583:2493
100% (876 bytes / 876 bytes), 876.00 B/s, 514.00 B/s compressed, 24.00 stmts/s, 1 / 1 binlogs done
Total duration: 00:00:01s
Binlogs loaded: 1
Uncompressed data size: 876 bytes
Compressed data size: 514 bytes
Statements executed: 37
Average uncompressed throughput: 589.87 B/s
Average compressed throughput: 346.11 B/s
Average statement throughput: 24.91 B/s
MySQL localhost test JS > \sql show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1|
+----------------+
1 row in set (0.0020 sec)
MySQL localhost test JS > \sql select * from t1;
+----+----+
| id | t|
+----+----+
|1 | t1 |
|2 | t2 |
+----+----+
2 rows in set (0.0006 sec)
MySQL localhost test JS >
参考:
https://dev.mysql.com/doc/mysql-shell/9.3/en/mysql-shell-utilities-dump-binlogs.html