MySQL学习笔记之日志管理

日志是mysql数据库中一个很重要的东西，日志类型有：错误日志，日志文件，二进制日志，慢日志，事务日志等等，下面我们一起来了解一下mysql日志.

一、日志类型:

MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：

日志文件 记入文件中的信息类型

错误日志 记录启动、运行或停止时出现的问题。

查询日志 记录建立的客户端连接和执行的语句。

二进制日志 记录所有更改数据的语句。主要用于复制和即时点恢复。

慢日志 记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

事务日志 记录InnoDB等支持事务的存储引擎执行事务时产生的日志。

默认情况下，所有日志创建于mysqld数据目录中。通过刷新日志，你可以强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，出现日志刷新。如果你正使用MySQL复制功能，从复制服务器将维护更多日志文件，被称为接替日志。

二、错误日志:

错误日志主要记录如下几种日志：

服务器启动和关闭过程中的信息,服务器运行过程中的错误信息,事件调度器运行一个时间是产生的信息,在从服务器上启动从服务器进程是产生的信息

错误日志定义:

可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件。如果你执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建一个新的空日志文件。(如果未给出--log-error选项，则不会重新命名）。

查看当前错误日志配置,代码如下:

1. mysql>SHOWGLOBALVARIABLESLIKE'%log_error%';
2. +---------------+---------------------------------------+
3. |Variable_name|Value|
4. +---------------+---------------------------------------+
5. |log_error|/data/mysql/localhost.localdomain.err|
6. +---------------+---------------------------------------+
7. 1rowinset(0.45sec)

是否记录警告日志,代码如下:

1. mysql>SHOWGLOBALVARIABLESLIKE'%log_warnings%';
2. +---------------+-------+
3. |Variable_name|Value|
4. +---------------+-------+
5. |log_warnings|1|
6. +---------------+-------+
7. 1rowinset(0.00sec)

三、通用查询日志

启动开关：general_log={ON|OFF}

日志文件变量：general_log_file[=/PATH/TO/file]

全局日志开关：log={ON|OFF} 该开关打开后，所有日志都会被启用

记录类型：log_output={TABLE|FILE|NONE}

因此,要启用通用查询日志,需要至少配置general_log=ON,log_output={TABLE|FILE},而general_log_file如果没有指定，默认名是host_name.log.

看看上述几个值的默认配置,代码如下:

1. mysql>SHOWGLOBALVARIABLESLIKE'%general_log%';
2. +------------------+---------------------------+
3. |Variable_name|Value|
4. +------------------+---------------------------+
5. |general_log|OFF|
6. |general_log_file|/data/mysql/localhost.log|
7. +------------------+---------------------------+
9. mysql>SHOWGLOBALVARIABLESLIKE'%log_output%';
10. +---------------+-------+
11. |Variable_name|Value|
12. +---------------+-------+
13. |log_output|FILE|
14. +---------------+-------+

四、慢查询日志:

MySQL如果启用了slow_query_log=ON选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）。日志记录文件为slow_query_log_file[=file_name]，如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

默认与慢查询相关变量,代码如下:

1. mysql>SHOWGLOBALVARIABLESLIKE'%slow_query_log%';
2. +----------------------------+--------------------------------+
3. |Variable_name|Value|
4. +----------------------------+--------------------------------+
5. |slow_query_log|OFF|
6. |slow_query_log_file|/data/mysql/localhost-slow.log|
7. +----------------------------+--------------------------------+//phpfensi.com

服务器参数设定方法与通用查询日志相同,不做解释,默认没有启用慢查询,为了服务器调优,建议开启,代码如下:

mysql> SET GLOBAL slow_query_log=ON;

Query OK, 0 rows affected (1.45 sec)

##如果要长久生效,则需要在配置文件中定义,那么多久算是慢呢？如果查询时长超过long_query_time的定义值（默认10秒）,即为慢查询,代码如下:

1. mysql>SHOWGLOBALVARIABLESLIKE'long_query_time';
2. +-----------------+-----------+
3. |Variable_name|Value|
4. +-----------------+-----------+
5. |long_query_time|10.000000|
6. +-----------------+-----------+

五、二进制日志:

二进制日志启动开关：log-bin [= file_name],在5.6及以上版本一定要手动指定,5.6以下版本默认file_name为$datadir/mysqld-binlog.

二进制日志用于记录所有更改数据的语句,主要用于复制和即时点恢复.

查看二进制日志的工具为:mysqlbinlog

二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。二进制日志还包含关于每个更新数据库的语句的执行时间信息。它不包含没有修改任何数据的语句。

二进制日志的主要目的是在数据库存在故障时，恢复时能够最大可能地更新数据库（即时点恢复），因为二进制日志包含备份后进行的所有更新。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。

那么二进制日志是记录执行的语句还是执行后的结果数据呢？

第一种情况:加入一个表有10万行数据,而现在要执行一个如下语句将amount字段的值全部在原来的基础上增加1000,代码如下:

UPDATE sales.january SET amount=amount+1000；

此时如果要记录执行后的结果数据的话,日志会非常大,因此在这种情况下应记录执行语句,这种方式就是基于语句的二进制日志.

第二种情况:如果向某个字段插入的是当前的时间呢？代码如下:

INSERT INTO tb SET Birthdate=CURRENT_TIME();

此时就不能记录语句了，因为不同时间执行的结果是不一样的,这是应该记录这一行的值,这种就是基于行（row）的二进制日志.

在有些情况,可能会结合两种方式来记录,这种叫做混合方式的二进制日志.

二进制日志记录时间:

默认情况下，并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能二进制日志中最后的语句丢失了。要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使二进制日志在每N次二进制日志写入后与硬盘同步。

对非事务表的更新执行完毕后立即保存到二进制日志中。对于事务表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT) 被缓存起来，直到服务器接收到COMMIT语句。在该点，执行完COMMIT之前，mysqld将整个事务写入二进制日志。当处理事务的线程启动时，它为缓冲查询分配binlog_cache_size大小的内存。如果语句大于该值，线程则打开临时文件来保存事务。线程结束后临时文件被删除。

