Macrotone Blogs

Macrotone blogs upon Joomla, our products and other matters.
Categories
Tags
Archives
Calendar
Categories:   All Categories
Suggested keywords
x
Font size: + –
  Print
2 minutes reading time (445 words)

MySQL logs and QNAP systems

4772 Hits

 

All (most) company development and testing making use of MySQL databases locates the databases upon QNAP systems.  The default MySQL installing being by the QNAP installation itself.  Recently there has been some interest in the space being consumed.  Looking at the database settings in the /etc/my.cnf file it can be seen that it is not optimum.
The main areas of interest are the log files.  It is noted that the binlog format is set to STATEMENT which is why the messages about log format have been seen.  [It is not known how many systems are set to the default so it is sensible perhaps to leave the setting alone.  In this way we have a 'worse' case scenario for when we distribute software and have to include session settings to enable the software to install and/or function.]

More importantly the settings for the purging of the binary logs was set to zero.  This is the cause of the space consumption.

Looking at the MySQL administration manual we can read the following:

PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

The binary log is a set of files that contain information about data modifications made by the MySQL server. The log consists of a set of binary log files, plus an index file.

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. BINARY and MASTER are synonyms. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.

This statement has no effect if the server was not started with the --log-bin option to enable binary logging.

Examples:

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

The BEFORE variant's datetime_expr argument should evaluate to a DATETIME value (a value in 'YYYY-MM-DD hh:mm:ss' format).

In our situation we run a command as follows in the phpAdmin tool:

purge binary logs before '2011-11-25 00:00:00';

The manual also states:

You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days.  If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.

In our situation because we are not using replication of any form in the phpAdmin tool we issued the following command:

set global expire_logs_days=1;

The number of days for automatic binary log file removal. The default is 0, which means no automatic removal.  Possible removals happen at startup and when the binary log is flushed.

Tags:
QNAP MySQL logs
×
Stay Informed

When you subscribe to the blog, we will send you an e-mail when there are new updates on the site so you wouldn't miss them.

MySQL versions and binlog_format settings
PHP calling MySQL functions and procedures

About the author

Geoffrey Chapman

Geoffrey Chapman

Subscribe to updates from author Unsubscribe to updates from author Geoffrey Chapman

An IT professional with a wide experience of IT systems, specialising in Database Management and Security.

Author's recent posts
More posts from author
 
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries