MySQL——日志文件与日志作用

2017年11月1日 828点热度 0人点赞 0条评论

MySQL常用的日志文件

错误日志(error log)： log-err

查询日志(general query log)： log

慢查询日志: -log-slow-queries

二进制日志 (binary log)： log-bin

中继日志( relay log)：realy-log

innodb 在线redo 日志

查看MySQL支持的日志

mysql> show variables like 'log_%';
+----------------------------------------+----------------------------+
| Variable_name | Value |
+----------------------------------------+----------------------------+
| log_bin | ON |
| log_bin_basename | /var/mysql/mysql-bin |
| log_bin_index | /var/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysqld.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexe|

1.错误日志

错误日志是一个文本文件。记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。

在默认情况下，系统记录错误日志的功能是开启的，默认文件是/var/log/mysqld.log，错误信息被输出到标准错误输出。更改log存储位置需要更改 log-error选项

指定日志路径两种方法

编辑my.cnf 写入

log-error=[path]/[file]

通过命令参数错误日志

mysqld_safe --user=mysql --log-error=[path]/[file]

2. 查询日志

Geleral log记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来，记录的格式为 {Time ,Id ,Command,Argument }。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。

因此，Mysql默认是把General log关闭的。

general query log写入日志是在客户端执行命令，并且释放锁之前，写入日志

数据库访问量很大，想要从SQL方面进行优化，因为为了性能考虑，一般general log不会开启。slow log可以定位一些有性能问题的sql，而general log会记录所有的SQL。

general query log和slow query log开始支持写到文件或者数据库表两种方式，并且日志的开启，输出方式的修改，都可以在Global（全局）级别动态修改。

查看日志的存放方式 show variables like 'log_output';

mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.02 sec)

如果设置

mysql> set global log_output='table'

的话，则日志结果会记录到名为gengera_log的表中，这表的默认引擎都是CSV

如果设置表数据到文件

set global log_output=file;

设置general log的日志文件路径：

set global general_log_file='/var/log/mysqld/general.log';

开启general log：

set global general_log=on; ##不需要服务器的重启

关闭general log：

set global general_log=off;

查看/var/log/mysqld/general.log的信息，可以大致看到哪些sql查询/更新/删除/插入比较频繁了。

比如有些表不是经常变化的，查询量又很大，就完全可以cache；对主备延迟要求不高的表，读可以放到备库；等等

set global sql_log_off=on; 可以让当前session不记录执行的sql 语句

3、慢查询日志

慢查询日志采用的是简单的文本格式，可以通过文本编辑器查看其中的内容。

其中记录了语句的执行时刻，执行消耗的时间，执行用户，连接主机等相关信息。

mysql还提供了专门分析日志的工具， mysqldumpslow（MySQL5.7）用来解决可能存在的性能问题。

慢查询只有在大于long_query_time 指定时间的sql statement , min_examined_row_limit最小检查行数限制才会被检查，语句被写入slow log 是在语句被执行后，并且释放锁之前，默认情况下，慢查询日志是被关闭的（因为性能问题）

long_query_time 的值被设置为0-10之间，默认值是10。

log_slow_admin_statements ：系统管理语句，在慢查询日志中. 系统管理语句包括

ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE

log_queries_not_using_indexes 默认情况下，管理语句和不使用索引的语句是不会记录在慢查询中的，需要人工设置

log-short-format 记录少量的日志

log_throttle_queries_not_using_indexes ：5.6.5版本开始新增了一个参数log_throttle_queries_not_using_indexes，用来表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。该值默认为0，表示没有限制。

在生产环境下，若没有使用索引，此类SQL语句会频繁地被记录到slow log，从而导致slow log文件的大小不断增加，故DBA可通过此参数进行配置。

log_slow_slave_statements :默认情况下主从架构的子节点是不会复制慢查询日志，为了改变这个，你可以去使用thelog_slow_slave_statements这个系统变量来开启。

##指定初始化参数，开启慢查询

slow_query_log = 1 #0是关闭慢查询，1是打开

slow_query_log_file = /var/lib/mysql/log-slow-query.log ##指定慢查询日志的名称，如果你没有指定慢查询日志的名称，默认会在数据文件目录下创建 host_name-slow.log慢查询日志

show variables like 'log_output';##日志存放的方式，是表还是文件

未验证选项：

在mysql 5.1.12 之前需要使用sql语句来修改：不能按照my.conf中的项来修改的。修改通过"show VARIABLES like "%slow%" "

打开慢查询，运行如下sql：

set global log_slow_queries = ON;

mysql> set long_query_time=0.01; #设置大于0.1s的sql语句记录下来

使用mysql自带命令mysqldumpslow查看

常用命令
-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string

eg:
s，是order的顺序，说明写的不够详细，看了代码，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序 -t，是top n的意思，即为返回前面多少条的数据 -g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。

mysqldumpslow -t 10 -s t -g “left join” host-slow.log

这个是按照时间返回前10条里面含有左连接的sql语句。

4 二进制日志

二进制日志不会记录select ，show 等不修改数据的语句。打开二进制日志会消耗一些系统系能，但是对于高可用和系统恢复，所带来的好处大于减少的性能。

二进制日志即binlog日志记录数据定义语言(DDL)和数据控制语言(DML) 但不包括数据查询语言。

二进制日志的功能

