MySQL database encrypted backup

Hello there, we will be learning how to take the encrypted backup of a MySQL database.

MySQL and MariaDB include the mysqldump utility to simplify the process to create a backup of a database or system of databases. Using mysqldump creates a logical backup. You can only use this tool if your database process is accessible and running.

Before You Begin :

You will need a working MySQL or MariaDB installation, and a database user to run the backup.

Installation steps for Ubuntu :

sudo apt update
sudo apt install mysql-server -y

The installer installs MySQL and all dependencies.

sudo mysql_secure_installation

After installation is complete, the mysql_secure_installation utility runs. This utility prompts you to define the mysql root password and other security related options, including removing remote access to the root user and setting the root password.

root@ip-10-0-1-157:~# sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : n

 ... skipping.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

After the installation is complete, you can start the database service by running the following command. If the service is already started, a message informs you that the service is already running:

systemctl start mysql

To ensure that the database server launches after a reboot, run the following command:

systemctl enable mysql

Start the mysql shell :

There is more than one way to work with a MySQL server, but this article focuses on the most basic and compatible approach, the mysql shell.

  • At the command prompt, run the following command to launch the mysql shell and enter it as the root user:
sudo mysql -u root -p
  • When you’re prompted for a password, enter the one that you set at installation time, or if you haven’t set one, press Enter to submit no password.
  • The following mysql shell prompt should appear:
mysql>

Set the root password :

If you logged in by entering a blank password, or if you want to change the root password that you set, you can create or change the password.

  • For versions earlier than MySQL 5.7, enter the following command in the mysql shell, replace password with your new password:
UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';
  • For version MySQL 5.7 and later, enter the following command in the mysql shell, replacing passwordwith your new password:
UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root';
  • To make the change take effect, reload the stored user information with the following command:
FLUSH PRIVILEGES;

Create Database, table and insert some data:

There is a difference between a database server and a database, even though those terms are often used interchangeably. MySQL is a database server, meaning it tracks databases and controls access to them. The database stores the data, and it is the database that applications are trying to access when they interact with MySQL.

To create a database, log in to the mysql shell and run the following command, replacing demodb with the name of the database that you want to create:

mysql> CREATE DATABASE customer;
Query OK, 1 row affected (0.00 sec)

Show databases:

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customer           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

Use the customer database:

mysql> USE customer;
Database changed

Create a demo table:

mysql> CREATE TABLE cus_tbl(
    ->    cus_id INT NOT NULL AUTO_INCREMENT,
    ->    cus_firstname VARCHAR(100) NOT NULL,
    ->    cus_surname VARCHAR(100) NOT NULL,
    ->    PRIMARY KEY ( cus_id )
    -> );

Query OK, 0 rows affected (0.06 sec)

Show tables in customer database:

mysql> SHOW tables;
+--------------------+
| Tables_in_customer |
+--------------------+
| cus_tbl            |
+--------------------+
1 row in set (0.00 sec)

Insert some data:

mysql> INSERT INTO cus_tbl
    -> (cus_id, cus_firstname, cus_surname)
    -> VALUES
    -> (5, 'Ajeet', 'Maurya'),
    -> (6, 'Deepika', 'Chopra'),
    -> (7, 'Vimal', 'Jaiswal');

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Verify the data in newly created table:

mysql> SELECT * from cus_tbl;
+--------+---------------+-------------+
| cus_id | cus_firstname | cus_surname |
+--------+---------------+-------------+
|      5 | Ajeet         | Maurya      |
|      6 | Deepika       | Chopra      |
|      7 | Vimal         | Jaiswal     |
+--------+---------------+-------------+
3 rows in set (0.00 sec)

Exit the MySQL shell prompt:

mysql> exit
Bye

Back up a DatabasE :

The mysqldump command’s general syntax is:

mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql
  • mysqldump prompts for a password before it starts the backup process.
  • Depending on the size of the database, it could take a while to complete.
  • The database backup will be created in the directory the command is run.
  • -$(date +%F) adds a timestamp to the filename.

I see a lot of people doing this:

mysqldump --user=root --password=foo --host localhost database > database.sql

Even if you start the above command as root, any user on your server can view the process list :

