Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.5Mb
PDF (A4) - 38.6Mb
PDF (RPM) - 33.3Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 134.2Kb
Man Pages (Zip) - 190.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Server Log Maintenance

5.4.7 Server Log Maintenance

As described in Section 5.4, “MySQL Server Logs”, MySQL Server can create several different log files to help you see what activity is taking place. However, you must clean up these files regularly to ensure that the logs do not take up too much disk space.

When using MySQL with logging enabled, you may want to back up and remove old log files from time to time and tell MySQL to start logging to new files. See Section 7.2, “Database Backup Methods”.

On a Linux (Red Hat) installation, you can use the mysql-log-rotate script for this. If you installed MySQL from an RPM distribution, this script should have been installed automatically. Be careful with this script if you are using the binary log for replication. You should not remove binary logs until you are certain that their contents have been processed by all slaves.

On other systems, you must install a short script yourself that you start from cron (or its equivalent) for handling log files.

Binary log files are automatically removed after the server's binary log expiration period. Removal of the files can take place at startup and when the binary log is flushed. The default binary log expiration period is 30 days. You can specify an alternative expiration period using the binlog_expire_logs_seconds system variable. If you are using replication, you should specify an expiration period that is no lower than the maximum amount of time your slaves might lag behind the master. To remove binary logs on demand, use the PURGE BINARY LOGS statement (see Section 13.4.1.1, “PURGE BINARY LOGS Syntax”).

You can force MySQL to start using new log files by flushing the logs. Log flushing occurs when you issue a FLUSH LOGS statement or execute a mysqladmin flush-logs, mysqladmin refresh, mysqldump --flush-logs, or mysqldump --master-data command. See Section 13.7.7.3, “FLUSH Syntax”, Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”, and Section 4.5.4, “mysqldump — A Database Backup Program”. In addition, the binary log is flushed when its size reaches the value of the max_binlog_size system variable.

FLUSH LOGS supports optional modifiers to enable selective flushing of individual logs (for example, FLUSH BINARY LOGS).

A log-flushing operation does the following:

  • If general query logging or slow query logging to a log file is enabled, the server closes and reopens the general query log file or slow query log file.

  • If binary logging is enabled, the server closes the current binary log file and opens a new log file with the next sequence number.

  • If the server was started with the --log-error option to cause the error log to be written to a file, the server closes and reopens the log file.

The server creates a new binary log file when you flush the logs. However, it just closes and reopens the general and slow query log files. To cause new files to be created on Unix, rename the current log files before flushing them. At flush time, the server opens new log files with the original names. For example, if the general and slow query log files are named mysql.log and mysql-slow.log, you can use a series of commands like this:

shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mv mysql-slow.log mysql-slow.old
shell> mysqladmin flush-logs

On Windows, use rename rather than mv.

At this point, you can make a backup of mysql.old and mysql-slow.old and then remove them from disk.

A similar strategy can be used to back up the error log file, if there is one.

You can rename the general query log or slow query log at runtime by disabling the log:

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

With the logs disabled, rename the log files externally; for example, from the command line. Then enable the logs again:

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

This method works on any platform and does not require a server restart.

Note

For the server to recreate a given log file after you have renamed the file externally, the file location must be writable by the server. This may not always be the case. For example, on Linux, the server might write the error log as /var/log/mysqld.log, where /var/log is owned by root and not writable by mysqld. In this case, the log-flushing operation will fail to create a new log file.

To handle this situation, you must manually create the new log file with the proper ownershiop after renaming the original log file. For example, execute these commands as root:

shell> mv /var/log/mysqld.log /var/log/mysqld.log.old
shell> install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Virender Bhanot on April 6, 2011
A PHP script to purge the master bin logs based on the position of slave. Can also be used to send alerts if slave is found to have a problem as mentioned below. Uncomment the line mentioned to actually purge logs.


<?
# Use as you like. No Warranties please. Use at your own risk.
# The script purges (deletes) the old log files based on the current file on which slave is acting.
# The script was tested by running the same on slave server. 
# It additionally sends an email/ sms if some of the parameters on slave are not found as required.

# define the number of logs to keep
define('KEEPNUM'5);
define('LINEBRK'"\n"); //make it <br> if running on browser

# connectivity settings for the slave (localhost)
define('HOST''localhost');
define('USER''user');
define('PASS''pass');

# connectivity settings for the master (remote)
define('HOSTM''x.x.x.x');
define('USERM''user');
define('PASSM''pass');

