[Maia-users] database issues

[Kurt Buff]

I'm a bad boy, to be replying to myself, but, what the heck. It's late, and I need to document this before I leave...


Kurt Buff wrote:
> 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

OK - I did a good dump to disk, after setting up my NFS server, and making the directory 777.

I then took the deep plunge, by deinstalling mysql and ripping out the directory entirely, then upgrading from ports, and restoring from the dump. Worked like a charm! I ran into interesting problems with the passwords in the mysql for vscan, config.php, amavisd.conf and maia.conf - I found the error messages in configtest.pl confusing, due to my ignorance, but the error message on the web page was a real stumper for a while - "DB Error: connect failed" - just a tad vague. 

Plus, I uncovered a little problem with my postfix master.cf - I don't know how it was working before, but it was a quick fix.

I'm not a fan of late nights like this... :)

Thanks to Karl for his help - now I'm going to monitor my disk space much more carefully, and if it starts growing again, I'm going to get real interested in why.

Kurt