[TriLUG] MySql backup

[Jeremy Portzer]

On Fri, 2003-03-21 at 09:25, Turnpike Man wrote:
> 
> --- Michael Thompson <thompson at easternrad.com> wrote:
> > > This will *ONLY* work is you stop mysqld beforehand.
> > Oops!  Forgot to add that important point!  Thanks!
> > 
> > --mwt
> > 
> > > 
> > > 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
> > > 
> 
> As I have just learned, my copy method was fortunate that the databases I have
> are not very active, thus probably no activity when I performed the copy!
> 
> So, the above script (which I could have never written on my own)... that is
> for online backups of mysql?  Then what process is used to restore?
> 

Don't run scripts without understanding what they do.  To break down
Michael's script line by line:

for i in `mysql -B -e "show databases" | tail +2`; do

# the above runs a MySQL command to determine what databases you have,
# pipes it through a tail command to remove 2 lines of header
# information, and then loads the output into the "for" loop 
# $i contains the database name throughout the loop

mysqldump $i | 		# runs msyqldump -- output goes to stdout
gzip -9 		# compresses at a high compression level
> $i.`date --iso-8601=date`.gz     #final output gets named with the
# database name, and the current date in iso-8601 format (see man date)

ls -t $i.* | 		# lists $i files sorted by modification time
tail +7 | 		# removes the last 7 lines from the output
xargs rm		# sends the output through "rm" to delete

done	# end of loop

So what this does is runs a mysqldump on each database, names them with
neato names that mach the current date, then deletes all the previous
backups older than 7 days.  You need to run this in a new directory,
otherwise that last section might delete other files.

Shell scripting is awesome!

--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/2083a62a/attachment.pgp>