# dump ## 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** ```shell sdb-admin dump --exclude users,roles, groups --output-path dump.sql ``` ```shell sdb-admin dump --only databases,tables,procedures --databases db1,db2 ``` By default, the Master Aggregator node will be used to run queries. A node may alternatively be specified using a combination of `--host`, `--port`, `--user`, and `--password`. **Examples** ```shell sdb-admin dump --host 192.168.1.100 --port 9000 --user admin --password secret --output-path dump.sql ``` ```shell sdb-admin dump --host 192.168.1.100 --port 9000 --user guest --password secret --only tables ``` ## 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](https://docs.singlestore.com/db/v9.1/manage-data/back-up-and-restore-data.md). ## 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md). ## Invoking the Dump Command The following `dump` command can be invoked at the command line from inside the directory where Toolbox is installed. ```shell sdb-admin dump ``` This command can be used with the flags specified in the [Usage](https://docs.singlestore.com/#UUID-0e1acfb4-bc0f-525d-52ad-ef291dc2f912_section-idm232355690141238.md) 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](https://docs.singlestore.com/db/v9.1/load-data/data-sources/replicate-data-from-mysql/load-data-from-mysql.md). ## 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 --host string The cluster-addressable hostname for the node --only VALUES Only dump specified objects --output-path ABSOLUTE_PATH Write the dump file to this path -p, --password STRING The database user's password. If a password is specified on the command line, it must not contain an unescaped '$' character as it will be replaced by the shell. If a password is not specified on the command line and user is not "root", Toolbox will attempt to read the password from the ‘MEMSQL_PASSWORD’ environment variable. If this variable is present but does not contain a value, or if the variable does not exist, Toolbox will prompt for a password --port PORT The cluster-addressable port for the node --ssl Use SSL to connect to SingleStore --ssl-ca FILE_PATH The path to the CA file to use when the SQL user connects to SingleStore via SSL. If this option is not specified, the default CA file will be used --user string The database user 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. *** Modified at: August 27, 2025 Source: [/db/v9.1/reference/singlestore-tools-reference/sdb-admin-commands/dump/](https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/sdb-admin-commands/dump/) (An index of the documentation is available at /llms.txt)