你的位置:首页 > 数据库

[数据库]mysql中的日志(一)


如果你是一个Web开发人员,你需要参考各种日志文件,以调试应用程序或改善其性能。日志是故障排除的最佳手段。对于著名的MySQLdatabase服务器(或MariaDB服务器),你需要参考以下日志文件:

  • The Error Log. 包含服务器运行时发生的错误信息(也服务器启动和停止)
  • The General Query Log. 这是mysqld运行的常规记录(连接,断开连接,查询)
  • The Slow Query Log. slow SQL statements

这篇文章并不是指二进制日志。它需要非常高的标准服务器硬件,只有在特殊情况下(如复制,主 - 从机设置,某些数据恢复操作)是非常有用的。 除此之外,它只是一个“性能杀手”。

关于MySQL日志的官方文档 here.

 

通过配置文件启用

配置参数在 [mysqld] 下面

 vim /etc/mysql/my.cnf



默认的配置文件 (in Debian server)

# * Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.# As of 5.1 you can enable the log at runtime!#general_log_file    = /var/log/mysql/mysql.log#general_log       = 1## Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.## Here you can see queries with especially long duration#log_slow_queries    = /var/log/mysql/mysql-slow.log#long_query_time = 2#log-queries-not-using-indexes## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about#    other settings you may need to change.#server-id       = 1#log_bin            = /var/log/mysql/mysql-bin.logexpire_logs_days    = 10max_binlog_size     = 100M#binlog_do_db      = include_database_name#binlog_ignore_db    = include_database_name



默认情况下,所有的日志文件没有启用 (除了windows吓得错误日志). Default Debian setup sends Error log to syslog

Error Log

错误日志记录到syslog的情况,配置文件 /etc/mysql/conf.d/mysqld_safe_syslog.cnf

[mysqld_safe]syslog



推荐的方式. 移除 /etc/mysql/conf.d/mysqld_safe_syslog.cnf  然后在 /etc/mysql/my.cnf 添加

[mysqld_safe]log_error=/var/log/mysql/mysql_error.log [mysqld]log_error=/var/log/mysql/mysql_error.log



General Query Log

general_log_file    = /var/log/mysql/mysql.loggeneral_log       = 1



Slow Query Log

log_slow_queries    = /var/log/mysql/mysql-slow.loglong_query_time = 2log-queries-not-using-indexes



Restart MySQL server after changes

service mysql restart



or, using systemd

systemctl restart mysql.service



 

运行时启用日志

从 MySQL 5.1 你可以禁用日志 at runtime.

启用日志, (mysql -u root -p ) 

SET GLOBAL general_log = 'ON';SET GLOBAL slow_query_log = 'ON';



禁用日志, (mysql -u root -p )

SET GLOBAL general_log = 'OFF';SET GLOBAL slow_query_log = 'OFF';



所有平台使用,无须重启

查看日志

Error log

1
tail -f /var/log/syslog



注意: 如果你没有指定 Error log file, 默认在 (usually /var/lib/mysql in a file named {host_name}.err).

General Query log

1
tail -f /var/log/mysql/mysql.log



注意: 如果你没有指定 General log file, 默认在 (usually /var/lib/mysqlin a file named {host_name}.log).

Slow Query log

1
tail -f /var/log/mysql/mysql-slow.log



注意: 如果你没有指定 Slow Query log file, 默认在 (usually/var/lib/mysql in a file named {host_name}-slow.log).

日志切割

不要忘记了切割日志.否则日志文件将会很大

In Debian (and Debian derivatives as Ubuntu etc) 

1
nano /etc/logrotate.d/mysql-server



其他版本,可能需要一些改变

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# - I put everything in one block and added sharedscripts, so that mysql gets
#   flush-logs'd only once.
#   Else the binary logs would automatically increase by n times every day.
# - The error log is obsolete, messages go to syslog now.
/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
        daily
        rotate 7
        missingok
        create 640 mysql adm
        compress
        sharedscripts
        postrotate
                test -x /usr/bin/mysqladmin || exit 0
                # If this fails, check debian.conf!
                MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
                if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
                  # Really no mysqld or rather a missing debian-sys-maint user?
                  # If this occurs and is not a error please report a bug.
                  #if ps cax | grep -q mysqld; then
                  if killall -q -s0 -umysql mysqld; then
                    exit 1
                  fi
                else
                  $MYADMIN flush-logs
                fi
        endscript
}



