What file is mysql-bin.000001 in mysql? Can it be deleted?

After installing mysql with ports, I found that /var space was insufficient after a period of time. I checked and found that files such as mysql-bin.000001 and mysql-bin.000002 took up space. So what are these files for? This is the operation log of the database, such as UPDATE a table, or DELETE some data. Even if the statement has no matching data, the command will be stored in the log file, and the execution time of each statement will also be recorded.

This is done for two main purposes:

1: Data Recovery
If there is a problem with your database, and you have backups before, you can look at the log file to find out which command caused your database to fail, and try to recover the loss.
2: Synchronize data between master and slave servers
All operations on the master server are logged, and the slave server can follow this log to ensure that the two are in sync.

There are two processing methods:
1: There is only one mysql server, then you can simply comment out this option.
In vi /etc/my.cnf, comment out the log-bin line in it and restart the mysql service.
2: If your environment is a master-slave server, then you need to do the following.
A: On each slave, use SHOW SLAVE STATUS to check which log it is reading.
B: Use SHOW MASTER LOGS to get a series of logs on the master server.
C: Determine the oldest log among all the slave servers. This is the target log. If all the slave servers are updated, it is the last log on the list.
D: Clean up all logs, but exclude the target log, because the slave server has to synchronize with it.

The method of clearing the log is:

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2008-12-19 21:00:00';

If you are sure that the slave server has been synchronized, it is the same as the master server, then you can directly RESET MASTER to delete these files.

I found out that my server space was 10G, but after a few days, I had 5G left, and the files I uploaded were only a few hundred megabytes. What is it that takes up so much space?

The web root directory of the directory is placed in /home, and all the files add up to less than 300M, and the server has occupied nearly 5G of space. It's scary. Finally, I checked step by step and found out that it was this folder that occupied the space. a lot of space resources

It turns out that the var directory under the mysql folder takes up the most space. What's in it? Let's take a look:

I found so many mysql-bin.0000X files, what is this? It turns out that this is the operation log file of mysql. I only have a database of dozens of M, and the operation log is almost 3G in size.

How to delete the mysql-bin.0000X log file?

Red indicates the entered command.

[root@jiucool var]# /usr/local/mysql/bin/mysql -u root -p
Enter password: (enter password)
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 264001
Server version: 5.1.35-log Source distribution

Type ‘help;’ or ‘/h’ for help. Type ‘/c’ to clear the current input statement.

mysql> reset master; (clear log files)
Query OK, 0 rows affected (8.51 sec)

mysql>

Ok, let's check how much space does the mysql folder take up?

[root@jiucool var]# du -h –max-depth=1 /usr/local/mysql/
37M /usr/local/mysql/var
70M /usr/local/mysql/mysql-test
15M /usr/local/mysql/lib
448K /usr/local/mysql/include
2.9M /usr/local/mysql/share
7.6M /usr/local/mysql/libexec
17M /usr/local/mysql/bin
11M /usr/local/mysql/docs
2.9M /usr/local/mysql/sql-bench
163M /usr/local/mysql/

Well, take a look, the entire mysql directory occupies only 163M in size! OK, no problem, since the mysql-bin.0000X log file occupies such a large space, the meaning of existence is not particularly large, so let's not let it be generated.

[root@jiucool var]# find / -name my.cnf

Find my.cnf, the mysql configuration file, we can comment out the log-bin=mysql-bin line.

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

Restart mysql.

OK, so far, the operation is complete. In the future, N G log files will not be generated because of the database size of dozens of M.

These log files are terrifying. It’s only been about 20 days since I moved to this new VPS, and the log files are nearly 3 GB in size in less than a month. If I don’t clear the log files in a month or two, that’s fine!

MySql data directory mysql-bin.000001 file cleaning method

Write in the MYSQL installation directory. The data directory stores all the database files. In this directory, there are some files like mysql-bin. It is the operation log file of the database and can be cleared. Clear method:
Go to the bin directory under mysql under cmd, enter mysql -u root -p; then enter the password, enter reset master after successfully entering.
mysql> reset master;
Query OK, 0 rows affected, 1 warning (0.20 sec)
This deletes the log files, if you don't want these log files to be generated you can do this:
Open my.ini in the mysql directory, find log-bin=mysql-bin and comment it out.
#log-bin=mysql-bin
(It is best to temporarily close the MYSQL database when modifying the database configuration file)