# 1. 系统环境准备
# 检查系统版本
$ cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

# 关闭SELinux
$ setenforce 0
$ sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/’ /etc/selinux/config

# 关闭防火墙（或配置防火墙规则）
$ systemctl stop firewalld
$ systemctl disable firewalld

# 安装必要的依赖包
$ yum install -y wget gcc gcc-c++ make cmake ncurses-devel bison libaio-devel openssl-devel

# 2. 创建MySQL用户和用户组
$ groupadd mysql
$ useradd -r -g mysql -s /bin/false mysql

# 3. 下载并安装MySQL
# 下载MySQL 8.4.0
$ wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.0-el7-x86_64.tar.gz

# 解压安装包
$ tar -xzf mysql-8.4.0-el7-x86_64.tar.gz
$ mv mysql-8.4.0-el7-x86_64 /usr/local/mysql

# 创建数据目录
$ mkdir -p /mysql/data
$ chown -R mysql:mysql /mysql/data

# 4. 初始化MySQL
$ /usr/local/mysql/bin/mysqld –initialize –user=mysql –datadir=/mysql/data

# 输出示例
2026-04-09T00:00:00.000000Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.4.0)
initializing of server in progress as process 12345
2026-04-09T00:00:00.123456Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2026-04-09T00:00:01.234567Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2026-04-09T00:00:02.345678Z 6 [Note] [MY-010454] [Server] A temporary password is generated for
root@localhost: ABCDE12345!@#$%

# 5. 配置MySQL
# 创建配置文件
$ vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql

# 6. 启动MySQL服务
# 创建systemd服务文件
$ vi /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
After=network.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_safe –datadir=/mysql/data
ExecStop=/usr/local/mysql/bin/mysqladmin -u root -p shutdown
PrivateTmp=true

[Install]
WantedBy=multi-user.target

# 重载systemd配置
$ systemctl daemon-reload

# 启动MySQL服务
$ systemctl start mysqld

# 查看MySQL服务状态
$ systemctl status mysqld
● mysqld.service – MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2026-04-09 00:00:00 CST; 1min ago
Process: 12345 ExecStart=/usr/local/mysql/bin/mysqld_safe –datadir=/mysql/data (code=exited,
status=0/SUCCESS)
Main PID: 12346 (mysqld)
CGroup: /system.slice/mysqld.service
└─12346 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/mysql/data
–plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –log-error=/mysql/data/mysql-error.log
–pid-file=/mysql/data/mysql.pid –socket=/tmp/mysql.sock

# 7. 安全初始化
$ /usr/local/mysql/bin/mysql_secure_installation

# 输出示例
Securing the MySQL server deployment.

Enter password for user root: # 输入临时密码

The existing password for the user account root has expired. Please set a new password.

New password: # 输入新密码

Re-enter new password: # 确认新密码

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: n # 是否安装密码验证组件

Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n # 是否修改root密码

… skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y # 是否删除匿名用户
Success.

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y # 是否禁止root远程登录
Success.

By default, MySQL comes with a database named ‘test’ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y # 是否删除test数据库
– Dropping test database…
Success.

– Removing privileges on test database…
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y # 是否重新加载权限表
Success.

All done!

# 8. 验证MySQL安装
$ /usr/local/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.4.0 MySQL Community Server – GPL

Copyright (c) 2000, 2026, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SELECT VERSION();
+———–+
| VERSION() |
+———–+
| 8.4.0 |
+———–+
1 row in set (0.00 sec)

# 1. 基础配置
[mysqld]
# 服务器标识
server-id = 1

# 数据目录
datadir = /mysql/data

# 套接字文件
socket = /tmp/mysql.sock

# 错误日志
log-error = /mysql/logs/mysql-error.log

# 通用日志
general_log = 0
general_log_file = /mysql/logs/mysql-general.log

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /mysql/logs/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# 二进制日志
binlog_format = row
binlog_row_image = minimal
binlog_expire_logs_seconds = 604800 # 7天
sync_binlog = 1

# 2. 内存配置
# InnoDB缓冲池大小（建议设置为服务器内存的60-80%）
innodb_buffer_pool_size = 8G

# InnoDB缓冲池实例数（建议设置为CPU核心数或CPU核心数的一半）
innodb_buffer_pool_instances = 4

# InnoDB缓冲池块大小
innodb_buffer_pool_chunk_size = 128M

# 临时表大小
tmp_table_size = 256M
max_heap_table_size = 256M

# 连接内存
read_buffer_size = 128K
read_rnd_buffer_size = 256K
sort_buffer_size = 256K
join_buffer_size = 256K

