25.5.23 ndb_restore — Restore an NDB Cluster Backup

Skip to Main Content

MySQL 8.4 Reference Manual Including MySQL NDB Cluster 8.4

25.5.23 ndb_restore — Restore an NDB Cluster Backup

The NDB Cluster restoration program is implemented as a separate command-line utility ndb_restore, which can normally be found in the MySQL bin directory. This program reads the files created as a result of the backup and inserts the stored information into the database.

ndb_restore must be executed once for each of the backup files that were created by the START BACKUP command used to create the backup (see Section 25.6.8.2, “Using The NDB Cluster Management Client to Create a Backup”). This is equal to the number of data nodes in the cluster at the time that the backup was created.

Note

Before using ndb_restore, it is recommended that the cluster be running in single user mode, unless you are restoring multiple data nodes in parallel. See Section 25.6.6, “NDB Cluster Single User Mode”, for more information.

Options that can be used with ndb_restore are shown in the following table. Additional descriptions follow the table.

• --allow-pk-changes

When this option is set to 1, ndb_restore allows the primary keys in a table definition to differ from that of the same table in the backup. This may be desirable when backing up and restoring between different schema versions with primary key changes on one or more tables, and it appears that performing the restore operation using ndb_restore is simpler or more efficient than issuing many ALTER TABLE statements after restoring table schemas and data.

The following changes in primary key definitions are supported by --allow-pk-changes:

• Extending the primary key: A non-nullable column that exists in the table schema in the backup becomes part of the table's primary key in the database.

  Important

  When extending a table's primary key, any columns which become part of primary key must not be updated while the backup is being taken; any such updates discovered by ndb_restore cause the restore operation to fail, even when no change in value takes place. In some cases, it may be possible to override this behavior using the --ignore-extended-pk-updates option; see the description of this option for more information. - Contracting the primary key (1): A column that is already part of the table's primary key in the backup schema is no longer part of the primary key, but remains in the table. - Contracting the primary key (2): A column that is already part of the table's primary key in the backup schema is removed from the table entirely.

These differences can be combined with other schema differences supported by ndb_restore, including changes to blob and text columns requiring the use of staging tables.

Basic steps in a typical scenario using primary key schema changes are listed here:

1. Restore table schemas using ndb_restore --restore-meta
2. Alter schema to that desired, or create it
3. Back up the desired schema
4. Run ndb_restore --disable-indexes using the backup from the previous step, to drop indexes and constraints
5. Run ndb_restore --allow-pk-changes (possibly along with --ignore-extended-pk-updates, --disable-indexes, and possibly other options as needed) to restore all data
6. Run ndb_restore --rebuild-indexes using the backup made with the desired schema, to rebuild indexes and constraints

When extending the primary key, it may be necessary for ndb_restore to use a temporary secondary unique index during the restore operation to map from the old primary key to the new one. Such an index is created only when necessary to apply events from the backup log to a table which has an extended primary key. This index is named NDB$RESTORE_PK_MAPPING, and is created on each table requiring it; it can be shared, if necessary, by multiple instances of ndb_restore instances running in parallel. (Running ndb_restore --rebuild-indexes at the end of the restore process causes this index to be dropped.) - --append

When used with the --tab and --print-data options, this causes the data to be appended to any existing files having the same names. - --backup-path=dir_name

The path to the backup directory is required; this is supplied to ndb_restore using the --backup-path option, and must include the subdirectory corresponding to the ID backup of the backup to be restored. For example, if the data node's DataDir is /var/lib/mysql-cluster, then the backup directory is /var/lib/mysql-cluster/BACKUP, and the backup files for the backup with the ID 3 can be found in /var/lib/mysql-cluster/BACKUP/BACKUP-3. The path may be absolute or relative to the directory in which the ndb_restore executable is located, and may be optionally prefixed with backup-path=.

It is possible to restore a backup to a database with a different configuration than it was created from. For example, suppose that a backup with backup ID 12, created in a cluster with two storage nodes having the node IDs 2 and 3, is to be restored to a cluster with four nodes. Then ndb_restore must be run twice—once for each storage node in the cluster where the backup was taken. However, ndb_restore cannot always restore backups made from a cluster running one version of MySQL to a cluster running a different MySQL version. See Section 25.3.7, “Upgrading and Downgrading NDB Cluster”, for more information.

