随笔- 435 文章- 0 评论- 6 阅读- 33万

通过 MySQL Shell 8.2.0 工具进行数据复制/迁移

一、通过sysbench创造测试数据

1、创造测试数据

[root@hankyoon db_tools]# sysbench --mysql-user=root --mysql-password='xxxxxx' --mysql-socket=/data/mysql/3307/mysql.sock /usr/share/sysbench/oltp_common.lua --tables=10 --table_size=1000000 prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)

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'...
[mysqld]
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'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...

2、测试数据大小2.4G

[root@hankyoon 3307]# du -sh sbtest
2.4G    sbtest

3、通过mysql shell连接数据库

[root@hankyoon 3307]# mysqlsh -uroot -p -S /data/mysql/3307/mysql.sock
Please provide the password for 'root@/data%2Fmysql%2F3307%2Fmysql.sock': **********
Save password for 'root@/data%2Fmysql%2F3307%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): y
MySQL Shell 8.2.0

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 session to 'root@/data%2Fmysql%2F3307%2Fmysql.sock'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 16
Server version: 8.0.28-19 Percona Server (GPL), Release 19, Revision 31e88966cd3
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS >

二、数据迁移：使用MySQL Shell进行数据迁移

1、迁移实例：

MySQL  localhost  JS > util.copyInstance('yoon@10.10.6.6:3308')
Please provide the password for 'yoon@10.10.6.6:3308': **********
Save password for 'yoon@10.10.6.6:3308'? [Y]es/[N]o/Ne[v]er (default No): y
ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.

2、参数local_infile 默认为OFF，这里需要提前将目标库参数进行设置

root@localhost:(none) 06:10:12 > set global local_infile = ON;
Query OK, 0 rows affected (0.00 sec)

3、设置为on后，自动继续迁移数据，（提示：'root'@'localhost' already exists报错）

Util.copyInstance: local_infile disabled in server (MYSQLSH 53025)
 MySQL  localhost  JS > util.copyInstance('yoon@10.10.6.6:3308')
Copying DDL, Data and Users from in-memory FS, source: hankyoon:3307, target: hankyoon:3308.
SRC: Acquiring global read lock
SRC: Global read lock acquired
Initializing - done
SRC: 2 out of 6 schemas will be dumped and within them 10 tables, 0 views.
SRC: 1 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.28-19. Dump was produced from MySQL 8.0.28-19
TGT: Scanning metadata...
TGT: Scanning metadata - done
TGT: Checking for pre-existing objects...
ERROR: TGT: Account 'root'@'localhost' already exists
ERROR: TGT: One or more objects in the dump already exist in the destination database. You must either DROP these objects or exclude them from the load.

4、这里将目标端用户root进行排除掉，并且进行并行迁移

MySQL  localhost  JS > util.copyInstance('yoon@10.10.6.6:3308',{threads:4,excludeUsers:["root@localhost"]})
Copying DDL, Data and Users from in-memory FS, source: hankyoon:3307, target: hankyoon:3308.
SRC: Acquiring global read lock
SRC: Global read lock acquired
Initializing - done
SRC: 2 out of 6 schemas will be dumped and within them 10 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.28-19. Dump was produced from MySQL 8.0.28-19
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% (10.00M rows / ~9.86M rows), 34.47K rows/s, 8.66 MB/s
SRC: Dump duration: 00:02:39s
SRC: Total duration: 00:02:39s
SRC: Schemas dumped: 2
SRC: Tables dumped: 10
SRC: Data size: 1.94 GB
SRC: Rows written: 10000000
SRC: Bytes written: 1.94 GB
SRC: Average throughput: 12.15 MB/s
TGT: Executing common postamble SQL
100% (1.94 GB / 1.94 GB), 6.69 MB/s, 10 / 10 tables done
Recreating indexes - done
TGT: 40 chunks (10.00M rows, 1.94 GB) for 10 tables in 2 schemas were loaded in 2 min 39 sec (avg throughput 12.16 MB/s)
TGT: 0 accounts were loaded
TGT: 0 warnings were reported during the load.

---
Dump_metadata:
  Binlog_file: binlog.000004
  Binlog_position: 835009314
  Executed_GTID_set: ''