LabSrc

Right off the bat, I'll tell you that there is no completely secure way to automate your MariaDB backups. I researched this for awhile and haven't found one yet. That isn't to say there aren't ways to mitigate your risk, but as some point you're going to need to store your password somewhere. If you are unwilling to invite any risk to your databases, your best options are to automate entire backups of your server or to run your database backups manually. Not great alternatives, I know. However, if your need for automation outweighs your lust for tinfoil-hatted security, one of the most significant steps you can take in mitigating the risk to your data is encrypting your backups.

If you read my guide on encrypting MariaDB databases, Please Encrypt Your Databases, and put the time and effort into encrypting your databases, all that work will be for naught if you leave your database backups unencrypted. You could always choose not to backup your databases, but that would just be plain irresponsible...*cough*.

In this guide I'll show you how to automate the entire backup process while minimizing your exposure. This includes the aforementioned encryption process as well as taking extra steps to protect the keys used to encrypt your backups. As usual, I will be doing this with my current go-to Linux distro, Ubuntu 18.04 using MariaDB 10.3.

Before We Begin...

Instead of adding a note, I felt this topic was important enough to get it's own preface. This guide assumes that all of your databases were created with InnoDB tables. InnoDB is the transactional storage engine that has replaced MyISAM as the default storage engine in both MariaDB and MySQL. The biggest benefit in terms of backups is that InnoDB does not require that all tables in a database be locked while the backup is running. If your database contains MyISAM tables and they are backed up without being locked, it can lead to inconsistent backups.

If any of your non-system databases contain MyISAM tables, I highly suggest you convert them to InnoDB tables before proceeding with this guide. There are plenty of articles on the net that can walk you through what, in my experience, is a relatively painless process.

It's also important to note that the default system database named, mysql, contains MyISAM tables. It's important that you DO NOT CONVERT THE SYSTEM TABLES TO INNODB. They need to remain MyISAM tables for MariaDB to operate properly.

If you're unsure whether your non-system databases are using MyISAM tables, you can find out by running the following command within your MariaDB console. If this query comes back empty, then you can safely proceed with the rest of this guide.

SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema <> 'mysql'

With that friendly warning out of the way, let's begin!!

You Had One Job

The first thing we're going to do is create a new user in MariaDB whose sole job is run backups. This user will only have the ability to read databases, but won't have the ability to alter them. It's also only usable directly from the database server with remote access being disabled. Go ahead and log in to the MariaDB console on your database server to get started.

mysql -u root -p

Now we'll create our brave and stalwart backup user.

GRANT SELECT, SHOW VIEW ON *.* TO 'mdbbackup'@'localhost' IDENTIFIED BY 'password';
flush privileges;

*NOTE: Remember that this guide assumes that all of your non-system databases were created with InnoDB tables. Because of this, we do not need the LOCK TABLES privilege that is traditionally used when taking live backups.

The password for this backup user will be stored in a text file on the database server, so please make it as convoluted and complicated as possible, but be aware that some symbols may cause issues in MariaDB. And for the love of god, don't use your root password!

*TIP: If you're having trouble coming up with an unguessable password, you can use a password generator like pwgen to do the work for you.

For Your Eyes Only

Now we'll create the MariaDB Option file which will store the credentials for our backup user.

sudo touch /etc/mysql/mariadb.conf.d/backup.cnf
sudo chmod 600 /etc/mysql/mariadb.conf.d/backup.cnf
sudo nano /etc/mysql/mariadb.conf.d/backup.cnf

Add the following section into the Option file and type in your backup user's credentials.

*NOTE: The system user that executes the actual backup command needs to have read access to the backup.cnf file. This file contains the MariaDB backup user's precious credentials, so we'll be running the backup command as root and restrict access to the file accordingly. If you desire even more work...I mean security, you can create an entirely new user on your server for the sole purpose of creating backups.

The Keys to Encryption

If you've made it this far, you may be wondering how this guide is any different from the dozens of others you've seen on the web. Well the main difference is we're not only going to encrypt our backups. We're going to encrypt our encryption keys as well. This means that even if someone gets ahold of your backups and your keys, they still won't be able to decrypt your backups without the encrypted key's passphrase. Just another layer of security to keep your data safe and cozy.

We'll kick this off by generating a pair of encryption keys. The public key in this pair will be used to encrypt all of our backups and the private key will be used strictly for decryption purposes.

Generate a Private Key

sudo openssl genpkey -algorithm RSA -out /etc/mysql/mdbbackup-priv.key -pkeyopt rsa_keygen_bits:4096 -aes256

Enter a brand new passphrase when prompted and I emphasize you do not use the same password as the MariaDB Backup user.

Like the passphrase we generated for the MariaDB backup user, make sure it's long and complicated. Since we'll only be needing this passphrase when decrypting backups, we don't need to store this passphrase on the server. Just remember that if you lose this passphrase, all of your backups will be useless.

