mysql在线备份遇到的问题总结

mysql在线备份遇到的问题总结

前提条件：
为客户部署一套我们公司开发的系统，由于给的机器数量有限，没有条件搭建主从环境，所以选择在生产环境晚上定时备份数据库

1、最开始使用mysql jump导出数据，由于数据量越来越大，发现备份的时间越来越长，并且恢复起来更慢；

mysqldump -u$MYSQL_USER -p$MYSQL_PASS --default-character-set=utf8 --single-transaction -t --skip-opt -q -a databasesName > databasesName.sql

2、后来选用了innobackupex进行全量备份

innobackupex  --databases="databasesName --defaults-file=$MYSQL_CNF --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USER --password=$MYSQL_PASS  --no-timestamp --parallel=4 --stream=tar $BACKUP_DIR 2> $BACKUP_DIR/mysqlXtraBackup.log | pigz -p 4 > $BACKUP_DIR/mysqlXtraBackup_databasesName.tar.gz

使用innobackupex导出很快，并且恢复起来比较方便；
innobackupex备份原理：

1. 首先调用xtrabackup来备份innodb数据文件，当xtrabackup完成后，innobackupex就查看文件xtrabackup_suspended；
2. 然后执行“FLUSH TABLES WITH READ
   LOCK”将阻止新的写入并且让myisam引擎的表数据刷到硬盘上，然后开始复制myisam引擎的数据表；

可上两天突然发现从半夜开始所有的应用服务都用不了了，日志显示都是在等待数据库响应

在数据库上show processlist；

发现大部分进程状态都是waiting for global read lock

并且有很多慢查询还在执行，持续时间已经很长了

还有一个进程执行的是flush table with read lock，状态是waiting for table flush

至此总算真相大白：

1. 由于系统中的应用存在很多慢查询，晚上有不少汇总任务导致的慢查询；

2. innobackupex 执行FLUSH TABLES WITH READ LOCK时，由于数据库中存在慢查询或者长事务，导致table flush阻塞，需要等待所有的慢查询或者长事务执行完；

3. flush table with read lock执行后本身就会阻塞所有写入操作，但是由于flush table with read lock也被阻塞了，就导致所有的读操作也被阻塞；所以数据库所有的操作都被阻塞，应用全部在等待数据库响应;

   解决方案：

   1.应用尽量优化慢查询，长事务操作
   2.调整备份时间至访问量最小的时刻
   3.根本解决方案还是搭建主从数据库，在从库上执行备份操作，备份时暂停同步操作