# 3. InnoDB配置
# InnoDB日志文件大小（建议设置为缓冲池大小的20%）
innodb_log_file_size = 2G

# InnoDB日志缓冲大小
innodb_log_buffer_size = 64M

# InnoDB刷新策略
innodb_flush_log_at_trx_commit = 2

# InnoDB双写缓冲区
innodb_doublewrite = ON

# InnoDB自适应哈希索引
innodb_adaptive_hash_index = ON

# InnoDB文件格式
innodb_file_format = Barracuda

# InnoDB表空间
innodb_file_per_table = ON

# InnoDB IO线程数
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# InnoDB刷新线程数
innodb_purge_threads = 4

# 4. 连接配置
# 最大连接数
max_connections = 1000

# 连接超时时间
wait_timeout = 7200
interactive_timeout = 7200

# 跳过DNS解析
skip_name_resolve = ON

# 5. 查询优化器配置
# 优化器开关
optimizer_switch =
‘index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on’

# 优化器成本模型
optimizer_cost_model = ‘default’

# 6. 安全配置
# 密码验证插件
validate_password.policy = MEDIUM
validate_password.length = 8
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 1

# SSL配置
ssl-ca = /mysql/ssl/ca.pem
ssl-cert = /mysql/ssl/server-cert.pem
ssl-key = /mysql/ssl/server-key.pem

# 加密连接
require_secure_transport = ON

# 7. 其他配置
# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# 时区
default_time_zone = ‘+8:00’

# 最大允许包大小
max_allowed_packet = 64M

# 表名区分大小写
lower_case_table_names = 1

# 临时文件目录
tmpdir = /mysql/tmp

# 8. 配置检查
# 检查配置文件语法
$ /usr/local/mysql/bin/mysqld –defaults-file=/etc/my.cnf –validate-config

# 输出示例
mysqld: [Warning] Could not open the mysql.plugin table. Please run mysql_upgrade to create it.

# 重启MySQL服务使配置生效
$ systemctl restart mysqld

# 验证配置
mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+————-+
| Variable_name | Value |
+————————-+————-+
| innodb_buffer_pool_size | 8589934592 | # 8GB
+————————-+————-+

mysql> SHOW GLOBAL VARIABLES LIKE ‘slow_query_log’;
+—————-+——-+
| Variable_name | Value |
+—————-+——-+
| slow_query_log | ON |
+—————-+——-+

# 1. 索引优化
# 创建合适的索引
mysql> ALTER TABLE fgedu_orders ADD INDEX idx_order_user_id (user_id);

# 创建复合索引
mysql> ALTER TABLE fgedu_orders ADD INDEX idx_order_user_status (user_id, status);

# 删除未使用的索引
mysql> ALTER TABLE fgedu_orders DROP INDEX idx_unused;

# 分析索引使用情况
mysql> SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
COUNT_STAR AS access_count
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
TABLE_SCHEMA = ‘fgedudb’
AND INDEX_NAME IS NOT NULL
ORDER BY
access_count DESC;

# 2. 查询优化
# 避免SELECT *
mysql> SELECT id, order_no, total_amount FROM fgedu_orders WHERE user_id = 1;

# 使用LIMIT限制结果集
mysql> SELECT * FROM fgedu_orders WHERE status = ‘paid’ LIMIT 100;

# 避免在WHERE子句中使用函数
mysql> SELECT * FROM fgedu_orders WHERE created_at >= ‘2026-04-01 00:00:00’ AND created_at
< '2026-04-02 00:00:00' ; # 使用EXPLAIN分析执行计划 mysql> EXPLAIN SELECT * FROM fgedu_orders WHERE user_id = 1
ORDER BY created_at DESC LIMIT 10;

# 3. 表结构优化
# 使用合适的数据类型
mysql> CREATE TABLE fgedu_products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL
);

# 避免使用TEXT和BLOB类型存储频繁查询的数据

# 分区表优化
mysql> CREATE TABLE fgedu_orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(20) NOT NULL,
user_id INT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p2026 VALUES LESS THAN (2027),
PARTITION p2027 VALUES LESS THAN MAXVALUE
);

# 4. 缓存优化
# 使用InnoDB缓冲池
innodb_buffer_pool_size = 8G

# 使用查询缓存（MySQL 5.7及以下）
query_cache_size = 0 # MySQL 8.0已移除
query_cache_type = 0

# 使用应用级缓存
# 如Redis、Memcached等

# 5. 硬件优化
# 使用SSD磁盘
$ lsblk -o NAME,SIZE,TYPE,FSTYPE,MOUNTPOINT
NAME SIZE TYPE FSTYPE MOUNTPOINT
sda 100G disk
├─sda1 500M part xfs /boot
└─sda2 99.5G part xfs /
sdb 500G disk
└─sdb1 500G part xfs /mysql

