Re: database issues

Karl Latiss wrote:
> On Tue, 2007-08-28 at 18:29 -0700, Kurt Buff wrote:
> > Karl Latiss wrote:
> > > On Tue, 2007-08-28 at 09:51 -0700, Kurt Buff
wrote:
> > > > I have Maia running with postfix, etc.,
on a 68gb 
> > > partition. I had the system keeping 30 days
of quarantine, 
> > > but have just today lowered that down to 25
days, and am 
> > > currently running
'expire-quarantine-cache.pl', as disk space 
> > > is almost full - about 3gb free at the
moment. I'll be 
> > > backing it down in 5 day increments to get
quarantine down to 
> > > 15 days, in the hopes of freeing disk space.
> > > > 
> > > > I'm wondering, though, if something is
amiss with the MySQL 
> > > installation on this box, as there are
database files in 
> > > /var/db/mysql that have date stamps well
older than 30 days, 
> > > totalling around 26gbytes.
> > > > 
> > > Presuming InnoDB the only way to reduce the
datafile size is 
> > > to dump and
> > > restore. InnoDB data files do not
automatically reduce in 
> > > size when data
> > > is deleted.
> > > 
> > > -- 
> > > Karl Latiss <karl.latissatvert.com.au>
> > > Atvert Systems
> > 
> > I kind of suspected that, and yes, they are
InnoDB. I've 
> found a 70gb USB2 drive that I can attach, and use as 
> scratch, so this is looking like soonish kind of thing,

> unless my deletion of older quarantine items has
helped. I'll 
> know more about that tomorrow, but I've been monitoring
disk 
> space, and it's still dwindling - I'm down to 2.5gbytes
free 
> as of this writing, down from 2.9gbytes when I asked
the question.
> > 
> > I think I'll start looking at how to do this - any

> suggested URLs, in case my google-fu is weak?
> > 
> I just dump and restore:
> 
> mysqldump -Q --add-drop-table -u <username> -p -h
<hostname> 
> <dbname> >
> dbname.sql
> drop database <dbname>
> create database <dbname>
> mysql -u <username> -p -h <hostname>
<dbname> < dbname.sql
> 
> This assumes InnoDB is your default table type.
> 
> The drop removes the datafiles and create then creates
them. Keep in
> mind that if you have other InnoDB based databases
using this 
> same mysql
> server then the data files will not be removed and so
no space will be
> saved. If you do share the database server it's
probably worth looking
> at using the innodb_file_per_table setting.
> 
> Karl.

Well, I hope you have a word of advice here. I've done:

----------
     mysqldump -Q --add-drop-table -u root -p maia
/mnt/dump/maia.sql
     mysql -u root -p
          drop database maia;
          quit;
     /usr/local/etc/rc.d/mysql-server stop
-----------

After that, I took a look at /var/db/mysql, and still see 88
mysql-bin.* files in there with 57g worth of data. Should
that have disappeared? I assume I can't just nuke them,
because the other databases, namely information_schema,
cluster, mysql, and test - correct?

I then followed with 

----------
     /usr/local/etc/rc.d/mysql-server stop
     mysql -u root -p
----------

to start mysql back up again.

"Now what?", is my plaintive cry.

Kurt
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

Re: database issues

Thread: Re: database issues