[MySQL]: General/Error log as a solution for user audit log

July 13, 2015 · by · in MySQL

MySQL General Log :

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
To set up MySQL General log
=============================

[ root @ vm-5cc5-bad6 : ~ ] grep ‘general’ /etc/my.cnf
general-log
general-log-file=/var/log/mysql_general.log
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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>
mysql> SET GLOBAL general_log = ‘ON’;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show variables like ‘general%’;
+——————+—————————-+
| Variable_name | Value |
+——————+—————————-+
| general_log | ON |
| general_log_file | /var/log/mysql_general.log |
+——————+—————————-+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> ^DBye
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] cat /var/log/mysql_general.log
/usr/sbin/mysqld, Version: 5.6.25-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
150713 4:12:31 6 Query show variables like ‘general%’
150713 4:12:36 6 Quit
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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>
mysql>
mysql> grant all privileges on *.* to ‘testuser’@’%’ identified by ‘test123’;
Query OK, 0 rows affected (0.00 sec)
mysql> ^DBye
To test unauthorized access :
=============================

[ root @ my-hostname : ~ ] mysql -u testuser -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘testuser’@’localhost’ (using password: YES)
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] mysql -u testuser -p”test123″ -h localhost
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user ‘testuser’@’localhost’ (using password: YES)
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
Authorized access :
=================

[ root @ my-hostname : ~ ] mysql -u testuser -p”test123″ -h my-hostname
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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>
mysql>
mysql> ^DBye
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] cat /var/log/mysql_general.log
/usr/sbin/mysqld, Version: 5.6.25-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
150713 4:12:31 6 Query show variables like ‘general%’
150713 4:12:36 6 Quit
150713 4:12:49 7 Connect root@localhost on
7 Query select @@version_comment limit 1
150713 4:13:27 7 Query GRANT ALL PRIVILEGES ON *.* TO ‘testuser’@’%’ IDENTIFIED BY PASSWORD ‘*676243218923905CF94CB52A3C9D3EB30CE8E20D’
150713 4:13:30 7 Quit
150713 4:13:39 8 Connect testuser@localhost as on
8 Connect Access denied for user ‘testuser’@’localhost’ (using password: YES)
150713 4:35:50 9 Connect root@localhost on
9 Query select @@version_comment limit 1
150713 4:35:57 9 Query flush privileges
150713 4:36:02 9 Query flush tables
150713 4:36:03 9 Quit
150713 4:36:08 10 Connect testuser@localhost as on
10 Connect Access denied for user ‘testuser’@’localhost’ (using password: YES)
150713 4:36:21 11 Connect testuser@localhost as on
11 Connect Access denied for user ‘testuser’@’localhost’ (using password: YES)
150713 4:36:28 12 Connect testuser@my-hostname.nam.nsroot.net on
12 Query select @@version_comment limit 1
150713 4:36:31 12 Quit
[ root @ my-hostname : ~ ]

2nd Option : Error Log:
======================

[ root @ my-hostname : ~ ] mysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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>
mysql>
mysql> show variables like ‘%error%’;
+———————+—————————+
| Variable_name | Value |
+———————+—————————+
| binlog_error_action | IGNORE_ERROR |
| error_count | 0 |
| log_error | /var/lib/mysql/mysqld.log |
| max_connect_errors | 100 |
| max_error_count | 64 |
| slave_skip_errors | OFF |
+———————+—————————+
6 rows in set (0.00 sec)
mysql>
mysql>
mysql> show variables like ‘log_warnings’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| log_warnings | 1 |
+—————+——-+
1 row in set (0.00 sec)

mysql> set GLOBAL log_warnings=2;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> ^DBye
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] cat /var/lib/mysql/mysqld.log
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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>
mysql>
mysql> show grants;
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION |
| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION |
+———————————————————————+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> grant all on *.* to ‘newtestuser’@’%’ identified by ‘test123’;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> ^DBye
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] mysql -u newtestuser -p”test123″ -h my-hostname
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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> exit
Bye
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] mysql -u newtestuser -p”test123″
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user ‘newtestuser’@’localhost’ (using password: YES)
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] mysql -u newtestuser -p”test123_123232″ -h my-hostname
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user ‘newtestuser’@’my-hostname.nam.nsroot.net’ (using password: YES)
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] cat /var/lib/mysql/mysqld.log
2015-07-13 04:10:52 10059 [ERROR] Could not open /var/log/mysql_general.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
2015-07-13 05:45:34 10059 [Warning] Access denied for user ‘newtestuser’@’localhost’ (using password: YES)
2015-07-13 05:45:44 10059 [Warning] Access denied for user ‘newtestuser’@’my-hostname.nam.nsroot.net’ (using password: YES)
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ] tail -10 /var/log/mysql_general.log
150713 5:44:19 14 Query show grants
150713 5:45:06 14 Query GRANT ALL PRIVILEGES ON *.* TO ‘newtestuser’@’%’ IDENTIFIED BY PASSWORD ‘*676243218923905CF94CB52A3C9D3EB30CE8E20D’
150713 5:45:09 14 Quit
150713 5:45:24 15 Connect newtestuser@my-hostname.nam.nsroot.net on
15 Query select @@version_comment limit 1
150713 5:45:27 15 Quit
150713 5:45:34 16 Connect newtestuser@localhost as on
16 Connect Access denied for user ‘newtestuser’@’localhost’ (using password: YES)
150713 5:45:44 17 Connect newtestuser@my-hostname.nam.nsroot.net on
17 Connect Access denied for user ‘newtestuser’@’my-hostname.nam.nsroot.net’ (using password: YES)
[ root @ my-hostname : ~ ]
[ root @ my-hostname : ~ ]
More details at : https://dev.mysql.com/doc/refman/5.1/en/query-log.html

  • Ask Question
    • Tags: