how to back up a MySQL on a Linux Ubuntu 18.04 | HostAdvice

How to Backup Your MySQL Database on an Ubuntu 18.04 VPS or Dedicated Server

Keeping a database backup for your website or application increases the level of security in case of a disaster. In fact, most webmasters prefer creating and downloading an offline version of their MySQL database every day for recovery purposes.

MySQL server and Ubuntu 18.04 VPS come with essential tools for creating backups of your databases. Backups increase the reliability of your websites and you should take them regularly if you want high availability in your business.

In this guide, we are going to show you how you can take backups from your MySQL database running on Ubuntu 18.04 VPS (Bionic Beaver). You can then copy the MySQL backups on another server or your local computer for double security.

Prerequisites

  • Ubuntu 18.04 server
  • MySQL community server
  • A non-root user with sudo privileges
  • Mysql server username and password
  • A sample MySQL database

Special note: If you are worried about reliability and security issues for your website,  and are considering switching to another web host, we recommend that you consult with HostAdvice’s expert and user reviews before doing so. Go to HostAdvice’s best MySQL hostingbest VPS hosting, or best Linux Hosting to find the best rated web hosts in these categories.

Step 1:
Using MySQL dump command

Ubuntu comes with a nice command called ‘mysqldump’.  We are going to use the command as shown below to backup our database. Replace the username, database_name and backup_file_name wit the correct values. Also, enter your database password when prompted to do so:

$ mysqldump -u username -p database_name > backup_file_name.sql

For example,  to backup a database named ‘wp_database’ to a file name ‘wp_database_bk.sql’ we are going to use the command below. Please note wp_user is the username used to log in on the wp_database:

$ mysqldump -u wp_user -p wp_database > wp_database_bk.sql

The command above will create a mysql backup under the path “/home/<user>/wp_database_bk.sql”

Step 2:
Restoring MySQL database

You can restore any MySQL database using the command below:

$ mysql -u username -p database_name < backup_name.sql

For instance, to restore our wp_database_bk.sql to a database named wp_database_1, we run the command below:

$ mysql -u wp_user -p wp_database_1 < wp_database_bk.sql

Step 3:
Automating the backup process

Creating manual backups in a production environment can be tedious. Luckily, there is a Utility called automysqlbackup that we can download from the Ubuntu’s repository. The package uses cron jobs to schedule backups at different intervals without manual intervention.

To download the utility, run the command below:

$ sudo apt-get install automysqlbackup

Then to take on-demand backups, run the command below:

$ sudo automysqlbackup

You can list the content of the daily backup folder by running the command below:

$ sudo ls -a /var/lib/automysqlbackup/daily

You can customize automysqlbackup utility by editing its configuration file located at “/etc/default/automysqlbackup” by running the command below:

$ sudo nano /etc/default/automysqlbackup

The utility organizes the MySQL backup files pretty well under the “/var/lib/automysqlbackup” directory.

Conclusion

We have covered two different methods of creating MySQL backups on your Ubuntu 18.04 server. Each method has its own benefits and advantages. While the automatic backups make work for some administrators, some may require the manual method, especially on production servers.

So it all depends on your needs, system resource and the nature of your application. Remember, it is always advisable to keep an updated database backup in an offsite location in case your remote Ubuntu 18.04 servers a breakdown.

Also, choose a backup frequency that matches the severity of your problems in case of disaster. For some, a daily backup may do but others might have the need for hourly backups.  I hope you enjoyed reading the guide.

Check out the top 3 Dedicated server hosting services:

Hostinger
AU$4.48 /mo
Starting price
Visit Hostinger
Rating based on expert review
  • User Friendly
    4.7
  • Support
    4.7
  • Features
    4.8
  • Reliability
    4.8
  • Pricing
    4.7
IONOS
AU$1.50 /mo
Starting price
Visit IONOS
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.0
  • Features
    4.5
  • Reliability
    4.5
  • Pricing
    4.3
Ultahost
AU$4.34 /mo
Starting price
Visit Ultahost
Rating based on expert review
  • User Friendly
    4.3
  • Support
    4.8
  • Features
    4.5
  • Reliability
    4.0
  • Pricing
    4.8

 

How to Disable MySQL 5 "Strict Mode" on an Ubuntu 18.04 VPS or Dedicated Server

How to Disable MySQL Strict Mode on MySQL 5.6 Running on an Ubuntu 18.04 VPS.
2 min read
Idan Cohen
Idan Cohen
Marketing Expert

How To Install Apache, MySQL & PHP on an Ubuntu 18.04 VPS or Dedicated Server

This article shows users how to setup PHP, Apache web server and MySQL database
4 min read
Idan Cohen
Idan Cohen
Marketing Expert

How to Install PhpMyAdmin on a Ubuntu 18.04 VPS or Dedicated Server

This article will describe the process of installing phpMyAdmin on an Ubuntu 18.
3 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How to Install Apache Cassandra on an Ubuntu 18.04 VPS or Dedicated Server

This tutorial will help you install and configure Apache Cassandra (an opensourc
3 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top