Mysql has four kinds of log files: 1. Binary log file, which records various operations of the database in binary form, but does not record query statements; 2. Error log file, which mainly records the MySQL server Information during the startup and stop process, failures and exceptions that occur during server operation, etc.; 3. General query log files, recording server startup and shutdown information, client connection information, updates, SQL statements for query data records, etc. ; 4. Slow query log file, mainly records operations whose execution events exceed the specified time.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
The log is an important part of the database and is mainly used to record the running status, daily operations and error information of the database.
In MySQL, log files can be divided into 4 types: binary log files, error log files, general query log files and slow query log files.
Binary log file: This log file records various operations of the database in binary form, but does not record query statements. It is mainly used to record changes in the database, that is, DDL and DML statements of SQL statements, and does not include data record query operations.
Error log file: mainly records information during the startup and stop process of the MySQL server, failures and exceptions that occur during the operation of the server, etc.
General query log file: This log records the startup and shutdown information of the MySQL server, client connection information, updates, SQL statements for querying data records, etc.
Slow query log file: records operations whose execution events exceed the specified time. By analyzing the slow query log with tools, you can locate the performance bottleneck of the MySQL server.
Among the log files supported by MySQL, except for binary log files, other log files are text files. By default, MySQL only starts the error log file, and other logs need to be started manually.
Using logs has advantages and disadvantages. After logging is enabled, although the performance of the MySQL server can be maintained, it will reduce the execution speed of MySQL. For example, in a MySQL with frequent query operations, recording general query logs and slow query logs will take a lot of time.
Log files also take up a lot of hard disk space. For databases with a very large number of users and very frequent operations, the storage space required for log files is even larger than that required for database files. Therefore, whether to enable logs and what type of logs to enable depends on the specific application.
Extended knowledge: MySQL uses binary logs to restore the database
The binary log records all user operations on database changes, such as INSERT statements, UPDATE statements, CREATE statements, etc. . If the database loses data due to improper operation or other reasons, you can use the binary log to view user operations within a certain period of time, and restore the database in combination with database backup.
The following will introduce in detail the method of using binary logs to restore the database.
When the database is accidentally damaged, you should first use the most recent backup file to restore the database. In addition, after the backup, the database may have been updated, and the binary log can be used to restore it. Because the binary log stores statements that update the database, such as UPDATE statements, INSERT statements, etc.
The command to restore the database from the binary log is as follows:
mysqlbinlog filename.number | mysql -u root -p
The above command can be understood as: first use the mysqlbinlog command to read the contents of filename.number, and then use the mysql command to restore the contents to in the database.
Tips: Although the binary log can be used to restore the MySQL database, it also takes up a lot of disk space. Therefore, after backing up the MySQL database, the binary logs before the backup should be deleted. If an exception occurs after the backup, resulting in database data loss, it can be restored through the binary log after the backup.
When using the mysqlbinlog command to perform a restore operation, the smaller number must be restored first. For example, mylog.000001 must be restored before mylog.000002.
Example 1
The following uses binary logs to restore the database, the code is as follows:
mysqlbinlog mylog.000001 | mysql -u root -p mysqlbinlog mylog.000002 | mysql -u root -p mysqlbinlog mylog.000003 | mysql -u root -p mysqlbinlog mylog.000004 | mysql -u root -p
[Related recommendations:mysql video tutorial]
The above is the detailed content of Which is the log file of mysql?. For more information, please follow other related articles on the PHP Chinese website!