一般来说，实现公司的MySQL数据库每日备份是个很常见的需求。

在项目的初始阶段可能不太重视，当业务渐渐起来以及开发人员逐渐增多之后，数据库的每日备份就很重要了。

一、快速使用

大部分人在使用MySQL的人备份时最常见的选择还是mysqldump。

mysqldump -u"$DB_USER" -p"$DB_PASS" --databases "$db_name" 

（一）编写shell文件

创建一个 .sh 脚本文件，实现 “备份 - 压缩 - 记录日志” 的完整流程。

下面我创建了个mysql_backup.sh文件。

#!/bin/bash
# MySQL备份脚本（按日期分文件夹管理）
# 功能：备份指定数据库，排除iplatform库的特定日志表，自动压缩并清理旧备份

#######################################
# 【配置参数区】-- 在此处修改以下参数
#######################################
DB_USER="root"                   # 数据库用户名
DB_PASS="123456"            # 数据库密码（在此处修改）
BACKUP_ROOT_DIR="/home/backup"   # 备份根目录（所有日期的备份放在这里）
DB_LIST=(                        # 需要备份的数据库列表（在此处增删）
  "user"
  "order"
)
EXCLUDE_TABLES=(                 # 需要排除的表（格式：库名.表名）
  "user.log"
  "order.log"
)
RETENTION_DAYS=30                # 备份保留天数（默认30天）
export PATH=$PATH:/usr/local/mysql/bin    # 设置环境变量
#######################################
# 【配置结束】-- 以下内容无需修改
#######################################

# 初始化变量（按日期创建子文件夹）
CURRENT_DATE=$(date +%Y%m%d)      # 当天日期（用于文件夹命名）
TIMESTAMP=$(date +%Y%m%d_%H%M%S)  # 完整时间戳（用于文件名）
BACKUP_DIR="$BACKUP_ROOT_DIR/$CURRENT_DATE"  # 当天备份的具体目录
LOG_FILE="$BACKUP_DIR/backup_$TIMESTAMP.log"

# 检查并创建备份目录（包括当天子文件夹）
if [ ! -d "$BACKUP_DIR" ]; then
  echo "备份目录 $BACKUP_DIR 不存在，尝试创建..."
  mkdir -p "$BACKUP_DIR"
  # 检查目录创建结果
  if [ $? -ne 0 ]; then
    echo "错误：无法创建备份目录 $BACKUP_DIR，请检查权限！"
    exit 1
  fi
  echo "备份目录创建成功"
fi

# 写入日志头部
echo "===== 备份开始于 $TIMESTAMP =====" >> "$LOG_FILE"
echo "备份数据库列表：${DB_LIST[*]}" >> "$LOG_FILE"
echo "排除表列表：${EXCLUDE_TABLES[*]}" >> "$LOG_FILE"
echo "备份文件存放目录：$BACKUP_DIR" >> "$LOG_FILE"

# 备份单个数据库的函数
backup_database() {
  local db_name=$1
  local exclude_params=""
  
  # 生成排除表参数（仅对iplatform库生效）
  if [ "$db_name" = "iplatform" ]; then
    for table in "${EXCLUDE_TABLES[@]}"; do
      exclude_params+=" --ignore-table=$table"
    done
  fi
  
  # 执行备份并压缩（文件存放在当天子文件夹中）
  echo "开始备份数据库：$db_name" >> "$LOG_FILE"
  mysqldump -u"$DB_USER" -p"$DB_PASS" --databases "$db_name" $exclude_params | gzip > "$BACKUP_DIR/${db_name}_$TIMESTAMP.sql.gz"
  
  # 检查备份结果
  if [ $? -eq 0 ]; then
    echo " 数据库 $db_name 备份成功：${db_name}_$TIMESTAMP.sql.gz" >> "$LOG_FILE"
    echo " 数据库 $db_name 备份成功"  # 同时输出到控制台
  else
    echo " 数据库 $db_name 备份失败！" >> "$LOG_FILE"
    echo " 数据库 $db_name 备份失败！"  # 同时输出到控制台
  fi
}

# 批量备份所有数据库
for db in "${DB_LIST[@]}"; do
  backup_database "$db"
done

