mysql日志总结

吉佳

第1章 Mysql日志
1.1 错误文件的配置方式
1.1.1 方法1：在my.cnf配置文件中调整
注意，是在[mysql_safe]模块下面进行配置
　　[root@mobanji logs]# vim /etc/my.cnf
　　[mysqld_safe]
　　log-error = /application/mysql/logs/cc666.err
1.1.2 方法2：在启动MySQL服务的命令里加入记错错入日志参数
　　显示如下：
　　[root@mobanji ~]# mysqld_safe --log-error=/application/mysql-5.6.34/data/cc666.err &
　　[1] 13188
　　mysql> show variables like 'log_error%';
　　+---------------+------------------------------------+
　　| Variable_name | Value                              |
　　+---------------+------------------------------------+
　　| log_error     | /application/mysql/logs/cc666.err |
　　+---------------+------------------------------------+
　　1 row in set (0.00 sec)
　　mysql>
1.1.3 错误日志（error log）轮询
　　管理员可以使用命令轮询错误日志，例如可以按天轮询，具体方法如下：
　　[root@mobanji ~]# cd /application/mysql/data/                    #切换到日志目录下
　　[root@mobanji data]# mv cc666.err cc666_$(date +%F).err        #将错误日志按天移动改名
　　[root@mobanji data]# mysqladmin flush-logs                       #执行刷新日志命令
　　[root@mobanji data]# mysqladmin -uroot -p123  flush-logs
　　Warning: Using a password on the command line interface can be insecure.
　　[root@mobanji data]# ls -l old
　　cc666/                cc666_2017-05-04.err  oldgirl/
　　[root@mobanji data]# ls -l cc666_2017-05-04.err                 #新的错误日志诞生了
　　-rw-r----- 1 mysql root 60 May  4 21:41 cc666_2017-05-04.err
　　[root@mobanji data]# cat cc666_2017-05-04.err
　　170504 21:41:02 mysqld_safe A mysqld process already exists
　　[root@mobanji data]#
1.1.4 数据库故障排查案例分析
　　新手安装数据库时，遇到数据库无法启动时的排查方法为：
　　1.先把错误日志文件清空，然后重新启动MySQL服务，再查看日志文件报有什么错误，并根据错误日志进行处理。
　　2.如果无法解决，则删除数据文件，重新初始化数据库。
　　假设在故障排查时，得到的错误日志提示为：
　　InnoDB:The error means mysqld does  not have the access rights to
　　Innodb:the directory
　　根据提示可知，是权限问题导致的问题，可对数据目录递归执行权限，然后在重启数据库命令如下：
　　[root@mobanji data]# chown -R mysql.mysql /application/mysql/data/
　　[root@mobanji data]#
1.2 普通查询日志（general query log）介绍与配置
1.2.1 普通日志查询介绍
　　普通日志查询的作用是记录客户端连接信息，以及执行的SQL语句信息
1.2.2 普通查询日志（gengeral query log)功能配置
　　可能官方考虑到普通查询日志的重要性比较低，因此默认情况下普通查询日志是关闭状态，如下所示：
　　[mysqld]   #写在[mysql]下
　　general_log = on
　　general_log_file = /application/mysql-5.6.34/data/cc666.log
　　[root@mobanji data]# cat /application/mysql-5.6.34/data/cc666.log
　　/application/mysql/bin/mysqld, Version: 5.6.34-log (Source distribution). started with:
　　Tcp port: 0  Unix socket: (null)
　　Time                 Id Command    Argument
1.2.3 开启日志查询临时开启
　　mysql> set global general_log = on;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> show variables like 'general_log%';
　　+------------------+-------------------------------------------+
　　| Variable_name    | Value                                     |
　　+------------------+-------------------------------------------+
　　| general_log      | ON                                        |
　　| general_log_file | /application/mysql-5.6.34/data/cc666.log |
　　+------------------+-------------------------------------------+
　　2 rows in set (0.00 sec)
　　mysql>
　　mysql> system cat /application/mysql/data/db02.log
　　/application/mysql/bin/mysqld, Version: 5.6.34-log (Source distribution). started with:
　　Tcp port: 3306  Unix socket: /application/mysql-5.6.34/tmp/mysql.sock
　　Time                 Id Command    Argument
　　170504 10:34:57     6 Query show variables like 'general_log%'
　　170504 10:35:51     6 Query select * from mysql.user
　　170504 10:36:02     6 Query show databases
　　访问量不大的企业没有审计需求的不用开启。互联网公司要关闭普通日志，减小磁盘IO读写负载
1.2.4 普通查询日志（general query log）生产使用建议
　　在高并发数据库的场景，普通查询日志应该是关闭的（默认也是关闭），主要是因为查询日志信息量会很大，容易导致磁盘I/O性能问题，当访问量不是很大，而企业又有审计执行的SQL语句需求时，可以考虑开启该功能。
1.3 二进制日志（binary log）介绍与配置
1.3.1 二进制日志（binary log）介绍

