Backing Up and Restoring Data
MemSQL provides BACKUP and RESTORE commands for making and restoring from binary database backups. BACKUP writes a consistent
snapshot of the database to disk across the cluster. RESTORE
restores the database from this snapshot across all nodes in the cluster. MemSQL’s distributed
RESTORE even handles replication, creating master and slave partitions on paired leaf nodes automatically. Unlike
mysqldump, the backup files are created on the leaves and not the client.
This MemSQL feature is only available in MemSQL Enterprise Edition. MemSQL Enterprise Edition includes 24x7 support and several enterprise-only features for critical deployments. For more information about MemSQL Community and MemSQL Enterprise, see the MemSQL Editions page.
BACKUP operation is executed, each leaf node in the cluster creates a
.backup file for each partition. These files are created on the leaves in the destination directory (relative to
memsqlbin/data) provided to
BACKUP by each leaf. MemSQL doesn’t move the
.backup files off the leaf nodes, its up to you to move them to a safe location after the backup and to restore them to the original locaiton before the restore.
Backup and Restore on NFS (Network File System)
MemSQL makes BACKUP and RESTORE easy to use with a Network
File System (NFS) by naming the
.backup files so that that no two leaves will attempt to write to the same file even if they are all writing to the same NFS directory.
To back up MemSQL with NFS:
- Ensure that the same NFS share is mounted in the same directory on all leaves (e.g.
- When running
BACKUP, simply select a
backup_paththat points to a directory on that NFS share (e.g.
BACKUP DATABASE memsql_demo to '/mnt/backup_nfs/backup_2016_05_04/')
Backup and Restore without NFS
If your cluster is not on NFS and you are restoring a cluster with a
different configuration or on different hardware than the original, you
must manually distribute partitions across the cluster before running
RESTORE. If the cluster has redundancy level greater than one, you
must group leaf nodes into replication pairs. Paired nodes must have the
same set of partition backups in their local backup directories
backup_path). MemSQL will automatically choose master
and slave partitions when you run
RESTORE on the master aggregator.