高性能MySQL笔记（下）
来自Alex's wiki
相关文章：高性能MySQL笔记（上）
目录
- 1 第十章复制
- 2 十一章可扩展的MySQL
- 3 第十二章高可用性
- 4 第十三章云端的MySQL
- 5 第十四章应用层优化
- 6 第十五章备份与恢复
- 7 附录D EXPLAIN
- 8 附录E 锁的调试
- 9 附录F 在MySQL上使用Sphinx
第十章复制
10.2配置复制
mysql主从复制-CHANGE MASTER TO 语法详解
主主及主从示例：
1.两主互为主从 （1）在主1上执行 grant replication slave on *.* to slave@'10.161.0.168' identified by 'lemon123'; （2）在主2上执行 CHANGE MASTER TO MASTER_HOST='10.161.0.145',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=107; start slave; （3）在主2上执行 grant replication slave on *.* to slave@'10.161.0.145' identified by 'lemon123'; （4）在主1上执行 CHANGE MASTER TO MASTER_HOST='10.161.0.168',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000021',MASTER_LOG_POS=107; 2.主从复制 （1）在主1上执行 grant replication slave on *.* to slave@'10.161.0.164' identified by 'lemon123'; grant replication slave on *.* to slave@'10.161.0.165' identified by 'lemon123'; grant replication slave on *.* to slave@'10.161.0.169' identified by 'lemon123'; （2）在三个从库上分别执行 CHANGE MASTER TO MASTER_HOST='10.129.150.89',MASTER_USER='slave',MASTER_PASSWORD='lemon123',MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=261;
10.2.1创建复制账号
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'YourPassword';
10.2.2配置主库和备库
在主库的my.cnf文件中增加或修改如下内容：
log_bin = mysql-bin server_id = 10
不要在my.cnf中设置master_port或master_host这些选项，它们已被废弃，只会导致问题没有好处 重启后检查主库状态：
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 98 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
在备库的my.cnf文件中增加或修改如下内容（事实上只有sever_id是必须的）：
log_bin = mysql-bin server_id = 2 relay_log = /var/lib/mysql/mysql-relay-bin log_slave_updates = 1 read_only= 1
其中
- relay_log
- 允许备库将其重放的事件也记录到自身的二进制日志中
- read_only
- 如果可能的话最好使用read_only选项，该选项会阻止任何没有特权权限的线程修改数据。但不是很实用，尤其是那些需要在备库建表的应用
10.2.3启动复制
连到主：
CHANGE MASTER TO MASTER_HOST='server1', MASTER_USER='repl', MASTER_PASSWORD='p4ssword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;
启动：
START SLAVE;
查看状态：SHOW SLAVE STATUS\G;
查看复制线程列表：SHOW PROCESSLIST\G
10.2.5推荐的复制配置
如果用的InnoDB强烈推荐如下配置（这些都是默认配置）：
| innodb_flush_logs_at_trx_commit=1 | # Flush every log write |
| innodb_support_xa=1 | # MySQL 5.0 and newer only |
| innodb_safe_binlog | # MySQL 4.1 only, roughly equivalent to innodb_support_xa |
主库(一定要指定日志文件的绝对路径)：
- log_bin=/var/lib/mysql/mysql-bin # Good; specifies a path and base name
- #log_bin # Bad; base name will be server’s hostname
备库(一定要指定中继日志的绝对路径)：
- relay_log=/path/to/logs/relay-bin
- skip_slave_start
- read_only #除了复制线程和其它超级线程之外不能修改
如果正在使用MySQL5.5并且不介意额外的fync()导致的性能开销，最好设置如下选项：
- sync_master_info = 1
- sync_relay_log = 1
- sync_relay_log_info = 1
10.4复制拓扑
记住下面的基本原则：
- 一个MySQL备库只能有一个主库
- 每个备库必须有一个唯一的服务器ID
- 一个主库可以有多个备库
- 如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库
10.4.8定制的复制方案
- 创建日志服务器
- 日志服务器不需要任何数据，它的目的仅仅是将数据提供给别的数据服务器
- 它更加容易重放且/或者过滤二进制日志事件，它对崩溃后重启恢复很有帮助。同时对基于时间点的恢复也很有帮助，在第15章我们会讨论。
- 可以从mysqlsandbox 上获得一个沙箱脚本来创建日志服务器。
- 示例，假设有 somelog-bin.000001, somelog-bin.000002 两个日志文件。把它放在MySQL日志文件夹中，如/var/log/mysql，然后编辑my.cnf：
- log_bin = /var/log/mysql/somelog-bin
- log_bin_index = /var/log/mysql/somelog-bin.index
- 我们再来创建日志索引文件（然后启动日志服务器并通过 SHOW MASTER LOGS 来确保其找到日志文件）：
- # /bin/ls −1 /var/log/mysql/somelog-bin.[0-9]* > /var/log/mysql/somelog-bin.index
10.5复制和容量规划
10.5.2备库什么时候开始延迟
- 监控方式
- 如果有复制延迟的曲线图则要观察图上的一些短暂的延迟骤升
- 测试法
- 如果将备库停止一个小时，然后开启并在1个小时内赶上，说明只消耗了一半的容量。也就是说备库在一个小时内赶上了两个小时内所有的变更
10.6复制的管理和维护
10.6.1监控复制
在主库上可以使用 SHOW MASTER LOGS 命令来查看当前主库的二进制日志位置和配置（该命令用于给PURGE MASTER LOGS命令决定使用哪些参数）
此外还可以通过SHOW BINLOG EVENTS来查看复制事件：
10.6.2测量备库延迟
- Seconds_behind_master
- SHOW SLAVE STA TUS中的Seconds_behind_master 可以显示延迟，但不准确。
- heartbeat record
- 最好的方式是使用heartbeat record
- 这是一个在主库上每秒执行一次的时间戳。延时 = 备库当前时间戳 - 心跳时间翟
- pt-heartbeat 脚本是复制心跳最流行的一种实现
10.6.3确定主备是否一致
可能使用工具：pt-table-checksum
利用pt-table-checksum做主从一致性校验
通常情况下可以在主库上运行该工具：
$ pt-table-checksum --replicate=test.checksum <master_host>
该命令会检查所有的表，并将结果插入到test.checksum 中。当查询在备库执行完就可以简单的比较主备之间的不同了。pt-table-checksum能够发现服务器所有的备库，在每台备库上运行查询，并自动输出结果
10.6.4从主库重新同步备库
最简单的办法是mysqldump转储受影响的数据并重新导入
但问题多多尤其在一个繁忙的数据库里；或从一百万行找出一千行的不同，是非常浪费资源的
- pt-table-sync
- 可以解决该问题。该工具能高效地查找及解决服务器间的不同
- 它是结合pt-table-checksum生成的checksum表来工作的，所以只能同步那些已知的不同步的表的数据块
10.6.5改变主库
整个过程最困难的是获取新主库上合适的二进制日志的位置，这样才可以从和老主库相同的逻辑位置开始复制
计划内提升
计划外提升
确定期望的日志位置
如果有备库和新主库的位置不相同，则需要找到该备库最后一条执行的事件在新主库的二进制日志中相应的位置，然后执行CHANGE MASTER TO 。可以通过mysqlbinlog工具来找到备库执行的最后一条查询，然后在主库找到同样一条查询，进行简单的计算即可得到。示例命令如下（不需要用户名密码）：
mysqlbinlog mysql-bin.000011 | tail -n 30
例子——提升server2为新主库：
假设server1是server2和server3的主库，其中server1已经崩溃。根据SHOW SLAVE STATUS获得Master_Log_File/Read_Log_Pos的值，server2已经执行完server1的所有二进制日志，但server3还不是最新的数据。如下图所示：
- 根据以上结果，server2已经跟执行完主库上所有二进制日志，但server3还不是最新数据
- 应该在server3的CHANGE MASTER TO语句赋予一个什么样的参数呢？server3在1493停止，比server2的1582要小89字节。server2正在向偏移量为8167的二进制日志写入，8167-89=8078，因此理论上我们应将server3指向server2的日志偏移量为8087的位置。最好去确认下这个位置附近的日志事件（使用mysqlbinlog）
- 假设我们观察到事件是一样的，下面这条语句可以完成从server3到server2的切换：CHANGE MASTER TO MASTER_HOST="server2", MASTER_LOG_FILE="mysql-bin.000009", MASTER_LOG_POS=8078;
10.6.6在一个主主配置中交换角色
10.7复制的问题和解决方案
mysql开启二进制日志记录文件 简介： MySQL的二进制日志
10.7.1数据损坏或丢失的错误
- 主库意外关闭
- 如果没有设置sync_binlog，当主库故障重启后可能在二进制日志里丢失一些事务。这时解决方案是从备库的下一个二进制日志的开头读日志。但一些事件会永久性的丢失。
- 建议使用pt-table-checksum工具检查主备一致性，以便于修复。可以通过开启sync_binlog来避免事件丢失，innodb_flush_log_at_trx_commit没有设置为1也可能导致数据丢失（但不会损坏）
- 备库意外关闭
- 可能会导致同步信息没来得及写入master.info，备库可能桧重新尝试一些二进制日志事件，可能导致唯一索引错误。唯一的办法就是忽略这些错误
- 可以用pt-slave-restart来帮助完成这一点
- 主库上的二进制文件损坏
- 除了忽略，别无选择。可以在主库上执行 FLUSH LOGS 命令，这样主库会开始一个新的日志文件，然后将备库指向该文件开始的地方。
- 也可以尝试去发现损坏区域的结束位置。某些情况下可以通过 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 来忽略一个损坏的事件。如果有多个损坏的事件，就需要重复该事件。但如果有太多损坏的事件，损坏的事件头会阻止服务器去找到下一个事件。这种情况下，可能不得不手动找到下一个完好的事件
- 备库上的二进制文件损坏
- 可以重新CHANGE MASTER TO
- 数据改变，但事件仍是有效SQL
- 不幸的是，MySQL甚至不能察觉这种损坏。
- 数据改变且事件是无效SQL
- 可以通过mysqlbinlog提取事件，并看到一些错乱的数据：UPDATE tbl SET col????????????????? 可以通过偏移量的方式找到下一个事件
- 数据遗漏并且/或者事件的长度是错误的
- 某些事件已经损坏或被覆盖，或者偏移量已经改变并且下一个事件的起始偏移量也是错误的
- 以上这两种情况 mysqlbinlog 已经起不了多少作用。
- 通过一些简单的分析后可用 mysqlbinlog --start-position来跨过损坏的事件。或者使用CHANGE MASTER TO 的 MASTER_LOG_POS参数。以下是找到偏移量的两个例子：
- 例1：$ mysqlbinlog mysql-bin.000113 | egrep '^# at '
- 例2：strings -n 2 -t d mysql-bin.000113
- 然后找到'G结尾的行，它们是固定长度事件头的一部分。(linux命令strings)
10.7.10丢失的临时表
尽量不用（在备库上使用的影响会小一点）
另一个办法是使用伪临时表：在一个temp库上创建，如表top_users_1234，其中1234是函数CONNECTION_ID()的返回值。可能通过清理线程将其移除——通过SHOW PROCESSLIST 来获得活跃的连接列表，并将其与表名中的连接ID比较
10.7.12InnoDB锁引起的锁争用
- 替换INSERT...SELECT
- 在主库上先执行 SELECT INTO OUTFILE 再执行 LOAD DATA INFILE。这种方法更快，而且不需要加锁
以下是加锁示意图：
10.7.13在主—主复制结构中写入两台数据库
可以使用auto_increment_increment 和 auto_increment_offset 。但自增问题只是所有问题的一小部分
10.7.14过大的复制延迟
- log_slow_slave_statements
- 在备库打开该选项，在标准的MySQL慢查询日志能够记录MySQL5.1及更新版本中执行的语句，这样就可以找到哪些语句执行的慢了。最好配合pt-query-digest使用
除了购买更快的磁盘和CPU，备库没有太多的优化空间
- 一个简单的办法是设置 innodb_flush_log_at_trx_commit = 2 来实现，这样事务会提交的快些（见8.5.1节）
- 还可以在备库上禁止二进制日志记录；
- 设置 innodb_locks_unsafe_for_binlog = 1
- 设置MyISAM的 delay_key_write = ALL
但这些都是牺牲安全为代价的，如果要把备库提升为主库要记得把这些选项设置回安全值。
不要重复写操作中代价较高的部分
- LOAD DATA INFILE
- 可能先从备库执行，再回传到主库,防止在每个备库上重放。下面例子有效避免了在主库及其它备库上执行昂贵的GROUP BY操作：
- 先在备库上执行（summary_db是备库的特别保留的数据库）：
- REPLACE INTO summary_db.summary_table (col1, col2, ...) -> SELECT col1, sum(col2, ...) FROM main_db.enormous_table GROUP BY col1;
- 现在就可以执行SELECT INTO OUTFILE，然后再执行LOAD DATA INFILE，将结果加载到主库中。
在复制之外并行写入
- 一个很好的例子：之前讨论的归档，可以禁止归档查询写到二进制日志中去
- 另一个例子，根据网上总结（非教材内容，需要在主备所有机器都执行，容易不一致）Mysql中load data infile主从复制注意点 。
- 在主库、备库上执行如下语句：
- time mysql -e "set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile 'infile.txt' into table load_test fields terminated by '\t' lines terminated by '\n' (col1, col2);"
- 注意： time命令是用于统计执行时间的。set sql_log_bin=0 等的设置仅针对本次连接有效。
- 在主库、备库上执行如下语句：
为复制线程预取缓存
10.7.15来自主库过大的包
- max_ allowed_packet
- 如果该值在主备不匹配。主库可能会记录一个备库认为过大的包。当备库获取二进制日志时可能会碰到各种各样的问题，包括无限的报错和重试、或者中继日志损坏
10.7.16受限的复制还宽
可以开启 slave_compressed _protocol 选项。可减少三分之一的带宽
10.8复制有多快
时间插件NOW_USEC P290、P493
10.9MySQL复制的高级特性
- 半同步
- 在某些场景下半同步能提高性能，在主库关闭sync_binlog的情况下保证更加安全。写入远程的内存（一台备库的反馈）比写入本地的磁盘（写入并刷新）要更快。有些性能测试表明性能提高了两倍！
- 半同步意思是：事务提交时，在客户端接收到查询结束反馈前必须保证二进制日志已经传输到至少一台备库上。
- 12.4.2 里Percona XtraDB Cluster也使用了该技术
- 多线程复制
- MySQL5.6已提供
十一章可扩展的MySQL
11.1什么是可扩展性
可扩展性表明了，当需要增加资源以执行更多工作时系统能获得划算的同等提升。以下是可扩展性定律的三种模型：
11.2扩展MySQL
11.2.4向外扩展
- 3.选择分区键
- 4.多个分区键——一个例子：
- 一个社交网站下的读书俱乐部站点，该站点可以显示任何书籍的任何评论，也能显示用户读过或评论过的所有书籍。假设用户数据和书籍数据都进行了分片设计。而评论同时拥有用户ID和评论ID，这样就跨越了两个分片的边界。实际上无须冗余存储两份评论数据，替代方案是：
- 将评论和用户数据一起存储，然后把每个评论的标题和ID与书籍数据存储在一起。这样在渲染大多数关于某本书的评论的视图时无须同时访问用户和书籍数据存储，如果需要显示完整的评论内容，可以从用户数据存储中获得。
- 5.跨分片查询
- 可以使用数据队列服务（如Gearman）来聚合查询，再用PHP等应用使用队列服务的聚合结果。
- 跨分片查询也可以借助汇总表来执行。可以遍历所有分片来生成汇总表并将结果在每个分片上冗余存储。也可以把汇总表放到另外一个数据存储中，这样就只需要存储一份了。
- 6.分配数据、分片和节点
- 应该让分片比节点节点容量小很多。可以假设100G的表，则可以1G/分片。
- 简单说，“易于管理的大小”是指保持表足够小，以便能在5或10分钟内提供日常维护操作，例如 ALTER TABLE、CHECK TABLE 或者 OPTIMIZE TABLE操作
- 7.在节点上部署分片
- 以下是一些常用的用法
- 每个分片使用单一数据库，并且数据库名要相同。典型的应用场景是需要每个分片都能镜像到原应用的结构。这在部署多个应用实例，并且每个实例对应一个分片时很有用。
- 将多个分片的表放到一个数据库中，在每个表名上包含分片号（如 bookclub.comments_23）这种配置下，单个数据库可以支持多个分片
- 为每个分片使用一个数据库，并在数据库中包含所有应用需要的表。在数据库名中包含分片号（如bookclub_23.comments, bookclub_23.users ）。
- 库和表都加分片号 如 bookclub_23.comments_23
- 每个节点上多个MySQL实例，每个实例上有一个或多个分片。可以使用以上任意组合方式分片
- 如果为已有应用分片，结果往往是一个节点对应一个分片
- 8.静态分配
- 如：SELECT CRC32(111) % 100;
- 缺点：分片很大且数量不多时，很难平衡负载；无法处理热点数据；修改分片策略困难
- 9.动态分配
- 14.分片工具
-
- JAVA
- Hibernate Shards (http://shards.hibernate.org) 、 HiveDB (http://www.hivedb.org)
- PHP
- Shard-Query (http://code.google.com/p/shard-query/)
- ScaleBase (http://www.scalebase.com)
- ScalArc (http://www.scalarc.com)
- dbShards (http://www.dbshards.com).
另外几个集群中间件 TDDL、Amoeba、Cobar、MyCAT架构比较
11.2.6通过集群扩展
- 1.MySQL Cluster
- 当使用MySQL来控制NDB时，行的主键就是键，其他的列是值。
- 需要 高速连接设备+大量内存（服务器间需要大量内存）
- 缺点：
- 复杂查询现在支持的不是很好，如那些关联和聚合的查询
- 它是一个事务型的数据库，但不支持MVCC，所以需要读锁，也不做任何死锁检测
- 2.Clustrix
- 它能直接替代MySQL。与MySQL Cluster很像；关键不同点是，Clustrix是完全的分布式执行并且缺少顶层的代理，它能很好的理解MySQL协议，无需MySQL进行协议转换。
- 实验表明，Clustrix能提供高性能和扩展性
- 3.ScaleBase
- 是一个软件代理。相似的还有 ScaleArc、dbShards
11.2.7内向扩展
- 如果应用层认为关联表具有层次关系，那么归档的顺序也应该和它一样
- 当解除归档时，可以设置一检查点。如用户的最后登录时间
保持活跃数据独立
- 如可以将user表划分为：active_users 和 inactive_users 表。
- MySQL表分区，能够帮助把最近的数据留在内存中。
- 基于时间的数据分区。一般而言新数据要比老数据活跃
- 如 可以通过分片，把老数据放到一台低配机器上，把新数据放到活跃的节点，该节点使用更大的内存和快速硬盘。
- 可以使用动态分片轻松实现这种策略。如：分片目录表可能定义如下：
11.3负载均衡
11.3.1直接连接
- 基于会话分离（推荐）
- 可以在会话层设置一个标志位，表明用户做了更新，就将该用户的查询在一段时间内总是指向主库。
- 基于版本的分离
- 这个基于会话的分离相似：你可以跟踪对象的版本号及/或时间戳，通过从备库读取对象的版本或时间以戳来判断数据是否足够新。如，在用户在发表了一篇文章后，可以更新用户的版本，这样就会从主库去读取数据了。
- 基于全局版本/会话的分离
- 当写时执行一次SHOW MASTER STATUS，把日志坐标写到REDIS，然后在备上SHOW SLAVE STATUS，把结果同之前缓存比较，如果备库更新则可以安全写入。
- pt-heartbeat可以监控延迟
11.3.2引入中间件
如HAProxy
11.3.3一主多备间的负载均衡
- 过滤和数据分区
- 可以使用复制过滤技术在相似的备库上对数据进行分区。
- 但没有内建的办法在行级别上进行复制过滤。你可以使用一些独创的技术实现，如用触发器和一些不同的表
- 保证备库跟上主库
- 可以使用 MASTER_POS_WAIT(file, pos) ，直到备库跟上主库。见 10.6.6
- 同步写操作
- 也可以用MASTER_POS_WAIT()函数来确保写操作已经被同步到一个或多个备库上。
第十二章高可用性
12.4避免单点故障
12.4.2同步复制
- 1.MySQL Cluster;它在所有的节点上进行主—主复制。这意味着在任何节点上写入；这些节点拥有等同的读写能力。每一行都是冗余存储的，这样即便丢失了一个节点，也不会丢失数据。
- 已经大规模在线产品的使用经验
- 一系列新特性：非索引数据的磁盘存储、数据节点能在线扩展、使用ndbinfo表来管理集群、配置和管理集群的脚本、多线程操作、下推、能处理BLOB列和很多列的表、集中式用户管理
- 2.Percona XtraDB Cluster
- Percona XtraDB Cluster速度很快。跨节点复制实际比没有集群还要快，因为在完全持久性模式下，写入远程RAM比写入本地磁盘要快。（对比：10.9半同步）
- 至少需要三个节点才能实现高可用的集群
Percona XtraDB Cluster的优点如下：
- 提供了基于InnoDB（ExtraDB）的透明集群，所以无须转换到别的技术。而NDB需要很多的学习成本
- 提供了真正的高可用性，所有节点等效，并在任何时候提供读写服务
- 节点失效时保证数据不丢失。因为所有节点都拥有所有全部的数据。
- 备库不会延迟，因为在事务提交前，写入集合已经在集群的所有节点上传播并被确认了。
缺点：
- 比较新
- 存储数据比较大
- 写入速度由最差的结点来决定
- 。。。。所有不建议用
12.4.3基于复制的冗余
复制管理器是使用标准MySQL复制来创建冗余的工具。它通常监控和管理三件事：
- 应用和MySQL的通信
- MySQL服务器的健康度
- MySQL服务器间的复制
两个复制管理器：
12.5故障转移和故障恢复
在UNIX领域，故障转移常常使用High Availability Linux 项目提供的工具来完成。Pacemaker是栈中一个主要的工具，它替代了老的心跳工具。
以下小节提供了一些比较普遍的故障转移技术
12.5.1提升备库或者切换角色
第十三章云端的MySQL
第十四章应用层优化
第十五章备份与恢复
15.3设计MySQL备份方案
建议：
- 在生产环境中，对于大数据库来说，物理备份是必须的：逻辑上备份太慢并受到资源限制，从逻辑备份中恢复需要很长时间。基于快照的备份，例如Percona XtraBackup 和 MySQL Enterprise Backup（mysql enterprise backup入门使用） 是最好的选择
- 保留多个备份集
- 定期从逻辑备份（或者物理备份）中抽取数据进行恢复测试
- 保存二进制日志用于基于故障点的恢复。expire_logs_days参数应该设置得足够长，至少可以从最近两次物理备份中铸基于时间点的恢复，这样就可以在保持主库运行且不应用任何二进制日志的情况下创建一个备库。备份二进制日志与过期设置无关，二进制备份需要保存足够长的时间，以便能从最近的逻辑备份进行恢复
- 完全不借助备份工具本身来监控备份和备份的过程。需要另外验证备份是否正常
- 通过演练整个恢复过程来测试备份和恢复，测算恢复所需的资源(CPU、磁盘空间、实际空间以及网络带宽等)
- 对安全性要仔细考虑。如果有人能接触生产服务器，他是否也能访问备份服务器呢？反过来呢？
15.3.1在线备份还是离线备份
避免使用 FLUSH TABLES WITH READ LOCK 的最好方法是只使用InnoDB表。
15.3.2逻辑备份还是物理备份
- 逻辑备份
- 要测试备份和恢复的时间
- PerconaServer的mysqldump会对输出格式化，能减少2/3的还原时间
- 物理备份
- 好处是备份和恢复都很简单。物理备份是跨平台、操作系统和MySQL版本的。
- 缺点：文件很大，比逻辑备份大很多。
建议混合使用物理备份和逻辑备份：先使用物复制，以此数据启动MySQL服务器实例并运行 mysqlcheck 。然后周期性的使用mysqldump 执行逻辑备份。
15.3.3备份什么
- 非显著数据
- 例如，二进制日志和InnoDB事务日志
- 复制配置
- 如果要恢复一个涉及复制关系的服务器，应该备份所有与复制相关的文件，例如二进制日志、中继日志、日志索引文件和.info文件。至少应该包含 SHOW MASTER STATUS 和/或 SHOW SLAVE STATUS 的输出。执行FLUSH LOGS 也非常有好处，可以让MySQL从一个新的二进制日志开始。从日志的开头做基于故障时间点的恢复要比从中间容易。
- 服务器配置
- 在UNIX上，这可能包括cron任务、用户和组的配置、管理脚本，以sudo规则。
增量备份和差异备份
例如在每周日做一个全备份。在周一，对自周日以来所有改变做一个差异备份。在周二就有两个选择：备份自周日以来的所有改变（差异），或只备份自从周一备份后的所有改变。
一些建议：
- 使用Percona XtraBackup和MySQL Enterprise Backup中的增量备份特性
- 备份二进制日志
- 不要备份没有改变的表。如果使用InnoDB,可以利用触发器记录修改时间的一个小的“最后修改时间”表中，帮助跟踪最新的修改操作。
- 不要备份没有改变的行。可以通过在表中加一个时间戳列来解决
- 某些数据不需要备份
- 备份所有数据，然后发送到一个有去重特性的目的地，例如ZFS文件管理程序
15.3.4存储引擎和一至性
- 数据一致性
- 也可以使用 mysqldump来获得InnoDB表的一致性逻辑备份，采用 --single-transaction 选项
- 文件一致性
- 对于 InnoDB，确保文件在磁盘上的一致性更难。即使使用 FLUSH TABLES WITH READ LOCK，InnoDB依旧在后台运行：插入缓存、日志和写线程断续将变更合并到日志和表空间文件中。这些线程设计上是异步的——在后台执行这些工作可以帮助InnoDB取得更高的并发性——正因为如此它们与LOCK TABLES无关。因此，不仅需要确保每个文件内部是一致的，还需要同时复制同一个时间点的日志和表空间文件。如果 在备份时有其它线程在修改文件，或在与表空间文件不同的时间点备份日志文件，会在恢复后再次因系统损坏而告终。可以通过以下几个方法规避这个问题：
- 等待直到InnoDB的清除线程和插入缓冲合并线程完成。可以观察 SHOW INNODB STATUS输出，当没有脏缓存或挂起的写时，就可以复制文件。不推荐这种方法。
- 在一个类似LVM的系统中获取数据和日志文件一致快照，必须让数据和日志文件在快照时相互一致；单独取它们的快照是没有意义的。
- 发送一个STOP信号给MySQL，做备份，然后再发送一个CONT信号再次唤醒MySQL。（这种方法可以避免重启服务器，从而避免预热）
- 复制
- 当从备库备份时，应该保存所有关于复制进程的信息，例如备库相对于主库的位置。这对于很多情况都非常有用：克隆新的备库，重新应用二进制日志到主库上以获得指定时间点的恢复，将备库提升为主库等。如果需要停止备库，则要确保没有打开的临时表，因为它们可能导致不能重启备库。
- 故意将一个备库延时一段时间对于某些灾难场景非常有用。例如延时复制一小时，当一个不期望的语句在主库上运行后，将有一个小时的时间观察到并从中继日志重放之前停掉复制。然后可以提升备库为主库。重放少量相关的日志事件，跳过错误的语句。这比我们后面要讨论的基于时间点的恢复技术可能要快很多。pt-slave-delay工具可以帮助实现这个方案
15.4管理和备份二进制日志
- 它们对于基于时间点的恢复是必须的
- MySQL复制也使用二进制日志，因此备份和恢复的策略经常和复制相互影响
- 可以在不同的卷上保存数据和二进制日志
- 也可以配置一个 --log_slave_update 的只读备库，这样可以获得额外的安全性
15.4.2安全的清除老的二进制日志（4.1之前）,新版本使用expire_logs_days
#新版本已经不用！ 这里只为学习一下命令 0 0 * * * /usr/bin/find /var/log/mysql -mtime +N -name "mysql-bin.[0-9]*" | xargs rm 0 0 * * * /usr/bin/mysql -e "PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N DAY"
15.5备份数据
15.5.1生成逻辑备份
- SQL导出
- 如导出test下的t1表：mysqldump test t1
- 符号分隔文件备份
- 特点是速度快，比SQL方式快一倍，以下是导出和导入的例子：
- SELECT * INTO OUTFILE '/tmp/t1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test.t1;
- LOAD DATA INFILE '/tmp/t1.txt' INTO TABLE test.t1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
15.5.2文件系统快照
本节重点讨论LVM快照
快照会在 /dev 目录下创建一个新的逻辑卷，可以像挂载其他设备一样挂载它。
先决备件和配置：
- 所有的InnoDB文件（InnoDB表空间文件和InnoDB事务日志）必须是在单个逻辑卷（分区）。你需要绝对的时间点一致性，LVM不能为多于一个卷做某个时间点的一致的快照。（这是LVM的一个限制；其他的一些文件系统没这个限制）
- 如果需要备份表定义，MySQL数据目录必须在相同的逻辑卷中。
- 必须在卷组中有足够的空闲空间来创建快照。需要多少取决于负载。当配置系统时，应该留一些未分配的空间以便后面做快照.
按如下方法查看系统中的卷组：
# vgs VG #PV #LV #SN Attr VSize VFree vg 1 4 0 wz--n- 534.18G 249.18G
按如下方法查看系统中的卷:
# lvs LV VG Attr LSize Origin Snap% Move Log Copy% home vg -wi-ao 40.00G mysql vg -wi-ao 225.00G tmp vg -wi-ao 10.00G var vg -wi-ao 10.00G
输出显示MySQLe 225G空间。设备名是/dev/vg/mysql 用ls和mount来查看：
# ls -l /dev/vg/mysql lrwxrwxrwx 1 root root 20 Sep 19 13:08 /dev/vg/mysql -> /dev/mapper/vg-mysql # mount | grep mysql /dev/mapper/vg-mysql on /var/lib/mysql
创建、挂载和删除LVM快照
创建，我们给它16G的写时复制空间，名字为 backup_mysql：
# lvcreate --size 16G --snapshot --name backup_mysql /dev/vg/mysql Logical volume "backup_mysql" created
查看：
| # lvs | ||||||||
| LV | VG | Attr | LSize | Origin | Snap% | Move | Log | Copy% |
| backup_mysql | vg | swi-a- | 16.00G | mysql | 0.01 | |||
| home | vg | -wi-ao | 40.00G | |||||
| mysql | vg | owi-ao | 225.00G | |||||
| tmp | vg | -wi-ao | 10.00G | |||||
| var | vg | -wi-ao | 10.00G |
监控：
# watch 'lvs | grep backup'
挂载：
# mkdir /tmp/backup # mount /dev/mapper/vg-backup_mysql /tmp/backup # ls -l /tmp/backup/mysql
删除：
# umount /tmp/backup # rmdir /tmp/backup # lvremove --force /dev/vg/backup_mysql Logical volume "backup_mysql" successfully removed
15.6 从备份中恢复
恢复时我们喜欢以 --skip-networking 和 --socket=/tmp/mysql_recover.sock 选项来启动MySQL
15.6.1恢复物理备份
- 对于MyISAM可能需要LOCK TABLES 和 FLUSH TABLES
- 对于InnoDB如果事务日志文件和表空间文件不匹配——例如，替换了表空间文件，但没有替换事务日志文件——InnoDB会拒绝启动
- 如果InnoDB使用 innodb_file_per_table ,InnoDB会将每个表的数据存储于一个.ibd文件中（类似于MyISAM的 .MYI + .MYD的组合）。可以在服务器运行时通过复制这些文件来复制和还原单个表。但这些文件并不完全独立于InnoDB。每个.bid文件都有一些内部信息保存着它与主表空间（共享）之间的关系。在还原这样的文件时，需要让InnoDB先‘导入’这个文件。
还原物理备份后启动MySQL
- 启动前要检查服务器配置，并确保文件有正确的归属和权限
- 建议观察MySQL启动时的错误日志： $ tail -f /var/log/mysql/mysql.err
- 注意MySQL在有错时也会启动，只是InnoDB不会工作
15.6.2恢复逻辑备份
- 禁掉二进制日志通常是个好主意，除非要将还原操作复制到备库
- 加载巨大文件对一些存储引擎也有影响。应该以可控大小的块来加载，并逐个提交事务
加载SQL文件
- 方式1
- $ mysql < sakila-backup.sql
- 方式2
- 这种方法更灵活，如可以先关闭二进制日志：
- mysql> SET SQL_LOG_BIN = 0;
- mysql> SOURCE sakila-backup.sql;
- mysql> SET SQL_LOG_BIN = 1;
- 如果压缩过，则应该在一个操作里完成
- $ gunzip -c sakila-backup.sql.gz | mysql
- 当恢复单个表时
- $ grep 'INSERT INTO `actor`' sakila-backup.sql | mysql sakila
- 如果是压缩的，则先解压：
- $ gunzip -c sakila-backup.sql.gz | grep 'INSERT INTO `actor`'| mysql sakila
加载符号分隔文件
- 如果使用的 SELECT INTO OUTFILE，则可以使用 LOAD DATA INFILE 通过相同参数来加载
- 也可以使用 mysqlimport 这是对 LOAD DATA INFILE的一个包装
可以使用FIFO“命名管道”来处理大的压缩文件：
$ mkfifo /tmp/backup/default/sakila/payment.fifo $ chmod 666 /tmp/backup/default/sakila/payment.fifo $ gunzip -c /tmp/backup/default/sakila/payment.txt.gz > /tmp/backup/default/sakila/payment.fifo
管道会等待直到其它程序打开它并从另一端打开它并读取数据：
mysql> SET SQL_LOG_BIN = 0; -- Optional
-> LOAD DATA INFILE '/tmp/backup/default/sakila/payment.fifo' INTO TABLE sakila.payment;
- pt-fifo-split
- 这个程序还可以帮助分块加载大文件，而不是在单个大事务中操作，这样效率更高
15.6.3基于时间点的恢复
参考一下10.7节案例：mysql开启二进制日志记录文件 简介： MySQL的二进制日志
以下是一个典型场景（MyISAM）：
- 正常备份
- mysql> FLUSH TABLES WITH READ LOCK;
- -> server1# cp -a /var/lib/mysql/sakila /backup/sakila;
- mysql> FLUSH LOGS;
- -> server1# mysql -e "SHOW MASTER STATUS" --vertical > /backup/master.info;
- mysql> UNLOCK TABLES;
- 然后假设有人在晚些时候执行了些灾难性语句
- mysql> USE sakila;
- mysql> DROP TABLE sakila.payment;
- 现在发现问题，首先恢复原先的备份文件
- server1# /etc/init.d/mysql stop
- server1# mv /var/lib/mysql/sakila /var/lib/mysql/sakila.tmp
- server1# cp -a /backup/sakila /var/lib/mysql
- 修改my.cnf以禁止正常的连接
- skip-networking
- socket=/tmp/mysql_recover.sock
- 启动服务器
- server1# /etc/init.d/mysql start
- 用grep来找出问题语句
- server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 | grep -B3 -i 'drop table sakila.payment'
- # at 352
- #070919 16:11:23 server id 1 end_log_pos 429
- error_code=0
- SET TIMESTAMP=1190232683/*!*/;
- DROP TABLE sakila.payment/*!*/;
- 可见问题语句在352位置，下一个语句在429，可以用如下命令重放到352，然后从429继续
- server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 --stop-position=352 | mysql -uroot -p
- server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 --start-position=429 | mysql -uroot -p
接下来是检查数据以确保没有问题，然后关闭服务器并撤销对my.cnf的改变，最后重启服务器
15.6.4更高级的恢复技术
- 用于快速恢复的延时复制
- 当延时复制时，在备库执行问题语句前发现了问题，则恢复就更容易了：
- 先停止备库，然后 START SLAVE UNTIL master_log_file='binlog.000002',master_log_pos=829090187
- 接着执行：SET GLOBAL SQL _SLAVE_SKIP_COUNTER=1 来跳过问题语句（或简单的使用 CHANGE MASTER TO来前移备库在日志中的位置）
- 然后 START SLAVE 让备库执行完所有的中继日志
- 使用日志服务器进行恢复
- 这种方式更加安全和灵活
- 假设还是15.6.3中的场景。下面是步骤：
- 将需要恢复的服务器叫作server1
- 在另一台server2的机器上恢复昨晚的备份。在这台机器上运行恢复进程，以免在恢复时犯错而导致事情更糟
- 按照第10章的做法设置server2来接收server1的二进制日志（复制日志到另外一个服务器并设置日志服务器通常是个好主意，但要格外注意）
- 改变server2的配置文件，增加如下内容：replicate-do-table=sakila.payment
- 重启server2，然后用CHANGE MASTER TO来让它成为日志服务器的备库。配置它从昨晚昨晚备份的二进制日志坐标读取。这时候不要运行 START SLAVE
- 检查server2上的SHOW SLAVE STATUS的输出，验证一切正常。要三思而行！
- 找到二进制日志中问题语句的位置，在server2上执行START SLAVE UNTIL来重放事件直到该位置。
- 在server2上用STOP SLAVE停掉复制进程
- 将所需表从server2复制到server1
15.6.5InnoDB崩溃恢复
- 二级索引损坏
- 一般可用 OPTIMIZE TABLE 来修复
- innodb_force_recovery及聚簇索引损坏
- 聚簇索引损坏时也许只能使用 innodb_force_recovery 选项来导出表。
- 这个值通常情况下是0，最大可以设置成6。特别的：在有点危险的前提下，可以把这个数值提高到4. 使用这个值时，若有数据页损坏，将会丢失一些数据；如果将数值设置的更高，可能从坏掉的表里面提取数据，或者可能增加SELECT INTO OUTFILES的风险。换句话说，这个值直到4都对数据没有损害，但可能丧失修复问题的机会；而到5和6会主动修复问题，但损害数据的风险也会很大
- InnoDB Recovery Toolkit
- 如果InnoDB数据损坏到了根本不能启动MySQL的程度，还可以使用Percona出品的InnoDB Recovery Toolkit从表空间数据文件里抽取数据。可以从 http://www.percona.com/software 免费获取
15.7备份和恢复工具
我们喜欢对LVM使用 mylvmbackup 做快照备份，使用 Percona Extrabackup （开源）或MySQL Enterprise Backup（收费）做InnoDB热备份。不建议对大数据量使用mysqldump
- MySQL Enterprise Backup
- 是MySQL Enterprise 中的一部分，收费项目
- Persona ExtraBackup
- 与MySQL Enterprise Backup很相似。但免费。它支持类似流、增量、压缩和多线程（并行）备份操作。也有许多另的功能，用以降低在高负载的系统上备份的影响
- MySQL 5.6 XtraBackup 全量和增量的备份和恢复
- mylvmbackup
- 它通过LVM快照帮助MySQL自动备份
- Zmanda Recovery Manager
- 有免费和商业两种版本。它是一个备份和恢复的管理器，并非单一工具。
附录D EXPLAIN
一个复杂的例子
EXPLAIN
SELECT actor_id,
(SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id =
der_1.actor_id LIMIT 1)
FROM (
SELECT actor_id
FROM sakila.actor LIMIT 5
) AS der_1
UNION ALL
SELECT film_id,
(SELECT @var1 FROM sakila.rental LIMIT 1)
FROM (
SELECT film_id,
(SELECT 1 FROM sakila.store LIMIT 1)
FROM sakila.film LIMIT 5
) AS der_2;
附录E 锁的调试
找出谁持有锁：
$ mysqladmin debug
输出如下：
| Thread | database.table_name | Locked/Waiting | Lock_type |
| 7 | sakila.film | Locked - read | Read lock without concurrent inserts |
| 8 | sakila.film | Waiting - write | Highest priority write lock |
以下代码用于显示谁在等待谁：
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) AS wait_time,
r.trx_query AS waiting_query,
l.lock_table AS waiting_table_lock,
b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread,
SUBSTRING(p.host, 1, INSTR(p.host, ':') - 1) AS blocking_host,
SUBSTRING(p.host, INSTR(p.host, ':') +1) AS blocking_port,
IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,
b.trx_query AS blocking_query
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id
LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC\G;
以下输出显示：线程3已经等待store表中的锁达6S。它在线程2上被阻塞，而该线程已经空闲了8S
以下代码显示有多少查询被哪些线程阻塞，而没有多余信息：
SELECT CONCAT('thread ', b.trx_mysql_thread_id, ' from ', p.host) AS who_blocks,
IF(p.command = "Sleep", p.time, 0) AS idle_in_trx,
MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP)) AS max_wait_time,
COUNT(*) AS num_waiters
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX AS r ON r.trx_id = w.requesting_trx_id
LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
GROUP BY who_blocks ORDER BY num_waiters DESC\G;
结果显示：线程2已经空闲了更长一段时间，并且至少有一个线程已经等待它释放它的锁长达37S。有8个线程在等待线程2完成它的工作并提交
附录F 在MySQL上使用Sphinx
属性是存在索引中的，它不进行全文索引，但是可以用于过滤和排序。