【MySQL 8.0】通过mysqldump与mysqlimport迁移数据库
原创
©著作权归作者所有：来自51CTO博客作者dbprofessional的原创作品，请联系作者获取转载授权，否则将追究法律责任
[mysql@node01 ~]$ ssh-keygen
[mysql@node01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@node02
[mysql@node01 ~]$ ssh node02 date
Sun Apr 2 16:50:44 CST 2023
[mysql@node01 ~]# mysqlshow -pabcd.1234 -vv tpcc10
Database: tpcc10
+------------+----------+------------+
| Tables | Columns | Total Rows |
+------------+----------+------------+
| customer | 21 | 300000 |
| district | 11 | 100 |
| history | 8 | 300000 |
| item | 5 | 100000 |
| new_orders | 3 | 90000 |
| order_line | 10 | 2999812 |
| orders | 8 | 300000 |
| stock | 17 | 1000000 |
| warehouse | 9 | 10 |
+------------+----------+------------+
9 rows in set.
[mysql@node01 ~]$ mysql -uroot -pabcd.1234 -hnode01
(root@node01) > show global variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
[mysql@node01 ~]# mysqldump -uroot -pabcd.1234 --tab=/var/lib/mysql-files --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' tpcc10
[mysql@node01 ~]# scp -p /var/lib/mysql-files/* node02:/var/lib/mysql-files/
[mysql@node02 ~]$ mysql -uroot -pabcd.1234 -hnode02
(root@node02) > create database tpcc10;
Query OK, 1 row affected (0.00 sec)
[mysql@node02 ~]# vim create.sh
#!/bin/sh
find /var/lib/mysql-files -name '*.sql' > /tmp/schema.txt
while read -r line
do
echo mysql -uroot -pabcd.1234 -D tpcc10 -e \"source $line\" | sh
done < /tmp/schema.txt
[mysql@node02 ~]# chmod a+x create.sh
[mysql@node02 ~]# ./create.sh
[mysql@node02 ~]# vim load.sh
#!/bin/sh
find /var/lib/mysql-files -name '*.txt' > /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 | sh
done < /tmp/data.txt
[mysql@node02 ~]# chmod a+x load.sh
[mysql@node02 ~]# ./load.sh
[mysql@node02 ~]# mysqlshow -pabcd.1234 -vv tpcc10
Database: tpcc10
+------------+----------+------------+
| Tables | Columns | Total Rows |
+------------+----------+------------+
| customer | 21 | 300000 |
| district | 11 | 100 |
| history | 8 | 300000 |
| item | 5 | 100000 |
| new_orders | 3 | 90000 |
| order_line | 10 | 2999812 |
| orders | 8 | 300000 |
| stock | 17 | 1000000 |
| warehouse | 9 | 10 |
+------------+----------+------------+
9 rows in set.
