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.log shell> mysqladmin flush-logs shell> 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_name server_id = 1

  （4）重启mysql服务器

sudo /usr/local/mysql/support-files/mysql.server stop sudo /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/data auto.cnf my_log_base_name.index employees mysql ib_buffer_pool mysqld_safe.pid ib_logfile0 performance_schema ib_logfile1 sys ibdata1 zhengweiwzwdeMacBook-Pro.local.err ibtmp1 zhengweiwzwdeMacBook-Pro.local.pid my_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 = ON master_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操作；

未完待续