如何在Ubuntu上实现MySQL自动化运维

在 Ubuntu 上落地 MySQL 自动化运维

一 基础架构与工具选型

• 自动化部署与配置管理：使用 Ansible（或 Puppet/Chef）批量安装 MySQL/MariaDB、分发配置、执行安全基线；结合 Terraform 管理云上实例与网络，形成 IaC 闭环。
• 备份与恢复：以 mysqldump + cron 实现定时全量/增量备份，脚本化压缩与保留策略，定期做恢复演练验证可用性。
• 监控与告警：以 Prometheus + Grafana 为可视化核心，配合 mysqld_exporter 采集指标；辅以 MySQLTuner、Percona Toolkit 做性能分析与慢查询治理。
• 日志与审计：集中收集 MySQL 错误日志/慢查询日志，用 ELK（Elasticsearch, Logstash, Kibana） 做检索与可视化。
• 安全与合规：完成安装后的 mysql_secure_installation、最小权限账号、限制远程 root、定期补丁更新与防火墙策略。

二 自动化部署与配置

• 安装与初始化
  • 安装数据库：sudo apt update && sudo apt install mysql-server
  • 安全初始化：sudo mysql_secure_installation（设置 root 强密码、移除匿名用户、禁止远程 root、删除测试库）
• 批量部署与配置管理（Ansible 示例思路）
  • 使用官方 APT 源安装指定版本（如 8.0），分发 my.cnf 与系统服务配置；
  • 执行安全基线（密码策略、权限收敛、日志与慢查询开关）；
  • 多环境（测试/预发/生产）通过 inventory 与变量分层管理。
• 无人值守脚本要点（可直接改造为 Ansible 模块）
  • 支持多版本（5.7/8.0）、自动适配 Ubuntu 软件源；
  • 安全增强（强密码、移除匿名/测试库、禁用远程 root）；
  • 性能基线（如 innodb_buffer_pool_size、max_connections、开启慢查询日志）。

三 自动化备份与恢复

• 备份脚本模板（安全凭据建议使用 ~/.my.cnf）
  • 创建脚本：/usr/local/bin/mysql_backup.sh
  • 内容示例：

    #!/usr/bin/env bash
    set -Eeuo pipefail
    
    BACKUP_DIR="/backup/mysql"
    DATE=$(date +"%Y%m%d%H%M%S")
    LOGFILE="/var/log/mysql_backup.log"
    
    mkdir -p "$BACKUP_DIR"
    
    BACKUP_FILE="$BACKUP_DIR/all_dbs-$DATE.sql.gz"
    
    # 若使用 ~/.my.cnf 则无需 -p 明文
    if mysqldump --single-transaction --routines --triggers --default-character-set=utf8mb4 \
         --all-databases | gzip > "$BACKUP_FILE"; then
        echo "$(date) [INFO] Backup succeeded: $BACKUP_FILE" >> "$LOGFILE"
    else
        echo "$(date) [ERROR] Backup failed" >> "$LOGFILE"
        exit 1
    fi
    
    # 保留最近 7 天
    find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +7 -delete
    

  • 赋权：chmod +x /usr/local/bin/mysql_backup.sh
• 定时任务（每天 02:00 全量备份）
  • crontab -e
  • 0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
• 恢复演练
  • gunzip < /backup/mysql/all_dbs-YYYYMMDDHHMMSS.sql.gz | mysql -u root -p
• 关键实践
  • 凭据安全：在 ~/.my.cnf 中配置 [client] 段，权限 600；
  • 一致性：InnoDB 使用 –single-transaction；
  • 保留策略：按容量与合规设置保留周期（如 7 天）；
  • 定期验证：每周抽样恢复并校验数据/结构一致性。

四 监控告警与性能优化

• 指标与可视化
  • 部署 mysqld_exporter，以 .my.cnf 提供监控账号凭据；
  • Prometheus 抓取 exporter 指标，Grafana 导入 MySQL 官方/社区仪表板，配置阈值告警（如连接数、复制延迟、慢查询激增）。
• 性能诊断与调优
  • 定期运行 MySQLTuner 获取配置与参数建议；
  • 使用 Percona Toolkit（如 pt-query-digest、pt-table-checksum）分析慢查询、校验主从一致性。
• 日志与审计
  • 启用并轮转 错误日志/慢查询日志；
  • 使用 Filebeat/Logstash 将日志送入 Elasticsearch，在 Kibana 建立错误与慢查询面板。

五 安全加固与日常维护清单

• 安全
  • 完成 mysql_secure_installation；
  • 仅开放必要端口（默认 3306），限制来源 IP；
  • 应用账号遵循最小权限与按库授权；
  • 定期升级 MySQL/MariaDB 与操作系统补丁。
• 维护
  • 例行巡检：错误日志、慢查询、复制状态、磁盘/IO/连接数；
  • 备份验证与容量评估（告警阈值与扩容策略）；
  • 变更管理：变更前后备份与回滚预案，灰度与窗口期执行。