高可用MySQL：构建健壮的数据中心 

（美）贝尔（Bell,C.），（美）肯德尔（Kindahl,M.），（美）塞尔曼

（Thalmann,L.）著

宁青，唐李洋，诸云萍译

ISBN 978-7-121-14407-3  

2011年10月出版

定价：98.00元

16开

604页

内 容 简 介

本书是“MySQL High Availability”的中文翻译版，主要讲解真实环境下如何使用MySQL 的复制、集群和监控特性，揭示MySQL 可靠性和高可用性的方方面面。本书由MySQL 开发团队亲自执笔，定位于解决MySQL 数据库的常见应用瓶颈，在保持MySQL 的持续可用性的前提下，挖潜各种提高性能的解决方案。本书分为三个部分。第一部分讲述MySQL 复制，包括高可用性和横向扩展，第二部分介绍构建健壮的数据中心时监控和性能方面的问题，第三部分给出其他MySQL 相关内容，包括云计算和MySQL 集群。

本书读者对象是MySQL 专业人士。假设读者已拥有SQL、MySQL 管理和操作系统的基础背景知识。书中介绍一些关于复制、灾难恢复、系统监控及其他高可用性主题的背景信息。相关有用的背景知识请参考其他书籍的第1 章。对于相关专业的师生，本书也有很高的参考价值。

译者序

MySQL 是个非常优秀的开源数据库，也是装机量最多的数据之一。少数几台MySQL人工就可以管理了，但当服务器几十、上百台的时候，如果光靠人力维护，这个维护成本就相当高了。如何提高MySQL 服务器的可用性这个问题摆在了MySQL DBA 和系统架构师们面前。你手上这本书中讲了些方法和工具，能够帮助你解决这些问题中的大部分。

本书着重介绍了MySQL 复制，监控MySQL 和搭建MySQL 高可用集群。

1．MySQL 复制技术简单来说就是将Master 的数据同步到Slave 上。通过使用MySQL复制技术可以提高数据库系统的可用性, 可以实现数据的异地备份, 可以实现服务器负载均衡。本书第一部分将从原理到应用逐步介绍MySQL 复制技术。

2．没有监控就像《新约》描述的那样“那在黑暗中行走的，不知往何处去”，如何在发生问题前就能得到预示，监控就是我们前面的那盏灯。本书第二部分将介绍各种需要监控的地方以及如何监控它们，并如何解决复制中出现的问题。在第二部分最后将介绍下MySQL 企业级的监控工具。

3．云计算这个概念现在被炒得越来越火，让人云里雾里。什么是云，MySQL 能在云中干嘛，MySQL Cluster 又是什么，如何搭建MySQL 高可用集群用将会在第三部分做出介绍。

翻译本书纯属意外，去年9 月间朱少民老师问我们数据团队有没有兴趣翻译一本关注于MySQL 高可用性书籍，当时恰逢我们在一个名为Athena 的项目里，使用的是Cassandra和MySQL 作为底层存储部分，对这本书非常感兴趣，于是我们一口答应了。在这里向朱老师说声感谢。有兴趣的同学可以关注一下他的微博http://weibo.com/kerryzhu。

几个月来大家都在忙着翻译和本职工作的同时，李洋同学准备去美国留学，诸云萍刚刚怀上了宝宝，而我这时候则忙着毕业、找工作、筹备婚礼，并准备迎接我们家的新成员。虽然有这些外在的干扰，但我们也是战战兢兢地很努力地去翻译本书，主要是怕没翻好被读者骂。因此看完这本书仅仅只是开始，个人微博地址是http://weibo.com/ninqing，如果你对这本书中的内容有所疑问，欢迎在上面询问和拍砖，我会尽力满足各位的愿望。

本书由宁青、唐李洋、诸云萍三人合作翻译完成。同时感谢张春雨、高洪霞、刘皎、丁曼几位编辑的辛苦工作，是你们把这本书呈现给读者，在此十分感谢你们！

同样要感谢我的朋友们：Grant、黄波、高飞、谢恒，等等，有你们的帮助才让我们在技术的道路上得到了锻炼。还要感谢我的爱人王新，还有女儿宁悦晗，你太淘气了。

宁青

2011 年07 月30 日

写于西子湖畔

序

