MySQL管理之道

本书首先介绍了MySQL5.7和MariaDB10.1版本的新特性，然后以构建高性能MySQL服务器为核心，从故障诊断与优化、性能调优、备份与恢复、高可用集群架构搭建与管理、读写分离和分布式DB架构搭建与管理、性能和服务监控等方面多角度深入讲解了如何去管理与维护MySQL服务器。书中内容以实战为导向，所有内容均来自于笔者多年实践经验的总结和新知识的拓展，同时也针对运维人员、DBA等相关工作者会遇到的有代表性的疑难问题给出了实用的情景模拟，并给出了解决方案。不论你目前有没有遇到过此类问题，相信对你以后处理相关问题都会有所借鉴。本书适合所有希望构建和管理高性能、高可用性的MySQL数据库系统的开发者和DBA阅读。

贺春肠，从事数据库管理工作多年，有管理和维护高并发海量数据的经验，曾经任职于中国移动飞信、聚美优品、机锋网、普惠金融等企业，致力于MySQL、Linux等开源技术的研究，主要负责MySQL性能调优和架构设计。

推荐序：飞跃的第2版
前言
第1章MariaDB架构与历史1
1.1MariaDB的介绍1
1.2MariaDB和MySQL的兼容性2
1.3MariaDB10.0新增的功能3
1.3.1更多的存储引擎4
1.3.2速度的提升5
1.3.3扩展和新功能5
1.4如何将MySQL迁移至MariaDB8
1.5使用二进制包安装MariaDB10.1企业版10
1.6总结12
第2章MySQL5.7与MariaDB10.1的新特性13
2.1性能提升14
2.2安全性的提升15
2.2.1默认开启SSL15
2.2.2不再明文显示用户密码20
2.2.3sql_mode的改变20
2.3InnoDB存储引擎的提升22
2.3.1更改索引名字时不会锁表22
2.3.2在线DDL修改varchar字段属性时不锁表23
2.3.3InnoDB/MyisAM存储引擎支持中文全文索引26
2.3.4InnoDBBufferPool预热改进29
2.3.5在线调整innodb_Buffer_Pool_Size不用重启mysql进程31
2.3.6回收（收缩）undolog回滚日志物理文件空间32
2.3.7InnoDB提供通用表空间33
2.3.8创建InnoDB独立表空间指定存放路径34
2.3.9迁移单独一张InnoDB表到远程服务器35
2.3.10修改InnoDBredolog事务日志文件大小更人性化36
2.3.11死锁可以打印到错误日志里37
2.3.12支持InnoDB只读事务37
2.3.13支持InnoDB表空间数据碎片整理38
2.4JSON格式的支持39
2.4.1支持用JSON格式存储数据39
2.4.2动态列支持用JSON格式存储数据41
2.5支持虚拟列（函数索引）44
2.5.1MySQL5.7支持函数索引44
2.5.2MariaDB10.0/10.1支持函数索引45
2.6功能提升46
2.6.1支持杀死慢的SQL语句46
2.6.2支持一张表有多个INSERT/DELETE/UPDATE触发器48
2.6.3引入线程池（ThreadPool）技术49
2.6.4提供审计日志功能52
2.6.5支持explainupdate53
2.6.6在MySQL5.7中按Ctrl+C组合键不会退出客户端54
2.6.7可将错误日志打印到系统日志文件中54
2.6.8支持创建角色55
2.6.9支持TokuDB存储引擎56
2.7优化器改进59
2.7.1针对子查询select采用半连接优化59
2.7.2优化派生子查询62
2.7.3优化排序limit65
2.7.4优化IN条件表达式66
2.7.5优化unionall69
2.7.6支持索引下推优化70
2.7.7支持MultiRangeRead索引优化72
2.7.8支持BatchedKeyAccess（BKA）索引优化75
2.7.9支持HashJoin索引优化77
2.8半同步复制改进78
2.8.1半同步复制简介78
2.8.2半同步复制的安装配置79
2.8.3参数说明79
2.8.4功能测试80
2.8.5性能测试84
2.9GTID复制改进87
2.9.1GTID复制概述87
2.9.2在MySQL5.6的GTID模式下同步复制报错不能跳过的解决方法90
2.9.3MySQL5.7中GTID复制的改进93
2.9.4GTID复制的陷阱95
2.9.5MariaDB10.1中GTID复制的改进96
2.9.6GTID的使用方式不同97
2.10MySQL5.6/5.7从库崩溃安全恢复98
2.11MariaDB10.0/10.1从库崩溃安全恢复99
2.12slave从库多线程复制99
2.13slave支持多源复制101
2.14MySQL5.7设置同步复制过滤不用重启mysql服务进程103
2.15小结104
第3章故障诊断105
3.1影响MySQL性能的因素105
3.2系统性能评估标准106
3.2.1影响Linux服务器性能的因素106
3.2.2系统性能评估指标107
3.2.3开源监控和评估工具介绍109
3.3故障与处理112
3.3.1连接数过多导致程序连接报错的原因112
3.3.2记录子查询引起的宕机117
3.3.3诊断事务量突高的原因120
3.3.4谨慎设置binlog_format=MIXED123
3.3.5未设置swap分区导致内存耗尽，主机死机127
3.3.6MySQL故障切换之事件调度器注意事项128
3.3.7人工误删除InnoDBibdata数据文件，如何恢复130
3.3.8update忘加where条件误操作恢复（模拟Oracle闪回功能）132
3.3.9delete忘加where条件误操作恢复（模拟Oracle闪回功能）141
第4章同步复制报错故障处理145
4.1最常见的3种故障145
4.1.1在master上删除一条记录时出现的故障146
4.1.2主键重复147
4.1.3在master上更新一条记录，而slave上却找不到148
4.2特殊情况：slave的中继日志relay-log损坏149
4.3人为失误151
4.4避免在master上执行大事务152
4.5slave_exec_mode参数可自动处理同步复制错误153
4.6如何验证主从数据一致154
4.7binlog_ignore_db引起的同步复制故障156
4.8MySQL5.5.19/20同步一个Bug157
4.9恢复slave从机上的某几张表的简要方法159
4.10如何干净地清除slave同步信息160
第5章性能调优162
5.1表设计162
5.2字段类型的选取166
5.2.1数值类型167
5.2.2字符类型172
5.2.3时间类型174
5.2.4小技巧：快速修改表结构181
5.2.5pt-online-schema-change在线更改表结构185
5.2.6MySQL5.6在线DDL更改表测试191
5.3采用合适的锁机制194
5.3.1表锁的演示194
5.3.2行锁的演示197
5.3.3InnoDB引擎与MyISAM引擎的性能对比199
5.4选择合适的事务隔离级别201
5.4.1事务的概念201
5.4.2事务的实现202
5.4.3事务隔离级别介绍204
5.5SQL优化与合理利用索引210
5.5.1如何定位执行很慢的SQL语句210
5.5.2SQL优化案例分析211
5.5.3合理使用索引221
5.6my.cnf配置文件调优231
5.6.1per_thread_buffers优化231
5.6.2global_buffers优化233
5.6.3QueryCache在不同环境下的使用234
5.6.4tuning-primer.sh性能调试工具的使用238
5.6.572GB内存的my.cnf配置文件241
5.6.6谨慎使用分区表功能244
5.7MySQL5.6同步复制新特性详解246
第6章备份与恢复256
6.1冷备份257
6.2逻辑备份257
6.2.1mysqldump增加了一个重要参数258
6.2.2取代mysqldump的新工具mydumper259
6.2.3逻辑备份全量、增量备份脚本262
6.3热备份与恢复263
第7章高可用MHA架构集群管理268
7.1MHA架构简介268
7.1.1master自动监控和故障转移270
7.1.2手工处理master故障转移270
7.1.3在线平滑切换270
7.2MHA配置安装270
7.3MHA故障切换演示277
7.3.1场景一：master自动监控和故障转移277
7.3.2场景二：master手工故障转移285
7.3.3场景三：在线平滑切换285
7.4MHA高可用架构总结290
第8章MySQL架构演进：“一主多从、读/写分离”293
8.1实现读/写分离的两种方式293
8.2主从同步延迟的判断标准297
8.3HAProxy感知MySQL主从同步延迟300
8.4读/写分离MariaDBMaxScale架构搭建演示307
8.4.1配置环境及安装介绍307
8.4.2基于connect方式的测试311
8.4.3基于statement方式（SQL解析）的测试312
8.4.4MaxScale延迟检测313
8.5读/写分离OneProxy介绍及架构搭建演示315
8.5.1OneProxy简介316
8.5.2OneProxy的功能及安装介绍316
8.5.3OneProxy读/写分离接入限制320
第9章CodershipGaleraCluster集群架构搭建与管理322
9.1CodershipGaleraCluster的特性和优缺点323
9.2CodershipGaleraCluster的局限性323
9.3CodershipGaleraCluster的工作原理324
9.4CodershipGaleraCluster的配置328
9.4.1CodershipGaleraCluster的配置环境及安装328
9.4.2功能测试332
9.5HAProxy结合GaleraCluster实现无单点秒级故障切换337
第10章OneProxy分库分表的搭建与管理345
10.1OneProxy分库分表的搭建346
10.1.1配置与安装346
10.1.2前端PHP/Java程序接入事项349
10.2OneProxy分库分表接入限制349
10.3OneProxy分库分表基本测试351
10.3.1分库分表的功能测试351
10.3.2分库分表的二级分区测试354
10.3.3分库分表的聚合测试358
10.3.4分库分表的插入测试359
10.3.5分库分表不支持跨库join的测试360
10.3.6分库分表不支持分布式事务的测试361
10.3.7分库分表不支持存储过程的测试361
10.4搭建OneProxy高可用故障切换HA362
10.5OneProxy黑名单SQL防火墙搭建测试363
第11章Lepus慢日志分析平台搭建与维护367
11.1Lepus基础组件的安装367
11.2安装percona-toolkit工具370

