mysql 几种常见的日志设置

wll2015年09月14日 22:47 分类 : MySQL  > MySQL应用
阅读: 1747

分享到微信朋友圈

MySQL日志是我们需要掌握的知识,下面就为您介绍几个最常见的MySQL日志类型,如果想学习下日志的设置可以看看。MySQL日志包含下面几种: 错误日志(error log): log-err 查询日志(general query log): log 慢查询日志: -log-slow-queries 二进制日志 (binary log): log-bin 中继日志( relay log) innodb 在线redo 日志

MySQL日志是我们需要掌握的知识,下面就为您介绍几个最常见的MySQL日志类型,如果想学习下日志的设置可以看看。MySQL日志包含下面几种:

错误日志(error log): log-err
查询日志(general  query log): log
慢查询日志: -log-slow-queries
二进制日志 (binary log): log-bin
中继日志( relay log)
innodb 在线redo 日志

默认情况下,没有启动任何log,可以通过log 选项来启动相关的log

mysql> show variables like 'log_%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_trust_function_creators | OFF                      |
| log_error                       | /var/lib/mysql/mysql.log |
| log_output                      | FILE                     |
| log_queries_not_using_indexes   | OFF                      |
| log_slave_updates               | ON                       |
| log_slow_queries                | OFF                      |
| log_warnings                    | 1                        |
+---------------------------------+--------------------------+
8 rows in set (0.00 sec)
默 认情况下,所有日志创建于mysqld数据目录中。通过刷新日志,你可以强制 mysqld来关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志)。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时,出现日志刷新

1. 错误日志
错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下,系统记录错误日志的功能是关闭的,错误信息被输出到标准错误输出。需要在启动的时候开启 log-error选项
如果没有指定文件名 默认hostname.err,默认路径为datadir目录
指定日志路径两种方法
编辑my.cnf     写入 log-error=[path]
通过命令参数错误日志 mysqld_safe --user=mysql --log-error=[path] &
mysql> show variables like '%err%' ;
+--------------------+--------------------------+
| Variable_name      | Value                    |
+--------------------+--------------------------+
| error_count        | 0                        |
| log_error          | /var/log/mysql/mysql.log |
| max_connect_errors | 10                       |
| max_error_count    | 64                       |
| slave_skip_errors  | OFF                      |
+--------------------+--------------------------+
5 rows in set (0.00 sec)
error log 的清理
mv  mysql.log  mysql_old.log
mysqladmin  -uroot -pmysql flush-logs
On Windows, use rename rather than mv.
2 查询日志 general  query log

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。l

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.00 sec)
如果设置mysql> set global log_output='table'  的话,则日志结果会记录到名为gengera_log的表中,这表的默认引擎都是CSV
如果设置表数据到文件  set global log_output=file;
设置general log的日志文件路径:set global general_log_file='/tmp/general.log';
开启general log: set global general_log=on;    ---不需要服务器的重启
关闭general log: set global general_log=off;

查看tmp/general.log的信息,可以大致看到哪些sql查询/更新/删除/插入比较频繁了。比如有些表不是经常变化的,查询量又很大,就完全可以cache;对主备延迟要求不高的表,读可以放到备库;等等

general  query log 的清理
mv general.log  general_old.log
mysqladmin  -uroot -pmysql flush-logs
On Windows, use rename rather than mv.
tcsql perf test "

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

3、慢查询日志: -log-slow-queries
慢查询日志采用的是简单的文本格式,可以通过文本编辑器查看其中的内容,其中记录了语句的执行时刻,执行消耗的时间,执行用户,连接主机等相关信息。mysql还提供了专门分析日志的工具, mysqlslowdump用来解决可能存在的性能问题。
慢查询只有在大于
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 :默认情况下a replication slave does not write replicated queries to the slow query log. To change this, use thelog_slow_slave_statements system variable.
---指定初始化参数,开启慢查询
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> show VARIABLES like "%slow%"  ; 
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| log_slow_queries    | OFF                               |
| slow_launch_time    | 2                                 |
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/log-slow-query.log |
+---------------------+-----------------------------------+
4 rows in set (0.00 sec)
mysql> show variables like "long_query_time";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.010000 |
+-----------------+----------+
1 row in set (0.00 sec)

使用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 二进制日志: -log-bin
二进制日志不会记录select ,show 等不修改数据的语句。打开二进制日志会消耗一些系统系能,但是对于复制和系统恢复,所带来的好处大于减少的性能,
二进制日志即binlog日志 记录数据定义语言(DDL)和数据控制语言(DML) 但不包括数据查询语言

二进制日志的功能
1、恢复(recovery
2、复制(replication

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

binlog-do-db=db_name  明确告诉mysql,需要对某个数据库记录binlog,如果有了binlog-do-db=db_name 显示指定,mysql会忽略正对其他书库执行query,而仅仅记录正对指定数据库执行的query
binlog-ignore-db=db_name :显示的指定忽略某个数据库的binlog记录。

binlog-do-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_usebinlog_cache_disk_use的状态,可以判断当前binlog_cache_size的设置是否合适



sync_binlog:
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
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=[path]

log-bin             = /var/log/mysql/mysql-bin.log    --指定二进制日志的名称
log_bin_index       = /var/log/mysql/mysql-bin.log.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工具查看
/usr/local/mysql/bin/mysqlbinlog mysql-bin.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
可以使用purgebinary logs语句来清除binary logs。

Binlog_format参数也特别重要。从mysql5.1版本开始引入这个参数,该参数可以设置的值有STATEMENT\ROWMIXED

(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 | STATEMENT |
+---------------+-----------+
1 row in set (0.00 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功能之后,就很少使用更新日志了。

总结:

日志直接性能损耗数据库系统中最为昂贵的IO 资源,在默认情况下,系统仅打开错误日志,关闭了其他所有日志,以达到尽可能减少IO 损耗提高系统性能的目的。

但在实际应用场景中,都至少需要打开二进制日志,因为这 是MySQL 很多存储引擎进行增量备份的基础,也是MySQL 实现复制的基本条件。有时候为了进一步的性能优化,定位执行较慢的SQL 语句,很多系统也会打开慢查询日志来记录执行时间超过特定数值的SQL 语句。

一般情况下,在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将MySQL 中执行的每一条Query 都记录到日志中,会该系统带来比较大的IO 负担,而带来的实际效益却并不是非常大。

一般只有在开发测试环境中,为了定位某些功能具体使用了哪些SQL 语句的时候,才会在短时间段内打开该日志来做相应的分析。所以,在MySQL 系统中,会对性能产生影响的MySQL日志(不包括各存储引擎自己的日志)主要是Binlog 。



wll
wll的专栏

精彩评论:0

还可以输入250个字 评论

评论成功

评论失败

 

微信公众号

微博