Store database backups in a safe place.
You should use the built-in functionality that automatically copies local backups to S3. Alternatively, manually copy local backups to another location.
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 replica partitions on paired leaf nodes automatically. Unlike mysqldump
, the backup files are created on the leaves and not the client.
When a 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 location before the restore.
MemSQL does not support restoring database backups from a newer version of MemSQL into an older version.
Backup History Table
MemSQL keeps track of metadata for all backups in the mv_backup_history
table in information_schema. This table provides important metadata on recent successful backups. Note that this table does not contain information for failed backups.
The mv_backup_history
table can be cleared by running CLEAR BACKUP_HISTORY
.
Table Columns
Column | Description |
---|---|
backup_id |
A unique identifier for the backup. |
database_name |
Name of the database that was backed up. |
end_timestamp |
Timestamp at which the backup completed. |
num_partitions |
Number of partitions for the backup. |
backup_path |
Path to where the backup file was placed after backup completed. |
Example
memsql> BACKUP DATABASE orders TO "./"
memsql> BACKUP DATABASE orders TO "./my_backups/"
memsql> BACKUP DATABASE users TO "./users_backup/"
memsql> SELECT * FROM information_schema.mv_backup_history;
+-----------+---------------+---------------------+----------------+-----------------------------+
| BACKUP_ID | DATABASE_NAME | END_TIMESTAMP | NUM_PARTITIONS | BACKUP_PATH |
+-----------+---------------+---------------------+----------------+-----------------------------+
| 1 | orders | 2018-04-25 16:38:57 | 4 | ./orders.backup |
| 3 | users | 2018-04-25 16:39:32 | 4 | ./users_backup/users.backup |
| 2 | orders | 2018-04-25 16:39:14 | 4 | ./my_backups/orders.backup |
+-----------+---------------+---------------------+----------------+-----------------------------+
memsql> CLEAR BACKUP_HISTORY;
memsql> SELECT * FROM information_schema.mv_backup_history;
Empty set (0.01 sec)
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.
/mnt/backup_nfs/
- When running
BACKUP
, simply select abackup_path
that 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
(specified by backup_path
). MemSQL will automatically choose master
and replica partitions when you run RESTORE
on the master aggregator.
Backup and Restore on S3
Backup and restore can also specify an S3 bucket. In this case, all the backup files across all the leaves (one per partition as described above) are put directly into the S3 bucket. When restore is called, MemSQL will distribute the backup files appropriately and restore the data (similar to how restore from an NFS drive works).
Related Topics