在MySQL Shell使用dump和load binlogs给不同实例迁移数据

1.首先确认mysqlsh 版本在MySQL Shell 9.2.0以后

[root@mysql8_3 bin]# mysqlshMySQL Shell 9.3.0Copyright (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.sockMySQL Shell 9.3.0Copyright (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 5240Server version: 8.4.4-commercial MySQL Enterprise Server - CommercialNo default schema selected; type \use <schema> to set one.MySQL localhost SQL > \jsSwitching 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 lockGlobal read lock acquiredInitializing - done3 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 - doneAll transactions have been startedLocking instance for backupGlobal read lock has been releasedWriting global DDL filesWriting users DDLRunning data dump using 4 threads.NOTE: Progress information uses estimated values and may not be accurate.Writing schema metadata - done   Writing DDL - doneWriting table metadata - doneStarting data dump100% (52.57K rows / ~52.36K rows), 32.64K rows/s, 139.12 KB/s uncompressed, 131.14 KB/s compressedDump duration: 00:00:01sTotal duration: 00:00:01sSchemas dumped: 3Tables dumped: 19Uncompressed data size: 3.23 MBCompressed data size: 802.75 KBCompression ratio: 4.0Rows written: 52570Bytes written: 802.75 KBAverage uncompressed throughput: 3.20 MB/sAverage compressed throughput: 794.87 KB/s

6.创建表和写入数据,然后根据前面导出的起始点,导出之后的binlog差额

MySQL localhost test JS > \sqlSwitching 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: 1Duplicates: 0Warnings: 0MySQL localhost test SQL > insert into t1 select 2 id,'t2' t;Query OK, 1 row affected (0.1960 sec)Records: 1Duplicates: 0Warnings: 0MySQL localhost test SQL > select * from t1;+----+----+| id | t|+----+----+|1 | t1 ||2 | t2 |+----+----+2 rows in set (0.0006 sec)MySQL localhost test SQL > \jsSwitching 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 UTCStarting from binary log file: binlog.000129:3514Will finish at binary log file: binlog.000129:4263Dumping 1 binlogs (749 bytes of data) using 4 threads116% (876 bytes / 749 bytes), 0.00 B/s, 0.00 B/s compressed, 1 / 1 binlogs doneDump was written to: /u01/dump/binlog_dump/2025-04-27-09-54-44Total duration: 00:00:00sBinlogs dumped: 1GTID set dumped: 4966325d-1509-11f0-a15f-525400381583:2493-2494Uncompressed data size: 876 bytesCompressed data size: 514 bytesCompression ratio: 1.7Events written: 12Bytes written: 514 bytesAverage uncompressed throughput: 876.00 B/sAverage compressed throughput: 514.00 B/sMySQL localhost test JS >

7.另一个MySQL实例test数据库里没有t1表,然后导入前一个MySQL实例的binlog差额

[root@mysql8_3 mysql3309]# mysqlsh --mysql --user=root --password=123456 --socket=/tmp/mysql3309.sockMySQL Shell 8.4.5Copyright (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 61Server version: 8.4.4-commercial MySQL Enterprise Server - CommercialNo 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 dataOpening dump '/u01/dump/binlog_dump'Loading dump '2025-04-27-09-54-44' created at 2025-04-27 09:54:44 UTCLoading binary log file 'binlog.000129', GTID set: 4966325d-1509-11f0-a15f-525400381583:2493-2494 (876 bytes)Found starting GTID: 4966325d-1509-11f0-a15f-525400381583:2493100% (876 bytes / 876 bytes), 876.00 B/s, 514.00 B/s compressed, 24.00 stmts/s, 1 / 1 binlogs doneTotal duration: 00:00:01sBinlogs loaded: 1Uncompressed data size: 876 bytesCompressed data size: 514 bytesStatements executed: 37Average uncompressed throughput: 589.87 B/sAverage compressed throughput: 346.11 B/sAverage statement throughput: 24.91 B/sMySQL 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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值