10-MySQlL DBA笔记-基础知识

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了10-MySQlL DBA笔记-基础知识，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含49677字，纯文字阅读大概需要71分钟。

内容图文

第四部分 运维篇
首先来了解一下数据库的定义，数据库是高效的、可靠的、易用的、安全的多用户存储引擎，我们可以通过它访问大量的持久化数据。
我们管理和维护数据库，本质上也是要确保如上的特性，尽可能地保证数据库的高效、可靠、易用、安全、高并发和高吞吐。
比如，对于安全，我们要尽量避免因各种软件、硬件、操作错误而导致的数据丢失或损毁。
对于高并发，也要求我们在访问控制、并发控制上做适当的设置和调优。
数据库系统也应该是易用的，应尽可能地做到对应用程序透明，研发人员不用去关心具体的物理存储对于应用程序的影响。
数据存储在磁盘上的方式和布局应与程序认为的逻辑结构无关。
数据库系统应该是高效的，比如能够处理高并发的请求，能够处理复杂的查询，或者能够计算大量的数据。
MySQL处理复杂查询的能力目前还不太好，对大数据的分析处理也不是强项，但对于互联网的OLTP应用，如果设置、调优得当，得到较高的吞吐率其实并不是一件难事。
此外，数据库也应该是可靠的、高可用的，数据库运维很重要的一个指标就是服务的可用性，如果不能提供持续稳定的服务，那么其他指标再好也没有用。
运维篇将首先介绍数据库运维的一些基础知识，接着再介绍各种维护任务所需要的知识和技能，如监控、复制、升级、迁移、备份和恢复。
然后通过一些案例给读者讲述一些维护技巧及如何处理问题。
数据库运维从来都不仅仅是一个技术问题，本篇最后将讲述规模化运维管理的一些原则、经验总结和认知。

第10章 基础知识
笔者在此假设本书的读者是熟悉Unix或Linux操作系统的，至少会进行一般的操作，这本书不会讲述操作系统的学习和脚本语言的撰写，
如果你是一个初学者，那么建议先阅读一些入门图书，比如《Unix&Linux大学教程》、《鸟哥的Linux私房菜》、《Linux命令行与Shell脚本编程大全》等。
读者还需要搭建自己的学习和测试环境，配备了基础的学习环境并懂得构建自己的测试环境后，才可以通过实践不断拓宽、深化自己的知识体系。
如果你现在仍然没有一个适宜的学习环境，那么建议你尽快搭建一套LAMP或LNMP环境。
基础环境的部署和使用将有助于你快速熟悉操作系统和数据库。
本章将主要讲述和MySQL相关的一些基础知识。包括与MySQL相关的数据库文件及参数设置，最后也会简要介绍下MySQL的灾难恢复过程。

10.1 文件和I/O管理
10.1.1 MySQL日志文件
如表10-1所示，MySQL有几个不同的日志文件，可以帮助你了解mysqld（MySQL Server的主程序）内部发生的事情。
表10-1 MySQL的日志文件及功能
默认情况下，所有日志均创建于mysqld数据目录中。
通过刷新日志，可以强制mysqld关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志中）。
当你执行一个flush logs语句或执行mysqladmin flush-logs或mysqladmin refresh时，会使得日志刷新。
下面将分别叙述各种日志文件。

1.错误日志
错误日志文件包含了mysqld启动或停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。
如果没有给定file_name值，mysqld将使用错误日志名host_name.err，并在数据目录中写入日志文件。
需要留意的是，对于MySQL 5.1，当我们使用flush logs命令刷新日志时，错误日志会被清空，并生成一个备份的错误日志，
这种情况下，往往只能看到最近的错误日志，这可能会致使我们不能及时发现问题。
我们可以使用工具实时监控错误日志，比如swatch，或者自己编写脚本检查错误日志。
也可以发送MySQL错误日志到系统日志服务Syslog，这样，我们就可以利用一些日志分析工具集中分析和处理错误信息。

2.通用日志
如果想要知道mysqld内部发生了什么，你应该用--log[=file_name]或-l[file_name]选项启动它。
如果没有给定file_name的值，那么默认名就是host_name.log。
所有连接和语句都将被记录到日志文件中。
如果怀疑在客户端发生了错误并且想要确切地知道该客户端发送给mysqld的语句，那么该日志可能会非常有用。
生产环境中，下线一个业务的时候，也可以打开这个日志，检查是否仍然有流量过来访问。
mysqld按照它接收的顺序将语句记录到查询日志。这个顺序可能与执行的顺序不同。
如下命令可查询通用日志的路径。
mysql> show variables like ‘%gene%’ ;
可以使用命令SET GLOBAL general_log ON打开通用日志记录。
由于通用日志记录了所有的查询，所以一定要记得关闭它，否则，在一个生产繁忙的系统中，通用日志在几小时之内可能就会塞满磁盘。

3.二进制日志
二进制日志包含了所有更新了数据或已经潜在更新了数据的语句。
语句以“事件”（event）的形式保存，它描述了数据的更改信息。
二进制日志还包含了每个更新数据库的语句的执行时间信息，但它不包含没有修改任何数据的语句。
如果想要记录所有的语句（例如，为了识别有问题的查询），那么我们应该使用通用日志。
二进制日志的主要目的是恢复数据，因为二进制日志包含备份后进行的所有更新。
二进制日志还用于在主复制服务器上记录所有将要发送给从服务器的语句。
如果未给出二进制日志的文件名，那么默认名为主机名-bin。
如果给出了文件名，但没有包含路径，那么文件将被写入数据目录。
建议最好指定一个文件名，语句如下。 log-bin =/path/to/logmysql-bin
mysqld将在每个二进制日志名的后面添加一个数字扩展名。每次要启动服务器或刷新日志时，该数字将会增加。
如果当前的日志大小达到了max_binlog_size参数设置的值，那么mysqld会自动创建新的二进制日志。
mysqld还将创建一个二进制日志索引文件，其中包含了所有使用二进制日志文件的文件名。
默认情况下该索引文件与二进制日志文件的文件名相同，扩展名 为“.index”。
当mysqld正在运行时，不可手动编辑该文件，这样做可能会使mysqld发生异常。
我们可以使用mysql连接数据库，运行SHOW BINARY LOGS命令查看当前有哪些二进制文件，
还可以用RESET MASTER语句删除所有的二进制日志文件，或者用PURGE BINARY LOGS命令只删除部分二进制文件。
如下的例子将删除历史二进制日志，一直到mysql-bin.000005这个文件为止。
mysql> purge binary logs to ‘ mysql-bin.000005’ ;
具有SUPER权限的客户端可以通过SET sql_log_bin=0语句禁止将自己的语句记入二进制记录中。
这在某些情况下很有用，比如进行数据库的主主切换时，再或者进行数据库的版本升级时。
我们可以用mysqlbinlog工具检查二进制日志文件。如果想要重新处理日志上的语句，那么这个工具将会很有用。
例如，可以用二进制日志更新MySQL数据库，方法如下。 shell> mysqlbinlog log-file | mysql -h host -P port
默认情况下，并不是每次写入时都会将二进制日志与硬盘同步。
因此如果操作系统或机器（不仅仅是MySQL服务器）发生崩溃，那么二进制日志中最后的语句有可能就会丢失。
要想防止这种情况的发生，可以设置sync_binlog全局变量为N（1是最安全的值，但也是最慢的），使二进制日志在每N次二进制日志写入后就与硬盘同步一次。
下面来简单介绍下二进制日志的格式。
MySQL有两种记录命令的形式，一种是语句级（binlog_format=statement），一种是行级（binlog_format=row）。
建议将记录命令的形式设置为混合模式 （binlog_format=mixed），
这在大部分情况下是适用的，它在一般情况下将使用语句记录日志，但在一些特殊情况下，就会临时更改为行级记录的形式，以便得到更健壮的复制特性。
（1）语句级（statement-based）
基于语句级的日志记录里包含了原始执行的SQL语句（这会让DBA的维护更方便），还有其他信息，如执行语句的线程ID，语句执行时的时间戳，执行所耗时长等。
（2）行级（row-based）
如果是行级格式的日志，那么它所记录的事件信息包含了行的更改信息而不是原始的SQL语句，这样可能会让DBA觉得不方便。
通过mysqlbinlog默认看到的都是一些经过base-64编码的信息，mysqlbinlog加参数-verbose（或-v），将会生成带注释的语句，
如果连续两次使用这个参数（如-v–v），则会生成字段的类型、长度、是否为NULL等属性信息。
一般而言，行级日志更健壮，而语句级的日志如果应用了MySQL的一些额外特性，比如存储过程、触发器，则可能会导致复制异常。
所以，如果使用的是语句级的复制，那么请务必保持数据库应用的简单性，只用到基本的核心特性即可。
以下将简单介绍下mysqlbinlog解析出来的二进制日志，主要有如下几项。
#at 141：事件的起始点。
#1003099:28:36 server id 123 end_log_pos 245：语句执行的时间，对于复制，这个时间会传输到从库。
server id是产生这个事件的MySQL实例的server id参数值。 end_log_pos指下一个事件的开始点，其实也就是这个事件的终点+1。
Query thread_id=3350 exec_time=11 error_code=0：thread_id指执行这个SQL的线程id。
exec_time在主从库中有不同的含义，在主库中，等于执行这个事件所花费的时间；
在从库中，等于这个事件结束执行的时间点减去在主库上开始执行的时间点，这个差异可以表征主从之间的滞后程度。
error_code为错误状态，等于0时表示状态正常。

