Xpand implements Fast Backup and Restore as a binary backup mechanism that works at the row level. Each Xpand node sends its data directly to the backup target in parallel, eliminating bottlenecks and allowing backup to scale with cluster size. Restore is handled similarly; the initiating node coordinates with other participating nodes in parallel to read from the dump file and restore replicas.
The structure of the dumped data consists of text files describing the schema and metadata about the backup. The dumped data also contains binary files of compacted row data and data consistency information. Modifying these files may make it impossible to restore from the backup.
Xpand uses a Cyclic Redundancy Check (CRC) during Backup and Restore. Calculating the CRC allows the Restore function to validate the data written during the Backup function. CDC adherence also requires that any failed backup or restore be restarted from the beginning.
Xpand's Fast Backup and Restore:
Xpand recommends running BACKUP and RESTORE in a screen session or using tmux to avoid connection failures.
Xpand also recommends running both BACKUP and RESTORE during off-peak hours.
Backup runs as a single transaction and will pin BigC (Xpand's garbage collection) for the duration of the operation. This may require operational consideration if your cluster does not have enough free space for the undo log created from this long running transaction.
Backup SQL Syntax |
sql> BACKUP identifier [, identifier] [EXCLUDING excluded_identifier] TO 'target' [COMPRESSED] |
Both the identifer and excluded_identifer should refer to valid system objects. This can be a fully qualified name, or include wildcards (*) for the database, object, or both. For example:
Additionally the excluded_identifer can specify a given table. For example:
Xpand uses username and password authentication. The server path for FTP represents the relative path from the FTP root directory whereas the path for the SFTP server should be the absolute path from the Linux root directory. For example: "ftp://user:[email protected]/relative_path/from_ftp_root" or "sftp://user:[email protected]:absolute_path/from_Linux_root".
Care should be taken when excluding objects from a backup. This can cause errors during RESTORE if another object depends on the excluded object.
For example, if a trigger or view refers to an excluded object that does not already exist in the restored environment, an error will occur during the RESTORE.
Triggers, views, and foreign keys that reference an excluded object will be backed up, unless they are specifically excluded.
To use an SFTP for Backup and Restore, specify the absolute path of the file location (versus relative path for FTP) and provide a username and password for authentication. For example: "sftp://user:[email protected]:absolute_path/from_Linux_root"
To create a compressed backup, specify the COMPRESSED option. This functionality reduces bandwidth requirements for backup to remote locations and reduces the amount of storage utilized, but adds additional latency to complete a backup.
You may want to tune BACKUP to be slower or faster.
sql> set global backup_backup_concurrency = desired_value; |
The global variable backup_backup_concurrency determines the number of tables that can be backed up simultaneously. With the default value of 1, the backup process is limited to one table at a time.
sql> BACKUP db_name1.*, db_name2.tbl_name1, db_name2.tbl_name2 TO 'ftp://storage01/uploads/johndoe+kibbles.jun01'; sql> BACKUP *.* EXCLUDING db_name2.tbl_name1 TO 'ftp://storage01/uploads/johndoe.kibbles.jun01'; sql> BACKUP test TO 'sftp://plato:[email protected]:22/tmp/tc5326/tc5326.bkup'; |
The following queries show the status of all currently running backups and restores.
sql> SELECT * from system.backups where source="ftp://storage01/uploads"\G
*************************** 1. row **************************
source: ftp://storage01/uploads
backup: 1_52T_large_dogfood_dbx2
cluster_name: cl5d97786267d0d4cb
version: 5.0.45-clustrix-7.5.2
status: ERROR
start_time: 2016-06-15 01:43:36
completed_time: 2016-06-15 03:19:31
size: 0 |
Run the following query to view information for a backup in progress |
sql> select * from system.backup_status\G
*************************** 1. row **************************
nodeid: 2
id: 5756997494884148226
type: BACKUP
url: ftp://storage01/uploads/1_52T_large_dogfood_dbx2_2
objs: (("test" . "")("clustrix_ui" . "")("bugstest_01" ."")("jtunes_01" . "")("longstats_01" . "")("npktest_01" ."")("perfstats_01" . "")("run_resources_01" . "")("statd_01" ."")("bugstest_02" . "")("jtunes_02" . "")("longstats_02" ."")("npktest_02" . "")("perfstats_02" . "")("run_resources_02" ."")("statd_02" . "*"))
start_time: 2016-06-22 22:50:32
expected_bytes: 793977192448
replicas: 1070
rows: 49589227
bytes: 23991322310
1 row in set (0.01 sec) |
sql> SELECT * from system.backup_tables where source="ftp://storage01/uploads" limit 2;
+-------------------------+--------------------------+-------------+------------+------+
| source | backup | db |table | size |
+-------------------------+--------------------------+-------------+------------+------+
| ftp://storage01/uploads | 1_52T_large_dogfood_dbx2 | bugstest_01 |attach_data | 0 |
+-------------------------+--------------------------+-------------+------------+------+
| ftp://storage01/uploads | 1_52T_large_dogfood_dbx2 | bugstest_01 |attachments | 0 |
+-------------------------+--------------------------+-------------+------------+------+
2 rows in set (12.16 sec) |
Use the following syntax to restore objects previously backed up with Fast Backup.
Restore SQL Syntax |
sql> RESTORE identifier [AS identifier] [, identifier [AS identifier]] [EXCLUDING excluded_identifier] FROM target [REPLICAS=N] |
Both the identifer and excluded_identifer should refer to valid system objects. This can be a fully qualified name, or include wildcards (*) for the database, object, or both. For example:
Additionally, the excluded_identifer can specify a given table. For example:
Tables may be renamed during the restore process by providing a table alias [AS identifier].
The server path for FTP represents the relative path from the FTP root directory whereas the path for the SFTP server should be the absolute path from the Linux root directory. For example: "ftp://user:[email protected]/relative_path/from_ftp_root" or "sftp://user:[email protected]:absolute_path/from_Linux_root".
Care should be taken when restoring from a backup that used the EXCLUDING option. This can cause errors during restoration if another object depends on the excluded object(s).
For example, if a trigger or view refers to an object that does not already exist in the restored environment, an error will occur.
Triggers, views, and foreign keys that reference an object excluded from the backup will be successfully restored only if the object exists in the restored environment.
The number of replicas for a table or index is recorded within the backups. Normally when RESTORE is run, tables and indexes are restored with the same number of replicas as the original. This option allows you to explicitly specify the number of replicas that should be created as part of the RESTORE operation.
For example, if a table had three replicas when backed up, the following example would create the table with only two replicas.
sql> RESTORE * FROM 'ftp://storage01/uploads/johndoe.kibbles.jun01' REPLICAS = 2; |
See more on specifying the number of replicas in a table in the section on Managing Data Distribution.
You may want to tune RESTORE to be slower or faster.
sql> set global backup_restore_concurrency = desired_value; |
The global variable backup_restore_concurrency determines the number of slices that can be restored simultaneously to each node.
sql> RESTORE db_name.tbl_name AS db_name_backup.tbl_name FROM 'ftp://[email protected]/backups/backupfolders/backup_file'; sql> RESTORE tbl_name, tbl_name2, tbl_name3 FROM 'ftp://[email protected]://server.com/backups/backupfolders/backup_file'; sql> RESTORE ehms FROM 'sftp://[email protected]/root/ehmssftp'; |
The following global variables impact Fast Backup and Restore. The defaults provided are generally acceptable. These variables are not available by session.
Name | Description | Default Value |
---|---|---|
backup_backup_concurrency | The number of tables that can be backed up simultaneously. | 1 |
backup_restore_concurrency | The maximum number of slices restored concurrently on each node. | 16 |
backup_write_compression_level | Compression level from 1 (fastest) to 9 (best compression) | 6 |
Fast Backup and Restore can also be used to resolve the following issues in replicated environments.
Reset the position recorded in the binlogs file by issuing the command:
sql> CHANGE MASTER TO MASTER_LOG_FILE='foo1.002843', MASTER_LOG_POS=99744547,MASTER_HOST='foo1', MASTER_USER='root', MASTER_PORT=3306; |
6. Start the slave.
If you receive the following timeout error during the backup process, "Socket timeout while waiting for server response: read_timer", the global variable ftp_read_timeout_secs can be increased to a higher value, up to 3000, to prevent this error. This global applies to backups and restores of both FTP and SFTP servers.
To create a dump of the Xpand, use the mysqldump utility, which is included on each node in your cluster. If you intend to import the dump into a Slave, create the binary log for the replication stream before you create the dump.
To create the dump, issue the following command:
shell> mysqldump -u user -h clustrix host --single-transaction --master-data=2 --all-databases > mydumpfile.dump |
FTP Server Setup for Fast Backup and Restore
List of Errors for Backup and Restore