我能否在我的 MySQL 数据库上开启审计日志记录?
我想要监控所有查询一个小时,并将日志转储到文件中。
我能否在我的 MySQL 数据库上开启审计日志记录?
我想要监控所有查询一个小时,并将日志转储到文件中。
除了我在这里遇到的情况,运行以下命令是将查询转储到日志文件而无需重新启动的最简单方法。
SET global log_output = 'FILE';
SET global general_log_file='/Applications/MAMP/logs/mysql_general.log';
SET global general_log = 1;
可以关闭
SET global general_log = 0;
GRANT SUPER ON *.* TO user1@localhost
。 - RobM如果您不想或不能重启MySQL服务器,则可以在运行中的服务器上按照以下步骤进行:
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'
SET global general_log = 1;
SET global log_output = 'table';
select * from mysql.general_log
SET global general_log = 0;
mysql
数据库上执行CREATE TABLE
命令,而不是在任何用户创建的数据库上执行。也许可以更新SQL语句以反映这一点。 - Robert RossmannSELECT * FROM mysql.general_log order by (event_time) desc
。只是说一下。:-) - vinrav更新:对于MySQL 5.6+已不再有效。
使用--log选项启动mysql:
mysqld --log=log_file_name
或者将以下内容放置在您的my.cnf
文件中:
log = log_file_name
你可以使用--log-slow-queries
选项来仅记录慢查询,而不是使用--log
选项记录所有查询到log_file_name
中。默认情况下,执行时间超过10秒的查询被认为是慢查询,你可以通过将long_query_time
设置为查询必须执行的秒数来更改此设置。
在MySQL 5.6+中,顶部答案无效。请改用以下方法:
[mysqld]
general_log = on
general_log_file=/usr/log/general.log
在你的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.
phpMyAdmin
而没有访问服务器目录树时,如何在MySQL 5.6及更高版本中记录所有查询? - Vicky Devsudo systemctl restart mysqld
。 - M at启用表的日志记录
mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL log_output = 'table';
通过选择查询查看日志
SELECT * FROM mysql.general_log
如果查询太多,截断表格:TRUNCATE table mysql.general_log;
快速启用MySQL常规查询日志而无需重新启动。
mysql> SET GLOBAL general_log_file = '/var/www/nanhe/log/all.log';
mysql> SET GLOBAL general_log = 'ON';
我通过Homebrew安装了MySQL,MySQL版本为mysql Ver 14.14 Distrib 5.7.15,适用于osx10.11 (x86_64)的EditLine包装器。
值得一提的是,general_log 和 slow_log 是在5.1.6版本中引入的:
http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html
5.2.1. 选择常规查询和慢查询日志输出目标操作系统/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
添加日志记录(例如,我认为/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
重启 Mysql
结果:
$ 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