Re: database issues

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

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
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users