What is database backup?
Database backup is the process of backing up the operational state, architecture and stored data of a database software through database replication. It can be done for both databases and database servers, making it highly advantageous for organizations to store data.
Why is it required?
Database backup is performed to ensure a company’s compliance with business and government regulations as well as to maintain and ensure access to critical/essential business data in case of a disaster, technical outage or even a breech.
The database is the core of any application's data management and a place where all or maybe most of the data is stored. Database administrators can use this database backup copy to restore the database to its operational state along with its data and logs and hence prevent any loss of important information. The information obtained from a database backup can be stored locally or on a backup server.
Duplicity
Duplicity is a server backup and restoration tool which implements a traditional backup scheme where the initial archive contains the information (full backup) and only the changed information is added going ahead.
It is a free, open-source and advanced command-line backup utility tool which is built on top of librsyncand GnuPG. It produces digitally signed, versioned and encrypted tar volumes for storage on a local or remote computer. Duplicity supports many protocols for connecting to a file server including ssh/scp, rsync, ftp, DropBox, Amazon S3, Google Docs, Google Drive etc.
Here is the link to the official docs for your reference.
About this article
In this article we basically discuss two aspects:
- Using Duplicity for database backup.
- Restoring stored data onto a new server.
You can refer to my previous article on Duplicity to get started with the tool.
For this article, I have used three CentOS servers one of which will be a database production server, a backup server and a third server where the restoration of data can take place. (DB-prod, DB-backup and New-Restore Server). We will be using MySQL as the database here.
So let's go ahead and explore the tool
Using Duplicity for database backup
Install duplicity
- For the installation of Duplicity, run the following code on the
DB-prod server:
yum install epel-release
yum install duplicity
duplicity
- This snippet only needs to be installed on the prod server and not on the backup server. Here is the link to the other installation guides
Establish a connection between servers
- Create a
ssh key on the prod server and add .ssh/id_rsa.pub to the *ssh/authorized_keys of the backup server to establish a secure ssh connection. Use the following code snippet to proceed:
ssh-keygen -t rsa -m PEM
Setup for encryption
Duplicity can use GnuPG(GNU Privacy Guard) which allows you to encrypt and sign your data and communications; it features a versatile key management system along with access modules for all kinds of public key directories. GnuPG, also known as GPG, is a command line tool with features for easy integration with other applications.
MySQL installation on the three servers
- Install and configure MariaDB to proceed. It is fairly simple to install and set up. We can install the
dnf key on CentOS using the following snippet:
dnf install mysql-server
systemctl start mysqld.service
systemctl status mysqld
systemctl enable mysqld
mysql_secure_installation
Create MySQL dumps
Create a MySQL backup folder to store the MySQL dumps on the same machine. In Mysql, the best way to create the dumps of databases and restore databases from the dumps whenever required is to create this folder.
The mysqldump client utility tool which is provided performs logical backups which produces a set of SQL statements that can be executed to reproduce the original database object definitions and table data.
- In this example, let us consider that we have a
testDB with an authors table inside of it. Enter the following code snippet to continue:
mysql -u root -p
CREATE DATABASE testDB;
use testDB;
CREATE TABLE authors ( id INT, name VARCHAR(20), email VARCHAR(20));
INSERT INTO authors (id,name,email) VALUES(1,"Aditya","aditya@xyz.com");
INSERT INTO authors (id,name,email) VALUES(2,"Prakash","prakash@xyz.com");
INSERT INTO authors (id,name,email) VALUES(3,"nihal","nihal@yahoo.com");
- Use the following code snippet to create the dump:
cd mysql-backup/
mysqldump -u root -p testDB authors > authors.sql
Create backup
- We can create a backup of the entire
mysql-backup directory using the following snippet:
PASSPHRASE='<passphrase>' duplicity --encrypt-key <encrypt-key> mysql-backup sftp:
- Here, we will define the source of the directory that needs to be backed up(
mysql-backup) along with the encryption and destination server and folder(<db-backup_server>://root/remoteBackup/db-prod/dbbackup) which will create a full backup for you.
Note: Any subsequent running of this command will lead to the creation of incremental faster backups.
- If we list the content of the destination folder on the backup server which is
/root/test/home, we will find that the data which is stored/backed up has been encrypted with an ls -la element. Refer to the image below:
Unlike first article of this series, we will try to restore the backed-up content on a new server. But for that, we will need the same gpg keys on the new server so that it can decrypt the data that it pulls from the backup server.
Restoring Duplicity-stored data onto a new server.
Export the gpg keys of DB-prod to DB-backup
Use the following code snippet to store gpg keys in a file:
gpg --export-secret-keys > private.key
Fill in the PASSPHRASE when prompted to proceed with the restoration of data and transfer it to the backup server using the SCP element and the following code snippet:
scp private.key root@<db-backup_server>:/root/gpg_key_store/db-prod
Follow the earlier mentioned steps to install Duplicity along with a secure -ssh connection which has been established between the new restore server and the DB-backup server.
- Import the
private.key on the new restore server from DB-backup server and fill in the PASSPHRASE when prompted. Use the following code snippet to proceed:
scp root@<db-backup_server>:/root/gpg_key_store/db-prod/private.key private.key
gpg --import private.key
Restore the data
Restore data from the backup server to a required destination. Duplicity is smart enough to recognize that first parameter is the source and the second is the destination. Enter the following snippet into your console to proceed:
PASSPHRASE='<passphrase>' duplicity --encrypt-key <encrypt-key> sftp:
Run the following MySQL commands to go ahead with the restoration process:
mysql -u root -p
CREATE DATABASE testDBnew;
- Then, run the following command on the terminal:
mysql -u root -p testDBnew < <path to dump>.sql
- The major advantage of using Duplicity as the tool for the database backup is that the backed up data is encrypted unlike any other popular systems like
mysql-replication.
Conclusion
With Duplicity you can backup important files to a local drive or SFTP server, and restore them with ease. Duplicity is not just a backup solution; it allows you to keep a full history of file versions in your backup location and restore any version, at any time. After going through both my articles on Duplicity, you can now backup any data from any location with it being encrypted and easily restorable.
Hope you liked this article.
