Simple MySQL Backup

If, like me, you have a database full of small tables, but all amounting to a large amount of data then dumping the data from it into a single file is handy, but not very useful when it comes to retrieving a backup of one single table.
Instead, wouldn’t it be easier to dump each individual table into it’s own file? What about keeping a week’s worth of backups? Here’s one way I backup my WordPress db with a little Bash script:

export d=`date +%u`
mkdir -p backup/$d
for i in `echo "show tables" | mysql -u username -ppassword database|grep -v Tables_in_`;
do
  echo $i; mysqldump --add-drop-table --allow-keywords -q -a -c -u username -ppassword database $i > backup/$d/$i.sql
  rm -f backup/$d/$i.sql.gz
  gzip backup/$d/$i.sql
done


You might also like

If you like this post then please subscribe to my full RSS feed. You can also click here to subscribe by email. There are also my fabulous photos to explore too!

45 thoughts on “Simple MySQL Backup


  1. Oh, this is just so nice and 1337!!!1 Thanks for the script. Here’s a reference on how to use and format dates with BASH. I changed the first line of your code into export d=$(date +'%Y-%m-%d') to make it store the backups in folders like /backup/2005-06-28/ rather than in /backup/2/.


  2. Thanks Mathias!
    Glad you find the script useful.
    I only want to keep 7 days of backups. Using the day of the week guarantees that I don’t fill up my disk!


  3. Ah, I see. I didn’t know what exactly `date +%u` meant; I’m quite new to BASH and all. I just thought the /2/ thing looked a bit silly. In fact, I do like /backup/yyyy-mm-dd/ more, because I just need to think too much when using weekday numbers ;) I’ll try to enhance the script to make it remove folders that are older than a week, hoping this will help me in learning teh r34l 1337 sp34k.


  4. I like it.

    The only bit I don’t like is that %u isn’t mentioned in the GNU date format string reference, which left me thinking to myself “okay, what could ’2′ be?”


  5. I use something very similar for backing up my databases. Mine includes a config file however. Each database you need backed up, just put it on it’s own line in the config file and it’ll be backed up and rsynced to a remote location. Very slick. :)


  6. Alas, for I must contest this. :-D

    The output of man date on my system is pretty much identical to the reference linked to in comment 1.

    Of course, it’s date (GNU sh-utils) 2.0.11. But that’s all DreamHost has. :-)


    1. You are absolutely right the details or the reference is missing in date command’s manual.

      format of date command is:
      date [-nu] [-r seconds] [+format]

      the details of +%u and all of the other formats which you can use in your scripts can be seen in “man strftime”

      hope this helps.


  7. Ouch! I rememeber having to use date on a Solaris box ages ago. *shudder* That was painful! It (and Solaris in general) didn’t have any of the nice GNU features I had become used to. Call me spoiled I suppose!
    I ended up installing a lot of GNU stuff eventually to make my life easier.

  8. Pingback: Creating and restoring MySQL backups using the command line | Math Jazz (1 comments.)

  9. A nice, handy script. Do you think it would be safer to not include the password directly in the script, but rather move it to .my.cnf instead? That way the password won’t show up in clear text in the process list.


  10. This is just what I was looking for to tie in with my automatic remote backup scripts. Excellent stuff.


  11. just a date hack that makes it eaier to remove your old versions
    FILE=$PREFIX`date +%d%b`.tar.gz
    OLDBAK=$PREFIX`date +%d%b –date=’10 days ago’`.tar.gz


  12. Here’s a little mod to include all databases…this way I don’t have to hardcode a list of what to backup, and then forget to modify it. It also packs everything into one daily tarball, for ease of moving to another backup source/dloading etc.
    Modify date formats/paths/filenames/passwords as needed.

    #MySQL backup script
    export d=$(date +’%Y%m%d’)
    mkdir -p backup/$d

    for a in `echo “show databases” | mysql -uroot -pblah |grep -v Database`;
    do
    mkdir -p backup/$d/$a
    for i in `echo “show tables” | mysql -uroot -pblah $a|grep -v Tables_in_`;
    do
    echo $i; mysqldump –add-drop-table –allow-keywords -q -a -c -uroot -pblah $a $i > backup/$d/$a/$i.sql
    rm -f backup/$d/$a/$i.sql.gz
    gzip backup/$d/$a/$i.sql
    done
    done

    `tar cf $d.db.tar backup`
    `gzip $d.db.tar`
    rm -rf backup

  13. Pingback: Makisoft » Blog Archive » Copia de seguridad de Wordpress (1 comments.)

  14. Heres yet another modified backup script, I thought I’d contribute back.

    # MYSQL Backup Script
    # Contains portions of code from http://blogs.linux.ie/xeer/2005/06/28/simple-mysql-backup/

    export d=$(date +’%Y-%m-%d’)
    export savepath=’/home/public/backups/mySQL’

    export usr=’backupuser’
    export pwd=’replacewithyourpassword’

    echo “mySQL Backup Script”

    mkdir -p $savepath/$d

    echo “Dumping entire database..”
    mysqldump –add-drop-table –allow-keywords –all-databases -u$usr -p$pwd > $savepath/$d/all.sql

    echo “Dumping individual tables…”

    for a in `echo “show databases” | mysql -u$usr -p$pwd | grep -v Database`;
    do
    mkdir -p $savepath/$d/$a
    echo “Dumping database: $a”
    for i in `echo “show tables” | mysql -u$usr -p$pwd $a| grep -v Tables_in_`;
    do
    echo ” * Dumping table: $i”
    mysqldump –add-drop-table –allow-keywords -q -a -c -u$usr -p$pwd $a $i > $savepath/$d/$a/$i.sql
    done
    done

    echo “Archiving Files…”
    tar -C$savepath -c -f$savepath/$d.tar $d
    gzip $savepath/$d.tar
    echo “Deleting Temp Files”
    rm -rf $savepath/$d
    echo “Complete”


  15. Since Im a unix admin, I have a problem with commands that have passwords in them, since its a disaster from a security perspective (you can see the password in a process listing).

    Anyway you can make an options file called /usr/local/mysql/.my.cnf which contains:
    [client]
    user=youruser
    password=yourpassword

    Then rather than use the username or password on the command line you would call mysqldump like:
    mysqldump –defaults-file=/usr/local/mysql/.my.cnf –add-drop-table –-allow-keywords –-all-databases > $savepath/$d/all.sql

    The options file should have appropriate permissions permissions (600).

  16. Pingback: Suggestion Box

  17. A revised version:

    # MYSQL Backup Script
    # Contains portions of code from http://blogs.linux.ie/xeer/2005/06/28/simple-mysql-backup/

    export d=$(date +”%Y-%m-%d”)
    export savepath=”/home/user/backups/mySQL”

    echo “mySQL Backup Script”

    mkdir -p $savepath/$d

    echo “Dumping entire database…”
    mysqldump –defaults-file=/home/user/.my.cnf –add-drop-table –allow-keywords –all-databases > $savepath/$d/all.sql

    echo “Dumping individual tables..”

    for a in `echo “show databases” | mysql | grep -v Database`;
    do
    mkdir -p $savepath/$d/$a
    echo “Dumping database: $a”
    for i in `echo “use $a;show tables” | mysql $a| grep -v Tables_in_`;
    do
    echo ” * Dumping table: $i”
    mysqldump –defaults-file=/home/user/.my.cnf –add-drop-table –allow-keywords -q -a -c $a $i > $savepath/$d/$a/$i.sql
    done
    done

    echo “Archiving Files..”
    tar -C$savepath -c -f$savepath/$d.tar $d
    gzip $savepath/$d.tar
    echo “Deleting Temp Files”
    rm -rf $savepath/$d
    echo “Complete”

    ———————–

    This version will create individual folders for each database and seperate sql files for each table as well as a total sql dump in a single file. Next step – upload the tar.gz to a remote host! :)


  18. Nice Script. The last version is quite good that the first.I’m using the last version to backup my site.That Gzip facility is quite good.I modified the script so that it automatically emails the backup to the webmaster ( to me) and added cron jobs to automate it. Thn you all for this nice script.
    Raja
    http://www.cyese.info/

  19. Pingback: Confluence: DHIS-2

  20. Nice start but still hacky.
    Efficient backup and is a bigger issue than just a mysql specific one.
    There are
    My two cents:
    Read http://www.mikerubel.org/computers/rsync_snapshots/index.html
    Install and configure rsnapshot. It will take care of any folder you want to backup. Installs on ubuntu with apt-get.
    When working with rsnapshot, your mysql dumper script just stores the data in local files without any date/time information. Those are taken care by the backup system. One cool benefit is that in space approximately equal to the original data you get many snapshots. That is thanks to elegant usage of hard links. So only files which change do increase the overall snapshot size.


  21. both
    for a in `echo “show databases” | mysql | grep -v Database`;
    and
    for i in `echo “use $a;show tables” | mysql $a| grep -v Tables_in_`;

    could be
    for a in `echo “show databases” | mysql -s ;
    for i in `echo “show tables” | mysql -s $a`;

    to remove the unnecessary grep rows and make it look nicer

    and on a system with gnu tar and bzip2
    echo “Archiving Files…”
    tar -C$savepath -c -f$savepath/$d.tar $d
    gzip $savepath/$d.tar
    echo “Deleting Temp Files”
    rm -rf $savepath/$d
    echo “Complete”

    could be
    echo “Archiving Files…”
    tar -C$savepath -c -j -f$savepath/$d.tar.bz2 $d
    echo “Complete”

    Nice script otherwise!


  22. I have space problem on my Hard Disk of Server RS6000 working on AIX and Oracle 7.3.4 and PLSQL 2.3.4
    So I want to reduce the size of dump file created on the system by dropping a particular table which is large enough and would like to export it seperately.

    Any one sending me the required script is welcome. Pl add comments with each command to make it understandable as data is live and precious.


  23. Help required for export backup of whole data except one or two tables on IBM server RS6000
    o/s is AIX, Oracle ver 7.3.4 and PLSQL 2.3.4


  24. fancy that, looking for a little script like this, and guess who comes up first on google. lol, seo is good, script kool script also. must give u a call nice pic of yourself and the little fella. talk soon


  25. Actually, if you use this and your remote machine is compromised, all your backups are gone. Every backup should be done from your local machine and never the other way around.

Leave a Reply

Loading Facebook Comments ...