MySQL DBA工作笔记：数据库管理、架构优化与运维开发 PDF 下载

第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 通过对比来了解onlineDDL..............................................................................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 快速部署InnoDBCluster............................................................. 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