今天一个做Java开发的哥们向我咨询了一个问题。
他想把一个300多万条数据数据的表迁移到本地测试环境,他处理的方案是 先通过mysqldump命令将该表做一个备份,然后传输到测试环境,最后执行备份文件。
理想很丰满,现实很骨感,300多万的数据不大,备份下来也就2.5GB左右的sql文件,可是在测试环境居然执行了12个小时才执行了一半,无奈只能终止。
于是这个哥们开始咨询我,希望我能提供一个快速一点的方法。
既然是批量数据的迁移,那就肯定想到调整参数和索引相关,于是从这两个方向着手。
将 dump.sql文件中除id列主键索引外的所有索引都去掉,数据同步完成后再创建索引。
我调整的参数如下:
-- 默认值是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
重新导入数据,速度快了很多,不到半个小时,300多万的数据都导入成功了。
因为表有十多个索引,我放到一个文件里面批量执行,一个索引预估20分钟左右,索引预估需要3个小时左右,看来索引才是大头,导入数据其实没那么麻烦。
最后被忘记将配置文件进行恢复,2.2步骤设置的,容易丢数据,还是双1来的安心。