4.慢查询日志
当参数slow_query_log=1时，mysqld将记录一个执行时间超过long_query_time秒的所有SQL语句的日志文件。
如果没有给出慢查询文件名，则默认为主机名，后缀为“-slow.log”。
如果给出了文件名，但不是绝对路径名时，文件将会写入数据目录。
执行完语句并且释放完所有锁后即可记入慢查询日志。记录顺序与执行顺序可以不相同。
慢查询日志可以用来找到执行时间很长的查询，可以用于优化。
但是，检查又长又慢的查询日志会很困难。
要想让检查变得容易些，可以使用mysqldump slow命令或pt-query-digest获得日志中显示的查询摘要来处理慢查询日志。
慢查询日志的详细介绍和相关命令的使用请参考4.3节。

5.日志文件维护
MySQL服务器可以创建各种不同的日志文件，从而可以很容易地查看所进行的操作。
但是，必须要定期清理这些文件，以确保日志文件不会占用太多的硬盘空间。
至于错误日志文件，一般情况下不会变得很大；慢查询日志在慢查询很多的情况下可能会变得很大，这时可能需要手动处理或编写脚本进行处理；
对于二进制日志文件，可以设置合适的过期策略，如expire-logs-days=10，该语句的意思是设置过期日期为10天。
expire_logs_days设置会在运行flush logs命令后触发删除过期的日志，
注意，不要用操作系统下的rm命令删除日志，这可能会导致你执行日志清理的命令失败，你可能需要手动编辑文件hostname-bin.index来反映实际的文件列表。
虽然MySQL 5.1可以设置日志过期策略，但仍然存在一个可能，对于生产繁忙的系统，二进制日志可能会塞满磁盘，
MySQL 5.6可以设置保留的二进制日志文件大小， 以免磁盘空间过满，这在一定程度上改善了日志的保留策略。

10.1.2 InnoDB数据文件和日志文件
1.概述
先来简单看下数据库数据目录下的一些文件。
假设数据目录为/usr/lib/mysql/data，此目录下可能有如下这些文件。
（1）db.opt 数据库的结构定义和设置。
（2）*.frm 数据表的结构定义。
（3）*.MYD MyISAM表数据。
（4）*.MYI MyISAM索引数据。
（5）ibdata* InnoDB表空间数据文件。
如果将innodb_file_per_table设置为1，那么InnoDB数据表可以各自存储为一个文件，称为独立表空间。
如果innodb_file_per_table等于0，那么InnoDB数据表则可以统 一存放在一个共享表空间里。
默认innodb_file_per_table等于0，即InnoDB将使用共享表空间的方式，所有的数据都会存储在类似ibdata*这样的文件内。
（6）ib_logfile* InnoDB日志数据。
（7）*.idb InnoDB数据和索引（当将innodb_file_per_table设置为1，即为独立表空间的方式）。
（8）*.trg 触发器。
以下将主要讨论InnoDB表空间数据文件和它的日志文件。
如果你指定了无InnoDB配置选项，那么MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。
对于一般的生产负荷来说，这种配置太小了，可能会导致性能问题，所以需要手动设置大小。
笔者建议日志文件应大于256MB，数据文件初始可以分配1GB到5GB，并设置为自动扩展，这样的配置在一般情况下已经够用了，相关的配置项设置如下。
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_log_file_size = 256M
innodb_data_file_path的值应该为一个或多个数据文件规格的列表。如果要命名一个以上的数据文件，请用分号“;”分隔它们。
其语法格式为： innodb_data_file_path=datafile_spec1[;datafile_spec2]...
例如： innodb_data_file_path=ibdata1:5000M;ibdata2:5000M:autoextend
其中，autoextend属性和后面跟着的属性只能被用于innodb_data_file_path行里的最后一个数据文件。
如果对最后的数据文件指定autoextend选项，那么当数据文件耗尽表空间中的自由空间时，InnoDB就会扩展这个数据文件，扩展的幅度默认是每次8MB。

2.独立表空间的原理和设置
共享表空间的使用很简单，维护方便，同时它也是MySQL默认的配置，所以在生产中得到了广泛的应用，
但它也存在一些劣势，使用共享表空间比较明显的缺点是，不能快速回收删除大表的空间，I/O操作可能会消耗更多的资源等待。
而独立表空间是很多DBA推荐使用的方式，它刚好在这两点上弥补了共享表空间的不足。
使用独立表空间，可以在它自己的文件中存储每个InnoDB表和它的索引，这种情况下，每个表都有它自己的表空间。
可以向my.cnf的[mysqld]节中添加下面的语句来允许使用独立表空间，重启MySQL实例（MySQL Server）即可生效。
[mysqld]
innodb_file_per_table
重启实例之后，InnoDB将会把每个新创建的表存储到数据库目录下的文件tbl_name.ibd中。
这类似于MyISAM存储引擎所做的，但MyISAM是把表分成数据文件tbl_name.MYD和索引文件tbl_name.MYI。
对于InnoDB，数据和索引则会被一起存放到.ibd文件中。不过tbl_name.frm文件照旧会被创建。
如果从my.cnf文件里删除了innodb_file_per_table行，并重启了实例，那么InnoDB将会在共享的表空间文件里再次创建表。
也就是说，innodb_file_per_table只会影响表的创建。
如果用这个选项启动实例，那么新表将会被.ibd文件创建，但是你仍然能够访问共享表空间中的表。
如果删掉了这个选项，那么新表将在共享表空间内被创建，但是你仍然可以访问用独立表空间创建的任何表。
即使使用了独立表空间，也仍然有一部分共享数据需要存放在共享表空间内，所以idata*文件仍然存在。
你不能像对待MyISAM一样，在数据目录之间随意地移动.ibd文件。这是因为表定义是被存放在InnoDB共享表空间内的，而且InnoDB必须保持事务ID和事务日志顺序号的一致性。
如果某个数据文件变得很大，比如上百GB，这时你可能想要另外增加一个数据文件；或者磁盘已满，这时你想要把其他数据添加到另一个硬盘上，那么这时可以手动添加一个数据文件。

