Backup MySQL Databases In Linux Regularly - gHacks Tech News

Backup MySQL Databases In Linux Regularly

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

We need your help

Advertising revenue is falling fast across the Internet, and independently-run sites like Ghacks are hit hardest by it. The advertising model in its current form is coming to an end, and we have to find other ways to continue operating this site.

We are committed to keeping our content free and independent, which means no paywalls, no sponsored posts, no annoying ad formats or subscription fees.

If you like our content, and would like to help, please consider making a contribution:


Previous Post: «
Next Post: »

Comments

  1. 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).

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

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

  3. 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.

  4. 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 ). >;-)

  5. 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

  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. rReLmy said on January 12, 2009 at 10:29 pm
    Reply

    i love nano :D

    nice script

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

    I just wanna know how to take back up MySQL databases

  9. 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

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

    Interesting way to do a backup.

  11. 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.. :)

Leave a Reply

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

Please note that your comment may not appear immediately after you post it.