错误日志
错误日志是 MySQL 中最重要的日志之一,它记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,可以首先查看此日志。
该日志是默认开启的 , 默认存放目录为 mysql 的数据目录(var/lib/mysql), 默认的日志文件名为 hostname.err
(hostname是主机名)。
相关配置
log-error
:错误日志存放路径log-warnings
:是否将警告信息也输入错误日志,1代表是(5.7后废弃)log_error_verbosity
:(代替log-warnings)错误日志级别- 1:errors only
- 2:errors and warnings
- 3:errors,warnings and notes
二进制日志
二进制日志包含了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句的事件信息,但是不包括数据查询语句。语句以"事件"的形式保存,所以包含了时间、事件开始和结束位置等信息。此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制, 就是通过该binlog实现的。
二进制日志,默认情况下是没有开启的,需要到MySQL的配置文件中开启,并配置MySQL日志的格式。
mysqld还创建一个二进制日志索引文件,当二进制日志文件滚动的时候会向该文件中写入对应的信息。所以该文件包含所有使用的二进制日志文件的文件名。默认情况下该文件与二进制日志文件的文件名相同,扩展名为'.index'。
相关配置
log_bin=[on|off|file_name]
:开启binlog,如果没有给定file_name,则默认为datadir下的主机名加"-bin",并在后面跟上一串数字表示日志序列号。binlog_format=[STATEMENT|ROW|MIXED]
:指定二进制日志的格式。- STATEMENT:该日志格式在日志文件中记录的都是SQL语句(statement),每一条对数据进行修改的SQL都会记录在日志文件中,通过Mysql提供的mysqlbinlog工具,可以清晰的查看到每条语句的文本。主从复制的时候,从库(slave)会将日志解析为原文本,并在从库重新执行一次。
- ROW:该日志格式在日志文件中记录的是每一行的数据变更,而不是记录SQL语句。比如,执行SQL语句 : update tb_book set status='1' , 如果是STATEMENT 日志格式,在日志中会记录一行SQL文件; 如果是ROW,由于是对全表进行更新,也就是每一行记录都会发生变更,ROW 格式的日志中会记录每一行的数据变更。
- MIXED:这是目前MySQL默认的日志格式,即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT,但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点,而避开他们的缺点。
log_bin_index = file_name
:配置索引文件的名字expire_logs_days
:指定自动删除二进制日志的时间,即日志过期时间binlog_do_db
:明确指定要记录日志的数据库binlog_ignore_db
:指定不记录二进制日志的数据库binlog_cache_size = 32768
: 基于事务类型的日志会先记录在缓冲区,当达到该缓冲大小时这些日志会写入磁盘max_binlog_size
:指定二进制日志文件最大值,超出指定值将自动滚动。但由于事务不会跨文件,所以并不一定总是精确。sync_binlog = { 0 | n }
:日志何时刷到磁盘- 0:不同步,日志何时刷到磁盘由FileSystem决定,这个性能最好。
- n:每写n次二进制日志事件(不是事务),MySQL将执行一次磁盘同步指令fdatasync()将缓存日志刷新到磁盘日志文件中。Mysql中默认的设置是sync_binlog=0,即不同步,这时性能最好,但风险最大。一旦系统奔溃,缓存中的日志都会丢失。
- 在Innodb的主从复制结构中,如果启用了二进制日志(几乎都会启用),要保证事务的一致性和持久性的时候,必须将sync_binlog的值设置为1,因为每次事务提交都会写入二进制日志,设置为1就保证了每次事务提交时二进制日志都会写入到磁盘中,从而立即被从服务器复制过去。
日志查看
由于日志以二进制方式存储,不能直接读取,需要用mysqlbinlog工具来查看,语法如下 :
mysqlbinlog log-file
日志删除
对于比较繁忙的系统,由于每天生成日志量大 ,这些日志如果长时间不清除,将会占用大量的磁盘空间。下面介绍几种删除日志的常见方法 :
1) 通过 Reset Master
指令删除全部 binlog 日志,删除之后,日志编号将从 xxxx.000001重新开始。
2) 执行指令 purge master logs to 'mysqlbin.******'
,该命令将删除 ******
编号之前的所有日志。
3) 执行指令 purge master logs before 'yyyy-mm-dd hh24:mi:ss'
,该命令将删除日志为 "yyyy-mm-dd hh24:mi:ss" 之前产生的所有日志。
4) 设置参数 --expire_logs_days=#
,此参数的含义是设置日志的过期天数, 过了指定的天数后日志将会被自动删除,这样将有利于减少DBA 管理日志的工作量。
查询日志
查询日志中记录了客户端的所有操作语句,而二进制日志不包含查询数据的SQL语句。
默认情况下, 查询日志是未开启的。如果需要开启查询日志,需要手动配置。通常不需要开启此日志。
相关配置
general_log = {0 | 1}
: 是否开启查询日志general_log_file
: 查询日志存放路径,不给定路径时默认的文件名以hostname.log
命名。
慢查询日志
MySQL 的慢查询日志是 MySQL 提供的一种日志记录,它用来记录在 MySQL 中响应时间超过阈值的语句,具体指运行时间超过 long_query_time
值的 SQL,则会被记录到慢查询日志中。
long_query_time
的默认值为 10,意思是运行 10s 以上的语句。默认情况下,Mysql 数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
格式大致如下:
# Time: 180118 14:58:37
# User@Host: root[root] @ localhost [] Id: 150
# Query_time: 0.000270 Lock_time: 0.000109 Rows_sent: 0 Rows_examined: 6
SET timestamp=1516258717;
delete from user where User='app';
是哪条语句导致慢查询(sql_text),该慢查询语句的查询时间(query_time),锁表时间(Lock_time),以及扫描过的行数(rows_examined)
相关配置
slow_query_log
:是否开启慢查询日志,1 表示开启,0 表示关闭。slow-query-log-file
:新版(5.6 及以上版本):MySQL 数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件 host_name-slow.loglong_query_time
:慢查询阈值,当查询时间多于设定的阈值时,记录日志。log_queries_not_using_indexes
:未使用索引的查询也被记录到慢查询日志中(可选项)。log_output
:日志存储方式。默认值是'FILE'
,表示将日志存入文件。'TABLE'
表示将日志存入数据库,这样日志信息就会被写入到mysql.slow_log
表中,支持同时记录到文件和数据库,配置的时候以逗号隔开即可。
慢日志分析工具 mysqldumpslow
在实际生产环境中,如果要手工分析日志,查找、分析 SQL,显然是个体力活,MySQL 提供了日志分析工具 mysqldumpslow。
参数:
-s
按照那种方式排序c
:访问计数l
:锁定时间r
:返回记录al
:平均锁定时间ar
:平均访问记录数at
:平均查询时间
-t
是top n的意思,返回多少条数据。-g
可以跟上正则匹配模式,大小写不敏感。
例:
得到返回记录集最多的 10 个 SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前 10 条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合 | 和 more 使用 ,否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
原创文章,作者:彭晨涛,如若转载,请注明出处:https://www.codetool.top/article/mysql%e4%b9%8b%e6%97%a5%e5%bf%97%e9%85%8d%e7%bd%ae%e5%85%a8%e8%a7%a3/