MySQL Log notes

MySQL Log Note 1. MySQL Log compares the information in the log file. The error log records the problems that occur when the mysqld is started, running, or stopped. Locate the cause of the fault when the system fails. query the Client Connection established in the log and the statements executed. Records all operations performed by the database. Binary logs record all statements for changing data. Database data backup and replication slow log www.2cto.com records all queries whose execution time exceeds long_query_time seconds or where no index is used. Locate system performance problems and find SQL statements with performance problems
2. Purpose of the Error Log: the error log file contains information related to when mysqld is started and stopped, and when any serious error occurs on the server during running. Usage: 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 (host_name is the host name) and writes the log file to the data directory (DATADIR. Clear: If you execute FLUSHLOGS, use-old to rename the suffix of the Error Log and create a new empty log file: shell> mv hostname. err hostname-old.errshell> mysqladmin flush-logsshell> cp hostname-old.errto-backup-directoryshell> rm hostname-old.err
3. log query function: the query log records all connections and statements of the client (Binary logs do not include query statements .) This log may be useful when you want to know exactly the statement that the client sends to mysqld. Use: You can use the -- log [= file_name] Or-l [file_name] Option to start the query log service of mysqld. If the file_name value is not specified, the default name is host_name.log. Suggestion: do not open this log to avoid affecting the overall system performance. Clear: Same as the error log. Www.2cto.com 4. Slow query log function: the slow query log records all log files of SQL statements whose execution time exceeds long_query_time seconds. Obtaining the initial lock time is not counted as the execution time. Use: you can start with the -- log-slow-queries [= file_name] option, if the file_name value is not given, the default is the host_name-slow.log. If the file name is provided but not the absolute path name, the file is written to the data directory. View: Use the mysqldumpslow command to obtain the query summary displayed in the log to process slow query logs. Clear: Same as the error log.
5. binary log function: all statements in the binary log that have updated data or have potentially updated data (for example, no DELETE matching any row. The main purpose of the binary log is to enable the database to be updated as much as possible after the backup, because the binary log contains all updates made after the backup, it is also used to record all statements that will be sent to the slave server on the master replication server. Use: You can use the -- log-bin [= file_name] Option to start. 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 (DATADIR ). If you provide an extension (for example, -- log-bin = file_name.extension) in the log name, the extension is removed and ignored. Clear: Execute mysqladminflush-logs and delete all logs three days ago. You can use purge master logs to manually delete LOGS. This statement also securely updates binary log index files (date parameters can be used ). For automatic deletion, you can select -- expire_logs_days = day. data before day is automatically deleted. Www.2cto.com
Others: 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 ). For the transaction table, mysqld writes the entire transaction to the binary log before the COMMIT is executed. 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. Adjust binlog_cache_size to a sufficiently large value to avoid using temporary files. (Note: The Binlog_cache_use status variable shows the number of transactions for the buffer save statement. The Binlog_cache_disk_use status variable shows how many temporary files must be used in the transaction .) Author xinhanggebuguake