Advertisements

Backing up your server using JungleDisk Server Edition – part 2

In part 1, I told you how to set up JungleDisk backup for your Linux server. In this part 2, I’ll tell you how to automatically have it dump and backup your MySQL databases (correctly)!

There are security implications if this permissions are not set correctly on the files, as you’re storing your MySQL password in the script, and you’re going to have complete database dumps sitting on your hard drive. However, I will attempt to explain as clearly as possible my solution, and I’m not responsible if it doesn’t work for you.

So, start out by deciding where you want your database-dumping script to run from. A few good example spots are /root (root users home directory), and /etc/jungledisk (with the configuration files). I’ve called my backup script prebackup.sh. You’ll understand the name further below, but I’m going to use that name the rest of the way through.

So create your prebackup.sh script as root, and set it to mode 700.

touch prebackup.sh && chmod 0700 prebackup.sh

This makes sure that root is the only user who can read, write, or execute it.

Now, using your favorite text editor, you can use the following sample script:

#!/bin/bash
date=`date -I`
dir=/var/backups
file=sqlbackup-$date.sql
touch $dir/$file &&
chmod 600 $dir/$file &&
mysqldump --all-databases -p'__MySQLPassword__' >> $dir/$file
find $dir -ctime +7 -delete

Danny pointed out that creating gzipped MySQL dumps, as I had in my original script, is discouraged as it defeats the data de-dup feature of jungledisk. The above script has been changed from the original to make uncompressed dumps. Thanks, Danny!

The last line, with the find statement, is responsible for the cleanup of the directory. It will delete any file which is older than 7 days. If you want more or less time, simply change +7 to your desired number of days (keeping the + sign).

Warning: There’s very little (read: none) sanity-checking in this script. Replace __MySQLPassword__ with your root MySQL password.

Jay pointed out that there will likely be issues with handling special characters in the SQL password. If you have any suggestions, please feel free to post them in the comments below. 

After saving, you should have a 183-byte (give or take) file with 0700 mode:

root@ve:/etc/jungledisk# ls -l prebackup.sh
-rwx------ 1 root root 183 Mar 24 17:08 prebackup.sh

You should make sure that the directory in $dir is owned by user and group root and mode 0700. This will make sure that noone else has access to your dumped databases. Now that you have your script, it’s time to automate it. You could schedule a cron job to run the script, but it’s easier to have JungleDisk run it for you at the start of every backup job.

Start your management-side program, login, and go to your server’s backup configuration. Under Backup Options, check Enable Pre and Post Backup Scripts. Now, click Configure, and in the Pre-Backup Script, enter the full path and filename of your newly created script, i.e. /etc/jungledisk/prebackup.sh.

That’s it!

Now, on your assigned schedule, the server engine will run your database dumping script, and start your backup job. Of course, make sure whatever directory you’re dumping your databases to is included in your backup set.

The last thing to note is this script is amazingly light; it doesn’t delete any old databases and it doesn’t do much else. You’re free to modify it, and I would greatly appreciate any feedback on your modifications.

Comments are welcome, as always.

Advertisements

