使用MySQL Shell 8.1 工具进行数据复制/迁移-每日运维

适用范围

MySQL Shell 8.1版本工具包中提供了一个新的数据逻辑拷贝方式，在不需要中间存储介质需求的情况下进行不落地数据迁移（将本地MySQL实例数据逻辑导出不落地方式直接导入至目标端数据库中）。

方案概述

新版本MySQL Shell 8.1中提供了一些新方法将一个实例数据拷贝到另一个实例中而不需要中间存储：

util.copyInstance()

util.copySchemas()

util.copyTables()

本文将以拷贝实例copyInstance()举例，将MySQL实例数据拷贝到远端服务器MySQL实例中。

实施步骤

1. 造数据：使用sysbench造一些基础数据作为测试数据

[root@c1 8034]# sysbench --mysql-host=localhost --mysql-port=8034 --mysql-user=root --mysql-password=mysql --mysql-socket=/data/mysql/8034/run/mysql.sock /usr/local/share/sysbench/oltp_common.lua --tables=10 --table_size=1000000 prepare sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)


Creating table 'sbtest1'...

Inserting 1000000 records into 'sbtest1'

Creating a secondary index on 'sbtest1'...

Creating table 'sbtest2'...

Inserting 1000000 records into 'sbtest2'

Creating a secondary index on 'sbtest2'...

Creating table 'sbtest3'...

Inserting 1000000 records into 'sbtest3'

Creating a secondary index on 'sbtest3'...

Creating table 'sbtest4'...

Inserting 1000000 records into 'sbtest4'

Creating a secondary index on 'sbtest4'...

Creating table 'sbtest5'...

Inserting 1000000 records into 'sbtest5'

Creating a secondary index on 'sbtest5'...

Creating table 'sbtest6'...

Inserting 1000000 records into 'sbtest6'

Creating a secondary index on 'sbtest6'...

Creating table 'sbtest7'...

Inserting 1000000 records into 'sbtest7'

Creating a secondary index on 'sbtest7'...

Creating table 'sbtest8'...

Inserting 1000000 records into 'sbtest8'

Creating a secondary index on 'sbtest8'...

Creating table 'sbtest9'...

Inserting 1000000 records into 'sbtest9'

Creating a secondary index on 'sbtest9'..
## 数据库中已有一些测试数据 大小约2G
root@localhost:(none) 05:06:58 >show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sbtest             |

| sys                |

+--------------------+

5 rows in set (0.01 sec)
root@localhost:(none) 05:04:59 >select sum(data_length+index_length)/1024/1024/1024 as 'Total Size GB' from information_schema.tables;

+----------------+

| Total Size GB  |

+----------------+

| 1.979049682617 |

+----------------+

1 row in set (0.02 sec)

2. 数据迁移：使用MySQL Shell进行数据迁移

## 使用MySQL Shell登录本地MySQL [root@c1 mysql]# /data/mysql/mysqlshell811/bin/mysqlsh --uri=root@localhost --socket=/data/mysql/8034/run/mysql.sock Please provide the password for 'root@/data%2Fmysql%2F8034%2Frun%2Fmysql.sock': ***** Save password for 'root@/data%2Fmysql%2F8034%2Frun%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): yes MySQL Shell 8.1.1


Copyright (c) 2016, 2023, 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.

Creating a Classic session to 'root@/data%2Fmysql%2F8034%2Frun%2Fmysql.sock'

Fetching schema names for auto-completion... Press ^C to stop.

Your MySQL connection id is 17

Server version: 8.0.34 MySQL Community Server - GPL

No default schema selected; type \use  to set one.

 MySQL  localhost  JS >

 MySQL  localhost  JS > copyInstance('mysqldba@192.168.139.130:8034')

ReferenceError: copyInstance is not defined

 MySQL  localhost  JS > util.copyInstance('mysqldba@192.168.139.130:8034')

Please provide the password for 'mysqldba@192.168.139.130:8034': *****

Save password for 'mysqldba@192.168.139.130:8034'? [Y]es/[N]o/Ne[v]er (default No): yes

ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.

Util.copyInstance: local_infile disabled in server (MYSQLSH 53025)

 MySQL  localhost  JS > 
## 参数local_infile 默认为OFF，这里需要提前将目标库参数进行设置

root@localhost:(none) 06:10:12 >set global local_infile = ON;

Query OK, 0 rows affected (0.00 sec)
## 继续进行数据迁移：这里将目标端用户root进行排除掉，并且进行并行迁移。

 MySQL  localhost  JS >

util.copyInstance('mysqldba@192.168.139.130:8034',{threads:4,excludeUsers:["root@localhost"]})

Copying DDL, Data and Users from in-memory FS, source: 192.168.139.128:8034, target: 192.168.139.129:8034.

SRC: Acquiring global read lock

SRC: Global read lock acquired

Initializing - done

SRC: 1 out of 5 schemas will be dumped and within them 9 tables, 0 views.

