mysql修改默认字符集_百万级MySQL的数据量，该如何快速的完成数据迁移？

＃ 背景

上个月，我和朋友一起做了一个微信小程序。趁着5月20日节日的人气，两周内迅速积累了过百万用户。我们在小程序页面增加了领取点，为微信用户提供发送模板消息通知。

这个小程序的后端逻辑一开始是写在一个框架里的，框架自带的表是用来存储微信端授权登录的用户信息的，用表来存储。截至目前，已采集数据超过380万条，其中很大一部分已成功用于向用户发送模板通知，取得了良好的二次推广效果。

随着数据量的增加，之前使用的服务器空间开始变得有点不足。最近写了一个新的框架，专门针对小程序的后端开发，所以想把原来的数据迁移到新系统的数据库中。买了一台4核8G的机器，开始数据迁移。我们简单记录一下迁移过程。

# 计划选择

在日常开发中，我们经常使用的数据备份和迁移方法是使用工具导出一个sql文件，然后将sql导入到新数据库中，完成数据迁移。实验发现，将百万级数据库导出成sql文件需要几分钟的时间。导出的sql文件大小约为1G。然后通过scp命令将1G的sql文件复制到另一台服务器上。也需要几分钟的时间。我使用命令将数据导入到新服务器的数据库中。我跑了一夜，无法导入数据。 CPU 已满。

通过命令行直接操作数据库是一种比较方便的导出和导入数据的方式，但是当数据量较大时，往往比较耗时，并且对服务器性能要求较高。如果数据迁移的时间要求不是很高，可以尝试编写脚本来迁移数据。虽然我没有实际尝试过，但我认为可能有两种脚本解决方案。

第一种方法是在迁移目标服务器上运行迁移脚本，远程连接源数据服务器的数据库，通过设置查询条件分块读取源数据，读取后写入目标数据库。这种迁移方法可能效率相对较低。数据导出和导入相当于一个同步过程，需要等到读取完成后再进行写入。如果查询条件设计合理，还可以通过多线程的方式启动多个迁移脚本，达到并行迁移的效果。

第二种方法可以与redis结合构建“生产+消费”的迁移方案。源数据服务器可以作为数据生产者，在源数据服务器上运行多线程脚本，并行读取数据库中的数据，并将数据写入redis队列。目标服务器作为消费者，也在目标服务器上运行多线程脚本，远程连接redis，并行读取redis队列中的数据，并将读取到的数据写入目标数据库。与第一种方法相比，该方法是一种异步方案。数据导入和数据导出可以同时进行。通过使用redis作为数据中转站，效率将会大大提高。

您可以使用Go语言编写迁移脚本，利用其原生的并发特性来达到并行迁移数据的目的，提高迁移效率。

第一个迁移方案效率太低，而第二个迁移方案编码成本昂贵。通过网上找到的资料对比分析，最终选择使用mysql的数据导入file.txt，将数据file.txt加载到表中。命令以导入导出文件的形式完成百万数据的迁移。

# 迁移过程

导出源数据库中的数据文件

  select * from dc_mp_fans into outfile '/data/fans.txt';    

将数据文件复制到目标服务器

zip fans.zip /data/fans.txtscp fans.zip root@ip:/data/    

将文件导入到目标数据库

unzip /data/fans.zip

load data infile '/data/fans.txt' into table wxa_fans(id,appid,openid,unionid,@dummy,created_at,@dummy,nickname,gender,avatar_url,@dummy,@dummy,@dummy,@dummy,language,country,province,city,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy);

通过以下步骤，您可以在几分钟内完成百万级数据表的跨服务器迁移。

# 注释

mysql安全项设置

在mysql中执行load data and into命令需要在mysql中打开该选项。您可以使用 show like '%%';检查mysql是否开启了该选项。默认值为Null，表示不允许执行导入导出命令。通过 vim /etc/f 修改mysql配置项，并将值设置为空：

[mysqld]  secure_file_priv=''

您可以通过命令导入和导出数据文件。

导入导出数据表字段不对应

在上面的例子中，数据从源数据库表迁移到目标数据库表。两个数据表的字段是：-

导入数据时，可以设置字段名称与目标字段的数据相匹配，并通过@dummy丢弃不需要的目标字段数据。

# 总结

根据这次数据迁移的经验，总结就是：少量数据可以通过命令导入导出。此方法简单方便。 - 当数据量较大且您有足够的迁移耐心时，您可以选择自己编写脚本并选择合适的并行方案来迁移数据。这种方法编码成本较高。 - 当数据量较大，想在短时间内完成数据迁移时，可以通过mysql导入导出文件进行迁移，效率更高。