mysql 使用mysqldump备份数据

一、mysqldump备份

使用mysql_config_editor设置凭据

# 设置登录凭据
mysql_config_editor set --login-path=local --host=localhost --user=yourusername --password
# 在提示时输入密码

1、备份所有数据库

mysqldump --login-path=local --all-databases > /data/mysql/all.db

2、备份指定数据库

mysqldump --login-path=local test2 > /data/mysql/test2.db

3、备份指定数据库指定表

mysqldump --login-path=local mysql db event > /data/mysql/table2.db

4、编写sh脚本定时备份(全表)

#!/bin/bash

# 定义备份目录
BACKUP_DIR="/data/mysql"

# 获取当前时间，并格式化为年月日时分秒
TIMESTAMP=$(date +%Y%m%d%H%M%S)

# 格式化备份文件名
BACKUP_FILE="${BACKUP_DIR}/${TIMESTAMP}.db"

# 使用mysqldump备份所有数据库
mysqldump --login-path=local --all-databases > "${BACKUP_FILE}"

if [ $? -eq 0 ]; then
    echo "备份成功完成: ${BACKUP_FILE}"
else
    echo "备份失败!"
    exit 1
fi

# 保留最近60天的备份文件
find "${BACKUP_DIR}" -name "*.db" -type f -mtime +60 -exec rm -f {} \;

# 输出示例
echo "创建的备份文件名称为 ${TIMESTAMP}.db"
echo "旧的备份(超过60天)已被删除。"

5、编写sh脚本定时备份(单表)

#!/bin/bash

# 定义备份目录
BACKUP_DIR="/data/mysql"

# 定义备份的数据库
BACKUP_DBNAMES=("test" "test2")

# 确保备份目录存在
if [ ! -d "$BACKUP_DIR" ]; then
    mkdir -p "$BACKUP_DIR"
    echo "备份目录 $BACKUP_DIR 已创建。"
fi

# 遍历每个数据库进行备份
for DBNAME in "${BACKUP_DBNAMES[@]}"; do
    # 获取当前时间，并格式化为年月日时分秒
    TIMESTAMP=$(date +%Y%m%d%H%M%S)

    # 格式化备份文件名，添加数据库名称作为前缀
    BACKUP_FILE="${BACKUP_DIR}/${DBNAME}_${TIMESTAMP}.sql"

    # 使用mysqldump备份数据库
    echo "开始备份数据库 $DBNAME 到 $BACKUP_FILE"
    /usr/local/mysql/bin/mysqldump --login-path=local "$DBNAME" > "$BACKUP_FILE"

    # 检查备份是否成功
    if [ $? -eq 0 ]; then
        echo "备份成功完成: $BACKUP_FILE"
    else
        echo "备份失败: $BACKUP_FILE"
    fi
done

# 保留最近60天的备份文件
echo "正在删除超过60天的备份文件..."
find "$BACKUP_DIR" -name "*.sql" -type f -mtime +60 -exec rm -f {} \;
echo "旧的备份(超过60天)已被删除。"

编写crontab执行脚本

首先需要赋予脚本执行权限
chmod +x xxx.sh

#编写crontab内容
crontab -e

#这里做测试,十分钟执行一次,实际按需求填写执行时间
*/10 * * * * /bin/bash xxxx.sh


#查看定时任务
crontab -l

二、mysqldump还原

1、mysql外还原 (sh脚本还原)

编写sh脚本还原数据(全表)

#!/bin/bash

# 定义备份文件路径
BACKUP_FILE="/data/mysql/all.db"

# 检查备份文件是否存在
if [ ! -f "$BACKUP_FILE" ]; then
    echo "错误: 备份文件 $BACKUP_FILE 不存在."
    exit 1
fi

echo "开始从 $BACKUP_FILE 恢复数据..."
# 使用mysql_config_editor设置凭据 --login-path=local
mysql --login-path=local < "$BACKUP_FILE"


# 检查上一个命令的退出状态
if [ $? -eq 0 ]; then
    echo "成功: 数据已从 $BACKUP_FILE 恢复."
else
    echo "错误: 恢复过程中出现问题."
fi

编写sh脚本还原数据(单表输入)

#!/bin/bash


read -p "请输入备份文件的路径: " BACKUP_FILE


if [ ! -f "$BACKUP_FILE" ]; then
    echo "错误: 备份文件 $BACKUP_FILE 不存在."
    exit 1
fi


read -p "请输入要恢复的数据库名称(先确定输入的数据库存在!!!): " DATABASE_NAME


echo "开始从 $BACKUP_FILE 恢复数据到 $DATABASE_NAME 数据库..."
mysql --login-path=local -D "$DATABASE_NAME" < "$BACKUP_FILE"


if [ $? -eq 0 ]; then
    echo "成功: 数据已从 $BACKUP_FILE 恢复到 $DATABASE_NAME 数据库."
else
    echo "错误: 恢复过程中出现问题."
fi

常见问题:

cron找不到mysqldump 命令,直接写完整路径,查找你mysql安装的目录/bin下面

查找mysqldump

find / -type f | grep "mysqldump"

查看mysql_config_editor配置的账号

mysql_config_editor print --login-path=local

移除mysql_config_editor配置的账号

mysql_config_editor remove --login-path=local