After performing certain operations, or before performing a backup, you may want to flush the contents in the logs. Flushing a log means that its files are closed and reopened, and all the buffered information is written to the file during the process. For file-based logs, the FLUSH LOGS statement can be used. To flush all logs, simply run the following command:

FLUSH LOGS;

The following flavors are available to selectively flush only one log:

FLUSH ERROR LOGS; -- error log
FLUSH GENERAL LOGS; -- general query log
FLUSH SLOW LOGS; -- slow query log
FLUSH BINARY LOGS; -- binary log
FLUSH ENGINE LOGS; -- storage engines logs
FLUSH RELAY LOGS; -- replication slaves log

By default, these statements are replicated by the slaves, if any. To execute them only on the masters, the LOCAL keyword (or NO_WRITE_TO_BINLOG, which is a synonym) can be used, as shown in the following code snippet:

FLUSH NO_WRITE_TO_BINLOG LOGS;
FLUSH LOCAL ERROR LOGS;

Note that this has no effect on the general_log and slow_log tables, if they exist. The flushing of all tables can be done using the FLUSH TABLES command, which closes the data files and reopens them. This forces the cached changes to be applied to the files. However, there is no way to specifically flush only the logs or the tables—all tables in all databases will be flushed, as shown in the following code snippets:

FLUSH TABLES;
FLUSH LOCAL TABLES;

The mysqladmin utility can also be used to flush logs or tables, shown as follows:

mysqladmin flush-logs
mysqladmin flush-tables

MariaDB does not automatically rotate the logs.

The only exception is the binary log, which rotates when it reaches a certain size. Also, when the binary log is flushed, a new file is created automatically. The binary log rotation will be discussed in Chapter 8, Backup and Disaster Recovery.

The rotation of all other logs must be implemented by the user, which can be done by renaming the current file periodically or when the files reach a certain size. Red Hat Enterprise Linux and the derived Linux distributions provide a tool that can be used to perform the rotation automatically.

Let's see an example of how to rename the general log files from a Linux shell.

First, you may want to obtain a list of the error files, their size, and some more related data. Suppose that the current file is called maria.log.01, and the older files have different numeric suffixes (such as 02 and 03), shown as follows:

root@this:/usr/local/mysql/data# ls | grep "maria.log."
maria.log.01
maria.log.02
maria.log.03

This means that one of the MariaDB configuration files contains the following options:

general_log=1
general_log_file="query"

Now, you want to rename the files. If the server is running, first you need to disable the general log using the following SQL statement:

MariaDB [(none)]> SET GLOBAL general_log = 0;
Query OK, 0 rows affected (0.07 sec)

You can delete the last file and rename the other files, shown as follows:

root@this:/usr/local/mysql/data# rm maria.log.03
root@this:/usr/local/mysql/data# mv maria.log.02 maria.log.03
root@this:/usr/local/mysql/data# mv maria.log.01 maria.log.02

To re-enable the general query log, use the command shown as follows:

MariaDB [(none)]> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.00 sec)

Now let's flush the logs as follows:

root@this:/usr/local/mysql/data# ../bin/mysqladmin flush-logs -uroot -p

Since the maria.log.01 file does not exist anymore, the server recreates this file. In order to be sure, let's check whether everything worked fine using the following command:

root@this:/usr/local/mysql/data# ls | grep "maria.log."
maria.log.01
maria.log.02
maria.log.03

In Windows, the command to delete a file is del, the command to rename a file is rename, and the command to get a list of files is dir. So, the correct sequence of the commands to be used is shown as follows:

del maria.log.03
rename maria.log.02 maria.log.03
rename maria.log.01 maria.log.02
dir maria.log.*

Of course, we will never do this manually on a production server. Instead, you need a well-tested script that does this automatically.

The Red Hat Linux distribution has a script that does this. It is called mysql-log-rotate.

As mentioned in the previous section, it is possible to rotate a table-based general query log, as well as a table-based slow query log. In order to do this, we will create a stored procedure. This procedure can be called by an event, which is described at the end of this section.

Let's look at the following code and discuss how it works:

CREATE PROCEDURE '_'.'rotate_general_log'()
BEGIN
    DECLARE old_general_log TINYINT DEFAULT @@global.general_log;
    SET @@global.general_log = 0;
    DROP TABLE 'mysql'.'general_log03';
    RENAME TABLE
        'mysql'.'general_log02' TO 'mysql'.'general_log03',
        'mysql'.'general_log' TO 'mysql'.'general_log02';
    CREATE TABLE 'mysql'.'general_log' LIKE 'mysql'.'general_log02';
    SET @@global.general_log = old_general_log;
END;

Since all procedures must belong to a database, we have a commodity database called '_' for general purpose routines. A short name allows you to call the procedure quickly from the command line.

The first action the event takes is to delete the oldest log table. Then, with a RENAME TABLE statement, log tables are renamed: general_log becomes general_log02 and general_log02 becomes general_log03. The RENAME TABLE statement is always an atomic operation, and so, if one renaming fails, all rename operations will fail. In this case, the procedure will recreate the general_log table using the same definition as general_log02.

The general_log table is disabled at the beginning of this procedure. However, it is possible that it was already disabled, so the value of the general_log variable is copied to a temporary variable and is restored at the end of the procedure.

This procedure is just a very basic example. A good procedure is beyond the purpose of this book. However, it should be flexible and error-proof. To improve it, the reader could implement the following ideas:

Writing this code as a procedure is useful for at least two reasons: it is easier to debug and it can be called manually at any time. However, you also want the rotation to happen at regular time intervals, and so you need to write an event that calls the routine, shown as follows:

CREATE EVENT 'event_db'.'rotate_general_log'
    ON SCHEDULE
        EVERY 1 WEEK
        STARTS '2014-01-05 00:00:00'
    COMMENT 'Rotates general_log'
DO BEGIN
    CALL '_'.'rotate_general_log'();
END;

The event should be executed when none of the server's workload is too high. In the previous example, it is activated at midnight, between Sunday and Monday.