How does MySQL view and delete error logs 01/18 Update SLTechnology News&Howtos

How does MySQL view and delete error logs

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL how to view and delete error log", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL how to view and delete error log" bar!

MySQL log:

Mainly include: error log, query log, slow query log, transaction log, binary log

Log is an important part of mysql database. The log file records the changes that occur during the operation of the mysql database; that is, it is used to record the client connection status of the mysql database, the execution of SQL statements, error messages, and so on. When the database is accidentally damaged, the cause of the file error can be seen through the log, and the data can be recovered through the log file.

Error log

In mysql databases, error logging is enabled by default. Also, the error log cannot be disabled. By default, the error log is stored in a data file in the mysql database. The name of the error log file is usually hostname.err. Where hostname represents the server hostname.

The error log information can be configured by itself, and the information recorded in the error log can be defined through log-error and log-warnings, where log-err defines whether the error log is enabled and where the error log is stored, and log-warnings defines whether warning information is also defined in the error log. By default, the error log records the following information: information during server startup and shutdown (not necessarily error messages, such as how mysql starts InnoDB's tablespace file, how to initialize its own storage engine, etc.), error messages while the server is running, information generated when the event scheduler runs an event, and information generated when starting the server process from the server.

Let's define the functionality of the mysql error log:

In general, log-level definitions are only defined at the global level without callback variables.

Where log_error can be defined directly as the file path or ON | OFF;log_warings can only use 1 | 0 to define switch startup.

You can use log_error to change the location of the error log as follows:

[root@stu18 data] # vim / etc/ my.cnf[mysqld] Log_error=DIR/ [filename]

Resolution: parameter DIR specifies the path to the error log. Parameter filename is the name of the error log. If this parameter is not specified, it defaults to the host name. Restart the mysql server to take effect.

View the mysql error log:

[root@stu18 data] # tail-20 stu18.magedu.com.err130813 15:30:50 InnoDB: Starting shutdown...130813 15:30:51 InnoDB: Shutdown completed Log sequence number 1630920130813 15:30:51 [Note] / usr/local/mysql/bin/mysqld: Shutdown complete130813 15:30:52 mysqld_safe mysqld from pid file / mydata/data/stu18.magedu.com.pid ended130813 15:30:53 mysqld_safe Starting mysqld daemon with databases from / mydata/data130813 15:30:54 InnoDB: The InnoDB memory heap is disabled # disables the heap function of InnoDB memory. 130813 15:30:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins # Mutexes (mutexes) and rw_locks (row-level locks) are compiled by GCC and built into InnoDB. 130813 15:30:54 InnoDB: Compressed tables use zlib 1.2.3 # default compression tool is zlib130813 15:30:55 InnoDB: Initializing buffer pool, size = 128.0m # buffer pool (buffer pool) for InnoDB engine 130813 15:30:55 InnoDB: Completed initialization of buffer pool130813 15:30:55 InnoDB: highest supported file format is Barracuda.130813 15:30:57 InnoDB: Waiting for the background threads to start130813 15:30:58 InnoDB: 5.5.33 started Log sequence number 1630920130813 15:30:58 [Note] Server hostname (bind-address): '0.0.0.09; port: 3306130813 15:30:58 [Note] -' 0.0.0.0' resolves to '0.0.0.0' # 0.0.0.0 deconstructs the hostname, which fails here 130813 15:30:58 [Note] Server socket created on IP: '0.0.0.0room.130813 15:30:58 [Note] Event Scheduler: Loaded 0 events # event scheduler does not have any events because it is not loaded. 130813 15:30:58 [Note] / usr/local/mysql/bin/mysqld: ready for connections. # mysql starts and completes the request waiting for the client. Version: '5.5.33 socket:' / tmp/mysql.sock' port: 3306 Source distribution # create a local sock for local connections.

Delete the error log:

Before mysql5.5.7: database administrators can delete error logs from a long time ago to ensure hard disk space on the mysql server. In the mysql database, you can use the mysqladmin command to open a new error log. The syntax of the mysqladmin command is as follows: mysqladmin-u root-pflush-logs can also be used to log in to the mysql database using the FLUSHLOGS statement to open a new error log.

After mysql5.5.7: the server will turn off this feature. You can only rename the original error log file and manually flush the log to create a new one as follows:

[root@stu18 data] # mv stu18.magedu.com.err stu18.magedu.com.err.old [root@stu18 data] # mysqladmin flush-logs [root@stu18 data] # lshellodb myclass mysql-bin.000003 mysql-bin.index stu18.magedu.com.pid ibdata1 mysql mysql-bin.000004 performance_schema ib_logfile0 mysql-bin.000001 stu18.magedu.com.err test ib_logfile1 mysql-bin.000002 stu18.magedu.com.err.old

For more information, please refer to the official document: http://dev.mysql.com/doc/refman/5.5/en/error-log.html

Query log:

Query logs are turned off by default. Because the query log records all the operations of the user, it also contains information such as additions, deletions, queries, etc., in the environment of large concurrent operations, it will produce a large amount of information, resulting in unnecessary disk IO, which will affect the performance of mysql. If it is not for the purpose of debugging the database, it is recommended not to open the query log.

Check whether the query log is enabled:

Extended parsing: there are generally three ways to output logs: file (file), table (table), and none (do not save). The first two output locations can be defined at the same time. None indicates that the log function is enabled but log information is recorded. File is defined by general_log_file | / mydata/data/stu18.log, etc. When the output location is defined as a table, the log content can be viewed as follows:

Slow query log:

Slow query logs are used to record queries that have been executed for more than a specified time. Through the slow query log, you can find out which query statements are inefficient for optimization. It is generally recommended to turn it on, which has little impact on server performance, but can record queries that have been executed on the mysql server for a long time. That can help us locate performance problems.

View the definition of the slow query log:

Mysql > SHOW GLOBAL VARIABLES LIKE'% log%'; | slow_query_log | OFF # defines the location of slow log | slow_query_log_file | / mydata/data/stu18-slow.log # defines the location of slow log when the output mode is file (file)

Start and set the slow query log:

1. You can enable slow log by using the log-slow-queries option in the configuration file my.cnf, which is as follows:

[root@stu18 data] # vim / etc/ my.cnf [mysqld] slow_query_log=1log-slow-queries [= DIR/ [filename]]

Where the DIR parameter specifies the storage path of the slow log; the filename parameter specifies the file name of the log, and the completion name of the generated log file is filename-slow.log. If you do not specify a storage path, the slow query log is stored in the data file of the mysql database by default. If you do not specify a file name, the default file name is hostname-slow.log.

2. Define it directly by logging in to the mysql server as follows:

First have global permissions; then execute mysql > set global slow_query_log=1

How much time is exceeded by default is called slow query log?

This time value is generally set through the long_query_time option, which is measured in seconds and can be accurate to microseconds. If the query time exceeds this time value (the default is 10 seconds), the query statement will be recorded in the slow query log. View the default time value of the server as follows:

Note: the slow query time not only means that the statement itself is executed for more than 10 seconds, but also the query execution time or other reasons caused by the requisition of other resources are recorded in the slow query. So the length of the slow check represents all the time between the start of the query and the end of the query for any possible reason.

Test whether you can log:

View the slow query log:

Mysql > use mysqlmysql > selectuser,host,password from user where user= "root" +-+ | user | host | password | +-+ | root | localhost | | root | stu18.magedu.com | | root | 127.0.0.1 | root |:: 1 | | +-+ 4 rows in set (0.08sec) # query time is 0.08mysql > systemmore / mydata/data/stu18_slow.log # query slow query log information / usr/local/mysql/bin/mysqld | Version: 5.5.33-log (Source distribution). Startedwith:Tcp port: 3306 Unix socket: / tmp/mysql.sockTime Id Command Argument > part has been omitted > # Time: 13100723 startedwith:Tcp port 46VR User@Host:root [root] @ localhost [] # Query_time:0.108459 Lock_time: 0.000216 Rows_sent:4 Rows_examined: 6SETtimestamping 1381160793

Transaction log:

Transaction logs (InnoDB-specific logs) can help improve the efficiency of transactions. Using the transaction log, the storage engine only needs to modify the memory copy of the table when modifying the data, and then record the modification behavior in the transaction log on the hard disk, instead of persisting the modified data to the disk every time. The transaction log is appended, so the operation of writing the log is the sequential IPUP O in a small area of the disk, unlike the random IUnip O, which needs to move the head in multiple places on the disk, so using the transaction log method is relatively faster. After the transaction log is persistent, the modified data in memory can be slowly brushed back to disk in the background. At present, most storage engines are implemented in this way, which we usually call pre-write logs, and it is necessary to write to disk twice to modify data.

If the modification of the data has been recorded in the transaction log and persisted, but the data itself has not been written back to disk, the system crashes and the storage engine can automatically recover the modified data when it is restarted. The recovery method it has depends on the storage engine.

View the definition of the transaction log:

Mysql > SHOWGLOBAL VARIABLES LIKE'% log%' +-- +-+ | Variable_name | Value | +- -- +-+ | innodb_flush_log_at_trx_commit | 1 # whether the innodb synchronizes the log from buffer to file when the transaction commits. 1 indicates the transaction to commit. Synchronization is not submitted and synchronized every other second Poor performance will result in a large number of disks. Defined as 2 means that the transaction is synchronized only when the transaction is committed, but the entire transaction may be lost | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 # there are at least two | | innodb_log_group_home_dir |. / # define the location of the innodb transaction log group | | innodb_mirrored_log_groups | 1 # indicates mirroring the log group | each transaction log is 5 megabytes in size. File: [root@stu18 data] # ls-lh-rw-rw---- 1 mysqlmysql 5.0M Oct 7 23:36 ib_logfile0-rw-rw---- 1 mysqlmysql 5.0M Aug 12 01:06 ib_logfile1

Binary log:

Binary log, also known as change log, is mainly used to record modified data or mysql statements that may cause data changes, and records the time when the statement occurred, the length of execution, the data of the operation, and so on. So binary logs can be used to query what changes have been made in the mysql database. Generally speaking, the upper limit of size and volume is 1G.

Binary on state:

Mysql > showglobal variables like "log_bin%" +-- +-+ | Variable_name | Value | +-- +-+ | log_bin | ON | # enabled | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +-+-+

Parameters related to binary logs:

Mysql > showglobal variables like "% log%"; sql_log_bin = {ON | OFF} # is used to control whether the session-level binary logging feature is on or off. The default is ON, which means that logging is enabled. The user can modify the value of this variable at the session level, but it must have SUPER permission. Binlog_cache_size = 32768 # the default value of 32768 Binlog Cache is used in environments where binary logging (binlog) recording is turned on. It is a memory area designed by MySQL to improve the recording efficiency of binlog and to temporarily cache binlog data for a short period of time. In general, if there are no big transactions in our database and writes are not particularly frequent, 2MB~4MB is an appropriate choice. However, if our database has a large number of transactions and a large number of writes, we can increase binlog_cache_size appropriately. At the same time, we can use binlog_cache_use and binlog_cache_disk_use to analyze whether the set binlog_cache_size is enough and whether a large number of binlog_cache are cached using temporary files (binlog_cache_disk_use) due to insufficient memory. Binlog_stmt_cache_size= 32768 # when non-transactional statements use binary log caching but exceed the binlog_stmt_cache_size, a temporary file is used to hold the statements. Log_bin = mysql-bin# specifies the location of the binlog, which is under the data directory by default. Binlog-format= {ROW | STATEMENT | MIXED} # specifies the type of binary log, which defaults to MIXED. If the binary log is formatted but not enabled, a warning log message is generated when MySQL starts and is recorded in the error log. Sync_binlog = 1 sets how often the binary log is synchronized to the disk file, 0 means out of sync, and any positive value indicates that the binary is synchronized after every number of writes. When the value of autocommit is 1, the execution of each statement will cause binary log synchronization, otherwise, the commit of each transaction will cause binary log synchronization max _ binlog_cache_size= {4096.. 18446744073709547520} # binary log cache size, version 5.5.9 and later applies only to transaction cache, and the upper limit is determined by max_binlog_stmt_cache_size. Max_binlog_stmt_cache_size= {4096.. 18446744073709547520} # binary log cache size, version 5.5.9 or later only applies to transaction cache expire_log_days = {0.99} # sets the number of days for binary logs to expire, and binary log files exceeding this number of days will be automatically deleted. The default is 0, which means that the automatic deletion of expiration is not enabled. If this feature is enabled, automatic deletion usually occurs when MySQL starts or when FLUSH logs are started.

How the binary log is defined:

First, log_bin can be directly defined as the file path, or it can be ON | OFF.

Second, you can enable binary logs by editing the log-bin option in my.cnf, which is as follows:

[root@stu18 ~] # my.cnf [mysqld] log-bin [= DIR\ [filename]]

Where the DIR parameter specifies the storage path of the binary file, and the filename parameter specifies the file name of the two-level file, which is in the form of filename.number,number 000001, 000002, and so on. Each time you restart the mysql service or run mysql > flush logs;, a new binary log file is generated, and the number of these log files is incremented. In addition to generating the above file, a file named filename.index is also generated. The list of all binary log files stored in this file is also known as the index of binary files.

[root@stu18 ~] # cd / mydata/data/ [root@stu18 data] # ls-lh-rw-rw---- 1 mysqlmysql 14K Aug 13 15:30 mysql-bin.000001-rw-rw---- 1 mysqlmysql 150 Aug 13 17:05 mysql-bin.000002-rw-rw---- 1 mysqlmysql Aug 13 17:06 mysql-bin.000003-rw-rw---- 1 mysqlmysql 150 Aug 13 17:07 mysql-bin.000004-rw-rw---- 1 mysqlmysql 150 Aug 13 17:39 mysql-bin.000005-rw-rw---- 1 mysqlmysql 126 Aug 13 19:03 mysql-bin.000006-rw-rw---- 1 mysqlmysql 126 Aug 13 19:03 mysql-bin.000007-rw-rw---- 1 mysqlmysql 126 Aug 13 19:05 mysql-bin.000008-rw-rw---- 1 mysqlmysql 107 Aug 13 19:05 mysql-bin.000009-rw-rw---- 1 mysqlmysql 353 Oct 7 23:40 mysql-bin.000010-rw-rw -1 mysqlmysql 190 Oct 7 20:43 mysql-bin.index [root@stu18 data] # cat mysql-bin.index./mysql-bin.000001./mysql-bin.000002./mysql-bin.000003./mysql-bin.000004./mysql-bin.000005./mysql-bin.000006./mysql-bin.000007./mysql-bin.000008./mysql-bin.000009./mysql-bin.000010

If we insert a data into a field of a table and the data is the current time (date-time type); applying this binary to another server over time will change the data, resulting in data inconsistency, so it is not reliable to use the default statement definition for this kind of non-deterministic data.

The definition format commonly used in binary logs:

1. Statement (statement): default record format

2. Row: what is defined is not the data itself, but what the data of this row is

3. Mixed mode (mixed): alternately use rows and statements, and be judged by the mysql server.

The row-based definition format will have a larger amount of data but can ensure the accuracy of the data.

View the binary log:

Binary logs are defined in binary format; using this format can store more information and make writing to binary logs more efficient. However, you cannot directly use the View command to open and view the binary log.

Small extension: the location of the binary log: usually the location of the last event execution end time, and each log file has its own metadata, so for the current version of mysql, the binary start position is usually 107.

View information about the current binary file:

Command to view binary log information:

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] mysql > showbinlog events\ G # View all binary information * * 87. Row * * Log_name: mysql-bin.000001 Pos: 13580 Event_type: Query Server_id: 1End_log_pos: 13688 Info: use `hellodb`; / *! 40000 ALTERTABLE `toc` DISABLE KEYS * / * 88. Row * * Log_name: mysql-bin.000001 Pos: 13688 Event_type: Query Server_id: 1End_log_pos: 13795 Info: use `hellodb`; / *! 40000 ALTERTABLE `toc` ENABLE KEYS * / * * 89. Row * * Log_name: mysql-bin.000001 Pos: 13795 Event_type: Stop Server_id: 1End_log_pos: 13814 Info:89 rows in set (0.00sec) mysql > showbinlog events in 'mysql-bin.000011' # View the binary information of the specified log +-- -+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-+- -+ | mysql-bin.000011 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log Binlogver: 4 | mysql-bin.000011 | Query | 1 | 190 | create database yong | | mysql-bin.000011 | 190 | Query | 1 | 293 | create table yong.tb1 (idint) Name char (20) | | mysql-bin.000011 | 293 | Query | 1 | 357 | BEGIN | | mysql-bin.000011 | 357 | Query | 1 | 452 | insert into yong.tb1values (1) 'tom') | | mysql-bin.000011 | 452 | Xid | 1 | 479 | COMMIT / * xid=103 * / | +-- -+ 6 rows in set (0.00sec) mysql > showbinlog events in 'mysql-bin.000011' from 190 # start from the specified event location +- -Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-+- -- + | mysql-bin.000011 | 190 | Query | 1 | 293 | create table yong.tb1 (idint Name char (20) | | mysql-bin.000011 | 293 | Query | 1 | 357 | BEGIN | | mysql-bin.000011 | 357 | Query | 1 | 452 | insert into yong.tb1values (1) 'tom') | | mysql-bin.000011 | 452 | Xid | 1 | 479 | COMMIT / * xid=103 * / | + -- + 4 rows in set (0.00sec) mysql > showbinlog events in 'mysql-bin.000011' from 190 limit 3 # specify offset (not statement) Is an event) +-+-+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-+- -- + | mysql-bin.000011 | 190 | Query | 1 | 293 | create table yong.tb1 (idint Name char (20) | | mysql-bin.000011 | 293 | Query | 1 | 357 | BEGIN | | mysql-bin.000011 | 357 | Query | 1 | 452 | insert into yong.tb1values (1) 'tom') | +-+-+ 3 rows in set (0.00sec)

