【MySQL 8.0】通过transportable tablespace(可传输表空间)迁移数据库
原创
©著作权归作者所有:来自51CTO博客作者dbprofessional的原创作品,请联系作者获取转载授权,否则将追究法律责任
[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 ~]$ mysqldump -uroot -pabcd.1234 --no-data --databases tpcc10 > tpcc10.sql
[mysql@node01 ~]$ scp tpcc10.sql node02:/home/mysql/
[mysql@node01 ~]$ /etc/init.d/mysql.server stop
[mysql@node02 ~]$ mysql -uroot -pabcd.1234 -hnode02
(root@node02) > create database tpcc10;
Query OK, 1 row affected (0.00 sec)
(root@node02) > source tpcc10.sql
[mysql@node02 ~]$ mysql -uroot -pabcd.1234 -e " select concat('alter table ', table_name, ' discard tablespace;') as '#' from information_schema.tables where table_schema = 'tpcc10';" > discard_ts.sql
[mysql@node02 ~]$ cat discard_ts.sql
#
alter table customer discard tablespace;
alter table district discard tablespace;
alter table history discard tablespace;
alter table item discard tablespace;
alter table new_orders discard tablespace;
alter table order_line discard tablespace;
alter table orders discard tablespace;
alter table stock discard tablespace;
alter table warehouse discard tablespace;
[mysql@node02 ~]$ mysql -uroot -pabcd.1234 tpcc10 < discard_ts.sql
[mysql@node01 ~]$ scp -p /usr/local/mysql/data/tpcc10/*.ibd node02:/usr/local/mysql/data/tpcc10/
[mysql@node02 ~]$ mysql -uroot -pabcd.1234 -e " select concat('alter table ', table_name, ' import tablespace;') as '#' from information_schema.tables where table_schema = 'tpcc10';" >import_ts.sql
[mysql@node02 ~]$ cat import_ts.sql
#
alter table customer import tablespace;
alter table district import tablespace;
alter table history import tablespace;
alter table item import tablespace;
alter table new_orders import tablespace;
alter table order_line import tablespace;
alter table orders import tablespace;
alter table stock import tablespace;
alter table warehouse import tablespace;
[mysql@node02 ~]$ mysql -uroot -pabcd.1234 tpcc10 < import_ts.sql
[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.