mysql数据迁移_Mysql数据迁移方法

MySQL迁移通常使用的有三种方法：

1、数据库直接导出，拷贝文件到新服务器，在新服务器上导入。

2、使用第三方迁移工具。

3、数据文件和库表结构文件直接拷贝到新服务器，挂载到同样配置的MySQL服务下。

第一种方案的优点：会重建数据文件，减少数据文件的占用空间，兼容性最好，导出导入很少发生问题，需求灵活。缺点:使用传统导出导入时间占用长。

第二种方案的优点：设置完成后传输无人值守，自动完成。缺点：不够灵活，设置繁琐，传输时间长，异常后很难从异常的位置继续传输。

第三种方案的优点：时间占用短，文件可断点传输，操作步骤少。缺点：新旧服务器中MySQL版本及配置必须相同，可能引起未知问题。

假如数据库迁移是因为业务瓶颈或项目改造等需要变动数据表结构的(比如分区分表)，我们便只能使用第一种方法了。

我一般使用MySQL的 SELECT INTO OUTFILE 、LOAD DATA INFILE 快速导出导入数据

【导出导出工作准备】

导出前关闭日志，避免数据备份过程中频繁记录日志

删除主键，关闭自动增长。在该表中主键其实作用不大，自动增长是需要的(mysql中自动增长的一列一定要为key，所以设置为主键)，等待数据转移结束后重新设置回来

删除表中索引。在插入数据时索引的存在会很大程度上影响速度，所以先关闭，转移后重新建立

Mysql系统参数调优，如下：(具体含义后面给出)

innodb_data_file_path           = ibdata1:1G:autoextend

innodb_file_per_table           = 1

innodb_thread_concurrency       = 20

innodb_flush_log_at_trx_commit  = 1

innodb_log_file_size            = 256M

innodb_log_files_in_group       = 3

innodb_max_dirty_pages_pct      = 50

innodb_lock_wait_timeout        = 120

key_buffer_size=400M

innodb_buffer_pool_size=4G

innodb_additional_mem_pool_size=20M

innodb_log_buffer_size=20M

query_cache_size=40M

read_buffer_size=4M

read_rnd_buffer_size=8M

tmp_table_size=16M

max_allowed_packet = 32M

【操作方法及结果】

(1)create table t2 as select * from t1

CREATE TABLE dn_location3

PARTITION BY RANGE (UNIX_TIMESTAMP(UPLOADTIME))

(   PARTITION p141109 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-09 00:00:00')),

PARTITION p141110 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-10 00:00:00')),

PARTITION p141111 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-11 00:00:00')),

PARTITION p141112 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-12 00:00:00'))

)

as select * from dn_location

where uploadtime > '2014-08-04';

create table t2 as select * from dn_location2;

as创建出来的t2表(新表)缺少t1表(源表)的索引信息，只有表结构相同，没有索引。

此方法效率较高，在前面的实验环境下，42min内将一张表内4600W的数据转到一张新的表中，在create新表时我添加了分区的操作，因此新表成功创建为分区表，这样一步到位的既转移了数据又创建了分区表。此方法平均速度：6570W条/h ，至于该方法其他需要注意的地方，暂时没有去了解。

(2)使用MySQL的 SELECT INTO OUTFILE、LOAD DATA INFILE

LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。当用户一前一后地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 将数据从一个数据库写到一个文件中，然后再从文件中将它读入数据库中时，两个命令的字段和行处理选项必须匹配。否则，LOAD DATA INFILE 将不能正确地解释文件内容。

假设用户使用SELECT ... INTO OUTFILE 以逗号分隔字段的方式将数据写入到一个文件中：

SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM table2;

为了将由逗号分隔的文件读回时，正确的语句应该是：

LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';

如果用户试图用下面所示的语句读取文件，它将不会工作，因为命令LOAD DATA INFILE 以定位符区分字段值：

LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY '\t';

下面是我用来导入导出的命令：

select * into outfile 'ddd.txt' fields terminated by ',' from dn_location;

load data infile 'ddd.txt' into table dn_location2  FIELDS TERMINATED BY ',';

通过该方法导出的数据，是将各字段(只有数据，不导出表结构)数据存在一个文件中，中间以逗号分隔，因为文件中并不包含数据库名或者表名，因此需要在导入导出的时候些明确。该方法在18分钟内导出1.6亿条记录，46min内导入6472W条记录，平均速度：8442W条/h。mysql官方文档也说明了，该方法比一次性插入一条数据性能快20倍

转自：

https://www.jianshu.com/p/e9f0fea97bc3