MySQL服务器日志（1）

  参考MySQL5.7官方文档
  MySQL服务器有以下几种类型的日志，通过这些日志，可以知道服务器的活动；

• 错误日志：记录了mysqld启动、运行、停止时，所遇到的问题；
• 普通查询日志：日志记录了服务器建立的客户端连接，以及来自客户端的查询语句；
• 二进制日志：记录了更新数据的语句，也包括复制数据的语句；
• 中继日志：只有主从复制的slave服务器才会有，记录的是来自主服务器的数据变化；
• 慢查询日志：记录的是查询时间超过变量long_query_time秒的查询；
• DDL日志：也叫元数据日志，记录的是通过DDL进行的元数据操作；

  关于中继日志，可以先参考文章高性能MySQL主从架构的复制原理及配置详解

1 设置普通查询日志和慢查询日志的输出

  mysql服务器可以让用户灵活地普通查询日志和通用查询日志的输出地方，可以分为两种，在服务器启动时设置和在服务器运行时设置；
  参考：http://dev.mysql.com/doc/refman/5.7/en/log-destinations.html

1.1 在服务器启动时

  如果要指定日志的输出形式，需要使用log-out-put选项，但是不能启用日志记录功能；

    --log-out-put=TABLE,FILE

  log-out-put选项有3种取值：TABLE,FILE,NONE;默认值为FILE。
  TABLE：将日志分别输出到数据库mysql的general_log和 slow_log表中；
  FILE：将日志输出到文件中；
  NONE：禁止日志输出；
  可以同时取值多个，中间用逗号隔开；当取值为多个时，NONE的优先级最高；

  如果要启用日志输出功能，需要使用选项general-log和选项slow-query-log，默认状态都是OFF禁用；

    --general-log=1    --slow-query-log=1

  general-log指定普通查询日志的状态，取值为0或1，默认为0；1代表启用，0或不指定代表禁用；
  slow-query-log指定慢查询日志的状态，取值为0或1，默认为0；1代表启用，0或不指定代表禁用；

  如果指定日志输出到文件，还要指定日志文件的位置，需要使用选项general-log-file和选项slow-query-log-file；

    --general-log-file=file_name    --slow-query-log-file=file_name

  general-log-file选项指定普通查询日志的文件名称，默认是hostname.log;
  slow-query-log-file选项指定慢查询日志的文件名称，默认是hostname-slow.log;
  查看本人机器服务器如下：

mysql> show variables like 'slow_query_log%';+---------------------+---------------------------------------------------------+| Variable_name       | Value                                                   |+---------------------+---------------------------------------------------------+| slow_query_log      | OFF                                                     || slow_query_log_file | /usr/local/mysql/data/xxxdeMacBook-Pro-slow.log |+---------------------+---------------------------------------------------------+2 rows in set (0.00 sec)mysql> show variables like 'general_log%';+------------------+----------------------------------------------------+| Variable_name    | Value                                              |+------------------+----------------------------------------------------+| general_log      | OFF                                                || general_log_file | /usr/local/mysql/data/xxxdeMacBook-Pro.log |+------------------+----------------------------------------------------+2 rows in set (0.01 sec)mysql> 

  使用举例：

• 把普通查询日志，写到文件和表中：

--log-output=TABLE,FILE --general_log=1

• 把普通查询日志和慢查询日志，只写到表中：

--log-output=TABLE --general-log=1--slow-query-log=1

• 把慢查询日志，只写到文件中：

--log-output=FILE （可以省略）--slow-query-log=1

1.2在服务器运行时

  通过控制与日志相关的系统变量，可以在服务器运行时，动态地控制日志的输出。

• global变量log_output：当前日志输出的目的地，可以动态修改；
• global变量general_log和slow_query_log：普通查询日志和慢查询日志的启用（ON）与禁用（OFF），可以动态修改；
• global变量general_log_file和slow_query_log_file：普通查询日志和慢查询日志的文件名称，可以在服务器启动时设置，可以在运行时动态修改；
• session变量sql_log_off：控制当前连接的查询日志的启用（ON）与禁用（OFF）；

2 错误日志

http://dev.mysql.com/doc/refman/5.7/en/error-log.html
- mysqld在启动、停止、以及运行过程中的错误日志；
- mysqld检查表、修复表的信息；
- mysqld非正常退出时的栈跟踪信息；
- mysqld_safe使用mysqld启动时，如果mysqld非正常退出，mysqld_safe重启mysqld的信息；

  mysqld写错误日志时，使用log-error选项：

 --log-error[=file_name]

  没有log-error选项时，错误日志输出到标准输出中；
  如果file_name有文件名，则会创建该文件；否则，写到默认文件host_name.err文件中；
  如果错误日志输出到标准输出中，则log_error变量为stderr，本机器上的值为：

