|
|
Maintaining Log FilesThe MySQL server has the capability of generating several kinds of log files. These are useful for diagnosing problems, improving server performance, enabling replication, and crash recovery. When the server begins executing, it examines its startup options to see whether it should perform logging and opens the appropriate log files if so. There are several types of logs you can tell the server to generate. The following list describes each one briefly, and the next several sections provide more detail.
The default location for each of these log files is the data directory. The server won't create any of them unless you ask for them, with one exception: On Windows, the server creates the error log unless you specify the --console option to send diagnostic information to the console window rather than to a file. Each log can be enabled by specifying a startup option for mysqld. Other than the binary and relay logs, these logs are written in text format and can be viewed directly. To display the contents of a binary or relay log file, use the mysqlbinlog utility. There also are some special-purpose logs that are managed by particular storage engines. The ISAM log is used for debugging purposes to record changes to ISAM and MyISAM tables; I won't mention it further. The BDB and InnoDB storage engines maintain logs of their own for internal purposes (such as for performing auto-recovery after a crash). Of all the logs, the general query log is most useful for monitoring the server, so when you first start using MySQL, I recommend that you enable the general log in addition to whatever other logs you want. After you have gained some experience with MySQL, you may want to turn off the general log to reduce your disk space requirements. To enable logging, use the options shown in the following table. If the log filename is optional (as indicated by square brackets) and you don't provide a name, the server uses a default name and writes the log file in the data directory. The server derives the default name for each of the log files from the name of your server host, represented by HOSTNAME in the following discussion. If you specify a log name as a relative pathname, the server interprets it relative to the data directory. A full pathname can be specified to place the log in some other directory. The server will create any log file that does not exist. However, it will not create the directory in which the file is to be written, so create the directory if necessary before starting the server.
If the BDB or InnoDB storage engine is enabled, it creates its own logs (by default, in the data directory). You cannot control whether the logs are generated, but you can specify where they are written by using the following options:
If you specify either of the InnoDB options you should specify both and give them both the same value. You can specify logging options on the command line for mysqld or mysqld_safe. However, because you usually specify log options the same way each time you start the server, it's most common to list them in an appropriate group of an option file. Typically, options are listed in the [mysqld] group, but they need not always be. "Specifying Server Startup Options" details the option groups applicable to the server and to the server startup programs.
The Error LogThe error log is used for recording diagnostic and error information. This log is handled differently on Unix and Windows, as described in the following discussion. The Error Log on UnixOn Unix, the server by default does not create an error log, but instead sends diagnostic output to the console. However, if you start the server by invoking mysqld_safe, an error log is created by default. This happens as follows:
The default error log name is HOSTNAME.err. You can specify a different error log name by passing a --log-error option to mysqld_safe, either on the command line or in the [mysqld_safe] group of an option file. If you invoke mysqld directly on Unix, you can specify a --log-error option to create an error log explicitly rather than sending error messages to the console. mysqld_safe and mysqld treat the --log-error option somewhat differently, so you probably should specify the option only to one of them if you use it explicitly:
If the error log file already exists but is not writable to the login account used for running the server, startup will fail with no output being written to the error log. This can happen if you start the server with different --user values at different times. It's best to use the same account consistently, as discussed in "Running the Server Using an Unprivileged Login Account." An error log always is created if you start the server using the mysql.server script, because mysql.server invokes mysqld_safe. However, mysql.server doesn't recognize --log-error on the command line or in its [mysql.server] option group. If you want to give a specific error log name in this case, you can do so in the [mysqld_safe] group of an option file. The Error Log on WindowsOn Windows, the server writes diagnostic information to a file named HOSTNAME.err in the data directory by default. If you start the server with the --console option, it writes diagnostic output to the console window and does not create an error log. (The --console option has no effect if you run the server as a service, because there is no console to write to in that case.) The General Query LogThis log contains a record of when clients connect to the server, each statement that is sent to it by clients, and various other events such as server startup and shutdown. If you enable the general log by specifying the --log option without a filename, the default name is HOSTNAME.log in the data directory. The server writes statements to this log in the order that it receives them. This may well be different from the order in which they finish executing, particularly for a mix of short and long statements. The Binary Log and the Binary Log Index FileThe server uses the binary log to record statements that modify data, such as INSERT, DELETE, or UPDATE. It does not write SELECT statements to this log. An UPDATE statement such as the following one does not appear in the binary log, either, because it doesn't actually change any values: UPDATE t SET i = i; MySQL must execute a statement first to determine whether it modifies data, so it writes statements to the binary log when they finish executing rather than when it receives them. The binary log also contains additional information that is useful for replication purposes, such as statement execution timestamps. Unlike other log files, statements are not written to the binary log as text, but in a more efficient binary format. This takes less space than writing the statements as text, but the binary nature of this log means that it is not directly viewable. You can use the mysqlbinlog utility to display the contents of binary log files in readable text form. The binary log can be used for database backup and recovery. Also, if you want to set up a server as a master server that is replicated to a slave server, you must enable the binary log. If you enable the binary log by specifying the --log-bin option without a filename, the server generates binary logs in numbered sequence, using HOSTNAME-bin as the basename: HOSTNAME-bin.000001, HOSTNAME-bin.000002, and so forth. (Before MySQL 4.1, the suffix has three digits rather than six.) Otherwise, the server uses the name that you specify as the basename, with the exception that if the name includes an extension, the extension is stripped. The next file in the sequence is generated each time you start the server or flush the logs, or when the current log reaches its maximum size. This size is determined by the value of the max_binlog_size system variable. The server writes statements to the binary log in order of execution. That is, they're logged in the order they finish, not the order in which they are received, which is an important property for making replication work properly. For statements that are part of a transaction, the server caches them until the transaction is committed. Then the server logs all statements in the transaction. If the transaction is rolled back, the transaction is not written to the binary log, because it results in no changes to the database. Actually, it is more correct to say that rolled-back transactions usually are not written to the binary log. If a transaction makes changes to non-transactional tables such as MyISAM tables, those changes cannot be rolled back. In this case, even a rolled-back transaction is logged to the binary log, to ensure that in a replication setup the changes to the non-transactional tables replicate properly. If you use the --log-short-format option in conjunction with --log-bin, MySQL writes less information to the binary log. If you enable binary logging, the server also creates an accompanying binary log index file that lists the names of the existing binary log files. The default index filename is the same as the basename of the binary logs, with an .index extension. To specify a name explicitly, use the --log-bin-index option. If the name includes no extension, .index is added to the name automatically. For example, if you specify --log-bin-index=binlog, the index filename becomes binlog.index. If you are using the binary logs for replication purposes, be sure not to delete any given binary log file until you are sure that its contents have been replicated to all applicable slave servers and it is no longer needed. "Expiring Replication-Related Log Files" describes how to check this.
The Update LogLike the binary log, the update log is used for recording statements that modify data, but its contents are written as text rather than in binary format. Prior to MySQL 3.23.14 (when the binary log was introduced), the update log was useful for database backup and recovery. The update log now is deprecated in favor of the binary log, which serves the same purposes and which in addition supports replication operations. In MySQL 5.0, the update log has been removed. To enable update logging before MySQL 5.0, use the --log-update option. The MySQL server names update log files using the following rules:
For update logs that are generated in numbered sequence, the server creates the next file in the series whenever it starts or the logs are flushed. If you use the --log-short-format option in conjunction with --log-update, MySQL writes less information to the update log. In MySQL 5.0, the update log is removed. If you specify both --log-bin and --log-update, the latter is ignored with a warning. If you specify only --log-update, it is treated as though you used --log-bin instead. This second case presents a problem if you upgrade to 5.0 from an earlier version, because you might end up with a series of files in which the earlier files are in text format and later files are in binary format. For this reason, it's best to use only --log-bin. The Slow-Query LogThe slow-query log provides a record of which queries took a long time to execute, where "long" is defined as the value of the long_query_time system variable in seconds. Slow queries also cause the server to increment its Slow_queries status counter. Because the time a query takes is not known until it finishes, queries are written to the slow-query log after they execute, not when they are received. The slow-query log is a text file, so it is viewable with any file-display program. You can use the mysqldumpslow utility to summarize the contents of this log. The slow-query log can be useful for identifying queries that you might be able to improve if you rewrite them. However, when interpreting its contents, you'll need to take general load into account. Query time is measured in real time (not CPU time), so if your server is bogged down, it's more likely that a query will be assessed as "slow," even if at some other time it runs under the limit. If you enable the slow-query log by specifying the --log-slow-queries option without a filename, the default name is HOSTNAME-slow.log in the data directory. Two other options affect what is written to this log: With --log-queries-not-using-index, the server also logs queries that execute without benefit of any index. With --log-short-format, the server writes less information to the log. The Relay Log and the Relay Log Index FileFor servers that are replication slaves, statements received from the master server are written to the relay log as they are received. The relay log acts as a holding area for these statements where they are held pending execution on the slave server. Two separate slave server threads handle statement reading and execution. The I/O thread reads statements from the master and logs them to the relay logs. The SQL thread reads the relay logs, executes the statements, and deletes each relay log when it has been completely processed. This decoupling of function allows the threads to run independently. The relay log shares several characteristics in common with the binary log:
If you enable the relay log by specifying the --relay-log option without a filename, the server generates relay logs in numbered sequence, using HOSTNAME-relay-bin as the basename: HOSTNAME-relay-bin.000001, HOSTNAME-relay-bin.000002, and so forth. (Before MySQL 4.1, the suffix has three digits rather than six.) Otherwise, the server uses the name that you specify as the basename, with the exception that if the name includes an extension, the extension is stripped. The next file in the sequence is generated each time you start the server or flush the logs, or when the current log reaches its maximum size. This size is determined by the value of the max_relay_log_size system variable. If you enable relay logging, the server also creates an accompanying relay log index file that lists the names of the existing relay log files. The default index filename is the same as the basename of the relay logs, with an .index extension. To specify a name explicitly, use the --relay-log-index option. If the name includes no extension, .index is added to the name automatically. For example, if you specify --relay-log-index=relay-log, the index filename becomes relay-log.index. Log File ExpirationLogging is important, but one danger of enabling logging is that it has the potential to generate huge amounts of information, possibly filling up your disks. This is especially true if you have a busy server that processes lots of statements. To keep the last few logs available online while preventing log files from growing without bound, you can use log file expiration techniques. Several methods are available for keeping logs manageable:
Log rotation often is used in conjunction with log flushing, to make sure that any buffered log information has been written to disk. Logs can be flushed by executing a mysqladmin flush-logs command or by issuing a FLUSH LOGS statement. The rest of this section describes how to use these expiration methods. The example log-expiration scripts discussed here can be found in the admin directory of the sampdb distribution. For any techniques that you put into practice, you should also consider how the log files fit into your database backup methods. It's a good idea to back up any log files that may be needed for recovery operations, so you shouldn't expire them before backing them up. Rotating Fixed-Name Log FilesThe MySQL server writes some types of log information to files that have fixed names, such as the general query log and the slow-query log. To expire fixed-name logs, use log rotation. This allows you to maintain the last few logs online but limit the number to as many as you choose, to prevent them from overrunning your disk. Log file rotation works as follows. Suppose that your general query log file is named qlog. At the first rotation, rename qlog to qlog.1 and tell the server to begin writing a new log file named qlog. At the second rotation, rename qlog.1 to qlog.2, qlog to qlog.1, and tell the server to begin writing another new qlog file. In this way, each file rotates through the names qlog.1, qlog.2, and so forth. When the file reaches a certain point in the rotation, expire it by letting the previous file overwrite it. For example, if you rotate the logs daily and you want to keep a week's work of logs, you would keep qlog.1 tHRough qlog.7. At each rotation, you expire qlog.7 by letting qlog.6 overwrite it to become the new qlog.7. The frequency of log rotation and the number of old logs you keep will depend on how busy your server is (active servers generate more log information) and how much disk space you're willing to allocate to old logs. On Unix, you can rename the current log file while the server has it open. After doing so, flushing the logs causes the server to close that file and open a new one, thereby creating a new log file with the original name. The following shell script, rotate_fixed_logs.sh, can be used to perform rotation of fixed-name log files:
#! /bin/sh
# rotate_fixed_logs.sh - rotate MySQL log file that has a fixed name
# Argument 1: log file name
if [ $# -ne 1 ]; then
echo "Usage: $0 logname" 1>&2
exit 1
fi
logfile=$1
mv $logfile.6 $logfile.7
mv $logfile.5 $logfile.6
mv $logfile.4 $logfile.5
mv $logfile.3 $logfile.4
mv $logfile.2 $logfile.3
mv $logfile.1 $logfile.2
mv $logfile $logfile.1
mysqladmin flush-logs
The script takes the log filename as its argument. You can either specify the full pathname of the file or change directory into the log directory and specify the file's name in that directory. For example, to rotate a log named qlog in /usr/mysql/data, you can execute it like this:
% rotate_fixed_logs.sh /usr/mysql/data/qlog
Or like this: % cd /usr/mysql/data % rotate_fixed_logs.sh qlog Note: The first few times a log rotation script executes, you won't have a full set of log files in the rotation, so the script may complain that it can't find all the files to be rotated. That's normal. To make sure that you have permission to rename the log files, run the script while logged in under the same account that you use for running the server (mysql in this book). Note that the mysqladmin command in the script includes no connection parameter arguments such as -u or -p. If the relevant parameters for invoking mysqladmin are stored in the .my.cnf option file in the mysql account home directory, you don't need to specify them on the mysqladmin command in the script. If you don't use an option file, the mysqladmin command needs to know how to connect to the server using a MySQL account that has sufficient privileges to flush the logs. To handle this, you might want to set up a limited-privilege account that can't do anything but issue flush commands. Then you can put that account's password in the script with minimal risk if you make the script accessible only to mysql. If you want to do this, the MySQL account should have only the RELOAD privilege. For example, to call the user flush and assign a password of flushpass, use the following GRANT statement: GRANT RELOAD ON *.* TO 'flush'@'localhost' IDENTIFIED BY 'flushpass'; After creating this account, change the mysqladmin command in the rotate_fixed_logs.sh script to look like this: mysqladmin -u flush -pflushpass flush-logs To protect the script against being read by other login accounts, you can make the script readable only to mysql. Execute the following command while logged in as mysql:
% chmod go-rwx rotate_fixed_logs.sh
To see how to use the rotate_fixed_logs.sh script to rotate and flush the logs periodically, consult "Automating the Log Expiration Procedure." Under Linux, you may prefer to use the logrotate utility to install the mysql-log-rotate script that comes with the MySQL distribution, rather than using rotate_fixed_logs.sh or writing your own script. Look for mysql-log-rotate in /usr/share/mysql for RPM distributions, or in the support-files directory of your MySQL installation for binary distributions, or under the share/mysql directory of MySQL source distributions. On Windows, log rotation doesn't work quite the same way as on Unix because file-locking semantics are different. If you attempt to rename a log file on Windows while the server has it open, a "file in use" error occurs. To rotate the logs, you must stop the server first, rename the files, and then restart the server. The log file renaming can be performed using the following batch script, rotate_fixed_logs.bat:
@echo off
REM rotate_fixed_logs.bat - rotate MySQL log file that has a fixed name
if not "%1" == "" goto ROTATE
@echo Usage: rotate_fixed_logs logname
goto DONE
:ROTATE
set logfile=%1
erase %logfile%.7
rename %logfile%.6 %logfile%.7
rename %logfile%.5 %logfile%.6
rename %logfile%.4 %logfile%.5
rename %logfile%.3 %logfile%.4
rename %logfile%.2 %logfile%.3
rename %logfile%.1 %logfile%.2
rename %logfile% %logfile%.1
:DONE
Invoke rotate_fixed_logs.bat much like the rotate_fixed_logs.sh shell script, with a single argument that names the log file to be rotated. For example, if MySQL is installed at C:\mysql, you can execute the script like this:
C:\> rotate_fixed_logs C:\mysql\data\qlog
Or like this: C:\> cd \mysql\data C:\> rotate_fixed_logs qlog Expiring Numbered Log FilesFixed-name log files can be expired using filename rotation, as just discussed. For numbered log files such as you can generate for the binary log and the update log, log expiration needs to be handled a bit differently. In this case, you can expire files based on age (assessed as time of last modification) rather than by rotating them through a given set of names. The reason for using age is that numbered logs are not necessarily created on a fixed schedule, so you can't assume that it's okay to retain just the last n files. If the server happens to receive several log flushing commands in a short time span, you can easily have many numbered logs, none of which are old enough to need expiring. If you use binary logs for replication, do not use age-based replication. Instead, see "Expiring Replication-Related Log Files" later in this chapter. If you are not using the binary logs for replication, the easiest way to expire them is to set the expire_logs_days system variable. When this variable has a value n greater than zero, the server automatically expires binary log files that are older than n days and updates the binary log index file. For example, to set this variable to expire binary logs that have not been changed for a week, put these lines in an option file: [mysqld] expire_logs_days=7 For a script-based approach to expiring numbered log files by age, you might use a script like this:
#! /usr/bin/perl -w
# expire_numbered_logs.pl - Look through a set of numbered MySQL
# log files and delete those that are more than a week old.
# Usage: expire_numbered_logs.pl logfile ...
use strict;
die "Usage: $0 logfile ...\n" if @ARGV == 0;
my $max_allowed_age = 7; # max allowed age in days (change as desired)
foreach my $file (@ARGV) # check each argument
{
unlink ($file) if -e $file && -M $file >= $max_allowed_age;
}
exit (0);
expire_numbered_logs.pl is written in Perl. It can be used on both Unix and Windows because Perl is a cross-platform scripting language. To use the script, invoke it with the names of the log files that are candidates for expiration. For example, on Unix, you can execute it like this:
% expire_numbered_logs.pl /usr/mysql/data/update.[0-9]*
Or like this: % cd /usr/mysql/data % expire_numbered_logs.pl update.[0-9]* Note: The expire_numbered_logs.pl script is dangerous if you don't invoke it with appropriate arguments! For example, you definitely don't want to invoke it like this: % cd /usr/mysql/data % expire_numbered_logs.pl * The * pattern matches all filenames, so those commands will remove all files in the data directory that are more than a week old, not just log files. Expiring Replication-Related Log FilesThe server generates binary logs in numbered sequence. One way to manage them is to expire them based on age, as described in the previous section. However, if you're using the binary logs for replication, age is not an indicator of whether a log can be removed. You should not expire a binary log until after its contents have been replicated to all the slave servers. A difficulty here is that, due to the asynchronous nature of MySQL replication, the master server itself doesn't know how many slaves there are or which files have been propagated to them. The master won't purge binary logs that have not yet been sent to connected slaves, but there is no guarantee that a given slave is connected at any particular time. You must know which servers are acting as slaves, and then connect to each one and issue a SHOW SLAVE STATUS statement to determine which of the master's binary log files the slave currently is processing. (The file's name is the value in the Master_Log_File column.) Any binary log that is no longer used by any of the slaves can be removed. To understand how this works, suppose that you have the following scenario:
In this case, the lowest-numbered binary log still required by the set of slave servers is binlog.000040, so any log with a lower number can be removed. To do that, connect to the master server and issue the following statement:
mysql> PURGE MASTER LOGS TO 'binlog.000040';
That causes the server to delete all binary logs with numbers lower than the named file, which for the situation just described includes binlog.000038 and binlog.000039. The SHOW SLAVE STATUS and PURGE MASTER LOGS statements both require the SUPER privilege. A replication slave server creates the relay logs in numbered sequence. It generates a new relay log when the current one reaches the maximum allowable size (or when the logs are flushed). The slave server removes old relay logs automatically as it finishes processing them, but if the maximum relay log size is large, the current file also grows large. To minimize the amount of relay log information stored on disk, you can reduce the maximum allowable size by setting the max_relay_log_size system variable. Automating the Log Expiration ProcedureIt's possible to invoke log expiration scripts manually, but you need not remember to run them yourself if you have a way to schedule the commands to execute automatically. On Unix, one way to do this is to use the cron utility and set up a crontab file that defines the expiration schedule. If you're not familiar with cron, check the relevant Unix manual pages using these commands: % man cron % man crontab You might need to use another command to read about the crontab file format:
% man 5 crontab
Suppose that you want to rotate a general query log named qlog by using the rotate_fixed_logs.sh script, that this script is installed in /home/mysql/bin, and that the log files are located in the /var/mysql/data directory. Log in as mysql, and then edit the mysql user's crontab file using this command:
% crontab -e
This command allows you to edit a copy of your current crontab file (which might be empty if no cron jobs have yet been set up). Add a line to the file that looks like this: 0 4 * * * /home/mysql/bin/rotate_fixed_logs.sh /var/mysql/data/qlog This entry tells cron to run the script at 4 a.m. each morning. You can vary the time or scheduling as desired; check the crontab manual page for the format of the entries. You'll probably want to rotate the logs more frequently for a busy server that generates lots of log information than for one that is less active. To make sure that the logs are flushed regularly (for example, to generate the next numbered binary log or update log), you can schedule a mysqladmin flush-logs command to execute periodically by adding another crontab entry. You might need to list the full pathname to mysqladmin to make sure that cron can find it. Automatic log file expiration is more problematic on Windows, because Windows file-locking semantics prevent you from renaming a file while the server has it open. This means that you cannot rotate any current log file without stopping and restarting the server, and there might be no time of day when you can guarantee that the server will not be in use.  |
|
|