function 
send_email_sms($text

   
//Put here your code to send email and sms
   //return $retval; 


if(!
$dbh mysql_connect(HOSTUSERPASS)) die(mysql_error());
$query 'show slave status;';
if(!
$rs mysql_query($query$dbh)) die(mysql_error());
$row mysql_fetch_array($rs);

if (
strcmp($row['Slave_IO_Running'],"Yes") != 0send_email_sms("Alert. Slave IO not running.");
if (
strcmp($row['Slave_SQL_Running'],"Yes") != 0send_email_sms("Alert. Slave IO not running.");
if (
strcmp($row['Last_Error'],"") != 0send_email_sms("Alert. Error on Slave. ".$row['Last_Error']);

$x $row['Master_Log_File'];
echo 
"Current Master file on which Slave is acting: ".$x.LINEBRK;

if(!
$dbhm mysql_connect(HOSTMUSERMPASSM)) die(mysql_error());
$query1 'show master logs;';
if(!
$rs1 mysql_query($query1$dbhm)) die(mysql_error());
$lognames = array();

while(
$row1 mysql_fetch_array($rs1))
  
$lognames[] = $row1[0];

$marker array_search($x,$lognames);
$delmarker $marker KEEPNUM;

if(
$delmarker 2) {echo "Not enough logs to keep. Exiting..".LINEBRK;exit;}

echo 
"We are going to purge logs upto (not including) $lognames[$delmarker]".LINEBRK;

$del_query "PURGE MASTER LOGS TO '$lognames[$delmarker]'";
# Uncomment the following line to actually purge (i.e. delete) the logs
//if(!$rs2 = mysql_query($del_query, $dbhm)) die(mysql_error());
echo "The Master Server MySQL binary logs have been purged. The oldest log now available on the master is $lognames[$delmarker].".LINEBRK;
?> 

  Posted by Matthieu Bienvenüe on October 12, 2012
Here is an upgraded version of the Virender Bhanot ones that could handle multiple slaves. This script could run on CLI or web browser.

#!/usr/bin/php

<?
// purge_master_logs.php - MB 08/10/2012
//
// Source inspired by Virender Bhanot comment's : http://dev.mysql.com/doc/refman/5.6/en/log-file-maintenance.html
//
// Use as you like. No Warranties please. Use at your own risk.
// The script purges (deletes) the old log files based on the current file on which Slaves are acting.
// It additionally sends an email/ sms if some of the parameters on slave are not found as required.
//
// Modified in order to support multiple slaves. Get the oldest log file used by all slaves in order to use it as the reference.
// The script could be executed on any host (even another one that don't have mysql installed; this is the case for host with only Mysql installed and no PHP).
// Remove nothing if one Slave (or Master) is down or has a problem.
//
// The script assumes that bin log filenames use the default Mysql pattern (e.g. : mysql-bin.000003).
//
// Settings are read from slaves.config and master.config files, one host per line with the following format: host:user:password; comments lines starting with # are ignored.
//
// To display debuging information use ./purge_master_logs.php debug on CLI or purge_master_logs.php?debug on a web browser, by default nothing is displayed in order to avoid cron jobs send e-mails.
//


define('KEEPNUM'5); // define the number of logs to keep
define('SLAVE_CONFIG_FILE''slaves.config');
define('MASTER_CONFIG_FILE''master.config');
//---- Nothing to be changed under this line

function send_email_sms($text)
{
   
//Put here your code to send email and sms
   //return $retval;
   
echo $text LINEBRK;
   exit(
1);
}

if( empty(
$_SERVER['HTTP_USER_AGENT']) ) // used on CLI
{
   
define('LINEBRK'"\n");
   
parse_str(implode('&'array_slice($argv1)), $_GET); // populate $_GET with CLI parameters
}
else
{
   
define('LINEBRK'"<br>\n"); // make it <br> if running on browser
}

if( isset(
$_GET['debug']) )
{
   
$debug true;
}
else
{
   
$debug false;
}

register_shutdown_function('shutdown'$debug); // display nothing (with echo, etc.) when in production (usefull for cron jobs)
ob_start();


//---- Read configs files
//---- Slaves
$slaves_lines file(SLAVE_CONFIG_FILEFILE_IGNORE_NEW_LINES FILE_SKIP_EMPTY_LINES);

if( 
$slaves_lines === false )
{
   
send_email_sms("Cannot read Slaves config file (" SLAVE_CONFIG_FILE ")");
}
$slaves_host = array();
$slaves_users = array();
$slaves_pass = array();
foreach( 
$slaves_lines as $line )
{
   if( 
substr($line0strlen('#')) !== '#' // lines starting with # are comments, ignore it
   
{
      list(
$host$user$password) = explode(':'$line3);
      
$slaves_host[] = $host;
      
$slaves_users[] = $user;
      
$slaves_pass[] = $password;
   }
}
unset(
$slaves_lines);
if( empty(
$slaves_host) || empty($slaves_users) || empty($slaves_pass) )
{
   
send_email_sms("No hosts defined in Slaves config file (" SLAVE_CONFIG_FILE ")");
}
echo 
"Slaves config : host|user|password" LINEBRK;
foreach( 
$slaves_host as $index => $host )
{
   if( empty(
$slaves_pass[$index]) )
   {
      
$password_txt 'NOT DEFINED';
   }
   else
   {
      
$password_txt 'SET BUT NOT DISPLAYED HERE';
   }
   echo 
$host '|' $slaves_users[$index] . '|' $password_txt LINEBRK;
}

//---- Master
$master_lines file(MASTER_CONFIG_FILEFILE_IGNORE_NEW_LINES FILE_SKIP_EMPTY_LINES);

if( 
$master_lines === false )
{
   
send_email_sms("Cannot read Master config file (" MASTER_CONFIG_FILE ")");
}
foreach( 
$master_lines as $line )
{
   if( 
substr($line0strlen('#')) !== '#' // lines starting with # are comments, ignore it
   
{
      list(
$master_host$master_user$master_password) = explode(':'$line3);
      break; 
// only one line of settings
   
}
}
unset(
$master_lines);
if( empty(
$master_host) || empty($master_user) || empty($master_password) )
{
   
send_email_sms("Problem when reading Master config file (" MASTER_CONFIG_FILE "); missing parameter");
}
echo 
"Master config : host|user|password" LINEBRK;
if( empty(
$master_password) )
{
   
$password_txt 'NOT DEFINED';
}
else
{
   
$password_txt 'SET BUT NOT DISPLAYED HERE';
}
echo 
$master_host '|' $master_user '|' $password_txt LINEBRK LINEBRK;


//---- get log files number on each slave
$file_numbers = array();
$min_file_number 999999;
foreach( 
$slaves_host as $index => $host )
{
   if( !
$dbh mysql_connect($host$slaves_users[$index], $slaves_pass[$index]) )
   {
      die(
mysql_error() . LINEBRK "line: " __LINE__ LINEBRK "Host: " $host LINEBRK);
   }
   
$query 'show slave status;';
   if( !
$rs mysql_query($query$dbh) )
   {
      die(
mysql_error() . LINEBRK "line: " __LINE__ LINEBRK "Host: " $host LINEBRK);
   }
   
$row mysql_fetch_array($rs);

   if( 
strcmp($row['Slave_IO_Running'], "Yes") != )
   {
      
send_email_sms("Alert. Slave IO not running on host: " $host);
   }
   if( 
strcmp($row['Slave_SQL_Running'], "Yes") != )
   {
      
send_email_sms("Alert. Slave SQL not running on host: " $host);
   }
   if( 
strcmp($row['Last_Error'], "") != )
   {
      
send_email_sms("Alert. Error on Slave on host: " $host LINEBRK $row['Last_Error']);
   }
   
mysql_close($dbh);

   
$log_file $row['Master_Log_File'];
   echo 
"Current Master file used on Slave host " $host ": " $log_file LINEBRK;

   
$ext pathinfo($log_filePATHINFO_EXTENSION);
   
$min_file_number min($min_file_numberintval($ext)); // the smallest number is the oldest log file
}

$file_name "mysql-bin." sprintf("%06d"$min_file_number);
echo 
LINEBRK "Current Master oldest file on which Slaves are acting: " $file_name LINEBRK;

if( !
$dbhm mysql_connect($master_host$master_user$master_password) )
{
   
send_email_sms(mysql_error() . LINEBRK "line: " __LINE__ LINEBRK);
}
$query1 'show master logs;';
if( !
$rs1 mysql_query($query1$dbhm) )
{
   die(
mysql_error() . LINEBRK "line: " __LINE__ LINEBRK);
}
$lognames = array();

while( 
$row1 mysql_fetch_array($rs1) )
{
   
$lognames[] = $row1[0];
}

$marker array_search($file_name$lognames);
$delmarker $marker KEEPNUM;

if( 
$delmarker )
{
   echo 
"Not enough logs to keep. Nothing to purge. Exiting." LINEBRK;
   exit(
0);
}

echo 
"We are going to purge logs up to (not including) $lognames[$delmarker]LINEBRK;

$del_query "PURGE MASTER LOGS TO '$lognames[$delmarker]'";
# Uncomment the following lines to actually purge (i.e. delete) the logs
//if( !$rs2 = mysql_query($del_query, $dbhm) )
//{
//   die(mysql_error() . LINEBRK . "line: " . __LINE__ . LINEBRK);
//}
echo "The Master Server MySQL binary logs have been purged. The oldest log now available on the master is $lognames[$delmarker]." LINEBRK;
exit(
0);
/*
 * shutdown(): used to display echo's only when debuging is activated
 */
function shutdown($debug)
{
   if( 
$debug )
   {
      
ob_end_flush();
   }
   else
   {
      
ob_end_clean();
   }
}


?>

  Posted by Jeff Levene on September 20, 2018
In Linux (or UNIX), if you're nervous about getting the permissions wrong and possibly leaving the server unable to write the log, the following will always work, as long as you have room in the filesystem for another copy of the log (albeit briefly):

shell$ cp log_name.log log_name.log.old
shell$ > log_name.log

The second command begins with the ">", and means: run nothing, and redirect "nothing's" output to overwrite the file. That is, it truncates the file (makes it empty), while preserving all of its permissions, ownerships, ACLs, etc. The only danger of this approach is that if it's running, any log entries written between the two commands will be lost. You can minimize the danger by putting the 2 commands together on one line, with a semicolon (";") between them, or you can eliminate it by stopping the server, if that's an option.
Sign Up Login You must be logged in to post a comment.