　　二进制日志（binary log）的作用是记录数据库里的数据被修改的SQL语句，一般为DDL和DML语句，例如含有insert update delete create drop>1.3.2 二进制日志（binary log）作用
　　二进制日志最重要的作用有2个：
　　第一个记录MySQL数据的增量数据，用来做增量数据库恢复，没有二进制功能，MySQL的备份将无法完整还原数据
　　第二个是实现主从复制功能，具体可见MySQL主从复制相关内容
1.3.3 二进制日志（binary log）配置
　　前文已经讲过了，查验二进制文件内容如下所示
　　[root@mobanji data]# grep log_bin /etc/my.cnf
　　# log_bin
　　log_bin = /application/mysql/logs/cc666-bin
　　[root@mobanji data]#
　　mysql> system mysqlbinlog cc666-bin.00005|grep  "cc666"
　　mysql> show variables like 'log_bin';
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| log_bin       | ON    |#记录binlog开关
　　+---------------+-------+
　　1 row in set (0.00 sec)
　　mysql> show variables like '%log_bin';
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| log_bin       | ON    |  #记录binlog开关
　　| sql_log_bin   | ON    |  #临时不记录binlog开关
　　+---------------+-------+
　　2 rows in set (0.00 sec)
　　mysql>
　　mysql> system mysqlbinlog cc666-bin.000013|grep 'cc666';   # 过滤binlog文件 没有
　　mysql>
　　mysql> drop database cc666;
　　Query OK, 1 row affected (0.23 sec)
　　mysql> system mysqlbinlog cc666-bin.000013|grep 'cc666';  #继续过滤发现记录了binlog
　　drop database cc666
　　mysql>
　　Sql_log_bin的功能就是用户通常用于mysql数据恢复时，不希望恢复的数据SQL记录到binlog里面的情况。
　　有个参数可以实现在开启binlog功能的前提下，临时不记录binlog,实例如下：
　　mysql> set session sql_log_bin = OFF;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> show variables like  '%log_bin';
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| log_bin       | ON    |
　　| sql_log_bin   | OFF   |    #已经关闭
　　+---------------+-------+
　　2 rows in set (0.00 sec)
　　mysql> show variables like  '%log_bin';
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| log_bin       | ON    |
　　| sql_log_bin   | OFF   |
　　+---------------+-------+
　　2 rows in set (0.00 sec)
　　mysql> create database cc666;
　　Query OK, 1 row affected (0.00 sec)
　　mysql> system mysqlbinlog cc666-bin.000013|grep 'cc666';
　　drop database cc666
　　mysql> system mysqlbinlog cc666-bin.000013|grep 'cc666';
　　drop database cc666
　　mysql>
1.4 二进制文件刷新条件
　　数据库重启会自动刷新binlog为新文件
　　执行mysqldump -F 或mysqladmin flush-logs 会将binlog刷新为新文件
　　Binlog文件达到1GB左右时，会自动刷新binlog为新文件
　　人为配置切割及调整
　　Binlog 最大值控制参数及默认大小查看方法如下：
　　mysql> show variables like 'max_binlog_size';
　　+-----------------+------------+
　　| Variable_name   | Value      |
　　+-----------------+------------+
　　| max_binlog_size | 1073741824 |
　　+-----------------+------------+
　　1 row in set (0.00 sec)
1.5 二进制日志（binary log）索引文件
　　除了很多按序生成的binlog文件列表外，还有一个索引文件，例如下文里的cc666-bin.index
　　[root@mobanji data]# cd /application/mysql/logs/
　　[root@mobanji logs]# ls
　　bin.sql            cc666-bin.000008  cc666-bin.000011  cc666-bin.index
　　cc666-bin.000006  cc666-bin.000009  cc666-bin.000012  cc666.err
　　cc666-bin.000007  cc666-bin.000010  cc666-bin.000013  slow.log
　　索引文件的文件名和binlog文件一样，只是扩展名为index,查看索引文件内容的命令如下：
1.5.1 binlog文件的索引参数
　　mysql> show variables like 'log_bin_index';
　　+---------------+------------------------------------------+
　　| Variable_name | Value                                    |
　　+---------------+------------------------------------------+
　　| log_bin_index | /application/mysql/logs/cc666-bin.index |
　　+---------------+------------------------------------------+
　　1 row in set (0.00 sec)
　　mysql>
1.6 删除二进制日志（binary log）日志方法
　　Binlog日志很重要，不能随意清除，有些读者看到所维护的服务器空间满了，竟然会直接删除binlog物理文件，这样的操作是错误的，应避免，那么如何正确删除binlog文件呢？
　　首先1，要确定什么时候可以删除binlog
　　理论上每天的数据库全备时刻以前的binlog都是无用的，但是工作中我们会根据需要保留3-7天的本地binlog的文件。
1.6.1 设置参数自动删除binlog
　　设置参数自动删除binlog是每个管理员都应该做的，参数设置例如下
　　假设参数为
　　Expire_logs_days=7 #删除七天以前的日志
　　该参数默认是没有配置的，生产中可以实现在线修改永久配置文件
　　mysql> show variables like 'expire_logs-days';
1.6.2 从最开始一直删除到指定文件位置（不含指定文件）
　　mysql> show binary logs;
　　+-------------------+-----------+
　　| Log_name          | File_size |
　　+-------------------+-----------+
　　| cc666-bin.000006 |       143 |
　　| cc666-bin.000007 |      1487 |
　　| cc666-bin.000008 |      4033 |
　　| cc666-bin.000009 |       576 |
　　| cc666-bin.000010 |       246 |
　　| cc666-bin.000011 |       168 |
　　| cc666-bin.000012 |       143 |
　　| cc666-bin.000013 |       218 |
　　+-------------------+-----------+
　　8 rows in set (0.00 sec)
　　删除标识位置之后的
　　mysql> purge binary logs to 'cc666-bin.000006';
　　Query OK, 0 rows affected (0.01 sec)
1.6.3 按照时间点进行删除
　　mysql> system ls -l --time-style=long-iso cc666-bin*;
　　-rw-rw---- 1 mysql mysql  143 2017-05-03 15:53 cc666-bin.000006
　　-rw-rw---- 1 mysql mysql 1487 2017-05-03 17:43 cc666-bin.000007
　　-rw-rw---- 1 mysql mysql 4033 2017-05-03 17:59 cc666-bin.000008
　　-rw-rw---- 1 mysql mysql  576 2017-04-25 08:53 cc666-bin.000009
　　-rw-rw---- 1 mysql mysql  246 2017-05-04 18:06 cc666-bin.000010
　　-rw-rw---- 1 mysql mysql  168 2017-05-04 21:55 cc666-bin.000011
　　-rw-rw---- 1 mysql mysql  143 2017-05-04 22:32 cc666-bin.000012
　　-rw-rw---- 1 mysql mysql  218 2017-05-05 12:12 cc666-bin.000013
　　-rw-rw---- 1 mysql mysql  336 2017-05-04 22:32 cc666-bin.index
　　mysql>
　　mysql> PURGE MASTER LOGS BEFORE '2017-05-02 19:53';
　　Query OK, 0 rows affected (0.01 sec)
1.7 常用binlog参数
1.7.1 Binlog_cache_size
　　二进制日志缓存是数据库为每一个客户连接分配的内存空间，对于事务引擎来说，适当调整该参数会获得更好的性能，该参数的默认值为：
1.7.2 Max_binlog_size
　　该参数用于设置binlog日志的最大大小，默认为1GB，但该值并不能很严格控制binlog大小，若binlog大小接近1GB，而此时又执行了一个较大事务，为了保证事务的完整性，数据库不会做日志刷新动作，而是直到该事务的日志全部记录进入当前binlog日志后才会进行刷新，该参数的默认值查询结果为
　　mysql> show variables like '%max_binlog_size%';
　　+-----------------+------------+
　　| Variable_name   | Value      |
　　+-----------------+------------+
　　| max_binlog_size | 1073741824 |
　　+-----------------+------------+
　　1 row in set (0.00 sec)
　　mysql>
1.7.3 Sync_binlog
　　这个参数的作用是控制binlog什么时候同步到磁盘。对数据来说，这是很重要的参数，它不仅可以影响数据库的性能，还会影响数据库的完整性
　　Sync_binlog=0 表示在事务提交之后，数据库不会将binlog_cache 中的数据刷到磁盘，而是让文件系统自行决定什么时候来做刷新或者缓存满之后才刷新到磁盘
　　Sync_binlog=n 表示每进行n次事务提交之后，数据库将进行一次缓存数据强制刷新到磁盘的操作。
　　该参数默认的设置是0，示例如下：
　　mysql> show variables like '%sync_binlog%';
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| sync_binlog   | 0     |
　　+---------------+-------+
　　1 row in set (0.14 sec)
　　mysql>
1.8 记录二进制文件的三种模式
　　MySQL使用不同的模式记录二进制日志信息，最常见的有三种模式
1.8.1 语句模式
　　语句模式是MySQL5.6版本模式，简单地说，就是每一条被修改的数据的SQL语句都会记录到master的binlog中，在复制slave库的时候，SQL进程会解析成和原来master端执行过的SQL来再次执行。
　　该模式的优点是不需要记录细到master的binlog中。在复制slave库的时候，SQL进程会解析成和原来master端执行过的相同的SQL来再次执行
　　该模式的优点是不需要记录到每一行数据的更改变化，因此，可减少binlog日志量，要少很多，节约磁盘I/O，提高系统性能。
　　但该模式同样有一些缺点，由于语句模式记录的是执行的SQL语句，所以，对于某些具有特殊功能的SQL语句，就可能导致无法再从库上正确执行，从而导致主从库数据不一致。
　　例如：当特殊的函数被执行时，当触发器，存储过程等特殊功能被执行时，而row level模式是基于每一行来记录变化的，所以不会出现类似的问题。
1.8.2 行级模式
　　简单地说，行级模式就是将数据被修改的每一行的情况记录为一条语句。
　　优点：在行级模式下，binlog中科院不记录执行的SQL语句的上下文相关信息，仅仅记录了哪一条被修改了，修改成什么样了，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的内容。
　　缺点：行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行后，日志中记录的就是100万行，语句模式就用一条语句即可搞定，而行级模式之后，日志中记录的就是100万行的修改记录，binlog量可能会大的惊人。
1.8.3 混合模式（mixed-based）
　　混合模式默认采用语句模式记录日志，会在一些特定情况下会记录模式切换为行级模式记录，这些特殊情况包含不限于有：
　　当函数中包含UUID（）时
　　当表中有自增列被更新时
　　当触发器或者等特殊功能时
　　当FOUND_ROWS(),ROW_COUNT()，USER（）CURRENT_USER（）CURRENT_USER等执行时
1.9 二进制日志的模式配置调整
　　临时调整命令如下：
　　mysql>SET GLOBAL binlog_format=’STATEMENT’;
　　>SET GLOBAL binlog_format=’ROW’
　　>SET GLOBAL binlog_format=’MIXED’
　　永久调整可以直接将‘binlog_format=’模式名’’写入到my.cnf配置文件中并重启服务。
1.10 慢查询日志
1.10.1 慢查询日志介绍
　　简单地理解，慢查询日志就是记录执行时间超出指定值或其它条件的SQL语句
1.10.2 慢查询日志相关参数说明
　　慢查询的参数对于数据库SQL的优化非常重要，是SQL优化前提，因此，这里以表的形式说明。

