1 全量备份与增量备份
1.1 全量备份
全量数据是数据库中所有数据,全量备份是把所有数据进行备份
--- 备份所有数据库的所有数据
mysqldump -B --master-data=2 --single-transaction -A | gzip > /bak/all.sql.gz
--- 备份ocean数据库的所有数据
mysqldump -B --master-data=2 --single-transaction ocean | gzip > /bak/ocean.sql.gz
1.2 增量备份
增量数据是上一次全量备份之后至下一次全量备份之前的更新数据,增量数据是MySQL的binlog日志,因此binlog日志备份可以称之为增量备份。
1.3 全量与增量结合备份
方案一:每日定时全备一次,一天增量数据
周一全量备份 | 周二全量备份 | 周三全量备份 | 周四全量备份 | ...... |
Mon.sql.gz | Tue.sql.gz | Wed.sql.gz | Thur.sql.gz | ...... |
周一增量数据 | 周二增量数据 | 周三增量数据 | 周四增量数据 | ...... |
ocean-bin.000001 ocean-bin.000002 ocean-bin.000003 ocean-bin.000004 ocean-bin.000005 ...... | ocean-bin.000011 ocean-bin.000012 ocean-bin.000013 ocean-bin.000014 ocean-bin.000015 ...... | ocean-bin.000021 ocean-bin.000022 ocean-bin.000023 ocean-bin.000024 ocean-bin.000025 ..... | ocean-bin.000031 ocean-bin.000032 ocean-bin.000033 ocean-bin.000034 ocean-bin.000035 ...... | ...... |
优劣如下
优点:恢复数据时需要的数据少,恢复时间短,维护成本低
缺点:每日一个全备文件,占用系统资源大
方案二:每周定时全备一次,一周增量数据
周一全量备份 | | | | |
week1.sql.gz | | | | |
周一增量数据 | 周二增量数据 | 周三增量数据 | 周四增量数据 | ...... |
ocean-bin.000001 ocean-bin.000002 ocean-bin.000003 ocean-bin.000004 ocean-bin.000005 ...... | ocean-bin.000011 ocean-bin.000012 ocean-bin.000013 ocean-bin.000014 ocean-bin.000015 ...... | ocean-bin.000021 ocean-bin.000022 ocean-bin.000023 ocean-bin.000024 ocean-bin.000025 ..... | ocean-bin.000031 ocean-bin.000032 ocean-bin.000033 ocean-bin.000034 ocean-bin.000035 ...... | ...... |
优劣如下
优点:每周一次全量备份,系统资源占用,备份次数少
缺点:恢复时数据文件多,恢复时间长,维护成本高
总结
1.中小型企业数据库较少,每日全量备份耗时短,能够在非生产时间段完成备份,根据需求保留足够多的数据备份,普遍采用每日全量备份策略
2.大型企业数据量大,每日全量备份耗时长,非生产时间无法完成备份导致系统资源占用高,最终影响客户体验,因此会采用每周全量备份的策略
2 MySQL常用的备份方式
2.1 逻辑备份
MySQL逻辑备份是使用mysqldump把数据以SQL语句的形式导出或备份成文件。恢复数据库通过mysql或source将存储SQL语句的文件数据还原到MySQL数据库中
--- 备份所有数据库
mysqldump -A -B --master-data=2 --single-transaction | gzip > /bak/all.sql.gz
--- 恢复数据库
zcat /bak/all.sql.gz | mysql
--- 恢复增量数据
mysqlbinlog ocean-bin.000001 ocean-bin.000002 > bin.sql
mysql < bin.sql
逻辑备份的优劣
优势:操作简便,可夸平台、版本恢复,可实现分库分表备份
劣势:备份速度慢,恢复效率低
应用场景
备份文件不超过30GB,建议使用逻辑备份。夸平台、版本迁移时,只能使用逻辑备份。
2.2 物理备份
物理备份通常有两种方式,冷备份和热备份。
- 冷备份通过cp、rsync、tar、ftp等工具复制MySQL数据库文件,备份期间数据写入会导致数据丢失,确保数据一致性,数据冷备份期间会停止数据库服务。生产系统是无法频繁停机的,所以这种备份方式很少被应用于生产环境
- 热备份通过热备份工具(Xtrabackup)实现物理全备及增量备份
物理备份的优劣
优势:速度快,效率高
劣势:不易跨平台、版本,能够实现分库分表备份,但恢复复杂度高
应用场景
备份数据超过30GB,建议使用Xtrabackup备份,以提高备份效率
3 逻辑备份与物理备份对比
| 逻辑备份 | 物理备份 |
备份原理 | SQL语句的形式存储 | 复制物理文件、非SQL语句的备份文件 |
备份工具 | mysqldump、mysql、mysqlbinlog | cp、rsync、tar、ftp、Xtrabackup |
满足要求 | 锁表,不停库 | 冷备份:锁表或停库 热备份:事务备份方案 |
配置要求 | 恢复目标库,无需考虑系统版本、mysql版本、数据库配置等 | 物理复制需要系统、配置、版本一致 |
性能分析 | 速度慢 | 速度快 |
易用性 | 安全、易操作、不易丢失数据 | 冷备份:易操作、应用场景极少 热备份:操作复杂,难度高 |
4 逻辑备份的应用场景
4.1 中小企业数据备份
使用mysqldump备份脚本
#!/bin/bash
export PATH=/app/mysql5.7/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/bak
[ ! -d $bak_path ] && mkdir -p $bak_path
mysqldump -B -A --master-data=2 | gzip > $bak_path/${file_name}.sql.gz
#rsync all data to backup server
rsync -az $bak_path/ rsync_backup@node2::mysql/ --password-file=/etc/rsync.password
#del expiries file
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7 | xargs rm -f
复杂脚本
#!/bin/bash
export PATH=/app/mysql5.7/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/bak
[ ! -d $bak_path ] && mkdir -p $bak_path
#if wiik is 6 ten bak other file name.
if [ $(date +%w) -eq 6 ]
then
file_name=bak_$(date +%w_%F)
else
file_name=bak_$(date +%F)
fi
mysqldump -B -A --master-data=2 | gzip > $bak_path/${file_name}.sql.gz
md5sum $bak_path/${file_name}.sql.gz > $bak_path/${file_name}.flag
#rsync all data to backup server
rsync -az $bak_path/ rsync_backup@node2::mysql/ --password-file=/etc/rsync.password
#del expiries file
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7 | xargs rm -f
参考 http://edu.51cto.com/course/course_id-8198.html
备份策略建议
MySQL数据库配置一主多从的架构,采取在一个从服务器做全量、增量备份。备份方案可选mysqldump或Xtrabackup。
4.2 增量恢复
完全恢复数据的必要条件
- 具备全量备份(mysqldump)
- 全量备份之后产生的所有binlog日志
--- 停止数据服务端口
iptables -I INPUT -p tcp --dport 3306 ! -s 10.6.0.243 -j DROP
仅10.6.0.243可以访问数据库
--- 解压全量备份
gzip -cd all.sql.gz > all.sql
--- 解析binlog文件
sed -n '22p' all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='ocean-bin.000006',MASTER_LOG_POS=6658;
--- ocean-bin.000006的6658位置恢复
mysqlbinlog -d ocean ocean-bin.000006 --start-position=6658 -r bin.sql
mysqlbinlog -d ocean ocean-bin.000007 ocean-bin.000008 -r bin.sql
--- 踢出误操作语句
grep -w drop bin.sql
sed -i '/drop database ocean/d' bin.sql
--- 恢复全量备份
mysql < /bak/all.sql
--- 恢复增量备份
mysql ocean < /bak/bin.sql
5 分库分表备份
实际生产环境中,一个实例中可能存在多个生产库。恢复时可能仅需要一个库,甚至某套库中的某个表,这时就需要分库分表备份
5.1 分库备份
抽取实例中所有库名,对每个库执行备份
#!/bin/bash
export PATH=/app/mysql5.7/bin:/usr/local/bin:/usr/sbin/usr/bin
bak_path=/bak/$(date +%F)
[ ! -d $bak_path ] && mkdir -p $bak_path
for dbname in `mysql -e "show databases" | sed '1,2d' | grep -v _schema`
do
mysqldump -B --master-data=2 | gzip > $bak_path/${dbname}_$(date +%F).sql.gz
done
5.2 分表备份
循环数据库中的所有表执行备份
#!/bin/bash
export PATH=/app/mysql5.7/bin:/usr/local/bin:/usr/sbin/usr/bin
bak_path=/bak/$(date +%F)
[ ! -d $bak_path ] && mkdir -p $bak_path
for dbname in `mysql -e "show databases" | sed '1,2d' | grep -v _schema`
do
for tablename in `mysql -e "show tables from $dbname;" | sed 'id'`
do
mysqldump -B --master-data=2 | gzip > $bak_path/${dbname}_${tablename}_$(date +%F).sql.gz
done
done
注:分库分表备份,恢复单库很方便,不建议用于完整恢复。binlog会有写入操作,破坏数据一致性
6 生产备份架构
一般生产环境多是一主多从架构,在不对外提供服务的从库上配置备份策略。备份策略一般是周日凌晨2点执行全量备份,然后依据日期推送至异地备份服务器,备份保留有效期为30天。