MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

22.4.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.

Note

Beginning with NDB 8.0.17, this program no longer prints NDBT_ProgramExit: ... when it finishes its run. Applications depending on this behavior should be modified accordingly when upgrading from NDB 8.0.16 or earlier to a NDB 8.0 later release.

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 22.5.3.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 22.5.8, “NDB Cluster Single User Mode”, for more information.

The following table includes options that are specific to the NDB Cluster native backup restoration program ndb_restore. Additional descriptions follow the table. For options common to most NDB Cluster programs (including ndb_restore), see Section 22.4.31, “Options Common to NDB Cluster Programs — Options Common to NDB Cluster Programs”.

Table 22.268 Command-line options for the ndb_restore program

Format Description Added, Deprecated, or Removed

--allow-pk-changes[=0|1]

Allow changes to set of columns making up table's primary key

ADDED: NDB 8.0.21

--append

Append data to a tab-delimited file

(Supported in all MySQL 8.0 based releases)

--backup-path=dir_name

Path to backup files directory

(Supported in all MySQL 8.0 based releases)

--backupid=#,

-b

Restore from the backup with the given ID

(Supported in all MySQL 8.0 based releases)

--connect,

-c

Alias for --connectstring

(Supported in all MySQL 8.0 based releases)

--disable-indexes

Causes indexes from a backup to be ignored; may decrease time needed to restore data

(Supported in all MySQL 8.0 based releases)

--dont-ignore-systab-0,

-f

Do not ignore system table during restore. Experimental only; not for production use

(Supported in all MySQL 8.0 based releases)

--exclude-databases=db-list

List of one or more databases to exclude (includes those not named)

(Supported in all MySQL 8.0 based releases)

--exclude-intermediate-sql-tables[=TRUE|FALSE]

If TRUE (the default), do not restore any intermediate tables (having names prefixed with '#sql-') that were left over from copying ALTER TABLE operations

(Supported in all MySQL 8.0 based releases)

--exclude-missing-columns

Causes columns from the backup version of a table that are missing from the version of the table in the database to be ignored

(Supported in all MySQL 8.0 based releases)

--exclude-missing-tables

Causes tables from the backup that are missing from the database to be ignored

(Supported in all MySQL 8.0 based releases)

--exclude-tables=table-list

List of one or more tables to exclude (includes those in the same database that are not named); each table reference must include the database name

(Supported in all MySQL 8.0 based releases)

--fields-enclosed-by=char

Fields are enclosed with the indicated character

(Supported in all MySQL 8.0 based releases)

--fields-optionally-enclosed-by

Fields are optionally enclosed with the indicated character

(Supported in all MySQL 8.0 based releases)

--fields-terminated-by=char

Fields are terminated by the indicated character

(Supported in all MySQL 8.0 based releases)

--hex

Print binary types in hexadecimal format

(Supported in all MySQL 8.0 based releases)

--ignore-extended-pk-updates[=0|1]

Ignore log entries containing updates to columns now included in extended primary key

ADDED: NDB 8.0.21

--include-databases=db-list

List of one or more databases to restore (excludes those not named)

(Supported in all MySQL 8.0 based releases)

--include-stored-grants

Restore shared users and grants to ndb_sql_metadata table

ADDED: NDB 8.0.19

--include-tables=table-list

List of one or more tables to restore (excludes those in same database that are not named); each table reference must include the database name

(Supported in all MySQL 8.0 based releases)

--lines-terminated-by=char

Lines are terminated by the indicated character

(Supported in all MySQL 8.0 based releases)

--lossy-conversions,

-L

Allow lossy conversions of column values (type demotions or changes in sign) when restoring data from backup

(Supported in all MySQL 8.0 based releases)

--no-binlog

If a mysqld is connected and using binary logging, do not log the restored data

(Supported in all MySQL 8.0 based releases)

--no-restore-disk-objects,

-d

Do not restore objects relating to Disk Data

(Supported in all MySQL 8.0 based releases)

--no-upgrade,

-u

Do not upgrade array type for varsize attributes which do not already resize VAR data, and do not change column attributes

(Supported in all MySQL 8.0 based releases)

--ndb-nodegroup-map=map,

-z

Nodegroup map for NDBCLUSTER storage engine. Syntax: list of (source_nodegroup, destination_nodegroup)

(Supported in all MySQL 8.0 based releases)

--nodeid=#,

-n

ID of node where backup was taken

(Supported in all MySQL 8.0 based releases)

--num-slices=#

Number of slices to apply when restoring by slice

ADDED: NDB 8.0.20

--parallelism=#,

-p

Number of parallel transactions to use while restoring data

(Supported in all MySQL 8.0 based releases)

--preserve-trailing-spaces,

-P

Allow preservation of trailing spaces (including padding) when promoting fixed-width string types to variable-width types

(Supported in all MySQL 8.0 based releases)

--print

Print metadata, data and log to stdout (equivalent to --print-meta --print-data --print-log)

