How to delete log in mysql

MySQL is a commonly used relational database management system nowadays. However, as the amount of data increases and the system runs, MySQL slowly slows down. At this time, it is necessary to clear the logs in MySQL to improve the performance of the database. performance.

There are four main logs in MySQL: error log, slow query log, binary log, and transaction log. Among these four types, if you want to clear it, you need to pay attention to some details. Before proceeding, it is best to make a backup. So let's take a look at the specific operations of deleting each log in MySQL.

1. Delete the error log

The MySQL error log is mainly used to record information when errors occur. Viewing the error log helps us understand the problems that occur during the operation of the system. When the log file is too large and needs to be cleared, you can do the following:

1.1 Check the path of the error log

Enter the command on the command line: SHOW VARIABLES LIKE '%log_error%';

will get the following results:

##Variable_nameValuelog_error/var/log/mysql/error.logAs you can see, the error log path of mysql is /var/log/mysql /error.log.

1.2 Clear the error log

Enter the command on the command line: echo "" > /var/log/mysql/error.log.

If you still want to confirm whether it is empty, you can check whether it is empty through the following command:

tail -f /var/log/mysql/error.log

2. Delete Slow query log

MySQL's slow query log is used to record long-executed query statements, which helps us understand some database performance issues. When the log file is too large and needs to be cleared, you can do the following:

2.1 Check the path of the slow query log

Enter the command on the command line: SHOW VARIABLES LIKE '%slow_query%' ;

will get the following results:

Variable_nameValueslow_query_logONslow_query_log_file/var/log/mysql/mysql-slow.logAs you can see, the slow query log path of MySQL is /var/log/mysql/mysql-slow.log.

2.2 Clear the slow query log

Enter the command on the command line: echo "" > /var/log/mysql/mysql-slow.log.

If you still need to confirm whether the log is cleared, you can enter:

tail -f /var/log/mysql/mysql-slow.log

3. Delete binary log

MySQL’s binary log is used to record database transaction operations and is used when performing database master-slave replication. Clearing the binary log requires special attention, and the operation needs to be performed without affecting the master-slave synchronization of the database.

3.1 Turn off the binary log

In the MySQL configuration file my.cnf, find the [mysqld] section and add the following line of code:

log-bin=mysql-bin #Enable binary log

binlog_format=mixed             #Format
server-id=1                         #MySQL server ID, unique

Restart the MySQL server so that the binary log will be opened. To turn off the binary log, you need to modify or comment out log-bin and binlog_format in my.cnf, and restart the MySQL server.

3.2 Clear the binary log

Without affecting the master-slave synchronization, you can clear the binary log in the following way.

First, check the file name and location of the binary log through the following command:

SHOW MASTER STATUS;

You will get the following results:

FilePositionBinlog_Do_DBBinlog_Ignore_DBmysql-bin.000001 107##

You can see that the position of the binary log is 107.

Then, clear the binary log through the following command:

RESET MASTER;

Then check the file name and location of the binary log again:

SHOW MASTER STATUS;

You can see that the generation of new binary logs has been restarted.

It should be noted that when performing operations, it should be confirmed that it will not affect the normal business operation of the MySQL server.

4. Delete transaction log

MySQL transaction log is a log unique to the InnoDB engine. It records the execution process of each transaction in the MySQL server. After a system failure, the transaction log can be used Recover MySQL server data. Again, it's a good idea to make a backup before proceeding.

4.1 View the transaction log file

In the MySQL configuration file my.cnf, find the [mysqld] section and add the following lines of code:

innodb_log_file_size=1024M #Set transactions Log file size
innodb_log_files_in_group=3 #Set the number of transaction log files

Restart the MySQL server so that the transaction log will be opened. To view the transaction log path, you can execute the following statement in MySQL:

SHOW VARIABLES LIKE '%innodb_log%';

You can see the transaction log file path of InnoDB.

4.2 Clear the transaction log

Before performing the operation, it is best to perform a backup to prevent data loss caused by incorrect operations.

First you need to stop the MySQL server, then delete the transaction log file, and finally restart the MySQL server. When starting the MySQL server, MySQL will automatically recreate new transaction log files.

The above is the operation of deleting various logs in MySQL. It should be noted that it is best to make a backup before performing the operation to prevent data loss caused by incorrect operations.

The above is the detailed content of How to delete log in mysql. For more information, please follow other related articles on the PHP Chinese website!