MySQL DBA工作笔记-数据库管理.架构优化与运维开发

第1 章 MySQL 发展和技术选型 1.1 如何看待MySQL ..............................................................................................................1 1.1.1 MySQL 始出...........................................................................................................1 1.1.2 MySQL 学习周期和难度........................................................................................1 1.1.3 解读DB-Engines 的正确姿势................................................................................2 1.1.4 MySQL 主要的一些分支........................................................................................3 1.1.5 如何看待MySQL 的技术发展...............................................................................3 1.2 如何看待MySQL 存储引擎..............................................................................................5 1.2.1 InnoDB 发展时间线...............................................................................................6 1.2.2 存储引擎之战.........................................................................................................7 1.2.3 存储引擎矩阵图.....................................................................................................7 1.3 MySQL 软件和版本选型...................................................................................................8 1.3.1 选择官方版的原因.................................................................................................9 1.3.2 Percona 分支的优劣对比........................................................................................9 1.3.3 选择MariaDB 的原因.......................................................................................... 10 1.3.4 MySQL 版本选型................................................................................................. 10 1.3.5 分支选择的参考和标准........................................................................................ 11 1.3.6 初步结论............................................................................................................... 13 1.4 MySQL 常用工具选择和建议......................................................................................... 13 1.4.1 运维管理类工具................................................................................................... 14 1. 主流运维管理工具........................................................................................... 14 2. 数据备份恢复工具........................................................................................... 14 3. MySQL 审计插件............................................................................................ 15 1.4.2 应用工具............................................................................................................... 15 1. 客户端工具...................................................................................................... 15 2. 数据库版本管理工具....................................................................................... 15 1.4.3 监控管理类工具................................................................................................... 16 1. 操作系统监控.................................................................................................. 16 2. 性能监控工具.................................................................................................. 16 1.4.4 诊断和优化工具................................................................................................... 16 1. 诊断工具.......................................................................................................... 16 2. 性能测试工具.................................................................................................. 18 3. 慢日志分析工具.............................................................................................. 18 1.4.5 初步结论............................................................................................................... 18 1.5 MySQL 安装.................................................................................................................... 19 1.5.1 常见的三种安装方式............................................................................................ 19 1.5.2 MySQL 安装规范................................................................................................. 20 1.5.3 MySQL 5.7 安装部署实践与总结........................................................................ 20 1.5.4 搭建从库............................................................................................................... 23 案例1-1：MySQL 频繁停库的问题分析........................................................................ 25 第2 章 理解MySQL 体系结构 2.1 通过文件来了解MySQL 体系结构................................................................................. 29 2.1.1 从例子来理解MySQL 存储结构......................................................................... 32 2.1.2 慢日志诊断........................................................................................................... 32 案例2-1：MySQL 日志故障的处理和分析.................................................................... 35 2.2 玩转MySQL 数据字典.................................................................................................... 38 2.2.1 MySQL 巡检模块：Sys Schema 的设计.............................................................. 39 2.2.2 解惑：MySQL 关于数据字典的一个疑问........................................................... 47 2.3 InnoDB 体系结构............................................................................................................ 50 2.3.1 InnoDB 体系结构图............................................................................................. 51 2.3.2 查看InnoDB 状态的小技巧................................................................................. 52 2.3.3 InnoDB 的多线程技术.......................................................................................... 53 2.3.4 InnoDB 的缓存池管理技术.................................................................................. 55 2.3.5 InnoDB 中的脏页管理.......................................................................................... 58 2.3.6 InnoDB 的日志管理............................................................................................. 60 2.3.7 InnoDB 中的检查机制.......................................................................................... 62 2.3.8 MySQL 是如何保证数据完整性的...................................................................... 63 2.4 换个角度看MySQL......................................................................................................... 66 2.4.1 MySQL 里的一些极限值...................................................................................... 66 案例2-2：关于MySQL 中的一些极限值的初步验证纠错............................................. 67 2.4.2 mysql. service 启动脚本浅析................................................................................ 68 2.4.3 MySQL 待改进的一些问题.................................................................................. 71 2.5 MySQL 参数解析............................................................................................................ 72 2.5.1 MySQL 参数变化分析.......................................................................................... 73 2.5.2 MySQL 5.7 参数解析............................................................................................ 74 第3 章 MySQL 基础运维面面观 3.1 环境部署和构建.............................................................................................................. 76 3.1.1 源码安装MySQL ................................................................................................. 76 3.1.2 在eclipse 中配置MySQL 源码环境.................................................................... 80 3.1.3 分分钟搭建MySQL 一主多从环境...................................................................... 87 3.2 MySQL 服务管理............................................................................................................ 88 3.2.1 Socket 连接........................................................................................................... 88 3.2.2 TCP/IP 连接.......................................................................................................... 89 3.2.3 MySQL 访问模式的演进...................................................................................... 89 3.2.4 无密码登录........................................................................................................... 90 案例3-1：通过shell 脚本检测MySQL 服务信息.......................................................... 91 案例3-2：MySQL 密码加密认证的简单脚本................................................................. 93 案例3-3：MySQL 中如何得到权限信息........................................................................ 94 方法1：重新导出导入整个数据库...................................................................... 94 方法2：导出mysql 的权限配置.......................................................................... 94 方法3：pt 工具导出............................................................................................ 95 3.3 MySQL 备份恢复............................................................................................................ 97 3.3.1 数据安全警示录................................................................................................... 97 3.3.2 常规备份方案....................................................................................................... 98 备份工具1：mysqldump ...................................................................................... 99 备份工具2：xtrabackup 工具............................................................................ 101 备份工具3：mysqlpump .................................................................................... 102 备份工具4：mysqlbackup.................................................................................. 104 备份工具5：binlog 备份工具............................................................................ 105 数据导出小技巧................................................................................................. 105 数据导入小技巧................................................................................................. 106 3.3.3 MySQL 数据恢复............................................................................................... 106 恢复方法1：使用xtrabackup 进行数据全量恢复............................................. 107 恢复方法2：使用xtrabackup 进行数据增量恢复............................................. 109 恢复方法3：使用mysqlbinlog 手工恢复.......................................................... 112 恢复方法4：使用开源工具恢复数据 binlog2sql.............................................. 115 恢复方法5：使用参数innodb_force_recovery .................................................. 117 恢复方法6：基于逻辑的数据恢复.................................................................... 117 恢复方法7：基于冷热数据分离的恢复思路.................................................... 118 恢复方法8：基于句柄的无备份恢复................................................................ 119 恢复方法9：直接恢复物理文件........................................................................ 121 3.4 MySQL 安全审计.......................................................................................................... 122 3.4.1 常用方法............................................................................................................. 122 3.4.2 性能测试结果..................................................................................................... 123 3.4.3 测试小结............................................................................................................. 123 第4 章 SQL 开发规范和基础 4.1 数据库开发规范............................................................................................................ 124 4.1.1 配置规范............................................................................................................. 124 4.1.2 建表规范............................................................................................................. 125 iv MySQL DBA 工作笔记：数据库管理、架构优化与运维开发 4.1.3 命名规范............................................................................................................. 125 4.1.4 索引规范............................................................................................................. 125 4.1.5 应用规范............................................................................................................. 126 4.1.6 分表规范............................................................................................................. 127 4.1.7 存储过程规范..................................................................................................... 127 4.1.8 安全规范............................................................................................................. 127 4.1.9 数据安全规范..................................................................................................... 128 案例4-1：MySQL 无法创建表的问题分析.................................................................. 131 4.2 解读MySQL 数据类型.................................................................................................. 135 4.2.1 MySQL 整数类型............................................................................................... 135 案例4-2：数值类型在binlog 中需要注意的细节......................................................... 136 4.2.2 MySQL 小数类型............................................................................................... 138 4.2.3 MySQL 字符串类型........................................................................................... 139 案例4-3：MySQL 中需要注意的字段长度.................................................................. 140 4.2.4 MySQL 日期类型............................................................................................... 142 案例4-4：MySQL 多版本的时间类型问题.................................................................. 143 4.2.5 JSON 类型.......................................................................................................... 144 4.3 MySQL 特有的SQL ...................................................................................................... 146 4.3.1 新增类................................................................................................................ 146 4.3.2 删除类................................................................................................................ 147 4.3.3 修改类................................................................................................................ 148 专题4-1：解读Replace into 语句................................................................................. 148 4.3.4 查询类................................................................................................................ 153 4.4 MySQL 常用函数.......................................................................................................... 153 4.4.1 数学函数............................................................................................................. 154 案例4-5：MySQL 字符串中抽取数值的方法............................................................... 155 案例4-6：order by 的妙用............................................................................................. 156 4.4.2 字符串函数......................................................................................................... 157 案例4-7：MySQL 字符函数的压力测试...................................................................... 160 案例4-8：字符串动态匹配........................................................................................... 162 4.4.3 条件控制函数..................................................................................................... 162 4.4.4 日期和时间函数................................................................................................. 163 案例4-9：Now()和sysdate()的差别.............................................................................. 166 4.4.5 系统信息函数..................................................................................................... 169 4.4.6 其他常用的MySQL 函数................................................................................... 169 第5 章 MySQL 运维管理实践 5.1 数据变更管理................................................................................................................ 171 5.1.1 MySQL 脚本部署的四种策略............................................................................ 171 案例5-1：实战对比四种脚本部署策略的优劣............................................................. 172 5.1.2 通过对比来了解online DDL.............................................................................. 175 案例5-2：MySQL 5.5 版本原生的DDL 代价测试....................................................... 176 案例5-3：MySQL 5.7 版本原生的DDL 代价测试....................................................... 176 5.1.3 Online DDL 的两种算法..................................................................................... 177 案例5-4：对比测试online DDL 的两种算法（copy 和inplace） ............................... 177 5.1.4 pt-osc 的原理和实现........................................................................................... 181 案例5-5：源码分析pt-osc 的实现原理........................................................................ 181 案例5-6：平滑删除数据的小技巧................................................................................ 184 5.2 MySQL 复制管理.......................................................................................................... 186 5.2.1 MySQL 复制的四种类型.................................................................................... 186 5.2.2 MySQL 半同步复制........................................................................................... 187 案例5-7：简单测试半同步复制.................................................................................... 190 5.2.3 GTID 的管理模式............................................................................................... 191 案例5-8：修复GTID 复制失败的分析......................................................................... 195 5.2.4 如何看待主从延迟............................................................................................. 200 案例5-9：MySQL 5.6、5.7 版本并行复制测试............................................................ 203 5.2.5 主从数据不一致的分析...................................................................................... 209 案例5-10：经典的自增列问题测试.............................................................................. 210 案例5-11：主从不一致的修复过程.............................................................................. 212 第6 章 MySQL 查询优化 6.1 MySQL 优化基础.......................................................................................................... 215 6.1.1 MySQL 索引解析............................................................................................... 216 6.1.2 推理SQL 的解析过程........................................................................................ 221 案例6-1：group by 问题浅析........................................................................................ 224 6.1.3 读懂执行计划..................................................................................................... 227 6.1.4 使用MySQL Profile 定位性能瓶颈.................................................................... 234 案例6-2：合理评估新特性的使用................................................................................ 238 6.2 SQL 查询优化................................................................................................................ 240 6.2.1 MySQL 中的派生表........................................................................................... 240 6.2.2 MySQL 中的半连接........................................................................................... 243 6.2.3 MySQL 反连接................................................................................................... 247 6.2.4 行值表达式优化................................................................................................. 250 6.3 MySQL 优化技巧.......................................................................................................... 251 6.3.1 MySQL 分页逻辑优化....................................................................................... 251 6.3.2 数据隐式转换..................................................................................................... 254 案例6-3：一条update 语句引发的“血案”................................................................ 256 第7 章 MySQL 事务和锁 7.1 MySQL 并发控制.......................................................................................................... 263 7.1.1 为什么需要事务................................................................................................. 263 7.1.2 MySQL 并发控制技术方案................................................................................ 265 7.1.3 MySQL 中的MVCC........................................................................................... 266 7.2 事务隔离级别................................................................................................................ 269 7.2.1 MySQL 中的隔离级别RR 和RC....................................................................... 270 7.2.2 RR 隔离级别下的unique 失效........................................................................... 272 7.2.3 RR 隔离级别下的更新冲突................................................................................ 274 7.3 MySQL 锁机制.............................................................................................................. 275 7.3.1 MySQL 锁的类型............................................................................................... 275 7.3.2 索引加锁过程的差异.......................................................................................... 278 7.3.3 这样分析一个死锁问题...................................................................................... 280 7.4 经典的死锁案例集........................................................................................................ 283 场景1：3 条insert 语句导致的死锁问题...................................................................... 283 场景2：事务回滚导致的死锁....................................................................................... 285 场景3：自增列导致的死锁........................................................................................... 287 场景4：事务提交导致的死锁问题............................................................................... 288 场景5：delete 和insert 混合的死锁.............................................................................. 291 场景6：2 条delete 语句导致的死锁问题..................................................................... 293 7.5 事务降维........................................................................................................................ 294 降维策略1：存储过程调用转换为透明的SQL 调用................................................... 295 降维策略2：Drop 操作转换为可逆的DDL 操作......................................................... 295 降维策略3：Truncate 操作转换为安全的DDL 操作................................................... 295 降维策略4：DDL 操作转换为DML 操作.................................................................... 296 降维策略5：Delete 操作转换为高效操作.................................................................... 296 降维策略6：Update 操作转换为Insert 操作................................................................ 296 第8 章 MySQL 集群和高可用设计 8.1 MySQL 高可用方案....................................................................................................... 297 8.1.1 MySQL 高可用方案概览.................................................................................... 298 8.1.2 MySQL 高可用方案的建议................................................................................ 298 8.1.3 MySQL 高可用的迭代方案思考........................................................................ 299 8.2 MySQL 高可用方案之MHA......................................................................................... 300 8.2.1 MHA 原理和架构............................................................................................... 300 8.2.2 如何系统的测试MHA ....................................................................................... 303 8.2.3 快速测试MHA 的步骤...................................................................................... 304 8.2.4 从代码关系图理清MHA 的脉络....................................................................... 308 8.2.5 我们可能不知道的MHA 逻辑........................................................................... 312 8.2.6 MHA 的缺点和局限性....................................................................................... 313 8.2.7 MHA 的补充和改进........................................................................................... 314 8.3 MySQL 高可用方案之InnoDB Cluster ......................................................................... 315 8.3.1 InnoDB Cluster 三大件....................................................................................... 316 8.3.2 快速入手InnoDB Cluster 的建议....................................................................... 317 8.3.3 使用sandbox 快速部署InnoDB Cluster............................................................. 318 8.3.4 InnoDb Cluster 核心组件：MGR ....................................................................... 323 8.3.5 部署MGR 的几种姿势....................................................................................... 324 方法1：分分钟搭建MGR 单主/多主测试环境................................................ 324 方法2：线上环境规范部署............................................................................... 325 8.3.6 常见的MGR 问题.............................................................................................. 327 问题1：单主模式加入节点失败........................................................................ 328 问题2：模式配置错误导致无法启动集群........................................................ 328 问题3：节点配置不统一导致集群无法启动.................................................... 328 问题4：数据写入失败修复............................................................................... 329 问题5：模拟灾难.............................................................................................. 329 问题6：如何判断一个复制组中的主节点........................................................ 330 8.3.7 迁移到MGR 需要思考的问题........................................................................... 331 案例8-1：切换到MGR 的参考步骤................................................................. 332 案例8-2：大事务导致的运维系统无法访问..................................................... 336 8.4 基于consul 的高可用扩展方案..................................................................................... 339 8.4.1 基于consul 服务的高可用方案.......................................................................... 339 8.4.2 基于MHA Consul 的MySQL 高可用设计....................................................... 340 8.4.3 MySQL 高可用方案：MGR consul 组合测试................................................... 341 第9 章 MySQL 性能测试 9.1 sysbench 压测MySQL................................................................................................... 344 9.1.1 压测MySQL——环境部署和硬件压测............................................................. 344 9.1.2 压测MySQL 起步.............................................................................................. 348 9.1.3 压测MySQL——提高吞吐量测试..................................................................... 351 9.1.4 压测MySQL——定位压测瓶颈........................................................................ 356 9.1.5 定制sysbench 的Lua 模板................................................................................. 360 9.2 批量初始化数据性能测试............................................................................................. 363 9.2.1 批量初始化数据初步想法.................................................................................. 363 9.2.2 批量初始化数据的方案...................................................................................... 363 方案1：存储过程导入....................................................................................... 363 方案2：使用内存表........................................................................................... 364 方案3：使用程序/脚本生成数据，批量导入................................................... 365 方案4：使用内存表和外部文件导入混合........................................................ 365 viii MySQL DBA 工作笔记：数据库管理、架构优化与运维开发 方案5：存储过程显式事务提交........................................................................ 365 方案6：批量生成insert 语句使用管道导入..................................................... 366 方案7：批量生成insert 语句显式事务提交..................................................... 366 方案8：sysbench 工具生成............................................................................... 366 方案9：Oracle 的极简方案............................................................................... 367