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. Claude LaFrenière said on September 14, 2012 at 5:52 pm
    Reply

    Hi Martin Brinkmann :)

    In my Windows 7 this is already done in the Task Scheduler by the default installation… The command line is:

    %windir%\system32\rundll32.exe /d srrstr.dll,ExecuteScheduledSPPCreation

    :)

    1. Martin Brinkmann said on September 14, 2012 at 6:22 pm
      Reply

      Thanks for posting that, easier than my way. Have not tried it yet though but will. Wonder why I do not have it.

    2. ilev said on September 14, 2012 at 8:31 pm
      Reply

      I have it as well but it was configured to run only when the PC is idle.

      Task Scheduler > Task Scheduler Library > Microsoft > Windows > System Restore.
      There are 2 triggers : at 00:00 every day, at startup.

      1. Rich said on September 15, 2012 at 4:07 am
        Reply

        Much easier and simpler method is at tweaking.com Registry Backup. It runs at startup and invokes VSS to copy registry and keeps last 30 days for restores. Can be invoked within MSFT Recovery Console so better suited to non-boot situations. No authority issues either.

  2. Michael said on September 16, 2012 at 4:57 am
    Reply

    My task scheduler is also configured this way. But it doesn’t work. My system does NOT make a restore point every time it boots. Its typically not running at midnight.

  3. Achilles Khalil said on September 16, 2012 at 6:57 am
    Reply

    Hi this is achilles Seo Expert and website designer and developer. I am here to tell you if need help or need any seo expert contact me I can design any kind of site for any country and Optimize with search engin also can bring your site at TOP. Please feel free to contact me. Whoever your work is great awesome. Be Happy.

  4. Frantic said on September 16, 2012 at 10:33 pm
    Reply

    How is this done in Windows XP, I wonder?

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.