First, the log introduction MySQL logs are mainly divided into four categories, using these log files, you can see what happened inside MySQL. These four types of logs are:
Logs a problem that occurs when the MySQL service starts, runs, or stops the MySQL service.
Records established client connections and execution statements.
A statement that records all change data and can be used for data replication.
Records all queries that have an execution time exceeding long_query_time or that do not apply to the index. Two, binary files
- To start and set up a binary file
In my.cnf [mysqld] There are several binary settings: log-bin [=path/[filename]] Expire_logs_days = Ten max_binlog_size = 100mlog-bin define binary log, Pat h indicates the path of the binary file; filename specifies the file name; Index is a list of other binary logs. Expire_binlog_size defines when an expired log is purged. The default value is zero, which means no automatic cleanup. Max_binlog_size defines the size of a single file, when the file size limit is exceeded, the log is scrolled, the current file is closed, a new log file is reopened, and the variable cannot be set to greater than 1G or less than 4KB. You can use the show variables like ' log_% '; Log settings. Recommendation: The database file should not be placed on the same disk as the log file.
Statement: Show binary bogs; View the number of binary log files and file names. Use the command Mysqlbinlog to view the log format, delete the binary log statements: Reset master, delete the binaries. Delete the specified binaries: Purge (Master | binary) logs to ' Log_name ' Purge (Master | binary) logs before ' date '
- Recovering a database using a binary file
Mysqlbinlog the syntax for recovering data is as follows: mysqlbinlog [option] filename mysql-u user p password option is optional and filename is the log file name. The more commonly used two-pair option parameter is the--start-date 、--stop-date and--start-position 、--stop-position. --start-date 、--Stop-date can set the starting point and end point of the recovery database. --start-position 、--Stop-position can specify the starting and ending locations of the recovery database.
- Temporarily stop the binary logging feature
Can be modified in the configuration file, but changes in the configuration file need to restart the database. So use the command to modify, set sql_log_bin = [0 | 1] zero for pause, 1-bit recovery. Third, the error log records information about critical errors when the MySQL service starts, runs, or stops the MySQL service.
- Starting and setting the error log
In the configuration file, the configuration is as follows: Log-error [=path/[filename]] path is the directory path where the log file resides, file_name is the log name. After you modify the configuration item, you need to restart the MySQL service to take effect.
Check the error log to monitor the operating status of the system to facilitate timely detection of failures, repair failures. To view the error log path: Show variables like ' Log_err '; Locate the log file, which can be viewed with common Linux commands.
The MySQL error log is stored as a text file in the file system and can be deleted directly. Flush logs only re-opens the log file and does not back up and create operations. If the log file is not present, use flush logs to create it automatically. After you delete the error log, you need to execute the following command on the server side: Mysqladmin-u root-p flush-logs Four, universal query Log General query logging includes all user actions for MySQL, starting and shutting down services, executing queries and updating statements, and so on.
- To start and set up a common query log
log [=path/[filename]] path is the directory path where the log file is located, file_name is the log name. After you modify the configuration item, you need to restart the MySQL service to take effect
- View the General query log
Under Windows, use Notepad to open it, and use commands such as more,less,cat to view it under Linux.
- Delete a generic query log
The universal query log is a text file format, so you can delete it directly. Re-establish the new log file, you can use Mysqladmin Flush-log Five, the slow query log records all execution time more than long_query_time all queries or non-applicable indexes of the query, through the slow query log, you can find the execution time is longer, the execution of less efficient statements, It is then optimized. Start and set the slow query log in the MY.CNF configuration file as follows: [Mysqld] log-slow-queries [=path/[filename]] Long_query_time=npath is the directory path where the log files are located, FileName is the log name, and N is the time in seconds. After you modify the configuration item, you need to restart the MySQL service to take effect
You can use the Slow query log analysis tool to view; the more notable are: MySQL dump slow, MySQL SLA, MySQL log filter
can be deleted directly.
MySQL Log Learning Summary