(Supported in all MySQL 8.0 based releases)

--print-data

Print data to stdout

(Supported in all MySQL 8.0 based releases)

--print-log

Print to stdout

(Supported in all MySQL 8.0 based releases)

--print-meta

Print metadata to stdout

(Supported in all MySQL 8.0 based releases)

--print-sql-log

Write SQL log to stdout; default is FALSE

(Supported in all MySQL 8.0 based releases)

--progress-frequency=#

Print status of restoration each given number of seconds

(Supported in all MySQL 8.0 based releases)

--promote-attributes,

-A

Allow attributes to be promoted when restoring data from backup

(Supported in all MySQL 8.0 based releases)

--rebuild-indexes

Causes multithreaded rebuilding of ordered indexes found in the backup. Number of threads used is determined by setting BuildIndexThreads parameter

(Supported in all MySQL 8.0 based releases)

--remap-column=[db].[tbl].[col]:[fn]:[args]

Apply offset to value of specified column using indicated function and arguments

ADDED: NDB 8.0.21

--restore-data,

-r

Restore table data and logs into NDB Cluster using the NDB API

(Supported in all MySQL 8.0 based releases)

--restore-epoch,

-e

Restore epoch info into the status table. Convenient on a MySQL Cluster replication slave for starting replication. The row in mysql.ndb_apply_status with id 0 will be updated/inserted

(Supported in all MySQL 8.0 based releases)

--restore-meta,

-m

Restore metadata to NDB Cluster using the NDB API

(Supported in all MySQL 8.0 based releases)

--restore-privilege-tables

Restore MySQL privilege tables that were previously moved to NDB

DEPRECATED: NDB 8.0.16

--rewrite-database=olddb,newdb

Restores to a database with a different name than the original

(Supported in all MySQL 8.0 based releases)

--skip-broken-objects

Causes missing blob tables in the backup file to be ignored

(Supported in all MySQL 8.0 based releases)

--skip-table-check,

-s

Skip table structure check during restoring of data

(Supported in all MySQL 8.0 based releases)

--skip-unknown-objects

Causes schema objects not recognized by ndb_restore to be ignored when restoring a backup made from a newer MySQL Cluster version to an older version

(Supported in all MySQL 8.0 based releases)

--slice-id=#

Slice ID, when restoring by slices

ADDED: NDB 8.0.20

--tab=dir_name,

-T dir_name

Creates a tab-separated .txt file for each table in the given path

(Supported in all MySQL 8.0 based releases)

--verbose=#

Level of verbosity in output

(Supported in all MySQL 8.0 based releases)


Typical options for this utility are shown here:

ndb_restore [-c connection_string] -n node_id -b backup_id \
      [-m] -r --backup-path=/path/to/backup/files

Normally, when restoring from an NDB Cluster backup, ndb_restore requires at a minimum the --nodeid (short form: -n), --backupid (short form: -b), and --backup-path options.

Prior to NDB 8.0.19, when ndb_restore was used to restore any tables containing unique indexes, it was necessary to include --disable-indexes or --rebuild-indexes. Beginning with NDB 8.0.19, when automatic metadata synchronization is enabled, this is no longer necessary.

The -c option is used to specify a connection string which tells ndb_restore where to locate the cluster management server (see Section 22.3.3.3, “NDB Cluster Connection Strings”). If this option is not used, then ndb_restore attempts to connect to a management server on localhost:1186. This utility acts as a cluster API node, and so requires a free connection slot to connect to the cluster management server. This means that there must be at least one [api] or [mysqld] section that can be used by it in the cluster config.ini file. It is a good idea to keep at least one empty [api] or [mysqld] section in config.ini that is not being used for a MySQL server or other application for this reason (see Section 22.3.3.7, “Defining SQL and Other API Nodes in an NDB Cluster”).

You can verify that ndb_restore is connected to the cluster by using the SHOW command in the ndb_mgm management client. You can also accomplish this from a system shell, as shown here:

shell> ndb_mgm -e "SHOW"

More detailed information about all options used by ndb_restore can be found in the following list:

Error reporting.  ndb_restore reports both temporary and permanent errors. In the case of temporary errors, it may able to recover from them, and reports Restore successful, but encountered temporary error, please look at configuration in such cases.

Important

After using ndb_restore to initialize an NDB Cluster for use in circular replication, binary logs on the SQL node acting as the replication slave are not automatically created, and you must cause them to be created manually. To cause the binary logs to be created, issue a SHOW TABLES statement on that SQL node before running START SLAVE. This is a known issue in NDB Cluster.

Restoring a backup to a previous version of NDB Cluster.  You may encounter issues when restoring a backup taken from a later version of NDB Cluster to a previous one, due to the use of features which do not exist in the earlier version. For example, tables created in NDB 8.0 by default use the utf8mb4_ai_ci character set, which is not available in NDB 7.6 and earlier, and so cannot be read by an ndb_restore binary from one of these earlier versions.