关于复制（replication）的研究很多，但其中的大多数研究成果都没有得到应用。相反，MySQL 复制已经被广泛部署，但其原理并不为大多数人所知。本书将改变这种状况。本书中介绍的内容比较适合以下人群：愿意阅读大量的源代码，而且在生产环境中花很多时间进行调试，能够在深夜会议中探讨这些内容的人。

复制允许在出现不可避免的故障的情况下提供高可用的数据服务。故障的原因很多，包括磁盘、服务器或数据中心的损耗。即使所有硬件都是完美无缺且完全冗余的，还有人为因素的影响。例如，数据库表可能被误删，应用程序可能写入了不正确的数据等，总会有偶然故障发生。但通过合理的准备工作，可以保证从故障中恢复，关键是冗余和备份。MySQL 复制支持冗余和备份。

但MySQL 的复制并不仅限于支持故障恢复，它还频繁用于读操作的横向扩展（scaleout）。MySQL 可以实现大量服务器的高效复制。对于那些读频繁的应用，在商用硬件上支持大量查询是一个低成本的有效策略。

MySQL 复制还有其他有用的应用。在线DDL 是关系型数据库管理系统中非常复杂的一个特性。MySQL 不支持在线DDL，但通过使用复制，往往可以足够好地部分实现它。如果有创意，还可以使用复制做更多的事情。

复制是使得MySQL 如此广泛流行的特性之一，它允许将流行的MySQL 原型转换为成功的商业关键部署。复制主张简单和便于使用，这一点和MySQL 十分相似。然而，在生产环境中运行往往不够完美。本书解释了成功使用MySQL 复制所必须知道的内容，帮助读者理解复制是怎样实现的，哪些地方可能出错，怎样防止问题的出现，以及怎样在问题出现的时候解决它们——尽管你已经很努力地避免这些问题。

MySQL 复制还在继续完善中。与故障一样，变化总是存在的。MySQL 需要不断应对这些变化，使得复制更高效、更健壮、更有趣。例如，基于行的复制（Row-basedreplication）是MySQL 5.1 中的新特性。

尽管MySQL 部署形态各异，规模各不相同，我最关心的还是互联网应用的数据服务。MySQL 到分布式存储系统（如HBase 和Hadoop）复制的可能性也使我兴奋不已。这样MySQL 就可以更好地共享数据中心。

我曾经在Facebook 和Google 的团队支持重要的MySQL 部署，有机会、问题和时间学习这本书中所覆盖的很多东西。本书的作者们同样是MySQL 复制的专家，通过阅读这本书读者可以分享他们的专业知识。

——Mark Callaghan

前 言

本书的作者们一直都在MySQL 领域努力且工作了多年。Charles Bell 是复制和备份领域的高级开发人员，其兴趣包括MySQL、数据库理论、软件工程和敏捷开发实践等。Mats Kindahl 博士是复制的主要开发人员，同时也是MySQL Backup 和Replication 小组的成员。他是MySQL 基于行的复制的主架构师和实现者，还开发了MySQL 的单元测试框架。Lars Thalmann 博士是MySQL Backup 和Replication 小组的开发经理和技术领导，设计了很多复制和备份的特性，主要从事MySQL 集群、复制和备份技术的开发工作。

为了填补很多MySQL 书籍的缺口，我们写了这本书。关于MySQL 有很多出色的书籍，但很少集中讲述它的高级特性和复制，诸如高可用性、可靠性和可维护性。本书将涵盖所有这些主题，当然还有更多内容。

我们希望阅读更加有趣，故添加了一个遭遇老板提出种种要求的MySQL 专家的小故事。在该故事中，你将认识Joel Thomas，最近他决定在一家刚开始使用MySQL 的公司工作。从中你可以看到Joel 学习MySQL 的方式，以及如何处理MySQL 专家所面临的一些最棘手的问题。希望你会觉得这部分内容很有趣。

读者对象

本书读者对象是MySQL 专业人士。我们假设读者已拥有SQL、MySQL 管理和操作系统的基础背景知识。我们会试着介绍一些关于复制、灾难恢复、系统监控及其他高可用性主题的背景信息。相关有用的背景知识请参考其他书籍的第1 章。

本书的组织结构

本书分为三个部分。第一部分讲述MySQL 复制，包括高可用性和横向扩展，第二部分介绍构建健壮的数据中心时监控和性能方面的问题，第三部分给出其他MySQL 相关内容，包括云计算和MySQL 集群。