Important

It is not possible to restore a backup made from a newer version of NDB Cluster using an older version of ndb_restore. You can restore a backup made from a newer version of MySQL to an older cluster, but you must use a copy of ndb_restore from the newer NDB Cluster version to do so.

For example, to restore a cluster backup taken from a cluster running NDB Cluster 8.4.7 to a cluster running NDB Cluster 8.0.44, you must use the ndb_restore that comes with the NDB Cluster 8.0.44 distribution.

For more rapid restoration, the data may be restored in parallel, provided that there is a sufficient number of cluster connections available. That is, when restoring to multiple nodes in parallel, you must have an [api] or [mysqld] section in the cluster config.ini file available for each concurrent ndb_restore process. However, the data files must always be applied before the logs. - --backup-password=password

This option specifies a password to be used when decrypting an encrypted backup with the --decrypt option. This must be the same password that was used to encrypt the backup.

The password must be 1 to 256 characters in length, and must be enclosed by single or double quotation marks. It can contain any of the ASCII characters having character codes 32, 35, 38, 40-91, 93, 95, and 97-126; in other words, it can use any printable ASCII characters except for !, ', “, $, %, \, and ^.

It is possible to omit the password, in which case ndb_restore waits for it to be supplied from stdin, as when using --backup-password-from-stdin. - --backup-password-from-stdin[=TRUE|FALSE]

When used in place of --backup-password, this option enables input of the backup password from the system shell (stdin), similar to how this is done when supplying the password interactively to mysql when using the --password without supplying the password on the command line. - --backupid=#, -b

This option is required; it is used to specify the ID or sequence number of the backup, and is the same number shown by the management client in the Backup backup_id completed message displayed upon completion of a backup. (See Section 25.6.8.2, “Using The NDB Cluster Management Client to Create a Backup”.)

Important

When restoring cluster backups, you must be sure to restore all data nodes from backups having the same backup ID. Using files from different backups results at best in restoring the cluster to an inconsistent state, and is likely to fail altogether. - --character-sets-dir

Directory containing character sets. - --connect, -c

Alias for --ndb-connectstring. - --connect-retries

Number of times to retry connection before giving up. - --connect-retry-delay

Number of seconds to wait between attempts to contact management server. - --connect-string

Same as --ndb-connectstring. - --core-file

Write core file on error; used in debugging. - --decrypt

Decrypt an encrypted backup using the password supplied by the --backup-password option. - --defaults-extra-file

Read given file after global files are read. - --defaults-file

Read default options from given file only. - --defaults-group-suffix

Also read groups with concat(group, suffix). - --disable-indexes

Disable restoration of indexes during restoration of the data from a native NDB backup. Afterwards, you can restore indexes for all tables at once with multithreaded building of indexes using --rebuild-indexes, which should be faster than rebuilding indexes concurrently for very large tables.

This option also drops any foreign keys specified in the backup.

MySQL can open an NDB table for which one or more indexes cannot be found, provided the query does not use any of the affected indexes; otherwise the query is rejected with ER_NOT_KEYFILE. In the latter case, you can temporarily work around the problem by executing an ALTER TABLE statement such as this one:

ALTER TABLE tbl ALTER INDEX idx INVISIBLE;

This causes MySQL to ignore the index idx on table tbl. See Primary Keys and Indexes, for more information, as well as Section 10.3.12, “Invisible Indexes”. - --dont-ignore-systab-0, -f

Normally, when restoring table data and metadata, ndb_restore ignores the copy of the NDB system table that is present in the backup. --dont-ignore-systab-0 causes the system table to be restored. This option is intended for experimental and development use only, and is not recommended in a production environment. - --exclude-databases=db-list

Comma-delimited list of one or more databases which should not be restored.

This option is often used in combination with --exclude-tables; see that option's description for further information and examples. - --exclude-intermediate-sql-tables[=TRUE|FALSE]

