blog
How to Perform Efficient Backup for MySQL and MariaDB
All backup methods have their pros and cons. They also affect database workloads differently. Your backup strategy will depend upon the business requirements, the environment you operate in and resources at your disposal. Backups are usually planned according to your restoration requirement. Data loss can be full or partial, and you do not always need to recover the whole dataset. In some cases, you might just want to do a partial recovery by restoring missing tables or rows. In this case, you will need a combination of Percona Xtrabackup, mysqldump and binary logs to cover the different cases.
Performing a backup on MySQL or MariaDB is not that hard, but to be efficient, we do need to understand the effects of each and every procedure. It also depends on a number of factors like storage engine, recovery objective, dataset and delta size, storage capability and capacity, security as well as high availability design and architecture.
One of the most important things in performing a backup is to make sure you get a consistent backup. Backing up non-transactional tables like MyISAM and MEMORY require tables to be locked to guarantee consistency, this can be done using the global lock (FLUSH TABLE WITH READ LOCKS). Consequently, global lock will temporarily make the server to be read-only. For InnoDB, locking is unnecessary and other DML operations are allowed to execute while the backup is running.
In term of backup size, if you have limited storage space backed by an outdated disk subsystem, compression is your friend. Performing compression is a CPU intensive process and can directly impact the performance of your MySQL server. However, if it can be scheduled during periods of low traffic, compression can save you a lot of space. It is a tradeoff between processing power and storage space, and reduces the risk of server crash caused by a full disk.
If your database workload is write-intensive, you might find the difference in size (delta) between the two latest full backups to be fairly big, for example 1GB for a 10GB dataset per day. Performing regular full backups on databases with this kind of workload will likely introduce performance degradation, and it might be more efficient to perform incremental backups. Ultimately, this kind of workload will bring the database to a state where the backup size is rapidly growing and physical backup might be the only way to go.
When creating an encrypted backup, one thing to have in mind is that it usually takes more time to recover. The backup has to be decrypted prior to any recovery activities. With a large dataset, this could introduce some delays to the RTO. On the other hand, if you are using private key for encryption, make sure to store the key in a safe place. If the private key is missing, the backup will be useless and unrecoverable. If the key is stolen, all created backups that use the same key would be compromised as they are no longer secured.
It is common nowadays to have a high availability setup using either MySQL Replication or MySQL/MariaDB Galera Cluster. It is not necessary to backup all members in the replication chain or cluster. Since all nodes are expected to hold the same data (unless the dataset is sharded across different nodes), it is recommended to perform backup on only one node (or one per shard).
Taking a MySQL backup on a dedicated backup server will simplify your backup plans. A dedicated backup server is usually an isolated slave connected to the production servers via asynchronous replication. A good backup server consists of plenty of disk space for backup storage, with the ability to do storage snapshots. Since it uses loosely-coupled asynchronous replication, it will unlikely cause additional overhead to the production database. However, this server might become a single point of failure, with the risk of inconsistent backup if the backup server regularly lags behind.
As we have seen, there are quite a few things to consider in order to make efficient backups of MySQL and MariaDB. Each of the mentioned points are discussed in depth, together with example use-cases and best practices in our latest whitepaper – The DevOps Guide to Database Backups for MySQL and MariaDB.
