MySQL 高级知识之使用 mysqldump 备份和恢复

一 前言

mysqldump是 MySQL 自带的备份工具，它可以实现对 MyISAM 表的温备份和对 innodb 表的热备份。但是它的还原速度很慢，在备份小型数据库时，使用很方便。

二 数据库备份类型

热备份 温备份 冷备份

• 热备份：数据读写不受影响
• 温备份：仅可以执行读操作
• 冷备份：离线备份、读写均终止

物理备份 逻辑备份

• 物理备份：复制数据文件
• 逻辑备份：将数据导出成文本文件

完全备份 增量备份 差异备份

• 完全备份：备份所有数据
• 增量备份：仅备份上次完全备份或增量备份变化的数据
• 差异备份：仅备份上次完全备份以来所有变化的数据

三 数据库备份

3.1 备份全库

# 备份全库
mysqldump -h192.168.1.11 -uroot -p123456 --all-databases > /opt/#{DATE}.sql

3.2 备份单库

# 备份单库
mysqldump -h192.168.1.11 -uroot -p123456 db-name > /opt/#{DATE}.sql
 
# 还原单库
mysql -uroot -p123456 db-name </opt/2021-11-12_10_29.sql

3.3 排除部分库备份

mysql -uroot -p'123456' -N -e "show databases;"|grep -Ev "information_schema|performance_schema|sys|mysql|database1"|xargs mysqldump -uroot -p'123456' --databases > /opt/#{DATE}.sql

3.4 备份脚本

创建备份脚本：vim backup.sh

#!/bin/bash
#备份 MySQL 数据库
db_user=root
db_password=123456
# 排除库名称
db_names="information_schema|mysql|performance_schema|sys"
echo "MySQL 数据库备份开始时间：...............$(date "+%Y-%m-%d %H:%M:%S")">>/home/project/backup-mysql/logs.log
mysql -u$db_user -p$db_password -N -e "show databases;"|grep -Ev $db_names|xargs mysqldump -u$db_user -p$db_password --databases |gzip > /home/project/backup-mysql/mysql-db-$(date "+%Y-%m-%d").sql.gz
echo "MySQL 数据库备份结束时间：...............$(date "+%Y-%m-%d %H:%M:%S")">>/home/project/backup-mysql/logs.log

3.5 自动化备份脚本

创建备份脚本：vim backup.sh

#!/bin/bash
# 定义备份用户/密码
DbUser=root
DbPasswd=123456
 
# 定义备份数据库
DbName=("neworg_prod_project" "prod_project")
# 定义备份目录
Path=/home/project/backup-mysql/data
# 定义备份数据保存天数 Mtime=3+1 天数也就是4天
Mtime=3
# 定时任务日志
LogPath=/home/project/backup-mysql/db_backup.log
 
Time=$(date +%F)
CurrentPath=$(pwd)
 
if ! [ -d ${Path} ];then
    mkdir ${Path}
fi
 
for i in ${DbName[@]};do
    if [ -d ${Path}/${Time} ];then
        cd ${Path}/${Time}
    else
        mkdir ${Path}/${Time} && cd ${Path}/${Time}
    fi
          mysqldump -f -x  -u${DbUser} -p${DbPasswd} ${i} >${i}.sql
                  zip ${i}.zip ${i}.sql
                  rm -f ${i}.sql
done
 
cd ${Path} && find ${Path} -name '*.sql' -mtime +${Mtime}|awk -F"/" '{print $3}'|xargs rm -rf
 
# 定时任务凌晨1点 自动添加
if ! (grep -r ${CurrentPath}/${0} /var/spool/cron/root &>/dev/null);then
        echo >> /var/spool/cron/root
        echo "#DBServer: Backup Database" >> /var/spool/cron/root
    echo "0 0 1 * * ? bash ${CurrentPath}/${0} >> ${LogPath}" >> /var/spool/cron/root
fi