[TriLUG] MySql backup

Jerry M. Howell II jmhowell at jmhowell.com
Fri Mar 21 16:25:14 EST 2003


On Fri, Mar 21, 2003 at 09:36:08AM -0500, Jeremy Portzer wrote:
> On Fri, 2003-03-21 at 08:03, Michael Alan Dorman wrote:
> > 
> > An alternative to 'mysqldump -a' is a shell script that uses a
> > for-loop to dump all the databases individually.  Something like
> > (untested, but it's cobbled together from scripts I actually use):
> > 
> > for i in `mysql -B -e "show databases" | tail +2`; do
> >     mysqldump $i | gzip -9 > $i.`date --iso-8601=date`.gz
> >     ls -t $i.* | tail +7 | xargs rm
> > done
> > 
Thank you, I might try this. Someone emailed me with a script that
they use. It uses a command called mysqlhotcopy. is this beter than
mysqldump? At least on a system that suports it or should I stick
with mysqldump? Or, mabe I should keep this script to move data
from servers that don't support mysqlhotcopy. Any comments are welcome.

> 
> 	* stop applications gracefully
> 	* stop databases
> 	* backup by copying files, or mysqldump (without stopping the DB
> server)
> 	* restart databases, restart applications

This information is very helpful. This might explain why I got a
corupted database right in the middle.

> Enterprise database systems use transactions to avoid this sort of
> problem.  A transaction can encapsulate updates to several tables at a
> time, so when the backup is performed it will backup either before or
> after the transaction, not in the middle.
> 
I'm not familiar with how to do this but I probably need to learn this
as we have had someone as far away as finland (out of all places
overseas, the home of the linux creator himself). Any recomendations
where I can start googleing on this or any websites? we are light around
midnight till 5 am, thats when I choose to rsync the system so I'm ok
for now but as our system grows, you never know.

Thank you all
-- 
Jerry M. Howell II



More information about the TriLUG mailing list