How to Backup MySQL Databases on Ubuntu VPS Server Automatically

ubuntu Apr 26, 2020

Introduction

Backing up your customer's data regularly is very important and you should do it. This article shows you how to backup MySQL databases 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 big companies and Website.

In this article i will use a DigitalOcean Ubuntu VPS, you can get 100$ credit by following this link

DigitalOcean – The developer cloud
Helping millions of developers easily build, test, manage, and scale applications of any size – faster than ever before.

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

You are now 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.

We started sharing these tutorials and articles to help and inspire developers and engineers around the world. If our blog has been helpful to you, feel free to buy us a coffee to keep us going :).

Buy us a coffeeBuy us a coffee

Ousseynou Diop

Full stack Developer & Python Lover

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.