【MySQL 8.0】通过mysqldump与mysqlimport迁移表

[mysql@node01 ~]$ mysql -uroot -pabcd.1234 -hnode01 -D tpcc10 
(root@node01) > select count(*) from customer;
+----------+
| count(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.06 sec)

(root@node01) > select @@global.secure_file_priv; 
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+

[mysql@node01 ~]$ mysqldump -uroot -pabcd.1234 --tab=/var/lib/mysql-files --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' tpcc10 customer
[mysql@node01 ~]$ ls -lh /var/lib/mysql-files/
total 175M
-rw-rw-r-- 1 mysql mysql 2.3K Apr  2 20:50 customer.sql
-rw-rw-rw- 1 mysql mysql 175M Apr  2 20:50 customer.txt

[mysql@node01 ~]$ ssh-keygen
[mysql@node01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@node02
[mysql@node01 ~]$ ssh node02 date
Sun Apr  2 21:17:27 CST 2023

[mysql@node01 ~]$ scp /var/lib/mysql-files/* node02:/var/lib/mysql-files/

[mysql@node02 ~]$ mysql -uroot -pabcd.1234 tpcc10 < /var/lib/mysql-files/customer.sql

[mysql@node02 ~]$ mysqlimport -uroot -pabcd.1234 tpcc10 --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' /var/lib/mysql-files/customer.txt
tpcc10.customer: Records: 300000  Deleted: 0  Skipped: 0  Warnings: 0

[mysql@node02 ~]$ mysql -uroot -pabcd.1234 -hnode02 -D tpcc10 
(root@node02) > select count(*) from customer;
+----------+
| count(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.06 sec)

[root@node02 ~]# yum install -y parallel

[mysql@node02 ~]$cd /var/lib/mysql-files

[mysql@node02 mysql-files]$ wc -l customer.txt
300000 customer.txt

[mysql@node02 mysql-files]$ split -l 50000 customer.txt customer.

[mysql@node01 mysql-files]$ ls -ltrh customer.a*
-rw-rw-r-- 1 mysql mysql 30M Aug 10 22:40 customer.aa
-rw-rw-r-- 1 mysql mysql 30M Aug 10 22:40 customer.ab
-rw-rw-r-- 1 mysql mysql 30M Aug 10 22:40 customer.ac
-rw-rw-r-- 1 mysql mysql 30M Aug 10 22:40 customer.ad
-rw-rw-r-- 1 mysql mysql 30M Aug 10 22:40 customer.ae
-rw-rw-r-- 1 mysql mysql 30M Aug 10 22:40 customer.af

[mysql@node02 mysql-files]$ wc -l customer.aa
50000 customer.aa

[mysql@node02 ~]$ vim load.sh 
#!/bin/sh
find /var/lib/mysql-files -name 'customer.a*' > /tmp/data.txt
while read -r line
do
   echo mysqlimport -uroot -pabcd.1234 tpcc10 --fields-terminated-by="','" --fields-enclosed-by=\'\"\' --lines-terminated-by="'\n'" $line >>load.txt
done < /tmp/data.txt

[mysql@node02 ~]# chmod a+x load.sh
[mysql@node02 ~]# ./load.sh 

[mysql@node02 ~]$ time parallel -j 6 :::: load.txt  
......
tpcc10.customer: Records: 50000  Deleted: 0  Skipped: 0  Warnings: 0
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
tpcc10.customer: Records: 50000  Deleted: 0  Skipped: 0  Warnings: 0
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
tpcc10.customer: Records: 50000  Deleted: 0  Skipped: 0  Warnings: 0
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
tpcc10.customer: Records: 50000  Deleted: 0  Skipped: 0  Warnings: 0
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
tpcc10.customer: Records: 50000  Deleted: 0  Skipped: 0  Warnings: 0
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
tpcc10.customer: Records: 50000  Deleted: 0  Skipped: 0  Warnings: 0
mysqlimport: [Warning] Using a password on the command line interface can be insecure.