MySQL Log File (5.1)

MySQL Log File

Error Log
General query log
Binary log
Slow query log
Log File Maintenance

MySQL has several different log files to help you find outMysqldInternal events:

Log Files	Types of information recorded in files
Error Log	Record Start, Run, or stopMysqldTime-outCurrent problem.
Query logs	Record the established client connection and executed statements.
Update log	Statement used to record data changes. This log is not supported.
Binary log	Records all statements for changing data. It is also used for replication.
Slow log	Record all queries whose execution time exceeds long_query_time seconds or where no index is used.

By default, all logs are created onMysqldData Directory. By refreshing logs, you can forceMysqldTo close and reopen the log file (or switch to a new log in some cases ). When you execute a flush logs statement or executeMysqladmin flush-logsOrMysqladmin refreshThe log is refreshed. If you are using the MySQL replication function, the slave replication server maintains more log files, which are called slave logs.

The error log file containsMysqldWhen the server is started and stopped, and when any serious error occurs during the running of the server.

IfMysqldInexplicably dead andMysqld_safeYou need to restart it,Mysqld_safeWrite a restarted mysqld message in the error log. IfMysqldNote that you need to automatically check or repair a table, then write a message in the error log.

In some operating systems, ifMysqldThe error log contains the stack trace information. Trace information can be used to determineMysqldDead place.

-- Log-error [=File_name] Option to specifyMysqldLocation where the error log file is saved. If noFile_nameValue,MysqldUse Error Log nameHost_name.errAnd write log files in the data directory. If you execute flush logs, the error log uses-old to rename the suffix andMysqldCreate a new empty log file. (If the -- log-error option is not provided, it will not be renamed ).

If -- log-error is not specified, or (in Windows) if you use the -- console option, the error is written to stderr. Standard output is usually your terminal.

In Windows, if the -- console option is not provided, the error output is always written to the. Err file.

General query log

If you want to knowMysqldWhat happened internally, you should use -- log [=File_name] Or-L [File_name] Option to start it. If noFile_nameValue. The default name isHost_name. Log.All connections and statements are recorded in log files. When you suspect that an error occurs on the client and want to know exactly what the client sendsMysqldThe log may be very useful..

MysqldThe query log is recorded according to the order it receives. This may be different from the execution sequence. This is different from the Update log and binary log. They record the log after the query is executed but before any lock is released. (Query logs also contain all statements, while binary logs do not contain statements that only query data ).

Server restart and log refresh do not generate new general query log files (although refresh is disabled and general query log files are re-opened ). In UNIX, you can use the following command to rename the file and create a new file:

shell> mv hostname.log hostname-old.log

shell> mysqladmin flush-logs

shell> cp hostname-old.log to-backup-directory

shell> rm hostname-old.log

In Windows, you cannot rename the log file when the server opens the log file. You must stop the server and rename the log file. Then, restart the server to create a new log file.

Binary log

Binary logs are in a more effective format and contain all available information in the Update log in a transaction-safe manner.