# 使用多核CPU
$ lscpu | grep CPU\(s\)
CPU(s): 8

# 增加内存
$ free -h
total used free shared buff/cache available
Mem: 16G 8G 2G 100M 6G 7G

# 6. 监控性能
# 使用SHOW STATUS查看性能指标
mysql> SHOW GLOBAL STATUS LIKE ‘Queries’;
mysql> SHOW GLOBAL STATUS LIKE ‘Threads_connected’;
mysql> SHOW GLOBAL STATUS LIKE ‘Slow_queries’;

# 使用Performance Schema
mysql> SELECT * FROM performance_schema.events_statements_history_long WHERE TIMER_WAIT > 1000000000000
LIMIT 10;

# 使用sys Schema
mysql> SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

# 使用第三方工具
$ pt-query-digest /mysql/logs/mysql-slow.log
$ mysqladmin extended-status -i 1

# 1. 用户管理
# 创建用户
mysql> CREATE USER ‘app_user’@’192.168.1.%’ IDENTIFIED BY ‘secure_password’;

# 授予权限
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON fgedudb.* TO ‘app_user’@’192.168.1.%’;

# 撤销权限
mysql> REVOKE DELETE ON fgedudb.* FROM ‘app_user’@’192.168.1.%’;

# 删除用户
mysql> DROP USER ‘app_user’@’192.168.1.%’;

# 查看用户权限
mysql> SHOW GRANTS FOR ‘app_user’@’192.168.1.%’;

# 2. 密码策略
# 设置密码策略
mysql> SET GLOBAL validate_password.policy = ‘MEDIUM’;
mysql> SET GLOBAL validate_password.length = 8;
mysql> SET GLOBAL validate_password.mixed_case_count = 1;
mysql> SET GLOBAL validate_password.number_count = 1;
mysql> SET GLOBAL validate_password.special_char_count = 1;

# 强制用户修改密码
mysql> ALTER USER ‘app_user’@’192.168.1.%’ PASSWORD EXPIRE;

# 3. 访问控制
# 限制用户IP访问
mysql> CREATE USER ‘app_user’@’192.168.1.100’ IDENTIFIED BY ‘secure_password’;

# 使用防火墙限制访问
$ iptables -A INPUT -p tcp –dport 3306 -s 192.168.1.0/24 -j ACCEPT
$ iptables -A INPUT -p tcp –dport 3306 -j DROP
$ service iptables save

# 4. 加密配置
# 生成SSL证书
$ mkdir -p /mysql/ssl
$ cd /mysql/ssl
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca.pem
$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem
$ openssl x509 -req -in server-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 >
server-cert.pem
$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
$ openssl x509 -req -in client-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 02 >
client-cert.pem

# 配置SSL
[mysqld]
ssl-ca = /mysql/ssl/ca.pem
ssl-cert = /mysql/ssl/server-cert.pem
ssl-key = /mysql/ssl/server-key.pem
require_secure_transport = ON

# 测试SSL连接
$ mysql -u root -p –ssl-ca=/mysql/ssl/ca.pem –ssl-cert=/mysql/ssl/client-cert.pem
–ssl-key=/mysql/ssl/client-key.pem

mysql> SHOW STATUS LIKE ‘Ssl_cipher’;
+—————+—————————+
| Variable_name | Value |
+—————+—————————+
| Ssl_cipher | TLS_AES_256_GCM_SHA384 |
+—————+—————————+

# 5. 审计日志
# 启用审计日志
[mysqld]
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_file = /mysql/logs/audit.log
audit_log_policy = ALL

# 查看审计日志
$ tail -n 10 /mysql/logs/audit.log

# 6. 安全加固
# 运行mysql_secure_installation
$ /usr/local/mysql/bin/mysql_secure_installation

# 禁用不必要的插件
mysql> UNINSTALL PLUGIN validate_password;

# 限制root用户本地登录
mysql> DELETE FROM mysql.user WHERE User=’root’ AND Host!=’localhost’;
mysql> FLUSH PRIVILEGES;

# 启用严格SQL模式
mysql> SET GLOBAL sql_mode =
‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION’;

# 1. 备份策略
# 完全备份
$ mysqldump -u root -p –single-transaction –routines –triggers –events –all-databases >
/mysql/backup/full_backup_$(date +%Y%m%d_%H%M%S).sql

