Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


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
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
up vote 2 down vote accepted

You can use this script:

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;
    BACKUP_SUBDIR="$BACKUP_DIR/mysql_`date +%Y-%m-%d`"
    if [ ! -d $BACKUP_SUBDIR ]; then
        mkdir -p $BACKUP_SUBDIR
    if [ -f $BACKUP_FILE ]; then
        unlink $BACKUP_FILE
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.


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

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

for db in $dbs
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

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

share|improve this answer

Your Answer


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.