Mysql Log learning summary and mysql Log Summary

Mysql Log learning summary and mysql Log 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 Log

Record the problems that occur when the mysql service is started, running, or stopped.

• Query logs

Record the established client connection and execution statements.

• Binary log

Records all statements for changing data and can be used for data replication.

• Slow query log

Records all queries whose execution time exceeds long_query_time or where no index is applicable. Ii. binary files

• Start and set binary files

In my. in cnf, [mysqld] has several binary settings: log-bin [= path/[filename] expire_logs_days = 10 max_binlog_size = 100Mlog-bin defines the binary log, and path indicates the path of the binary file; filename specifies the file name. 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.

• View binary files

Statement: show binary bogs; view the number of binary log files and file names. 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'

• Restore a database using a binary file

The syntax for restoring data from mysqlbinlog is as follows: 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.

• Temporarily disable the binary log feature

You can modify it 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 error logs

In the configuration file, configure log-error [= path/[filename] path to the directory where the log file is located, and file_name to the log name. After the configuration item is modified, restart the mysql service to take effect.

• View error logs

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.

• Delete error logs

Mysql error logs are stored in the file system as text files 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 general query logs

Log [= path/[filename] path is the directory path of the log file, and file_name is the log name. After the configuration item is modified, restart the mysql service to take effect.

• View general query logs

In windows, you can open it with notepad. in linux, you can run more, less, cat, and other commands.

• Delete common query logs

Common query logs are in text file format, so they can be deleted directly. 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 = npath indicates the directory path of the log file, and filename indicates the log name, n is time, in seconds. After the configuration item is modified, restart the mysql service to take effect.

• View slow query logs

You can use the slow query log analysis tool for viewing. The most famous ones are mysql dump slow, mysql sla, and mysql log filter.

• Delete slow query logs

You can delete it directly.