第一部分 复制

第1 章 引言 解释了本书的价值，并提供了阅读的情境。

第2 章 MySQL 复制原理 讨论了建立基本复制的手动和自动过程。

第3 章 二进制日志 解释了与复制、灾难恢复、故障排除和其他管理任务相关的关键文件。第4 章 复制的高可用性 给出了服务器故障恢复的多种方法，包括自动化脚本的使用。

第5 章 MySQL 复制的横向扩展 介绍了提高响应时间和处理大数据集的多种技术和方法。第6 章 高级复制 描述了很多主题，包括安全数据传输和基于行的复制。

第二部分 监控和灾难恢复

第7 章 监控入门 给出了必须注意的主要操作系统参数，以及监控它们的工具。

第8 章 MySQL 监控 介绍了几种数据库行为和性能的监控工具。

第9 章 存储引擎监控 更加详细地解释了需要监控的参数，重点描述MyISAM 或者InnoDB 相关的问题。

第10 章 复制监控 详细描述了如何跟踪主节点和从节点。

第11 章 复制的故障排除 介绍了如何处理故障、重启、崩溃及其他意外事故。

第12 章 保护你的投资 解释了备份和灾难恢复技术的使用。

第13 章 MySQL 企业版 介绍了用于简化上述很多任务的工具套件。

第三部分 高可用性环境第

14 章 云计算解决方案 介绍了最流行的云计算服务即亚马逊的AWS，并提供了在这种虚拟化环境中使用MySQL 的技术。

第15 章 MySQL 集群 展示了如何使用MySQL 集群实现高可用性。

附录A 复制技巧和窍门 提供了某些情况下很有用的过程包。

