Shell MySQL Backups - What's your solution?

Question

I'm looking for a great solution to backup my MySQL databases - all of them. I'd like each one in at least it's own file. I need FTP ability later, but not right now. Right now I just need it to save on the same server that it's running on.

What do you use?

update

I suppose I should be clearer in what I'm asking for... I'm looking for a bash script that will utilize mysqldump to creat individual backup files for each database or an individual file for a table if the table is large enough.

Answer 1

You can use this script:

#!/bin/bash
BACKUP_DIR="/opt/backup"
MYSQL_USER=your_user
MYSQL_PASS=your_pass
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

DB_LIST="$($MYSQL -u $MYSQL_USER -p$MYSQL_PASS -Bse 'show databases')"
for db in $DB_LIST;
do
    BACKUP_SUBDIR="$BACKUP_DIR/mysql_`date +%Y-%m-%d`"
    BACKUP_FILE="$BACKUP_SUBDIR/$db.gz"
    if [ ! -d $BACKUP_SUBDIR ]; then
        mkdir -p $BACKUP_SUBDIR
    fi
    if [ -f $BACKUP_FILE ]; then
        unlink $BACKUP_FILE
    fi
    $MYSQLDUMP -u $MYSQL_USER -p$MYSQL_PASS $db | $GZIP -9 > $BACKUP_FILE
done

Answer 2

I've been using this AutoMySQLBackup script for my mysqldumps. Does daily, weekly, monthly backups and rotations. Quite handy.

Answer 3

I use a simple Bash script that does a mysqldump and then a rsync (easily use ftp) to a another offiste machine.

very simple yet it works!

no need to take the database down either.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Luc

Answer 4

This daily backup will create a backup of your db and it will create 4 day backups and rotate them. You can then backup that folder to a remote site.

#!/bin/bash -x
#
# Marco Maldonado MySQL Backup
# Version 1.0 September 9 2008
# comments [email protected]

MYSQL=`which mysql`
MYSQLDUMP=`which mysqdump`
BACKUPS=/opt/backups
dbs=`$MYSQL -u root -pYOURPASSWORD -Bse 'show databases'`

for db in $dbs
do
rm -rf $BACKUPS/$db.3
mv $BACKUPS/$db.2 $BACKUPS/$db.3
mv $BACKUPS/$db.1 $BACKUPS/$db.2
mv $BACKUPS/$db.0 $BACKUPS/$db.1
#mkdir $BACKUPS/$db.0
#$HOTCOPY $userpassword $db $BACKUPS/$db.0
mysqldump -u root -pIndr@sN3t $db  > $BACKUPS/$db.0
done

Make sure you have a folder inside /opt and you call it backups