mysql 大sql文件使用mysqldump备份提速方案

mysql 大sql文件使用mysqldump备份提速方案

mysqldump备份

备份时添加 --extended-insert 将多个insert into 语句合并成一行，能提高效率3-4倍

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

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

使用msyqldump -uroot -pPWD –-extended-insert database >database.sql

调整MYSQL快速插入参数

如果你的数据库储存引擎是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 数据库

msyql -uroot -pPWD
> source /path/bak.sql

恢复数据完成后，获取所有表行数，和原来数据库对比数据差异

生成查询sql

select concat(
    'select "',
    TABLE_name,
    '", count(*) from ',
    TABLE_SCHEMA,
    '.',
    TABLE_name,
    ' union all'
) from information_schema.tables
where TABLE_SCHEMA='database';

复制所有生成的sql ,去掉最后一行结尾处的 union all ，执行查询，等待查询结果

select "call_me_cash_coupon", count(*) from xxx.call_me_cash_coupon union all
select "call_me_com_spe", count(*) from xxx.call_me_com_spe union all
select "call_me_delivery_clerk", count(*) from xxx.call_me_delivery_clerk union all
select "call_me_goods_spec_relation", count(*) from xxx.call_me_goods_spec_relation 

执行效果：

-call_me_cash_coupon	count(*)
call_me_cash_coupon	633
call_me_com_spe	64
call_me_delivery_clerk	4510
call_me_goods_spec_relation	3067