时间：2021-07-01 10:21:17 帮助过：7人阅读
根据 /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
以上方法要求服务重启才能生效：
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 日志：
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.mysql.com/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
开启mysql日志
标签：数位 conf like upd automatic 检验 else only mysql