Automatic Database backups using free Sypex Dumper tool

Photo credit – williamhook2631871046

Automatic database backups can be set up very easily using a simple shell script and a cron job. However it may not be a practical solution for huge databases and the restoration process can also be difficult. There are various tools available to make this process simple. Sypex Dumper is just one of them.

What is Sypex Dumper

Sypex Dumper is a software product (PHP-script), which can help you create a backup copy (dump, export) of a MySQL database, and also restore the database from the backup file if needed. Read more and download

With this tool huge databases can be backed up and restored with very high speed using least server resources and greatly reducing the size of the database dumps.

Free version of the tool is enough to create the automatic backups. The Paid version allows to selectively restore a particular table from the entire database.

Steps to set up automatic Database Backups

Let’s assume you want to create backup of your database every day and keep the recent 30 backups on your server

Create the required job in SXD

1. Login to Sypex Dumper with your database user credentials

Setup automatic database backups using Sypex Dumper

2. Click on the Export option

3. Select the database from the Database drop down

4. Since we want to keep only the last 30 database backups add 30 in the Autodelete If number of files more than box.

5. Add some comments e.g. Last 30 backups

6. Clicking the Save button will create the backup job with the name specified

Create the shell script to execute the job

Now that the job is created we want to execute it using a shell script. Here is a sample script

Automate the script

Finally just add the above script to a cron job so that it runs once daily

 

 

Shell script to backup database and send it to remote server automatically

Database backup script
Photo credit – 132889348@N0722868800432

This article explains how to create a database backup script to send the backup to remote server  without any manual intervention. For security reasons or to enable disaster recovery it is important to keep database backup copies on some additional server outside your network.

The process to create a script for database backup and to autosend it to a remote server is not really so complicated as many would think.

To make it simpler let’s divide the whole task in 3 different sub tasks

  1. Create a backup file of your database.
  2. Authorize the origin server i.e. Your current server to send the files to the remote server (So that the remove server knows that it is receiving the files from a genuine source)
  3. Create a script to simply SCP the files from the origin server to the remove server

Above mentioned are mandatory steps. Additionally you may also want to create a log file to log the status of every step just in case if something goes wrong.

Now lets discuss each step in detail

Step 1: Create a backup file of your database

The backup file of your database can simply be a compressed sql file. There are lot of automated tools to achieve this. But to create a script is also quite simple.

For those who want to script everything here is a sample script (and assuming you have only one database)

Above command just creates a dump of the database. The dump is in sql format. Let’s say db.sql

The size of the file however would be too big (depending upon your database) as it is an uncompressed database file. So you may wish to change the above command to output a gzip or bzip2 file.

GZip

bzip2

Note: Although the compressing provided by bzip2 is better than Gzip it takes much longer (around 6-10 times) to compress a file in comparison to Gzip. However in our case the process will mostly run during the night and will be automated so you might wish to use bzip2. So in this case both the options are ok.

Step 2: Authorize the origin server to send the files to the remote server

As you would have guessed this steps is to avoid password prompts so that the process happens automatically without any manual intervene.

It involves only 2 steps

A. Create an SSH key on the origin server

Command: ssh-keygen

This will create a key in your USER/.ssh folder with the name of the key provided while running above command.

When asked to enter passphrase, leave it blank (unless you will be caching the passphrase on the remote server)

If you want to use your current SSH keys thats fine too however they may have a passphrase on it. You can remove the passphrase using below command

Command: ssh-keygen -p

This command will first ask the ID of the key for which you wish to change the passphrase. Then it will prompt you to add the new passphrase. You can leave it blank.

 

B. Add the private key to the authorized_keys file on the remote server

This step is very simple. Just copy the public key (e.g. id_rsa.pub) contents and paste them on a new line in the authorized_keys file on the remote server

Note: Do not delete any content in this file. Just append the file with the contents of the key starting on a new line.

Step 3: Create a script to simply SCP the files from the origin server to the remove server

Final step is very simple. Here we will just be copying the files from the origin server to the remote server using below command

 

Here the port number is of the remote server where you will be sending the files. If the scp port is non standard or non default only then -P PORT_NUMBER option is necessary