Backup MySQL Databases In Linux Regularly

Martin Brinkmann
Jan 12, 2009
Updated • Nov 29, 2012
Backup, Development
|
12

The post Using Cron to Automate Linux Tasks by Jack got me thinking that some users might be interested in a practical example. The following tutorial will explain how to setup a small script to backup MySQL databases on a Linux server which will be run daily using a cron job. The best way to start would be to read up on the introduction by Jack about Cron so that you got a understanding of the basics.

We start by creating the bash script first and continue with the cron job after the bash script has been tested to work as intended. All commands in the following paragraphs are executed from the command line.

vi backup.sh

This will create an empty text file in the vi editor. This will be the script that will be executed by the cron job on a daily basis.

bin/sh
/usr/bin/mysqldump -A -u [Username] -p[Password] | gzip > /backups/mysqldump`date +%m_%d_%y`.gz

Press i to enter insert mode and copy and paste the two lines above in the editor. What the script does is to use the mysqldump function to create a mysql backup and store it gzipped in the backups directory using mysqldumpDate.gz as the name with data being the day the backup was created.

The two variables [Username] and [Password] have to be edited and the username and password of a user with sufficient rights entered. It might also be necessary to change the location of the backup folder.

Press ESC to enter command mode and :x to save the file.

./backup.sh

Execute the script to see if it is working correctly. It should create the dump of the MySQL database in the defined directory. It might be necessary to change the permission level of the backup.sh file if you get a permission error during execution.

chmod 755 backup.sh

If the script is working as intended it can be added as a cron job. The following command will open the user crontab list.

crontab -e

If you are in the vi editor you press i to enter insert mode again and add the following line

0 7 * * * /path/to/backup.sh

Please refer to the tutorial for a detailed instruction on how to set the cron job up to suite your needs. This one will execute the script every day on 7am.

It is a good idea to check back after the next execution to verify that everything is working as intended.

Advertisement

Tutorials & Tips


Previous Post: «
Next Post: «

Comments

  1. Mario said on January 4, 2010 at 11:05 pm
    Reply

    i know its an old post (almost a year old) but i really needed to say this :

    PICO shall live forever..

    VI was created by the same devil as the one who created Kraft Singles wrapping.. :)

  2. remote backup said on February 27, 2009 at 8:45 pm
    Reply

    Interesting way to do a backup.

  3. dahamsta said on January 13, 2009 at 1:56 pm
    Reply

    I prefer to backup my databases individually, so I can restore them in order of imporatnce if necessary:

    for DB in `mysql -u root -p$PW -Bse ‘show databases’`
    do
    mysqldump -u root -p$PW $DB > $MYTMP/$DB.sql
    done

    Where $PW is a password set earlier in the script.

    adam

  4. Monjur Ahmed Laskar said on January 13, 2009 at 8:56 am
    Reply

    I just wanna know how to take back up MySQL databases

  5. rReLmy said on January 12, 2009 at 10:29 pm
    Reply

    i love nano :D

    nice script

  6. RG said on January 12, 2009 at 6:07 pm
    Reply

    I wish I always felt comfortable enough to do backups attended, I realize the chance of problems is low but still but it has happened. In my case, backing up a forum, I put too much faith in mysqldump locking tables before running.

  7. Ajay said on January 12, 2009 at 12:25 pm
    Reply

    I just run the cPanel backup job on my server that backs up all the accounts on an external drive, not just the mySQL DB

  8. John the catarrhine said on January 12, 2009 at 11:15 am
    Reply

    Actually VI is easy (so is Emacs, maybe just slightly harder).

    If you want a challenge try TECO ( http://en.wikipedia.org/wiki/Text_Editor_and_Corrector ). >;-)

  9. gokudomatic said on January 12, 2009 at 10:12 am
    Reply

    vi is the devil. it works perfectly but you have to sell your soul to understand it.

  10. Martin said on January 12, 2009 at 9:05 am
    Reply

    Dotan you really do not know about the secret smiley commands in Vi? :)

  11. Dotan Cohen said on January 12, 2009 at 2:52 am
    Reply

    1) You’ve just unleashed VI on a lot of unsuspecting innocents!

    2) I do not remember ever seeing a smiley icon ever used to save and exit VI before (hint: reread the post _after_ posting it).

Leave a Reply

Check the box to consent to your data being stored in line with the guidelines set out in our privacy policy

We love comments and welcome thoughtful and civilized discussion. Rudeness and personal attacks will not be tolerated. Please stay on-topic.
Please note that your comment may not appear immediately after you post it.