Coming up with a backup strategy that does not affect database performance or lock your tables can be tricky. How do you backup your production database cluster without affecting your applications? Should you use mysqldump or Percona Xtrabackup/MariaBackup? In this note, we will cover some of the common backup methods for MySQL/MariaDB, and how you can get the most out of these.
Xtrabackup is an open-source MySQL hot backup utility from Percona, while MariaBackup is mostly the same tools developed by Mariadb. They are a combination of xtrabackup (built in C) and innobackupex (built on Perl) and can back up data from InnoDB, XtraDB and MyISAM tables.
They do not lock your database during the backup process. For large databases (100+ GB), they provide much better restoration time as compared to mysqldump. The restoration process involves preparing MySQL data from the backup files before replacing or switching it with the current data directory on the target node.
This is probably the most popular backup method for MySQL. mysqldump is the perfect tool when migrating data between different versions or storage engines of MySQL, or when you need to change something in the text file dump before restoring it. Use it in conjunction with xtrabackup to give you more recovery options. Using mysqldump is safe, but it has drawbacks. mysqldump will do, for each database and for each table, “SELECT * FROM” and write the content to the mysqldump file. The problem with the “SELECT * FROM .. ” is if you have tables (and a data set/DB size) that does not fit in the innodb buffer pool. The active data set (that your application uses) will take a hit when the SELECT * FROM .. will load up data from disk, store the pages in the InnoDB buffer pool, and to do so, expunge pages part of the active data set from the InnoDB buffer pool, and put them on disk.
Hence you will get a performance degradation on that node, since the active data set is no longer in RAM but on DISK (if the InnoDB buffer pool is not large enough to fit the entire database).
If you want to avoid that, then use xtrabackup/mariabackup. Nevertheless, it is common to use –single-transaction and it does not block the nodes (except for a very short time when a START TRANSACTION is made, but that can be neglected). And yes, all nodes can still perform read and writes. But you will take a performance hit in the cluster while mysqldump is running – since CPU, DISK and RAM are used by the mysqldump process.
Your backup strategy will depend on factors ranging from database size, growth and workload to hardware resources and non-functional requirements (e.g. need to do point-in-time recovery).