Mysql Study Notes (log management), mysql Study Notes

Mysql Study Notes (log management), mysql Study Notes

Logs: mysql logs that record the running status, daily operations, and error information of the database: Binary logs, error logs, and query logs. Binary log: records database operations in binary format, but does not record query statement error logs. This log records mysql server startup, information such as disabling and running errors query logs. This type of logs can be classified into two types: Common query logs and slow query logs. The former records user logon and query statements, and the latter records various operation logs whose execution time exceeds the specified time. One of the most important means for database maintenance is to use log files, you can not only view the cause of the error through the log file, but also use the log file to love you for data recovery. By default, mysql only starts the Error Log File, while other log files can be started manually. Operation binary log startup log if the mysql database stops unexpectedly, you can view the operations performed by the user and the modifications made to the database server files through the binary file, then, the database server is restored based on the records in the binary log file. By default, binary is disabled. By configuring my. ini. For details, [mysqld] log-bin [= dir \ [filename. INI file header to add, to add in the middle, the following location: log-slow-queries = mysql-slow.log log-error = mysql. err log-bin = mysql-bin adding error may cause mysql to fail to start. Dir is used to specify the storage path of the binary file. filename is used to specify the file name of the binary file. The specific format is filename. number, where the number format is 000001, 000002... if the dir and filename parameters are not set when the binary file is started, the default name "hostname"-bin "is used for the binary log file. number, Which is saved in the data file, the default directory. Every time you restart the mysql server, a new binary log file will be generated. The filename part of these log files will not change, but the number value will continue to increase. Filename. index: List of binary files. This file will be automatically added to view the log mysqlbinlog filename. number (this command is always executed incorrectly .) The binary log records the operations performed on the server, but does not include the show binlog events statement; view the content of the specified binlog file show binlog events in 'mysql-bin.000002 '; view the currently written binlog file show master status \ G to obtain the binlog file list show binary logs; stop the log. if the log-bin option is added to the ini configuration file, mysql always starts the binary log function. If you want to stop, you only need to delete the log-bin option. Some operations need to be recorded in the log, but some operations do not, you can use the set command, for example: set SQL _LOG_BIN = 0 set SQL _log_bin = 1 only users with super permissions can execute the set statement to delete the log reset master to delete all the binary files purge master logs to filename. number: delete all the two prohibited files smaller than the number. purge master logs before 'yyyy-mm-dd hh: MM: the operation error log of the binary log file before the specified time is deleted is enabled by default. The name format is generally hostname. err, Which is saved in the configuration file my. in ini, error-bin [= dir \ [filename] can be used to customize the path for saving error logs. If it is not enabled or disabled, you can go to the configuration file my. start in ini. [mysqld] error-bin = [dir \ [filename] viewing error logs is stored as text files, therefore, you can directly open view and stop, comment out the corresponding options in the configuration file, and restart and delete flush logs to execute the preceding command. mysql will first create a new error log, rename the old error log to filename. err-old. If the Administrator does not think that the old log needs to be saved, the administrator can directly Delete the operation query log generic query log to record all user operations on the mysql server, it contains the startup and shutdown information of the mysql server, the SQL statement for updating data records, and the SQL statement for querying data records. Slow query logs are used to record query statements whose execution time exceeds the specified time. With this type of logs, you can find which query statements have low execution efficiency, so as to optimize the startup. The default setting is disabled. ini [mysqld] log [= dir \ [filename] the suffix of the common query log file is *. log-slow-queries [= dir \ [filename] long_query_time = n the suffix of the slow query log file is * slow. if long_query_time is not set for log, the default value is 10 seconds. My Slow query log is enabled by default. If you want to know about your recent operations, you can view the general query log. If you want to know which operations have timed out, you can view that the slow query log is a text file and can be opened directly. Stop directly comment out the corresponding options in the configuration file, and restart to delete mysqladmin-u root-p flush-logs.