Mysql数据定时备份

￡神魔★判官ぃ 2023-10-02 12:29 19阅读 0赞

## 一、备份准备&备份测试 ##

### 1.备份目录准备 ###

`可自定义`

#mysql专用目录
    mkdir /mysql
    #mysql备份目录
    mkdir /mysql/backup
    #mysql备份脚本
    mkdir /mysql/backup/scripts
    #mysql备份文件
    mkdir /mysql/backup/files
    #mysql备份日志
    mkdir /mysql/backup/logs

### 2.备份脚本准备 ###

#### (1)、脚本内容 ####

`vi /mysql/backup/scripts/backup_full.sh`

#!/bin/bash
    
    # cront进程不会自动加载用户目录下的.profile文件，所以需要加source /etc/profile使环境变量生效
    source /etc/profile
     
    #备份目录
    BACKUP_ROOT=/mysql/backup
    BACKUP_FILEDIR=$BACKUP_ROOT/files
    BACKUP_LOGDIR=$BACKUP_ROOT/logs
     
    #当前日期
    DATE=$(date +%Y%m%d)
     
    ######备份######
     
    #查询所有数据库
    #-uroot -p123456表示使用root账号执行命令，且root账号的密码为:123456
    DATABASES=$(mysql -uroot -p123456 -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 -uroot -pRoot@1024 --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)、如果是Docker启动的MySQL ####

#!/bin/bash
    
    # 进入名字为mysql_good 的docker容器
    docker exec -i 容器名称 /bin/bash <<'EOF'
    # 在docker内建立对应日期的文件夹
    mkdir -p /usr/good/data/mysql/$(date +%Y%m%d)
    # 备份文件到指定文件夹
    mysqldump -u 用户名 密码 数据库名 > /usr/good/data/mysql/$(date +%Y%m%d)/名称_$(date +%Y%m%d_%H%M%S).sql
    # 退出docker容器
    exit
    EOF
    
    # 进入宿主机的备份目录
    cd /home/opuser/back_mysql/files/
    # 将容器内的备份文件拷贝到本地
    docker cp yishu_mysql:/usr/good/data/mysql/$(date +%Y%m%d) /mysql/backup/files
    
    # 备份之后，我们发现容器里还存在导出的数据库，这样当前主机就有两本同样导出的数据库，可以删除容器里的数据库
    docker exec -i 容器名称 /bin/bash <<'EOF'
    #删除容器中导出的数据库
    rm -rf /usr/good/data/mysql/$(date +%Y%m%d)
    #退出docker容器
    exit
    EOF

#### (3)、脚本执行可能报错，解决方法 ####

`mysqldump: Error 1227: Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation when dumping table`log\_status`at row: 0，这是因为没有导出的权限`

# 解决方法（进入mysql数据库）,执行命令
    ## 授权
    grant BACKUP_ADMIN on *.* to 'root'@'%';
    ## 刷新
    FLUSH PRIVILEGES;

### 二、定时任务 ###

#### 1.安装crontab ####

yum install -y crontab

#### 2.创建定时备份任务 ####

`第一步骤写好的脚本backup_full.sh`

#添加定时任务
    crontab -e
    #每天凌晨3点执行
    00 3 * * * sh /mysql/backup/scripts/backup_full.sh
    #查看定时任务
    crontab -l

**定时时间格式**

*  每五分钟执行 \*/5 \* \* \* \*
 *  每小时执行 0 \* \* \* \*
 *  每天执行 0 0 \* \* \*
 *  每周执行 0 0 \* \* 0
 *  每月执行 0 0 1 \* \*
 *  每年执行 0 0 1 1 \*

#### 3.定时清理7天以前的备份 ####

##### (1)、创建文件清理脚本 #####

# 创建脚本文件
    vi /mysql/backup/scripts/backup_full_clean.sh
     
    # 写入以下内容
    #!/bin/bash
    find /mysql/backup/files -mtime +7 -name "*.gz" -exec rm -rf {
        } \;

##### (2)、创建定时清理任务 #####

#添加定时任务
    crontab -e
    #每天凌晨1点执行
    00 1 * * * sh /mysql/backup/scripts/backup_full_clean.sh
    #查看定时任务
    crontab -l