ps axw | grep mysql
40802   ??  S     33:05.94 mysqld
42176 s005  S+     0:03.86 mysqldump --user=root --password=foo database

Here is what MySQL says to it:

MySQL End-User Guidelines for Password Security :
Specifying a password on the command line should be considered insecure. You can use an option file to avoid giving the password on the command line.

Option file :

The best solution to this problem is to create a mysql option file :

ubuntu@ip-10-0-1-157:~$ ll | grep mysql
-rw-rw-r-- 1 ubuntu ubuntu   63 Mar 10 07:47 .mysqldump

ubuntu@ip-10-0-1-157:~$ cat ~/.mysqldump
[mysqldump]
host = localhost
user = root
password = "password"

With this you can do the following call:

mysqldump --defaults-extra-file=~/.mysqldump database > database.sql

The process table can no longer reveal the password:

ps axw | grep mysql
40802   ??  S     33:05.94 mysqld
42176 s005  S+     0:03.86 mysqldump --defaults-extra-file=~/.mysqldump database

The next step is to secure the mysqldump file. MySQL recommends 600, I would even recommend 400 to also prevent it from accidentally modifying.

Encryption :

Symmetric vs Asymmetric :

Before you are going to encrypt your database dumps you have to choose a method which best suits security.

Symmetric Encryption :

You have one key or password which is used for encryption and also for decryption. If someone gets hands on this key/password on the server which does the backup your database dumps are considered compromised. So you have to protect it very well on a filesystem and process level.

Asymmetric Encryption :

Also known as public-private key encryption. It overcomes some of the above security problems. You have one key which can be publicly available that is used to encrypt your data. If this key is compromised, nothing bad can happen. The private is held on a different location which is only used to decrypt your backups. You should also never loose this key, as you won’t be able to restore your backups anymore.

With this method each of your servers can hold a copy of the same public key and decrypt the backup.

Conclusion :

Go with asymmetric encryption!

You can use OpenSSL S/Mime which can handle large file encryption as well.

Race conditions :

If you encrypt your backups, make sure not simply write the unencrypted files to disk, encrypt them and delete the original files. This leaves a door open for an attacker to view the unencrypted data in the moment it is written to disk.

You better pipe the dumps directly to the encryption process and then write it to disk

Example :

Generate public-private keypair :

Generate a 2048 bit strong rsa keypair:

ubuntu@ip-10-0-1-157:~$ openssl req -x509 -nodes -newkey rsa:2048 -keyout mysqldump-key.priv.pem -out mysqldump-key.pub.pem
Generating a 2048 bit RSA private key
...............................+++
.....+++
writing new private key to 'mysqldump-key.priv.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:IN
State or Province Name (full name) [Some-State]:UP
Locality Name (eg, city) []:Noida
Organization Name (eg, company) [Internet Widgits Pty Ltd]:SimplyDevOps
Organizational Unit Name (eg, section) []:DevOps
Common Name (e.g. server FQDN or YOUR name) []:Vishnu
Email Address []:vishnuharidadhich@gmail.com

Encrypt :

Use the public key to encrypt:

openssl smime -encrypt -binary -text -aes256 -in database.sql -out database.sql.enc -outform DER mysqldump-secure.pub.pem

Encrypt without race condition. Note the echo command is just a representation. Later on the mysqldump will be parsed to openssl.

echo "output" | openssl smime -encrypt -binary -text -aes256 -out database.sql.enc -outform DER mysqldump-secure.pub.pem

Decrypt :

Use the private key to decrypt

openssl smime -decrypt -in database.sql.enc -binary -inform DEM -inkey mysqldump-secure.priv.pem -out database.sql

Mysqldump Options :

Triggers, Routines and Events :

If you use mysqldump without any parameters, only triggers are dumped, stored procedures and events will not be backed up. This is just the default behaviour.
So in order to also dump stored procedures and events you need to specify the following:

mysqldump --routines --events database > database.sql

Mysqldump automatically adds --triggers, but just to be on the safe side in case future versions will remove it, append it as well.

mysqldump --routines --events --triggers database > database.sql

Transactional Table Options :

