[TriLUG] MySql backup

[Jeremy Portzer]

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
> 

Note that although mysqldump will give you consistent output on a
table-to-table basis -- there won't be any corrupted records in the
table -- it might not be a safe way to backup your application. 
MySQLdump will safely export the contents of EACH table, but suppose an
application was in the midst of updating rows in several tables?  If you
backup the DB in the middle of this, you could still have inconsistent
data on the application level.   You really should do this:
	* stop applications gracefully
	* stop databases
	* backup by copying files, or mysqldump (without stopping the DB
server)
	* restart databases, restart applications
Of course once you get a nifty script to do this, this can happen at
3:00 AM when traffic is light.  If you serve people around the world so
there is no "light" period this becomes harder.

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.

--Jeremy

-- 
/=====================================================================\
| Jeremy Portzer       jeremyp at pobox.com       trilug.org/~jeremy     |
| GPG Fingerprint: 712D 77C7 AB2D 2130 989F  E135 6F9F F7BC CC1A 7B92 |
\=====================================================================/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
URL: <http://www.trilug.org/pipermail/trilug/attachments/20030321/f5f70e74/attachment.pgp>