A lot of big files in /var/lib/mysql/ => /var full!
On 4/23/2010 9:25 AM, Jarry wrote:
today I discovered mysql is slowly eating my disk space!
Actually, one web-server already had /var 98% full.
After a little search I found more than 200 files in
/var/lib/mysql/mysqld-bin.000001 -~ 000214 of various
size, but together take ~10GB of disk space. Yet phpmyadmin
shows I have only one database ~15MB. So what is all this
mysqld-bin.* crap doing in /var/lib/mysql? I increased
/var, but it does not solve the problem. How can I prevent
mysql from filling up my whole /var partition?
I looked into /var/log/mysql, mysql.err and mysql.log
are empty, in mysqld.err there are these messages:
100423 15:47:05 [Warning] No argument was provided to --log-bin, and
--log-bin-index was not used; so replication may break when this MySQL
server acts as a master and has his hostname changed!! Please use
'--log-bin=mysqld-bin' to avoid this problem.
InnoDB: The InnoDB memory heap is disabled
InnoDB: use atomic builtins.
100423 15:47:05 InnoDB: Started; log sequence number 0 43715
100423 15:47:05 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.90-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306
Gentoo Linux mysql-5.0.90-r2
I must admit I didt not play with mysql configutation much,
just followed gentoo MySQL Startup Guide and everything
seemed to work...
Add this line to your /etc/init.d/my.cnf and it'll need to be in the
expire_logs_days = 7
Then log into Mysql and run this command to set the variable without
having to restart Mysql.
SET GLOBAL expire_logs_days=7;
While logged in you can immediately expire the old logs with the
following command. Even though you've set seven days as the max time
Mysql will not expire the old logs until the current log reaches 1GB and
it is time to create a new log.
PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);
Seven days works well for most home systems, but you can set it higher
or lower depending on your situation. It is generally not a good idea to
turn bin logs off because there are cases when it's easier to recover
data or fix tables if you have current logs.