SRC: 0 out of 4 users will be dumped.

Gathering information - done

SRC: All transactions have been started

SRC: Locking instance for backup

SRC: Global read lock has been released

SRC: Writing global DDL files

SRC: Writing users DDL

SRC: Running data dump using 4 threads.

NOTE: SRC: Progress information uses estimated values and may not be accurate.

TGT: Opening dump...

NOTE: TGT: Dump is still ongoing, data will be loaded as it becomes available.

TGT: Target is MySQL 8.0.34. Dump was produced from MySQL 8.0.34

TGT: Scanning metadata...

TGT: Scanning metadata - done

TGT: Checking for pre-existing objects...

TGT: Executing common preamble SQL

TGT: Executing DDL...

TGT: Executing DDL - done

TGT: Executing user accounts SQL...

TGT: Executing view DDL...

TGT: Executing view DDL - done

TGT: Loading data...

TGT: Starting data load

Writing schema metadata - done

Writing DDL - done

Writing table metadata - done

SRC: Starting data dump

TGT: Recreating indexes...

101% (9.00M rows / ~8.88M rows), 75.75K rows/s, 19.15 MB/s

SRC: Dump duration: 00:00:52s

SRC: Total duration: 00:00:52s

SRC: Schemas dumped: 1

SRC: Tables dumped: 9

SRC: Data size: 1.75 GB

SRC: Rows written: 9000000

SRC: Bytes written: 1.75 GB

SRC: Average throughput: 33.28 MB/s

TGT: Executing common postamble SQL

100% (1.75 GB / 1.75 GB), 14.90 MB/s, 9 / 9 tables done

Recreating indexes - done

TGT: 36 chunks (9.00M rows, 1.75 GB) for 9 tables in 1 schemas were loaded in 52 sec (avg throughput 33.32 MB/s)

TGT: 0 accounts were loaded

TGT: 0 warnings were reported during the load.
---

Dump_metadata:

  Binlog_file: binlog.000005

  Binlog_position: 835006820

  Executed_GTID_set: 7ce9d1d6-60d8-11ee-9656-000c29ff0201:1-3785
 MySQL  localhost  JS > \q

Bye!

[root@c1 mysql]#

3. 总结

使用MySQL Shell util.copyInstance进行迁移，1.75G用时52秒，4个并行度的情况下大约每分钟可以达到接近2G的速度，速度在逻辑迁移类型中算是比较快的速度了，如果CPU资源充足可以适当调大并行度来进一步提高迁移速度。
将MySQL实例复制到另一台服务器，如果目标端是相同版本，可能克隆插件的方式是最快的方法，但是克隆的限制条件也比较多，并不是所有情况都适用，所以在其他情况下推荐使用MySQL Shell 8.1。

使用MySQL Shell 8.1 工具进行数据复制/迁移

适用范围

MySQL Shell 8.1版本工具包中提供了一个新的数据逻辑拷贝方式，在不需要中间存储介质需求的情况下进行不落地数据迁移（将本地MySQL实例数据逻辑导出不落地方式直接导入至目标端数据库中）。

实施步骤

1. 造数据：使用sysbench造一些基础数据作为测试数据

2. 数据迁移：使用MySQL Shell进行数据迁移

3. 总结

参考文档

https://dev.mysql.com/doc/mysql-shell/8.1/en/mysql-shell-utils-copy.html

相关文章

发布评论取消回复

向阳逐梦

【Rocky 9】Step by Step 从 CentOS 7.9 升级到 Rocky Linux 9.2

oracle查询V$ARCHIVE_GAP慢的问题解决–转瞻Derek

Oracle 11g RAC集群日常运维命令总结

使用MySQL Shell 8.1 工具进行数据复制/迁移

适用范围 MySQL Shell 8.1版本工具包中提供了一个新的数据逻辑拷贝方式，在不需要中间存储介质需求的情况下进行不落地数据迁移（将本地MySQL实例数据逻辑导出不落地方式直接导入至目标端数据库中）。

实施步骤

1. 造数据：使用sysbench造一些基础数据作为测试数据

2. 数据迁移：使用MySQL Shell进行数据迁移

3. 总结

参考文档 https://dev.mysql.com/doc/mysql-shell/8.1/en/mysql-shell-utils-copy.html

相关文章

发布评论 取消回复

向阳逐梦

【Rocky 9】Step by Step 从 CentOS 7.9 升级到 Rocky Linux 9.2

oracle查询V$ARCHIVE_GAP慢的问题解决–转瞻Derek

Oracle 11g RAC集群日常运维命令总结

适用范围

MySQL Shell 8.1版本工具包中提供了一个新的数据逻辑拷贝方式，在不需要中间存储介质需求的情况下进行不落地数据迁移（将本地MySQL实例数据逻辑导出不落地方式直接导入至目标端数据库中）。

参考文档

https://dev.mysql.com/doc/mysql-shell/8.1/en/mysql-shell-utils-copy.html

发布评论取消回复