MySQL 使用 mysqldump 备份数据库

目录

前言

lock-tables

single-transaction

单表备份

---

前言

工作中曾经遇到过一个线上的问题：凌晨12点后，公司系统几乎所有的业务都无法正常响应，接口都阻塞住了，而在白天就是正常。由于公司的产品是面向小企业的，主要的用户是各个小企业的员工，下班后基本没人会使用，所以也是被偶然发现，就反馈到了开发这里。

造成这种故障的原因其实很简单，就是运维写了一个定时脚本，每天凌晨都将线上的数据库做一次备份，因为公司使用的数据库是 MySQL，小公司业务量也不大，所以备份直接使用 MySQL 中自带的 mysqldump 来进行逻辑备份，使用 mysqldmp 备份的时候将整个数据库都锁了，故所有的接口里对数据库的写操作都被阻塞了。

lock-tables

备份脚本简化后如下所示

mysqldump --database dbname > /server/backup/mysql_$(date +%F).sql

通过 mysqldump --help 命令，可以看到下面的参数描述

  -l, --lock-tables   Lock all tables for read.
                      (Defaults to on; use --skip-lock-tables to disable.)

也就是说，使用 mysqldump 去备份数据时，lock-tables 参数时默认开启的，即将数据库下所有的表都上了读锁，只能读，无法写，所以就导致了所有的写入数据库操作阻塞，接口也就无法正常响应了。

single-transaction

要解决这个问题，其实很简单，同样在 mysqldump --help 命令下，可以看到

 --single-transaction
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does); the dump is NOT guaranteed to be
                      consistent for other storage engines. While a
                      --single-transaction dump is in process, to ensure a
                      valid dump file (correct table contents and binary log
                      position), no other connection should use the following
                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                      TRUNCATE TABLE, as consistent snapshot is not isolated
                      from them. Option automatically turns off --lock-tables.

使用--single-transaction，则会自动将关闭 --lock-tables 参数，所以在备份期间，除了DDL语句，当前备份的表是可以被正常的读写的。

实现的原理其实就是基于数据快照（MVCC），在对表开始备份时会生成一份数据快照，备份的数据也就是快照里的数据，期间如果对表的数据做了修改，这些修改是不会备份到备份表中的。

MySQL 中只有 InnoDB 引擎才支持 MVCC，实际业务上绝大多数表都是 InnoDB 引擎创建，所以可以使用 --single-transaction 来对数据库进行备份，并且能在备份期间让数据库处于可用状态，但是这样就无法保证表数据的一致性。

修改后的语句如下

mysqldump --database dbname --single-transaction > /server/backup/mysql_$(date +%F).sql

单表备份

使用 --single-transaction 实际上就能够解决问题了，但是运维却反馈说一开始就是使用了 --single-transaction，发现备份的数据有少了，才将 --single-transaction 去掉了。

--single-transaction 确实无法保证备份表数据的一致，因为备份是基于快照数据，在备份期间，是可以进行写库的。如果备份期间有大量的写库操作，那么备份的数据自然就会和源数据会有不一致。

虽然从开发角度来看，这些不一致的数据，交给下一次全量备份就行了。但是运维角度看，期望备份后数据就是要和源数据一模一样，不然就是 mysqldump 有问题，不敢继续用 mysqldump 来备份数据。

运维不想用 --single-transaction 参数，就只能用其他方法了。注意到备份的时候是全库备份，每次备份的时间会比较久，而备份期间又会锁全部表，导致接口响应超时。实际上可以拆开来备份，按表来备份，这样每次备份的时间就大大缩短了。而且看了生产上的表数据，最大的表也就才100万+的数据，测试了单表备份，5秒内都能备份好了，这样即使备份期间锁表，也不会导致接口超时。

脚本如下：

#/bin/sh

BACKUPDIR=/server/backup
DATE=`date +%F`
USER=root
PASSWD=”123456”
CMD=”mysql –u$USER –p$PASSWD”
DUMPCMD=”mysqldump –u$USER –p$PASSWD --single-transaction -F”

for dbname in `${CMD} –e “show databases”|sed ‘1d’`
do
mkdir –p${BACKUPDIR}/${dbname}
for tablename in`${CMD} –D ${dbname} –e “show tables”|sed ‘1d’`
do
${DUMPCMD} --tables${dbname} ${tablename} |gzip > ${BACKUPDIR}/${dbname}/${tablename}_$(DATE).sql.gz
done
done

#删除7天以前的备份文件
find /server/backup/${dbname} -type f –name “*.sql.gz”-mtime +7|xargs rm -f