★ FEATURED ARTICLE 1. 项目概述MySQL作为最流行的开源关系型数据库之一在企业级应用中扮演着核心角色。这次我想分享一个完整的MySQL部署与监控方案这个方案在我们生产环境已经稳定运行了3年多处理着日均千万级的查询请求。不同于简单的安装教程我会重点讲解生产环境中必须考虑的配置优化、安全加固和监控策略。这套方案适用于从中小型应用到大型分布式系统的各种场景特别适合需要长期稳定运行的业务系统。2. MySQL服务部署2.1 环境准备与安装对于生产环境我强烈建议使用Linux系统。这里以Ubuntu 20.04 LTS为例# 更新软件包索引 sudo apt update # 安装MySQL服务器 sudo apt install mysql-server -y # 安全初始化 sudo mysql_secure_installation安装过程中有几个关键点需要注意为root账户设置强密码建议16位以上包含大小写字母、数字和特殊字符移除匿名用户禁止root远程登录移除测试数据库2.2 生产级配置优化默认的MySQL配置只适合开发和测试环境。生产环境需要调整以下关键参数在/etc/mysql/mysql.conf.d/mysqld.cnf中[mysqld] # 内存相关配置 innodb_buffer_pool_size 12G # 建议为系统内存的50-70% innodb_buffer_pool_instances 8 # 每个实例至少1GB # 日志配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 log_error /var/log/mysql/error.log # 连接配置 max_connections 500 wait_timeout 300 interactive_timeout 300 # 其他重要参数 innodb_flush_log_at_trx_commit 1 sync_binlog 1重要提示调整innodb_buffer_pool_size后MySQL需要重启才能生效。建议在业务低峰期进行操作。2.3 用户权限管理生产环境中必须遵循最小权限原则-- 创建应用专用用户 CREATE USER app_user% IDENTIFIED BY Complex_Password_123!; -- 授予特定数据库权限 GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO app_user%; -- 创建监控专用用户用于Prometheus等监控工具 CREATE USER monitorlocalhost IDENTIFIED BY Monitor_Pass_456!; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO monitorlocalhost;3. MySQL监控体系搭建3.1 监控指标分类一个完整的MySQL监控体系应该包含以下四类指标性能指标QPS、TPS、连接数、线程状态等资源指标CPU、内存、磁盘I/O、网络流量等可用性指标服务状态、复制延迟、备份状态等业务指标慢查询数量、锁等待时间、缓存命中率等3.2 Prometheus Grafana监控方案3.2.1 部署mysqld_exporter# 下载最新版mysqld_exporter wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz # 解压并安装 tar xvfz mysqld_exporter-* sudo cp mysqld_exporter-*/mysqld_exporter /usr/local/bin/ # 创建配置文件 sudo tee /etc/.mysqld_exporter.cnf EOF [client] usermonitor passwordMonitor_Pass_456! EOF # 创建systemd服务 sudo tee /etc/systemd/system/mysqld_exporter.service EOF [Unit] DescriptionMySQL Prometheus Exporter Afternetwork.target [Service] Usermysql Groupmysql ExecStart/usr/local/bin/mysqld_exporter \ --config.my-cnf/etc/.mysqld_exporter.cnf \ --collect.global_status \ --collect.global_variables \ --collect.slave_status \ --collect.info_schema.innodb_metrics \ --collect.info_schema.processlist \ --collect.info_schema.tables \ --collect.info_schema.tablestats \ --collect.info_schema.userstats Restartalways [Install] WantedBymulti-user.target EOF # 启动服务 sudo systemctl daemon-reload sudo systemctl enable mysqld_exporter sudo systemctl start mysqld_exporter3.2.2 Grafana仪表板配置推荐使用MySQL Overview仪表板ID7362它包含了以下几个关键视图MySQL状态概览正常运行时间、版本、QPS、TPS等查询性能慢查询、全表扫描、排序操作等连接信息当前连接数、连接错误、线程状态等InnoDB指标缓冲池状态、行操作、锁等待等系统资源CPU、内存、磁盘、网络使用情况3.3 关键告警规则配置在Prometheus中配置以下告警规则groups: - name: mysql.rules rules: - alert: MySQLDown expr: up{jobmysqld} 0 for: 1m labels: severity: critical annotations: summary: MySQL instance down (instance {{ $labels.instance }}) description: MySQL has been down for more than 1 minute - alert: HighThreadsRunning expr: mysql_global_status_threads_running 100 for: 5m labels: severity: warning annotations: summary: High number of running threads (instance {{ $labels.instance }}) description: Number of running threads is high ({{ $value }}) - alert: ReplicationLag expr: mysql_slave_status_seconds_behind_master 30 for: 5m labels: severity: warning annotations: summary: MySQL replication lag (instance {{ $labels.instance }}) description: Replication lag is {{ $value }} seconds4. 高级监控与优化技巧4.1 慢查询分析与优化除了开启慢查询日志还可以使用pt-query-digest工具进行分析# 安装Percona Toolkit sudo apt install percona-toolkit # 分析慢查询日志 pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt分析报告会显示最耗时的查询类型查询执行时间分布表扫描和索引使用情况具体的优化建议4.2 性能模式(Performance Schema)监控MySQL 5.7提供了更强大的Performance Schema-- 查看等待事件统计 SELECT event_name, count_star, sum_timer_wait/1000000000 as sum_sec FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY sum_timer_wait DESC LIMIT 10; -- 查看最耗时的SQL语句 SELECT digest_text, count_star, avg_timer_wait/1000000000 as avg_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_timer_wait DESC LIMIT 10;4.3 备份监控监控备份是否成功执行非常重要。可以添加以下检查# 检查最近一次备份时间 backup_file$(ls -t /backup/mysql/ | head -1) backup_time$(stat -c %Y /backup/mysql/$backup_file) current_time$(date %s) hours_since_backup$(( (current_time - backup_time) / 3600 )) if [ $hours_since_backup -gt 24 ]; then echo Warning: No fresh backup in last 24 hours fi5. 常见问题排查5.1 连接数耗尽现象应用报Too many connections错误解决方案临时增加连接数SET GLOBAL max_connections 1000;查找连接泄漏SELECT user, host, db, command, time, state, info FROM information_schema.processlist ORDER BY time DESC;优化应用连接池配置建议使用HikariCP5.2 磁盘空间不足现象数据库写入失败磁盘空间报警紧急处理# 查找大表 SELECT table_schema, table_name, round(((data_length index_length) / 1024 / 1024), 2) as size_mb FROM information_schema.TABLES ORDER BY size_mb DESC LIMIT 10; # 清理二进制日志如果启用 PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);5.3 复制延迟现象从库落后主库监控显示复制延迟增加排查步骤检查复制状态SHOW SLAVE STATUS\G检查从库负载top -H -p $(pgrep -f mysqld)检查网络延迟ping -c 10 master_host6. 生产环境经验总结经过多年的MySQL运维我总结了以下几点关键经验配置不是一成不变的随着数据量增长和业务变化需要定期review和调整配置参数。特别是innodb_buffer_pool_size应该随着数据量增加而增加。监控指标要 actionable不要监控那些你不知道如何应对的指标。每个监控项都应该有明确的应对方案。备份重于一切没有验证过的备份等于没有备份。定期测试备份恢复流程。预防优于修复通过性能预测和容量规划在问题发生前进行扩展。文档至关重要记录所有的配置变更、问题排查过程和解决方案。这在新成员加入或问题重现时非常宝贵。这套MySQL部署和监控方案在我们多个生产环境中运行良好但每个业务场景都有其特殊性建议根据实际需求进行调整。特别是监控指标和告警阈值需要结合业务特点来设定。
阅读完成 · 觉得有帮助？
