一、通过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('[email protected]:3308')
Please provide the password for '[email protected]:3308': **********
Save password for '[email protected]: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('[email protected]: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('[email protected]: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: ''
标签：SRC,8.2,Creating,TGT,...,Shell,done,MySQL From： https://www.cnblogs.com/hankyoon/p/17819618.html