Is it possible for me to turn on audit logging on my mysql database?
I basically want to monitor all queries for an hour, and dump the log to a file.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
(Note: For mysql-5.6+ this won’t work. There’s a solution that applies to mysql-5.6+ if you scroll down or click here.)
If you don’t want or cannot restart the MySQL server you can proceed like this on your running server:
- Create your log tables on the
mysqldatabase
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;
Method 2
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;
Method 3
UPDATE: NO LONGER VALID FOR MYSQL 5.6+
Start mysql with the –log option:
mysqld --log=log_file_name
or place the following in your my.cnf file:
log = log_file_name
Either one will log all queries to log_file_name.
You can also log only slow queries using the --log-slow-queries option instead of --log. By default, queries that take 10 seconds or longer are considered slow, you can change this by setting long_query_time to the number of seconds a query must take to execute before being logged.
Method 4
Top answer doesn’t work in mysql 5.6+. Use this instead:
[mysqld] general_log = on general_log_file=/usr/log/general.log
in your my.cnf / my.ini file
Ubuntu/Debian: /etc/mysql/my.cnf
Windows: c:ProgramDataMySQLMySQL Server 5.x
wamp: c:wampbinmysqlmysqlx.y.zmy.ini
xampp: c:xamppmysqlbinmy.ini.
Method 5
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
If there’s too much queries, truncate the table:
TRUNCATE table mysql.general_log;
Method 6
Quick way to enable MySQL General Query Log without restarting.
mysql> SET GLOBAL general_log_file = '/var/www/nanhe/log/all.log'; mysql> SET GLOBAL general_log = 'ON';
I have installed mysql through homebrew, mysql version : mysql Ver 14.14 Distrib 5.7.15, for osx10.11 (x86_64) using EditLine wrapper
Method 7
For the record, general_log and slow_log were introduced in 5.1.6:
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 general_log and slow_log tables in the mysql
database
Method 8
OS / mysql version:
$ 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:
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
Result:
$ sudo tail -f /var/log/mysql/mysqld_general.log
181210 9:41:04 21 Connect <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="6c1e0303182c00030f0d0004031f18">[email protected]</a> 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_empAll methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0