Outdated Version

You are viewing an older version of this section. View current production version.

BACKUP

Back up data to a binary file.

Syntax

BACKUP [DATABASE] db_name TO "backup_path"

Remarks

  • db_name is the name of a MemSQL database.
  • The BACKUP command writes a consistent snapshot of the database to disk. This means a backup stores the data in the database as it existed at the time the BACKUP operation started (all committed transactions at the time the BACKUP started will be stored in the backup).
  • BACKUP is an online operation, which means that writes will continue to run while the BACKUP is in progress. Any writes executed after the BACKUP has started will not be reflected in the stored backup.
    Info

    Backing up a database does not backup the users and grants for objects in the database. You must do this as a separate action. In addition, backing up a database does not back up engine variables, so the user must back up the memsql.cnf file and output of SHOW _SYNC VARIABLES LIKE '%' command from each node.

  • A backup of system databases cannot be created.
  • All partitions of the database need to be in the online or replicating state to run a BACKUP operation. The backup file is created on the server and not the client, unlike mysqldump. backup_path is specified relative to memsqlbin/data.
  • This command must be run on the master aggregator node (see Node Requirements for MemSQL Commands).
  • The aggregator needs to briefly block new queries from running across the cluster to start a BACKUP. Once the BACKUP is running, queries can run against the cluster normally. If there is a long-running write query executing at the time a BACKUP operation is run, the BACKUP will wait for the query to finish.
  • The easiest way to BACKUP and RESTORE databases is using a network file system (NFS) mounted on every node in the cluster as described here: Backing Up and Restoring Data.
Info

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 Developer and MemSQL Enterprise, see the MemSQL Editions page.

Example

The following example creates a backup on each node in the /var/lib/memsql/data directory.

mysql> BACKUP DATABASE memsql_demo to "./";
Query OK, 1 row affected (6.32 sec)