任何一种数据库,都有各种各样的日志,帮助DBA追踪数据库曾经发生过的各种事件。Mysql也不例外,在Mysql中,有四种不同的日志,分别是错误日志、二进制日志、查询日志和慢查询日志,这些日志可以帮助我们mysql服务器(mysqld)内部发生的事件,数据库性能故障,记录数据变更的历史,用户恢复数据库等等。


一、错误日志

错误日志记录了mysql启动和停止,以及服务器在运行过程中发生任何严重错误的相关信息。  在mysql数据库中,错误日志是默认开启的,并且错误日志无法被进制。


错误日志用log_error=[file_name] 指定mysqld(mysql服务器) 保存错误日志文件的位置。 如果没有指定file_name值,mysqld默认使用错误日志名host_name.err(host_name)为主机名) 并在数据目录中写入日志文件。


1. 配置方式:

在【mysqld】中添加如下信息:

#Enter a name for the error log file. Otherwise a default name will be used.  
 log-error="D:/SoftInstall/Mysql/mysql_loe_err.txt"

2. 查看是否启用了日志文件:  show global variables like '%bin%';


3.查看当前的错误日志配置,缺省情况下位于数据目录:


mac 查看mysql日志目录_慢查询


4.查看当前mysql server错误日志文件

150811 17:01:07 [Note]Plugin 'FEDERATED' is disabled.
15081117:01:07 InnoDB: The InnoDB memory heap is disabled     #禁用了InnoDB memory的堆功能
15081117:01:07 InnoDB: Mutexes and rw_locks use Windows interlocked functions   #Mutexes(互斥量)和rw_locks(行级锁)是GCC编译的是InnoDB内置的
15081117:01:07 InnoDB: Compressed tables use zlib 1.2.3  #默认压缩工具是zlib
15081117:01:07 InnoDB: Initializing buffer pool, size = 91.0M         #InnoDB引擎的缓冲池(buffer pool)的值大小
15081117:01:07 InnoDB: Completed initialization of buffer pool   
150811 17:01:07 InnoDB:highest supported file format is Barracuda.
150811 17:01:07  InnoDB: Waiting for the background threads tostart
150811 17:01:08 InnoDB:1.1.8 started; log sequence number 12384365
150811 17:01:08 [Note]Server hostname (bind-address): '0.0.0.0'; port: 3306
15081117:01:08 [Note]   - '0.0.0.0' resolves to'0.0.0.0';                       #0.0.0.0 会反解主机名,这里反解失败
15081117:01:08 [Note] Server socket created on IP: '0.0.0.0'.    
15081117:01:08 [Note] Event Scheduler: Loaded 0 events                                 #事件调度器没有任何事件,因为没有装载
15081117:01:08 [Note] D:\SoftInstall\Mysql\bin\mysqld: ready for connections.         #mysql启动完成等待客户端的请求
Version:'5.5.28'  socket: ''  port: 3306 MySQL Community Server (GPL)            #创建一个本地sock用于本地连接


二、 二进制日志

二进制日志也叫binlog,记录了所有的DDL和DML语句,但是不包括数据查询语句。 主要用于记录修改数据或有可能引起数据改变的mysql语句,并且记录了语句发生时间,执行时长,操作的数据等等。 所以通过二进制日志可以查询mysql数据库中进行了哪些变化。

1. 配置方式:

在【mysqld】添加如下信息:

#Enter a name for thebinary log. Otherwise a default name will be used. 
log-bin="D:/SoftInstall/Mysql/mysql_log_bin"

重启MySQL服务,在D:/SoftInstall/Mysql路径下生成以下文件:

mac 查看mysql日志目录_mysql_02


2. 查看二进制日志开启状态:

Mysql>show variables like 'log_%';


显示如下:

mac 查看mysql日志目录_sql_03


3. 看二进制日志数目:show maseter

mac 查看mysql日志目录_mac 查看mysql日志目录_04


4. 查看二进制日志文件: show binary logs 可以查看自己binlog的名称。

mac 查看mysql日志目录_慢查询_05


5.实例:

1. 创建一张test表,并且往里面插入数据,如下:

mac 查看mysql日志目录_mysql_06


查看产生的日志文件: show binlog events

mac 查看mysql日志目录_慢查询_07


6. 删除二进制日志:

