加快MySQL逻辑恢复速度的方法和参数总结

MYSQL临时参数设置方法: (举例实时打印SQL日志方法)

mysql> SET GLOBAL general_log = 'ON'; //临时开启实时记录SQL语句
mysql> SET GLOBAL general_log_file = '/usr/local/mysql/var/general_log.log'; //指定记录文件

    关闭临时实时记录SQL语句

mysql> SET GLOBAL general_log = 'Off';

ps:此方法只是临时开启,mysql重启之后就会失效。

MYSQL永久开启参数方法:(举例实时打印SQL日志方法)

vim /etc/my.cf

    在配置文件添加一下内容:

general_log = 1
general_log_file = /usr/local/mysql/var/general_log.log

    然后重启mysql服务

service mysql restart

 

MYSQL检查参数方法mysql> SHOW VARIABLES like 'genera%';   (like过滤按需要过滤)

用SHOW VARIABLES命令检查配置项的设置值:比如下面SHOW VARIABLES like 'genera%'    显示实时打印是否开启,     general_log | OFF  则为OFF,执行SET GLOBAL general_log = 'ON';命令后,再次检查会发现general_log | ON    表示生效了。

mysql> SHOW VARIABLES like 'genera%';
+------------------+-----------------------------+
| Variable_name | Value |
+------------------+-----------------------------+
| general_log | OFF |
| general_log_file | /opt/mysql/data/Storage.log |
+------------------+-----------------------------+

 

下以下参数可根据以上方法临时或永久开启。    注意数据安全,为了加速还原后恢复原来设置。

测试了一下用命令方式关闭log-bin=OFF无效,必须到my.cnf里去 #注释log-bing才有效。

进入mysql的配置文件 cd /etc/my.cnf  注释掉2行:

 #log-bin=mysql-bin

#binlog_format=mixed
 
 

日常工作中经常会有需要从mysqldump导出的备份文件恢复数据库的情况,相比物理备份恢复这种方式在恢复时间上往往显得力不从心。

本文就总结了几个对于逻辑备份恢复有加速作用的参数和操作

注意:我们的大前提是,恢复的目标数据库在恢复完成前,没有对外部提供服务

1. 参数调整

log-bin=OFF

恢复时开启二进制日志显然是无意义的,增加了不必要的IO。因此关闭该选项

 

sync_binlog=0

如果关闭了二进制日志,则这个选项不调整。

但如果因为特殊原因不能关闭二进制日志时,可以考虑减少binlog的fsync来减少磁盘IO压力。

 

Innodb_buffer_pool_size 尽可能大

尽可能大的配置Innodb_buffer_pool_size 来保证更多的脏页能够存在于BP中,增大潜在的写入合并的可能性,从而减少了磁盘的IO。

 

Innodb_logfile_size=1G 或更大

增大redolog的体积可以推迟blocking checkpoint发生的时间,也一定程度缓解adaptive flush的刷写频率。

调整这个参数对于恢复表体积远大于Innodb Buffer Size时非常有用。

 

Innodb_doublewrite=OFF

由于不存在宕机风险(即使宕机,也就是重新再恢复一次),所以doublewrite也可以不需要了。

 

Innodb_flush_log_at_trx_commit=0

同上,由于不存在宕机风险,无需那么卖力的刷写redo log。

 

Innodb_flush_neighbors=1

由于逻辑导入更多的是顺序写入,打开flush neighbor以后不单能把IO pattern更贴近顺序。同时,innodb的内部逻辑也会把多个page合并成一次IO进行提交,刷写性能更高。

posted @ 2020-10-01 23:16  zxjnb  阅读(279)  评论(0编辑  收藏  举报