目录

第 03 节 理解 MySQL 体系结构

第 04 节 配置 MySQL

第 05 节 监控 MySQL

第 06 节 管理 MySQL 用户

第 07 节 MySQL 安全

第 08 节 维护稳定的系统

第 09 节 优化查询性能

第 10 节 选择备份策略

第 11 节 执行备份

第 12 节 配置复制拓扑

第 13 节 管理复制拓扑

第 14 节 使用 MySQL InnoDB Cluster 实现高可用性

---

第 03 节 理解 MySQL 体系结构

核心知识点：

1. 体系结构组件：

   • 包含 MySQL 服务器进程（mysqld）、客户端程序、非客户端程序，支持本地 / 远程跨操作系统连接。
   • 服务器进程为多线程单进程，管理磁盘 / 内存中数据库，支持并发连接和多存储引擎（如 InnoDB、MyISAM）。

2. 核心分层：

   • 连接层：处理通信协议（TCP/IP、Socket 等）、线程管理、SSL 加密。
   • SQL 层：解析器（语法验证）、授权（权限检查）、优化器（生成执行计划）、执行器、日志记录。
   • 存储层：多种存储引擎，InnoDB 为默认，支持事务、MVCC、行级锁；其他引擎（MyISAM、MEMORY 等）各有特性。

3. 数据存储与日志：

   • 数据字典：存储元数据，MySQL 8 采用事务性数据字典（基于 InnoDB）。
   • 表空间：InnoDB 包含系统表空间、单表文件表空间、通用表空间等。
   • 日志：重做日志（保障事务一致性）、还原日志（用于回滚和 MVCC）。

4. 内存管理：

   • 分为全局内存（如 InnoDB 缓冲池）和会话内存（如排序缓冲区），缓冲池建议配置为系统内存的 70-80%。

第 04 节 配置 MySQL

核心知识点：

1. 配置方式：

   • 命令行选项：启动时指定（如--datadir、--port），适合临时配置。
   • 选项文件：Linux 用my.cnf，Windows 用my.ini，按组（如[mysqld]、[client]）组织，支持优先级和包含其他文件。

2. 系统变量：

   • 分 GLOBAL（影响整体）和 SESSION（影响单个连接），部分可动态修改（SET GLOBAL/SESSION）。
   • 持久化变量：SET PERSIST可保存配置至mysqld-auto.cnf，重启后生效。

3. 多实例部署：

   • 同一主机可启动多个服务器，需保证数据目录、端口、套接字文件等唯一，可通过mysqld_multi或systemd管理。

第 05 节 监控 MySQL

核心知识点：

1. 日志监控：

   • 错误日志：记录启动 / 关闭及异常信息。
   • 慢查询日志：记录执行时间超过long_query_time的语句，可通过mysqldumpslow分析。
   • 二进制日志：记录数据修改，用于复制和恢复。

2. 状态与性能监控：

   • 状态变量：SHOW GLOBAL STATUS查看全局状态（如连接数、慢查询数）。
   • 性能模式（Performance Schema）：跟踪细粒度性能数据，如锁等待、I/O 统计。
   • sys模式：提供简化视图，便于分析性能问题（如用户资源消耗、临时表使用）。

3. 企业工具：

   • 企业审计：基于策略记录活动，日志为 XML 格式。
   • 企业监视器：可视化监控性能、生成预警和报告。

第 06 节 管理 MySQL 用户

核心知识点：

1. 用户与角色：

   • 用户帐户格式为'user'@'host'，主机支持通配符（如%表示任意主机）。
   • 角色：权限集合，可授予用户或其他角色，需激活后使用（服务器、用户、会话级激活）。

2. 权限控制：

   • 权限范围：全局（*.*）、数据库（db.*）、表、列、存储例程。
   • 授权语句：GRANT授予权限，REVOKE撤销，SHOW GRANTS查看权限。

3. 身份验证与安全：

   • 默认认证插件为caching_sha2_password，支持多种插件（如 PAM、LDAP）。
   • 口令管理：可设置有效期、手动过期，validate_password组件强制复杂口令。

第 07 节 MySQL 安全

核心知识点：

1. 网络安全：

   • 限制端口访问（默认 3306），使用防火墙隔离，优先通过本地套接字连接。
   • SSL/TLS 加密：服务器默认启用，客户端可指定--ssl-mode控制连接安全性。

2. 口令与账户安全：

   • 禁用空口令和匿名账户，使用mysql_secure_installation加固。
   • 连接控制插件：限制失败登录尝试，增加暴力破解难度。

3. 防御措施：

   • 防止 SQL 注入：使用参数化查询，避免拼接用户输入。
   • 企业防火墙：按白名单允许 SQL 语句，支持记录、保护、检测模式。

