Backup multiple MySQL databases into separate files

Update: want to know how to do this on Windows? See my post Backup multiple MySQL databases into separate files – Windows.

#!/bin/bash
# sonia 16-nov-05
# backup each mysql db into a different file, rather than one big file
# as with --all-databases - will make restores easier

USER="root"
PASSWORD="secret"
OUTPUTDIR="/var/lib/bacula"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# clean up any old backups - save space
rm "$OUTPUTDIR/*bak" > /dev/null 2>&1

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
 -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# dump each database in turn
for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
    --databases $db > "$OUTPUTDIR/$db.bak"
done
Tags:


Share This


 


57 Responses to Backup multiple MySQL databases into separate files

  1. Very nice script, thanks for posting it. :)

  2. Very nice and works first time.

  3. Hi,

    Excellent.Nice article thanks for sharing.

  4. how can i import these backup files back to mysql server all at once?
    or i have to import then one by one? or i can issue this command

    mysql -u user -ppassword -h mysqlhost < *.bak

    ?

  5. A good idea is also to modify the line to show the databases to avoid the “information_schema” DB which we don’t want to back up…you get a MySQL error if you try to.

    databases=`$MYSQL –user=$USER –password=$PASSWORD -e ‘SHOW DATABASES;’ | tr -d ‘| ‘ | grep -Ev ‘(Database|information_schema)’`

    • More good news! Your whole “tr” part is useless in fact :p I’m quoting the MySQL manual:

      The default output format is different (more concise) when you run mysql in batch mode than when you use it interactively. For example, the output of SELECT DISTINCT species FROM pet looks like this when mysql is run interactively:
      +———+
      | species |
      +———+
      | bird |
      | cat |
      | dog |
      | hamster |
      | snake |
      +———+
      In batch mode, the output looks like this instead:
      species
      bird
      cat
      dog
      hamster
      snake

      So you just don’t need the whole tr part. Finally, I’m using your updated script this way, by compressing the output too:

      #Clean up any old backups
      rm -rf $OUTPUTDIR/*

      #Get a list of databases
      databases=`$MYSQL –user=$USER –password=$PASSWORD -e ‘SHOW DATABASES;’ | grep -Ev ‘(Database|information_schema)’`

      #Dump each database in turn
      for db in $databases; do
      $MYSQLDUMP –opt –hex-blob –force –user=$USER –password=$PASSWORD $db | gzip > $OUTPUTDIR/$db.gz
      done

  6. […] modified a MySQL backup shell script from Sonia Hamilton, and these scripts could both be modified to work with nix, but they are mainly for Windows. Read […]

  7. Thanks for this guys!!

    I’ve just modified a version of this to work on our system and back up our DBs.

    K

  8. Thank you very much
    This one worked for me.
    Have a nice day!

  9. […] Backup multiple MySQL databases into separate files « sonia hamilton – life on the digital bikepa… (tags: mysql backup bash) […]

  10. Isn’t this line missing a $ before OUTPUTDIR?

    rm “OUTPUTDIR/*bak” > /dev/null 2>&1

  11. Awesome worked with a few tweaks ! ! ! :)

  12. Thanks for the script, very helpful.

  13. Is there anyway to use the same for windows server?
    I’m having following in mysql-backup.bat file to take backup

    @echo off
    echo Running dump…
    D:wampbinmysqlmysql5.0.51bbinmysqldump -u root -p –result-file=D:backup_newbackup_%DATE:~10,4%%DATE:~7,2%%DATE:~4,2%_%time:~0,2%%time:~3,2%%time:~6,2%.sql –all-databases

    Pls advise the way to seperate this backup in to all seperate database.

  14. Hi!

    Thank you Sonia for the great script. I made some enhancements to the script, you can find my article here: http://carrotplant.com/en/blog/how-to-dump-all-mysql-databases-into-separate-files

    Best regards,
    Daniel

  15. Had to make some slight mods for OSX, it didn’t like the new lines mid command. Take out the new lines,, gravy. Thanks! Very helpful script. Going into the toolbox.

    -Sean

    • You’re welcome Sean! I use git to manage my ‘toolbox’ – means I can easily sync it, even on corporate machine with strong firewalls – can sync to a USB key :-)

  16. Just wanted to chime in my thanks on this, I now have mysql backups happening every day.. Although there were a few tweaks us BSD users have to make and a few BASH changes for it to run properly.. It ended up looking like this:

    databases=$(/usr/local/bin/mysql -u$USER -p$PASSWORD -e “SHOW DATABASES;” | grep
    -Ev “(Database|information_schema)”)

    #Dump each database in turn
    for db in $databases; do
    echo “Now Dumping $db”
    $MYSQLDUMP -u$USER -p$PASSWORD $db | gzip > $OUTPUTDIR/$db.gz
    done

  17. […] go out to Sonia Hamilton, as her blog is where I found the original version of this script, however it did need a fair bit […]

  18. 6 years have passed, and this script still rocks!

    Thank you Sonia!

    Dotan

  19. Thank you very much Sonia, you saved my life :)

  20. replace
    | tr -d “| ” | grep -v Database`
    by
    mysql -BN -e etc …

  21. Very nice, just what I was looking for!
    Obviously you need to have the backslashes in place for the commands to work.
    EG:

    databases=`$MYSQL --user=$USER --password=$PASSWORD \
    -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

  22. Sonia, thank you for this. I just found it and was able to cut a backup script of a system I inherited from 274 lines to only 165. (a mysqldump line for each database) :)

    Gracias! y saludos desde Puerto Rico :)

  23. hello Sonia

    your script worked perfectly. Just did a small change so that output files will be in gz format.

    thanks for script

    http://www.cogitsolutions.com/blog/2012/03/mysql-database-backup-multiple-database-in-seperate-files/

  24. Hi,

    Does any one have a version of this for windows?

    Would be greatly appreciated.

    Thanks

  25. :: MySQl DB user
    set dbuser=xxxx

    :: MySQl DB users password
    set dbpass=xxxx

    :: Switch to the MySQL data directory and collect the folder names
    pushd “C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data”

    :: Loop through the folders and use the fnames for the sql filenames, collects all databases automatically this way

    echo “hello”

    echo “Pass each name to mysqldump.exe and output an individual .sql file for each”

    FOR /D %%F IN (*) DO (
    “C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe” –user=%dbuser% –password=%dbpass% –databases %%F > “C:\SQLBackup\%%F.%backupdate%.sql”
    )

  26. […] ago I wrote a post on how to Backup multiple MySQL databases into separate files (Linux). I get a lot of questions about how to do the same on Windows; here’s a script […]

  27. […] Referência: snowfrog.net […]

  28. Hi Sonia.. Clean concise script..

    I use your script to backup my home webserver dbs to Dropbox!

    I just archive all the .bak files and use https://github.com/andreafabrizi/Dropbox-Uploader to upload them to dropbox.

    Thanks.

  29. […] script was taken from http://blog.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/ and works wonderfully! This entry was posted in Backup, FreeBSD by michael. Bookmark the […]

  30. Thanks Sonia for my time saving.
    I add this script into the logrotate:
    # cat buckupdb
    /var/backup/sql/*sql {
    daily
    missingok
    notifempty
    compress
    postrotate
    /root/scripts/db_bak.sh > /dev/null 2>/dev/null || true
    endscript
    }

  31. Thank you!! This really is truly helpfull, i’ll try it out making use of my own, individual 1st wordpress weblog.

  32. WoW ! Thank you, I saved hours with this.

  33. […] гнев победил лень, и я нашёл на http://blog.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/, а потом и немного модифицировал скрипт для бекапа […]

  34. […] Ref: http://blog.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/ #!/bin/bash # sonia 16-nov-05 # backup each mysql db into a different file, rather than one big file # as with –all-databases – will make restores easier   USER="root" PASSWORD="secret" OUTPUTDIR="/var/lib/bacula" MYSQLDUMP="/usr/bin/mysqldump" MYSQL="/usr/bin/mysql"   # clean up any old backups – save space rm "$OUTPUTDIR/*bak" > /dev/null 2>&1   # get a list of databases databases=`$MYSQL –user=$USER –password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`   # dump each database in turn for db in $databases; do echo $db $MYSQLDUMP –force –opt –user=$USER –password=$PASSWORD –databases $db > "$OUTPUTDIR/$db.bak" done This entry was posted in Web Development, คอมพิวเตอร์. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL. « จัดการน้องเขย โหลดหนัง […]

  35. Handfull script,
    thanks sonia.

  36. Thanks for this Sonia.

    I made a few modifications so that this can work on Plesk servers a little more securely (no need to include the password plain-text) and so that 2 backups are maintained for each database instead of just the one (otherwise there’s the risk of a server failure after your rm line, before all databases have had a chance to perform a fresh dump).


    #!/bin/bash

    # Original script courtesy of Sonia Hamilton
    # http://blog.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/

    # Modified by QWeb Ltd to:
    # - work more securely on Plesk servers
    # - keep existing backups for 2 days
    # http://www.qweb.co.uk/

    # backup each mysql db into a different file, rather than one big file
    # as with --all-databases - will make restores easier

    # Plesk renames root to admin
    USER="admin"

    # Plesk stores the admin password here
    PASSWORD="`cat /etc/psa/.psa.shadow`"

    # mkdir this folder if it doesn't yet exist
    OUTPUTDIR="/home/database-backups"

    MYSQLDUMP="/usr/bin/mysqldump"
    MYSQL="/usr/bin/mysql"

    # clean up older backups (save space)
    rm "$OUTPUTDIR/*bak2" > /dev/null 2>&1

    # get a list of databases
    databases=`$MYSQL --user=$USER --password=$PASSWORD \
    -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

    # dump each database in turn
    for db in $databases; do
    # maintain backups for 2 days to prevent complete loss if the server dies during this backup process, for example
    mv "$OUTPUTDIR/$db.bak" "$OUTPUTDIR/$db.bak2"

    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
    --databases $db > "$OUTPUTDIR/$db.bak"
    done

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>