Ubuntu MySQL备份怎样做

Ubuntu 上备份 MySQL 的实用做法

一 手动备份常用命令

• 备份单个库
  • 命令：mysqldump -u 用户名 -p 数据库名 > /path/备份名.sql
  • 示例：mysqldump -u root -p mydb > /var/backups/mysql/mydb.sql
• 备份单个表或多张表
  • 命令：mysqldump -u 用户名 -p 数据库名 表1 [表2] > /path/表备份.sql
  • 示例：mysqldump -u root -p mydb users orders > /var/backups/mysql/mydb_tables.sql
• 备份多个库
  • 命令：mysqldump -u 用户名 -p --databases 库1 库2 > /path/多库备份.sql
• 备份所有库
  • 命令：mysqldump -u 用户名 -p --all-databases > /path/all_databases.sql
• 常用选项
  • InnoDB 一致性快照：--single-transaction
  • 仅导出数据：--no-create-info
  • 生成 DROP TABLE：--add-drop-table
• 压缩备份（节省空间）
  • 方式一：mysqldump ... | gzip > /path/备份.sql.gz
  • 方式二：先导出再压缩 gzip /path/备份.sql
• 远程备份
  • 命令：mysqldump -h 主机 -P 端口 -u 用户 -p 数据库 > /path/备份.sql
• 说明
  • 若只需导出功能，可仅安装 mysql-client 并使用 mysqldump。

二 自动化定时备份脚本

• 创建备份目录
  • sudo mkdir -p /var/backups/mysql
• 备份脚本示例（含保留策略与压缩）
  • 文件：/usr/local/bin/backup_mysql.sh
  • 内容：

    #!/usr/bin/env bash
    set -Eeuo pipefail
    
    DB_USER="backup_user"
    DB_PASS="StrongPass!23"
    DB_NAME="mydb"
    BACKUP_DIR="/var/backups/mysql"
    RETENTION_DAYS=7
    DATE=$(date +"%Y%m%d_%H%M%S")
    LOG="$BACKUP_DIR/backup_$DATE.log"
    
    mkdir -p "$BACKUP_DIR"
    
    echo "[$DATE] 开始备份: $DB_NAME" >> "$LOG"
    
    mysqldump -u "$DB_USER" -p"$DB_PASS" \
      --single-transaction \
      --routines --triggers --events \
      --hex-blob --set-gtid-purged=OFF \
      "$DB_NAME" \
      | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz" 2>>"$LOG"
    
    if [ $? -eq 0 ]; then
      echo "[$DATE] 备份成功: ${DB_NAME}_${DATE}.sql.gz" >> "$LOG"
    else
      echo "[$DATE] 备份失败" >> "$LOG"
      exit 1
    fi
    
    # 清理过期备份
    find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete >> "$LOG" 2>&1
    echo "[$DATE] 清理完成，保留最近 $RETENTION_DAYS 天" >> "$LOG"
    

• 赋权并测试
  • sudo chmod +x /usr/local/bin/backup_mysql.sh
  • /usr/local/bin/backup_mysql.sh
• 加入定时任务（每天 02:00）
  • sudo crontab -e
  • 添加：0 2 * * * /usr/local/bin/backup_mysql.sh
• 安全建议
  • 避免在命令行中明文写密码，可用 ~/.my.cnf 配置凭据（见下文）。

三 备份文件的安全与传输

• 传输到远程服务器
  • rsync：rsync -avz /var/backups/mysql/ user@remote:/backup/mysql/
  • scp：scp /var/backups/mysql/mydb_*.sql.gz user@remote:/backup/mysql/
• 加固要点
  • 备份目录权限：sudo chown -R backup_user:backup_user /var/backups/mysql && sudo chmod 700 /var/backups/mysql
  • 建议备份到独立磁盘或网络存储，并定期做恢复演练。

四 恢复数据的常用方法

• 从普通 SQL 备份恢复
  • mysql -u 用户名 -p 数据库名 < /path/备份.sql
• 从压缩备份恢复
  • gunzip < /path/备份.sql.gz | mysql -u 用户名 -p 数据库名
• 从所有库备份恢复
  • mysql -u 用户名 -p < /path/all_databases.sql
• 使用备份包工具（如 mysqlpump）备份的文件，也可用 mysql 命令导入。

五 实用建议与排错要点

• 凭据与权限
  • 创建最小权限备份账号：

    CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass!23';
    GRANT SELECT, SHOW VIEW, RELOAD, LOCK TABLES, PROCESS, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
    FLUSH PRIVILEGES;
    

  • 使用 ~/.my.cnf 避免命令行暴露密码：

    [client]
    user=backup_user
    password=StrongPass!23
    host=localhost
    

    然后命令可简化为：mysqldump --single-transaction 数据库名 > backup.sql
• 一致性、性能与影响
  • InnoDB 优先使用 --single-transaction；MyISAM 需要全局读锁时可配合 --lock-all-tables（会短暂阻塞写入）。
  • 大型库建议按库/表拆分备份、在低峰期执行、并保留多份历史副本。
• 校验与演练
  • 定期校验备份可用性（随机解压导入测试库），并监控备份日志与磁盘空间。