# 增量备份
$ mysqldump -u root -p –single-transaction –routines –triggers –events –databases fgedudb –where=”id >
$(cat /mysql/backup/last_id.txt)” > /mysql/backup/inc_backup_$(date +%Y%m%d_%H%M%S).sql

# 差异备份
$ mysqldump -u root -p –single-transaction –routines –triggers –events –databases fgedudb >
/mysql/backup/diff_backup_$(date +%Y%m%d_%H%M%S).sql

# 2. 物理备份
# 使用xtrabackup进行完全备份
$ xtrabackup –backup –user=root –password=password
–target-dir=/mysql/backup/xtrabackup_full_20260409_000000

# 使用xtrabackup进行增量备份
$ xtrabackup –backup –user=root –password=password
–target-dir=/mysql/backup/xtrabackup_inc_20260409_010000
–incremental-basedir=/mysql/backup/xtrabackup_full_20260409_000000

# 3. 备份验证
# 验证逻辑备份
$ mysql -u root -p -e “source /mysql/backup/full_backup_20260409_000000.sql” > /dev/null 2>&1
if [ $? -eq 0 ]; then
echo “Backup verified successfully”
else
echo “Backup verification failed”
fi

# 验证物理备份
$ xtrabackup –prepare –target-dir=/mysql/backup/xtrabackup_full_20260409_000000

# 4. 恢复策略
# 逻辑恢复
$ mysql -u root -p < /mysql/backup/full_backup_20260409_000000.sql # 物理恢复 $ systemctl stop mysqld $ rm -rf /mysql/data/* $ xtrabackup --copy-back --target-dir=/mysql/backup/xtrabackup_full_20260409_000000 $ chown -R mysql:mysql /mysql/data $ systemctl start mysqld # 5. 点恢复 # 恢复完全备份 $ mysql -u root -p < /mysql/backup/full_backup_20260409_000000.sql # 应用二进制日志 $ mysqlbinlog --start-position=107 /mysql/binlog/mysql-bin.000001 | mysql -u root -p fgedudb # 6. 备份自动化 # 创建备份脚本 #!/bin/bash # backup_mysql.sh BACKUP_DIR="/mysql/backup" DATE=$(date +%Y%m%d_%H%M%S) MYSQL_USER="root" MYSQL_PASSWORD="password" # 创建备份目录 mkdir -p $BACKUP_DIR # 完全备份 mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --single-transaction --routines --triggers --events --all-databases>
$BACKUP_DIR/full_backup_$DATE.sql

# 压缩备份
gzip $BACKUP_DIR/full_backup_$DATE.sql

# 清理7天前的备份
find $BACKUP_DIR -name “full_backup_*.sql.gz” -mtime +7 -type f -exec rm -f {} \;

# 发送备份通知
# echo “MySQL backup completed at $DATE” | mail -s “MySQL Backup Notification” admin@example.com

# 使用crontab定期执行
# crontab -e
# 每天凌晨1点执行完全备份
0 1 * * * /mysql/scripts/backup_mysql.sh

# 7. 备份监控
# 查看备份文件
$ ls -la /mysql/backup/
total 102400
-rw-r–r– 1 root root 104857600 Apr 9 01:00 full_backup_20260409_010000.sql.gz

# 检查备份大小
$ du -sh /mysql/backup/full_backup_20260409_010000.sql.gz
100M /mysql/backup/full_backup_20260409_010000.sql.gz

# 1. 监控指标
# 性能指标
– QPS/TPS
– 响应时间
– 慢查询数
– 缓冲池命中率
– 连接数

# 资源指标
– CPU使用率
– 内存使用率
– 磁盘I/O
– 磁盘空间
– 网络流量

# 系统指标
– 错误日志
– 二进制日志
– 审计日志
– 主从复制状态

# 2. 监控工具
# 使用MySQL自带工具
$ mysqladmin extended-status -i 1

# 使用Performance Schema
mysql> SELECT * FROM performance_schema.events_statements_history_long WHERE TIMER_WAIT > 1000000000000
LIMIT 10;

# 使用sys Schema
mysql> SELECT * FROM sys.processlist WHERE command != ‘Sleep’;

# 使用第三方工具
# Prometheus + Grafana
# 安装mysqld_exporter
$ wget
https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
$ tar -xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
$ mv mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/

# 创建MySQL用户
mysql> CREATE USER ‘exporter’@’localhost’ IDENTIFIED BY ‘exporter_password’ WITH MAX_USER_CONNECTIONS 3;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO ‘exporter’@’localhost’;

# 创建配置文件
$ vi /etc/mysqld_exporter.cnf
[client]
user=exporter
password=exporter_password

# 启动mysqld_exporter
$ mysqld_exporter –config.my-cnf=/etc/mysqld_exporter.cnf &

