假如你是一名 web 开发人员,如果你想调试你的应用或提升其性能的话,那你需要去参考各种日志文件。日志是开始故障排除最好的选择。就著名的 MySql 数据库服务器而言,你需要参考以下日志文件:
关于 MySql 日志的官方文档参考 http://dev./doc/refman/5.7/en/server-logs.html。 通过 MySql 配置启用日志日志相关参数位于 [mysqld] 部分。编辑 MySql 配置文件: nano /etc/mysql/my.cnf 以上是 Debian 下的默认安装目录,其他 Linux 发布版可能不太一样,这个文件中 MySql 服务器的参数如下: # * 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.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name MySql 安装默认是不启用所有日志文件的(Windows 平台下的 error 日志除外)。Debian 安装 MySql 默认是将 error 日志发送给 syslog。 error 日志根据 /etc/mysql/conf.d/mysqld_safe_syslog.cnf 配置,error 日志推送给 syslog:[mysqld_safe] syslog 这是推荐的做法。如果你由于某种原因,不想讲 error 日志推给 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_log_file = /var/log/mysql/mysql.log general_log = 1 慢查询日志要启用慢查询日志,将相关行取消注释(或者添加)即可:log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes 配置修改后重启 MySql 服务器以上方法要求服务重启才能生效:service mysql restart 或者使用 systemd: systemctl restart mysql.service 运行时启用日志MySql 5.1 之后我们可以在运行时启用或者禁用日志。运行时启用日志,登录 MySql 客户端(mysql -u root -p)然后执行: SET GLOBAL general_log = 'ON'; SET GLOBAL slow_query_log = 'ON'; 运行时禁用日志,登录 Mysql 客户端(mysql -u root -p)后执行: SET GLOBAL general_log = 'OFF'; SET GLOBAL slow_query_log = 'OFF'; 这种方式适用于所有平台并且不需要重启服务。 显示日志结果error 日志按以上办法设置以后,你可以通过以下命令显示 error 日志:tail -f /var/log/syslog 备注:如果你没有配置 error 日志文件,MySql 将把 error 日志保存在数据目录(通常是 /var/lib/mysql)下的一个名为 {host_name}.err 的文件中。 普通查询日志按以上办法设置以后,你可以通过使用以下命令来显示普通日志:tail -f /var/log/mysql/mysql.log 备注:如果你没有配置普通日志文件,MySql 将把普通日志保存在数据目录(通常是 /var/lib/mysql)下的一个名为 {host_name}.log 的文件中。 慢查询日志按以上办法设置以后,你可以通过使用以下命令来显示慢查询日志:tail -f /var/log/mysql/mysql-slow.log 备注:如果你没有配置慢查询日志文件,MySql 将把普通日志保存在数据目录(通常是 /var/lib/mysql)下的一个名为 {host_name}-slow.log 的文件中。 循环日志别忘了滚动日志,否则的话日志文件可能会变得很庞大。在 Debian(以及 Debian 派生系列诸如 Ubuntu 等)系统,MySql 初始安装之后,循环日志就已经使用了 logrotate: nano /etc/logrotate.d/mysql-server 对于其他 Linux 发行版,可能需要做一些改动:
检验服务器配置使用 show variables like '%log%'; 来检查服务器和日志文件相关的变量: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) 服务器变量相关官方文档参考 http://dev./doc/refman/5.7/en/server-options.html。 何时启用日志MySql 默认安装的话,所有的日志文件都不会被启用的(除了 Windows 平台上的 error 日志)。Debian 上安装默认将 error 日志发给 syslog。实际上,在很多情况下日志文件都可以提供关键问题的解决办法:
示例以下是一个 MySql 普通日志的示例: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./blog/how-and-when-to-enable-mysql-logs。 |
|