, , , , ,

  1. #1 by Max on March 30, 2011 - 3:40 pm

    Thanks! Any plans to put together a post script to clean up after the backup? I’d appreciate it!

    • #2 by Mike on March 30, 2011 - 5:15 pm

      I was actually planning on posting a cleanup script.

      I’ve got some busy days ahead of me, but I’ll post it up when it’s finished.

    • #3 by Mike on April 3, 2011 - 10:05 am

      I’ve modified the prebackup.sh script to include a line which deletes sql backups older than 7 days. Feel free to adjust it to your liking.

  2. #4 by John sysadmin on March 31, 2011 - 3:01 am

    You can also use a program to make server backup like Handy Backup Server .

    • #5 by Mike on March 31, 2011 - 7:40 am

      Thank you for your comment.

      However, the article was specific to Linux servers, and the software you mentioned was for Windows only. I also generally tend to favor cross-platform solutions, which JungleDisk is.

  3. #6 by Danny on May 27, 2011 - 9:22 pm

    I was under the impression that you don’t want to gzip the dumps. Per this guy:

    http://serverfault.com/questions/195125/can-i-backup-mysql-while-mysql-is-running/244169#244169

    “Jungledisk uses data deduplication and differential backups to help improve the efficiency of its storage. If you do raw dumps; then it all works. If you gzip first, then it has to store the entire (different) gzip; and no optimizations in backup speed or required backup storage space are gained.”

    Thoughts?

    • #7 by Mike on May 27, 2011 - 9:51 pm

      @Danny

      Thanks for your comment.

      The Serverfault article is correct — data deduplication can only occur if the data is in an uncompressed format. That way the portions of the raw databases which are the same will not have to be transferred or stored, which saves both bandwidth and storage fees.

      Thank you for bringing this up. I didn’t take de-dup into consideration when writing the article the first time through, I was more focused on keeping local disk space usage low. However, for users who have sufficient disk space, doing the MySQL dump without gzip is MUCH preferred.

      For those checking back, or who wish to switch to uncompressed MySQL dumps, make the following change to the script:

      Change:
      mysqldump --all-databases -p'__MySQLPassword__' | gzip >> $dir/$file
      to:
      mysqldump --all-databases -p'__MySQLPassword__' >> $dir/$file

      and
      file=sqlbackup-$date.sql.gz
      to:
      file=sqlbackup-$date.sql

      Thanks again!

      • #8 by Mike on May 27, 2011 - 9:55 pm

        I’ve updated the original article (and my previous comment) based on your feedback. Thanks again!

        • #9 by Danny on May 27, 2011 - 10:16 pm

          Thank you so much for your quick reply. One other question. I just tried to use the prebackup.sh script, but received the following errors:

          xScriptReturnedError – Execution of pre-backup script (/etc/jungledisk/prebackup.sh) failed
          Exception Code: xScriptReturnedError (150)
          Time: 5/27/11 10:45:02 PM (GMT-5)
          Detailed Message: Execution of pre-backup script (/etc/jungledisk/prebackup.sh) failed
          Error Location: BlockBackupManager.cpp:1392
          via BlockBackupManager.cpp:1443
          via BlockBackupManager.cpp:1497

          I am running the script on a CentOS-based server running cPanel. Are there any modifications that I need to make?

          • #10 by Mike on May 28, 2011 - 8:47 am

            Hmm.

            What I would suggest is running each line of the script on its own on the command line. Its likely one line is failing, causing the script to return a non-zero exit status (error).

            If you find anything useful, let me know.

  4. #11 by jay on December 5, 2011 - 3:37 pm

    Hey Mike,

    Any idea why running this mysqldump script would save a 0kb backup file in the correct location (/var/backups) with nothing in it?

    • #12 by Mike on December 5, 2011 - 3:57 pm

      Off the top of my head, I have two suggestions:

      1) run your mysqldump command (from the script) at the command line, substituting values for variables. Do you get a good dump?

      2) run the mysqldump without the “>> $dir/$file” part at the end. This will dump the output to the screen. (use Ctrl-c to stop). Do you get output?

      If you have special characters in your password, it might trip up the script. I was using an alphanumeric password when I wrote it.

      Please feel free to let me know the results of your checking.

      • #13 by jay on December 5, 2011 - 4:06 pm

        ah ha im betting its special characters as i have a lot of them. any quick tips to escaping them?

        • #14 by Mike on December 5, 2011 - 4:24 pm

          At the moment the best suggestion i can give is to experiment with backslash-escaping your special characters. Try both single and double backslashes.

          I won’t be able to try anything myself hands-on until later this evening.

        • #15 by Mike on December 5, 2011 - 9:41 pm

          Unfortunately it’s later than I planned to get back to this and I don’t have time to try it out myself. I did promise you a response, so here are some tips for getting this working around the special characters in your password:

          Backslash the special characters in the __MySQLPassword__. This would include back-ticks, single and double quotes, ampersands, etc. May work, depending on the special chars, and you may have to double-backslash it since its in a script.

          Try some of the tips from here, especially specifying your password via a ~/.my.cnf file under root’s home, since you’re running it as root. (/root)

          Lastly, there’s always the option of changing your password.

          If you do find a method that works, please feel free to come back and let me know how you did it. If I work something out later I’ll update, so please feel free to check back.

        • #16 by Mike on December 5, 2011 - 9:52 pm

          I’m not sure what you have in your password (and I don’t want to know) :) but this mentions that a % sign is a known issue.

    • #17 by Sean on January 25, 2012 - 6:23 pm

      Might be because of path. Try specifying full path for mysqldump.

  5. #18 by tonyatreadyartwork on February 20, 2012 - 3:26 pm

    Awesome guide!