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.

possible duplicate of [Backup MySQL Server](http://serverfault.com/questions/8044/backup-mysql-server) — Warner, Jul 15 '10 at 15:58

score 3 · Answer 1 · answered Jul 15 '10 at 17:32

3

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

answered Jul 15 '10 at 17:32

Lon

31
2

I use AutoMySQLBackup as well, from a remote server. That way, even if the MySQL server I'm backing up dies, I still have the data. – Joe Jul 15 '10 at 17:46

score 2 · Accepted Answer · answered Jul 15 '10 at 16:11

2

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

answered Jul 15 '10 at 16:11

Seishun

196
1
2
8

It will create a gzipped file for each database on your server in the backup directory. It's the script I am using on my server. – Seishun Jul 15 '10 at 16:20
What permissions does /opt/backup/ need to have? Also, what settings do you recommend for Mysqldump and gzip? What should MYSQL be? – Ben Jul 15 '10 at 17:32
Definitely, you'll want the directory to be writable by the user that will set up the cronjob for backup. MYSQL="$(which mysql)" will contain the full path to the mysql executable on your system, through the use of 'which' command. – Seishun Jul 16 '10 at 07:40

score 0 · Answer 3 · answered Jul 15 '10 at 16:00

0

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

answered Jul 15 '10 at 16:00

Luma

1,460
4
19
31

score 0 · Answer 4 · answered Jul 19 '10 at 19:27

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 marco@penguincares.no-ip.org

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

Shell MySQL Backups - What's your solution?

4 Answers4