SingleStore DB

dump
Description

Generates a sequence of SQL statements that can be executed to reproduce the objects defined in the cluster.

Usage
Generates a sequence of SQL statements that can be executed to reproduce the objects defined in the cluster.

Available object types are (all object types are dumped by default): 
	users, roles, 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.

SingleStore DB 7.0.15 is the minimum version required to run 'dump'.
            
Examples:
            
	sdb-admin dump --exclude users,roles,groups --output-path dump.sql
            
	sdb-admin dump --only databases,tables,procedures --databases db1,db2

Usage:
  sdb-admin dump [flags]

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 colored output in console, which some terminal sessions/environments may have issues 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-max-sessions POSITIVE_INTEGER   Maximum number of SSH sessions to open per host, must be at least 3
      --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.

Dumping Data in SQL Format

The dump command produces a sequence of SQL statements, which can be used to reproduce the objects defined in the original SingleStore database or the cluster. It creates a logical backup of the Data Definition Language (DDL) statements for database objects without the data. The database objects are dumped in 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 SingleStore 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 logical backup of the DDL statements in the database. To learn how to create a physical backup of the database, see Backup and Restore.

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 DB cluster by using a SQL client, such as the MySQL client or the SingleStore DB Client. For more information, refer to Load data from MySQL.