关于Mysql-大型SQL文件快速恢复方案

jiezi

5 年前

在使用 Mysql 数据库的过程中, 经常需要使用到备份和恢复数据库, 最简单便捷的方法便是通过导出 SQL 数据文件和导入 SQL 数据文件来完成备份和恢复, 但是随着项目的增长, 数据量越来越大, 每次恢复就成了一件很头疼的事情。

当我最近一次拉下项目中的 5GB 大小的数据库到本地进行恢复时, 竟然需要耗时 40-50 分钟, 想着日后的数据扩增, 数据量越来越大, 恢复成本也越来越高, 于是便查阅了一些资料, 可以通过以下设置来提高你的恢复效率.

首先我们需要在备份数据库的时候, 可以通过更改参数来提高我们的恢复效率.

mysqldump --extended-insert

mysqldump 的 --extended-insert 参数表示批量插入, 会将多个 insert 语句合并成一个语句, 与没有开启 -extended-insert 的备份导入效率相差 3 - 4 倍.

使用 –extended-insert=false 导出的 sql 文件数据是这样的, 每行一条 insert 语句, 执行效率非常低下

使用 –extended-insert=true 导出的表如下图这种，一个很长的 insert 语句, 会进行批量插入。

如果你的数据库储存引擎是 MYISAM 参数的话, 可以将此参数设置到 512M 或 256M,MyISAM 会使用一种特殊的树状缓存来做出更快的批量插入。

相关文档 https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_bulk_insert_buffer_size

该值默认是 8M = 8388608byte

查看插入缓冲区大小

SHOW VARIABLES LIKE '%bulk%';

设置插入缓冲区大小 (全局)

SET GLOBAL bulk_insert_buffer_size =1024*1024*512;

设置插入缓冲区大小 (session)

SET bulk_insert_buffer_size =1024*1024*256;

如果需要设置 Mysql 重新启动时, 依然保留该值, 需要将这段配置添加了 my.cnf

[mysqld]
bulk_insert_buffer_size = 256M

对于 Innodb 引擎中, 我们可以关闭一些系统检查项来实现更快的插入的方案.

// 关闭自动提交
SET autocommit=0;

// 关闭唯一检查
set unique_checks = 0;

// 关闭外键检查
SET foreign_key_checks=0;

// 备份的时候开启 --extended-insert 参数

关于 Innodb 批量数据加载相关文档:https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-bulk-data-loading.html

做好以上优化后, 你的 Mysql 恢复效率瞬间会提升一个档次, 在没做以上参数优化时, 每次恢复数据库都需要耗费 40 分钟的时间, 设置后只需要 16 分钟左右, 我的数据库文件容量在 5GB 左右.

以上就这些, 途观有更好的方案和建议的话, 希望各位同学一起探讨,Happy Coding。

1. 更改备份参数