Automatic Backup of MySQL Database

If you are a developer or even owner of a company, you always want assurance that your data is safe. Of course there is no other way than backing up your database. But the question is, how often you need to back up? Of course most people will answered, they want, as updated as possible. Right? But how can you do that? Are you going to back up your data manually as often as daily?

For those who are using Linux Servers, there is a very good utility for automation of database backup, it is automysqlbackup. Below are the features of this utility based on their project page:

AutoMySQLBackup with a basic configuration will create Daily, Weekly and Monthly backups of one or more of your MySQL databases from one or more of your MySQL servers.

Other Features include:

  • Email notification of backups
  • Backup Compression and Encryption
  • Configurable backup rotation
  • Incremental database backups

Installation

If you are using Ubuntu, open terminal and run the following command:


sudo apt-get install automysqlbackup

If you are using other OS, you can get the source here. There are installation instructions in README file.

Settings

If you need to change the settings here, /etc/default/automysqlbackup. By default, it is not exporting routines or store procedures. If you are a developer, you want to include during export. So, you need to do some changes on their source by running the following command in terminal:


sudo nano /usr/sbin/automysqlbackup

find OPT=”–quote-names by pressing CTRL+W and typing OPT=”quotes-names, add — routines after quotes-name. After that line should look like this,


OPT="--quote-names --routines"                  # OPT string for use with mysql ...

save the file by pressing CTRL+X, type Y, and press enter.

You can test if automysqlbackup is exporting your data correctly by running the following code in terminal:


automysqlbackup

if you encountered an error due to permission, you can try to add sudo before automysqlbackup.

By default your data is save in /var/lib/automysqlbackup, if you want to change it you can edit in /etc/default/automysqlbackup file.

Scheduling

The great thing about Ubuntu and other Linux OS is, it is very easy to set up scheduling of your backup script. Open terminal and run the following command:


sudo crontab -e

go to the last line of the file and add the following:

0 0 * * * /usr/sbin/automysqlbackup

This will run the automysqlbackup every 12am or midnight.

jundy

R&D Supervisor && Traveller && Nature Lover

More Posts - Website

Follow Me:
TwitterFacebookGoogle PlusYouTube

Leave a Reply

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


*