Sign inTry Free

dump

On this page

Description

Generates a sequence of SQL statements that can be executed to reproduce the objects defined in the cluster. SingleStore 7.0.15 is the minimum version required to run dump.

It creates a backup of only the metadata without the data. Available object types are (all object types are dumped by default): roles, users, groups, resource-pools, databases, tables, views, procedures, functions, aggregates, and pipelines.

When --databases flag is used, databases, tables, views, procedures, functions, aggregates, and pipelines will only be dumped for the specified databases.

Examples 

sdb-admin dump --exclude users,roles, groups --output-path dump.sql
sdb-admin dump --only databases,tables,procedures --databases db1,db2

Dumping Data in SQL Format

The dump command produces a sequence of DDL statements, which can be used to reproduce the objects defined in the original SingleStore database or the cluster. The database objects definitions are dumped into a SQL file, which can be loaded into another database in the same or a different cluster to emulate the original database structure. The database dump can also be used to compare DDL statements across clusters.

You can run the dump command cluster-wide to dump a variety of object types, such as Users, Groups, Roles, and Resource Pools. At the database level, the command allows you to export Databases, Tables, Views, Procedures, Functions, Aggregates, and Pipelines. While the command is designed to export all object types by default, you can choose to dump specific object types using the flags provided. For example, you can extract just the permission data by specifying the Users, Roles, and Groups object types and load the data into a replica server.

Note: The dump command only creates a backup of the DDL statements in the database. To learn how to create a backup of the database including the data, see Back Up and Restore Data.

User Privilege Requirements

The privileges required to create and reload a dump file follow the same pattern of permissions and scope used to read and manipulate each object type individually.

You need the SHOW METADATA permission to export all object types. For individual object types, the minimum permission required to run the SHOW command for each object type will be sufficient for the export action as well. To reload a dump file, you need the permissions that are required to execute the statements in the dump file. For more information, see Permissions Matrix.

Invoking the Dump Command

The following dump command can be invoked at the command line from inside the directory where Toolbox is installed.

sdb-admin dump

This command can be used with the flags specified in the Usage section. To retrieve help for the command, run sdb-admin dump --help.

Using the flags supported by the sdb-admin dump command, you can dump DDL for objects in one or more databases or an entire cluster. With a combination of flags, you can further select the object types to dump, as demonstrated in the table below.

Flags

Description

--only resource-pools,databases,tables

Resource Pools, all CREATE DATABASE statements, and all CREATE TABLE statements are dumped

--databases db1

All Resource Pools, Users, Groups, and Roles are dumped. CREATE DATABASE statement for db1 and all objects including Tables, Views, Procedures, Functions, Aggregates, and Pipelines in db1 are dumped.

--only resource-pools,databases,tables

--databases db1

Resource-pools, CREATE DATABASE statement for db1 and all tables in db1 are dumped.

--exclude resource-pools,groups,roles

--databases db1

All the users are dumped. CREATE DATABASE statements for db1 and all the objects in db1 including Tables, Views, Procedures, Functions, Aggregates, and Pipelines are dumped.

Note:

  • To dump an entire cluster, use no flags. This will export all object types supported, and the output will be written to the default file path or the one specified in the --output-path flag. However, system databases and views such as information_schema, cluster, and memsql will not be dumped.

  • To dump select databases, specify the required databases via the --database flag. You can further filter this output by specifying the necessary object types in the --only flag. Alternatively, you can ignore certain object types in the output using the --exclude flag.

  • The --only and the --exclude flags allow you to export specific object types on the cluster as well as the database level. Note that these flags cannot be used together.

Reloading the Database Dump

You can reload the dump file into a SingleStore cluster by using a SQL client, such as the MySQL client or the SingleStore Client. For more information, refer to Load data from MySQL.

Usage

Usage:
  sdb-admin dump [flags]

  For flags that can accept multiple values (indicated by VALUES after the name of the flag),
  separate each value with a comma.

Flags:
      --databases VALUES            Only run for specified databases
      --exclude VALUES              Exclude the specified objects
  -h, --help                        Help for dump
      --only VALUES                 Only dump specified objects
      --output-path ABSOLUTE_PATH   Write the dump file to this path

Global Flags:
      --backup-cache FILE_PATH                File path for the backup cache
      --cache-file FILE_PATH                  File path for the Toolbox node cache
  -c, --config FILE_PATH                      File path for the Toolbox configuration
      --disable-colors                        Disable color output in console, which some terminal sessions/environments may have difficulty with
      --disable-spinner                       Disable the progress spinner, which some terminal sessions/environments may have issues with
  -j, --json                                  Enable JSON output
      --parallelism POSITIVE_INTEGER          Maximum number of operations to run in parallel
      --runtime-dir DIRECTORY_PATH            Where to store Toolbox runtime data
      --ssh-control-persist SECONDS           Enable SSH ControlPersist and set it to the specified duration in seconds
      --ssh-max-sessions POSITIVE_INTEGER     Maximum number of SSH sessions to open per host, must be at least 3
      --ssh-strict-host-key-checking          Enable strict host key checking for SSH connections
      --ssh-user-known-hosts-file FILE_PATH   Path to the user known_hosts file for SSH connections. If not set, /dev/null will be used
      --state-file FILE_PATH                  Toolbox state file path
  -v, --verbosity count                       Increase logging verbosity: valid values are 1, 2, 3. Usage -v=count or --verbosity=count
  -y, --yes                                   Enable non-interactive mode and assume the user would like to move forward with the proposed actions by default

Remarks

This command is interactive unless you use either --yes or --json flag to override interactive behavior.

Last modified: July 30, 2024

Was this article helpful?

On this page

Was this article helpful?