Mysql数据库日志
日志分类
| MySql日志类型 | 解析说明 |
| 错误日志（error log） | 当数据库启动、运行、停止时产生该日志 |
| 普通查询日志（general query log） | 客户端连接数据库执行语句时产生该日志 |
| 二进制日志（binary log） | 当数据库内容发生改变时产生该日志，也被用来实现主从复制功能 |
| 中继日志（relay log） | 从库上收到主库的数据更新时产生该日志 |
| 慢查询日志（show query log） | SQL语句在数据库查询超过指定时间时产生该日志 |
| DDL日志（metadata log） | 执行DDL语句操作元数据时产生该日志 |
1、错误日志
错误日志说明
错误日志的配置
错误日志轮询
安装数据库故障排除
2、普通查询日志
普通查询日志说明
普通查询日志的配置
3、二进制日志
二进制日志的说明
二进制日志的配置
二进制日志实在my.cnf的[mysqld]模块下配置[mysqld]log-bin=/application/mysql-5.6.36/data/mysql-binbinlog_format=row
binlog_formatmysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。① STATEMENT模式（SBR）每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)② ROW模式（RBR）不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。③ MIXED模式（MBR）以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------------+| Variable_name | Value |+---------------------------------+-----------------------------------------+| log_bin | ON #记录binlog开关 || sql_log_bin | ON #临时不记录binlog开关 +---------------------------------+-----------------------------------------+sql_log_bin可是临时关闭binlog，只是当前session窗口有效，不影响其他会话mysql> set sql_log_bin=OFF;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------------+| Variable_name | Value |+---------------------------------+-----------------------------------------+| log_bin | ON || log_bin_basename | /application/mysql/data/mysql-bin || log_bin_index | /application/mysql/data/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | OFF |+---------------------------------+-----------------------------------------+6 rows in set (0.00 sec)重新开启一个session会话窗口mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------------+| Variable_name | Value |+---------------------------------+-----------------------------------------+| log_bin | ON || log_bin_basename | /application/mysql/data/mysql-bin || log_bin_index | /application/mysql/data/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+-----------------------------------------+6 rows in set (0.00 sec)该会话下sql_log_bin=ON开启的
查看sql_log_bin=OFF是否生效mysql> set sql_log_bin=OFF;Query OK, 0 rows affected (0.00 sec)mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 143 || mysql-bin.000002 | 376 || mysql-bin.000003 | 143 || mysql-bin.000004 | 120 | ###最新的binlog文件及位置点+------------------+-----------+4 rows in set (0.00 sec)mysql> create database king;Query OK, 1 row affected (0.00 sec)mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 143 || mysql-bin.000002 | 376 || mysql-bin.000003 | 143 || mysql-bin.000004 | 120 |###最新的binlog文件及位置点+------------------+-----------+4 rows in set (0.00 sec)binlog日志没有变化mysql> system mysqlbinlog mysql-bin.000004|grep king;mysql>过滤binlog文件，没有记录binlog
开启sql_log_bin=ONmysql> set sql_log_bin=ON;Query OK, 0 rows affected (0.00 sec)mysql> drop database king;Query OK, 0 rows affected (0.17 sec)mysql> system mysqlbinlog mysql-bin.000004|grep king;drop database kingmysql> 发现了有记录的binlog
sql_bin_log的功能实在用户使用mysql恢复数据时，不希望恢复的数据SQL记录到binLog里面
二级制日志文件的刷新条件
1、数据库重启会自动刷新binlog新文件2、执行"mysqldump -F" 或"mysqladmin flush-logs"会将binlog刷新为新文件3、binlog文件达到1GB时，会自动刷新binlog为新文件4、人为配置切割及调整binlog最大值控制参数及默认大小查看：mysql> show variables like 'max_binlog_size';+-----------------+------------+| Variable_name | Value |+-----------------+------------+| max_binlog_size | 1073741824 |+-----------------+------------+1 row in set (0.00 sec)
二进制日志索引文件
二进制日志除了很多按序列生成的binlog文件列表外，还有一个索引文件[root@mysql-2 data]# pwd/application/mysql-5.6.36/data[root@mysql-2 data]# ls -l mysql-bin*-rw-rw---- 1 mysql mysql 143 Jun 15 03:08 mysql-bin.000001-rw-rw---- 1 mysql mysql 376 Jun 15 10:17 mysql-bin.000002-rw-rw---- 1 mysql mysql 143 Jun 15 15:12 mysql-bin.000003-rw-rw---- 1 mysql mysql 299 Jun 15 15:49 mysql-bin.000004-rw-rw---- 1 mysql mysql 164 Jun 15 15:12 mysql-bin.index索引文件名与binlog文件一样，只是扩展名为index，索引文件的内容[root@mysql-2 data]# cat mysql-bin.index /application/mysql/data/mysql-bin.000001/application/mysql/data/mysql-bin.000002/application/mysql/data/mysql-bin.000003/application/mysql/data/mysql-bin.000004binlog索引文件的控制参数、mysql> show variables like 'log_bin_index';+---------------+-----------------------------------------+| Variable_name | Value |+---------------+-----------------------------------------+| log_bin_index | /application/mysql/data/mysql-bin.index |+---------------+-----------------------------------------+1 row in set (0.00 sec)
删除二进制日志的方法（谨慎操作！！！）
binlog日志很重要，不要随意删除。确定什么时候可以删除binlog每天的数据库**全备时刻**,以前的binlog无用了，实际企业中需要保留3-7天的本地binlog文件按。
方法1：设置参数自动删除binlogexpire_logs_days = 7 ##设置删除7天前的日志默认该参数是没有配置的mysql> show variables like 'expire_logs_days';+------------------+-------+| Variable_name | Value |+------------------+-------+| expire_logs_days | 0 |+------------------+-------+1 row in set (0.00 sec)在全局下设置mysql> set global expire_logs_days=7;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'expire_logs_days';+------------------+-------+| Variable_name | Value |+------------------+-------+| expire_logs_days | 7 |+------------------+-------+1 row in set (0.00 sec)
方法2：从最开始一直删除到指定的文件位置（不含指定文件）一般用于处理临时的需求做一个备份[root@mysql-2 data]# cp mysql-bin.* /tmpmysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 143 || mysql-bin.000002 | 376 || mysql-bin.000003 | 143 || mysql-bin.000004 | 299 |+------------------+-----------+4 rows in set (0.00 sec)删除到mysql-bin.000002的binlog(不包含mysql-bin.000002)只删除mysql-bin.000001mysql> purge binary logs to 'mysql-bin.000002';Query OK, 0 rows affected (0.00 sec)mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000002 | 376 | #####000002以前的就没有了| mysql-bin.000003 | 143 || mysql-bin.000004 | 299 |+------------------+-----------+3 rows in set (0.00 sec)
方法3：按照时间删除binlog日志用于处理临时需求[root@mysql-2 data]# ls -l --time-style=long-iso mysql-bin*-rw-rw---- 1 mysql mysql 376 2019-06-15 10:17 mysql-bin.000002-rw-rw---- 1 mysql mysql 143 2019-06-15 15:12 mysql-bin.000003-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004-rw-rw---- 1 mysql mysql 123 2019-06-15 16:15 mysql-bin.index删除"2019-06-15:23"删除mysql-bin.000002和mysql-bin.000003mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000002 | 376 || mysql-bin.000003 | 143 || mysql-bin.000004 | 299 |+------------------+-----------+3 rows in set (0.00 sec)mysql> system ls -l --time-style=long-iso mysql-bin*;-rw-rw---- 1 mysql mysql 376 2019-06-15 10:17 mysql-bin.000002-rw-rw---- 1 mysql mysql 143 2019-06-15 15:12 mysql-bin.000003-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004-rw-rw---- 1 mysql mysql 123 2019-06-15 16:15 mysql-bin.indexmysql> purge master logs before '2019-06-15:23';Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> system ls -l --time-style=long-iso mysql-bin*;-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004-rw-rw---- 1 mysql mysql 41 2019-06-15 16:22 mysql-bin.indexmysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000004 | 299 |+------------------+-----------+1 row in set (0.00 sec)
方法4：删除所有的binlog,并从000001开始重新记录reset master命令可以清楚所欲的binlog文件，并从000001开始重新记录mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000004 | 299 |+------------------+-----------+1 row in set (0.00 sec)mysql> reset master;Query OK, 0 rows affected (0.01 sec)mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 120 |+------------------+-----------+1 row in set (0.00 sec)mysql> system ls -l --time-style=long-iso mysql-bin*;-rw-rw---- 1 mysql mysql 120 2019-06-15 16:26 mysql-bin.000001-rw-rw---- 1 mysql mysql 41 2019-06-15 16:26 mysql-bin.index
binlog的常用相关参数
查看binlog相关参数mysql> show variables like 'binlog_%';+-----------------------------------------+--------------+| Variable_name | Value |+-----------------------------------------+--------------+| binlog_cache_size | 32768 || binlog_checksum | CRC32 || binlog_direct_non_transactional_updates | OFF || binlog_error_action | IGNORE_ERROR || binlog_format | ROW || binlog_gtid_simple_recovery | OFF || binlog_max_flush_queue_time | 0 || binlog_order_commits | ON || binlog_row_image | FULL || binlog_rows_query_log_events | OFF || binlog_stmt_cache_size | 32768 || binlogging_impossible_mode | IGNORE_ERROR |+-----------------------------------------+--------------+12 rows in set (0.00 sec)mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------------+| Variable_name | Value |+---------------------------------+-----------------------------------------+| log_bin | ON || log_bin_basename | /application/mysql/data/mysql-bin || log_bin_index | /application/mysql/data/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+-----------------------------------------+6 rows in set (0.00 sec)
1、binlog_cache_size:二进制日志缓存时数据库为每一个客户连接分配的内存空间。对于事务引擎来说，适当调整该参数可以获得更好的性能默认值：mysql> show variables like '%binlog_cache%';+-----------------------+----------------------+| Variable_name | Value |+-----------------------+----------------------+| binlog_cache_size | 32768 || max_binlog_cache_size | 18446744073709547520 |+-----------------------+----------------------+2 rows in set (0.00 sec)
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)
3、sync_binlog这个参数的作用是控制binlog什么时候同步到磁盘。对数据库来说，这是很重要的参数，它不仅会影响数据库的性能，还会影响数据库数据的完整性。对于“sync_binlog”参数的说明具体如下。·“sync_binlog=0”表示在事务提交之后，数据库不会将binlog_cache中的数据刷新到磁盘，而是让文件系统自行决定什么时候来做刷新或者在缓存满了之后才刷新到磁盘。·“sync_binlog=n”表示每进行n次事务提交之后，数据库都会进行一次将缓存数据强制刷新到磁盘的操作。该参数默认的设置是0，示例如下：mysql> show variables like '%sync_binlog%';+---------------+-------+| Variable_name | Value |+---------------+-------+| sync_binlog | 1 |+---------------+-------+1 row in set (0.00 sec)设置为0时数据库的性能是最好的，但数据风险也是最大的，对于数据安全性要求较高的数据库，应该调整该参数将其改为1，值得注意的是，即使参数设置为1，仍然有binlog记录的内容与数据库的实际内容不一致的风险。可以在my.cnf下的[mysqld]模块设置[mysqld]sync_binlog=1
记录二进制日志的三种模式
(1)语句（statement-based）模式是MySQL5.6版本默认的模式，简单地说，就是每一条被修改的数据的SQL语句都会记录到master的binlog中。在复制slave库的时候，SQL进程会解析成与原来master端执行过的相同的SQL来再次执行。该模式的优点是不需要记录细到每一行数据的更改变化，因此，可减少binlog日志量，实际上是减少了很多，节约了磁盘I/O，提高了系统性能。但该模式同样有一些缺点，由于语句模式记录的是执行的SQL语句，所以，对于某些具有特殊功能的SQL语句来说，就可能会导致无法在从库上正确执行，从而导致主从库数据不一致的问题。例如，当特殊的函数被执行时，当触发器、存储过程等特殊功能被执行时，而row level模式是基于每一行来记录变化的，所以不会出现类似的问题
(2)行级模式简单地说，行级（row-based）模式就是将数据被修改的每一行的情况记录为一条语句。优点：在行级模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改了，修改成什么样了即可，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。缺点：行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行之后，日志中记录的就是100万行的修改记录，binlog日志的量可能会大得惊人。
（3）混合模式混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。当函数中包含UUID()时。当表中有自增列（AUTO_INCREMENT）被更新时。·当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。·当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。
二进制日志模式的选择
在互联网公司中，使用MySQL的特殊功能比较少（存储过程、触发器、函数），此时可以选择默认的语句模式。如果公司较多用到MySQL的特殊功能，如存储过程、触发器、函数等，并且需要做主从复制请首选行级模式，次选mixed模式。
二进制日志模式的配置
临时配置mysql> SET GLOBAL binlog_format = 'STATEMENT';mysql> SET GLOBAL binlog_format = 'ROW';mysql> SET GLOBAL binlog_format = 'MIXED';永久配置在my.cnf的mysqld模块下配置[mysqld]binlog_format=statementbinlog_format=rowbinlog_format=mixed
行模式二进制日志实际读取
mysql> use test;mysql> INSERT INTO test1 values(1,'a'),(2,'b');Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from test1;+----+------+| id | name |+----+------+| 1 | a || 2 | b |+----+------+2 rows in set (0.00 sec)
[root@mysql-2 data]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001#<==--base64-output=decode-rows -v以行级模式解析binlog日志。.......BEGIN/*!*/;# at 192#190615 19:17:34 server id 1 end_log_pos 243 CRC32 0x35c8b48a Table_map: `test`.`test1` mapped to number 72# at 243#190615 19:17:34 server id 1 end_log_pos 292 CRC32 0x3d5c2369 Write_rows: table id 72 flags: STMT_END_F### INSERT INTO `test`.`test1`### SET### @1=1### @2='a'### INSERT INTO `test`.`test1`### SET### @1=2### @2='b'# at 292#190615 19:17:34 server id 1 end_log_pos 323 CRC32 0x3e0a646e Xid = 61COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@mysql-2 data]#
4、慢查询日志
慢查询日志说明
慢查询日志（slow query log）是记录执行时间超出指定值（long_query_time）或其它地址条件（如：没有使用索引，结果集大于10000行）的SQL语句
慢查询日志的参数说明
| 慢查询参数 | 解释说明 |
| slow_query_log | 慢查询开启开关，默认为”OFF“ |
| slow-query-log-file | 记录慢查询语句的文件，文件名如"hostname-slow.log" |
| long_query_time | 记录大于指定N秒的SQL语句，默认为10s，也可以使用微秒单位 |
| log_queries_not_using_indexes | 记录没有使用到索引的SQL语句，默认为"OFF" |
| min_examined_row_limit | 记录结果大于N行的SQL语句，默认为0行 |
| log_slow_admin_statements | 记录管理的慢SQL语句，如：alter table, analyze table, check table, create table, create index, drop index, optimize table, repair table |
| log_throttle_queries_not_using_indexes | 限制每分钟写入记录的慢SQL语句的数量，默认为"0",表示没有限制 |
企业中常见的慢查询配置参数
在my.cnf的[mysqld]模块下配置[mysqld]slow-query-log = ON #<==慢查询开启开关long_query_time = 2 #<==记录大于2秒的SQL语句。log_queries_not_using_indexes = ON #<==没有使用到索引的SQL语句。slow-query-log-file = /application/mysql-5.6.36/slow.log #<==记录SQL语句的文件。min_examined_row_limit = 800 #<==记录结果集大于800行的SQL语句。
测试检查mysql> show variables like 'slow_query%';+---------------------+-----------------------------+| Variable_name | Value |+---------------------+-----------------------------+| slow_query_log | ON | #开启慢查询日志| slow_query_log_file | /application/mysql-5.6.26/slow.log | #文件路径+---------------------+-----------------------------+2 rows in set (0.00 sec)mysql> show variables like '%long_query%';+-----------------+----------+| Variable_name | Value |+-----------------+----------+| long_query_time | 2.000000 | #记录大于2s的查询+-----------------+----------+1 row in set (0.00 sec)mysql> show variables like '%log_queries_not%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | ON | #记录没有使用索引的查询+-------------------------------+-------+1 row in set (0.00 sec)mysql> show variables like '%min_examined_row_limit%';+------------------------+-------+| Variable_name | Value |+------------------------+-------+| min_examined_row_limit | 800 | #记录查询结果大于800行的SQL语句+------------------------+-------+1 row in set (0.00 sec)
慢查询日志的刷新方法
cd /application/mysql-5.6.36/mv slow.log slow.log.$(date +%F)mysqladmin flush-log制作脚本放入定时任务脚本：在/server/scripts下创建脚本vim /erver/scripts/cut_slow_log.sh
#!/bin/bashexport PATH=/application/mysql-5.6.36/bin:/sbin:/bin:/usr/bin:/usr/sbincd /application/mysql-5.6.35mv slow.log slow.log.$(date +%F)mysqladmin flush-log
放入定时任务[root@mysql-2 data]# crontab -e#cut mysql slow log00 00 * * * /bin/sh /erver/scripts/cut_slow_log.sh
分析慢查询日志的工具案例
(1)mysqlsla安装请提前下载好mysqlsla-2.03.tar.gz到指定目录下，然后执行如下命令安装：yum install perl-devel perl-DBI perl-DBD-MySQL -yrpm -qa perl-devel perl-DBI perl-DBD-MySQLtar xf mysqlsla-2.03.tar.gzcd mysqlsla-2.03perl Makefile.PLmake && make install
（2）利用mysqlsla工具分析慢查询mysqlsla命令的默认路径为：/usr/local/bin/mysqlsla。简单语法如下：mysqlsla -lt slow [SlowLogFilePath] > [ResultFilePath]在实际工作中，老男孩通常使用脚本调用mysqlsla工具进行分析，然后每天早晨8点，把分析结果发给企业的核心人员（DBA、运维总监、CTO、研发总监、核心开发），最后由DBA配合核心开发共同优化这些棘手的SQL慢查询。简单的案例脚本如下，注意切割日志和分析合并为一个脚本了：vim /server/scripts/slow_log_analyze.sh
#!/bin/bashexport PATH=/application/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbinDate=$(date +%F -d -1day)#cut slow logcd /application/mysql/mv slow.log slow.log_$Datemysqladmin flush-log#analyze slow logPath=/usr/local/bin/mysqlslacd /application/mysql$Path/mysqlsla -lt slow slow.log_$Date > analyzed_slow_$Dste.log 2>&1#rsync analyzed_slow to backup server 省略此步#send analyzed slow log to administrator on backup server by mail. 省略此步
定时任务crontab -e00 00 * * * /bin/sh /server/scripts/slow_log_analyze.sh
mysqldumpslow命令mysqldumpslow -s c -t 10 /data/slow/slow.logmysqldumpslow -s at -t 10 /data/slow/slow.log这会输出记录次数最多的10条SQL语句，其中：-s是表示按照何种方式排序c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序。ac、at、al、ar，表示相应的倒叙。-t是top n的意思，即为返回前面多少条的数据；