查看变量配置

TIP: Use show variables like '%log%'; 检验日志文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
root@cosmos ~ # mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 144332
Server version: 5.5.31-0+wheezy1 (Debian)
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show variables 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                        | 10                             |
| general_log                             | OFF                            |
| general_log_file                        | /var/lib/mysql/cosmos.log      |
| innodb_flush_log_at_trx_commit          | 1                              |
| innodb_locks_unsafe_for_binlog          | OFF                            |
| innodb_log_buffer_size                  | 8388608                        |
| innodb_log_file_size                    | 5242880                        |
| innodb_log_files_in_group               | 2                              |
| innodb_log_group_home_dir               | ./                             |
| innodb_mirrored_log_groups              | 1                              |
| log                                     | OFF                            |
| log_bin                                 | OFF                            |
| log_bin_trust_function_creators         | OFF                            |
| log_error                               |                                |
| log_output                              | FILE                           |
| log_queries_not_using_indexes           | OFF                            |
| log_slave_updates                       | OFF                            |
| log_slow_queries                        | OFF                            |
| log_warnings                            | 1                              |
| max_binlog_cache_size                   | 18446744073709547520           |
| max_binlog_size                         | 104857600                      |
| max_binlog_stmt_cache_size              | 18446744073709547520           |
| 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                          | OFF                            |
| slow_query_log_file                     | /var/lib/mysql/cosmos-slow.log |
| 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)



官方文档 here.

何时启用日志

默认情况下,所有的日志被禁用 (windows下的错误日志除外).Debian下默认讲错误日志写入 syslog.

事实上,没有什么情况下,日志能解决关键问题(这里还是英文来表示比较合适)

  • 总是启用错误日志.
  • 启用查询日志 (preferably at runtime) 当出现以下几种情况
    • check that your application handles correctly MySQL database connection (a common mistake is to connect multiple times to MySQL from a single script)
    • monitor executed queries from your application
    • test memcached (or similar software), checking out if a query was executed or memcached has handled the request
  • 启用慢查询 (preferably from MySQL configuration file for a short period of time, e.g. 2-3 days) when your application performance is reduced for some reason and you should detect the slow queries.

示例

General query log:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
131021 17:43:50    43 Connect root@localhost as anonymous on pnet_blog
       43 Init DB pnet_blog
       43 Query SELECT count(id) as total_posts FROM posts WHERE date_published is not null AND date_published <= '20131021144350'
       43 Query SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0,10
       44 Connect root@localhost as anonymous on pnet_blog
       44 Query SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144350' ORDER BY date_published DESC LIMIT 0, 10
       44 Quit
       43 Quit
131021 17:44:28    45 Connect root@localhost as anonymous on pnet_blog
       45 Init DB pnet_blog
       45 Query SELECT * FROM posts WHERE url='how-and-when-to-enable-mysql-logs'
       45 Query UPDATE posts SET impressions=impressions+1 WHERE id='41'
       45 Query SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published < '20131020150000' ORDER BY date_published DESC LIMIT 0,1
       45 Query SELECT url, post_title FROM posts WHERE date_published IS NOT NULL AND date_published > '20131020150000' ORDER BY date_published ASC LIMIT 0,1
       45 Query SELECT * FROM posts WHERE date_published is not null AND date_published <= '20131021144428' AND date_published >= '20130421144428' ORDER BY impressions DESC LIMIT 0,10
       46 Connect root@localhost as anonymous on pnet_blog
       46 Query SELECT id, title, impressions FROM tips WHERE date_published IS NOT NULL AND date_published <= '20131021144428' ORDER BY date_published DESC LIMIT 0, 10
       46 Quit
       45 Quit



 

原文:http://www.pontikis.net/blog/how-and-when-to-enable-mysql-logs