Automatically or Manually Backup MySQL Databases

MySQL is one of the most popular open source database management system for the development of interactive Websites. Its is one of the basic components of established website development platforms such as LAMP (Linux-Apache-MySQL-PHP) due to its proven reliability and speed of performance.

If your site stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (we all have been there).

There are several ways to backup MySQL data. In this article we’ll learn how to achieve an automatic backup solution to make the process easier. Starting with the mysqldump utility that comes with MySQL, we will review several examples using mysqldump, including the backup of your database to a file, another server, and even a compressed gzip file and send confirmation email to your email address.

This is a simple backup solution for people who run their own web server and MySQL database server on a dedicated or VPS server. Since, I manage couple of boxes, here is my own automated solution.

Install lftp

lftp is a file transfer program that allows sophisticated ftp, http and other connections to other hosts. If site is specified then lftp will connect to that site otherwise a connection has to be established with the open command. To install lftp, enter:

It is time to write a shell script that will automate entire procedure:

Shell script to backup MySQL database server

Following is the shell script. It will dump all database to /backup/mysql and later it will upload to FTP server and also it will send a confirmation email to the system admin. You need to setup correct server details, username,password and email address before using the script:

Save script as /home/your-name/ file. Setup executable permission:

Note : Make sure you have created this folder /backup/mysql (must have permission) in your local server, where the mysql server is running. And also make sure you have created this folder /home/httpdocs/mysql in your FTP server.

To run this script (backup MySQL) , enter:

Run MySQL backup script as cron job

To automate procedure setup a cron job. For example run backup everyday at midnight (i.e once a day), enter:

Append following cron job:

I hope you found these tips helpful. Be sure to drop a comment if you have any more ideas.


Leave a Reply

Your email address will not be published.