二进制日志的管理:

日志的滚动:在my.cnf中设定max_binlog_size = 200M，表示限制二进制日志最大尺寸为200M，超过200M后进行滚动。MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。

每次重启MySQL服务,日志都会自动滚动一次,另外如果需要手动滚动,则使用命令,代码如下:

mysql> FLUSH LOGS;

日志的查看,查看有哪些二进制日志文件,代码如下:

1. mysql>SHOWBINARYLOGS;
3. +----------------------+-----------+
4. |Log_name|File_size|
5. +----------------------+-----------+
6. |mysqld-binlog.000001|143|
7. |mysqld-binlog.000002|120|
8. +----------------------+-----------+

查看当前正在使用的是哪一个二进制日志文件,代码如下:

1. mysql>SHOWMASTERSTATUS;
2. +----------------------+----------+--------------+------------------+-------------------+
3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
4. +----------------------+----------+--------------+------------------+-------------------+
5. |mysqld-binlog.000002|120||||
6. +----------------------+----------+--------------+------------------+-------------------+
8. ##做个操作后再次查看：
10. mysql>usejiaowu;
12. Databasechanged
13. mysql>INSERTINTOstudents(Name,Age)VALUES('stu1',24);
14. QueryOK,1rowaffected(0.07sec)
16. mysql>SHOWMASTERSTATUS;
17. +----------------------+----------+--------------+------------------+-------------------+
18. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
19. +----------------------+----------+--------------+------------------+-------------------+
20. |mysqld-binlog.000002|394||||
21. +----------------------+----------+--------------+------------------+-------------------+
22. 1rowinset(0.00sec)
24. ##可以看到Position（位置）已经改变。

查看二进制日志内容,代码如下:

1. mysql>SHOWBINLOGEVENTSIN'mysqld-binlog.000002';
2. +----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
3. |Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
4. +----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
5. |mysqld-binlog.000002|4|Format_desc|1|120|Serverver:5.6.13-log,Binlogver:4|
6. |mysqld-binlog.000002|120|Query|1|203|BEGIN|
7. |mysqld-binlog.000002|203|Intvar|1|235|INSERT_ID=11|
8. |mysqld-binlog.000002|235|Query|1|363|use`jiaowu`;INSERTINTOstudents(Name,Age)VALUES('stu1',24)|
9. |mysqld-binlog.000002|363|Xid|1|394|COMMIT/*xid=13*/|
10. +----------------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+
11. 5rowsinset(0.01sec)
13. ##该语句还可以加上Position（位置），指定显示从哪个Position（位置）开始：
15. mysql>SHOWBINLOGEVENTSIN'mysqld-binlog.000002'FROM203;
16. +----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
17. |Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
18. +----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
19. |mysqld-binlog.000002|203|Intvar|1|235|INSERT_ID=11|
20. |mysqld-binlog.000002|235|Query|1|363|use`jiaowu`;INSERTINTOstudents(Name,Age)VALUES('stu1',24)|
21. |mysqld-binlog.000002|363|Xid|1|394|COMMIT/*xid=13*/|
22. +----------------------+-----+------------+-----------+-------------+------------------------------------------------------------------+
23. 3rowsinset(0.00sec)