MySQL Enterprise Backup is a known tool for everyone inside of the world of MySQL, no matter if people dealing with the tool are enterprise (business) minded or not. The tool can be considered the flagship of MySQL’s enterprise-level offerings: it comes as a part of its Enterprise tier and costs thousands to attain: is it worth your money, and perhaps more importantly, your time? We will figure that out in this blog.

Enterprise-level Backups and the Enterprise Tier of MySQL

In case you’re not aware of it yet, the Enterprise tier of MySQL can offer people a couple of interesting features and tools, including, but not limited to firewalls, data masking and de-identification, and, of course, backup features; the Enterprise Backup tool of MySQL makes MySQL capable to perform online, hot backups of any type: no matter if they’re full, incremental, or partial.

The enterprise tier of MySQL’s offerings is an important part of their business because it offers it all: monitoring, encryption, masking, auditing, a database-related firewall (we’ve covered some of the things it does in our security-related blogs), and, of course, the Enterprise Backup capability. The former is what we’re after for this blog post, so here’s what MySQL Enterprise Backup means for you as a user:

As you can see, MySQL Enterprise Backup isn’t very likely to disappoint – after all, you’ve paid money for a reason, right? You now know a thing or two some of the features offered by the tool – let’s now dig into how to best use it to your advantage.

Using MySQL Enterprise Backup

MySQL Enterprise Backup is known to offer a very wide array of choices for its users – some of which are outlined below. It should also be noted that MySQL Enterprise Backup primarily focuses on the InnoDB storage engine, but don’t worry too much if you don’t find yourself using it: you will still be able to use the tool, just your backups will be a little warmer than usual – the tool would only take warm backups in such a scenario. For most of us, the basic usage of the tool would look something similar to the following:

mysqlbackup --user=demo --password --port=3306 [one_or_more_options_here] backup

Here, mysqlbackup invokes MySQL Enterprise Backup, --user defines the user that you’re electing to use, --password without a password after it would grab the password from my.cnf, --port would specify the port, options defined after the port would specify any options you would need to use, and the backup command would start the backup procedure. Now, of course, you would like to know a little about the options you can invoke while using MySQL Enterprise Backup, so some of the common options include, but are not limited to:

For the full list of the available options, please head over to the documentation, but the list above should be able to give you a quick grasp of what you’re dealing with.

For example, to take a full backup, consider issuing a query like so:

mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/backup.bkp --backup-dir=/home/backupdirectory/ backup-to-image

It’s all pretty self-explanatory, really: the backup-image option would define a backup image where the backup would be stored, and the backup-to-image option would perform a full backup towards that image. Doesn’t get much easier than that, does it?

For an incremental backup, all we have to do is specify a LSN (Log Sequence Number) – everything would look something like this (we can also do it a little differently, have a look into the docs for additional methods):

mysqlbackup --defaults-file=/var/lib/mysql/my.cnf --incremental --start-lsn=XXXXXXXXXX --with-timestamp --incremental-backup-dir=/home/backups backup

For a partial backup, things don’t get much different either – for example, to exclude certain tables from being backed up, use the --exclude-tables option together with a regular expression:

mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/backup.bkp --exclude-tables=”^(demo_table|demo2)\.” --backup-dir=/home/backupdirectory/ backup-to-image

With that being said, backups can be also very easily encrypted. All we have to do is generate a key, then specify it when generating a backup – keys can be generated by using the “shasum” function like in this example:

echo “encrypted_with_this_password” | shasum -a 256

We can then encrypt the data by specifying the encryption key which is displayed once we run the command above like so:

mysqlbackup --backup-image=/directory/to/your/image.enc --backup-dir=/backups backup-to-image --encrypt --key=”YourKeyHere”

Data decryption, on the other hand, would look like something along those lines (here backup-image would link to the backup image, the decrypt option would specify that we’re decrypting a backup, the key file would provide MySQL with our key, and the directory after the backup-dir option would be the directory our backup would be stored in. The extract keyword is necessary to extract (decrypt) the backup):

mysqlbackup --backup-image=/directory/to/the/image.enc --decrypt --key-file=/home/Desktop/secrets/key --backup-dir=/home/backups extract

As you can see, there’s complex here either. From here on, though, we’ll leave it for you to experiment and take MySQL Enterprise Backup to the test – follow the advice given in this blog and you will surely put your backups on a whole new dimension, but before doing any changes on a live server, you should of course test them in a local environment instead – not doing so is a recipe for disaster.

We hope that this blog has helped you make your own enterprise backup recipe, and we’ll see you in the next one!