If you have at least one database with transactional tables (for example innoDB tables). You should add the single-transaction parameter, which makes sure that you dump the database in a single transaction and that it stays consistent.
This is actually best explained with a negative example:

  • Table A and table B have some kind relations (If something is deleted from B, it needs to be deleted from A as well)
  • You start dumping table A without single transactions
  • Another process deletes rows from table B which has relations to table A
  • You start dumping table B without single transactions

Now you are left with records in table A that do not have any associations in your table B.
Single transaction basically creates a checkpoint and dumps the data as it was exactly at this very moment.

So the dump command would now look like this :

mysqldump --routines --events --triggers --single-transaction database > database.sql

Working example :

The final version to dump a single database securely looks like this:

mysqldump --defaults-extra-file=/etc/mysqldump.cnf \
    --routines --events --triggers --single-transaction database | \
    openssl smime -encrypt -binary -text -aes256 -out database.sql.enc -outform DER mysqldump-secure.pub.pem

We will create a bash file to perform this task seamlessly :

ubuntu@ip-10-0-1-157:~$ cat create-backup.sh
#!/bin/bash

DATE=`date +%Y-%m-%d-%H-%M-%S`
ARCHIVE=${DATE}.sql.gz.enc
MYSQLINFO=~/.mysqldump
DATABASE=customer
PUBLIC_KEY=~/mysqldump-key.pub.pem

mysqldump --defaults-extra-file=${MYSQLINFO} ${DATABASE} --single-transaction --routines --events --triggers \
  | gzip -c \
  | openssl smime -encrypt -binary -text -aes256 -out ${ARCHIVE} -outform DER ${PUBLIC_KEY}

ubuntu@ip-10-0-1-157:~$ chmod u+x create-backup.sh

ubuntu@ip-10-0-1-157:~$ ls -al | grep backup
-rwxrw-r-- 1 ubuntu ubuntu  365 Mar 10 08:25 create-backup.sh

Run the created bash script to create an encrypted backup:

ubuntu@ip-10-0-1-157:~$ ./create-backup.sh

ubuntu@ip-10-0-1-157:~$ ls -al | grep 2019
-rw-rw-r-- 1 ubuntu ubuntu 1322 Mar 10 08:33 2019-03-10-08-33-02.sql.gz.enc

To verify the encrypted backup we will cat the output which clearly is not human readable :

ubuntu@ip-10-0-1-157:~$ cat 2019-03-10-08-33-02.sql.gz.enc
��0�ishnuharidadhich@gmail.com

Yayy! We have successfully created an encrypted backup of a MySQL database.

DEcrypt :

Now, we will decrypt it to get the original backup which can be used to restore the database. To decrypt we will use commands :

ubuntu@ip-10-0-1-157:~$ openssl smime -decrypt -in 2019-03-10-08-33-02.sql.gz.enc -binary -inform DEM -inkey mysqldump-key.priv.pem -out mysql-backup
.sql.gz

ubuntu@ip-10-0-1-157:~$  gzip -d mysql-backup.sql.gz

ubuntu@ip-10-0-1-157:~$ ls -al mysql-backup.sql
-rw-rw-r-- 1 ubuntu ubuntu 2120 Mar 10 08:39 mysql-backup.sql

ubuntu@ip-10-0-1-157:~$ head mysql-backup.sql
-- MySQL dump 10.13  Distrib 5.7.25, for Linux (x86_64)
--
-- Host: localhost    Database: customer
-- ------------------------------------------------------
-- Server version       5.7.25-0ubuntu0.18.04.2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

As we can see the mysql-backup.sql file contains human readable SQL commands to restore our customer database.

Automate Backups with cron :

Entries can be added to /etc/cron.daily/ to regularly schedule database backups.

Below is an example cron job to back up the entire database every day at 1 AM :

ubuntu@ip-10-0-1-157:~$ cat /etc/cron.daily/mysqldump
0 1 * * * /home/ubuntu/create-backup.sh

For more information on cron, see the cron(8) and cron(5) manual pages.

Restore MYsql database :

We shall use the redirection to restore the MySQL database as following to restore an entire database backup. You will be prompted for the MySQL root user’s password :
This will overwrite all current data in the MySQL database.

ubuntu@ip-10-0-1-157:~$ mysql -u root -p customer < mysql-backup.sql
Enter password:

More Information :

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

Thank you for reading the post.

Feel free to contact me in case anything bothers.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s