MySQL: Write All Executed Queries Into a Log File

Submitted by Peter Majmesku on Tue, 05/23/2017 - 13:54
  1. Open the MySQL config file, which is located at /etc/mysql/my.cnf on Linux systems.
  2. Then write the following lines at the end of that file.

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

Restart your MySQL server (e.g. by executing "sudo service mysql restart" on Ubuntu Linux systems). Now you can follow all new queries as they are executed by this command:

tail -f /var/log/mysql/general_mysql.log

You can leave this program in command line by pressing CTRL+C.

For disabling, just set "general_log" to "off" or comment the out the previous lines in /etc/mysql/my.cnf by writing the # character at the beginng of the lines, save and restart the MySQL server again.

Update: Alternative setting by "general_log" variable

Recently the approach from above was not possible on my MariaDB server in Linux Mint 18.1. The log file was just not written. Even if I have created it. Then I could enable the general_log by variable. For this login into your MariaDB/MySQL server via command line. Then enable the general_log:

SET GLOBAL general_log = On;

Check all your variables which have something to do with "log" by this command:

show variables like '%log%';

The filename of the general_log is stored in that variable: "general_log_file". The default general log file location on Ubuntu/Debian based Linux systems (like Linux Mint) is: 

 /var/log/mysql/general_mysql.log

These commands can help you, to see the setting quickly:

peter@computer ~/Dev/ $ mysql -uroot -ppassword -e "show variables like '%log%';" | grep general_log
mysql: [Warning] Using a password on the command line interface can be insecure.
general_log     ON
general_log_file        /var/log/mysql/general_mysql.log
peter@computer ~/Dev/ $ 
 

Tags

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.