+---------------------+----------------------------------------+| Variable_name       | Value                                  |+---------------------+----------------------------------------+| log_error           | /usr/local/mysql/data/mysqld.local.err || log_error_verbosity | 3                                      |+---------------------+----------------------------------------+2 rows in set (0.00 sec)mysql> 

  关于错误日志的更多内容，可以查看官方文档；

3 普通查询日志

  第一节中已经介绍如何开启普通查询日志以及设置输出目的地，这里补充几点：

  主从复制时，如果采用基于语句的二进制日志：

• slave会把从master接收到的语句写入到slave的查询日志中；
• 当slave使用mysqlbinlog工具从master读事件时，master会把语句写到master的查询日志中；

  如果采用基于行的二进制日志，则不会将更新语句，记录到查询日志中；
  如果采用基于混合模式的二进制日志，更新语句可能会记录到查询日志中，具体可参考：http://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html；

  服务器重启和日志刷新都不会产生新的日志文件，可以采用如下方式：

shell> mv host_name.log host_name-old.logshell> mysqladmin flush-logsshell> mv host_name-old.log backup-directory

  可以使用选项log-raw禁止语句中的密码重写，但是会带来安全风险：

4 二进制日志

  记录了数据库变化的事件，比如表的创建、表数据的更新；使用基于语句的日志模式时，还包含更新语句；此外，还包含更新数据时的耗时；

  二进制日志有两个重要目的：

• 主从复制
• 备份恢复

4.1 服务器启动时，开启binlog

  启动服务器时，可以使用log_bin选项开启日志：

 --log-bin[=base_name]

  该选项是global，如果设置base_name，会使得系统变量log_bin的值为ON，而不是[base_name]；系统变量log_bin_basename对应的值才是[base_name]；
  默认情况下，log_bin变量为OFF：

mysql> show variables like 'log_bin%';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin                         | OFF   || log_bin_basename                |       || log_bin_index                   |       || log_bin_trust_function_creators | OFF   || log_bin_use_v1_row_events       | OFF   |+---------------------------------+-------+5 rows in set (0.01 sec)

  MySQL5.7.3以后，开启日志，还需要指定选项sever-id;
  mac os下开启二进制日志方法如下：

  （1）拷贝默认的配置文件到/etc目录下

sudo cp my-default.cnf /etc/ 

  （2）重名配置文件为my.cnf

mv my-default.cnf my.cnf                         

  （3）编辑文件my.cnf, 编辑两个选项值

log_bin=my_log_base_nameserver_id = 1

  （4）重启mysql服务器

sudo /usr/local/mysql/support-files/mysql.server stopsudo /usr/local/mysql/support-files/mysql.server start

  （5）登录mysql服务器，查询日志相关的系统变量：

mysql> show variables like 'log_bin%';+---------------------------------+----------------------------------------------+| Variable_name                   | Value                                        |+---------------------------------+----------------------------------------------+| log_bin                         | ON                                           || log_bin_basename                | /usr/local/mysql/data/my_log_base_name       || log_bin_index                   | /usr/local/mysql/data/my_log_base_name.index || log_bin_trust_function_creators | OFF                                          || log_bin_use_v1_row_events       | OFF                                          |+---------------------------------+----------------------------------------------+5 rows in set (0.01 sec)

  （6）导入mysql测试库的建库建表语句以及测试数据：
      数据下载及导入
  查看二进制日志文件，这里会看到二进制文件的名称为my_log_base_name.000001，前缀是在步骤3中设置的，后缀则是以数字结尾的。

ls /usr/local/mysql/dataauto.cnf                           my_log_base_name.indexemployees                          mysqlib_buffer_pool                     mysqld_safe.pidib_logfile0                        performance_schemaib_logfile1                        sysibdata1                            zhengweiwzwdeMacBook-Pro.local.erribtmp1                             zhengweiwzwdeMacBook-Pro.local.pidmy_log_base_name.000001

4.2 binlog文件概述

  binlog文件是以数字后缀结尾的，服务器每产生一个binlog文件时，后缀数字就会自动加1；

  生成新binlog文件的情况有：

• 服务器启动时；
• 刷新日志（flush logs）时；
• 当前日志文件的大小超过max_binlog_size时；

  4.1小节最后列出的文件，有一个名为my_log_base_name.index的文件，这个文件保存了已经被用到的binlog文件，可以使用选项log-bin-index修改文件名称；查看文件内容如下：

sudo cat my_log_base_name.index./my_log_base_name.000001

4.3 binlog检验

• 事件长度
• checksum值

  默认情况下，事件被写入binlog时，事件长度也会被写入，用来校验是否写入正确；也可以通过设置系统变量binlog_checksum来将checksum的值写入：

    binlog_checksum = CRC32 （5.7版本的默认值）    binlog_checksum = NONE （禁用，只用长度做校验）

  当从master读取binlog时，默认也是用“事件长度”来校验正确性的；可以通过设置master_verify_checksum 系统变量，来启用checksum校验：

master_verify_checksum = ONmaster_verify_checksum = OFF （默认值，禁用，只用长度作校验）

  主从复制时，slave的IO线程也会读取master的“事件”的checksum，当slave的SQL线程读取中继日志（relay log）时，可以通过下面的设置来启用checksum校验：

slave_sql_verify_checksum=1 （默认）

4.4 binlog写入

• 写入时机：SQL语句或事务执行完，但在任何锁释放前或事务提交前；保证了binlog写入顺序和事务提交顺序一致；
• 对于非事务表的更新，在执行完，立即将binlog写入；
• 对于还未提交的事务，所有对事务表（如innodb表）的更新，都在缓存中，直到服务器收到commit指令时，在commit执行前，将整个事务写入binlog中；
• 对于非事务表的修改，不能回滚；如果一个事务被回滚了，但是这个事务中包含对非事务表的修改，那么整个事务就会写入binlog，并以rollback结尾。这样就能确保对非事务的修改记录。关于这种事务异常，可以参考文章MySQL复制对事务的处理

4.4 其他binlog相关变量

• binlog_cache_size（系统变量）：当事务线程开始时，会分配一个大小为bin_log_size的缓冲区来存储日志，当日志容量超过这个值时，线程会创建临时文件来存储。当线程结束时，会将临时文件删除的。
    默认大小：32768=32K；
    最小值 ：4096=4K；
    最大值 ：32位系统上为4G；64位系统上为2^64；

mysql> show variables like '%binlog_cache%';+-----------------------+----------------------+| Variable_name         | Value                |+-----------------------+----------------------+| binlog_cache_size     | 32768                || max_binlog_cache_size | 18446744073709547520 |+-----------------------+----------------------+2 rows in set (0.01 sec)

• Binlog_cache_use(状态变量)：使用缓冲区存储的事务数量。
• Binlog_cache_disk_use(状态变量)：使用临时文件存储的事务数量。

mysql> show status like 'Binlog_cache%use';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Binlog_cache_disk_use | 0     || Binlog_cache_use      | 0     |+-----------------------+-------+2 rows in set (0.00 sec)

  可以根据Binlog_cache_use和Binlog_cache_disk_use的值来调节binlog_cache_size的大小，避免产生过多的临时文件，提交服务器性能。

• sync_binlog(系统变量)：这个值表示每提交多少个事务，才会把内存数据刷到磁盘中。
    sync_binlog=0：从不会刷到磁盘；
    sync_binlog=1：每个事务提交前，都会把log刷到磁盘，这样就能保证，即使是服务器异常，最多也只是丢失最后一个事务，但是这样会降低服务器的性能。

    在5.7.7版本以前，这个值默认为0，之后的版本默认为1；

mysql> show variables like 'sync_binlog';+---------------+-------+| Variable_name | Value |+---------------+-------+| sync_binlog   | 1     |+---------------+-------+1 row in set (0.00 sec)

4.5 binlog格式

  binlog格式有3种：

• 基于SQL语句的记录（statement-based logging）:MySQL最初的主从复制就是基于SQL语句进行的，服务器启动时设置选项：

--binlog-format=STATEMENT

• 基于行的记录（row-based logging）:将单行是如何改变的记入日志，所以table用主键来唯一标识行是非常重要的，服务器启动时设置选项：

 --binlog-format=ROW

• 混合模式记录（mixed logging）:混合模式下，默认使用基于语句的日志记录，当基于语句的方式有的时候无法准确记住信息时（如包含时间函数），就转换为基于行的方式；服务器启动时设置选项：

 binlog-format=MIXED.

  也可以在服务器运行时，通过设置全局变量的值，来修改日志格式：

SET GLOBAL binlog_format = 'MIXED';

  但是修改正在进行主从复制的主服务器的binlog格式，会导致主从复制失败，因为binlog格式的设置只是针对某个MySQL服务器，修改了master的binlog格式，并不会导致slave的也被修改。

  客户端可以设置会话级别的日志格式：

SET SESSION binlog_format = 'STATEMENT';

  MySQL5.7.7以前默认用的是基于语句的方式，5.7.7以后默认使用基于行的日志记录；
  如果使用的是InnoDB表，并且事务隔离级别是读已提交或读未提交，那么只能使用基于行的格式；如果在运行时切换日志格式，会导致数据无法插入；
  当使用基于行的日志格式时，有些变化仍然使用基于语句的格式，如DDL操作；

未完待续