How to delete mysql log

In the MySQL database management system, logs are an important tool for recording database operations. Every database operation is logged, helping database administrators with troubleshooting, performance tuning, and data security.

However, as time goes by and the usage of the database increases, the size of the log file will continue to grow, and even the database may run slowly or crash due to overly large log files. In order to avoid this problem, sometimes it is necessary to delete the MySQL log manually.

The following will introduce how to delete various logs of MySQL.

1. Delete binary logs (Binary Logs)

Through the binary logging function, MySQL can record each executed SQL statement to a specific file in binary format , thus facilitating data recovery and data backup. Binary log files are usually saved on the master server and passed to the slave server through replication.

If you do not need the binary log function, or want to clean up the binary logs that are no longer needed, you can use the following steps to delete them.

Step 1: Use the following command to view the current binary log list:

SHOW BINARY LOGS;

Step 2: Use the following command to turn off the binary log function:

SET sql_log_bin=0;

Step 3: View the binary log list again and use the following command to confirm which log files need to be deleted:

SHOW BINARY LOGS;

Step 4: Use the following command to delete the specified binary log file:

PURGE BINARY LOGS TO 'binlog_file_name';

For example, if you want to delete the log files named binlog.000010 and binlog.000011, execute the following Command:

PURGE BINARY LOGS TO 'binlog.000011';

Note: Deleting log files is a very dangerous operation. Please be sure to carefully confirm whether the files to be deleted are correct and operate with caution. .

2. Delete Error Logs

The MySQL error log file contains error and warning information during the execution of the MySQL server. This information is very important and helps managers troubleshoot when problems arise. But when the error log file is too large, it will also have an impact on performance.

The following are the steps to delete the error log file:

Step 1: Use the following command to view the path and file name of the current error log file:

SHOW VARIABLES LIKE 'log_error' ;

After executing the above command, you will get the storage path and file name of the error log file. For example, the output is:

log_error /var/log/mysql/error.log

Step 2: Back up the current error log file to prevent the need to restore it after accidental deletion.

Step 3: Create an empty file as a new error log file using the following command:

sudo touch /var/log/mysql/error.log

Step 4: Use the following command to restart the MySQL service to make the new empty log file take effect:

sudo systemctl restart mysql

3. Delete Slow Query Logs

MySQL slow query log records SQL statements whose execution time exceeds the preset time threshold. These queries may affect MySQL performance. By analyzing slow query logs, you can find SQL statements that affect MySQL performance.

When the slow query log file is too large, it will also affect performance. You can delete it through the following steps:

Step 1: Use the following command to view the path and file name of the current slow query log:

SHOW VARIABLES LIKE 'slow_query_log_file';

Execute After the above command, you will get the storage path and file name of the slow query log file. For example, the output is:

slow_query_log_file /var/log/mysql/mysql-slow.log

Step 2: Back up the current slow query log file.

Step 3: Use the following command to clear the slow query log file:

sudo echo "" > /var/log/mysql/mysql-slow.log

Step 4 : Use the following command to re-enable the slow query log function:

SET GLOBAL slow_query_log = 1;

Note: Clearing the slow query log file will not delete the file, but clear the contents. Slow query log files need to be manually deleted periodically to free up disk space.

Summary: In MySQL, log files are an important tool for recording database operations, but as time goes by and database usage increases, log files may become too large, causing the database to run slowly or crash. Therefore, log files need to be cleaned regularly. However, cleaning log files is a very dangerous operation. Be sure to operate with caution and back up important log file contents before operation.

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