How to Backup MySQL Databases on Ubuntu VPS Server Automatically

Originally posted on my blog

Introduction

Backing up your customer's data regularly is very important and you should do it. This article shows you how to backup MySQL database at regular bases.

MySQL is an open source Relational Database Management System (RDBMS) that uses Structured query Language(SQL).
MySQL is very popular and used by many companies and Websites.

Why you should backup your database

There are many reasons why you should backup your database :

• Have a copy of your data
• Increase the level of security

Backup database manually using mysqldump

Most developers prefer creating and downloading the database manually.

mysqldump -u username -p database_name > data-dump.sql

This command will create a file called data-dump.sql , keep it in safe location.

To restore the database

 mysql -u username -p database_name < data-dump.sql

You've done the backup and the restoration, let's make it great again 😍

Backup database automatically using automysqlbackup

AutoMySQLBackup is a program that lets you take daily, weekly and monthly backups of your MySQL databases using mysqldump. It can back up multiple databases, compress the backups, back up remote databases, and email the logs.

Features :

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

Type the following command into the terminal to install the program:

sudo apt-get install automysqlbackup

A following prompt will ask you which mail configuration you prefer. Select “internet site” if you’re going to set up email notification. If not, just select “no configuration”.

Start the program

sudo automysqlbackup

The default location for Backups is /var/lib/automysqlbackup.

You should see three directories : daily, weakly, monthly.

The configuration file for automysqlbackup is located at /etc/default/automysqlbackup.

You can Open it in your favorite editor:

sudo nano /etc/default/automysqlbackup

You can do some customization, in many cases the defaults configurations can be left intact.

Conclusion

Now You are able to sleep well at night, knowing that your customer’s data will be backed up at regular bases.

Thank you for reading 💓 see you next.