Backing Up MySQL With ScaleBase
Backing up data is critical for production databases – and there are a lot of well-known solutions for backing up databases.
When the database is sharded, backing up data becomes problematic. If the backup is not synchronized across all shards, data inconsistency might occur. In this blog post I’ll try to detail the possible backup scenarios for sharded databases when using ScaleBase.
Backup Types
Let’s start by understanding the different backup types that are out there. You can read all about it here.
A physical backup involves copying all database files to a different location. Copying can take several hours for a decent database if it’s done to a disk or a tape. It might take only seconds if the database files reside on SAN/NAS storage hardware that supports snapshot technology.
A logical backup is a copy of the logical database structure. It backs up meaningful data rather than the physical backup’s bits and bytes. The logical backup is comprised of all CREATE TABLE statements and INSERT statements for the content.
Physical backup methods are faster than logical because they involve only file copying without conversion.
A full restore is also faster from a physical backup. However with a physical backup you can’t restore only one table, or selected specific data. If this is what you need, you’ll have to use logical backups.
Physical Backup
A physical backup can be cold, warm or hot.
| Backup Type | Single Database | Sharded Database with ScaleBase |
| Cold |
|
|
| Warm |
|
|
| Hot | Needs tools like “MySQL Enterprise Backup”, or “Percona xtrabackup”. | Needs tools like “MySQL Enterprise Backup”, or “Percona xtrabackup” on all databases servers. |
Logical Backup
| 1 DB | Sharded |
| The most common command for a logical backup is:mysqldump –single-transaction –all-databases | Run the command through ScaleBase. |
Benefits of Backing Up with ScaleBase
The added value of using ScaleBase when backing up data is:
- Vs. single database:
- Backup takes only a fraction of the time. Since each database is smaller, copying the data is faster.
- Vs. home-grown sharded environment:
- Instead of updating backup scripts, just change the IP address to ScaleBase. Everything will continue working exactly as before.
Hi,
Some point I wish to clarify:
- Warm backup: FLUSHing tables does not mean you can take a file system copy if you’re using InnoDB tables; this is unless you’re using the Google patch (or similar) for disabling InnoDB I/O. Do you?
- Hot backup: how would you run Percona’s Xtrabackup on all database simultaneously? This highlights your earlier note: “If the backup is not synchronized across all shards, data inconsistency might occur”.
- Snapshots: if you can take snapshots, and you’re on InnoDB-only DB, then you can make HOT backups (takes very small lock time for flushing the mysql data tables, though not required if they are unchanged).
So, HOT does not mean you must use tools like “MySQL Enterprise Backup”, or “Percona xtrabackup”. LVM snapshots are just fine.
Hi Shlomi,
Great input – thanks!!
You are absolutely right about all bullets you wrote. I’ll elaborate a bit:
1. Warm backup – all stated above is for MyISAM tables only and would NOT work on standard unpatched InnoDB
2. Backups can be taken at any time, synchronization should be done in the restore and recovery from the bin log files. We’ll address that in our future blog, stay tuned!
3. Snapshots: You are correct.
Again thanks for the response.
Doron