Where is mysql using my drive space?

letharion
letharion used Ask the Experts™
on
Mysql seems to 8GB of data.
du -hs /var/lib/mysql/
8.1G    /var/lib/mysql/

Problem is I need that space, and I can't figure out what uses all of it.
All databases I have should be really small, which I try to prove below
$ mysqldump -u root -p --all-databases > alldatabases.sql
Enter password:
$ ls -lh alldatabases.sql
-rw-r--r-- 1 aegir aegir 32M Jul  3 14:41 alldatabases.sql

So why does mysql have a number of gigabyte-sized mysqld-bin.xxxxxx?
# du -hsm /var/lib/mysql/* | sort -n | tail
92      /var/lib/mysql/mysqld-bin.000105
274     /var/lib/mysql/mysqld-bin.000121
682     /var/lib/mysql/mysqld-bin.000116
761     /var/lib/mysql/mysqld-bin.000102
928     /var/lib/mysql/mysqld-bin.000120
1026    /var/lib/mysql/mysqld-bin.000112
1026    /var/lib/mysql/mysqld-bin.000113
1026    /var/lib/mysql/mysqld-bin.000114
1026    /var/lib/mysql/mysqld-bin.000115
1026    /var/lib/mysql/mysqld-bin.000119
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this commands (from MySql server command line):

flush logs;
reset master;

You can also disable mysqld-bin logs in commenting out log-bin in conf file

Judging by the size of your log files,it seems that the server you are using is a MASTER MySQL server (there also must be a SLAVE) for MySQL replication.For replication, the binary log is used on master replication           servers as a record of the statements to be sent to slave           servers. The master server sends the events contained in its           binary log to its slaves, which execute those events to make           the same data changes that were made on the master
If you are using your server as master for replication, in your my.cnf file for MySQL server,there should be a section with lines like these:
log-bin = /var/lib/mysql/mysqld-bin.logbinlog-do-db=your_database                 //server-id=1                                          //probably
You can purge old bin-log files with statement:
PURGE BINARY LOGS BEFORE '2010-04-02 22:00:26';
which will purge logs made before 2nd April 2010,10PM. Make sure that your dates are correct,and you should not purge the logs for at least few days long. Purging the logs once in few weeksa should keep them relatively small,or you can do it by putting a line in a cron.
Other option is to add in my.cnf:
expire_logs_days=7
which will keep only last 7 days of logs.I just checked it and it works-it has deleted all old bin-log files on my server.

If your server is not a master server and there is no replicatiuon set,then you can safely comment out the log-bin line in your MySQL configuration file.
Good luck!


Author

Commented:
I appreciate your help. Unfortunately I haven't really had a lot of time to devote the related project. Since I need to deal with the question somehow, and you've helped me, I'm closing it. Might open a new one if I have follow up questions in the future.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial