Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
1  
possible duplicate of Backup MySQL Server –  Warner Jul 15 '10 at 15:58
    
Na, I'm asking for specific bash scripts. –  Webnet Jul 15 '10 at 16:07

4 Answers 4

up vote 2 down vote accepted

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
share|improve this answer
    
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? –  Webnet 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 7:40

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

share|improve this answer
    
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

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.