Mysql Query Log To File - Debian 9.md

Configure mysql to log query on file

Change the file /etc/mysql/conf.d/mysql.cnf (require elevated privileges) adding

[mysqld]
general_log = on
general_log_file=/var/log/query

If you change the filename, keep it consistent in the following steps !

Create initial file and give right ownership and permissions

su
cd /var/log
touch query
chown mysql:adm query
chmod 640 query

Note for Ubuntu users: verify mysql:adm, because on your system probably it should be mysql:mysql

Create logrotate config file

cd /etc/logrotate.d/
touch mysql-query-log 
nano mysql-query-log

Copy what follows (ensure file name in the first line is right !)

/var/log/query {
    missingok
    daily
    compress
    delaycompress
    create 640 mysql adm
    dateext
    nomail
    rotate 30
    sharedscripts
    postrotate
    # run if mysqld is running
    if test -n "`ps acx|grep mysqld`" ; then
      /usr/bin/mysqladmin flush-logs
    fi
    endscript
}

Note, again, for Ubuntu users: verify mysql:adm, because on your system probably it should be mysql:mysql

Allow linux root user to flush mysql log

    cd /root
    nano .my.cnf

Paste this

[mysqladmin]
user     = root
password = changeme

Remember to change changeme into your mysql's root user password; this is not the root linux user, is mysql's root user.

Test it

mysqladmin flush-logs

Test log file rotation

logrotate -v -f /etc/logrotate.d/mysql-query-log

In case of error about file already exists, delete the file query-<date> and retry

It's ok if at the end you see something like this

renaming /var/log/query to /var/log/query-20191017
creating new /var/log/query mode = 0640 uid = 107 gid = 4
running postrotate script

Remember to delete the new file just created from logrotate

rm /var/log/query-20191017

Note the date from the previous log and use in the rm command

Enjoy !