# 清理过期备份（删除整个过期日期的文件夹）
echo "开始清理${RETENTION_DAYS}天前的备份文件夹..." >> "$LOG_FILE"
find "$BACKUP_ROOT_DIR" -maxdepth 1 -type d -name "20[0-9][0-9][0-1][0-9][0-3][0-9]" -mtime +"$RETENTION_DAYS" -exec rm -rf {} \;
echo "清理完成" >> "$LOG_FILE"

# 写入日志尾部
echo "===== 备份结束于 $(date +%Y%m%d_%H%M%S) =====" >> "$LOG_FILE"
echo "" >> "$LOG_FILE"

echo "所有备份操作已完成，详细日志：$LOG_FILE"

上述shell脚本的实现有如下注意点：

• 1.脚本实现了备份多个数据库的功能，另外因为日志表通常较大，可以将较大的数据库表排除掉，以免每日备份的文件过大。

• 2.选择 .sql.gz 而非 .sql，核心是通过压缩解决纯 SQL 文件体积过大的问题，同时几乎不增加使用成本。纯 .sql 文件是明文文本格式，包含大量重复字符（如 SQL 关键字、表结构定义），压缩率极高。通常数据库备份的压缩率可达 70%-90%，即 10GB 的 .sql 文件压缩后仅需 1-3GB，能显著减少硬盘占用，尤其适合需要长期保留备份的场景。

• 3.脚本里需要设置环境变量，因为定时任务不认/etc/profile里加的环境变量。

（二）其它处理

1.更改文件权限

将.sh文件变更为可执行文件。

chmod +x mysql_backup.sh

2.换行符替换

可能脚本运行会报错 ./mysql_backup.sh: /bin/bash^M: 坏的解释器: 没有那个文件或目录，这是由于脚本文件的换行符格式不兼容导致的，通常是因为在 Windows 系统中编辑了脚本，然后传到 Linux 系统时保留了 Windows 的换行符（\r\n），而 Linux 期望的是 Unix 风格的换行符（\n）。

sed -i 's/\r$//' mysql_backup.sh

3.环境变量

执行脚本文件前，检查下环境变量是否有配置。

比如，可能mysqldump命令执行不了，因为mysql安装后没加入环境变量。下面添加下环境变量：

# 使用find命令搜索mysql安装位置
sudo find / -name "mysql" -type f 2>/dev/null | grep bin

编辑用户主目录下的 /etc/profile文件。

vi /etc/profile

添加环境变量

# MySQL PATH
export PATH=$PATH:/usr/local/mysql/bin
# 如果还需要库路径，可以添加
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/mysql/lib

让配置立即生效：

source /etc/profile

注意：定时任务crontab不会去加载/etc/profile等用户环境的变量，只包含/bin:/usr/bin的环境变量，所以需要在脚本中加入环境变量，或者在/bin:/usr/bin的目录加入软链接。

可能环境变量加了之后，还是连不上ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/mysql.sock' 。这时候需要可以配置下软链接。

# 假设找到的实际socket文件在 /var/lib/mysql/mysql.sock
# 而客户端期望在 /usr/local/mysql/mysql.sock
sudo ln -s /var/lib/mysql/mysql.sock /usr/local/mysql/mysql.sock

4.解压缩与还原数据库

可以先手动执行，查看脚本是否能成功运行得到.sql.gz的文件。

后续可以通过以下命令解压缩.sql.gz的文件，得到.sql文件。

# 解压不保留.gz文件
gzip -d backupfile.sql.gz
# 解压但保留原.gz文件
gzip -dc backup_file.sql.gz > backup_file.sql

查看或使用SQL文件

# 查看SQL文件内容
cat backup_file.sql
# 或者
less backup_file.sql

后续可以测试下备份的文件是否能成功还原。

# 导入到MySQL数据库
mysql -u username -p database_name < backup_file.sql

或者，一步到位直接解压并导入MySQL

# 直接解压并导入到MySQL
gzip -dc backup_file.sql.gz | mysql -u username -p database_name

还原过程中，若数据库不存在，则会重建。若数据库中还有很多有数据的表，则会删除表并重新建表来插入备份的数据。

（三）设置定时任务（Cron）

1.打开 Cron 编辑界面：

crontab -e

2.添加定时任务（例如每天凌晨 2 点执行）：

# 每天02:00执行备份脚本，并将错误输出到日志
0 2 * * * /path/to/mysql_backup.sh >> /path/to/backup/error.log 2>&1

3.保存退出wq，Cron 会自动生效。

# 可以查看当前用户的crontab：
crontab -l
# 查看最近的cron执行记录
tail -f /var/log/cron
# 查看生成.gz文件
zcat backup.sql.gz | head -100  # 查看前100行

（四）上传备份文件

数据库光完成备份了还不行，放到本地服务器也不可靠，一般还得将备份文件上传到异地服务器进行保存，以防止本地数据库服务器出现问题。

1.配置SSH免密登录

# 在备份服务器生成密钥
ssh-keygen -t rsa -b 4096
# 复制公钥到远程服务器
ssh-copy-id -p 22 backup_user@192.168.1.100
# 测试连接
ssh -p 22 backup_user@192.168.1.100 "echo '连接成功'"
# 在远程服务器创建必要的目录
mkdir -p /backup
chmod 755 /backup

2.安装传输工具

# CentOS/RHEL
yum install rsync -y

# Ubuntu/Debian
apt-get install rsync -y

3.编写传输shell脚本

编写上传脚本mysql_upload.sh使用

#!/bin/bash
# 备份文件上传到远程服务器脚本（简洁版）
# 配合mysql_backup.sh使用，建议在备份完成后执行

#######################################
# 【配置参数区】-- 在此处修改以下参数
#######################################
# 本地配置
BACKUP_ROOT_DIR="/home/backup"      # 本地备份根目录（与备份脚本相同）
DAYS_TO_SYNC="1"                    # 同步最近几天的备份（1=只同步当天）

# 远程配置
REMOTE_USER="info"                  # 远程服务器用户名
REMOTE_HOST="172.16.68.251"         # 远程服务器IP或域名
REMOTE_PORT="22"                    # SSH端口（默认22）
REMOTE_BACKUP_DIR="/backup/quality" # 远程服务器备份目录

# 传输配置
BW_LIMIT="50000"                    # 带宽限制KB/s (50MB/s)，0表示不限速
RSYNC_OPTIONS="-avz --progress"     # rsync基本选项

# 日志配置
LOG_BASE_DIR="/home/backup/logs"    # 使用用户有权限的目录
#######################################
# 【配置结束】-- 以下内容无需修改
#######################################

# 初始化变量
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
CURRENT_DATE=$(date +%Y%m%d)
LOG_DIR="$LOG_BASE_DIR"
UPLOAD_LOG="$LOG_DIR/upload_$(date +%Y%m%d).log"

# 创建日志目录
mkdir -p "$LOG_DIR"

# 记录开始
echo "===== $(date +'%Y-%m-%d %H:%M:%S') 备份上传开始 =====" >> "$UPLOAD_LOG"
echo "本地目录: $BACKUP_ROOT_DIR" >> "$UPLOAD_LOG"
echo "远程服务器: $REMOTE_USER@$REMOTE_HOST:$REMOTE_BACKUP_DIR" >> "$UPLOAD_LOG"

# 检查今天备份目录是否存在
TODAY_DIR="$BACKUP_ROOT_DIR/$CURRENT_DATE"
if [ ! -d "$TODAY_DIR" ]; then
    echo "错误：今日备份目录不存在 $TODAY_DIR" | tee -a "$UPLOAD_LOG"
    exit 1
fi

# 检查是否有备份文件
BACKUP_FILES=$(find "$TODAY_DIR" -name "*.gz" -type f 2>/dev/null | wc -l)
if [ "$BACKUP_FILES" -eq 0 ]; then
    echo "警告：今日没有备份文件" | tee -a "$UPLOAD_LOG"
    echo "检查目录: $TODAY_DIR" >> "$UPLOAD_LOG"
fi

# 添加带宽限制参数
if [ "$BW_LIMIT" -gt 0 ]; then
    RSYNC_OPTIONS="$RSYNC_OPTIONS --bwlimit=$BW_LIMIT"
fi

