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:
|
Feature |
Meaning |
|---|---|
|
The ability to take multiple types of backups without sacrificing on performance |
MySQL Enterprise Backup is capable of taking hot, full, partial, and incremental backups without disturbing the performance of MySQL while completing the process. For more information on the types of backups available in MySQL, refer to the documentation. |
|
Compression and Encryption |
MySQL Enterprise Backup offers the ability to encrypt backups using the industry-standard AES256 capability as well as compress them using zlib, LZ4, or LZMA. |
|
The ability to include or exclude tables from a backup |
MySQL Enterprise Backup gives you the ability to quickly include or exclude tables you want or do not want to back up for your convenience. |
|
Backup validation and the ability to restore specific data |
MySQL Enterprise Backup comes with the ability to restore specific tables, it can also verify the backups that have been taken – such a feature can be exceptionally useful if we don’t want to validate our backups ourselves to save some time. |
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:
|
Option |
Explanation |
|---|---|
|
|
The command is able to create one file (the so-called “image”) holding all of the data that has just been backed up. |
|
|
The command, as the name suggests, is able to “copy back” (i.e. restore) a backup. |
|
|
The command is similar to the previous command: the core difference is that this command would also try to “update” all of the log files to “refresh” those file sand make them current. Cannot be used for incremental backups, though. |
|
|
As the name suggests, this option is able to validate a backup and make sure the data is not damaged and is ready to be re-imported into a given server. |
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!
The post Backing Up MySQL Part 7: MySQL Enterprise Backup appeared first on Simple Talk.
from Simple Talk https://ift.tt/ZQjxhHI
via
No comments:
Post a Comment