mysqldump to multiple files

It is a good idea to run mysqldump by cron to regurlarly make backups of your MySQL databases. An easy way to do this is by adding the –all-databases parameter.  This way you will have a backup of all your databases – in one file though. If the amount of data and the number of databases is small this might be ok.

After the first session of restoring just one database or one table from your one-file-backup you realize that it would be nice to have the dumps split up in some convenient way. Like dumping each database to one file, one for each database.

This little shell script does the trick for you. It is really simple and has no error handling what so ever. It is released under GNU GPLv2 license. If you modifiy it or add features (like error handling or so), please do send me a copy or post it as a comment here.


#!/bin/sh
# mysqldump-multi.sh
# Copyright HelTech Communication, Stefan Helander 2010
# stefan@heltech.se, http://www.heltech.se
#
# Licensed under GNU GPLv2
#
# Dumps all MySQL databases. Each database stored in a single file.
#
# settings

DUMPDIR=/root/mysql-backup/backups
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=YOUR-PASSWORD-HERE
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# dump each db

for DB in `echo 'show databases' | $MYSQL -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS --batch -N`
do
$MYSQLDUMP -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS --quote-names --create-options --force $DB > $DUMPDIR/$DB.sql
done

2 replies
  1. suisse
    suisse says:

    ————————————————————–
    #!/bin/bash
    db=$1
    if [ “$db” = “” ]; then
    echo “Usage: $0 db_name”
    exit 1
    fi
    mkdir $$
    cd $$
    clear
    for table in `mysql $db -e ‘show tables’ | egrep -v ‘Tables_in_’ `; do
    echo “Dumping $table”
    mysqldump –opt -Q $db $table > $table.sql
    done
    if [ “$table” = “” ]; then
    echo “No tables found in db: $db”
    fi

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

17 + 12 =