# 配置Prometheus
$ vi /etc/prometheus/prometheus.yml
scrape_configs:
– job_name: ‘mysql’
static_configs:
– targets: [‘localhost:9104’]

# 重启Prometheus
$ systemctl restart prometheus

# 导入Grafana仪表盘（ID: 7362）

# 3. 告警配置
# Prometheus告警规则
$ vi /etc/prometheus/rules/mysql_alerts.yml
groups:
– name: mysql_alerts
rules:
– alert: MySQLHighConnections
expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100 > 80
for: 5m
labels:
severity: warning
annotations:
summary: MySQL连接数过高 ({{ $labels.instance }})
description: MySQL连接使用率超过80% (当前: {{ $value | printf “%.2f” }}%)

– alert: MySQLSlowQueries
expr: rate(mysql_global_status_slow_queries[5m]) > 5
for: 5m
labels:
severity: warning
annotations:
summary: MySQL慢查询数过高 ({{ $labels.instance }})
description: MySQL慢查询数超过5个/分钟 (当前: {{ $value | printf “%.2f” }}个/分钟)

– alert: MySQLHighCpuUsage
expr: rate(process_cpu_seconds_total{job=”mysql”}[5m]) * 100 > 80
for: 5m
labels:
severity: warning
annotations:
summary: MySQL CPU使用率过高 ({{ $labels.instance }})
description: MySQL CPU使用率超过80% (当前: {{ $value | printf “%.2f” }}%)

# 4. 告警通知
# 配置Alertmanager
$ vi /etc/alertmanager/alertmanager.yml
global:
smtp_smarthost: ‘smtp.example.com:587’
smtp_from: ‘alertmanager@example.com’
smtp_auth_username: ‘alertmanager’
smtp_auth_password: ‘password’

route:
receiver: ’email’
group_wait: 30s
group_interval: 5m
repeat_interval: 4h
group_by: [alertname, instance]

receivers:
– name: ’email’
email_configs:
– to: ‘admin@example.com’
send_resolved: true

# 重启Alertmanager
$ systemctl restart alertmanager

# 5. 监控报告
# 生成监控报告
$ python /mysql/scripts/generate_report.py

# 发送监控报告
# echo “MySQL监控报告” | mail -s “MySQL监控报告” admin@example.com -a /mysql/reports/mysql_report.pdf

# 1. 定期检查
# 检查数据库完整性
$ mysqlcheck -u root -p –all-databases

# 输出示例
fgedudb.fgedu_users OK
fgedudb.fgedu_orders OK
fgedudb.fgedu_products OK

# 检查表碎片
mysql> SELECT
TABLE_SCHEMA,
TABLE_NAME,
DATA_LENGTH,
INDEX_LENGTH,
DATA_FREE
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = ‘fgedudb’
AND DATA_FREE > 0
ORDER BY
DATA_FREE DESC;

# 2. 定期优化
# 优化表
$ mysqloptimize -u root -p –all-databases

# 重建索引
mysql> ALTER TABLE fgedu_orders REBUILD INDEX PRIMARY;

# 更新统计信息
mysql> ANALYZE TABLE fgedu_orders;

# 3. 定期清理
# 清理二进制日志
$ mysql -u root -p -e “PURGE BINARY LOGS BEFORE NOW() – INTERVAL 7 DAY;”

# 清理慢查询日志
$ mv /mysql/logs/mysql-slow.log /mysql/logs/mysql-slow.log.old
$ systemctl restart mysqld

# 清理审计日志
$ find /mysql/logs -name “audit.log.*” -mtime +30 -type f -exec rm -f {} \;

# 4. 定期备份
# 执行完全备份
$ /mysql/scripts/backup_mysql.sh

# 验证备份
$ gunzip -c /mysql/backup/full_backup_20260409_010000.sql.gz | head -n 100

# 5. 定期更新
# 检查MySQL版本
mysql> SELECT VERSION();
+———–+
| VERSION() |
+———–+
| 8.4.0 |
+———–+

# 下载最新版本
$ wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.1-el7-x86_64.tar.gz

# 升级MySQL
# 风哥教程参考MySQL官方文档进行升级

# 6. 文档更新
# 更新数据库文档
$ /mysql/scripts/generate_documentation.py

# 更新维护文档
$ vi /mysql/docs/maintenance.md

# 7. 培训与演练
# 数据库管理员培训
# 定期进行数据库管理员培训，提高技能水平

# 容灾演练
# 定期进行容灾演练，确保灾备系统的有效性

# 故障演练
# 定期进行故障演练，提高团队的应急响应能力