3.InnoDB增加数据文件
手动增加一个数据文件时需要重启MySQL实例，我们可以计算出最后一个文件的大小（针对按MB计算的大小取整，即字节数除以1024^2，再四舍五入），
然后修改配置文件，把innodb_data_file_path参数指定的最后一个文件大小设置为该值，并在其后继续追加新的数据文件。
解决方案具体如下：
当你要添加一个新文件名到innodb_data_file_path参数指定的文件名列表时，请确信它并不存在。
当你重启实例时，InnoDB会创建并初始化这个文件。
如果最后一个数据文件是用关键字autoextend定义的，那么在编辑my.cnf文件时必须考虑最后一个数据文件已经增长到多大了。
你需要获取这个数据文件的大小， 四舍五入，使其最接近1024*1024 bytes的乘积（即1MB），
然后在innodb_data_file_path中明确指定大致的尺寸。
然后添加另一个数据文件。
记住，只有 innodb_data_file_path里的最后一个数据文件才可以被指定为自动扩展。
如下是一个修改数据文件大小的示例。
首先关闭实例，查看最后一个数据文件的大小。如下是Linux操作系统ll命令的输出。
-rw-rw--- 1 mysql mysql 10829692928 Mar 10 10:27 ibdata4
然后计算最后一个数据文件的大小。 10829692928/1024/1024=10328 MB（四舍五入）
那么对原配置文件： innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:4000M:autoextend
做如下修改，增加一个数据文件ibdata5，初始值为8000MB，可自动扩展。
innodb_data_file_path = ibdata1:4000M;ibdata2:4000M;ibdata3:4000M;ibdata4:10328M;ibdata5:8000M:autoextend
最后，重新启动实例，MySQL Server会自动创建ibdata5。

4.改变InnoDB事务日志大小
不要试图通过直接更改配置文件来设置InnoDB事务日志的大小，这会导致不能启动数据库。
如果想要改变InnoDB事务日志文件的数量和大小，那么必须要停止MySQL实例，并确定它被无错误地关闭了。
随后复制旧日志文件到一个安全的地方作为备份，万一出错还可以恢复，
然后从日志文件目录删除所有的旧日志文件，
之后编辑my.cnf改变日志文件配置，并再次启动MySQL实例。
mysqld在启动之时会发现没有日志文件，然后告诉你它正在创建一个新的日志文件。
更改InnoDB事务日志大小的具体步骤如下:
1）干净关闭MySQL。
2）使用mv命令移走旧的InnoDB事务日志。
3）修改配置文件，更改innodb_log_file_size。
4）启动MySQL。
注意，在旧版本的MySQL中，所有事务日志大小的总和不能超过4GB。MySQL 5.6将总大小的限制扩展到了512GB。

5.InnoDB的undo区域
undo区域也称为undo空间、undo表空间，是InnoDB设计的一个特殊存储区域，它保存了被活动事务更改的数据的副本（前像），
如果另一个事务需要查看原来的数据（例如，满足一致性读），那么可以从undo区域中获得未被更改的数据。
默认情况下，undo区域也是在InnoDB共享表空间内。
MySQL的更高版本（MySQL 5.6及以上）也提供了该选项，可以把undo空间放到独立的表空间里，这样就可以把undo表空间放到其他更快的磁盘设备上，进行专门的优化。
如果undo暴涨可能会把共享表空间撑大。
出现这种情况，可能是因为写负载很大，比如执行了大量的删除和修改操作，但在生产环境中，更可能出现的一种情况是存在长时间未提交的事务。
如果一个事务长时间未提交，而我们默认使用的是repeatable read事务隔离级别，那么InnoDB不会去清理旧的行版本（old rowversions），因为未提交的事务仍然需要看到它。
当这个事务一直保持打开而不提交，就可能会导致大量旧的版本数据无法删除，从而导致undo暴涨。
将事务的隔离级别更改为read committed可以解决此问题。但根本的处理措施还是检查代码，找到未提交的事务。
通过命令SHOW INNODB STATUS的输出，可以看到当前有多少没有被清理的记录。
对比下面的Purge done for trx和Trx id counter，如果差异很大，则可能是因为大量事务所导致，也可能是操作大量数据的个别事务所导致的。
------------ TRANSACTIONS ------------
Trx id counter 0 80157601
Purge done for trx’
s n:o <0 80154573 undo n:o <0 0
对于写操作很频繁的应用，InnoDB清理线程的速度可能会跟不上，从而导致undo表空间越来越大，可以通过设置innodb_max_purge_lag参数，来避免InnoDB表空间的过分增大。
InnoDB事务系统维持了一个事务列表，该列表记录被UPDATE或DELETE操作标志为删除的索引记录。
这个列表的长度为purge_lag。当purge_lag超过 innodb_max_purge_lag之时，每个INSERT、UPDATE和DELETE操作都将被延迟一定的时间，比如我们可以将其设置为100万。
即允许有100万条未清理的记录，在达到 100万的阈值后，就会触发延迟其他的查询操作。
简而言之，undo里保存了数据的前像，它可以满足一致性查询，同时，在灾难恢复过程中，它也扮演了重要的角色，它的主要功能是在灾难恢复过程中回滚那些没有提交的变更。
灾难恢复的具体过程请参考10.2节。

10.1.3 临时文件
MySQL使用环境变量TMPDIR的值作为保存临时文件的目录路径名。
如果未设置TMPDIR，那么MySQL将使用系统的默认值，通常为/tmp、/var/tmp或/usr/tmp。
如果包含临时文件目录的文件系统过小，则可以对mysqld使用“--tmpdir”选项，在具有足够空间的文件系统内指定1个目录，或者修改配置文件内的参数tmpdir。
在MySQL 5.1中，“--tmpdir”选项可被设置为多个路径的列表，以循环的方式使用。
在Unix平台上，路径可用冒号字符“:”隔开，在Windows、NetWare和OS/2平台 上，路径可用分号字符“;”隔开。
注意，为了有效地分布负载，这些路径应位于不同的物理磁盘上，而不是位于相同磁盘的不同分区中。
如果MySQL服务器正作为复制从服务器使用，那么不应将“--tmpdir”设置为指向基于内存的文件系统的目录，或者当服务器主机重启时将要清空的目录。
对于复制从服务器，需要在机器重启时仍保留一些临时文件，以便能够复制临时表或执行LOAD DATA INFILE操作，如果在服务器重启时丢失了临时文件目录下的文件， 那么复制将会失败。
MySQL会以隐含的方式创建所有的临时文件。这样，就能确保在中止mysqld时会删除所有的临时文件。
使用隐含文件的缺点在于，在临时文件目录所在的位置中，看不到占用了文件系统的大临时文件。
进行排序时（ORDER BY或GROUP BY），MySQL通常会使用1个或多个临时文件。
对于大数据量的排序，临时空间可能会超过/tmp空间，此时，执行查询将会失败，MySQL错误日志里会出现错误记录“sortabort”。
解决方案是优化查询或把临时目录设置到另一个空间足够大的分区中。
对于某些SELECT查询，MySQL还会创建临时SQL表，它们有sql_*形式的名称。
ALTER TABLE会在与原始表目录相同的目录下创建临时表。