对于比较繁忙的在线事务处理系统,由于每天生成日志量大,这些日志如果长时间不清除,将会对磁盘空间带来很大的浪费。因此定期删除日志是DBA维护Mysql数据库的一个重要内容。删除日志方式:

方法一: 

 

mac 查看mysql日志目录_sql_08

 

只想删除mysqlbin-log.00001以前的bin-log,可以用以下的命令: purgemaster logs to 'xxx.000001'

 

mac 查看mysql日志目录_mac 查看mysql日志目录_09

 

从上面我们可以看出,我们一般都需要用到mysql-bin.00001文件。那么这个文件是干什么用的呢?


1. 数据恢复

如果你的数据库出问题了,而之前你有过备份,那么可以看日志文件,找出是哪个命令导致你的数据库出问题了,想办法挽回损失。


2. 主从服务器之间同步数据

主服务器上所有的操作都在记录日志中,从服务器可以根据该日志来进行,以确保两个同步。


三、慢查询日志:

慢查询日志有什么用呢? 主要是用来记录所有执行时间超过参数long_query_time(单位:秒)时间的SQL语句,帮我们找到执行慢的SQL,方便我们对SQL语句进行优化。

Long_query_time 默认为10秒,最小为0,精度可以到微秒。

默认情况下,有两类常见语句不会记录到慢查询日志:管理语句和不使用索引进行查询的语句。 如果想要监控这两类sql语句,可以分别通过参数: long-slow-admin-statements 和long-queries-not-using-indexes进行控制。

 

1.配置方式:

在[mysqld]中添加如下信息:

log-slow-queries="D:/SoftInstall/Mysql/mysql-slow.log"
# 代表MYSQL慢查询的日志存储目录,此目录文件一定要有写权限;同时windows 下需要写绝对路径。
long_query_time = 4
#最长执行时间
log-queries-not-using-indexes #没有使用到索引的查询也将被记录在日志中

配置好以后重新启动MYSQL服务

 

2. 查看慢查询日志是否开启。

配置完成后执行 show variables like '%slow%'; 查看慢查询日志是否开启,如果slow_query_log 和log_slow_queries显示为on那么说明慢查询日志已经开启了。

如下所示:

mac 查看mysql日志目录_mysql_10

 

Slow_launch_time 跟慢查询日志没有任何关系,它代表的是thread create的一个阙值,如果要看long_query_time,可以用以下方式进行查看。

 

mac 查看mysql日志目录_mac 查看mysql日志目录_11

 

3. Mysql 慢查询日志分析 :

# Time: 150822 15:15:50
# User@Host: root[root] @localhost [127.0.0.1]
# Query_time:0.001003  Lock_time: 0.000000 Rows_sent:78  Rows_examined: 78
use itcastoa0720;
SET timestamp=1440227750;
select * fromitcast_privilege;
# Time: 150822 15:17:54
# User@Host: root[root] @localhost [127.0.0.1]
# Query_time:0.064004  Lock_time: 0.053005 Rows_sent:1  Rows_examined: 78
SET timestamp=1440227874;
select count(*) fromitcast_privilege;
# Time: 150822 15:18:06
# User@Host: root[root] @localhost [127.0.0.1]
# Query_time:0.001003  Lock_time: 0.000000 Rows_sent:6  Rows_examined: 6
SET timestamp=1440227886;
select * fromitcast_department;
D:\SoftInstall\Mysql\bin\mysqld,Version: 5.5.28-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, NamedPipe: (null)
Time                 Id Command    Argument

 


四、 查询日志:

记录了客户端的所有语句,而二进制日志不包含只查询语句的语句。 

查询日志可以存放到一个文本文件或者表中,所有连接和语句被记录到该日志文件或表,缺省未开启该日志。

使用参数--log[=file_name] 选项启动它。 如果没有给定file_name的值,默认名是host_name.log

 

1.  配置方式:

在【mysqld】中配置以下信息:

#Enter a name for the query log file. Otherwise a default name will be used.
  log="D:/SoftInstall/Mysql/mysql_log"

重启mysql服务器。


2. 查看查询日志是否开启: show globalvariables like '%log%';

mysql> show globalvariables like '%log%';
+-----------------------------------------+--------------------+
| Variable_name                           | Value
   |
