Mysql Log learning Summary

Mysql Log learning Summary
I. Log introduction mysql logs are mainly divided into four types. Using these log files, you can view what happened in mysql. These four types of logs are: error logs record problems that occur when mysql service is started, running, or stopped. Query the client connection and execution statements created in the log records. Binary logs record all statements for changing data and can be used for data replication. The slow query log records all queries whose execution time exceeds long_query_time or where the index is not applicable. 2. Start and set the binary file in my. in cnf, [mysqld] has several binary settings: log-bin [= path/[filename] expire_logs_days = 10 max_binlog_size = 100 M log-bin defines binary logs, path indicates the path of the binary file; filename indicates the file name; where. index is a list of other binary logs. Expire_binlog_size defines the time for clearing expired logs. The default value is zero, indicating that it is not automatically cleared. Max_binlog_size defines the size of a single file. When the file size limit is exceeded, the log will scroll. Close the current file and open a new log file, you cannot set this variable to greater than 1 GB or less than 4 kb. You can use show variables like 'Log _ % '; To set logs. Suggestion: You are advised not to store database files and log files on the same disk. Run the show binary bogs command to view the number of binary log files and the file name. Run the command mysqlbinlog to view the log format and delete the binary log statement: reset master; Delete the binary file. Delete the specified binary file: purge (master | binary) logs to 'Log _ name' purge (master | binary) logs before 'date' adopts the following syntax to restore data from the database mysqlbinlog using binary files: mysqlbinlog [option] filename mysql-u user p password option is optional, and filename is the log file name. The two commonly used option parameters are -- start-date, -- stop-date, -- start-position, and -- stop-position. -- Start-date and -- stop-date can be used to set the start time and end time of database restoration. -- Start-position and -- stop-position can specify the start and end positions for database recovery. You can modify the binary log feature in the configuration file, but you need to restart the database to modify it in the configuration file. Therefore, use the command to modify, set SQL _log_bin = [0 | 1] 0 is paused, and 1 bit is restored. 3. The error log records information about serious errors when the mysql service is started, running, or stopped. Start and set the error log in the configuration file. The configuration is as follows: log-error [= path/[filename] path indicates the directory path where the log file is located, and file_name indicates the log name. After the configuration item is modified, restart the mysql service to take effect. You can view error logs to monitor the running status of the system, so that you can detect and fix faults in time. View the Error log Path: show variables like 'Log _ err'; locate the log file and use common linux commands. The mysql error log is stored in the file system as a text file and can be deleted directly. Flush logs only re-opens the log file and does not back up or create a log file. If the log file does not exist, use flush logs to automatically create it. After deleting the error log, run the following command on the server: mysqladmin-u root-p flush-logs 4. The general query log contains all user operations of mysql, start and close the service, and execute query and update statements. Start and set the common query log [= path/[filename] path to the directory where the log file is located, and file_name to the log name. After modifying the configuration item, you need to restart the mysql service to view the general query log. In windows, use NotePad to open it. in linux, run more, less, cat and other commands to view the query log. You can delete a common query log in the text file format. Re-create a new log file. You can use mysqladmin flush-log 5 to record all queries whose execution time exceeds long_query_time or where no index is applicable, you can find the statements with long execution time and low execution efficiency, and then optimize them. Start and set the slow query log in my. the cnf configuration file is as follows: [mysqld] log-slow-queries [= path/[filename] long_query_time = n path indicates the directory path of the log file, and filename indicates the log name, n is time, in seconds. After modifying the configuration item, you need to restart the mysql service to view the slow query log. You can use the slow query log analysis tool to view it. The more famous ones are: mysql dump slow, mysql sla, and mysql log filter can delete slow query logs directly.