How-To

Scheduling MySQL Backups in ServInt’s Jelastic Cloud PaaS

by Dominik Poetek  • 

Editor’s note: A version of this post originally appeared on the Jelastic blog.

The importance of performing regular backups of your data cannot be understated. We’ve talked about it before in the ServInt University, and we’ll keep talking about it as long as there are new hosting customers who have yet to learn the value of having good backups. While we keep backups for emergency recovery, we highly recommend keeping your own backups as well. This article will explain how to create MySQL scheduled backups in our Java and PHP Cloud PaaS solution, Jelastic.

The advantage of using scheduled backups is obvious: unlike manual backups, a backup process can be run during off-peak hours when data is unlikely to be accessed and without the need to oversee the operation.

You can run scheduled backups with Jelastic using the VDS service and cron. The backups will occur on the schedule you specify. The crontab command works by reading a crontab file, a plain text file that lists all the jobs you have scheduled. Each line of the crontab file consists of the command with all its options and arguments, and the date and time to run it.

Here are the steps for setting up backups in your Jelastic Cloud deployment.

Create environment

  1. Log into your Jelastic account or sign up for your first “cloudlet” if you haven’t done so yet.
  2. Create a new environment.

jel-backup-1

  1. In the Environment topology dialog pick VDS and MySQL as a database you want to use. Switch on Public IPv4 for your database. Then set the cloudlet limit and click Create.

jel-backup-2

Wait a moment for your environment to be created.

jel-backup-3

Establish an SSH connection as described here.

  1. Use the yum command to install the MySQL package (mysql.x86_64).
yum install mysql.x86_64

 

  • Create a new directory for your future backup files. For example:

 

mkdir /root/backups

Scheduling a backup

  1. Edit the crontab file using the following command:
crontab -e

The crontab file opens in the vim editor.

  1. The first time that you use a file, it is blank. Press “i” to enter INSERT mode.
  2. Using the following format, enter the details of the backup job you wish to schedule format:
*/1 * * * * /usr/bin/mysqldump -u root -p {database_password} -h {database_hostname} –all-databases –routines –lock-tables=0 | gzip > /root/backups/mysqlbackup_$(date +%m-%d-%Y-%H%M).sql.gz

{database_password} is the password and {database_hostname} is the Public IP address you received in your confirmation email when you created the environment you wish to back up.

In this case every minute */1 * * * * we backup all databases –all-databases from our MySQL server and save the backup files in the root/backups directory that we have created earlier gzip > /root/backups/mysqlbackup_$(date +%m-%d-%Y-%H%M).sql.gz). Click here for more on cron terminology.

  1. Exit from INSERT mode and switch to command mode by pressing the ESC key, then save changes with the following command:
:wq
  1. Navigate to the backup folder you have created earlier to check the results:
ls /root/backups

jel-backup-4

That’s it. You’re ready to go. Your SQL backups have been scheduled and will run automatically.

Due to our Jelastic Cloud’s vertical scaling feature, the cost of these backups is minimal, especially considering that cloudlet resources are needed only during the actual backup process. Jelastic gives your application the amount of RAM and the CPU power it needs, then recycles those resources back when you no longer need them. Still, be sure to monitor the disk usage of your backups and delete old backups as appropriate to prevent over-utilization of disk resources.

Photo by Horia Varlan

Find out more about ServInt solutions

Starting at $69

  • The New York Times
  • The Hill
  • Bloomberg
  • The Seattle Times
  • Computer World
  • Ars Technica
  • MSNBC