总结：今日做了个数据迁移，总结了一下几种方法
情景：需要将92,93,94,95四台主机的esblogdb库的tlog_req_20150329  与tlog_rsp_20150329表的数据分别加载到10.161.4.40主机test库的tlog_req和tlog_rsp中去，
92-95四个库每张表差不多20个G的大小。

方法一：ibd拷贝表结构，数据文件
1>在40主机上分别创建几个库 test92，test93，test94，test95，并在每个库中创建tlog_req_20150329  与tlog_rsp_20150329两张表
mysql> use test92;（93-95一样）
Database changed
mysql> show tables;
+-------------------+
| Tables_in_test94  |
+-------------------+
| tlog_req_20150329 |
| tlog_rsp_20150329 |
+-------------------+

ALTER TABLE tlog_rsp_20150329 DISCARD TABLESPACE;
ALTER TABLE tlog_req_20150329 DISCARD TABLESPACE;

2>copy 92-95主机的tlog_req_20150329  与tlog_rsp_20150329两张表的ibd与cfg文件到40主机的对应的目录下
[mysql@ns1db01 esblogdb]$ scp tlog_rsp_20150329.ibd 10.161.4.65:/app/mydata/test92/
mysql@10.161.4.65's password: 
tlog_rsp_20150329.ibd                                                     100%   22GB  34.1MB/s   10:50    
[mysql@ns1db01 esblogdb]$ scp tlog_req_20150329.ibd 10.161.4.65:/app/mydata/test92/
mysql@10.161.4.65's password: 
tlog_req_20150329.ibd                                                     100% 9168MB  35.1MB/s   04:21 

.。。。。。。。（93-95步骤一样）



3>到40主机的test92-test95库上import tablespace即可
mysql> alter table tlog_req_20150329 import tablespace;
Query OK, 0 rows affected, 1 warning (2 min 5.88 sec)


mysql> alter table tlog_rsp_20150329 import tablespace;
Query OK, 0 rows affected, 1 warning (10 min 0.76 sec)


mysql> select count(*) from tlog_rsp_20150329;
+----------+
| count(*) |
+----------+
|  2382309 |
+----------+
1 row in set (27.69 sec)

mysql> select count(*) from tlog_req_20150329;
+----------+
| count(*) |
+----------+
|  2578655 |
+----------+
1 row in set (23.35 sec)

。。。。。。。test93-95同理

4>在inert即可

mysql> set unique_checks=off;

mysql> set autocommit=off;

mysql> alter table tlog_req disable keys;

mysql> insert into test.tlog_req select * from test92.tlog_req_20150329;
Query OK, 2382309 rows affected (19 min 5.03 sec)
Records: 2382309  Duplicates: 0  Warnings: 0

mysql> insert into test.tlog_req select * from test93.tlog_req_20150329;
Query OK, 2578655 rows affected (20 min 22.74 sec)
Records: 2578655  Duplicates: 0  Warnings: 0

mysql> insert into test.tlog_req select * from test94.tlog_req_20150329;
Query OK, 2577870 rows affected (13 min 50.80 sec)
Records: 2577870  Duplicates: 0  Warnings: 0

mysql> insert into test.tlog_req select * from test95.tlog_req_20150329;
Query OK, 2381378 rows affected (12 min 11.74 sec)
Records: 2381378  Duplicates: 0  Warnings: 0

mysql> insert into test.tlog_rsp select * from test92.tlog_rsp_20150329;
mysql> insert into test.tlog_rsp select * from test93.tlog_rsp_20150329;
mysql> insert into test.tlog_rsp select * from test94.tlog_rsp_20150329;
mysql> insert into test.tlog_rsp select * from test95.tlog_rsp_20150329;

----补充：再摸清领导的要求之后，发现如果只是想起到查询作用，可以创建视图，那样可以节省大量的时间
create view test.tlog_req_view as
select * from test92.tlog_req_20150329 union all
select * from test93.tlog_req_20150329 union all
select * from test94.tlog_req_20150329 union all
select * from test95.tlog_req_20150329;

和

create view test.tlog_rsp_view as 
select * from test92.tlog_rsp_20150329 union all
select * from test93.tlog_rsp_20150329 union all
select * from test94.tlog_rsp_20150329 union all
select * from test95.tlog_rsp_20150329;

方法二：load data数据加载（需要花费大量的时间）
导出

select * from dbname.table into outfile '/the/file/root' [option];

转移
scp

导入：
load data local infile "C:\backup\data.txt" into table tbname [option]


方法三：mysqldump（我试了一下，速度是最快的）
1dum出92-95主机的表的数据(-t表示只dump出insert语句，没有创建表结构的语句  --compact表示去除一些多余的注释)
92：
mysqldump -uroot -pESBecs00 -P3309 -h127.0.0.1 -t --compact esblogdb tlog_req_20150329>/app/mydata_rpt/req_92.sql
mysqldump -uroot -pESBecs00 -P3309 -h127.0.0.1 -t --compact esblogdb tlog_rsp_20150329>/app/mydata_rpt/rsq_92.sql

93
:mysqldump -uroot -pESBecs00 -P3309 -h127.0.0.1 -t --compact esblogdb tlog_req_20150329>/app/mydata_rpt/req_93.sql
mysqldump -uroot -pESBecs00 -P3309 -h127.0.0.1 -t --compact esblogdb tlog_rsp_20150329>/app/mydata_rpt/rsp_93.sql

94:
mysqldump -uroot -pESBecs00 -P3309 -h127.0.0.1 -t --compact esblogdb tlog_req_20150329>/app/mydata_rpt/req_94.sql
mysqldump -uroot -pESBecs00 -P3309 -h127.0.0.1 -t --compact esblogdb tlog_rsp_20150329>/app/mydata_rpt/rsp_94.sql

95:
mysqldump -uroot -pESBecs00 -P3309 -h127.0.0.1 -t --compact esblogdb tlog_req_20150329>/app/mydata_rpt/req_95.sql
mysqldump -uroot -pESBecs00 -P3309 -h127.0.0.1 -t --compact esblogdb tlog_rsp_20150329>/app/mydata_rpt/rsp_95.sql


2，将生成的sql全部scp到40主机上去
[mysql@vm21 mydata]$ ls 
auto.cnf  ib_logfile0  mysql             performance_schema  req_94.sql  rsp_93.sql  test      vm21-slow.log
esblogdb  ib_logfile1  mysqld_multi.log  req_92.sql          req_95.sql  rsp_94.sql  vm21.err
ibdata1   lost+found   mysql.sock        req_93.sql          rsp_92.sql  rsp_95.sql  vm21.pid

3.可以开始导入了
use database_name;(事先建好表，可以先rename对象表to tlog_req_20150329；再rename回去即可，随机应变)
source /app/mydata/req_92.sql;
source /app/mydata/req_93.sql;
source /app/mydata/req_94.sql;
source /app/mydata/req_95.sql;
source /app/mydata/rsp_92.sql;
source /app/mydata/rsp_93.sql;
source /app/mydata/rsp_94.sql;
source /app/mydata/rsp_95.sql;
。。。。。

未待续。。。