Now we'll extract the public key from our private key in the x.509 format.

Extract the Public Key

sudo openssl req -x509 -nodes -key /etc/mysql/mdbbackup-priv.key -out /etc/mysql/mdbbackup-pub.key

Enter the private key's passphrase when prompted. You can leave the rest of prompts blank and hit Enter for each.

*NOTE: If you've ever gone through process of obtaining an SSL cert, these prompts will probably look familiar. However, since we won't be using these keys to verify our identity, the fields aren't important.

Initially, we have both keys stored in the /etc/mysql directory. While the public key needs to remain on the database server to encrypt our databases, the private key should be housed separately. Preferably, you'll want to store the key in a completely different location from the backups themselves. Even though an attacker would still need the private key's passphrase to decrypt them, it's still best to keep them as far apart as possible.

*NOTE: Some of you may be wondering why the need for x.509. Due to the file size limitations of the RSA cypher, we're going to use the OpenSSL SMIME utility to encrypt our databases with an AES 256 bit cypher which uses the x.509 format. And yes, technically our public key is more commonly known as a public cert, but to avoid confusion I just named it with a .key extension.

Encrypting on the Fly, Compressed?

Finally, we're at the actual backup step. We'll be using mysqldump as our method of creating our MariaDB backups. On Ubuntu, there is a customized version of mysqldump specifically for MariaDB that gets installed with the base MariaDB package. We'll also be using bunzip2 to compress our backups which saves you a lot of precious disk space.

*TIP: You can check which version of mysqldump you're running by looking at the first line of the output of mysqldump --help.

To start off we'll test out backing up and encrypting a single database.

sudo mysqldump --routines --quick --single-transaction testdb | sudo bzip2 | sudo openssl smime -encrypt -binary -text -aes256 -out testdb.sql.bz2.enc -outform DER /etc/mysql/mdbbackup-pub.key

This command will backup the database testdb along with any stored procedures within the database. Since we're piping our mysqldump backup directly to bunzip2 and then into the OpenSSL smime utility, the backup is compressed & encrypted on the fly with no chance for snooping.

Now we'll test decrypting the database. You'll be prompted for your private key passphrase during this process.

sudo openssl smime -decrypt -in testdb.sql.bz2.enc -binary -inform DEM -inkey /etc/mysql/mdbbackup-priv.key -out testdb.sql.bz2

You can restore the database along with any stored procedures with the following command. You'll need to do this with a MariaDB user such as root that has at the very least Create & Create Routines privileges for this database. The restore will not work with the backup user we've been using thus far.

bunzip2 < testdb.sql.bz2 | mysql -u root -p testdb

I'm Triggered, Maybe

There may be a couple more things in your database server that you may want backed up along with your databases and stored procedures. Most notable are Triggers & Events.

The only way to back them up is to grant your backup user more privileges. Unfortunately, these privileges also allow the backup user to potentially alter these features, so I would only grant privileges for the ones you actually use.

GRANT TRIGGER, EVENT on *.* to mdbbackup@localhost;
FLUSH PRIVILEGES;

To backup all of your databases with all these features included, you can run the following command.

sudo mysqldump --routines --triggers --events --quick --single-transaction --all-databases | sudo bzip2 | sudo openssl smime -encrypt -binary -text -aes256 -out alldb.sql.bz2.enc -outform DER /etc/mysql/mdbbackup-pub.key

Do It For Me, Please

At this point, you may be questioning how any of this actually makes your life easier. Well your patience and ability to endure boring tech speak has finally paid off. We're going to make the server do all the work for us by automating our backups with a little utlity called cron.

For those unfamiliar with cron, it's a simple, but magnificent utlity that's installed on pretty much every Linux distro. It's primary purpose is to do your work for you by running any command on a scheduled interval. All we need to do is setup a schedule, input our backup command and we'll have Aunty Maria's freshly baked, encrypted & compressed database backups hot out of the server.

First we'll launch root's crontab with the following command.

sudo crontab -e

Add this slightly modified backup commmand which will save the backup to /var/lib/mysql everyday at 1:00AM.

00 01 * * * mysqldump --routines --triggers --events --quick --single-transaction --all-databases | bzip2 | openssl smime -encrypt -binary -text -aes256 -out /var/lib/mysql/alldb_$(date +\%Y-\%m-\%d).sql.bz2.enc -outform DER /etc/mysql/mdbbackup-pub.key && chmod 600 /var/lib/mysql/alldb_$(date +\%Y-\%m-\%d).sql.bz2.enc

The major differences between this command and the one we used in the last section is we removed sudo from each command because this cron job will be running as root. We also added $(date +\%Y-\%m-\%d) to the filename which will add the current date so your backups don't overwrite themselves.

At the end of the command, we run chmod to limit permissions to the backup to read only access for root.

In the example cron job above, backups are saved locally within the same directory that houses your databases. This was done purely to make the example easier to understand. Ideally, backups should be stored offsite or on a different server. If you have no other choice but to store your backups locally, you'll want them to be kept on an external drive or at the very least on a separate disk. The last thing you need is for your server to die and all of your backups to go kaput with it.

With tha being said, remember to change /var/lib/mysql to your desired location before saving your cron job.

Remote Backups, Engage

*UPDATE: I just posted a follow-up article on how to create a SFTP Chrooted Jail for backups. After you're done reading this guide, feel free to check it out if you're interested in securely storing and transferring your backups to a remote server.

If you do decide to heed my advice and you happen to have a remote server with SSH access available, you can modify the cron job to copy the backups to another server with just a few tweaks. Just remember not to store your private key on that server along with your backups. Security is not about convenience. If it's not a pain in the butt, it's not secure.

00 01 * * * mysqldump --routines --triggers --events --quick --single-transaction --all-databases | bzip2 | openssl smime -encrypt -binary -text -aes256 -outform DER /etc/mysql/mdbbackup-pub.key | ssh user@serverIP "cat > alldb_$(date +\%Y-\%m-\%d).sql.bz2.enc && chmod 600 alldb_$(date +\%Y-\%m-\%d).sql.bz2.enc"

In this cron job, we've removed the -out /var/lib/mysql/alldb_$(date +\%Y-\%m-\%d).sql.bz2.enc portion of the encryption command and instead pipe the backups directly into SSH. SSH connects to your remote server and writes the backup directly to the remote user's home directory. The beauty of this is the backup is never stored locally on the database server.

For this to work, you'll need to setup a pair of passphrase-less SSH keys which inherently has it's own set of risks and whose setup is beyond the scope of this guide. If you do decide to go this route, I would highly recommended creating a brand new user on the remote server and locking it in a chrooted jail while using a restricted shell like rbash. I sense a future article idea.

Alternatively, you can use a utility like keychain to allow cron to access an SSH key's passphrase, but this requires you to login with the backup user at least once everytime your server reboots. Not the most reliable method when it comes to backups, but it's definitely safer than passphrase-less SSH keys.

*NOTE: There is another way to execute remote backups that I haven't mentioned and that's running mysqldump directly from a remote server. While this method may simplify some things, it requires granting remote access to your MariaDB instance. Because the MariaDB backup user at the very least has read rights to all of your databases, it's not something I'm comfortable recommending.

The last thing we'll go over is how to restore one or more databases from your encrypted backups.

Recovering From the Apocalypse

I've already shown you how to restore a single database from a single DB backup. Now we'll do the same from the backup that contains all of our databases. Like earlier we'll first need to decrypt our backup using our private key.

The following command assumes the private key still exists in /etc/mysql, but I know you'll be moving it to a new secure location right after this. Please don't forget to do that!

sudo openssl smime -decrypt -in alldb.sql.bz2.enc -binary -inform DEM -inkey /etc/mysql/mdbbackup-priv.key -out alldb.sql.bz2

After entering your private key's passphrase, you'll be left with an unencrypted, but still compressed backup.

Restoring Just One

If you want to restore a single database, the database needs to exist before the restoration can begin. If you're restoring to the original database, all existing data will be overwritten with your backup. Also remember that the restore must be run with a user that has write access to the database such as your MariaDB root user.

bunzip2 < alldb.sql.bz2 | mysql -u root -p --one-database testdb

In this example, we use bunzip2 to unzip our full backup on the fly and pipe it directly into MariaDB via the mysql client while restoring only a single database named testdb.

Restoring Everything

In the event of a major catastrophe, you can opt to restore all of your databases from the full backup either to the same MariaDB server or to a newly created MariaDB server with the following command. This won't delete any databases that were created after the backup was taken. In only overwrites databases that existed at the time the backup was taken.

bunzip2 < alldb.sql.bz2 | mysql -u root -p

*TIP: Restoring everything from your full backup will also recreate all MariaDB user accounts including their passwords. If restoring to a brand new server, make sure to either restart MariaDB after the restoration or log into the MariaDB console and run flush privileges; for the user accounts to work properly.

Final Thoughts

This was one of the more difficult guides for me to write as there was a lot of trial and error on my part and some less than ideal methods I had to employ.

I'm hoping some time soon the people behind MariaDB start developing a more robust and secure backup utility for the world to utilize. I've looked at Mariabackup which is MariaDB's fork of XtraBackup, but for my purposes it didn't seem to have much added benefit. However, if my databases were extremely large, I might be singing a different tune. Some things it does have going for it is support for incremental backups and reportedly much faster restore times so I'll probably need to revisit it in the near future.

I know this guide has room for improvement and I'm very open to hearing alternative or better methods to automating and securing database backups. I wanted to add a section about rotating or cleaning up old backups, but this guide was starting to run long and I didn't want anyone dropping dead from boredom. Please feel free to weigh in and comment down below.