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 hosting, best 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:
- Learn here about the best dedicated server providers you can have today
- Want info about best web hosting? Clicking this link can be of great help.