http://blog.51cto.com/e/u261/themes/default/images/spacer.gif

1.10.3 慢查询日志（slow query log）重要参数配置
　　Slow-query-log=ON                         #慢查询开启开关
　　Long_query_time =2                         #记录大于2秒的SQL语句
　　Log_queries_not_using_indexes =ON            #没有使用索引的SQL语句
　　Slow-query-log-file = /application/mysql/slow.log  #记录SQL语句的文件
　　Min_examinde_row_limit=800                #记录结果集大于800行的SQL语句
1.10.4 慢查询日志的刷新方法
　　在工作中，可以利用定时任务按天对慢查询日志进行切割，然后再分析。
　　示例切割脚本为：
　　[root@cc666 data]# mkdir /server/scripts/ -p
　　[root@cc666 data]# cat /server/scripts/cut_slow_log.sh
　　#!/bin/bash
　　# Author: cc666
　　# Organization: www.cc666edu.com
　　# Created Time : 2017-03-19 23:47:21
　　export PATH=/application/mysql/bin:/sbin:/bin:/usr/sbin:/usr/bin
　　cd /application/mysql &&\
　　mv slow.log slow.log.$(date +%F)&&\
　　mysqladmin flush-log
1.10.5 使用工具分析慢查询日志案例
　　实际工作中，慢查询的日志可能非常多，给优化的运维人员带来了一定的困难，MySQL官方提供了慢查询的分析工具mysqldumpslow，有兴趣的读者可以参考官方的4.6.9节“mysqldumpslow”了解该工具的用法
　　1.安装mysqlsla
　　请提前下载好mysqlsla-2.03.tar.gz到指定目录下，然后执行如下命令安装。
　　yum install  perl-devel perl-DBI perl-DBD-MySQL -y
　　rpm -qa perl-devel perl-DBI perl-DBD-MySQL
　　tar xf mysqlsla-2.03.tar.gz
　　cd mysqlsla-2.03
　　perl http://blog.51cto.com/e/u261/themes/default/images/spacer.gifMakefile.PL
　　make
　　make install
　　2.利用mysqlsla工具分析慢查询
　　Mysqlsla命令默认路径为：/usr/local/bin/mysqlsla
　　3.安装mysqlsla
　　3.mysqlsla安装
　　yum install  perl-devel perl-DBI perl-DBD-MySQL -y
　　rpm -qa perl-devel perl-DBI perl-DBD-MySQL
　　wget http://blog.51cto.com/e/u261/themes/default/images/spacer.gifhttp://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
　　tar xf mysqlsla-2.03.tar.gz
　　cd mysqlsla-2.03
　　perl http://blog.51cto.com/e/u261/themes/default/images/spacer.gifMakefile.PL
　　make
　　make install
　　mysqlsla命令默认路径为：
　　[root@cc666 mysqlsla-2.03]# which mysqlsla
　　/usr/local/bin/mysqlsla