10.1.4 MySQL套接字文件
服务器用来与本地客户端进行通信的Linux套接字文件（也称为socket文件），其默认位置是/tmp/mysql.sock。
此文件位于/tmp目录下可能会导致一些问题，原因在于，在某些版本的Linux上，任何人都能删除/tmp目录下的文件。
在Linux系统下，系统会自动删除/tmp目录下的一些文件，但并不会删除socket文件。
但某些没有经验的系统管理员可能配置了定时任务去删除/tmp目录下的文件，很可能连socket文件也会被删除，这将导致MySQL无法通过socket文件的方式进行登录。
由于现在的服务器一般都很强劲，多实例的配置也很普遍，建议不要将socket文件集中放在/tmp目录下，最好是放在单独的实例自身的目录中。
我们可以在全局配置文件中指定socket文件路径。例如，将下述行置于文件/etc/my.cnf中。
[mysqld]
socket=/path/to/socket
[client]
socket=/path/to/socket
如果你不放心socket文件，那么可以保留默认的root的其他登录方式，默认的root账号可以通过socket文件或127.0.0.1进行登录。
建议保留127.0.0.1的root登录账号，以防socket文件被异常清除。

10.2 MySQL如何进行灾难恢复
MySQL的灾难恢复类似于其他传统数据库的灾难恢复。
MySQL靠预写式日志（Write-Ahead Logging，WAL）来保证持久性，也就是说，数据文件不会马上写入脏数据，而是会先写日志。
InnoDB的脏数据是存在于innodb_buffer_pool里的，它会按一定的机制批量刷新到磁盘，这样做可以提高吞吐率。
我们把上面这种日志称为redo日志，即InnoDB的事务日志。
如果突然断电了，那么InnoDB是不能保证数据已经写入磁盘的，数据库重启后，MySQL需要知道当时执行的操作是成功了还是部分成功或失败了。
这时，只要使用了预写式日志，程序就可以检查redo日志，并将突然断电时计划执行的操作内容跟实际上执行的操作内容进行比较。
在这个比较的基础上，MySQL就可以决定是撤销已做的操作还是继续完成相应的操作，或者是保持原样。
这就是灾难恢复的过程。
由于MySQL知道宕机时有哪些日志是还没有被实际写入到数据文件的，
所以它会找到事务日志的某个点，把这个点之后的日志运行一遍，这个时候就会产生一 个新的问题，
虽然把所有日志都执行了一遍，但有一些更改并没有被提交，需要回滚。
我们配合undo日志（在undo区域内）可以确定哪些变更是需要回滚的，然后回滚那些没有提交的日志，
简单地说，灾难恢复过程可以分为redo（重做）和undo（回退）两个步骤。
由上可知，InnoDB事务日志在很大程度上决定了数据的安全性，事务日志的持久性决定了灾难恢复后最多丢失了多少记录？
事务日志都是顺序写入的，因此可以设置参数来调整commit（事务提交）时写入事务日志的频率。
MySQL的事务日志刷新可能会出现如下3种情况。
（1）innodb_flush_log_at_trx=1
每次commit时都写入磁盘。这样理论上我们只会丢失一个事务。
（2）innodb_flush_log_at_trx=2
每次commit时，写日志只缓冲（buffer）到操作系统缓存，但不刷新到磁盘，InnoDB会每秒刷新一次日志，所以宕机丢失的是最近1秒的事务。生产环境中建议使用此配置。
（3）innodb_flush_log_at_trx=0 每秒把日志缓冲区的内容写到日志文件，并且刷新到磁盘，但commit时什么也不做。
数据文件的写操作，可能会将块写坏，MySQL设计了一个数据存储区域双写缓冲（double write buffer），InnoDB使用双写缓冲来确保数据的安全，避免损坏块。
双写缓冲是InnoDB表空间的一个特殊的区域，主要用于写入页的备份，并且是顺序写入。
当InnoDB刷新数据（从InnoDB缓冲池到磁盘）时，首先写入双写缓冲，然后写入实际数据文件。
这样即可确保所有写操作的原子性和持久性。
崩溃重启后，Innodb会检查每个块（page）的校验和，判断块是否损坏，如果写入双写缓冲的是坏块，
那么显然没有写入实际数据文件，就要用实际数据文件的块来恢复双写缓冲，
如果写入了双写缓冲，但是数据文件写的是坏块，那么就用双写缓冲的块来重写数据文件。
这样的机制虽然提供了安全保障，但也增加了I/O。
对于读操作，InnoDB通过页校验码来保证数据的存取，每页在内存中都先算好一个校验值，放在文件头部，写入的时候先写校验值，读的时候也会校验一下校验值。
通过如上描述的预写式日志机制和双写缓冲区域，MySQL提供了极佳的灾难恢复性。
MySQL的稳定版本很少会因为主机断电等硬件故障而导致数据损坏。

10.3 变量设置、配置文件和主要参数
10.3.1 概述
很多人都喜欢研究各种参数配置文件，然后给自己的生产环境加上很多参数。
笔者的建议是，可以去研究它，测试它，但是在生产环境中，你应该在确定某个选项能解决特定的性能问题时，才去设置它，否则你应该尽量保持简单。
配置文件添加了过多的参数可能会导致混淆，维护性可能会变差，后来接手的DBA往往会问，为什么要这么设置。
实际的数据库产品中，很多参数只有在特定的上下文中才有意义，时过境迁，一些参数可能反而会成为性能问题的根源所在。
所以建议让生产环境的配置文件尽可能地保持简单，在确定需要时，才去设置相应的参数。
另外，数据库配置文件所起的作用有限。系统的性能更多地取决于物理部署和架构，取决于数据库设计、索引和SQL质量等。
设置好正确的基本参数之后，最好就不用再去关注它，应该花费更多的时间在库表设计、索引和查询优化上。
官方的安装包内有附带的示例配置文件，但不建议使用。
里面的一些设置不太符合生产实践，可能会有误导，而且这些配置也过时了，不适合现在的硬件和负载，也不适合互联网公司流量比较大的业务。
本章稍后会给出一份比较简单的配置文件，大家可以去对比下，然后检验下你的生产环境设置得是否合理。
注意，适合生产环境的才是最佳的，而任何建议的参考配置文件，往往是不可能覆盖到各种应用类型的，仅仅是为你的决策提供一个参照物。
所以，仍然建议以自己的生产配置为准。

10.3.2 如何设置参数、变量
配置文件内的参数需要尽量保持一样的书写风格，要么都是用下划线（如slow_query_log_file）要么都使用中线（slow-query-log-file）。
配置文件内的参数有些是影响全局的，有些是会话（session）级别的，即我们也可以在独立的连接内进行设置。
sort_buffer_size可用于设置全局和会话级，如下：
SET sort_buffer_size = <value>; #设置会话级。
SET GLOBAL sort_buffer_size = <value>; #设置全局。
set sort_buffer_size =default; #恢复默认值。
生产中尽量不要使用32位系统，32位系统的机器有内存寻址的限制，不能突破二点几GB的限制。
如果一定要使用，那么配置参数的时候，注意不要设置得过高，内存参数如果设置得太高，可能会导致32位的MySQL实例崩溃。
我们可以在SET命令中使用表达式，即，SET sort_buffer_size=10*1024*1024，但配置文件不允许使用表达式。
有时我们需要临时设置会话变量，执行操作，然后恢复原来的设置，通行的办法如下所示。
SET @saved_<unique_variable_name> := @@session.sort_buffer_size;
SET @@session.sort_buffer_size := <value>;
-- Execute the query...
SET @@session.sort_buffer_size := @saved_<unique_variable_name>;
有时我们需要临时调整一些参数或变量，来验证自己的一些想法，但在此过程中需要注意以下两点。
1）调整参数需要有一个基准，调整参数后，我们需要衡量调整的结果。最好是有一套监控系统来收集实例的运行状态，这样可以方便我们进行对比。
2）应尽量小步调整参数，一次不要调整太多参数，调整太多参数会比较危险，也会使我们无法明确到底是哪些参数调整后有效果。
随着对生产环境的日渐熟悉，我们总能找到一套适合自己生产环境的配置。

