拥有较大数据的mysql迁移方案

方案1

In this case, normally, the best solution is a mysqldump using the --tab option like this:

mysqldump --tab=/path/to/serverlocaldir --single-transaction <database> table_a

tab option produce 2 file, one file -table_a.sql- that contains only the table create statement and the oher file -table_a.txt- contains tab-separated data.

Now you can create your new table

create table table_b like table_a;

Then you simply load data in your new table via LOAD DATA without care for the table's name.

LOAD DATA INFILE '/path/to/serverlocaldir/table_a.txt' 
  INTO TABLE table_b FIELDS TERMINATED BY '\t' ...

LOAD DATA is usually 20 times faster than using INSERT statements.

LOAD DATA速度比INSERT语句要快，这里其实倒入也可以使用mysqlimport命令

方案2