目 录
前言 ................................................................................i
第一部分 复制
第1章 引言 ....................................................................3
到底什么是复制 .......................................................................................................5
那么，是否需要备份 ...............................................................................................6
什么是监控 ..............................................................................................................7
还有什么我可以阅读的 ............................................................................................7
小结 .........................................................................................................................8
第2章 MySQL复制原理 ..................................................9
复制的基本步骤 .....................................................................................................10
配置Master ..................................................................................................... 11
配置Slave .......................................................................................................12
连接Master和Slave .........................................................................................13
二进制日志简介 .....................................................................................................14
二进制日志记录了什么 ..................................................................................15
观察复制的动作 .............................................................................................16
二进制日志的结构和内容 ..............................................................................17
使用Python管理复制 ......................................................................................20
基本类及函数 .................................................................................................22
操作系统 ........................................................................................................23
服务器类 ........................................................................................................23
服务器角色.....................................................................................................25
建立新Slave ...................................................................................................26
克隆Master ....................................................................................................27
克隆Slave .......................................................................................................29
克隆操作的脚本 .............................................................................................31
执行常见的复制任务 .............................................................................................33
报表 ................................................................................................................33
小结 .......................................................................................................................39
第3章 二进制日志 ........................................................41
二进制日志的结构 .................................................................................................42
Binlog事件的结构 ..........................................................................................44
记录语句 ................................................................................................................45
记录数据操作语言 .........................................................................................46
记录数据定义语言 .........................................................................................46
记录查询 ........................................................................................................47
LOAD DATA INFILE语句 ..............................................................................52
二进制日志过滤器 .........................................................................................54
触发器、事件和存储程序 ..............................................................................56
存储过程 ........................................................................................................61
存储函数 ........................................................................................................64
Events ............................................................................................................66
特殊结构 ........................................................................................................66
非事务性的变化和错误处理 ..........................................................................67
记录事务 ................................................................................................................70
事务缓存 ........................................................................................................71
使用XA进行分布式事务处理 .........................................................................74
二进制日志管理 .....................................................................................................76
二进制日志和系统崩溃安全 ..........................................................................77
Binlog文件轮换（Rotation） .........................................................................78
事故（incident） ............................................................................................79
清除binlog文件 ..............................................................................................80
mysqlbinlog实用工具 .............................................................................................81
基本用法 ........................................................................................................81
解释事件（Interpreting Events） ...................................................................88
二进制日志选项和变量 ..........................................................................................92
小结 .......................................................................................................................94
第4章 基于复制的数据库高可用技术 .............................95
冗余 .......................................................................................................................96
计划 .......................................................................................................................98
Slave故障 .......................................................................................................98
Master故障 .....................................................................................................98
中继服务器故障 .............................................................................................99
灾难恢复 ........................................................................................................99
程序 .......................................................................................................................99
热备份 .......................................................................................................... 102
双Master ....................................................................................................... 107
半同步复制................................................................................................... 116
Slave的提升 ................................................................................................. 119
循环复制 ...................................................................................................... 134
小结 ..................................................................................................................... 138
第5章 MySQL集群的横向扩展 ....................................139
读操作的横向扩展 ............................................................................................... 141
异步复制的价值 ................................................................................................... 142
管理复制拓扑....................................................................................................... 144
应用层负载均衡器示例 ................................................................................ 147
级联复制（Hierarchal Replication）.................................................................... 150
中继服务器的设置 ....................................................................................... 151
使用Python添加中继服务器 ......................................................................... 152
专用Slave ............................................................................................................. 153
过滤复制事件 ............................................................................................... 154
使用过滤将事件分配给Slave ....................................................................... 155
数据分片 .............................................................................................................. 157
分片的表示................................................................................................... 159
数据分区 ...................................................................................................... 160
分片之间的均衡 ........................................................................................... 161
一个分片的例子 ........................................................................................... 163
数据的一致性管理 ............................................................................................... 174
非级联部署中的一致性 ................................................................................ 174
级联部署中的一致性 ........................................................................................... 176
小结 ..................................................................................................................... 182
第6章 高级复制 .........................................................183
复制架构基础....................................................................................................... 184
中继日志的结构 ........................................................................................... 185
复制线程 ...................................................................................................... 187
Slave线程的启动和停止 ............................................................................... 188
通过Internet运行复制........................................................................................... 189
使用内置支持建立安全复制 ........................................................................ 191
使用Stunnel建立安全复制 ............................................................................ 192
细粒度控制复制 ................................................................................................... 194
关于复制状态的信息 .................................................................................... 194
处理断开连接的参数 ........................................................................................... 201
Slave是如何处理事件的 ....................................................................................... 202
管理I/O线程 ................................................................................................. 202
SQL线程的处理 ........................................................................................... 203
Slave的安全和恢复 .............................................................................................. 208
同步，事务和数据库崩溃问题 ..................................................................... 209
保护非事务性语句的规则 ............................................................................ 210
多源复制 .............................................................................................................. 211
基于行的复制....................................................................................................... 214
基于行的复制参数 ....................................................................................... 215
混合模式的复制 ........................................................................................... 215
处理基于行复制的事件 ................................................................................ 216
事件的执行................................................................................................... 220
事件和触发器 ............................................................................................... 222
过滤 .............................................................................................................. 223
小结 ..................................................................................................................... 225
第二部分 监控和灾难恢复
第7章 监控入门 .........................................................229
监控方法 .............................................................................................................. 230
监控的好处 .......................................................................................................... 231
监控系统组件....................................................................................................... 231
处理器 .......................................................................................................... 231
内存 .............................................................................................................. 233
磁盘 .............................................................................................................. 233
网络子系统................................................................................................... 234
监控方法 .............................................................................................................. 235
Linux和UNIX监控 .............................................................................................. 236
进程活动 ...................................................................................................... 237
内存利用率................................................................................................... 241
磁盘利用率................................................................................................... 243
网络活动 ...................................................................................................... 246
常见系统统计信息 ....................................................................................... 248
使用cron自动监控 ........................................................................................ 249
Mac OS X 监控 .................................................................................................... 249
System profile ............................................................................................... 250
控制台（Console） ...................................................................................... 252
Activity Monitor ........................................................................................... 253
Microsoft Windows监控 ....................................................................................... 257
Windows Experience .................................................................................... 257
System Health Report ................................................................................... 259
Event Viewer（事件查看器） ...................................................................... 261
Reliability Monitor ....................................................................................... 263
The Task Manager（任务管理器） .............................................................. 264
Performance Monitor .................................................................................... 266
预防性维护监控 ................................................................................................... 267
小结 ..................................................................................................................... 268
第8章 监控MySQL .....................................................269
什么是性能 .......................................................................................................... 270
MySQL服务器监控 .............................................................................................. 270
如何显示MySQL性能 .................................................................................. 271
性能监控 ...................................................................................................... 271
SQL命令 ....................................................................................................... 272
mysqladmin实用工具 ................................................................................... 278
MySQL GUI工具 .......................................................................................... 279
MySQL管理器 .............................................................................................. 280
MySQL查询浏览器 ...................................................................................... 289
服务器日志................................................................................................... 290
第三方工具................................................................................................... 293
MySQL Benchmark套件 ............................................................................... 295
数据库性能 .......................................................................................................... 296
测量数据库性能 ........................................................................................... 297
数据库优化的最佳实践 ................................................................................ 308
提高性能的最佳实践 ........................................................................................... 315
一切都慢 ...................................................................................................... 316
慢查询 .......................................................................................................... 316
慢应用 .......................................................................................................... 316
慢复制 .......................................................................................................... 317
小结 ..................................................................................................................... 317
第9章 监控存储引擎 ...................................................319
MyISAM .............................................................................................................. 320
优化磁盘存储 ............................................................................................... 320
优化数据库表 ............................................................................................... 320
使用MyISAM实用工具 ................................................................................ 321
按索引顺序存储表 ....................................................................................... 323
压缩表 .......................................................................................................... 323
对数据表进行碎片整理 ................................................................................ 323
监控Key Cache ............................................................................................. 324
预加载Key Cache ......................................................................................... 325
使用多个Key Cache ..................................................................................... 325
其他需要考虑的参数 .................................................................................... 326
InnoDB ................................................................................................................. 328
使用SHOW ENGINE命令 ............................................................................ 329
使用InnoDB监控器 ...................................................................................... 332
监控日志文件 ............................................................................................... 335
监控缓冲池................................................................................................... 336
监控表空间................................................................................................... 338
使用INFORMATION_SCHEMA表 .............................................................. 338
其他需要考虑的参数 .................................................................................... 339
小结 ..................................................................................................................... 340
第10章 复制监控 .......................................................341
开始 ..................................................................................................................... 341
安装服务器 .......................................................................................................... 342
包容性和排他性复制 ........................................................................................... 342
复制线程 .............................................................................................................. 344
监控Master ........................................................................................................... 346
Master的监控命令 ........................................................................................ 346
Master的状态变量 ........................................................................................ 349
监控Slave ............................................................................................................. 350
Slave的监控命令 .......................................................................................... 350
Slave的状态变量 .......................................................................................... 353
使用MySQL管理器监控复制 ............................................................................... 354
其他需要考虑的项 ............................................................................................... 356
网络 .............................................................................................................. 356
监控和管理Slave滞后 ................................................................................... 356
Slave延迟的原因和预防措施 ....................................................................... 357
小结 ..................................................................................................................... 358
第11章 复制的故障排除 .............................................361
什么导致错误发生 ............................................................................................... 362
Master上的问题 ............................................................................................ 362
Slave上的问题 .............................................................................................. 366
高级复制问题 ............................................................................................... 371
排除复制故障的工具 ........................................................................................... 372
最佳实践 .............................................................................................................. 374
了解你的拓扑结构 ....................................................................................... 374
查询所有服务器的状态 ................................................................................ 376
查看日志 ...................................................................................................... 376
检查配置信息 ............................................................................................... 377
有序执行系统关闭 ....................................................................................... 377
在遇到错误后按序执行重启 ........................................................................ 377
手动执行失败查询 ....................................................................................... 378
常用程序 ...................................................................................................... 378
报告复制故障....................................................................................................... 379
小结 ..................................................................................................................... 380
第12章 保护你的投资 .................................................383
什么是信息保障 ................................................................................................... 384
信息保障的三个应用 .................................................................................... 384
信息保障为什么重要 .................................................................................... 385
信息完整性、灾难恢复及备份的职责 ................................................................. 385
高可用性与灾难恢复 .................................................................................... 386
灾难恢复 ...................................................................................................... 386
数据恢复的重要性 ....................................................................................... 391
备份和恢复................................................................................................... 392
备份实用程序和操作系统级的解决方案.............................................................. 396
InnoDB Hot Backup应用 .............................................................................. 397
物理文件的复制 ........................................................................................... 400
mysqldump工具 ............................................................................................ 402
XtraBackup ................................................................................................... 404
逻辑卷管理器快照 ....................................................................................... 404
备份方法的比较 ........................................................................................... 409
备份和MySQL复制 .............................................................................................. 410
使用复制进行备份和恢复 ............................................................................ 410
PITR ............................................................................................................. 411
自动备份 .............................................................................................................. 418
小结 ..................................................................................................................... 421
第13章 MySQL企业版 ..............................................423
MySQL企业版入门 .............................................................................................. 424
订阅级别 ...................................................................................................... 425
安装概述 ...................................................................................................... 426
MySQL企业组件 .................................................................................................. 427
MySQL企业服务器 ...................................................................................... 427
MEM ............................................................................................................ 427
MySQL产品支持 .......................................................................................... 431
MySQL企业版的使用 .......................................................................................... 431
安装 .............................................................................................................. 432
修复监控代理问题 ....................................................................................... 433
监控 .............................................................................................................. 434
查询分析器................................................................................................... 440
更多信息 ...................................................................................................... 443
小结 ..................................................................................................................... 443
第三部分 高可用性环境
第14章 云计算解决方案 .............................................447
什么是云计算....................................................................................................... 448
云架构 .......................................................................................................... 450
云计算是一种经济的选择吗 ........................................................................ 453
云计算实例 .......................................................................................................... 454
云计算的好处....................................................................................................... 454
云计算供应商....................................................................................................... 455
AWS ..................................................................................................................... 456
技术简要概述 ............................................................................................... 457
它是如何工作的 ........................................................................................... 461
Amazon Cloud工具....................................................................................... 461
入门 .............................................................................................................. 465
使用磁盘 ...................................................................................................... 479
接下来怎么做 ............................................................................................... 484
云中的MySQL ..................................................................................................... 484
MySQL复制和EC2 ....................................................................................... 485
EC2中使用MySQL的最佳实践 .................................................................... 488
开源云计算 .......................................................................................................... 490
小结 ..................................................................................................................... 491
第15章 MySQL集群 ...................................................493
什么是MySQL集群 .............................................................................................. 494
术语和组件................................................................................................... 494
MySQL集群和MySQL有何不同 .................................................................. 495
典型配置 ...................................................................................................... 495
MySQL集群的特点 ...................................................................................... 496
局部和全局冗余 ........................................................................................... 497
日志处理 ...................................................................................................... 498
冗余和分布式数据 ....................................................................................... 498
MySQL集群的架构 .............................................................................................. 499
如何存储数据 ............................................................................................... 501
分区 .............................................................................................................. 504
事务管理 ...................................................................................................... 504
联机操作 ...................................................................................................... 505
配置实例 .............................................................................................................. 506
入门 .............................................................................................................. 507
启动MySQL集群 .......................................................................................... 508
集群测试 ...................................................................................................... 513
关闭集群 ...................................................................................................... 514
达到高可用性....................................................................................................... 514
系统恢复 ...................................................................................................... 517
节点恢复 ...................................................................................................... 518
复制 ............................................................................................................. 518
获得高性能 .......................................................................................................... 523
高性能的注意事项 ....................................................................................... 523
高性能的最佳实践 ....................................................................................... 524
小结 ..................................................................................................................... 527
附录A 复制建议和窍门 ...............................................529
Slave停机了，怎么办 .......................................................................................... 529
检查冗长的二进制日志 ........................................................................................ 530
利用复制在表中重建数据 .................................................................................... 530
基于语句的日志 ........................................................................................... 530
基于行的日志 ............................................................................................... 531
使用MySQL Proxy来完成多Master的复制 .......................................................... 531
使用默认的存储引擎 ........................................................................................... 532
MySQL Cluster 多源（Multisource）复制 ........................................................... 532
多路（Multichannel）复制故障转移 ................................................................... 533
使用当前数据库来过滤 ........................................................................................ 533
Slave上的列比Master上多 ................................................................................... 534
Slave上的列比Master上少 ................................................................................... 535
选择某几列复制到Slave ....................................................................................... 536
复制心跳 .............................................................................................................. 537
在环形复制中忽略服务器 .................................................................................... 538
功能预览：延时复制 ........................................................................................... 538
功能预览：脚本式复制 ........................................................................................ 539
功能预览：Oracle算法 ......................................................................................... 540
索引 ...........................................................................541