Solution for clearing mysql log-bin

Some time ago, my work on the project was very busy. Recently, I have to make department adjustments to start a new project. I am very excited to learn a lot of new knowledge. I came back from work today and checked the VPS status. I found that the VPS space is more than 1 GB! The first reaction was intrusion, but I did not find any abnormal logon after reading the log. In addition, I usually use the private key to log on to VPS without a password, it may not be too large to intrude. I am very confused, because the system file usage should be more than 3G. I have not put any large files in the VPS at ordinary times, and should not be so much space at once. So we started to look for du and finally found the culprit! It was originally caused by the mysql log file.

After installing mysql and running for a while, a bunch of mysql-bin.000 like *** appears under the mysql directory, which is arranged from the mysql-bin.000001 and occupies a lot of hard disk space, up to a dozen G .. The original mysql-bin.000001, mysql-bin.000002 and other files are Database operation logs, such as UPDATE a table, or DELETE some data, even if the statement does not match the data, this command will be stored in the log file, the execution time of each statement is also recorded. What are these files like mysql-bin.00001 mainly used? 1. Data recovery

If there is a problem with your database, and you have had a backup before, you can check the log file to find out which command causes a problem with your database and find a way to recover the loss.

2. Data synchronization between master and slave servers

All operations on the master server are recorded in the log. The slave server can follow the log to ensure two synchronization.
3. Clearing methods

Run/usr/local/mysql/bin/mysql-u root-p to log on and execute:
Reset master;

If you only have one mysql server, find the my. cnf file vim/etc/my. cnf under/etc/.

# Log-bin = mysql-bin
# Binlog_format = mixed

Comment out the two lines, delete all the log files in the var directory under mysql, and restart the mysql service.

However, if you have configured a master-slave server, you need to perform 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 series of LOGS on the MASTER server.
C: identify the earliest log among all slave servers. This is the target log. If all slave servers are updated, it is the last log in the list.

D: clear all the logs, but not the target logs, because the slave server needs to synchronize with it. Simply put, the MySQL transaction logs under these MySQL directories are like files in mysql-bin.000. It is safe to delete the binlog that has been taken by the Replication Server. Generally, when the network is in good condition, the latest one is enough.

Note: The deletion of the mysql-bin log (mysql-bin.00001) causes mysql to fail, which is certainly not modifying the configuration file, which is problematic.

Edit my. cnf (usually in the etc directory)
Comment out log-bin = mysql-bin. (Add # above)
If not, comment out binlog_format = mixed!
Start mysql:/etc/init. d/mysql start
Remove the comment and restart mysql:/etc/init. d/mysql restart.