mysqldump备份不加–single-trantscation参数重大影响和mysql或者mariadb备份建议
mysqldump备份不加–single-trantscation参数现象:
某天凌晨某系统管理员打电话说,他们数据库感觉hang住了,想了解下为什么会出现这样的情况:
备注:其系统使用mariadb数据库
那查下数据库会话状态吧
再查下当前数据库正在执行的事务吧
看现象目前正在备份数据库,咨询管理员确认正在使用mysqldump备份数据,此时相关的插入、删除、load、trnucate动作都在处于锁等待状态,正是mysqldump备份把锁住了所有业务表,直到备份完成才释放相关锁,这就带来不好影响,如果是交易系统瞬间大量请求处于等待状态,数据库会话暴增。还好此系统是非实时交易系统,不会影响实时业务,但是因为数据库数据量较大,整个备份时间大约3个小时,还是影响了其系统夜间跑批延迟。
备份脚本核心备份语句如下:
mysqldump -uroot -p非明文密码
-P xxxx -hlocalhost -A | gzip -9 >/xx/备份集
优化–对mysqldump备份加–single-trantscation参数注意事项:
:
对于mysqldump 备份,为了避免锁表带来长时间数据库锁状态,那么加–single-trantscation参数,但是有没有其他影响呢?
查mariadb mysqldump说明:
在使用–single-trantscation备份期间,应该隔离 ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE动作,可能会造成备份失败或者备份数据不可用。
看上面的会话, truncate table fm_pos_merchant_trade_report ,此动作每晚定时执行,经系统管理员和项目组沟通,将备份时间窗口和此动作分开时段进行。
然后再运行加–single-trantscation参数的备份命令:
备份脚本核心备份语句如下:
mysqldump -uroot -p非明文密码
-P 5518 -hlocalhost --single-trantscation -A | gzip -9 >/xx/备份集
另外–single-transaction选项和该 --lock-tables选项是互斥的,请注意。
mysql或者mariadb数据库备份几点建议:
1、对于使用mysql或者mariadb系统备份数据库优先选择通过备份软件meb或者XtraBackup等来完成备份。
2、对于使用mysql或者mariadb系统使用mysqldump备份,如果是7x24小时系统,务必加–single-transaction参数,如果有从库建议在从库上面备份。
3、对于使用mysql或者mariadb系统使用mysqldump备份,如果是非7x24小时系统,不加–single-transaction参数,那么请避开日常业务和批处理时间进行备份。