MySQL 记录mysql中的所有查询

Log all queries in mysql

提问by public static

Is it possible for me to turn on audit logging on my mysql database?

我可以在我的 mysql 数据库上打开审计日志吗？

I basically want to monitor all queries for an hour, and dump the log to a file.

我基本上想监视一个小时的所有查询，并将日志转储到文件中。

采纳答案by Robert Gamble

Start mysql with the --log option:

使用 --log 选项启动 mysql：

mysqld --log=log_file_name

or place the following in your my.cnffile:

或将以下内容放入您的my.cnf文件中：

log = log_file_name

Either one will log all queries to log_file_name.

任何一个都将所有查询记录到 log_file_name。

You can also log only slow queries using the --log-slow-queriesoption instead of --log. By default, queries that take 10 seconds or longer are considered slow, you can change this by setting long_query_timeto the number of seconds a query must take to execute before being logged.

您还可以使用该--log-slow-queries选项而不是--log. 默认情况下，需要 10 秒或更长时间的查询被认为是缓慢的，您可以通过设置long_query_time查询在被记录之前必须执行的秒数来更改它。

回答by Alexandre Marcondes

(Note: For mysql-5.6+ this won't work. There's a solution that applies to mysql-5.6+ if you scroll downor click here.)

（注意：对于 mysql-5.6+，这不起作用。如果向下滚动或单击此处，有一个适用于 mysql-5.6+ 的解决方案。）

If you don't want or cannot restart the MySQL server you can proceed like this on your running server:

如果您不想或无法重新启动 MySQL 服务器，您可以在正在运行的服务器上进行如下操作：

• Create your log tables on the mysqldatabase

• 在mysql数据库上创建日志表

  CREATE TABLE `slow_log` (
   `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `query_time` time NOT NULL,
   `lock_time` time NOT NULL,
   `rows_sent` int(11) NOT NULL,
   `rows_examined` int(11) NOT NULL,
   `db` varchar(512) NOT NULL,
   `last_insert_id` int(11) NOT NULL,
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

  CREATE TABLE `general_log` (
   `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
                          ON UPDATE CURRENT_TIMESTAMP,
   `user_host` mediumtext NOT NULL,
   `thread_id` bigint(21) unsigned NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `command_type` varchar(64) NOT NULL,
   `argument` mediumtext NOT NULL
  ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

• Enable Query logging on the database

• 在数据库上启用查询日志记录

SET global general_log = 1;
SET global log_output = 'table';

• View the log

• 查看日志

select * from mysql.general_log

• Disable Query logging on the database

• 禁用数据库上的查询日志记录

SET global general_log = 0;

回答by Ram

Besides what I came across here, running the following was the simplest way to dump queries to a log file without restarting

除了我在这里遇到的情况之外，运行以下命令是无需重新启动即可将查询转储到日志文件的最简单方法

SET global log_output = 'FILE';
SET global general_log_file='/Applications/MAMP/logs/mysql_general.log';
SET global general_log = 1;

can be turned off with

可以关闭

SET global general_log = 0;

回答by alexeydemin

Top answer doesn't work in mysql 5.6+. Use this instead:

最佳答案在 mysql 5.6+ 中不起作用。改用这个：

[mysqld]
general_log = on
general_log_file=/usr/log/general.log

in your my.cnf / my.ini file

在您的 my.cnf / my.ini 文件中

Ubuntu/Debian: /etc/mysql/my.cnf
Windows: c:\ProgramData\MySQL\MySQL Server 5.x
wamp: c:\wamp\bin\mysql\mysqlx.y.z\my.ini
xampp: c:\xampp\mysql\bin\my.ini.

Ubuntu/Debian: /etc/mysql/my.cnf
Windows: c:\ProgramData\MySQL\MySQL Server 5.x
wamp: c:\wamp\bin\mysql\mysqlx.yz\my.ini
xampp: c:\xampp \mysql\bin\my.ini。

回答by Vipin Yadav

Enable the log for table

启用表的日志

mysql> SET GLOBAL general_log = 'ON';
mysql> SET global log_output = 'table';

View log by select query

通过选择查询查看日志

select * from mysql.general_log

回答by Nanhe Kumar

Quick way to enable MySQL General Query Log without restarting.

无需重新启动即可启用 MySQL 常规查询日志的快速方法。

mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL general_log_file = '/var/www/nanhe/log/all.log';

I have installed mysql through homebrew, mysql version : mysql Ver 14.14 Distrib 5.7.15, for osx10.11 (x86_64) using EditLine wrapper

我已经通过自制软件安装了 mysql，mysql 版本：mysql Ver 14.14 Distrib 5.7.15，用于 osx10.11 (x86_64) 使用 EditLine 包装器

回答by Marcello Romani

For the record, general_log and slow_log were introduced in 5.1.6:

为了记录，general_log和slow_log是在5.1.6中引入的：

http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html

http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html

5.2.1. Selecting General Query and Slow Query Log Output Destinations

As of MySQL 5.1.6, MySQL Server provides flexible control over the destination of output to the general query log and the slow query log, if those logs are enabled. Possible destinations for log entries are log files or the the general_log and slow_log tables in the mysql database

5.2.1. 选择通用查询和慢查询日志输出目的地

从 MySQL 5.1.6 开始，如果启用了常规查询日志和慢速查询日志，MySQL Server 可以灵活控制输出目标。日志条目的可能目的地是日志文件或 mysql 数据库中的 general_log 和 slow_log 表

回答by Lea de Groot

You should be aware that mysql logging on really impacts performance, but it can be a wise thing to do.

您应该知道 mysql 登录确实会影响性能，但这样做可能是明智之举。

I usually leave it on on the dev server (except when it drives us insane :))

我通常将它留在开发服务器上（除非它让我们发疯:)）

回答by Raphvanns

OS / mysql version:

操作系统/mysql 版本：

$ uname -a
Darwin Raphaels-MacBook-Pro.local 15.6.0 Darwin Kernel Version 15.6.0: Thu Jun 21 20:07:40 PDT 2018; root:xnu-3248.73.11~1/RELEASE_X86_64 x86_64

$ mysql --version
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.6.23, for osx10.8 (x86_64) using  EditLine wrapper

Adding logging (example, I don't think /var/log/...is the best path on Mac OS but that worked:

添加日志记录（例如，我认为这不是/var/log/...Mac OS 上的最佳路径，但有效：

sudo vi ./usr/local/mysql-5.6.23-osx10.8-x86_64/my.cnf

[mysqld]
general_log = on
general_log_file=/var/log/mysql/mysqld_general.log

Restarted Mysql

重启Mysql

Result:

结果：

$ sudo tail -f /var/log/mysql/mysqld_general.log
181210  9:41:04    21 Connect   root@localhost on employees
           21 Query /* mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
           21 Query SET NAMES latin1
           21 Query SET character_set_results = NULL
           21 Query SET autocommit=1
           21 Query SELECT USER()
           21 Query SELECT USER()
181210  9:41:10    21 Query show tables
181210  9:41:25    21 Query select count(*) from current_dept_emp

回答by Gonzalo Gallotti

In case using AWS RDS MYSQL, step by step guide here.

如果使用 AWS RDS MYSQL，请在此处提供分步指南。

When set as 'file' output you can view the log directly from AWS RDS "Log" Console.

当设置为“文件”输出时，您可以直接从 AWS RDS“日志”控制台查看日志。

AWS RDS MYSQL Logging

AWS RDS MYSQL 日志记录