pouët.net

MySQL database backups - the quick fix

category: general [glöplog]
 
First you have this script (say mysql_backup.sh):
Code:#!/bin/sh MYSQL_USER=mysql_user MYSQL_PASS=mysql_pass MYSQL_PATH=/usr/mysql BACKUP_PATH=/home/analogue/backups/mysql TARGET=$BACKUP_PATH/$1_backup.sql echo "Backing-up database $1 in $TARGET..." rm -f $TARGET.3.bz2 mv $TARGET.2.bz2 $TARGET.3.bz2 > /dev/null mv $TARGET.1.bz2 $TARGET.2.bz2 > /dev/null mv $TARGET.bz2 $TARGET.1.bz2 > /dev/null $MYSQL_PATH/bin/mysqldump -a -u$MYSQL_USER -p$MYSQL_PASS "$1" > $TARGET bzip2 $TARGET


...which gives you the ability to do this:
Code:./mysql_backup.sh pouet_db


...and with that, you can make a:
Code:/etc/cron.daily/backup_pouet_db


...with just this:
Code:#!/bin/sh /usr/local/my_scripts/mysql_backup.sh pouet_db


For the lazy or illiterate, the above will make almost certainly sure that backups of the pouet database will be kept for the last 4 days in /home/analogue/backups/mysql, and compressed too so that they don't take much space. Hope that helps.
added on the 2003-10-20 19:00:52 by moT moT
Duh, the {CODE} tag is not so clever after all. Anyone interested in the script, drop an email.
added on the 2003-10-20 19:03:24 by moT moT
that would actually require analogue to give a damn about the site.
added on the 2003-10-20 19:21:33 by Gargaj Gargaj
could someone tell more accurantly what happened? i read from the oneliners the "bug" is easy to fix for scene.org staff members but i haven't noticed what the actual problem is. :)
added on the 2003-10-21 08:15:59 by melw melw
melwyn: scene.org crashed, database got corrupted, last becakup was 2 months old.
added on the 2003-10-21 13:07:33 by Gargaj Gargaj
a slight change which would enable a very primitive log thru the cron mail would be this:
Code: mv $TARGET.2.bz2 $TARGET.3.bz2 1> /dev/null

in which case we redirect normal output in /dev/null and errors in standard output
added on the 2003-10-21 13:22:41 by packo packo
Pouet has been destroyed, it's like my universe has been ;(
added on the 2003-10-21 13:31:20 by Optimus Optimus
also recommended is the "-e" switch to the mysqldump, enables a much smaller output and faster loading.
added on the 2003-10-21 14:12:39 by Gargaj Gargaj

login