Mysql log file and log type introduction _mysql

Log file type

MySQL has several different log files that can help you find out what's going on inside Mysqld:

Log files	Type of information entered in a file
Error log	Log a problem that occurs when you start, run, or stop mysqld.
Query log	A statement that records established client connections and executions.
Update log	Record the statement that changed the data. The use of this log is not supported.
Binary log	Log all statements that change data. Also used for replication.
Slow log	Log all queries that have run longer than long_query_time seconds, or queries that do not use indexes.

By default, all logs are created in the MYSQLD data directory. By refreshing the log, you can force mysqld to close and reopen the log file (or, in some cases, switch to a new log). When you execute a FLUSH logs statement or perform a mysqladmin flush-logs or mysqladmin refresh, a log refresh occurs.

Error log

The error log file contains information about when Mysqld starts and stops, and when any critical errors occur during the running of the server.

If Mysqld dies inexplicably and mysqld_safe needs to restart it, 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 to the error log.

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

You can use the--log-error[=file_name option to specify where MYSQLD saves the error log file. If the file_name value is not given, MYSQLD uses the error log name Host_name.err and writes to the log file in the data directory. If you perform flush LOGS, the error log renames the suffix with-old and mysqld creates a new empty log file. (If the--LOG-ERROR option is not given, it will not be renamed).

If you do not specify--log-error, or (in Windows), if you use the--console option, the error is written to the standard error output stderr. Usually the standard output is for your terminal.

Common query Log

If you want to know what's going on inside the mysqld, you should start it with the--log[=file_name] or the-l [file_name] option. If there is no value for the given file_name, the default name is Host_name.log. All connections and statements are logged to the log file. This log can be useful when you suspect that an error has occurred on the client and you want to know exactly what the client is sending to mysqld.

Mysqld the statement to the query log in the order in which it was received. This may be different from the order in which they were executed. This differs from the update log and the binary log, which log when the query is executed, but before any one of the locks is released. (The query log also contains all statements, and the binary log does not contain statements that query data only).

Server restarts and log refreshes do not produce new generic query log files (although the refresh closes and the General query log file is reopened). In Unix, you can rename the file and create a new file with the following command:

Copy Code code 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 started with the--log-slow-queries[=file_name] option, Mysqld writes a log file that contains all of the SQL statements that have an execution time exceeding long_query_time seconds. Getting the first time to lock the table does not count as execution time.

If the file_name value is not given, the default hostname, and the suffix is-slow.log. If a file name is given, but not an absolute pathname, the file is written to the data directory.

The statement is executed and all locks are released into the slow query log. The record order can be different from the execution order.

Slow query logs can be used to find long-running queries that can be used for optimization. However, it can be difficult to check for long and slow log queries. To make it easier, you can use the Mysqldumpslow command to get a summary of the queries displayed in the log to process the slow query log.

In the MySQL 5.1 Slow query log, slow queries that do not use indexes are recorded as queries that use indexes. To prevent slow queries that do not use indexes from logging in slow query logs, use the--log-short-format option.

In MySQL 5.1, with the--log-slow-admin-statements server option, you can request that slow-management statements, such as Optimize table, ANALYZE table, and ALTER table, be written to the slow query log.

Queries that are processed with the query cache are not added to the slow query log because a query that has 0 rows or a row and cannot benefit from the index does not write to the slow query log.

Binary log

Introduction to binary Files

Binary logs are in a more efficient format and are transaction-safe in ways that contain all the information available in the update log.

The binary log contains all the statements that have updated the data or have potentially updated the data (for example, a delete that does not match any of the rows). Statement is saved as an "event," which describes the data changes.

Note: The binary log has replaced the old update log, and the update log is no longer used in MySQL 5.1.

The behavior of binary files

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

The primary purpose of the binary log is to enable the database to be updated most likely as a result of the recovery, because the binary log contains all the updates that were made after the backup.

The binary log is also used to record all statements that will be sent to the server from the primary replication server.

When running the server, the performance is approximately 1% slower if binary logging is enabled. However, the benefit of binary logging is that it is used to restore and allow settings to replicate over this small performance loss.

File path for binary files

When started with the--log-bin[=file_name] option, Mysqld writes to the log file of the SQL command that contains all the updated data. If no file_name value is given, the default name is the host name followed by-bin. If the file name is given, but the path is not included, the file is written to the data directory. It is recommended that you specify a filename.

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

Mysqld adds a numeric extension after each binary log name. This number increases every time you start the server or refresh the log. If the current log size reaches Max_binlog_size, a new binary log is also created automatically. If you are using a large transaction, the binary log will also exceed the Max_binlog_size: The transaction is written to a binary log and should never be written into a different binary log.

To be aware of which different binary log files are being used, MYSQLD also creates a binary log index file that contains the file names of all the binary log files used. By default, the file name is the same as the binary log file, with the name extension '. Index '. You can change the file name of the binary log index file with the--log-bin-index[=file_name option. When Mysqld is running, you should not edit the file manually; If you do this, you will get mysqld confused.

Binary Logging options

You can use the MYSQLD option below to affect what is logged to the binary log. See also the discussion behind the options.

--binlog-do-db=db_name

Tells the home server that if the current database (that is, the selected database for use) is db_name, the update should be logged to the binary log. All other databases that are not clearly specified are ignored. If you use this option, you should make sure that only updates are made to the current database.

For CREATE DATABASE, ALTER database, and drop database statements, there is an exception, that is, by manipulating the database to determine whether the statement should be logged, rather than with the current database.

An example that cannot be performed as expected: If you start the server with Binlog-do-db=sales and execute use prices; UPDATE sales.january SET amount=amount+1000, which does not write to the binary log.

--binlog-ignore-db=db_name

Tells the home server that the update should not be saved to the binary log if the current database (that is, use the selected database) is db_name. If you use this option, you should make sure that only updates are made to the current database.

An example that cannot be performed as you expect: If the server starts with Binlog-ignore-db=sales and executes the use prices; UPDATE sales.january SET amount=amount+1000, which does not write to the binary log.

Similar to--BINLOG-DO-DB, for CREATE DATABASE, ALTER database, and drop database statements, there is an exception to the database of operations that determines whether statements should be logged, rather than the current database.

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

The server evaluates the options based on the following rules to record updates in the binary log or ignore them. Please note that there is an exception to the Create/alter/drop database statement. In these cases, the database created, modified, or deleted replaces the current database according to the following rules.

1. Are there binlog-do-db or binlog-ignore-db rules?

• No: Writes statements to the binary log and exits.

• There are: Follow the next step.

2. There are some rules (binlog-do-db or binlog-ignore-db or both). There is currently a database (use whether to select a database?)?

• No: Do not write statements and exit.
• There are: Follow the next step.

3. There is the current database. Is there a binlog-do-db rule?

· are: Does the current database match the binlog-do-db rule?

O is: Write statement and exit.

O No: Do not write statements, exit.

· No: Perform the next step.

4. There are some binlog-ignore-db rules. Does the current database match the binlog-ignore-db rule?

•: Do not write statements, and exit.
• No: Write query and exit.

For example, a server that runs only with Binlog-do-db=sales does not write to the binary log statements for which the current database is not sales (in other words, binlog-do-db can sometimes represent "ignoring other databases").

If you are replicating, make sure that you do not use the old binary log files from the server before you can delete them.