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

After installing MySQL using ports, I found that /var was running out of space after a while. After checking, I found that files such as mysql-bin.000001 and mysql-bin.000002 were taking up the space. So what are these files for? This is the operation log of the database. For example, if you UPDATE a table or DELETE some data, even if there is no matching data for the statement, the command will be stored in the log file. The execution time of each statement will also be recorded.

This has two main purposes:

1: Data recovery If your database has problems and you have backed it up before, you can look at the log file to find out which command caused your database to have problems and find ways to recover the losses.
2: Synchronize data between master and slave servers All operations on the master server are recorded in the log, and the slave server can perform operations based on the log to ensure that the two are synchronized.

There are two treatment methods:
1: If there is only one MySQL server, you can simply comment out this option.
vi /etc/my.cnf, comment out the log-bin line, and restart the mysql service.
2: If your environment is a master-slave server, you need to do the following operations.
A: On each slave server, use SHOW SLAVE STATUS to check which log it is reading.
B: Use SHOW MASTER LOGS to obtain a list of logs on the master server.
C: Determine the earliest log among all slave servers. This is the target log. If all slave servers are updated, it is the last log on the list.
D: Clear all logs, but not the target log, because the slave server still needs to synchronize with it.

The method to clean up 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 and is the same as the master server, you can directly RESET MASTER to delete these files.

I found that my 10G server space was reduced to 5G after a few days of use, and the files I uploaded were only a few hundred MB. What on earth took up so much space?

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

So, it is the var directory under the mysql folder that takes up the most space. What is in it? Let's take a look:

I found so many mysql-bin.0000X files. What are they? They are the operation log files of mysql. My database is only a few dozen MB, but the operation log is almost 3 GB in size.

How to delete mysql-bin.0000X log files?

Red indicates entered commands.

[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 the mysql folder takes 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, let's take a look. The entire mysql directory only takes up 163M! OK, no problem. Since the mysql-bin.0000X log file takes up so much space and its existence is not particularly meaningful, let's not let it be generated.

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

Found my.cnf, the mysql configuration file, and commented out log-bin=mysql-bin.

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

Restart mysql.

OK, the operation is now complete. No more N GB of log files will be generated for a database size of only a few dozen MB.

These log files are horrible. I have only moved to this new VPS for about 20 days, and the log files are nearly 3G in less than a month. If I don't clear the log files in a month or two, it will be terrible!

MySql data directory mysql-bin.000001 file cleaning method

In the MYSQL installation directory, the data directory stores all database files. In this directory, there are some files like mysql-bin.000001, mysql-bin.000002, and mysql-bin.000003 that take up a lot of space. These files are database operation log files and can be cleared. Removal method:
In cmd, go to the bin directory under mysql and enter mysql -u root -p; then enter the password, and enter reset master after successfully entering.
mysql> reset master;
Query OK, 0 rows affected, 1 warning (0.20 sec)
This will delete the log files. If you do not want to generate these log files, 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)