MySQL Utilities

5.9 mysqldbimport — Import Object Definitions or Data into a Database

This utility imports metadata (object definitions), data, or both for one or more databases from one or more files.

If an object exists on the destination server with the same name as an imported object, it may be dropped first by using the --drop-first option.

To skip objects by type, use the --skip option with a list of the objects to skip. This enables you to extract a particular set of objects, say, for importing only events (by excluding all other types). Similarly, to skip creation of UPDATE statements for BLOB data, specify the --skip-blobs option.

To specify the input format, use one of the following values with the --format option. These correspond to the output formats of the mysqldbexport utility:

To indicate that input in csv or tab format does not contain column headers, specify the --no-headers option.

To turn off all feedback information, specify the --quiet option.

You must provide connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access all objects in the operation. For details, see NOTES.

Changing Storage Engines

By default, the utility creates each table on the destination server using the same storage engine as the original table. To override this and specify the storage engine to use for all tables created on the destination server, use the --new-storage-engine option. If the destination server supports the new engine, all tables use that engine.

To specify the storage engine to use for tables for which the destination server does not support the original storage engine on the source server, use the --default-storage-engine option.

The --new-storage-engine option takes precedence over --default-storage-engine if both are given.

If the --new-storage-engine or --default-storage-engine option is given and the destination server does not support the specified storage engine, a warning is issued and the server's default storage engine setting is used instead.

Importing Data with GTIDs

If you attempt to import databases on a server with GTIDs enabled (GTID_MODE = ON), a warning is generated if the import file did not include the GTID statements generated by mysqldbexport.

The utility also generates a warning if you import databases on a server without GTIDs enabled and there are GTID statements present in the file. Use the --skip-gtid option to ignore the GTID statements.

To make the most use of GTIDs and export/import, you should export all of the databases on the server with the --all option. This action generates an export file with all of the databases and the GTIDs executed to that point. Importing this file on another server ensures that server has all of the data as well as all of the GTIDs recorded correctly in its logs.

OPTIONS

mysqldbimport accepts the following command-line options:

NOTES

The login user must have the appropriate permissions to create new objects, access (read) the mysql database, and grant privileges. If a database to be imported already exists, the user must have read permission for it, which is needed to check the existence of objects in the database.

Actual privileges needed may differ from installation to installation depending on the security privileges present and whether the database contains certain objects such as views or events and whether binary logging is enabled.

Some combinations of the options may result in errors during the operation. For example, excluding tables but not views may result in an error when a view is imported.

The --new-storage-engine and --default-storage-engine options apply to all destination tables in the operation.

For the --format and --import options, the permitted values are not case-sensitive. In addition, values may be specified as any unambiguous prefix of a valid value. For example, --format=g specifies the grid format. An error occurs if a prefix matches more than one valid value.

When importing table data that contain a 0 in the auto_increment column, the import fails unless the NO_AUTO_VALUE_ON_ZERO SQL_MODE SQL_MODE is set. You can view the SQL_MODE with the show variables like 'sql_mode' command and change the SQL_MODE with the set sql_mode command.

When importing data and including the GTID commands, you may encounter an error similar to "GTID_PURGED can only be set when GTID_EXECUTED is empty". This occurs because the destination server is not in a clean replication state. To solve this problem, you can issue a "RESET MASTER" command on the destination prior to executing the import.

The path to the MySQL client tools should be included in the PATH environment variable in order to use the authentication mechanism with login-paths. This permits the utility to use the my_print_defaults tools which is required to read the login-path values from the login configuration file (.mylogin.cnf).

Keep in mind that you can only take advantage of multiprocessing if your system has multiple CPUs available for concurrent execution. Also note that multiprocessing is applied at the file level for the mysqldbimport utility, which means that only different files can be concurrently imported.

EXAMPLES

To import the metadata from the util_test database to the server on the local host using a file in CSV format, use this command:

shell> mysqldbimport --server=root@localhost --import=definitions \
          --format=csv data.csv
# Source on localhost: ... connected.
# Importing definitions from data.csv.
#...done.

Similarly, to import the data from the util_test database to the server on the local host, importing the data using bulk insert statements, use this command:

shell> mysqldbimport --server=root@localhost --import=data \
          --bulk-insert --format=csv data.csv
# Source on localhost: ... connected.
# Importing data from data.csv.
#...done.

To import both data and definitions from the util_test database, importing the data using bulk insert statements from a file that contains SQL statements, use this command:

shell> mysqldbimport --server=root@localhost --import=both --bulk-insert --format=sql data.sql

# Source on localhost: ... connected.
# Importing definitions and data from data.sql.
#...done.

PERMISSIONS REQUIRED

You also need permissions to create the new data directory and write data to it including permissions to create all objects in the import stream such as views, events, and stored routines. Thus, actual permissions vary based on the contents of the import stream.