Automate Your Website File and MySQL Backups

Part 2 – Backing up your Databases

Start by creating 2 folders on your web server for temporarily storing the daily backups. In this example, we will use
/home/CpanelUsername/backups/databases
and
/home/CpanelUsername/backups/files
These 2 storage folders are located outside of the public web directory. This is the safest way to prevent unwanted snooping. If you must store them in the public web directory, make sure to take the necessary step to prevent snooping. After all, these backups will likely contain sensitive file and database information.

Next we will create a shell script that will backup all your databases, and compress and archive them into a single file for easy download later.  Something to keep in mind is that sometimes creating this script in a windows text editor such as notepad can cause problems with the execution of the script. The reason is that most windows based text editors will use DOS style line-returns. Linux and other UNIX based systems prefer UNIX style line-returns and many times don’t understand DOS line-returns. One way to prevent this is to use the free program Notepad++ because with it, you have the option to specify the type of line-feeds to use. From the “edit” menu select “EOL Conversion” and then from the sub-menu select “UNIX Format”

Create a new file and name it “db-backup.sh”.  This file will contain a separate line for each database you want to back up.  Copy and past the following into “db-backup.sh”, making sure it is all on one line.

/usr/bin/mysqldump -u'MySQLusername' -p'MySQLpassword' --opt dbXname > /home/CpanelUsername/backups/databases/dbXname_$(date --iso).sql

Copy and past this same code on additional lines as needed, one line for each database you want to back up. Make sure there is only one copy per line with no blank lines in between. Edit each line with the specific information for your situation (MySQLusername, MySQLpassword, dbXname, and CpanelUsername). Each line will create a backup of one database with the most common useful options and store it temporarily in the databases folder you created earlier.

Copy and paste the following code on a new line directly after the last line you created in “db-backup.sh” above.

tar -cvzpf /home/CpanelUsername/backups/databases/CpanelUsername-databases-$(date --iso).tar.gz --remove-files /home/CpanelUsername/backups/databases/*$(date --iso).sql

As before, you will need to edit CpanelUsername according to your situation. This line of code will archive and compress the databases into a single file, and then clean up (remove) the original files.

That’s it for the shell script. Save the file and load it up to your web server in /home/CpanelUsername/backups. You can also download an example db-backup.sh to compare with yours.

There’s just one thing left to do to complete the database backups, create a cron job that runs db-backup.sh once per day. Log on to your web host’s Control Panel and create a new cron job. In the command line for the cron job copy and past the following command.

/bin/sh /home/CpanelUsername/backups/db-backup.sh

Remember to change CpanelUsername for your specific situation. Schedule the job to run once per day. 3 AM would be a good time for most sites.

That completes the database backup phase. The next page covers file backups.

Next Page – Backing up your files

This entry was posted in Website Administration, Website Backups and tagged , , , , . Bookmark the permalink.

11 Responses to Automate Your Website File and MySQL Backups

  1. Ryann Lalata says:

    I am glad to finally learn this. Thanks!

  2. Sounds very intersting.This helps me to learn something new.Thanks.

  3. Gena Nett says:

    Terrific, this information is exactly what I was searching for.

  4. Your Article is Very HelpFull , Thank you very much

  5. Pretty nice post. I just stumbled upon your blog and wished to say that I’ve truly enjoyed browsing your blog posts. In any case I will be subscribing to your rss feed and I hope you write again soon!

  6. I’ll be sharing this with my network. Very pleased with your style, thanks! I will come here again.

  7. Helpful information here. I added your site on my favorite, so visit again.

  8. I have to tell, that this article is great! Thanks for providing the such a wonderful article, you have assembled a tremendous amount of useful data here. So thanks again.

  9. Bobbi Carey says:

    What a great post

  10. Simply wanna input that you have a very nice site, I love the pattern it really stands out.

  11. humanizer says:

    There are a few excellent suggestions here. I need to renovate my own blog site a while. My goal is to begin from the start these times I think.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Proudly powered by WordPress.
preloaded image preloaded image preloaded image preloaded image preloaded image