Shulou(Shulou.com)06/01 Report--

After installing mysql with ports, after a period of time found that / var space is insufficient, check, you will find that mysql-bin.000001, mysql-bin.000002 and other files take up space, so what are these files? This is the operation log of the database, such as UPDATE a table, or DELETE some data, even if the statement does not have matching data, the command will be stored in the log file, including the execution time of each statement, will also be recorded.

This is done for the following two main purposes:

1: data recovery

If there is something wrong with your database and you have a backup before, you can look at the log file to find out which command caused the problem with your database and find a way to recover the loss.

2: synchronize data between master and slave servers

All operations on the master server are in the log, and the slave server can do so according to the log to ensure that the two are synchronized.

There are two ways to deal with it:

1: there is only one mysql server, so you can simply comment out this option.

Vi / etc/my.cnf comment out the line log-bin inside and restart the mysql service.

2: if your environment is a master-slave server, you need to do the following.

A: on each secondary server, use SHOW SLAVE STATUS to check which log it is reading.

B: use SHOW MASTER LOGS to get a series of logs on the primary server.

C: determine the earliest log of all secondary servers, this is the target log, and if all secondary servers are updated, it is the last log on the list.

D: clean up all logs, but not the target log, because you have to synchronize with it from the server.

The method to clean up the log is:

PURGE MASTER LOGS TO 'mysql-bin.010'

PURGE MASTER LOGS BEFORE '2008-12-19 2121 purl 0000'

If you are sure that the slave server has been synchronized, just like the master server, you can delete these files directly by RESET MASTER.

Before I found that I had 10G of server space, I only had 5G left in a few days, and the file I uploaded was only a few hundred megabytes. What on earth took up so much space?

Directory web root directory is put in / home, all the files add up to less than 300m, and the server has occupied nearly 5G of space, scary, and finally through my step-by-step query, it turns out that this folder occupies a lot of space resources

I see, the var directory under the mysql folder takes up the most space, so what's in it? Let's take a look:

What is it that so many mysql-bin.0000X files have been found? It turns out that this is the operation log file of mysql. I only tens of M database, the operation log is almost 3 G size.

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

Red indicates the command entered.

[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 >

All right, let's take a look at 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, take a look, the whole mysql directory only occupies 163m! OK, no problem, since the mysql-bin.0000X log file takes up so much space, the existence of the meaning is not very great, then let's not let it be generated.

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

Found the my.cnf that is mysql configuration file, we will comment out the log-bin=mysql-bin this can.

# 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 terrible. I've only moved to this new VPS for about 20 days, and in less than a month, the log files are nearly 3 gigabytes in size. If I don't clear the log files for a month or two, it's okay!

Cleaning method of mysql-bin.000001 files in MySql data directory

Write in the MYSQL installation directory, the data directory stores all the database files, there are some mysql-bin.000001,mysql-bin.000002,mysql-bin.000003-like files in this directory take up a lot of space, these files are database operation log files, can be cleared. Cleanup method:

Under cmd, go to the bin directory under mysql, type 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 deletes the log files, and if you don't want to generate them, you can do this:

Open the my.ini in the mysql directory, find log-bin=mysql-bin, and comment it out.

# log-bin=mysql-bin

(it is best to close the MYSQL database temporarily when modifying the database configuration file)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.