[Maia-users] database issues

[Kurt Buff]

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.latiss at atvert.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