Table of Contents
View and create database backups. Three backup methods are supported:
- mysqldump
- xtrabackup (full)
- xtrabackup (incremental)
- mariabackup (full)
- mariabackup (incremental)
- mongodump
- pg_dumpall
The s9s client also needs to know:
- The cluster ID (or cluster name) of the cluster to backup.
- The node to backup.
- The databases that should be included (default all databases).
By default, the backups will be stored on the controller node. If you wish to store the backup on the data node, you can set the flag --on-node
.
If you are using Percona Xtrabackup, an incremental backup requires that there is already a full backup made of the same databases (all or individually specified). Otherwise, the incremental backup will be upgraded to a full backup.
Usage
s9s backup {command} {options}
Command
Name, shorthand | Description |
---|---|
−−create |
Creates a new backup. This command-line option will initiate a new job that will create a new backup. |
−−create-schedule |
Creates a backup schedule, a backup that is repeated. Please note that there are two ways to create a repeated backup. A job that creates a backup can be scheduled and repeated and with this option a backup schedule can be created to repeat the creation of a backup. |
−−delete |
Deletes an existing backup. |
−−delete-old |
Initiates a job that checks for expired backups and removes them from the system. |
−−delete-all |
Delete all backups (filtering by some property as snapshot repository name). This command line option will initiate a job that checks for all backups and removes them from the system.db_cluster_id property is used as it could be that no cluster exists but there is data on DB. With the forced option, it will delete a backup record on the DB even when backup deletion failed. |
−−delete-after-upload |
Creates a job to generate a backup only stored in the cloud, as after creating a backup on the storage location filesystem, the copy will be deleted after uploading it to the cloud. This option has to be set to indicate the deletion of the backup local copy after uploading it to the cloud. |
−−list |
Lists the backups. When listing the backups with the --long option, more detailed columns are listed. See Backup List. |
−−list-databases |
Lists the backups in database view format. This format is designed to show the archived databases in the backups. |
−−list-files |
Lists the backups in file view format. This format is designed to show the archive files of the backups. |
−−list-schedules |
Lists the backup schedules. |
−−restore |
Restores an existing backup. |
−−restore-cluster-info |
Restores the information the controller has about a cluster from a previously created archive file. |
−−restore-controller |
Restores the entire controller from a previously created tarball (created by using the --save-controller option). |
−−pitr-stop-time |
Timestamp specification for doing point-in-time backup recovery. |
--psql-immediate |
By default PostgreSQL when started in recovery mode after backup restoration applies the available complete WAL stream as well to provide the most up-to-date state of the database. By this option, we can ask PostgreSQL not to apply WAL or at least as much of it as it is a must for database consistency and to finish recovery as soon as possible. |
−−save-cluster-info |
Saves information about one cluster. |
−−save-controller |
Saves the entire controller into a file. |
−−verify |
Creates a job to verify a backup. When this main option is used the --backup-id the option has to be used to identify a backup and the --test-server is also necessary to provide a server where the backup will be tested |
−−cloud-only |
Creates a job to generate a backup streaming it directly to the cloud, without intermediate files --cloud-only option has to be set to indicate direct streaming of backup to the cloud container |
−−create-snapshot-repository |
Creates a job to create a snapshot repository on elasticsearch cluster. When this main option is used the –cluster-id option has to be used to identify the cluster, the –snap‐ shot-repository-type defines the allowed type (example: “s3”) –snapshot-repository to specify the repository name, –credential-id to specify the cloud credentials to use, –s3-bucket is also necessary to provide s3 bucket to use for this repository. –s3-region is also necessary to provide s3 region to use for this repository. |
−−list-snapshot-repository |
Creates a job to list the snapshot repositories on an elasticsearch cluster. When this main option is used the –cluster-id option has to be used to identify the cluster. |
−−delete-snapshot-repository |
Creates a job to delete a snapshot repository on elasticsearch cluster. When this main option is used the –cluster-id option has to be used to identify the cluster, the –snap‐ shot-repository to specify the repository name of the repository to be deleted. |
Options
Name, shorthand | Description |
---|---|
−−backup-directory=DIR |
The directory where the backup is placed. |
−−backup-format[=FORMATSTRING] |
The string that controls the format of the printed information about the backups. See Backup Format. |
−−backup-id=ID |
The ID of the backup. |
−−backup-method=METHOD |
Controls what backup software is going to be used to create the backup. The controller currently supports the following methods: ndb , mysqldump , xtrabackupfull , xtrabackupincr , mariabackupfull , mariabackupincr , mongodump , pgdump , pg_basebackup , mysqlpump . |
−−backup-password=PASSWORD |
The password for the SQL account that will create the backup. This command-line option is not mandatory. |
−−backup-retention=DAYS |
Controls a custom retention period for the backup, otherwise, the default global setting will be used. Specifying a positive number value here can control how long (in days) the taken backups will be preserved, -1 has a special meaning, it means the backup will be kept forever, while value 0 is the default, means prefer the global setting (configurable on UI). |
−−backup-user=USERNAME |
The username for the SQL account that will create the backup. |
−−cloud-retention=DAYS |
Retention used when the backup is on a cloud. |
−−cluster-id=ID |
The ID of the cluster. |
−−compression-level |
Compression level (used threads) to apply on the backup compression process. Value must be between1 and 9. |
−−databases=LIST |
A comma-separated list of database names. This argument controls which databases are going to be archived into the backup file. By default, all the databases are going to be archived. |
−−encrypt-backup |
When this option is specified ClusterControl will attempt to encrypt the backup files using AES-256 encryption (the key will be auto-generated if not exists yet and stored in a cluster configuration file). |
−−full-path |
Print the full path of the files. |
−−memory=MEGABYTES |
Controls how much memory the archiver process should use while restoring an archive. Currently, only the xtrabackup supports this option. |
−−no-compression |
Do not compress the archive file. |
−−nodes=NODELIST |
The list of nodes involved in the backup. See Node List. |
−−on-node |
Do not copy the created archive file to the controller, store it on the node where it was created. |
−−on-controller |
Stream and store the created backup files on the controller. |
−−parallelism=N |
Controls how many threads are used while creating a backup. Please note that not all the backup methods support multi-thread operations. |
−−pitr-compatible |
Creates PITR-compatible backup. |
--pitr-stop-time |
Timestamp specification for doing point in time backup recovery. |
−−recurrence=STRING |
Schedule time and frequency in cron format. |
−−safety-copies=N |
Controls how many safety backups should be kept while deleting old backups. This command-line option can be used together with the --delete-old option. |
−−subdirectory=MARKUPSTRING |
Sets the name of the subdirectory that holds the newly created backup files. The command-line option argument is considered to be a subpath that may contain the field specifiers using the usual %X format. See Backup Subdirectory Variables. |
−−temp-dir-path=DIR |
By default, s9s backup creates temporary backup files to /var/tmp/cmon-% path. Specify this option withyour desired path if you want to target to another location. |
−−keep-temp-dir |
Specify this option if you want to retain your archive files from the temporary directory. |
−−test-server=HOSTNAME |
Use the given server to verify the backup. If this option is provided while creating a new backup after the backup is created a new job is going to be created to verify the backup. During the verification, the SQL software will be installed on the test server and the backup will be restored on this server. The verification job will be successful if the backup is successfully restored. |
−−title=STRING |
A short human-readable string that helps the user to identify the backup later. |
−−to-individual-files |
Archive every database into individual files. Currently, only the mysqldump backup method supports this option. |
−−use-pigz |
Use the pigz program to compress the archive. |
Backup Subdirectory Variables
Variable | Description |
---|---|
B | The date and time when the backup creation was beginning. |
H | The name of the backup host, the host that created the backup. |
i | The numerical ID of the cluster. |
I | The numerical ID of the backup. |
J | The numerical ID of the job that created the backup. |
M | The backup method (e.g. “mysqldump”). |
O | The name of the user who initiated the backup job. |
S | The name of the storage host, the host that stores the backup files. |
% | The percent sign itself. Use two percent signs, %% the same way the standard printf() function interprets it as a one percent sign. |
Backup List
Column | Description |
---|---|
ID | The numerical ID of the backup. |
PI | The numerical ID of the parent backup if there is a parent backup for the given entry. |
CID | The numerical ID of the cluster to which the backup belongs. |
V | The verification status. Here V means the backup is verified, - which means the backup is not verified. |
I | The flag showing if the backup is incremental or not. Here F means the backup is a full backup, I means the backup is incremental, - means the backup contains no incremental or full backup files (because for example, the backup failed) and B means the backup contains both full and incremental backups files (which is impossible). |
STATE | The state of the backup. Here “COMPLETED” means the backup is completed, “FAILED” means the backup has failed, and “RUNNING” means the backup is being created. |
OWNER | The name of the Cmon user that owns the backup. |
HOSTNAME | The name of the host where the backup was created. |
CREATED | The date and time when the backup was created. |
SIZE | The total size of the created backup files. |
TITLE | The name or title of the backup. This is a human-readable string that helps identify the backup. |
Backup Format
When the option --backup-format
is used the specified information will be printed instead of the default columns. The format string uses the %
character to mark variable fields and flag characters as they are specified in the standard printf()
C library functions. The %
specifiers are ended by field name letters to refer to various properties of the backups.
The %+12I
format string for example has the +12
flag characters in it with the standard meaning: the field will be 12 characters wide and the “+
” or “-
” sign will always be printed with the number. The properties of the backup are encoded by letters. The%16H
for example the letterH
encodes the hostname
. Standard \
notation is also available, \n
for example, encodes a new-line character.
The s9s-tools support the following fields:
Character | Description |
---|---|
B | The date and time when the backup creation was beginning. The format used to print the dates and times can be set using the --date-format . |
C | The backup file creation date and time. The format used to print the dates and times can be set using the --date-format . |
d | The names of the databases in a comma-separated string-list. |
D | The description of the backup. If the c the modifier is used (e.g.%cD ) the configured description is shown. |
e | The word “ENCRYPTED” or “UNENCRYPTED” depending on the encryption status of the backup. |
E | The date and time when the backup creation was ended. The format used to print the dates and times can be set using the --date-format . |
F | The archive file name. |
H | The backup host (the host that created the backup). If the c modifier is used (e.g. %cH ) the configured backup host is shown. |
I | The numerical ID of the backup. |
i | The numerical ID of the cluster to which the backup belongs. |
J | The numerical ID of the job that created the backup. |
M | The backup method used. If the c modifier is used the configured backup method will be shown. |
O | The name of the owner of the backup. |
P | The full path of the archive file. |
R | The root directory of the backup. |
S | The name of the storage host, the host where the backup was stored. |
s | The size of the backup file measured in bytes. |
t | The title of the backup. They can be added when the backup is created, it helps to identify the backup later. |
v | The verification status of the backup. Possible values are “Unverified”, “Verified” and “Failed”. |
% | The percent sign itself. Use two percent signs, %% the same way the standard printf() function interprets it as a one percent sign. |
Examples
Suppose we have a data node on 10.10.10.20 (port 3306) on cluster-id 2, and we want to backup all databases using mysqldump and store the backup on ClusterControl server:
$ s9s backup --create \
--backup-method=mysqldump \
--cluster-id=2 \
--nodes=10.10.10.20:3306 \
--on-controller \
--backup-directory=/storage/backups
Create a mongodump backup on 10.0.0.148 for cluster named ‘MongoDB ReplicaSet 5.0’ and store the backup on the database node:
$ s9s backup --create \
--backup-method=mongodump \
--cluster-name='MongoDB ReplicaSet 5.0' \
--nodes=10.0.0.148 \
--backup-directory=/storage/backups
Schedule a full backup using MariaDB backup every midnight at 1:10 AM:
$ s9s backup --create \
--backup-method=mariabackupfull \
--nodes=10.10.10.19:3306 \
--cluster-name=MDB101 \
--backup-dir=/home/vagrant/backups \
--on-controller \
--recurrence='10 1 * * *'
Schedule an incremental backup using MariaDB backup every day at 1:30 AM:
$ s9s backup --create \
--backup-method=mariabackupincr \
--nodes=10.10.10.19:3306 \
--cluster-name=MDB101 \
--backup-dir=/home/vagrant/backups \
--on-controller \
--recurrence='30 1 * * *'
Create a pg_dumpall backup on PostgreSQL master server and store the backup on ClusterControl server:
$ s9s backup --create \
--backup-method=pgdump \
--nodes=192.168.0.81:5432 \
--cluster-id=43 \
--backup-dir=/home/vagrant/backups \
--on-controller \
--log
List all backups for cluster ID 2:
$ s9s backup --list \
--cluster-id=2 \
--long \
--human-readable
Omit the--cluster-id=2
to see the backup records for all clusters.
Restore backup ID 3 on cluster ID 2:
$ s9s backup --restore \
--cluster-id=2 \
--backup-id=3 \
--wait
If the backup is encrypted, it will be automatically decrypted when restoring.
Create a job to verify the given backup identified by the backup ID. The job will attempt to install MySQL on the test server using the same settings as for the given cluster, then restore the backup on this test server. The job returns OK only if the backup is successfully restored on the test server:
$ s9s backup --verify \
--log \
--backup-id=1 \
--test-server=192.168.0.55 \
--cluster-id=1
Delete the local backup copy after uploading it to the cloud for cluster ID 1:
$ s9s backup \
--create \
--cluster-id=1 \
--nodes=192.168.0.209 \
--delete-after-upload \
--cloud-provider="aws" \
--s3-bucket="my-aws-bucket-eu-west-1" \
--credential-id=2 \
--backup-method=xtrabackupfull \
--wait
Delete old backups for cluster ID 1 that are longer than 7 days, but do not delete at least 3 of the latest backups:
$ s9s backup --delete-old \
--cluster-id=1 \
--backup-retention=7 \
--safety-copies=3 \
--log
Perform a point-in-time restoration at a particular date and time for backup ID 10 on cluster ID 1:
$ s9s backup \
--restore \
--cluster-id=1 \
--backup-id=10 \
--pitr-stop-time="2020-07-14 14:27:04" \
--log
Perform a PostgreSQL restoration without applying WAL backup ID 10 on cluster ID 1:
$ s9s backup \
--restore \
--cluster-id=1 \
--backup-id=10 \
--psql-immediate \
--log
Create a snapshot repository on a elasticsearch cluster with cluster ID 1:
$ s9s backup \
--create-snapshot-repository \
--cluster-id=1 \
--snapshot-repo-type=s3 \
--snapshot-repository=mySnapshotRepository \
--credential-id=1 \
--s3-bucket=elastic-s3-test \
--s3-region=eu-west-3 \
--wait
List a snapshot repository on a elasticsearch cluster with cluster ID 1:
$ s9s backup \
--list-snapshot-repository \
--cluster-id=1 \
Delete a snapshot repository on a elasticsearch cluster with cluster ID 1:
$ s9s backup \
--delete-snapshot-repository \
--cluster-id=1 \
--snapshot-repository=mySnapshotRepository \
--wait
Generate a backup streaming it directly to the cloud with cluster ID 1:
$ s9s backup \
--create \
--cluster-id=1 \
--nodes=192.168.0.209 \
--cloud-only \
--cloud-provider="aws" \
--s3-bucket="my-aws-bucket-eu-west-1" \
--credential-id=2 \
--backup-method=xtrabackupfull \
--wait