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 -yThe installer installs MySQL and all dependencies.
sudo mysql_secure_installationAfter 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 mysqlTo ensure that the database server launches after a reboot, run the following command:
systemctl enable mysqlStart 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
mysqlshell 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
mysqlshell 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
mysqlshell, replacepasswordwith 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
mysqlshell, replacingpasswordwith 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 changedCreate 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: 0Verify 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
ByeBack up a DatabasE :
The mysqldump command’s general syntax is:
mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sqlmysqldumpprompts 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.sqlEven 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 databaseHere 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.sqlThe 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 databaseThe 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.comEncrypt :
Use the public key to encrypt:
openssl smime -encrypt -binary -text -aes256 -in database.sql -out database.sql.enc -outform DER mysqldump-secure.pub.pemEncrypt 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.pemDecrypt :
Use the private key to decrypt
openssl smime -decrypt -in database.sql.enc -binary -inform DEM -inkey mysqldump-secure.priv.pem -out database.sqlMysqldump 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.sqlMysqldump 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.sqlTransactional 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.sqlWorking 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.pemWe 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.shRun 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.encTo 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.comYayy! 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.shFor 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.
DevOps: Culture and Process 