MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
ndb_import imports CSV-formatted data, such
as that produced by mysqldump
--tab, directly into
NDB using the NDB API.
ndb_import requires a connection to an NDB
management server (ndb_mgmd) to function; it
does not require a connection to a MySQL Server.
ndb_importdb_namefile_nameoptions
ndb_import requires two arguments.
db_name is the name of the database
where the table into which to import the data is found;
file_name is the name of the CSV file
from which to read the data; this must include the path to this
file if it is not in the current directory. The name of the file
must match that of the table; the file's extension, if any,
is not taken into consideration. Options supported by
ndb_import include those for specifying field
separators, escapes, and line terminators, and are described
later in this section. ndb_import must be
able to connect to an NDB Cluster management server; for this
reason, there must be an unused [api] slot in
the cluster config.ini file.
To duplicate an existing table that uses a different storage
engine, such as InnoDB, as an
NDB table, use the mysql
client to perform a
SELECT INTO
OUTFILE statement to export the existing table to a
CSV file, then to execute a
CREATE TABLE
LIKE statement to create a new table having the same
structure as the existing table, then perform
ALTER TABLE ...
ENGINE=NDB on the new table; after this, from the
system shell, invoke ndb_import to load the
data into the new NDB table. For example, an
existing InnoDB table named
myinnodb_table in a database named
myinnodb can be exported into an
NDB table named
myndb_table in a database named
myndb as shown here, assuming that you are
already logged in as a MySQL user with the appropriate
privileges:
In the mysql client:
mysql>USE myinnodb;mysql>SELECT * INTO OUTFILE '/tmp/myndb_table.csv'>FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'>LINES TERMINATED BY '\n'>FROM myinnodbtable;mysql>CREATE DATABASE myndb;mysql>USE myndb;mysql>CREATE TABLE myndb_table LIKE myinnodb.myinnodb_table;mysql>ALTER TABLE myndb_table ENGINE=NDB;mysql>EXIT;Bye shell>
Once the target database and table have been created, a running mysqld is no longer required. You can stop it using mysqladmin shutdown or another method before proceeding, if you wish.
In the system shell:
# if you are not already in the MySQL bin directory: shell>cdshell>path-to-mysql-bin-dirndb_import myndb /tmp/myndb_table.csv --fields-optionally-enclosed-by='"' \--fields-terminated-by="," --fields-escaped-by='\\'
The output should resemble what is shown here:
job-1 import myndb.myndb_table from /tmp/myndb_table.csv job-1 [running] import myndb.myndb_table from /tmp/myndb_table.csv job-1 [success] import myndb.myndb_table from /tmp/myndb_table.csv job-1 imported 19984 rows in 0h0m9s at 2277 rows/s jobs summary: defined: 1 run: 1 with success: 1 with failure: 0 shell>
The following table includes options that are specific to ndb_import. Additional descriptions follow the table. For options common to most NDB Cluster programs (including ndb_import), see Section 20.4.32, “Options Common to NDB Cluster Programs — Options Common to NDB Cluster Programs”.
Table 20.33 Command-line options for the ndb_import program
| Format | Description | Added, Deprecated, or Removed |
|---|---|---|
| Dump core on any fatal error; used for debugging | ADDED: NDB 7.6.2 |
|
| For table with hidden PK, specify autoincrement increment. See mysqld | ADDED: NDB 7.6.2 |
|
| For table with hidden PK, specify autoincrement offset. See mysqld | ADDED: NDB 7.6.2 |
|
| For table with hidden PK, specify number of autoincrement values that are prefetched. See mysqld | ADDED: NDB 7.6.2 |
|
| Number of cluster connections to create | ADDED: NDB 7.6.2 |
|
| When job fails, continue to next job | ADDED: NDB 7.6.2 |
|
| Number of threads, per data node, executing database operations | ADDED: NDB 7.6.2 |
|
| Error insert type, for testing purposes; use "list" to obtain all possible values | ADDED: NDB 7.6.2 |
|
| Error insert delay in milliseconds; random variation is added | ADDED: NDB 7.6.2 |
|
| Same as FIELDS ENCLOSED BY option for LOAD DATA statements. For CSV input this is same as using --fields-optionally-enclosed-by | ADDED: NDB 7.6.2 |
|
| Same as FIELDS ESCAPED BY option for LOAD DATA statements | ADDED: NDB 7.6.2 |
|
| Same as FIELDS OPTIONALLY ENCLOSED BY option for LOAD DATA statements | ADDED: NDB 7.6.2 |
|
| Same as FIELDS TERMINATED BY option for LOAD DATA statements | ADDED: NDB 7.6.2 |
|
| Number of milliseconds to sleep waiting for more to do | ADDED: NDB 7.6.2 |
|
| Number of times to re-try before idlesleep | ADDED: NDB 7.6.2 |
|
| Ignore first # lines in input file. Used to skip a non-data header | ADDED: NDB 7.6.2 |
|
| Input type: random or csv | ADDED: NDB 7.6.2 |
|
| Number of threads processing input. Must be 2 or more if --input-type is csv | ADDED: NDB 7.6.2 |
|
| State files (except non-empty *.rej files) are normally removed on job completion. Using this option causes all state files to be preserved instead | ADDED: NDB 7.6.4 |
|
| Same as LINES TERMINATED BY option for LOAD DATA statements | ADDED: NDB 7.6.2 |
|
| Import only this number of input data rows; default is 0, which imports all rows | ADDED: NDB 7.6.2 |
|
| Periodically print status of running job if something has changed (status, rejected rows, temporary errors). Value 0 disables. Value 1 prints any change seen. Higher values reduce status printing exponentially up to some pre-defined limit | ADDED: NDB 7.6.2 |
|
| Run database operations as batches, in single transactions | ADDED: NDB 7.6.2 |
|
| Do not use distribution key hint to select data node (TC) | ADDED: NDB 7.6.2 |
|
| A db execution batch is a set of transactions and operations sent to NDB kernel. This option limits NDB operations (including blob operations) in a db execution batch. Therefore it also limits number of asynch transactions. Value 0 is not valid | ADDED: NDB 7.6.2 |
|
| Limit bytes in execution batch (default 0 = no limit) | ADDED: NDB 7.6.2 |
|
| Output type: ndb is default, null used for testing | ADDED: NDB 7.6.2 |
|
| Number of threads processing output or relaying database operations | ADDED: NDB 7.6.2 |
|
| Align I/O buffers to given size | ADDED: NDB 7.6.2 |
|
| Size of I/O buffers as multiple of page size. CSV input worker allocates a double-sized buffer | ADDED: NDB 7.6.2 |
|
| Timeout per poll for completed asynchonous transactions; polling continues until all polls are completed, or error occurs | ADDED: NDB 7.6.2 |
|
| Limit number of rejected rows (rows with permanent error) in data load. Default is 0 which means that any rejected row causes a fatal error. The row exceeding the limit is also added to *.rej | ADDED: NDB 7.6.2 |
|
| If job aborted (temporary error, user interrupt), resume with rows not yet processed | ADDED: NDB 7.6.2 |
|
| Limit rows in row queues (default 0 = no limit); must be 1 or more if --input-type is random | ADDED: NDB 7.6.2 |
|
| Limit bytes in row queues (0 = no limit) | ADDED: NDB 7.6.2 |
|
| Where to write state files; currect directory is default | ADDED: NDB 7.6.2 |
|
| Save performance related options and internal statistics in *.sto and *.stt files. These files are kept on successful completion even if --keep-state is not used | ADDED: NDB 7.6.4 |
|
| Number of milliseconds to sleep between temporary errors | ADDED: NDB 7.6.2 |
|
| Number of times a transaction can fail due to a temporary error, per execution batch; 0 means any temporary error is fatal. Such errors do not cause any rows to be written to .rej file | ADDED: NDB 7.6.2 |
|
| Enable verbose output | ADDED: NDB 7.6.2 |
| Command-Line Format | --abort-on-error |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Boolean |
| Default Value | FALSE |
Dump core on any fatal error; used for debugging only.
| Command-Line Format | --ai-increment=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 1 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
For a table with a hidden primary key, specify the
autoincrement increment, like the
auto_increment_increment
system variable does in the MySQL Server.
| Command-Line Format | --ai-offset=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 1 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
For a table with hidden primary key, specify the
autoincrement offset. Similar to the
auto_increment_offset
system variable.
| Command-Line Format | --ai-prefetch-sz=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 1024 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
For a table with a hidden primary key, specify the number of
autoincrement values that are prefetched. Behaves like the
ndb_autoincrement_prefetch_sz
system variable does in the MySQL Server.
| Command-Line Format | --connections=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 1 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
Number of cluster connections to create.
| Command-Line Format | --continue |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Boolean |
| Default Value | FALSE |
When a job fails, continue to the next job.
| Command-Line Format | --db-workers=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value (≥ 5.7.20-ndb-7.6.4) | 4 |
| Default Value (≥ 5.7.18-ndb-7.6.2, ≤ 5.7.18-ndb-7.6.3) | 1 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
Number of threads, per data node, executing database operations.
| Command-Line Format | --errins-type=name |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Enumeration |
| Default Value | [none] |
| Valid Values |
|
Error insert type; use list as the
name value to obtain all possible
values. This option is used for testing purposes only.
| Command-Line Format | --errins-delay=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 1000 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
| Unit | ms |
Error insert delay in milliseconds; random variation is added. This option is used for testing purposes only.
| Command-Line Format | --fields-enclosed-by=char |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | String |
| Default Value | [none] |
This works in the same way as the FIELDS ENCLOSED
BY option does for the LOAD
DATA statement, specifying a character to be
interpeted as quoting field values. For CSV input, this is
the same as
--fields-optionally-enclosed-by.
| Command-Line Format | --fields-escaped-by=name |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | String |
| Default Value | \ |
Specify an escape character in the same way as the
FIELDS ESCAPED BY option does for the SQL
LOAD DATA statement.
--fields-optionally-enclosed-by=char
| Command-Line Format | --fields-optionally-enclosed-by=char |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | String |
| Default Value | [none] |
This works in the same way as the FIELDS OPTIONALLY
ENCLOSED BY option does for the
LOAD DATA statement,
specifying a character to be interpeted as optionally
quoting field values. For CSV input, this is the same as
--fields-enclosed-by.
| Command-Line Format | --fields-terminated-by=char |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | String |
| Default Value | \t |
This works in the same way as the FIELDS TERMINATED
BY option does for the LOAD
DATA statement, specifying a character to be
interpeted as the field separator.
| Command-Line Format | --idlesleep=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 1 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
| Unit | ms |
Number of milliseconds to sleep waiting for more work to perform.
| Command-Line Format | --idlespin=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
Number of times to retry before sleeping.
| Command-Line Format | --ignore-lines=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
Cause ndb_import to ignore the first
# lines of the input file. This
can be employed to skip a file header that does not contain
any data.
| Command-Line Format | --input-type=name |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Enumeration |
| Default Value | csv |
| Valid Values |
|
Set the type of input type. The default is
csv; random is
intended for testing purposes only. .
| Command-Line Format | --input-workers=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value (≥ 5.7.20-ndb-7.6.4) | 4 |
| Default Value (≥ 5.7.18-ndb-7.6.2, ≤ 5.7.18-ndb-7.6.3) | 2 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
Set the number of threads processing input.
| Command-Line Format | --keep-state |
|---|---|
| Introduced | 5.7.20-ndb-7.6.4 |
| Type | Boolean |
| Default Value | false |
By default, ndb_import removes all state files (except
non-empty *.rej files) when it
completes a job. Specify this option (nor argument is
required) to force the program to retain all state files
instead.
| Command-Line Format | --lines-terminated-by=name |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | String |
| Default Value | \n |
This works in the same way as the LINES TERMINATED
BY option does for the LOAD
DATA statement, specifying a character to be
interpeted as end-of-line.
| Command-Line Format | --log-level=# |
|---|---|
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 2 |
Performs internal logging at the given level. This option is intended primarily for internal and development use.
In debug builds of NDB only, the logging level can be set using this option to a maximum of 4.
| Command-Line Format | --max-rows=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
| Unit | bytes |
Import only this number of input data rows; the default is 0, which imports all rows.
| Command-Line Format | --monitor=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 2 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
| Unit | bytes |
Periodically print the status of a running job if something has changed (status, rejected rows, temporary errors). Set to 0 to disable this reporting. Setting to 1 prints any change that is seen. Higher values reduce the frequency of this status reporting.
| Command-Line Format | --no-asynch |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Boolean |
| Default Value | FALSE |
Run database operations as batches, in single transactions.
| Command-Line Format | --no-hint |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Boolean |
| Default Value | FALSE |
Do not use distribution key hinting to select a data node.
| Command-Line Format | --opbatch=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 256 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
| Unit | bytes |
Set a limit on the number of operations (including blob operations), and thus the number of asynchronous transactions, per execution batch.
| Command-Line Format | --opbytes=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
| Unit | bytes |
Set a limit on the number of bytes per execution batch. Use 0 for no limit.
| Command-Line Format | --output-type=name |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Enumeration |
| Default Value | ndb |
| Valid Values | null |
Set the output type. ndb is the default.
null is used only for testing.
| Command-Line Format | --output-workers=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 2 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
Set the number of threads processing output or relaying database operations.
| Command-Line Format | --pagesize=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 4096 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
| Unit | bytes |
Align I/O buffers to the given size.
| Command-Line Format | --pagecnt=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 64 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
Set the size of I/O buffers as multiple of page size. The CSV input worker allocates buffer that is doubled in size.
| Command-Line Format | --polltimeout=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 1000 |
| Minimum Value | 1 |
| Maximum Value | 4294967295 |
| Unit | ms |
Set a timeout per poll for completed asynchonous transactions; polling continues until all polls are completed, or until an error occurs.
| Command-Line Format | --rejects=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
Limit the number of rejected rows (rows with permanent
errors) in the data load. The default is 0, which means that
any rejected row causes a fatal error. Any rows causing the
limit to be exceeded are added to the
.rej file.
The limit imposed by this option is effective for the
duration of the current run. A run restarted using
--resume is considered a
“new” run for this purpose.
| Command-Line Format | --resume |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Boolean |
| Default Value | FALSE |
If a job is aborted (due to a temporary db error or when interrupted by the user), resume with any rows not yet processed.
| Command-Line Format | --rowbatch=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
| Unit | rows |
Set a limit on the number of rows per row queue. Use 0 for no limit.
| Command-Line Format | --rowbytes=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 262144 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
| Unit | bytes |
Set a limit on the number of bytes per row queue. Use 0 for no limit.
| Command-Line Format | --stats |
|---|---|
| Introduced | 5.7.20-ndb-7.6.4 |
| Type | Boolean |
| Default Value | false |
Save information about options related to performance and
other internal statistics in files named
*.sto and *.stt.
These files are always kept on successful completion (even
if --keep-state is not
also specified).
| Command-Line Format | --state-dir=name |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | String |
| Default Value | . |
Where to write the state files
(tbl_name.maptbl_name.rejtbl_name.restbl_name.stt
| Command-Line Format | --tempdelay=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 10 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
| Unit | ms |
Number of milliseconds to sleep between temporary errors.
| Command-Line Format | --temperrors=# |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type | Integer |
| Default Value | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
Number of times a transaction can fail due to a temporary
error, per execution batch. The default is 0, which means
that any temporary error is fatal. Temporary errors do not
cause any rows to be added to the .rej
file.
| Command-Line Format | --verbose |
|---|---|
| Introduced | 5.7.18-ndb-7.6.2 |
| Type (≥ 5.7.20-ndb-7.6.4) | Boolean |
| Type (≥ 5.7.18-ndb-7.6.2, ≤ 5.7.18-ndb-7.6.3) | Integer |
| Default Value (≥ 5.7.20-ndb-7.6.4) | false |
| Default Value (≥ 5.7.18-ndb-7.6.2, ≤ 5.7.18-ndb-7.6.3) | 0 |
| Minimum Value | 0 |
| Maximum Value | 2 |
NDB 7.6.4 and later: Enable verbose output.
Previously, this option controlled the internal logging
level for debugging messages. In NDB 7.6.4 and later, use
the --log-level option
for this purpose instead.
As with LOAD DATA, options for
field and line formatting much match those used to create the
CSV file, whether this was done using
SELECT INTO ...
OUTFILE, or by some other means. There is no
equivalent to the LOAD DATA
statement STARTING WITH option.
ndb_import was added in NDB 7.6.2.