10.3.3 配置文件的读取顺序
在Unix中，MySQL程序从表10-2所示的文件中读取启动选项。
表10-2 读取启动项的文件
其中，MYSQL_HOME是一个环境变量，包含与服务器相关的my.cnf文件驻留的目录路径。
如果未设置MYSQL_HOME，并且DATADIR中有一个my.cnf文件，而BASEDIR中没有my.cnf文件，那么mysqld_safe将会把MYSQL_HOME设置为DATADIR。
如果未设置MYSQL_HOME，并且在DATADIR中没有my.cnf，则mysqld_safe将MYSQL_HOME设置为BASEDIR。
也就是说，数据目录内的配置文件和安装目录下的配置文件都可能生效。
典型情况下二进制的安装目录为/usr/local/mysql/data，源代码的安装目录为/usr/local/var。
请注意这是配置时指定的数据目录的位置，而不是mysqld启动时用--datadir 指定的。
运行时使用--datadir对寻找选项文件的服务器没有效果，因为服务器在处理命令行参量之前就寻找这些选项了。
MySQL按照上述顺序寻找选项文件，如果存在多个选项文件，那么文件中指定的后读取的选项要优先于文件中指定的先读取的选项。
所以理论上在datadir或basedir内放置一个my.cnf即可。
在Unix平台上，MySQL忽略了人人可写的配置文件。这是特意设置的，它其实是一个安全措施。
MySQL默认加载配置文件的先后顺序也可以通过应用如下命令来得知。
$ which mysqld
/usr/local/mysql/bin/mysqld
/usr/local/mysql/bin/mysqld --verbose --help | grep -A 1 ‘
Default options’
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
通过以上命令可以知道加载配置文件的顺序。
注意：不要在生产环境中运行，因为会真的启动mysqld程序。
虽然官方文档中说明了配置文件的读取顺序，可是该顺序不一定可靠。
建议读者不要依赖于官方文档所说明的顺序来部署自己的多个MySQL配置文件。
对于生产环境的部署，建议仅存在并加载一个配置文件，而不要配置多个配置文件。
有些人除了配置文件，还喜欢在命令行内也设置一些参数，这样容易导致混淆，维护性也会变差，最终将丢失你所做的变更。

10.3.4 环境变量、配置文件、命令行选项的优先级
MySQL程序首先会检查环境变量，然后检查选项文件，最后再来检查命令行以确定给出了哪些选项。
如果多次指定一个选项，那么最后出现的选项占先。
这说明环境变量具有最低的优先级，命令行选项具有最高的优先级。
可以在选项文件中指定程序选项的默认值来让MySQL程序处理各个选项。
不需要在每次运行程序时都输入选项，但可以根据需要通过命令行选项来覆盖默认值。

10.3.5 配置文件详述
配置文件分成了很多节，MySQL程序通常会读取命名和自己名字一样的节。比如如下的配置文件。
[client]
port = 3306
socket = /path/to/tmp//3306/mysql.sock
default-character-set = utf8
客户端工具，如mysql、mysqldump会读取client这一节的配置。
default-character-set指程序和MySQL服务器进行通信时所使用的字符集。
这个字符集应该和输入窗口（Windows）或控制台窗口（Unix/Linux）里默认使用的字符集 一致。
再来看一个配置文件：
[mysqld]
character-set-server = utf8
port = 3306
socket = /path/to/tmp//3306/mysql.sock
user = mysql
skip-external-locking
datadir =/path/to/data/3306
log-error =/path/to/log3306/mysqld.err
pid-file = /path/to/tmp//3306/mysql.pid
#init_connect=’
set autocommit=0’
#init_connect=’
set names utf8’
#read-only
mysqld服务会读取这一节的配置。
init_connect这个参数可以在客户端连接进来的时候执行一些初始化操作，如记录连接IP，但不会对Super用户起作用。
对于my.cnf配置文件，可以添加一些基本设置，如下是一个例子。
expire_logs_days=10;
max_connect_errors=5000;
max_connections=2048;
slow_query_log=on;
long_query_time=0.5;
skip_name_resolve
下面对其中的参数做一些简单的介绍。
（1）max_connect_errors
将此值设置得足够大会更好，推荐值是5000。
如果一台尝试连接数据库的主机失败的次数超过了此阈值，那么这个主机会被MySQL Server阻止访问，必须在 MySQL Server上运行FLUSH HOSTS才能解除此限制。
（2）skip_name_resolve
必须设置此项，因MySQL的DNS解析可能会导致严重的性能问题。
注意设置了此项之后，MySQL权限表将使用IP来统一标识主机，而不能使用主机名来标识 了。
（3）sync_binlog
默认情况下，并不是每次写入时都会将二进制日志与硬盘同步。
因此如果操作系统或机器（不仅仅是MySQL实例）发生崩溃，那么有可能二进制日志中最后的语句会丢失。
要想防止出现这种情况，可以使用sync_binlog全局变量（1是最安全的值，但也是最慢的），使二进制日志在每N次写入后与硬盘同步一次。
待sync_binlog个记录写入二进制日志后，MySQL服务器会将该二进制日志同步到硬盘上。
请注意如果是autocommit模式，那么每执行一个语句便会向二进制日志写入一次，否则每个事务执行完才写入一次。
sync_binlog的默认值是0，表示不与硬盘同步。
值为1是最安全的选择，因为崩溃时最多丢掉二进制日志中的一个语句/事 务；但是，这也是最慢的选择（除非硬盘有电池备份缓存，使同步工作较快）。
建议配置范围为8~20。