前    言?Preface为什么要写这本书首先要感谢读者对第1版的认可。随着技术的更新，第1版的内容已逐渐变老，为了与时俱进，所以准备再写一本关于MariaDB 10和MySQL 5.7的数据库图书，把自己学到的新知识做一个系统性总结来呈现给大家。目前市面上针对相关知识进行介绍的书还寥寥无几，大多数读者只能通过阅读英文手册去获取新的知识，希望本书的出版能对大家有所帮助。
    本书以构建高性能MySQL服务器为核心内容，介绍了MariaDB 10和MySQL 5.7的新特性，并从故障诊断与优化、性能调优、备份与恢复、MySQL高可用集群搭建与管理、MySQL服务器性能和服务监控等角度深入讲解了如何去管理与维护MySQL服务器。书中内容均来自于笔者多年实践经验的总结和新知识的拓展，同时也包含很多实用的情景模拟，并针对运维人员、DBA等相关工作者常遇到的有代表性的疑难问题给出了解决方案。不论你目前有没有遇到过此类问题，相信都会有借鉴意义。
    如何阅读本书本书的知识结构分四部分：
    第一部分（第1章至第2章）介绍MySQL5.7/MariaDB 10的新特性、注意事项、安装和升级方法。
    第二部分（第3章至第6章）为故障诊断与优化，涉及生产环境下MySQL故障处理，以及性能调优等内容，包括表设计阶段范式的理解、字段类型的选取、采用表锁还是行锁、MySQL默认的隔离级别与传统SQL Server，以及Oracle数据库默认的隔离级别的区别、SQL语句的优化，以及合理利用索引等。
    第三部分（第7章至第10章）为架构篇，内容包括当前互联网流行的高可用架构MHA（Master High Availability）、分库分表中间件Oneproxy和读写分离中间件MariaDB MaxScale，以及Percona/MariaDB Galera Cluster集群管理。
    第四部分（第11章）阐述慢SQL管理平台的搭建与维护，主要介绍集中收集慢日志查询。
    本书的每个部分都可以单独作为一本迷你书阅读，如果你未接触MySQL5.7/MariaDB 10，建议从第一部分开始阅读。本书提供的脚本和相关软件，请在华章网站（www.hzbook.com）的本书页面下载。
    勘误和支持由于作者的水平有限，编写的时间也很仓促，书中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。你可以将书中的错误，发送邮件至我的邮箱chunyang_he@139.com或者通过QQ联系我：3783414，我很期待能够听到你们真挚的反馈。
    致谢在这里感谢沃趣科技公司高级DBA邱文辉提供《MariaDB 10 Hash Join索引优化》一文。
    感谢机械工业出版社华章公司的编辑杨绣国老师，感谢你的魄力和远见，在这一年多的时间中始终支持我的写作，你的鼓励和帮助引导我顺利完成全部书稿。
    贺春旸2016年5月于北京