Database backups are vital to recover your business critical databases.

Database failures can usually occur due to corruption of database files, damage to the file systems, software and hardware failures, etc.

So, a reliable database backup is always necessary for the database recovery.

At Bobcares, we help server owners setup regular database backups as part of our Managed Cloud Services.

Today, we’ll discuss how we setup Vultr database backup and fix common errors with it.

Vultr Database backup – How to do it?

Databases backups are really important when dealing with live websites. It’s really important to maintain regular database backups regardless of your business type.

Vultr offers automatic backup features that allow you to take free snapshots of your whole Vultr instance. But, the downside is that we can restore whole server from snapshot, not individual databases. It can also cause data inconsistencies in write intensive databases.

So, our Support Engineers take further steps to ensure consistent database backup on Vultr server. Now, let’s discuss the different methods to create database backup on Vultr instance.

1) Using mysqldump utility

Our Support Experts use the mysqldump utility to backup up MySQL or MariaDB databases. Databases are usually backed in .sql format. For example, to backup a single database, we use the following command.

mysqldump -u username -p database_name > database_backup.sql

Here, username is the name of the database user, database_name is the database to be backed up. And database_backup.sql is the file name where the database is backed up. This command will prompt for the MySQL user password. Once we give the correct password, we’ve the database backup in the file database_backup.sql.

However, if you have multiple MySQL databases on your system, it becomes a tedious task. In such cases, our Support Engineers write custom backup script to take the dump of all the databases on the Vultr instance.

Similarly, we use the pg_dump utility to backup PostgreSQL databases. For instance, we run the following command as super user(root) to backup a single PostrgreSQL database.

pg_dump -U username database_name > database_backup

Further, we use the command pg_dumpall if all the PostgreSQL databases need to be backed up.

a) Enable automatic backups

However, it’s a tiresome task to execute the above commands manually. That’s why, our Server Experts always receive  customer requests to automatically set the backup. In such cases, we create custom backup scripts to backup all databases at once and schedule it using cron jobs.

For example, consider we want to backup multiple databases. Firstly, we create a database user to handle these backups and assign proper privileges to this user. After that, we create a backup directory with environment and log files. Further, we export the MySQL backup user credentials to the environment file. Finally, we create a custom script to backup all the databases.

In addition to that, we write cron jobs to schedule this backup script to run at alternate intervals.

b) Enable remote backups

Further, keeping the database backups on the same server is a risk, because there’s no way to recover the backups when your Vultr instance crashes. That’s why, we always recommend customers to store database backups in remote storage locations like Google Drive, Amazon S3 etc.

Alternatively, in some cases we create custom scripts to transfer the database backups to a backup server or an alternate server on another location. For instance, in some cases we use FTP/SFTP to transfer the backups to remote host. Likewise, we use rsync command to sync the database backups from one server to another.

2) Using Backup tools

Another way to backup the databases is to use backup tools such as Automysqlbackup, MySQLDumper, etc. These utilities can be used to schedule automatic database backups at regular intervals.

For example, we can easily install Automysqlbackup utility in Ubuntu servers. The default location of the backups will be /var/lib/automysqlbackup. We specify the database user, password, the database to be backed up in the configuration file /etc/default/automysqlbackup.

[Need to setup database backup on your Vultr instance? Click here and get one of our Support Experts to look into your requirement.]

Vultr Database backup – Common failure points

Let’s now see the common errors that can occur during the Vultr database backup process and how our Support Experts fix them.

1) Mysqldump errors

Sometimes, users receive weird errors when using mysqldump command to backup the databases. Once such error is given below.

mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect

This can be due to MySQL downtime, incorrect permissions of MySQL socket file, MySQL running on a custom port, and so on. So, here our Support Engineers check each of these causes and rule out each possibility to fix the issue.

Similarly, mysqldump errors can occur when users give incorrect username or password, improper privileges for the database user, and more.

2) Backup tool errors

Likewise, database backup tools can also throw errors if not properly configured. One such error that we recently saw with Automysqlbackup utility is given below.

#Checking for permissions to write to folders:
base folder /var/backup ... does not exist. Exiting.
Note: Supplied more than one argument, ignoring ALL arguments - using default and global config file only.
Note: /etc/automysqlbackup/automysqlbackup.conf was not found - no global config file.
Error: No basedir found

Here, our Support Engineers ensure that the backup directory is valid and accessible. Moreover, we correct the permissions of this directory to fix the issue.

This error list is not complete. You can come up with number of errors while configuring database backups.

[Need an expert to fix your Vultr database backup errors? Our Database Experts can help you here.]

Conclusion

In short, it’s really important to maintain consistent database backups for disaster recovery. Today, we’ve discussed how our Dedicated Engineers setup database backups on a Vultr instance and fix the common errors with it.