10.3.6 配置文件示例
最终的一份简单的配置文件示例如下（MySQL 5.1）。
[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid_fi = /var/lib/mysql/mysql.pid
user = mysql
port = 3306
storage_engine = InnoDB
sync_binlog = 20
# INNODB
innodb_buffer_pool_size = <value>
innodb_log_file_size = <value>
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
# MyISAM
myisam-recover=default 默认自动修复
key_buffer_size = <value>
# LOGGING
log_error = /var/lib/mysql/mysql-error.log
log_slow_queries = /var/lib/mysql/mysql-slow.log
long_query_time = <value>
# OTHER
skip_name_resolve
expire_logs_days = <value>
max_connect_errors = <value>
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = <value>
thread_cache_size = <value>
table_cache_size = <value>
open_files_limit = 65535
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

10.4 MySQL Query Cache和优化器
MySQL Query Cache内缓存了我们提交的SQL语句的结果集及相关信息，有助于加速查询响应。
一般不需要考虑Query Cache带来的额外开销，除非是写操作很频繁的应用。
工作原理：
当MySQL运行查询语句时，首先会检查是否命中缓存，如果命中那么此时会增加Qcache_hits状态变量的值，并返回结果集给客户端。
如果在缓存中找不到此语句的缓存，则进入如下步骤。
1）MySQL解析器将分解查询语句，并建立一棵“解析树”，解析器会使用MySQL的语法解析并验证查询语句的语法是否正确，是否符合规范，当然各种符号也包 含在检查范围之内。
2）预处理器检查“解析树”中的表和列是否存在，列的别名是否混淆，并进行相关权限的检查。
3）如果前面两步都通过了检验，那么再进行如下步骤。
步骤1：优化器对“解析树”进行优化，生成执行成本最低的执行计划。
步骤2：执行此计划，存储查询结果。
步骤3：返回结果集给客户端。

Query Cache默认是关闭的，临时禁用Query Cache的办法是设置query_cache_size为0，
注意FLUSH QUERY CACHE命令并不会清空缓存。清除缓存的命令是RESET QUERY CACHE。
查看相关参数的语句为 mysql>show variables like'%query_cache%';
查看相关状态变量的语句为 mysql>show global status like'%Qcache%';
至于是否可以禁用Query Cache，对此我们要谨慎些，如果命中率不高，比如才70%~80%，那么关闭Query Cache一般不会有太大的问题，
但如果Query Cache有 98%~99%，那么关闭QueryCache可能会导致比较大的冲击，要仔细评估因为缓存失效而可能对数据库造成的冲击。
任何不是从缓存块中取得数据的查询语句都称为“缓存错失（cache miss）”，造成缓存错失的原因有以下几种。
1）所发送的查询语句是不可缓存的，查询语句不可缓存的原因主要有两种：
一是语句包含了不确定的值；二是所得到的结果集太大而无法将它保存到缓存中。
这两种原因造成的结果都会增加Qcache_not_cached变量的值，可以通过查看这个变量的值来检查查询语句的缓存情况。
2）所发送的查询语句之前没有发送过，所以也不会有什么缓存存在。
3）所发送的查询语句的结果集是之前存在于缓存中的，但由于内存不足，MySQL不得不将之前的一些缓存清除掉，以腾出空间来放置其他新的缓存结果。
4）数据的变更也会引发缓存的失效。如果是数据的变更引起的缓存失效，那么可以通过查看Com_*变量的值来确认有多少查询语句更改了数据，这些变量包括 Com_update、Com_delete等。

QueryCache有如下一些要点需要注意。
SQL语句在QueryCache中是通过散列映射表来查找的，大小写、空格等差异都会导致不同的散列结果，所以开发人员应该有一致的代码规范，以保证SQL语句风格一致。
QueryCache不会缓存子查询。
如果QueryCache结果集中相关的对象发生了变化，那么这个结果集就会被失效。比如某张表修改了数据，那么Query Cache内所有涉及这张表的结果集都会失效。
需要注意的是，长时间运行的事务，会降低Query Cache的效率。
因为如果InnoDB事务内的一条语句更改了表，那么MySQL就会让Query Cache与这个表相关的Cache都失效掉。直到这个事务提交之后，才可以重新缓存这个表的结果集。
Query Cache分配内存的时候，每次至少要分配query_cache_min_res_unit大小的内存块，Query Cache并不需要等待所有的结果集在Cache内全部生成后才发送给客户端。
因为失效等原因，实际上生产环境结果集所需要的Query Cache并不是很大，一般256MB就足够了。
对于写操作很频繁的应用，可以考虑禁用QueryCache。
留意碎片（fragmentation）的原因是，如果每次都分配较大的内存（query_cache_min_res_unit较大），那么更容易导致碎片化；
如果每次分配较小的内存（query_cache_min_res_unit较小），则需要更频繁的分配，所以需要在内存的浪费和CPU的成本之间做一个取舍。
我们可以计算下平均查询大小（Query Size）。公式为：Query Size=(query_cache_size–Qcache_free_memory)/Qcache_queries_in_cache，
通过平均查询的大小来大致确定一个合适的query_cache_min_res_unit应该设置为多大。
如果Qcache_lowmem_prunes比较大，而Qcache_free_blocks也比较大，那么可能是碎片比较严重，导致了查询缓冲被大量剔除。
我们不太好衡量开启了QueryCache是否真的有帮助。
最简单的办法是衡量缓冲命中率，公式为Qcache_hits/(Qcache_hits+Com_select)，如果缓冲命中率比较高，那么它就是有效的。
但即使不高（如20%~30%），也不一定意味着低效，我们关注的是提高特定查询的访问速度而不是只关注命中率这个指标。
相对查询来说，将结果集存储到Query Cache比结果集失效的成本更低。
如果一个系统中，大部分都是复杂的查询，那么用Query Cache将是一个很好的选择。
如果Qcache_not_cached比较小，但有大量缓存未命中，那么可能会有很多失效的操作，或者MySQL没有预热数据，或者重复的查询很少。Qcache_inserts在预热数据后，应该比Com_select小得多。
可监控一下Qcache_lowmem_prunes，确定是否因为内存不够而剔除了结果集。QueryCache的效率比较高的时候，Qcache_inserts应该比Com_select小得多。
如果查询结果没有被缓存，那么，MySQL将解析查询（Parse），通过优化器（Optimizer）生成执行计划，然后运行执行计划获取数据。
MySQL优化器生成的执行计划，在很大程度上决定了其性能，随着新版本的发布，MySQL优化器越来越智能，但它仍然存在很多限制，
DBA和研发人员需要熟悉所使用的MySQL版本的优化器规则，充分利用优化器，撰写高质量的SQL。
让优化器工作得更好，本质上就是进行查询优化，具体可参考第6章“查询优化”。

10.5 SHOW ENGINE INNODB STATUS解析
SHOW ENGINE INNODBSTATUS是一种常用的工具，但运行这个命令的输出却不容易阅读。
我们可以通过创建一些InnoDB监控表（注意必须是InnoDB引擎的表），来启用性能监控输出，输出InnoDB的各种信息，默认输出至MySQL错误日志。
如下命令将创建InnoDB标准监视器，即SHOW ENGINE INNODB STATUS输出。
CREATE TABLE innodb_monitor (a INT) ENGINE=InnoDB;
如下命令将创建表空间监视器，以输出共享表空间的信息。对独立表空间来说，它不适用，如果关闭了数据文件的自动扩展，那么通过这个监控，可以监视数据文件是否需要扩展。
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE = InnoDB;
如下命令将开启表监控器，会输出系统中所有InnoDB表的一些结构和内部信息。
CREATE TABLE innodb_table_monitor (a INT) ENGINE = InnoDB;
如下命令将开启InnoDB锁监控器，它的输出结果和标准监视器基本类似，但会有更多关于锁的信息。
CREATE TABLE innodb_lock_monitor(a INT) ENGINE = InnoDB;
创建表只是发出一个命令给InnoDB引擎，同理，删除表也是发送一个停止监控的命令给InnoDB引擎，所以MySQL在重启后是不会自动启动InnoDB监控的。
以下将对InnoDB进行标准监控，也就是运行SHOW ENGINE INNODB STATUS，对其输出做一些解析，其他监控器（如对于表空间的监控）可参考官方文档。
SHOW ENGINE INNODB STATUS命令的输出信息不太方便进行脚本解析，而且输出信息里有很多平均值，不太好估算我们自己指定范围的统计结果，
SHOW GLOBAL STATUS命令也有很多InnoDB的输出信息，使用SHOW GLOBAL STATUS会更好估算一些，也会更易于监控系统性能。
创建这些表之后，MySQL就会输出各种内部结构和性能信息到MySQL错误日志，对于InnoDB标准监视器，大概是每隔15s输出一次。
笔者个人很少启用各种性能监控，一般是在做诊断的时候，直接运行命令，例如：
SHOW ENGINE INNODB STATUS \G
具体的输出解析如下。
*************************** 1. row ***************************
Status:
=====================================
100206 21:51:18 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 26 seconds
以上输出结果为最近26s的统计。如果是前1~2s的统计那么结果将不太可信。我们需要确保至少有20~30s的统计，否则结果会不太准确，还需要重新运行这个命令。
SHOW ENGINE INNODB STATUS的输出主要包含如下几个部分，这里以MySQL 5.1/5.5为例来进行讲述，其他版本与此类似。
Background Thread
Semaphores
Latest ForeignKeyError
Latest Detect Deadlock
FileI/O
Insert Bufferand Adaptive Hash Index
Log
Buffer Pool and Memory
RowOperations
Transactions
（1）信号量（Semaphores）
下面是信号量相关信息。
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 13569, signal count 11421
--Thread 1152170336 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
--Thread 1147709792 has waited at ./../include/buf0buf.ic line 630 for 0.00 seconds the semaphore:
Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0
waiters flag 0
wait is ending
Mutex spin waits 5672442, rounds 3899888, OS waits 4719
RW-shared spins 5920, OS waits 2918; RW-excl spins 3463, OS waits 3163
解析：信号量（SEMAPHORES）节包含两部分信息，
一部分信息是当前的操作系统等待（OS WAIT ARRAY INFO），
在高并发的环境下，我们可能会看到这部分信息，因为InnoDB自旋等待超过了阈值，就会触发操作系统等待，如果等待通过自旋能够解决，那么这些信息就不会显示了。
通过检查这部分信息，可以大致判断负荷的热点在哪里，由于输出行只包含了一些文件名，因此还需要有一些源码的知识，才能判断出现等待的真实原因。
另一部分信息是事件统计（event counter），reservation count和signal count的值表征了InnoDB需要OS WAIT的频率。
我们也可以使用操作系统命令，如vmstat，通过检查上下文切换（context switch）的频率来确认OS WAIT的严重程度。
我们还需要了解一些操作系统进程调度的知识，如果进程不能获取锁（mutex可以理解为一种轻量级的锁），则CPU会自旋（spin），也就是CPU空转，以等待资源，
此时并不需要进行上下文切换这种高成本的操作，也许CPU空转一些时间片，就可以获取到资源，但如果自旋超过了一定的次数，仍然无法获得资源，
那么进程就需要切换到睡眠状态进行等待（OS WAIT），大量的OS WAIT意味着资源竞争很厉害，将造成很高的上下文切换频率。
如果每秒有几万次的OS WAIT，那么很 可能系统中存在性能问题。
大量的spin waits和spin rounds，意味着CPU在空转而没有实际做事，这会消耗大量的CPU资源，所以有时我们看到系统的CPU利用率很高，
但也许并不是真正地在做事，而是CPU正在空转等待资源。
通过调整innodb_sync_spin_loops参数，可以在CPU资源消耗和上下文切换之间找到平衡点。
（2）死锁
下面是一个系统的死锁信息。
———————— LATEST DETECTED DEADLOCK————————
100206 14:46:39
*** (1) TRANSACTION:
TRANSACTION 0 353348573, ACTIVE 0 sec, process no 22381, OS thread id 823933856 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s), undo log entries 1
MySQL thread id 3176551, query id 27696260 del40 10.12.14.181 ooes_rss update
insert into ooes_fav(id,name,uid,mtime,ctime,wapflag,url,parent_id,type) values(’ 1′ ,’ QQ’ ,’ 7080277′ ,’ 1265438796′ ,’ 1265438796′ ,” ,” ,” ,’ 2′ )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1484846 n bits 144 index `uid` of table `ooes_rss`.`ooes_fav` trx id 0 353348573 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 0 353348572, ACTIVE 0 sec, process no 22381, OS thread id 894077856 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1024, 103 row lock(s), undo log entries 101 #这个事务更大
MySQL thread id 3176549, query id 27696261 del40 10.12.14.180 ooes_rss update
*** (2) HOLDS THE LOCK(S): #Note –
InnoDB only prints information about few of the locks which transaction is holding.
RECORD LOCKS space id 0 page no 1484846 n bits 72 index `uid` of table `ooes_rss`.`ooes_fav` trx id 0 353348572 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1484846 n bits 144 index `uid` of table `ooes_rss`.`ooes_fav` trx id 0 353348572 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)
解析：这段信息展示了是哪些事务导致了死锁、死锁过程中它们的状态、它们持有的锁、要等待的锁、回退到哪个事务
（据官方文档可知，MySQL会回滚成本较小的事务，比如更新更少的行）等内容。
由输出的最后一行可以得知，回退到了事务1。
需要留意的是，这里只显示了部分持有的锁，只显示了事务中最近的语句，而实际上占据资源的可能是事务中前面的语句。
在一些简单情况下，可以通过SHOW ENGINE INNOD BSTATUS的输出确认导致死锁的原因；
在复杂的情况下， 则需要打开通用日志，检查具体各个事务是如何互相等待资源从而导致死锁的。
MySQL 5.6可以通过参数innodb_print_all_deadlocks将死锁信息打印到错误日志中。
（3）外键冲突
以下为外键冲突信息，开发人员需要注意。
———————— LATEST FOREIGN KEY ERROR————————
060717 4:29:00 Transaction:
TRANSACTION 0 336342767, ACTIVE 0 sec, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 368, undo log entries 1
MySQL thread id 9697561, query id 188161264 localhost root update
insert into child values(2,2)
Foreign key constraint fails for table `test/child`: ,
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
Trying to add in child table, in index `par_ind` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000000401; asc ;;
But in parent table `test/parent`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000140c2d8f; asc – ;;
2: len 7; hex 80009c40050084; asc
（4）事务信息
------------ TRANSACTIONS ------------
Trx id counter 0 80157601
Purge done for trx’
s n:o < 0 80154573 undo n:o < 0 0
History list length 6
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3396, OS thread id 1152440672
MySQL thread id 8080, query id 728900 localhost root
show innodb status
---TRANSACTION 0 80157600, ACTIVE 4 sec, process no 3396, OS thread id 1148250464, thread declared inside InnoDB 442
mysql tables in use 1, locked 0
MySQL thread id 8079, query id 728899 localhost root Sending data
select sql_calc_found_rows * from b limit 5
Trx read view will not see trx with id >= 0 80157601, sees < 0 80157597
---TRANSACTION 0 80157599, ACTIVE 5 sec, process no 3396, OS thread id 1150142816 fetching rows, thread declared inside InnoDB 166
mysql tables in use 1, locked 0
MySQL thread id 8078, query id 728898 localhost root Sending data
select sql_calc_found_rows * from b limit 5 Trx read view will not see trx with id >= 0 80157600, sees < 0 80157596
---TRANSACTION 0 80157598, ACTIVE 7 sec, process no 3396, OS thread id 1147980128 fetching rows, thread declared inside InnoDB 114
mysql tables in use 1, locked 0
MySQL thread id 8077, query id 728897 localhost root Sending data
select sql_calc_found_rows * from b limit 5
Trx read view will not see trx with id >= 0 80157599, sees < 0 80157595
---TRANSACTION 0 80157597, ACTIVE 7 sec, process no 3396, OS thread id 1152305504 fetching rows, thread declared inside InnoDB 400
mysql tables in use 1, locked 0
MySQL thread id 8076, query id 728896 localhost root Sending data
select sql_calc_found_rows * from b limit 5
Trx read view will not see trx with id >= 0 80157598, sees < 0 80157594
解析：事务列表可能会很长，所以对于存在大量并发事务的系统，SHOW ENGINE INNODB STATUS会截去部分内容，只显示部分事务。
具体输出参数及其解析如下所示。
Trx id counter…：当前事务号，每创建一个新事务，这个值就会递增。
Purge donefor trx’s n:o…：最近一次进行线程清理的事务号，事务如果过期，则可以被清除，清除的标准是这些事务已经提交，且不会再被其他的事务所需要。
我们可以检查当前事务号和最近一次清理线程所清理的事务号的差异，例如，0（64位）80154573（32位）与0（64位）80157601（32位），
如果差异很大，则可能有大量未被清理的事务，或者少量事务更新了大量数据。
事务应该被及时提交。长时间未提交的事务可能会阻塞清理操作，耗尽资源，不过对于Web访问，一般都是很小的事务，这点不太可能会成为问题。
事务更新记录时，将在UNDO中保存记录的前像。UNDO记录保存在InnoDB的共享表空间内。
如果事务未提交，或者其他用户需要查询UNDO记录以获得一致性读，此时是不能清理这部分事务的。
大量未清理的事务，可能会导致UNDO空间暴涨，在紧急情况下，我们可以设置innodb_max_purge_lag参数来延缓新事务的更新，不过这个参数要慎用，因为它会降低性能，治标不治本。
下面来举个例子说明一下这个参数。
如果你的InnoDB表空间可以忍受100M未清理的行，也就是平均每个事务大概影响1K的行，那么你可以设置这个值为 100000（100M/1K）。
undo n:o：Purge操作正在处理的UNDO日志记录号。
History list length 6：在UNDO空间内未被清理的事务数量，在事务更新数据的时候该值会增加，在事务清理后该值会减少。
Total number of lock structs in row lock hash table 0：行锁哈希表（row lock hash table）中的锁结构（lock struct）的数量，该值不同于被锁定的行，因为通常会有多个行对应一个锁结构。
LIST OF TRANSACTIONS FOR EACH SESSION： ---TRANSACTION 00，notstarted，process no 3396，OS thread id 1152440672：
每个事务都有两个状态，即not started或active。在生产系统中，同时运行的线程一般最多只有几个，所以大部分事务都是not started。
需要留意的是，即使连接的状态是sleep，事务也可能是active的，因为事务可能是多语句的，
在生产环境中可以发现，一些长时间sleep的异常线程可能会持有着资源不释放，从而导致整个系统出现异常。
InnoDB有一个参数为innodb_thread_concurrency，用来控制并发执行的线程数。
InnoDB试着在其内部控制操作系统线程的数量，使其少于或等于这个参数给出的限制。
如果SHOW ENGINE INNODB STATUS显示有很多线程在等待（waiting in InnoDB queue或sleeping before joining InnoDB queue）进入队列，那么往往是有性能上的问题，导致系统挂死。
MySQL让等待的线程睡眠，从避免太多线程并发竞争，如果你的计算机有多个处理器和磁盘，则可以试着将这个值调整得更大以更好地利用计算机的资源。
一个推荐的值是采用系统上处理器和磁盘的个数之和。
注意：MySQL的配置里还有一个thread_concurrency参数，建议设置为CPU数的2倍大小。此变量仅仅影响Solaris系统。
在Solaris中，mysqld用该值调用 thr_setconcurrency()函数。该函数使得应用程序可以向线程系统提供需要同时运行的、期望的线程数目。
此外，其实innodb_thread_concurrency这个参数才会影响到所有的平台。
mysql tables in use 1,locked 0：访问的表数目，锁定的表数目。一般的操作是不会锁表的，InnoDB支持行级锁，所以locked一般等于0，除非是进行ALTER TABLE、LOCK TABLE之类的操作。
MySQL thread id 52111305：SHOW PROCESSLIST命令输出中的id列。
（5）I/O信息
以下是IO helper threads的状态。
-------- FILE I/O --------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
这4个线程（Unix/Linux下总是4个）的作用分别是insert buffer merges、asynchronous log flushes、read-ahead和flushing of dirty buffers。
当前看到它们的状态都是waiting for i/o request。
Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o’ s: 0, sync i/o’ s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
6845394 OS file reads, 209547550 OS file writes, 1051178 OS fsyncs 7.27 reads/s, 16384 avg bytes/read, 256.68 writes/s, 1.88 fsyncs/s
如果以上Pending为非零值，则可能存在I/O瓶颈。
对于随机I/O，因InnoDB的I/O最小单元（pagesize）=16KB。所以为16384 avg bytes/read，对于全表扫描（fulltablescan）、索引范围扫描（index scan），这个avg bytes/read会大得多。
（6）INSERT BUFFER AND ADAPTIVE HASH INDEX MySQL
并没有提供手段对以下结构进行调优。
———————————— INSERT BUFFER AND ADAPTIVE HASH INDEX————————————
Ibuf: size 1, free list len 0, seg size 2,
这里ibuf即Insert buffer，虽然英文中说的是“buffer”，但实际上这是分配在InnoDB表空间中的一块区域，
它可以和其他数据块一样，缓存在InnoDB缓冲池里，Insert buffer可以减少I/O，因为它可以合并对索引叶节点的更改操作。
（7）LOG
下面将讲述InnoDB的log子系统。
—LOG—
Log sequence number 44961757582
Log flushed up to 449 61751106
Last checkpoint at 448 4209429402
0 pending log writes, 0 pending chkp writes
201992232 log i/o’ s done, 250.14 log i/o’ s/second
其中的输出参数及其解析具体如下。
Log sequence number 44961757582：表空间创建后写入log buffer的字节数，这个值可以用来衡量日志的写入速度。
通过采样Log sequence number的输出，可以获取每秒写入的日志量，如果我们要设置InnoDB事务日志的大小，那么能保持连续写入日志30~60分钟为佳。
Log flushed up to 44961751106：最近刷新（flush）数据的位置。
由此可以计算还有多少未刷新到日志文件（logfile）的数据。如果这些数据大于innodb_log_buffer_size的30%，那么就要考虑是否应增加日志缓冲（log buffer）了。
Last checkpoint at 4484209429402：最近一次检查点的位置。
0 pending logwrites,0 pending chkp writes：pending如果大于0，则可能有I/O瓶颈。
201992232 log i/o’s done,250.14 log i/o’s/second：这些输出衡量了我们的log I/O。
（8）BUFFER POOL AND MEMORY
以下是InnoDB缓冲池的信息。
---------------------- BUFFER POOL AND MEMORY ----------------------
Total memory allocated 4648979546; in additional pool allocated 16773888
Buffer pool size 262144
Free buffers 0
Database pages 258053
Modified db pages 37491
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 57973114, created 251137, written 10761167 9.79 reads/s, 0.31 creates/s, 6.00 writes/s
Buffer pool hit rate 999 / 1000
需要说明的是“Buffer pool hit rate”的参考价值不是很大。即使有很高的命中率，也可能有大量的物理磁盘读写。
（9）ROW OPERATIONS
以下是行操作信息。
-------------- ROW OPERATIONS --------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 10099, id 88021936, state: waiting for server activity
Number of rows inserted 143, updated 3000041, deleted 0, read 24865563
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
我们可以由以上信息获知各种查询的大概频率，需要留意的是如果“0 queries in queue”不为0，则是有查询需要等待，可能意味着系统忙，你需要做进一步的诊断。

小结：
本章介绍了MySQL运维所需要了解的各种数据库文件及MySQL如何进行灾难恢复。
你必须了解各种文件的作用和机制，避免在操作系统下对数据库文件误操作。
本章还介绍了数据库的参数设置与配置文件，MySQL的配置不应该经常变动，你应该使用大多数人建议的配置，根据自己的生产环境做适当调整即可。
最后介绍了查询缓冲和MySQL优化器，我们要熟悉这些主要的组件。
此外，还讲述了如何阅读SHOW ENGINE INNODB STATUS\G命令的输出。
其他的一些基础知识已在开发篇中进行了介绍，比如索引设计、查询优化，读者也应该熟悉这些内容。

内容总结

以上是互联网集市为您收集整理的10-MySQlL DBA笔记-基础知识全部内容，希望文章能够帮你解决10-MySQlL DBA笔记-基础知识所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端