Mysql如何实现数据迁移

共计 2643 个字符，预计需要花费 7 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 Mysql 如何实现数据迁移，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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 倍

关于“Mysql 如何实现数据迁移”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。