第 08 节 维护稳定的系统

核心知识点：

1. 稳定性保障：

   • 建立性能基线：记录正常状态下的 CPU、内存、I/O 及 MySQL 指标。
   • 硬件与环境：确保冗余（电源、网络），监控磁盘空间和 RAID 状态。

2. 容量规划：

   • 监控表大小：通过INFORMATION_SCHEMA.TABLES和文件系统查看逻辑 / 物理大小。
   • 扩展性：纵向扩展（升级硬件）、横向扩展（分片、复制）。

3. 故障排除：

   • 锁定问题：通过SHOW PROCESSLIST、性能模式查看锁争用（表锁、行锁、元数据锁）。
   • InnoDB 恢复：自动恢复失败时，使用--innodb_force_recovery选项手动恢复。

第 09 节 优化查询性能

核心知识点：

1. 识别需优化的查询：

   • 优先优化执行频繁的查询（即使单次耗时短），而非仅关注慢查询。

2. 索引优化：

   • 索引类型：主键、唯一索引、普通索引、全文索引等。
   • EXPLAIN分析：查看执行计划，判断是否使用索引（type列显示访问类型，如const、ref、ALL）。

3. 索引维护：

   • ANALYZE TABLE更新统计信息，帮助优化器选择更好的执行计划。
   • 不可见索引：临时隐藏索引，测试其对查询的影响。

第 10 节 选择备份策略

核心知识点：

1. 备份类型：

   • 热备：不中断服务（如 InnoDB 在线备份）。
   • 冷备：关闭服务器后复制文件。
   • 温备：允许读取但禁止写入。

2. 备份技术：

   • 逻辑备份：mysqldump生成 SQL 文件，跨平台但速度慢。
   • 物理备份：复制数据文件（如ibdata*、.ibd），速度快但依赖存储引擎。
   • 增量备份：基于二进制日志，补充完全备份。

3. 策略制定：

   • 结合备份类型（如每日完全备份 + 每小时增量备份），考虑 RTO（恢复时间目标）和 RPO（恢复点目标）。

第 11 节 执行备份

核心知识点：

1. 备份工具：

   • mysqldump：逻辑备份，支持单库、单表备份，--master-data记录复制坐标。
   • mysqlbackup（企业版）：物理备份，支持热备、增量备份和单文件备份。
   • 原始文件备份：适用于 MyISAM（复制.MYD、.MYI）和 InnoDB（需关闭服务器或使用快照）。

2. 二进制日志备份：

   • mysqlbinlog解析日志，用于时间点恢复（--start-datetime、--stop-position）。

3. 复制与备份：

   • 从从属服务器备份，避免影响主服务器，支持多源复制集中备份。

第 12 节 配置复制拓扑

核心知识点：

1. 复制基础：

   • 主从复制：主库写入二进制日志，从库通过 I/O 线程获取并应用（SQL 线程）。
   • 支持多种拓扑：主从、级联（中继从库）、双向、循环、多源复制。

2. 配置步骤：

   • 主库：启用二进制日志，创建复制用户（REPLICATION SLAVE权限）。
   • 从库：CHANGE MASTER TO指定主库信息，START SLAVE启动复制。

3. 增强特性：

   • GTID：全局事务 ID，简化复制配置和故障转移。
   • 半同步复制：主库等待至少一个从库确认后提交，增强数据完整性。

第 13 节 管理复制拓扑

核心知识点：

1. 故障转移：

   • 基于日志坐标：手动指定新主库的日志位置，易出错。
   • 基于 GTID：自动识别已执行事务，简化切换。

2. 复制线程与监控：

   • 线程：主库的 Binlog 转储线程，从库的 I/O 线程和 SQL 线程（多线程从库支持并行应用）。
   • 监控：SHOW SLAVE STATUS查看复制状态（延迟、错误），性能模式跟踪线程详情。

3. 问题排查：

   • 常见错误：网络中断、权限不足、数据不一致，通过错误日志和Last_IO_Error定位。

第 14 节 使用 MySQL InnoDB Cluster 实现高可用性

核心知识点：

1. 架构与特性：

   • 基于组复制（Group Replication），至少 3 个节点，自动故障转移。
   • 模式：单主（一写多读）、多主（多写，需避免冲突）。

2. 配置与管理：

   • 工具：MySQL Shell通过 AdminAPI 配置，MySQL Router实现客户端透明连接。
   • 部署：支持沙盘（本地测试）和生产环境，需配置 GTID、二进制日志等。

3. 维护操作：

   • 检查状态：cluster.status()查看集群健康度。
   • 恢复：forceQuorumUsingPartitionOf解决脑裂，rebootClusterFromCompleteOutage恢复完全停机。