When performing copying ALTER TABLE operations, mysqld creates intermediate tables (whose names are prefixed with #sql-). When TRUE, the --exclude-intermediate-sql-tables option keeps ndb_restore from restoring such tables that may have been left over from these operations. This option is TRUE by default. - --exclude-missing-columns

It is possible to restore only selected table columns using this option, which causes ndb_restore to ignore any columns missing from tables being restored as compared to the versions of those tables found in the backup. This option applies to all tables being restored. If you wish to apply this option only to selected tables or databases, you can use it in combination with one or more of the --include-* or --exclude-* options described elsewhere in this section to do so, then restore data to the remaining tables using a complementary set of these options. - --exclude-missing-tables

It is possible to restore only selected tables using this option, which causes ndb_restore to ignore any tables from the backup that are not found in the target database. - --exclude-tables=table-list

List of one or more tables to exclude; each table reference must include the database name. Often used together with --exclude-databases.

When --exclude-databases or --exclude-tables is used, only those databases or tables named by the option are excluded; all other databases and tables are restored by ndb_restore.

This table shows several invocations of ndb_restore using --exclude-* options (other options possibly required have been omitted for clarity), and the effects these options have on restoring from an NDB Cluster backup:

Table 25.23 Several invocations of ndb_restore using --exclude-* options, and the effects these options have on restoring from an NDB Cluster backup.

You can use these two options together. For example, the following causes all tables in all databases except for databases db1 and db2, and tables t1 and t2 in database db3, to be restored:

$> ndb_restore [...] --exclude-databases=db1,db2 --exclude-tables=db3.t1,db3.t2

(Again, we have omitted other possibly necessary options in the interest of clarity and brevity from the example just shown.)

You can use --include-* and --exclude-* options together, subject to the following rules:

• The actions of all --include-* and --exclude-* options are cumulative.
• All --include-* and --exclude-* options are evaluated in the order passed to ndb_restore, from right to left.
• In the event of conflicting options, the first (rightmost) option takes precedence. In other words, the first option (going from right to left) that matches against a given database or table “wins”.

For example, the following set of options causes ndb_restore to restore all tables from database db1 except db1.t1, while restoring no other tables from any other databases:

--include-databases=db1 --exclude-tables=db1.t1

However, reversing the order of the options just given simply causes all tables from database db1 to be restored (including db1.t1, but no tables from any other database), because the --include-databases option, being farthest to the right, is the first match against database db1 and thus takes precedence over any other option that matches db1 or any tables in db1:

--exclude-tables=db1.t1 --include-databases=db1 - --fields-enclosed-by=char

Each column value is enclosed by the string passed to this option (regardless of data type; see the description of --fields-optionally-enclosed-by). - --fields-optionally-enclosed-by

The string passed to this option is used to enclose column values containing character data (such as CHAR, VARCHAR, BINARY, TEXT, or ENUM). - --fields-terminated-by=char

The string passed to this option is used to separate column values. The default value is a tab character (\t). - --help

Display help text and exit. - --hex

If this option is used, all binary values are output in hexadecimal format. - --ignore-extended-pk-updates

When using --allow-pk-changes, columns which become part of a table's primary key must not be updated while the backup is being taken; such columns should keep the same values from the time values are inserted into them until the rows containing the values are deleted. If ndb_restore encounters updates to these columns when restoring a backup, the restore fails. Because some applications may set values for all columns when updating a row, even when some column values are not changed, the backup may include log events appearing to update columns which are not in fact modified. In such cases you can set --ignore-extended-pk-updates to 1, forcing ndb_restore to ignore such updates.

Important

When causing these updates to be ignored, the user is responsible for ensuring that there are no updates to the values of any columns that become part of the primary key.

For more information, see the description of --allow-pk-changes. - --include-databases=db-list

Comma-delimited list of one or more databases to restore. Often used together with --include-tables; see the description of that option for further information and examples. - --include-stored-grants

ndb_restore does not by default restore shared users and grants (see Section 25.6.13, “Privilege Synchronization and NDB_STORED_USER”) to the ndb_sql_metadata table. Specifying this option causes it to do so. - --include-tables=table-list

Comma-delimited list of tables to restore; each table reference must include the database name.

When --include-databases or --include-tables is used, only those databases or tables named by the option are restored; all other databases and tables are excluded by ndb_restore, and are not restored.

The following table shows several invocations of ndb_restore using --include-* options (other options possibly required have been omitted for clarity), and the effects these have on restoring from an NDB Cluster backup:

Table 25.24 Several invocations of ndb_restore using --include-* options, and their effects on restoring from an NDB Cluster backup.

You can also use these two options together. For example, the following causes all tables in databases db1 and db2, together with the tables t1 and t2 in database db3, to be restored (and no other databases or tables):

$> ndb_restore [...] --include-databases=db1,db2 --include-tables=db3.t1,db3.t2

(Again we have omitted other, possibly required, options in the example just shown.)

It also possible to restore only selected databases, or selected tables from a single database, without any --include-* (or --exclude-*) options, using the syntax shown here:

ndb_restore other_options db_name,[db_name[,...] | tbl_name[,tbl_name][,...]]

In other words, you can specify either of the following to be restored:

• All tables from one or more databases
• One or more tables from a single database
• --lines-terminated-by=char

Specifies the string used to end each line of output. The default is a linefeed character (\n). - --login-path

Read given path from login file. - --no-login-paths

Skips reading options from the login path file. - --lossy-conversions, -L

This option is intended to complement the --promote-attributes option. Using --lossy-conversions allows lossy conversions of column values (type demotions or changes in sign) when restoring data from backup. With some exceptions, the rules governing demotion are the same as for MySQL replication; see Section 19.5.1.9.2, “Replication of Columns Having Different Data Types”, for information about specific type conversions currently supported by attribute demotion.

This option also makes it possible to restore a NULL column as NOT NULL. The column must not contain any NULL entries; otherwise ndb_restore stops with an error.

ndb_restore reports any truncation of data that it performs during lossy conversions once per attribute and column. - --no-binlog

This option prevents any connected SQL nodes from writing data restored by ndb_restore to their binary logs. - --no-restore-disk-objects, -d

This option stops ndb_restore from restoring any NDB Cluster Disk Data objects, such as tablespaces and log file groups; see Section 25.6.11, “NDB Cluster Disk Data Tables”, for more information about these. - --no-upgrade, -u

When using ndb_restore to restore a backup, VARCHAR columns created using the old fixed format are resized and recreated using the variable-width format now employed. This behavior can be overridden by specifying --no-upgrade. - --ndb-connectstring

Set connection string for connecting to ndb_mgmd. Syntax: [nodeid=id;][host=]hostname[:port]. Overrides entries in NDB_CONNECTSTRING and my.cnf. - --ndb-mgm-tls

Sets the level of TLS support required to connect to the management server; one of relaxed or strict. relaxed (the default) means that a TLS connection is attempted, but success is not required; strict means that TLS is required to connect. - --ndb-mgmd-host

Same as --ndb-connectstring. - --ndb-nodegroup-map=map, -z

Any value set for this option is ignored, and the option itself does nothing. - --ndb-nodeid

Set node ID for this node, overriding any ID set by --ndb-connectstring. - --ndb-optimized-node-selection

Enable optimizations for selection of nodes for transactions. Enabled by default; use --skip-ndb-optimized-node-selection to disable. - --ndb-tls-search-path

Specify a list of directories to search for a CA file. On Unix platforms, the directory names are separated by colons (:); on Windows systems, the semicolon character (;) is used as the separator. A directory reference may be relative or absolute; it may contain one or more environment variables, each denoted by a prefixed dollar sign ($), and expanded prior to use.

Searching begins with the leftmost named directory and proceeds from left to right until a file is found. An empty string denotes an empty search path, which causes all searches to fail. A string consisting of a single dot (.) indicates that the search path limited to the current working directory.

If no search path is supplied, the compiled-in default value is used. This value depends on the platform used: On Windows, this is \ndb-tls; on other platforms (including Linux), it is $HOME/ndb-tls. This can be overridden by compiling NDB Cluster using -DWITH_NDB_TLS_SEARCH_PATH. - --no-defaults

|

（本文超出站点单文档体积上限，已截断）