了解和正确维护MySQL日志_MySQL
- WBOYOriginal
- 2016-05-31 08:48:381237browse
前段时间自己给自己挖了坑，为了优化MySQL数据库配置在运行有InnoDB的MySQL数据库中修改了my.cnf文件中关于InnoDB的部分配置，尽管是在关机的情况下操作的，但依旧忽略了InnoDB对日志文件（如ibdata1、ib_logfile0、ib_logfile1、ib_logfile2）强大的依赖性，删掉这些特殊的日志文件意味着对InnoDB的破坏，可能导致数据库无法启动和恢复。从MySQL中文手册上摘录了部分有用的知识点整理如下。
MySQL日志简介
MySQL共有5种不同的日志类型，分别为错误日志、通用查询日志、中继日志、二进制日志和慢日志。其中错误日志、
| 日志文件 | 记入文件中的信息类型 |
| 错误日志 | 记录启动、运行或停止mysqld时出现的问题。 |
| 查询日志 | 记录建立的客户端连接和执行的语句。 |
| 中继日志 | 从主复制服务器上接受到的数据变化。 |
| 更新日志 | 记录更改数据的语句。不赞成使用该日志。（新版本已经废弃） |
| 二进制日志 | 记录所有更改数据的语句。还用于复制。 |
| 慢日志 | 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。 |
错误日志文件包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
查询日志包含了所有连接和语句，能确切地知道某一个客户端发送给mysqld哪些语句。
查询日志还包含所有语句，而二进制日志不包含只查询数据的语句，如果想要记录所有语句（例如，为了识别有问题的查询），你应使用查询日志。
二进制日志以一种更有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息，还包含关于每个更新数据库的语句的执行时间信息。
二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。
二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。
如果当前的二进制日志大小达到max_binlog_size，还会自动创建新的二进制日志，但一个事务产生的二进制日志有可能会超过max_binlog_size。
可以用RESET MASTER语句删除所有二进制日志文件，或用PURGE MASTER LOGS只删除部分二进制文件。例如先查看现有的二进制文件，SHOW BINARY LOGS，再删除某些二进制文件，PURGE BINARY LOGS TO 'mysql-bin.000009'。
慢查询日志可以用来找到执行时间长的查询，可以用于优化。但是，检查又长又慢的查询日志会很困难。要想容易些，你可以使用 mysqldumpslow 命令获得日志中显示的查询摘要来处理慢查询日志。
日志维护
MySQL服务器可以创建各种不同的日志文件，从而可以很容易地看见所进行的操作。但是，你必须定期清理这些文件，确保日志不会占用太多的硬盘空间，否则很短的时间内就会把数据库磁盘吃满。
数据库备份经验
内容较多，占用空间较大的数据库在导出的过程中可能仅需要比较短的时间，通常一小时内就能备份完成，但恢复时会花掉很多时间，往往超过数小时。因此有必要及时删减数据库中的无用的信息，甚至设计数据库时考虑和标注清楚，那些数据是相对不重要的。如果备份时能将日志文件切断就更好了。
可以使用mysql-log-rotate脚本将日志文件进行切割。你可以通过mysqladmin flush-logs或SQL语句FLUSH LOGS来强制MySQL开始使用新的日志文件(不适用于二进制日志，要清除二进制日志参考上面的RESET MASTER或PURGE BINARY LOGS语法)。
当CPU为单核或性能不足时，iowaite的值会很大，表明CPU在等待磁盘写入的过程中花费了太多的时间，这时就有必要增加CPU的配置。如下图所示，%wa数值是%us的近10倍，说明磁盘IO开销比较严重。
附：关于InnoDB存储引擎
存储引擎比较：
不一定要使用相同的存储引擎，每个表可以使用不同的存储引擎。
InnoDB的中文参考资料可以参见 http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb
InnoDB是为处理巨大数据量时的最大性能设计，被用在众多需要高性能的大型数据库站点上。
被InnoDB存储引擎管理的两个重要的基于磁盘的资源是InnoDB表空间数据文件和它的日志文件。
如果你指定无InnoDB配置选项，MySQL将在数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。
要为一个主服务器建立一个新服务器，你不得不复制InnoDB表空间和日志文件，以及InnoDB表的.frm文件，并且移动复件到从服务器。
如果你可以关闭主服务器或者一个存在的从服务器。你可以采取InnoDB表空间和日志文件的冷备份，并用它来建立一个从服务器。要不关闭任何服务器来建立一个新的从服务器，你也可以使用非免费（商业的） InnoDB热备份工具 。
关于移动、备份和恢复InnoDB，可以参考InnoDB的中文参考资料： http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#backing-up