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.
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.
A physical backup can be cold, warm or hot.
|Backup Type||Single Database||Sharded Database with ScaleBase|
|Hot||Needs tools like “MySQL Enterprise Backup”, or “Percona xtrabackup”.||Needs tools like “MySQL Enterprise Backup”, or “Percona xtrabackup” on all databases servers.|
|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.