恢复（recovery）
复制（replication）

binlog 还有其他的一下附加参数

max_binlog_size 设置binlog的最大存储上线，当日志达到该日志的上限时，mysql会重新创建一个日志开始记录，不过偶尔也会超出该设置的binlog，一般都是以为即将达到上限时候，产生了一个比较大的事物，为了保证事物的安全，mysql不会将同一个事物分开记录到两个binlog

binlog-do-db=db_name ：指定保存更新到 binlog 的数据库。需要对某个数据库中的数据进行记录，如果有了binlog-do-db=db_name 显示指定，mysql会忽略正对其他书库执行query，而仅仅记录正对指定数据库执行的query

binlog-ignore-db=db_name ：显示的指定忽略某个数据库的binlog记录。

binlog-ignore-db=db_name ：指定不保存更新到 binlog 的数据库

说明：

binlog-do-db 和binlog-ignore-db ：参数有一个共同的概念，参数db_name 不是指query 语句更新的数据所在的数据库，而是执行query的时候，当前所处的数据库。不论更新那个数据库的数据，mysql仅仅比较当前连接所处的数据库与参数设置的数据库名。而不会分析query语句所更新的数据库所在数据库。

binlog_cache_size :

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

当使用事务的存储引擎InnoDB时，所有未提交的事务会记录到一个缓存中，等待事务提交时，直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32KB，此外，binlog_cache_size是基于回话的，也就是，当一个线程开始一个事务时，mysql会自动分配一个大小为binlog_cache_size的缓存，因此该值得设置需要相当小心，可以通过show global status 查看binlog_cache_use、binlog_cache_disk_use的状态，可以判断当前binlog_cache_size的设置是否合适。

sync_binlog:

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

参数sync_binlog=[N]表示每写缓存多少次就同步到磁盘，如果将N设置为1，则表示采用同步写磁盘的方式来写二进制日志，该参数很重要，这个以后还会提到。值得注意的是，在将该参数设置为1时，也应该将innodb_support_xa设为1来解决，这可以确保二进制日志和InnoDB存储引擎数据文件的同步

set global sql_log_off=on;## 可以让当前session不记录执行的sql 语句

Log-slave-update ##该参数在搭建master=>slave=>slave的架构时，需要配置。

指定日志路径mysqld_safe --user=mysql --log-bin=[path] &
如果没有指定文件名默认mysql-bin，默认路径为datadir目录

编辑my.cnf

[mysqld]

log-bin = /var/mysql/binlog/mysql-bin    ##指定二进制日志的名称或者路径

server-id = 1##MySQL5.6以后需要标明服务器顺序
read-only=0##是否只读

log_bin_index = /var/mysql/binlog/mysql-bin.index    ##二进制日志索引的名称

relay_log = /var/log/mysql/mysql-bin.relay ##中继日志的名称

relay_log_index = /var/log/mysql/mysql-bin.relay.index    ##中继日志索引的名称

如果你执行FLUSH LOGS，log-bin 会使用新的二进制日志编号。

查看binlog

由于binlog以是binary方式存取，不能直接查看，需要用mysql提供的mysqlbinlog工具查看

mysqlbinlog mysql-bin.000001#查看编号000001的日志

show binlog events \G##只能查看第一个日志的events
show binlog events in 'mysql-bin.000003';##可以查看具体位置日志的events

删除binlog

1. mysql> reset master; 删除所有日志，新日志重新从000001开始编号

2. mysql> purge master logs to 'mysq-bin.******' 命令可以删除指定编号前的所有日志
3. mysql> purge master logs to before 'YYYY-MM-DD HH:MM:SS' 命令可以删除'YYYY-MM-DD HH:MM:SS'之前的产生的所有日志
4. 在my.cnf 中指定日志过期 expire_logs-days=n

5.可以使用purgebinary logs语句来清除binary logs。

6.Binlog_format参数也特别重要。从mysql5.1版本开始引入这个参数，该参数可以设置的值有STATEMENT、ROW、MIXED；

（1）STATEMENT格式和之前的mysql版本一样，二进制日志文件记录的是日志的逻辑失SQL语句。

（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况，此时可以将InnoDB的事务隔离基本设为READ COMMITTED，以获得更好的并发性。

（3）MIXED格式下，mysql默认采用的STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况包括：

1）表的存储引擎为NDB，这时对于表的DML操作都会以ROW格式记录

2）使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数

3）使用了INSERT DELAY语句

4）使用了用户定义函数

5）使用了临时表

mysql> show variables like "%binlog_format%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)

5 中继日志( relay log)

中继日志也是二进制日志，用来给slave （主从架构的从服务器）恢复

6 innodb 在线redo 日志 innodb redo log

innodb 是一个事物安全的存储引擎，其事物安全性主要通过在线的redo日志和记录在表空间的undo信息来保证。redo 日志中记录了innodb所做的所有物理变更和事物信息。innodb的redo日志同样默认存放在数据目录下面。可以通过innodb_log_group_home_dir 来更改日志的存放位置。

通过innodb_log_files_in_group 设置日志的数量。

更新日志: -log-update

更新日志是在mysql 比较老的版本上使用的，其功能和binlog 基本类似，只不过，不是一二进制格式来记录，而是简单的文本格式记录内容，自从吗ysql增加了binlog功能之后，就很少使用更新日志了。