blog
Backup Best Practices for MySQL, MariaDB and Galera Cluster
Many thanks to everyone who registered and/or participated in Tuesday’s webinar on backup strategies and best practices for MySQL, MariaDB and Galera clusters led by Krzysztof Książek, Senior Support Engineer at Severalnines. If you missed the session, would like to watch it again or browse through the slides, they’re now online for viewing. Also check out the transcript of the Q&A session below.
Whether you’re a SysAdmin, DBA or DevOps professional operating MySQL, MariaDB or Galera clusters in production, you should make sure that your backups are scheduled, executed and regularly tested. Krzysztof shared some of his key best practice tips & tricks yesterday on how to do just that; including a live demo with ClusterControl. In short, this webinar replay shows you the pros and cons of different backup options and helps you pick the one that goes best with your environment.
Happy backuping!
Questions & Answers
Q. Can we control I/O while taking the backups with mysqldump and mysqldumper (I’ve used nice before, but it wasn’t helpful).
A. Theoretically it might be possible, although we haven’t really tested that. If you really want to apply some throttling then you may want to look into cgroups – it should help you to throttle I/O activity on a per-process basis.
Q. Can we take mydumper with ClusterControl and is ClusterControl is free software?
A. We don’t currently support it, but you can always use it manually; ClusterControl doesn’t prevent you from using this tool. There is a free community version of ClusterControl, yes, though its backup features are part of the commercial version. With the free community version you can deploy and monitor your database (clusters) as well as develop your own custom database advisors. You also have a one-month trial period that gives you access to all of ClusterControl’s features. You can find all the feature details here: https://severalnines.com/pricing
Q. Can xtrabackup work with data-at-rest encryption?
A. It can work with encrypted data in MySQL or Percona Server – it is because they encrypt only tablespaces, which xtrabackup just copies – it doesn’t have to access contents of tablespaces. MariaDB encrypts not only tablespaces but also, for example, InnoDB redo logs, which have to be accessed by xtrabackup – therefore xtrabackup cannot work with data-at-rest encryption as implemented in MariaDB. Because of this MariaDB Corporation forked xtrabackup into MariaDB Backup. This tool supports encryption done by MariaDB.
Q. Can you use mydumper for point-in-time recovery?
A. Yes, it is possible. mydumper can store GTID data so you can identify last applied transaction and use it as a starting position for processing binary logs.
Q. Is it a problem if we use binary logs with xtrabackup with start-datetime and end-datetime instead of start-position and end-position? We make a full backup on Fridays and every other day an incremental backup. When we need to recover we take the last full and all incremental backups and the binary logs from this day starting from 00:00 to NOW … could there be a problem with apply-log?
A. In general, you should not use –start-datetime or –end-datetime when you want to reply binary log on the database. It’s not granular enough – it has a resolution of one second and there could be many transactions that happened during that second. You can use it to minimize timeframe to look for manually, but that’s all. If you want to replay binary logs, you should use –start-position and –end-position. Only this will precisely define from which event you will replay binlogs and on which event it’ll end up.
Q. Should I run the dump software on load balancer or one of the MySQL nodes?
A. Typically you’ll use it on MySQL nodes. Some of the tools can only do just that. For example, Xtrabackup – you have to run it locally, on the database host. You can stream output to another location, but it has to be started locally.
Q. Can we take partial backups with ClusterControl? And if yes, how can we restore a backup on a running instance?
A. Yes, you can take a partial backup using ClusterControl (you can backup separate schema using xtrabackup) but, as of now, you cannot restore a partial backup on a running instance. This is caused by the fact that the schema you’d recover will not be consistent with the rest of the cluster. To make it consistent, the cluster has to be bootstrapped from the node on which you restore a backup. So, technically, the node runs all the time but it’s a fairly heavy and invasive operation. This will change in the next version of ClusterControl in which you’d be able to restore backups on a separate host. From that host you could then dump contents of a restored schema using mysqldump (or mydumper) and restore it on a production cluster.
Q. Can you please share the mysqldumper command?
A. It’s rather hard to answer this question without doing copy and paste from the documentation, so we think it will be the best if we’d point you to the documentation: https://github.com/maxbube/mydumper/tree/master/docs