mysql备份与恢复

博主： Ganhua
发布时间：2022 年 08 月 24 日
1474 次浏览
暂无评论
10751字数
分类： Mysql 全栈笔记

## 为什么要备份

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 日

社会很单纯～复杂滴是人呐～谁能在乎我呀

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

mysql备份与恢复

Ganhua • 2022 年 08 月 24 日

## 为什么要备份

## 定义恢复需求

首先问自己几个问题：

## 增量备份和差异备份

## 安全的清除老的二进制日志

> 需要决定日志的过期策略以防止磁盘被二进制日志写满

## 备份数据

### 逻辑备份

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!'

######备份######

echo "done"
```

2. 符号分割文件备份

```sql
select * from Table into outfile '/路径/文件名'
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
```

### 物理备份

#### Xtrabackup8

### 相关参数说明

> 备份所使用的脚本：`backup_func.sh`

```shell
#!/bin/bash

### 定时任务相关设置

### 恢复（记录Xtrabackup8恢复过程）

1. 解压old文件夹下压缩文件

```shell
tar -zxvf 2022-10-03.tar.gz
```

2. 解压后目录结构

```
yum install tree -y

3. 解压备份
   > 因为备份脚本在备份时使用了`--compress`指令,在恢复备份前,需要先解压缩备份：

```
xtrabackup --decompress --target-dir=/data/full/20221003-023006/
xtrabackup --decompress --target-dir=/data/incr/20221003-040002/
······
```

### 开始恢复

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)

mysql备份与恢复

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

网抑云精彩评论记录

前后端分离敏感数据 RSA或SM2，AES加密-解密

关于我

时间过得真的很快-我的2021

web3j 交易转账的nonce值设置

docker常用命令记录

arweave Java 离线签名与生成地址

回形矩阵-螺旋矩阵

缓存淘汰策略三个代表与Redis内存淘汰策略

go module的基本使用

mysql备份与恢复

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

mysql备份与恢复

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款