Loading... ## 为什么要备份 1. **灾难恢复** 硬件故障,不经意的bug导致等等。 2. **审计** 有时候需要知道数据或Schema在过去的某个时间点是什么样的。 3. **测试** 定期用最新的生产环境数据更新测试服务器。 ## 定义恢复需求 首先问自己几个问题: 1. 在不导致严重后果的情况下,可以容忍丢失多少数据?需要故障恢复,还是可以直接受自从上次日常备份后所有的工作全部丢失?是否有法律法规的要求? 2. 恢复需要在多长时间内完成?哪种类型的宕机是可以接受的?哪种影响是用户可以接受的?备份时哪种场景发生时,又该如何持续服务? 3. 需要恢复什么?常见的需求时恢复整个服务器,单个数据库,单个表,或仅仅是特定的事务或语句。 ## 增量备份和差异备份 > 当数据量很庞大的时候,一个常见的策略就是做定期的增量或差异备份。 > 差异备份是对自上次全备份后所有改变的部分而做的备份; > 增量备份则是自从任意类型的上次备份后所有修改做的备份。 ## 安全的清除老的二进制日志 > 需要决定日志的过期策略以防止磁盘被二进制日志写满 常见的设置是使用expire_logs_days变量告诉MySQL定期清理日志。 expire_logs_days设置在服务器启动或者刷新二进制日志时生效,因此二进制日志没有增长或者没有主动触发,服务器都不会清除老二进制文件。 ## 备份数据 ### 逻辑备份 1. mysqldump ```sh mysqldump -h主机名 -P端口 -u用户名 -p密码 参数1,参数2.... > 备份文件.sql ``` > 备份所使用的脚本:`dump_func.sh` ```shell #!/bin/bash #备份目录 BACKUP_ROOT=/mysql/backups BACKUP_FILEDIR=$BACKUP_ROOT/files BACKUP_LOGDIR=$BACKUP_ROOT/logs #当前日期 DATE=$(date +%Y%m%d) DB_USER='bkpuser' DB_PASS='Bkp@123456!' ######备份###### #查询所有数据库 DATABASES=$(mysql -u${DB_USER} -p${DB_PASS} -e "show databases" | grep -Ev "Database|sys|information_schema") echo $DATABASES #循环数据库进行备份 for db in $DATABASES do echo echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN---------- mysqldump -u${DB_USER} -p${DB_PASS} --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE---------- echo done echo "done" ``` 2. 符号分割文件备份 ```sql select * from Table into outfile '/路径/文件名' fields terminated by ',' enclosed by '"' lines terminated by '\r\n' ``` ### 物理备份 #### Xtrabackup8 ### 相关参数说明 * `--target-dir` 存储位置 * `--defaults-file `指定配置文件,必须作为命令行第一个命令 * `--databases` 指定备份的数据库和表 如果此选项不被指定将会备份所有数据库 格式如:--databases="db1[.table1] db2[.table1]" 多个库用空格隔开 * `--include` 用正则表达式的方式指定备份的数据库和表 * `--tables-file` 该选项是一个文件 该文件中包含一个要备份的表的完整名称 格式为:databasename.tablename * `--compact` 创建紧凑型备份 忽略所有辅助索引页 只备份data page通过--apply-log重建索引 * `--compress` 指示压缩备份的innodb文件生成 会生成*.qp 文件 * `--decompress` 解压缩qp文件 该选项必须敢撞qpress工具 * `--no-timestamp` 指定该备份会直接存储在BACKUP-DIR目录,不在创建时间戳文件 * `--apply-log` 一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务货已经提交事务尚未同步至数据文件中的事务。该命令主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件处于一致性状态 * `--use-memory` 接受一个字符参数(1M,1G,默认100M),仅与--apply-log一起使用,该选项指定prepare时用于崩溃恢复(crash-recovery)的内存 * `--copy-back` 拷贝先备份所有文件到它原始路径,但原始路径不能有任何文件或目录,除非指定 --force-non-empty-directories选项 * `--force-non-empty-directories` 恢复时指定此选项 可以使--copy-back 和 --move-back 复制到非空目录 但是不能有与恢复文件中同名的文件,否则恢复失败。 * `--rsync` 此选项可优化本地文件的传输 * `--incremental ` 创建一个增量备份而不是完全备份 当指定这个选项可以设置 --incremental-lsn 或 --incremental-basedir,如果这两个选项都不被指定,--incremental-basedir默认值为基础备份的第一个时间戳备份目录 * `--incremental-dir` 该选项接受一个字符串参数 ,该参数指定了一个增量备份与完整备份相结合的目录,一遍进行新的完整备份 * `--redo-only` 在准备基本完整备份和合并所有的增量备份时使用此选项 * `--parallel` 此选项接受一个整数参数,指定xtrabackup子进程应用同时备份文件的线程数 > 备份所使用的脚本:`backup_func.sh` ```shell #!/bin/bash # 备份相关基础路径(必填!!!) BACKUPDIR=/data/backups/mysql # 全量备份目录 Full=$BACKUPDIR/full # 增量备份目录 Incr=$BACKUPDIR/incr # 备份归档路径 Old=$BACKUPDIR/old # 日志路径 baklog=$BACKUPDIR/backup.log # 日期信息,用于区分当天备份用于归档 # TODAY=$(date +%Y%m%d%H%M) YESTERDAY=$(date -d"yesterday" +%Y-%m-%d) # Mysql实例相关信息(必填!!!) MYSQL=/usr/bin/mysql MYSQLADMIN=/usr/bin/mysqladmin DB_HOST='localhost' DB_PORT=3306 DB_USER='bkp' DB_PASS='123456' DB_SOCK=/var/lib/mysql/mysql.sock DB_CONF=/etc/my.cnf # 备份必备工具检查(压缩备份需要qpress) tools="percona-xtrabackup-80 qpress" # Check packages before proceeding for i in $tools; do if ! [[ $(rpm -qa $i) =~ ${i} ]]; then echo -e " 执行脚本所需 ${i} 包未找到.\n 预检查失败!!!" # 询问是否下载安装脚本 if [ "$i" = "qpress" ]; then read -p "是否自动安装所需qpress [Y/N]?" bol if [ -n "$bol" ] || [ "y" = "$bol" ] || [ "Y" = "$bol" ]; then wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm if [ $? -eq 0 ]; then rpm -ivh qpress-11-1.el7.x86_64.rpm else echo -e "安装失败!!!" fi fi fi exit 1 fi done # mysql 运行状态监测 if [ -z "$($MYSQLADMIN --host=$DB_HOST --socket=${DB_SOCK} --user=$DB_USER --password=$DB_PASS --port=$DB_PORT status | grep 'Uptime')" ]; then echo -e "HALTED: MySQL似乎没有运行,或者用户名和密码不正确" exit 1 fi # 备份用户名密码监测 if ! $(echo 'exit' | $MYSQL -s --host=$DB_HOST --socket=${DB_SOCK} --user=$DB_USER --password=$DB_PASS --port=$DB_PORT); then echo -e "HALTED: Supplied mysql username or password appears to be incorrect (not copied here for security, see script)." exit 1 fi #################################################### #归档备份函数(全量时自动触发) #################################################### function Xtr_tar_backup() { # if [ ! -d "${Old}" ]; then # mkdir ${Old} # fi for i in $Full $Incr $Old; do if [ ! -d $i ]; then mkdir -pv $i fi done # 压缩上传前一天的备份 echo "压缩前一天的备份,移动到${Old}" cd $BACKUPDIR tar -zcvf $YESTERDAY.tar.gz ./full/ ./incr/ #scp -P 8022 $YESTERDAY.tar.gz root@192.168.10.46:/data/backup/mysql/ mv $YESTERDAY.tar.gz $Old if [ $? = 0 ]; then rm -rf $Full $Incr echo "Tar old backup succeed" | tee -a ${baklog} 2>&1 else echo "Error with old backup." | tee -a ${baklog} 2>&1 fi } #################################################### #全量备份函数(手动触发) #################################################### function Xtr_full_backup() { if [ ! -d "${Full}" ]; then mkdir ${Full} fi Xtr_tar_backup # 第一步 创建本次的备份目录 FullBakTime=$(date +%Y%m%d-%H%M%S) mkdir -p ${Full}/${FullBakTime} FullBakDir=${Full}/${FullBakTime} # 第二步 开始全量备份 echo -e "备份时间: ${FullBakTime}\n" | tee -a ${baklog} 2>&1 echo -e "本次全量备份目录为 ${FullBakDir}\n" | tee -a ${baklog} 2>&1 xtrabackup --defaults-file=${DB_CONF} --host=${DB_HOST} --port=$DB_PORT --user=${DB_USER} --password=${DB_PASS} --socket=${DB_SOCK} --backup --compress --compress-threads=4 --target-dir=${FullBakDir} dirStorage=$(du -sh ${FullBakDir}) echo -e "本次备份数据 ${dirStorage}\n" | tee -a ${baklog} 2>&1 echo -e "备份完成...\n\n\n" | tee -a ${baklog} 2>&1 exit 0 } #################################################### #增量备份函数(手动触发) #################################################### function Xtr_incr_backup() { # 第一步 获取上一次全量备份和增量备份信息 LATEST_INCR=$(find $Incr -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1) LATEST_FULL=$(find $Full -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1) if [ ! $LATEST_FULL]; then echo "xtrabackup_info does not exist. Please make sure full backup exist." exit 1 fi echo "LATEST_INCR=$LATEST_INCR" if [ ! -d "${Incr}" ]; then mkdir ${Incr} fi # 判断上一次的备份路径,如果增量备份路径为空,则使用全量备份路径为--incremental-basedir if [ ! $LATEST_INCR ]; then CompliteLatestFullDir=$LATEST_FULL else CompliteLatestFullDir=$LATEST_INCR fi # 第二步 创建备份目录 IncrBakTime=$(date +%Y%m%d-%H%M%S) mkdir -p ${Incr}/${IncrBakTime} IncrBakDir=${Incr}/${IncrBakTime} # 第三步 开始增量备份 echo -e "日期: ${IncrBakTime}\n" | tee -a ${baklog} 2>&1 echo -e "整点: ${Hour}\n" | tee -a ${baklog} 2>&1 echo -e "本次备份为基于上一次备份${CompliteLatestFullDir}的增量备份\n" | tee -a ${baklog} 2>&1 echo -e "本次增量备份目录为: ${IncrBakDir}\n" | tee -a ${baklog} 2>&1 xtrabackup --defaults-file=${DB_CONF} --host=${DB_HOST} --port=$DB_PORT --user=${DB_USER} --password=${DB_PASS} --socket=${DB_SOCK} --backup --compress --compress-threads=4 --parallel=4 --target-dir=${IncrBakDir} --incremental-basedir=${CompliteLatestFullDir} dirStorage=$(du -sh ${IncrBakDir}) echo -e "本次备份数据 ${dirStorage}\n" | tee -a ${baklog} 2>&1 echo -e "备份完成...\n\n\n" | tee -a ${baklog} 2>&1 exit 0 } #################################################### #主体备份函数 #################################################### function printInfo() { echo "Your choice is $1" } case $1 in "full") echo "Your choice is $1" Xtr_full_backup ;; "incr") echo "Your choice is $1" Xtr_incr_backup ;; *) echo -e "No parameters specified!\nFor example:\n$0 full\n$0 incr" ;; esac exit 0 ``` ### 定时任务相关设置 ```shell crontab -e # 每天两点半做一次全量备份 30 2 * * * /data/backups/backup_func.sh full # 两点到22点每两小时增量备份一次 0 2-22/2 * * * /data/backups/backup_func.sh incr # 每周六凌晨3点逻辑备份一次 * * 2 * * 6 /data/backups/dump_func.sh ``` ### 恢复(记录Xtrabackup8恢复过程) 1. 解压old文件夹下压缩文件 ```shell tar -zxvf 2022-10-03.tar.gz ``` 2. 解压后目录结构 ``` yum install tree -y full | `-- 20221003-023006 `-- incr |-- 20221003-040002 |-- 20221003-060002 |-- 20221003-080002 |-- 20221003-100002 |-- 20221003-120002 |-- 20221003-140002 |-- 20221003-160002 |-- 20221003-180002 |-- 20221003-200002 |-- 20221003-220002 `-- 20221004-020002 ``` 3. 解压备份 > 因为备份脚本在备份时使用了`--compress`指令,在恢复备份前,需要先解压缩备份: ``` xtrabackup --decompress --target-dir=/data/full/20221003-023006/ xtrabackup --decompress --target-dir=/data/incr/20221003-040002/ ······ ``` 4. 准备备份 *数据文件在准备好之前不是时间点一致的,因为它们是在程序运行时在不同时间复制的,并且在发生这种情况时它们可能已被更改。* 在同时存在全量和增量备份需要合并的情况下,准备备份时需要带上`--apply-log-only`参数,<span style='color:#FF0000'>但是要注意在准备最后一个增量备份的时候,不需要加该参数.</span> ```shell xtrabackup --prepare --apply-log-only --target-dir=/data/full/20221003-023006/ xtrabackup --prepare --apply-log-only --target-dir=/data/full/20221003-023006/ --incremental-dir=/data/incr/20221003-040002/ xtrabackup --prepare --apply-log-only --target-dir=/data/full/20221003-023006/ --incremental-dir=/data/incr/20221003-060002/ ...... xtrabackup --prepare --target-dir=/data/full/20221003-023006/ --incremental-dir=/data/incr/20221004-020002/ ``` ### 开始恢复 1. 关闭mysql ```shell systemctl stop mysql ``` 2. 备份旧数据文件 ```shell mv -f /data/mysql/data/ /tmp/data mv -f /data/mysql/logs/ /tmp/logs mv -f /data/mysql/binlogs/ /tmp/binlogs ``` 3. 拷贝完全准备好的数据库文件 如果my.cnf不是在默认路径(/etc/my.cnf),需要指定一下mysql配置文件的路径:`--defaults-file=${DB_CONF}` ```shell xtrabackup --copy-back --target-dir=/home/full/20221003-023006/ ``` 4. 修改数据路径的文件权限 ```shell chown mysql:mysql -R /data/mysql/ ``` 5. 启动MySQL ```shell systemctl start mysql ``` ### 其他 #### 加密备份 ```shell xtrabackup --backup --encrypt=AES256 --encrypt-key="" ``` 相关文档:[https://docs.percona.com/percona-xtrabackup/8.0/index.html](https://docs.percona.com/percona-xtrabackup/8.0/index.html) 最后修改:2022 年 10 月 16 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 社会很单纯~复杂滴是人呐~谁能在乎我呀