MySQL数据库迁移快速导出导入大量数据
时间:2019-05-30 17:19 来源:linux.it.net.cn 作者:IT
数据库迁移是我们经常可遇到的问题，对于少量的数据，迁移基本上不会有什么问题。生产环境中，有以下情况需要做迁移工作：
-
磁盘空间不够。比如一些老项目，选用的机型并不一定适用于数据库。随着时间的推移，硬盘很有可能出现短缺；
-
业务出现瓶颈。比如项目中采用单机承担所有的读写业务，业务压力增大，不堪重负。如果 IO 压力在可接受的范围，会采用读写分离方案；
-
机器出现瓶颈。机器出现瓶颈主要在磁盘 IO 能力、内存、CPU，此时除了针对瓶颈做一些优化以外，选择迁移是不错的方案；
-
项目改造。某些项目的数据库存在跨机房的情况，可能会在不同机房中增加节点，或者把机器从一个机房迁移到另一个机房。再比如，不同业务共用同一台服务器，为了缓解服务器压力以及方便维护，也会做迁移。
MySQL迁移通常使用的有三种方法：
1、数据库直接导出，拷贝文件到新服务器，在新服务器上导入。
2、使用第三方迁移工具。
3、数据文件和库表结构文件直接拷贝到新服务器，挂载到同样配置的MySQL服务下。
第一种方案的优点：会重建数据文件，减少数据文件的占用空间，兼容性最好，导出导入很少发生问题，需求灵活。缺点:使用传统导出导入时间占用长。
第二种方案的优点：设置完成后传输无人值守，自动完成。缺点：不够灵活，设置繁琐，传输时间长，异常后很难从异常的位置继续传输。
第三种方案的优点：时间占用短，文件可断点传输，操作步骤少。缺点：新旧服务器中MySQL版本及配置必须相同，可能引起未知问题。
假如数据库迁移是因为业务瓶颈或项目改造等需要变动数据表结构的(比如分区分表)，我们便只能使用第一种方法了。
使用MySQL的SELECT INTO OUTFILE 、LOAD DATA INFILE快速导出导入数据
LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。MySQL官方文档也说明了，该方法比一次性插入一条数据性能快20倍。
当用户一前一后地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 将数据从一个数据库写到一个文件中，然后再从文件中将它读入数据库中时，两个命令的字段和行处理选项必须匹配。否则，LOAD DATA INFILE 将不能正确地解释文件内容。
下面是一个项目的例子，MySQL由windows平台迁移到Linux平台，数据总量12G
Windows平台导出数据：
tables.txt是保存数据表名称的文件，通过从文件中读取数据表名称，循环导出所有表：如果过程中摄及到分表，可根据分表规则修改导出的sql语句和批处理代码，非常灵活。
1
2
3
4
5
6
@echo off & setlocal enabledelayedexpansion
for /f %%i in (tables.txt) do ( set table=%%i
echo "dump table -- !table! --"
mysql -uroot -p12345678 codetc_old -e "SELECT * INTO OUTFILE 'F:/MySQL/Uploads/!table!.txt' FIELDS TERMINATED BY ',' FROM !table!"
)
pause
Linux平台导入数据：
1
2
3
4
5
#!/bin/bash
while read line
do
mysql -uroot -p12345678 codetc_new -e "LOAD DATA INFILE '/var/lib/mysql-files/$line.txt' INTO TABLE $line FIELDS TERMINATED BY ','"
done < tables.txt
数据导入之前需在新机器上创建表结构，12G的数据导出用时3分钟左右，导入用时4分钟左右（执行时间根据机器的配置会有所不同，不具有参考价值）
(责任编辑：IT)
数据库迁移是我们经常可遇到的问题，对于少量的数据，迁移基本上不会有什么问题。生产环境中，有以下情况需要做迁移工作：
MySQL迁移通常使用的有三种方法： 1、数据库直接导出，拷贝文件到新服务器，在新服务器上导入。 2、使用第三方迁移工具。 3、数据文件和库表结构文件直接拷贝到新服务器，挂载到同样配置的MySQL服务下。 第一种方案的优点：会重建数据文件，减少数据文件的占用空间，兼容性最好，导出导入很少发生问题，需求灵活。缺点:使用传统导出导入时间占用长。 第二种方案的优点：设置完成后传输无人值守，自动完成。缺点：不够灵活，设置繁琐，传输时间长，异常后很难从异常的位置继续传输。 第三种方案的优点：时间占用短，文件可断点传输，操作步骤少。缺点：新旧服务器中MySQL版本及配置必须相同，可能引起未知问题。 假如数据库迁移是因为业务瓶颈或项目改造等需要变动数据表结构的(比如分区分表)，我们便只能使用第一种方法了。 使用MySQL的SELECT INTO OUTFILE 、LOAD DATA INFILE快速导出导入数据 LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。MySQL官方文档也说明了，该方法比一次性插入一条数据性能快20倍。 当用户一前一后地使用SELECT ... INTO OUTFILE 和LOAD DATA INFILE 将数据从一个数据库写到一个文件中，然后再从文件中将它读入数据库中时，两个命令的字段和行处理选项必须匹配。否则，LOAD DATA INFILE 将不能正确地解释文件内容。 下面是一个项目的例子，MySQL由windows平台迁移到Linux平台，数据总量12G Windows平台导出数据： tables.txt是保存数据表名称的文件，通过从文件中读取数据表名称，循环导出所有表：如果过程中摄及到分表，可根据分表规则修改导出的sql语句和批处理代码，非常灵活。
Linux平台导入数据：
数据导入之前需在新机器上创建表结构，12G的数据导出用时3分钟左右，导入用时4分钟左右（执行时间根据机器的配置会有所不同，不具有参考价值） (责任编辑：IT) |