The binary log contains all statements that update data or have potentially updated data (for example, no Delete matching any row. The statement is saved as an "Event" and describes data changes.

NoteNote: The binary log replaces the old Update log, which is no longer used in MySQL 5.1.

The binary log also contains information about the execution time of each statement to update the database. It does not contain statements that do not modify any data. If you want to record all statements (for example, to identify problematic queries), you should use general query logs.

The main purpose of the binary log is to restore the database so that the database can be updated as much as possible, because the binary log contains all updates made after the backup.

Binary logs are also used to record all statements that will be sent to the slave server on the master replication server.

If binary logs are enabled when the server is running, the performance is about 1% slower. However, the benefit of binary logs is that it is used to recover and allow setting replication to exceed this small performance loss.

When -- log-bin [=File_name] Option,MysqldWrite a log file containing all SQL commands for updating data. If notFile_nameValue. The default name is the host name followed by-bin. If the file name is provided but the path is not included, the file is written to the data directory.

If you provide an extension (for example, -- log-bin =File_name.extension), Then the extension is removed and ignored.

MysqldAdd a digital extension after each binary log name. This number is increased every time you start the server or refresh the log. If the current log size reaches max_binlog_size, a new binary log is automatically created. If you are using a large transaction, the binary log will exceed max_binlog_size: the transaction is fully written into a binary log, and never written into different binary logs.

To know which different binary log files are used,MysqldCreate a binary log index file that contains the names of all binary log files used. By default, the file name is the same as that of the binary log file. The extension is '. Index '. You can use -- log-bin-index [=File_name] Option to change the file name of the binary log index file. WhenMysqldYou should not manually edit the file during running.MysqldIt becomes messy.

You can use the reset master statement to delete all binary log files, or use the purge master logs to delete only some binary files.

The binary log format has some known restrictions, which may affect recovery from backup.

You can use the followingMysqldOption to affect the recorded binary log Content. See the discussion below the options.

· -- BINLOG-do-DB =Db_name

Tell the master server that if the current database (that is, use the selected database) isDb_nameUpdate records should be recorded in binary logs. All other databases that are not explicitly specified are ignored. If this option is used, make sure that only the current database is updated.

An exception exists for create database, alter database, and drop database statements, that is, the database to be operated determines whether statements should be recorded, rather than the current database.

For example, if BINLOG-do-DB = Sales is used to start the server and use prices; update sales. january set amount = Amount + 1000;, this statement does not write binary logs.

· -- BINLOG-ignore-DB =Db_name

Tell the master server that if the current database (that is, use the selected database) isDb_name. Do not save updates to binary logs. If you use this option, make sure that only the current database is updated.

For example, if the server starts with BINLOG-ignore-DB = Sales and runs use prices; update sales. january set amount = Amount + 1000;, this statement does not write binary logs.

Similar to -- BINLOG-do-DB, there is an exception to the create database, alter database, and drop database statements, that is, the database to be operated determines whether statements should be recorded, instead of using the current database.

To record or ignore multiple databases, use multiple options to specify the corresponding options for each database.

The server evaluates the options based on the following rules to record updates to binary logs or ignore them. Note that there is an exception to the CREATE/alter/drop database statement. In these cases, according to the following rulesCreate,Modified or deletedThe database replaces the current database.

1. Is there any BINLOG-do-db or BINLOG-ignore-DB rule?

No: Write the statement to the binary log and exit.

Yes: Perform the next step.

2. There are some rules (BINLOG-do-db or BINLOG-ignore-db or both ). Is there a database currently (is the database selected for use ?)?

No:NoWrite the statement and exit.

Yes: Perform the next step.

3. There is a current database. Is there any BINLOG-do-DB rule?

Yes: Does the current database match the BINLOG-do-DB rule?

Yes: Write the statement and exit.

No: Do not write the statement and exit.

No: Perform the next step.

4. There are some BINLOG-ignore-DB rules. Does the current database match the BINLOG-ignore-DB rule?

Yes: Do not write the statement and exit.

No: Write query and exit.

For example, a server that only uses BINLOG-do-DB = Sales does not write statements of the current database that are not sales into binary logs (in other words, BINLOG-do-dB can sometimes indicate "ignore other databases ").

If you are copying them, make sure they are not deleted from the server before using the old binary log files. One way is to executeMysqladmin flush-logsDelete all logs three days ago. You can manually delete the log or use the purge master logs statement to securely update the binary log index file (date parameters can be used ).

Clients with super permissions can use the set SQL _log_bin = 0 statement to prohibit them from recording their statements in binary records.

You can useMysqlbinlogUtility checks binary log files. This is useful if you want to reprocess the log statements. For example, you can update the MySQL server from binary logs by using the following method:

shell> mysqlbinlog log-file | mysql -h server_name

AboutMysqlbinlog: a utility used to process binary log files.

If you are using a transaction, you must use the MySQL binary log for backup instead of the old Update log.

Logs are recorded in binary logs immediately after the query is completed, before the lock is released, or after the submission is complete. This ensures that logs are recorded in the execution order.

The update of non-transaction tables is saved to the binary log immediately after execution. For transaction tables, such as bdb or InnoDB tables, all Update (Update, delete, or insert) changes to the table are cached until the server receives the commit statement. At this point, before the commit is executed,MysqldWrite the entire transaction to the binary log. When the transaction processing thread starts, it allocates binlog_cache_size memory for the buffer query. If the statement is greater than this value, the thread opens a temporary file to save the transaction. The temporary file is deleted after the thread ends.

The binlog_cache_use status variable shows the number of transactions that use the buffer (or temporary files) to save statements. The binlog_cache_disk_use status variable shows how many temporary files must be used in these transactions. These two variables can be used to adjust binlog_cache_size to a sufficiently large value to avoid using temporary files.

Max_binlog_cache_size (4 GB by default) can be used to limit the total size of the buffer used to cache multi-statement transactions. If a transaction is greater than this value, it will fail and be rolled back.

If you are using update logs or binary logs, parallel inserts are converted to normal inserts when you use create... select or insert... select. In this way, you can use logs during backup to ensure that the backup of the table is re-created.

Note that the binary log format of MySQL 5.1 value is different from that of MySQL earlier versions, because the replication is improved.

By default, binary logs are not synchronized with the hard disk each time they are written. Therefore, if the operating system or machine (not just the MySQL server) crashes, the final statement in the binary log may be lost. To prevent this situation, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to make the binary logNAfter the binary log is written, it is synchronized with the hard disk. Even if sync_binlog is set to 1, the table content and binary log content may be inconsistent in the event of a crash. For example, if the InnoDB table is used, the MySQL server processes the commit statement, which writes the entire transaction to the binary log and submits the transaction to InnoDB. If a crash occurs between two operations, the transaction is rolled back by InnoDB at the time of restart, but it still exists in the binary log. You can use the -- InnoDB-safe-BINLOG option to solve this problem and increase the consistency between InnoDB table content and binary logs. (Note: In MySQL 5.1, -- InnoDB-safe-BINLOG is not required. This option is voided because XA transaction support is introduced ).

This option provides greater security. You must also configure the MySQL server to synchronize the binary logs (sync_binlog = 1) and (true by default) InnoDB logs of each transaction with the hard disk. This option is used to cut the InnoDB Transaction to be rolled back from the binary log after the transaction is rolled back when the transaction is restarted after the crash. This ensures that binary logs feed back the exact data in the InnoDB table and keep the slave server synchronized with the master server (do not receive rollback statements ).

Note that even if the MySQL server updates other storage engines instead of InnoDB, you can use -- InnoDB-safe-BINLOG. When InnoDB crashes and recovers, only statements/transactions that affect the InnoDB table are deleted from binary logs. If the MySQL server finds that the binary log is shortened when the crash is restored (that is, at least one InnoDB Transaction successfully committed is missing ), if sync_binlog = 1 and the hard disk/file system can be synchronized as needed (some do not need it, the output error message ("binary log <Name> is smaller than expected "). In this case, the binary log is inaccurate and the replication should begin with the data snapshot of the master server.

The method for writing binary log files and binary log index files is the same as writing them to the MyISAM table. Parameter

Slow query log

Use -- log-Slow-queries [=File_name] Option,MysqldWrite a log file containing all SQL statements whose execution time exceeds long_query_time. Obtaining the initial lock time is not counted as the execution time.

If notFile_nameThe default value is-slow. log. If the file name is provided but not the absolute path name, the file is written to the data directory.

After the statement is executed and all locks are released, the slow query log is recorded. The record sequence may be different from the execution sequence.

Slow query logs can be used to locate long-running queries and optimize them. However, it is difficult to check long and slow query logs. You can useMysqldumpslowCommand to obtain the query summary displayed in the log to process slow query logs.

In slow query logs of MySQL 5.1, slow queries without indexes are the same as those with indexes. To prevent slow queries that do not use indexes from being recorded in slow query logs, use the -- log-short-format option.

In MySQL 5.1, you can use the -- log-Slow-admin-statements server option to write slow management statements, such as optimize table, analyze table, and alter table, into slow query logs.

Queries processed using the query cache are not added to the slow query log. Because the table has zero rows or one row, queries that cannot benefit from the index are not written into the slow query log.

Log File Maintenance

The MySQL server can create different log files so that you can easily see the operations performed. However, you must clear these files regularly to ensure that logs do not occupy too much hard disk space.

When MySQL is enabled for logs, you may want to back up and delete old log files from time to time, and tell MySQL to start logging into new files.

On Linux (RedHat) installation, you can use the mysql-log-rotate script for this purpose. If you distribute the installation from rpmMySQLThe script should be installed automatically.

On other systems, you must install the short script by yourself.CronTo process log files.

You can useMysqladmin flush-logsOr SQL statement flush logs to forceMySQLStart to use the new log file.

The log clearing operation does the following:

• If you use standard logs (-- log) or slow query logs (-- log-Slow-queries), close and re-open the log file. (MySQL. log and 'hostname'-slow. log by default ).
• If you use the Update log (-- log-Update) or binary log (-- log-bin), close the log and open a new log file with a higher serial number.

If you only use the Update log, you only need to rename the log file and clear the log before the backup. For example, you can do this:

shell> cd mysql-data-directory

shell> mv mysql.log mysql.old

shell> mysqladmin flush-logs

Back up and delete"Mysql. Old".