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

本书特色

[

本书是来自一线MySQL DBA的技能进阶笔记，凝结了作者多年数据库管理心得。全书从运维管理、架构优化和运维开发三个层面娓娓道来，精心筛选了作者在实际工作中总结的技巧、对常见问题的处理方法以及对于运维体系的思考和实践；尤其是运维开发章节从0 到1 构建运维体系，能够对DBA已有知识体系和技能栈做到全新梳理。

]

内容简介

[

ACE，腾讯云TVP，一线数据库专家多年运维经验凝聚成简，全面理解新时代DBA技术转型思路和运维心得

]

作者简介

[

杨建荣，技术爱好者，dbaplus社群发起人之一，Oracle ACE，腾讯云TVP，YEP成员。拥有十多年数据库开发和运维经验，目前专注于开源技术、运维自动化和性能调优。拥有Oracle 10g OCP、OCM、MySQL OCP认证，对Shell、Java、Python有一定功底。每天通过微信、 博客进行技术分享，已连续坚持2000多天。

]

目录

第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

封面

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

作者:杨建荣

页数:596

定价:¥99.0

出版社:中国铁道出版社

出版日期:2019-08-01

ISBN:9787113260347

PDF电子书大小:142MB 高清扫描完整版