+-----------------------------------------+--------------------+
| back_log                                | 50
   |
| binlog_cache_size                       | 32768
   |
|binlog_direct_non_transactional_updates | OFF
   |
| binlog_format                           | STATEMENT
   |
|binlog_stmt_cache_size                  |32768
   |
| expire_logs_days                        | 0
   |
| general_log                             | ON
   |
| general_log_file                        | D:/SoftInstall/M
   |
|innodb_flush_log_at_trx_commit          |1
   |
|innodb_locks_unsafe_for_binlog          |OFF
   |
|innodb_log_buffer_size                  |1048576
   |
|innodb_log_file_size                    |48234496
   |
|innodb_log_files_in_group               |2
   |
|innodb_log_group_home_dir               |.\
   |
|innodb_mirrored_log_groups              |1
   |
| log                                     | ON
   |
| log_bin                                 | ON
   |
|log_bin_trust_function_creators         |OFF
   |
| log_error                               |D:\SoftInstall\M
xt |
| log_output                              | FILE
   |
|log_queries_not_using_indexes           |ON
   |
| log_slave_updates                       | OFF
   |
| log_slow_queries                        | ON
   |
| log_warnings                            | 1
   |
|max_binlog_cache_size                   |1844674407370954
   |
| max_binlog_size                         | 1073741824
   |
|max_binlog_stmt_cache_size              |1844674407370954
   |
| max_relay_log_size                      | 0
   |
| relay_log                               |
   |
| relay_log_index                         |
   |
|relay_log_info_file                     |relay-log.info
   |
| relay_log_purge                         | ON
   |
| relay_log_recovery                      | OFF
   |
|relay_log_space_limit                   |0
   |
| slow_query_log                          | ON
   |
|slow_query_log_file                     |D:/SoftInstall/M
   |
| sql_log_bin                             | ON
   |
| sql_log_off                             | OFF
   |
| sync_binlog                             | 0
   |
| sync_relay_log                          | 0
   |
|sync_relay_log_info                     |0
   |
+-----------------------------------------+--------------------+
41 rows in set (0.00 sec)

解析:
general_log   # 定义查询日志是否开启
general_log_file   #定义查询日志的文件地址名称

3. 使用记事本查看mysql查询日志 

D:\SoftInstall\Mysql\bin\mysqld,Version: 5.5.28-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, NamedPipe: (null)
Time                 Id Command    Argument
15082214:05:42            1Connect        root@localhost on
    1Query        select @@version_commentlimit 1
15082214:06:33            1Query        show global variables like'%log%'
15082214:07:32            1Query        mysql> show globalvariables like '%log%'
15082214:08:26            2Connect        root@localhost on
    2Query        select @@version_commentlimit 1
15082214:08:45            2Query        show global variables like'%log%'
15082214:17:39            2Query        show databases
15082214:17:44            2Query        SELECT DATABASE()
    2 InitDB        mytest
15082214:17:47            2Query        show tables
15082214:17:55            2Query        SELECT DATABASE()
    2 InitDB        mysql
15082214:18:01            2Query        show tables

可以看到mysql启动信息以及用户root连接服务器与执行查询语句的记录。

4. 删除查询日志:
查询日志是以文本文件的形式存储在文件系统中的,记录用户的所有操作。因此在用户查询、更新频繁的情况下,查询日志会增长得很快,DBA可以定期删除比较早的通用日志,以节省磁盘空间。
 
       a. 直接删除log文件 

       b.执行 flush logs , log 文件重新生成。


5. 扩展分析:

日志的输出位置一般有三种方式:file(文件),table(表),none(不保存);其中前两个输出位置可以同时定义,none表示是开启日志功能但是记录日志信息。file就是通过general_log_file |/mydata/data/stu18.log等方式定义的,而输出位置定义为表时查看日志的内容方式为:


mysql>use mysql;                      # 在此数据库中           
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
|general_log               |      #这个就是查询日志的表输出位置
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
|time_zone_leap_second     |
| time_zone_name            |
|time_zone_transition      |
|time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)



总结

日志虽然可以帮助我们诊断数据库出现的各种问题,但是同时日志也会影响mysql的性能,也会占用大量磁盘空间。因此,如果不必要,尽可能的开启日志。所以还需根据不同的环境去配置合适的日志管理。