# 根据DAYS_TO_SYNC确定要同步的目录
SYNC_FAILED=0
for ((i=0; i<DAYS_TO_SYNC; i++)); do
    DATE_DIR=$(date -d "$i days ago" +%Y%m%d)
    LOCAL_DIR="$BACKUP_ROOT_DIR/$DATE_DIR"
    
    if [ ! -d "$LOCAL_DIR" ]; then
        echo "跳过不存在的目录: $LOCAL_DIR" >> "$UPLOAD_LOG"
        continue
    fi
    
    echo "" >> "$UPLOAD_LOG"
    echo "同步目录: $LOCAL_DIR/" >> "$UPLOAD_LOG"
    echo "目标: $REMOTE_USER@$REMOTE_HOST:$REMOTE_BACKUP_DIR/$DATE_DIR/" >> "$UPLOAD_LOG"
    
    # 执行rsync
    rsync $RSYNC_OPTIONS -e "ssh -p $REMOTE_PORT" \
        "$LOCAL_DIR/" \
        "$REMOTE_USER@$REMOTE_HOST:$REMOTE_BACKUP_DIR/$DATE_DIR/" \
        >> "$UPLOAD_LOG" 2>&1
    
    if [ $? -eq 0 ]; then
        echo "✓ 目录 $DATE_DIR 同步成功" | tee -a "$UPLOAD_LOG"
    else
        echo "✗ 目录 $DATE_DIR 同步失败" | tee -a "$UPLOAD_LOG"
        SYNC_FAILED=1
    fi
done

# 记录结束
echo "" >> "$UPLOAD_LOG"
echo "===== $(date +'%Y-%m-%d %H:%M:%S') 备份上传结束 =====" >> "$UPLOAD_LOG"

if [ $SYNC_FAILED -eq 0 ]; then
    echo "所有目录同步成功" | tee -a "$UPLOAD_LOG"
    exit 0
else
    echo "部分目录同步失败，请检查日志" | tee -a "$UPLOAD_LOG"
    exit 1
fi

4.其它处理

# 将`.sh`文件变更为可执行文件。
chmod +x mysql_upload.sh
# 换行符替换
sed -i 's/\r$//' mysql_upload.sh
# 设置定时任务
crontab -e
# 每天02:00执行备份脚本，并将错误输出到日志
0 2 * * * /path/to/mysql_upload.sh >> /path/to/backup/error.log 2>&1

二、总结

1.导出结构解释

$ mysqldump test t1
-- [Version and host comments]
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

-- [More version-specific comments to save options for restore]

-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t1` (
  `a` int NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- [More option restoration]

导出文件包含表结构和数据，均以有效的SQL命令形式写出。

• 文件以设置MySQL各种选项的注释开始。这些选项要么是为了使恢复工作更高效，要么是为了保证兼容性和正确性。
• 接下来可以看到表结构，然后是数据。
• 最后，脚本重置在导出开始时变更的选项。

导出的输出对于恢复操作来说是可执行的。这很方便，但mysqldump的默认选项对于生成一个巨大的备份却不是太适合的。

mysqldump不是生成SQL逻辑备份的唯一工具。例如，还可以用mydumper或phpMyAdmin工具来创建。我们想指出的是，不是某一个特定的工具有多大的问题，而是单独的SQL逻辑备份本身就有一些缺点。

2.逻辑备份的缺点

下面是几个主要的问题。

将库表结构和数据存储在一起

如果想从单个文件恢复，这样做会非常方便，但如果只想恢复一个表或只想恢复数据就很困难了。可以通过导出两次的方法来缓解这个问题——一次只导出数据，另外一次只导出schema——但还是会有下一个麻烦。

巨大的SQL语句
服务器解析和执行SQL语句的工作量非常大，所以加载数据时会非常慢。

单个巨大的文件
大部分文本编辑器都不能编辑巨大的或者包含非常长的行的文件。尽管有时候可以用命令行的流编辑器——例如，sed或grep——来抽出需要的数据，但保持文件小型化仍然是更可取的。

逻辑备份的成本很高
比起逻辑备份这种从存储引擎中读取数据后通过客户端/服务器协议发送结果集的方式，还有其他更高效的方法。可以看到，在生产环境使用逻辑备份可能很难满足要求。

如果使用逻辑备份，强烈建议考虑使用mydumper，以避免单线程备份的一些问题，并实际测试使用该工具备份对数据库的影响。如果想使用物理备份，则考虑使用Percona XtraBackup工具。