6.9.7 运维变更与审计自动化

运维变更与审计自动化的目标是把“谁在何时对什么做了什么改动”完整记录，并将变更流程标准化、可追溯、可回滚。对 MySQL 而言，变更对象包括实例参数、用户与权限、表结构、SQL 上线、备份策略、主从拓扑与中间件配置等。

原理草图（变更与审计闭环）：

统一变更流程要素清单（工单字段建议）：
- 影响范围、窗口时间、执行人、SQL 清单、预计耗时、监控指标、回滚策略、风险等级
- 高风险变更（DDL、参数变更、主从切换）必须双人复核与演练验证

一、审计组件安装与启用示例（MySQL Enterprise Audit 或 MariaDB Audit 插件）
1）安装插件（示例以 MariaDB Audit 为例，MySQL 可用 enterprise_audit）

# 确认插件目录
mysql -uroot -p -e "SHOW VARIABLES LIKE 'plugin_dir';"

# 安装插件（需有对应 .so）
mysql -uroot -p -e "INSTALL SONAME 'server_audit';"

# 查看插件状态
mysql -uroot -p -e "SHOW PLUGINS LIKE 'server_audit';"

2）启用与配置（my.cnf）

# /etc/my.cnf
[mysqld]
server_audit=ON
server_audit_logging=ON
server_audit_events=CONNECT,QUERY,TABLE,QUERY_DDL
server_audit_file_path=/var/log/mysql/audit.log
server_audit_file_rotate_size=200M
server_audit_file_rotations=10

3）生效与验证

# 重启服务
systemctl restart mysqld

# 生成审计事件
mysql -uroot -p -e "CREATE DATABASE audit_test; DROP DATABASE audit_test;"

# 查看审计日志
tail -n 5 /var/log/mysql/audit.log

命令解释：
- INSTALL SONAME 安装审计插件；
- server_audit_events 控制记录事件类型；
- server_audit_file_rotate_size 防止单文件过大。

二、变更自动化示例

1）DDL 在线变更（pt-online-schema-change）

# 安装
yum install -y percona-toolkit

# 对大表新增列（会创建影子表并触发器同步）
pt-online-schema-change \
  --host=127.0.0.1 --user=ops --password='Passw0rd!' \
  --alter "ADD COLUMN remark VARCHAR(128) DEFAULT ''" \
  --database=appdb --table=orders \
  --charset=utf8mb4 --execute \
  --max-load Threads_running=50 \
  --critical-load Threads_running=80

# 解释：
# --alter 变更语句；--execute 真正执行；--max-load/--critical-load 负载节流

2）DDL 在线变更（gh-ost）

# gh-ost 参数示例（从主库执行，写到主库）
gh-ost \
  --user="ops" --password="Passw0rd!" --host="127.0.0.1" \
  --database="appdb" --table="orders" \
  --alter="ADD COLUMN remark VARCHAR(128) DEFAULT ''" \
  --allow-on-master \
  --max-load=Threads_running=50 \
  --critical-load=Threads_running=80 \
  --chunk-size=1000 \
  --switch-to-rbr \
  --execute

3）参数配置与版本控制（Git + 配置分发）

# 初始化配置仓库
mkdir -p /opt/mysql-config && cd /opt/mysql-config
git init
cp /etc/my.cnf my.cnf
git add my.cnf && git commit -m "baseline my.cnf"

# 修改参数后提交
sed -i 's/^innodb_buffer_pool_size.*/innodb_buffer_pool_size=2G/' my.cnf
git commit -am "tune buffer pool to 2G"

# 分发到目标主机（示例）
rsync -av my.cnf root@db01:/etc/my.cnf
ssh root@db01 "systemctl restart mysqld"

命令解释：
- git commit 保证变更可追溯；
- rsync 统一分发配置，配合审批单执行。

4）权限变更自动化（最小权限 + 记录工单号）

-- 变更前：在工单系统中记录工单号（示例变量）
SET @ticket='TCK-20240101';

-- 创建角色并授权（不直接给用户）
CREATE ROLE IF NOT EXISTS 'app_read';
GRANT SELECT ON appdb.* TO 'app_read';

-- 授权到用户并记录（审计日志中可关联工单号）
GRANT 'app_read' TO 'appuser'@'10.%';
SET DEFAULT ROLE 'app_read' TO 'appuser'@'10.%';

5）主从切换后自动验证

# 验证复制状态
mysql -uroot -p -e "SHOW SLAVE STATUS\G" | egrep "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running"

# 简单一致性校验（行数对比）
mysql -uroot -p -e "SELECT COUNT(*) FROM appdb.orders;"

三、审计日志集中采集示例（Filebeat -> ES）

# /etc/filebeat/filebeat.yml
filebeat.inputs:
- type: log
  paths:
    - /var/log/mysql/audit.log
  fields:
    service: mysql
    logtype: audit

output.elasticsearch:
  hosts: ["http://es01:9200"]
  index: "mysql-audit-%{+yyyy.MM.dd}"

四、排错清单（常见问题与处理）
- 插件安装失败：检查 plugin_dir 路径是否存在 .so 文件，确认 INSTALL SONAME 权限。
- 审计无日志：确认 server_audit_logging=ON，检查日志路径权限 chown mysql:mysql /var/log/mysql。
- pt-osc 失败：检查是否有外键或触发器限制，必要时加 --nocheck-foreign-keys 或改用 gh-ost。
- 变更导致延迟：降低 --chunk-size、调低 --max-load，在低峰执行。
- 权限变更不生效：检查 SET DEFAULT ROLE 是否执行，或用户 session 是否重新登录。

五、变更前后指标对比命令（可加入工单执行清单）

# 变更前
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | head -n 20

# 变更后
mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | head -n 20

六、练习（动手操作）
1）搭建审计插件并验证 DROP/ALTER 是否被记录，提交一份审计日志截图。
2）使用 pt-online-schema-change 为测试表新增列，观察 CPU/IO 与线程数变化。
3）用 Git 管理 my.cnf，提交一次参数变更并回滚到上一版本。
4）模拟权限变更：创建角色并给用户授权，检查审计记录中是否包含操作与时间。

通过流程、工具与审计的三位一体，实现 MySQL 运维变更的可控、可追溯与可持续优化。