Mysql Log Files and log types

Mysql Log Files and log types

Log File Type

MySQL has several different log files to help you find out what happened inside mysqld:

Log Files	Types of information recorded in files
Error Log	Record the problems that occur when you start, run, or stop mysqld.
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 in the mysqld data directory. By refreshing logs, you can force mysqld to close and reopen the log file (or switch to a new log in some cases ). When you execute a flush logs statement or mysqladmin flush-logs or mysqladmin refresh, the log is refreshed.

Error Log

The error log file contains information about mysqld startup and shutdown, and any serious errors that occur on the server during running.

If mysqld dies inexplicably and mysqld_safe needs to be restarted, mysqld_safe writes a restarted mysqld message in the error log. If mysqld notices that a table needs to be automatically checked or repaired, a message is written into the error log.

In some operating systems, if mysqld is dead, the error log contains the stack trace information. Tracking information can be used to determine where mysqld died.

You can use the -- log-error [= file_name] Option to specify the location where mysqld saves the error log file. If the file_name value is not specified, mysqld uses the error log name host_name.err and writes the log file to the data directory. If you execute flush logs, the error log uses-old to rename the suffix and mysqld to create 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.

General query log

If you want to know what happened inside mysqld, you should use the -- log [= file_name] Or-l [file_name] Option to start it. If the file_name value is not specified, the default name is host_name.log. All connections and statements are recorded in log files. This log may be useful when you suspect that an error occurs on the client and want to know exactly the statement sent from the client to mysqld.

Mysqld records the query logs 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:
Copy codeThe Code is as follows:
Shell> mv hostname. log hostname-old.log
Shell> mysqladmin flush-logs
Shell> cp hostname-old.log to-backup-directory
Shell> rm hostname-old.log

Slow query log

When the -- log-slow-queries [= file_name] option is enabled, mysqld writes a log file containing all SQL statements whose execution time exceeds long_query_time seconds. Obtaining the initial lock time is not counted as the execution time.

If the file_name value is not given, no host name is provided by default, and the suffix 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. To make it easier, you can use the mysqldumpslow command 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.

Binary log

Introduction to binary files

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.

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

Binary File Behavior

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.

Path of the binary file

When the -- log-bin [= file_name] option is enabled, mysqld writes a log file containing all the SQL commands for updating data. If the file_name value is not given, 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. We recommend that you specify a file name.

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

Mysqld adds 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, mysqld also creates 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 the -- log-bin-index [= file_name] Option to change the file name of the binary log index file. When mysqld is running, you should not manually edit the file; otherwise, mysqld will become messy.

Binary log options

The following mysqld option can be used to affect the binary log information recorded. See the discussion below the options.

-- Binlog-do-db = db_name

Tell the master server that if the current database (that is, the database selected for USE) is db_name, update records should be recorded in the binary log. 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, the database selected for USE) is db_name, the update should not be saved to the binary log. 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, the database created, modified, or deleted will replace the current database according to the following rules.

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: Do not write statements and exit.
· Yes: Perform the next step.

3. There is a current database. Whether binlog-do-exists-Db rules?

· Yes: Does the current database match the binlog-do-db rules?

O: Write the statement and exit.

O 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 statements 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.