[gentoo-user] A lot of big files in /var/lib/mysql/ => /var full!

[gentoo-user] A lot of big files in /var/lib/mysql/ => /var full!

[Jarry]

Hi,
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...

Jarry

-- 
_______________________________________________________________
This mailbox accepts e-mails only from selected mailing-lists!
Everything else is considered to be spam and therefore deleted.

Re: [gentoo-user] A lot of big files in /var/lib/mysql/ => /var full!

[Paul Hartman]

On Fri, Apr 23, 2010 at 11:25 AM, Jarry <mr.jarry@gmail.com> wrote:
> Hi,
> 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?

It is the binary transacton log. In your mysql config file you can set
the maximum age or amount of disk space these logs are allowed to use,
or disable it entirely.

Re: [gentoo-user] A lot of big files in /var/lib/mysql/ => /var full!

[kashani]

On 4/23/2010 9:25 AM, Jarry wrote:
> Hi,
> 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...
>
> Jarry
>

Add this line to your /etc/init.d/my.cnf and it'll need to be in the 
[mysqld] section.

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.

kashani