A few weeks ago we hosted a webinar together with Acronis and CPanel on security and backup best practices as both provide software in this space. Acronis develops Cyber Security cloud based software particularly specializing in backup and disaster recovery for other service providers. CPanel in turn helps those service providers to enable their customers to administer their systems via a fully fledged dashboard. The webinar was a follow-up on another join presentation at CloudFest. The webinar presented quite a number of good ideas and I highly recommend you watch it. Then again, a webinar is always time constrained so we couldn’t cover everything that I would have liked. Thus, in this blog post I will cover some of the aspects, particularly related to MariaDB, that were not discussed due to lack of time.
Backing up is a very important topic, often overlooked, particularly when it comes to databases. Why is backing up so important? Well, as Jeff Hardy from Acronis puts it in the webinar, as well as his “Economic model for cyber protection” at our MariaDB Server Security MiniFest, data is the equivalent of gold in the IT industry. This means that data loss can severely cripple a company. Then again, not all data is created equal. Some data is more important. For example, losing some sensor data for a weather station has much lower impact than losing customer transactions. This line of thinking is what should guide you when considering backup requirements and is what brings us to the first item in this list.
Consider your scenario
Before you start implementing a backup system, take a look at the system’s needs. Are you looking after a simple WordPress website that sees edits once a few days or weeks? Is this a somewhat popular online store, with 100-200 orders a day? Or maybe you’re running an online gaming platform that gets thousands of concurrent users, where all their actions need to be recorded. Depending on the scenario you’re in, you should plan accordingly.
For our WordPress example, it’s probably sufficient to schedule a backup every night for a week and also keep bi-weekly or monthly snapshots. The costs associated with backing up a WordPress website are usually quite low, as most of the time the database itself is rather small.
When we look at our online shop scenario, things get a little bit more complicated. With nightly backups we do not cover the current day’s orders. Losing those could be quite costly. At this point, depending on volume, we should start considering ways of mitigating an outage during the workday.
When we look at our gaming platform scenario however, we start running into really problematic cases. Usually popular services carry a hefty cost associated with downtime. And although high availability is an orthogonal topic, which I hope to cover in the future, HA, backup and disaster recovery go hand in hand. For such a system, backups are mandatory, but they are not enough. Fast recovery is important and that can only be achieved by having machines running concurrently, ready to take over.
As you can see, depending on the situation, your backup strategy needs planning. Every backup method has a cost associated with it both in terms of time, money and application impact (downtime).
Consider the impact of backing-up
Taking a database backup is not as simple as copying the data directory. On an active system, the database system will end up reading and writing to the data directory almost continuously. While the reads are not that troublesome from a backup standpoint, writes are. Doing a copy without taking some locks to ensure consistency means that your backup will be unusable as it can contain both stale and current data, depending on what part of the file was copied first. Thus we reach the conclusion that some form of write locking is necessary. And this is where our main discussion point arises:
During a backup procedure, the database will be unavailable for writes and will likely perform worse for reads too, due to the hardware being busy copying bytes around. Whether this is acceptable for your use case is a matter that needs consideration. Typically, backups are performed during lower-traffic periods (such as at night) so as to minimize the impact. The biggest factor for the impact of backup creation is given by the backup-method itself and that’s what we need to look at next.
Logical vs Physical backups
Now that we have evaluated our system and needs, we need to consider the tools we have available for backing up. MariaDB offers two dedicated tools. They are: mariadb-dump and mariadb-backup, while similar in end outcome (creating a backup), the difference between them is how that backup is created and stored, which leads to vastly different pros and cons.
Logical backups (easy but slow)
mariadb-dump will effectively generate a text file of SQL commands. Some of these commands will be of the form CREATE TABLE ... while others will be INSERT statements, CREATE USER statements, etc. By running these commands one can recreate the contents of a full MariaDB database. The data is logically stored in the text file, not the same physical representation that the database uses.
The advantage of this method is that it is very easy to run and the SQL generated is largely portable across database systems. This means that this backup format (albeit with a bit of work) can be used interchangeably with other database systems. It is also quite easy to inspect the text file and understand your backup contents.
The downside of this approach is that the dump itself will be rather large. There will be no binary representation of data stored in the text file and thus the whole file will be bigger. A bigger backup takes longer to create and longer to restore (it can take orders of magnitude longer). Also, during the backup procedure, unless one uses --single-transaction as one of the command flags, the whole system will be unavailable for writes as all tables will be under FLUSH TABLES WITH READ LOCK.
Physical backups (fast but complex)
mariadb-backup is mariadb-dump’s equivalent for taking a physical backup. What this means is that the backup will contain the actual bytes that are stored on disk by the Storage Engine (most likely InnoDB & Aria).
By taking physical backups, mariadb-backup has certain advantages over mariadb-dump. First of all, there is a significant speed boost during backup creation, as the system just needs to copy blocks of data, not convert it into SQL statements before writing it to disk. The tool also takes into account the current running transactions on the database system and only locks tables for the minimum time required. Additionally, the tool is capable of taking incremental backups, which is a very useful feature when the data changes rapidly and backup sizes is a concern.
The drawback of this backup procedure is that a backup is not easily verifiable as there is no simple text file to examine. Additionally, table-based backups are more complicated to achieve, requiring a more involved process. For more details, you can look up the KnowledgeBase article for InnoDB.
Nonetheless, despite mariadb-backup requiring a more involved process, it is the recommended backup procedure for MariaDB, thanks to the speed and extra features available for the backup process.
Test your backups
It goes without saying that the purpose of a backup is for it to be used in case of a failure. What is often overlooked however is actually testing that restoring works. What this means is that if you can not restore your backup, you effectively do not have a backup! It is thus important to test out the restore procedure often, to ensure there are indeed no problems. As such I highly recommend performing disaster recovery simulations frequently, say once every 3-6 months.
Conclusions
As you can see, backing up is not a simple topic. It has many nuances and many possible solutions. During the webinar, this topic was discussed in depth with Acronis and we can see that there is no one size fits all. Additionally the automation required is a non-trivial endeavor. That is why I was impressed to see that Acronis, together with CPanel implement most of these best practices as options or tweaks available from a web interface for hosting providers. There, one can schedule backups, perform full or even partial recovery (down to the table level) straight from the interface. This effectively abstracts away the nitty gritty details, while implicitly following the best practices outlined here.
I look forward to having more discussions with Acronis and CPanel on how to set-up and administer a database server. I’m sure that the insight gained there will lead to better backup features for the MariaDB. If you have a feature request for MariaDB, remember that you can always file a task in our JIRA tracker.