MySQL一次大表迁移的优化

MySQL一次大表迁移的优化

文章目录

• 一.问题描述
• 二. 解决方案
• • 2.1 调整索引
  • 2.2 调整参数
  • 2.3 重新导入数据
  • 2.4 重建索引
  • 2.5 恢复mysql配置文件

一.问题描述

  今天一个做Java开发的哥们向我咨询了一个问题。

  他想把一个300多万条数据数据的表迁移到本地测试环境，他处理的方案是 先通过mysqldump命令将该表做一个备份，然后传输到测试环境，最后执行备份文件。

  理想很丰满，现实很骨感，300多万的数据不大，备份下来也就2.5GB左右的sql文件，可是在测试环境居然执行了12个小时才执行了一半，无奈只能终止。

  于是这个哥们开始咨询我，希望我能提供一个快速一点的方法。

二. 解决方案

  既然是批量数据的迁移，那就肯定想到调整参数和索引相关，于是从这两个方向着手。

2.1 调整索引

将 dump.sql文件中除id列主键索引外的所有索引都去掉，数据同步完成后再创建索引。

2.2 调整参数

我调整的参数如下:

-- 默认值是128M，测试机器是16GB内存，可以调整为10G，缓存更多数据
innodb_buffer_pool_size=10G

-- 默认值是50M，尽量调大，减少checkpoint
innodb_log_file_size=4G

-- 每次事务提交，都写入os cache 缓存，可能1秒将cache里面数据写入磁盘，减少写磁盘
innodb_flush_log_at_trx_commit=2

-- 设置为0的时候，持久化 *** 作留给文件系统 ，减少写磁盘
sync_binlog=0

-- 关闭binlog，减少写磁盘
skip-log-bin

2.3 重新导入数据

重新导入数据，速度快了很多，不到半个小时，300多万的数据都导入成功了。

2.4 重建索引

因为表有十多个索引，我放到一个文件里面批量执行，一个索引预估20分钟左右，索引预估需要3个小时左右，看来索引才是大头，导入数据其实没那么麻烦。

2.5 恢复mysql配置文件

最后被忘记将配置文件进行恢复，2.2步骤设置的，容易丢数据，还是双1来的安心。