Knowledgebase

In this short tutorial we will learn how to configure the following:

1. Enable general log in mysql
   

   For general log activation we have 2 options available:

   • saving the logs into a file

   For saving the logs into a file please add below record to the /etc/my.cnf file under [mysqld] section.

   general_log_file = /var/log/mysql_general_log.log
   general_log = 1

   Make sure you create the log file /var/log/mysql_general_log.log (you can change this to your own naming) and also change ownership of the file to mysql.
   This is required so that the mysql server can write to the file later on:

   chown mysql.mysql /var/log/mysql_general_log.log

   After this is done, restart the database server using one of the following commands (depending on your mysql and OS version).

   
   service mysqld restart
   service mysql restart
   service mariadb restart
   
   
   

   • saving the logs into a table

   1. First, check if you already have the general_log existing in the mysql database.

   If not, you have to create them, note that you have to create in the mysql database.

   • To create the general_log table:

     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'
   

   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.

   2. Enable Query logging on the database

   SET global general_log = 1;
   SET global log_output = 'table';
   

   3. Now you can view the log by running this query:

   SELECT
       *
   FROM
       mysql.general_log;
   

   4. If you want to disable query logging on the database, run this query:

   SET global general_log = 0;
   

                

2. Enable slow query log in mysql
   

   For slow query log activation we have 2 options available:

   • saving the logs into a file

   For saving the logs into a file please add below record to the /etc/my.cnf file under [mysqld] section.

   log-slow-queries = /var/log/mysql-slow.log
   

   Make sure you create the log file /var/log/mysql-slow.log (you can change this to your own naming) and also change ownership of the file to mysql.
   This is required so that the mysql server can write to the file later on:

   chown mysql.mysql /var/log/mysql-slow.log

   After this is done, restart the database server using one of the following commands (depending on your mysql and OS version).

   
   service mysqld restart
   service mysql restart
   service mariadb restart
   
   
   

   • saving the logs into a table

   1. First, check if you already have the general_log existing in the mysql database.

   If not, you have to create them, note that you have to create in the mysql database.

   • To create the slow_log table:

   • And the slow_log table

     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'
   

   The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at leastmin_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.

    

3. Enable log rotation and archiving of the mysql logs

Configure these files:
/etc/logrotate.d/mysql_general with below content:

/var/log/mysql_general_log.log {
# create 600 mysql mysql
notifempty
daily
rotate 33
missingok
compress
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin && \
/usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin flush-logs
fi
endscript
}

/etc/logrotate.d/mysql_slow with below content:

/var/log/mysql-slow.log {
# create 600 mysql mysql
notifempty
daily
rotate 33
missingok
compress
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin && \
/usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin flush-logs
fi
endscript
}

You can tweak the log rotate for your needs. For example the rotation is setup to take place daily and it is configured to keep 33 daily archives. Older archives are overwritten.

Test configuration using this command:

/usr/sbin/logrotate -d /etc/logrotate.conf