View the binary log from the command line:

Since you cannot directly open and view the binary log using methods such as cat, you must use the mysqlbinlog command. However, it is recommended that you do not use this to open binary log files in use when you are performing mysql read and write operations; if you do not want to open flush logs. How the mysqlbinlog command is used:

[root@stu18 data] # mysqlbinlog mysql-bin.000017 # must be in the data directory / *! 50530 SET@@ session N.PSEUDOSLAVESLAVEMod 1 session / * / 50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! / # at 4 # where the incident began # 0:25:59 server id 1 end_log_pos 131009 Start: binlog v 4, server v 5.5.33-log created 131009 0:25:59 # Warning: thisbinlog is either in use or was not closed properly.BINLOG 'FzJUUg8BAAAAAAAAGsAABAAAQANS41LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA At the end of the end_log_pos event; which session thread of thread_id=12 created this statement; exec_time=0 execution time in seconds; error_code=0 error code 0 indicates that there is no SET timestamp 1381249596 Universe; # preset information (environment settings)

Export information for this database:

[root@stu18 data] # mysqlbinlog mysql-bin.000017 > / tmp/a.sql

Import information for this database:

[root@stu18 data] # mysql

< a.sql 删除二进制日志信息：二进制日志会记录大量的信息（其中包含一些无用的信息）。如果很长时间不清理二进制日志，将会浪费很多的磁盘空间。但是，删除之后可能导致数据库崩溃时无法进行恢复，所以若要删除二进制日志首先将其和数据库备份一份，其中也只能删除备份前的二进制日志，新产生的日志信息不可删(可以做即时点还原)。也不可在关闭mysql服务器之后直接删除因为这样可能会给数据库带来错误的。若非要删除二进制日志需要做如下操作：导出备份数据库和二进制日志文件进行压缩归档存储。删除二进制文件的方法如下： 1、删除所有的二进制日志（不可效仿）：使用RESET MASTER语句可以删除所有的二进制日志。该语句的形式如下： mysql>

Parsing: this operation is not recommended in a production environment at first; after all the binary logs are deleted, Mysql will recreate the new binary logs. The number of the new binary log starts at 000001.

2. Delete the binary log based on the file or point in time:

Grammatical form:

Mysql > PURGE {BINARY | MASTER} LOGS {TO 'log_name' | BEFORE datetime_expr}

Where TO'log_name' means to delete all other files before this file, or you can use BEFORE datetime_expr to specify when the binary files before this file have been deleted.

From this we can see that this way of cleaning up binary log files is very reasonable and will not lead to database errors.

Mysql > PURGEBINARY LOGS BEFORE'13-10-19 10 Query OK 26 sec; # use time to delete the binary log, 0 rowsaffected (0.05 log) Thank you for your reading, the above is the content of "how to view and delete the error log by MySQL". After the study of this article, I believe you have a deeper understanding of how to view and delete the error log of MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.