'\" t
.\"     Title: \fBmysqldbimport\fR
.\"    Author: [FIXME: author] [see http://docbook.sf.net/el/author]
.\" Generator: DocBook XSL Stylesheets v1.79.1 <http://docbook.sf.net/>
.\"      Date: 08/01/2016
.\"    Manual: MySQL Utilities
.\"    Source: MySQL 1.6.3
.\"  Language: English
.\"
.TH "\FBMYSQLDBIMPORT\FR" "1" "08/01/2016" "MySQL 1\&.6\&.3" "MySQL Utilities"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
mysqldbimport \- Import Object Definitions or Data into a Database
.SH "SYNOPSIS"
.HP \w'\fBmysqldbimport\ [\fR\fB\fIoptions\fR\fR\fB]\ \fR\fB\fIimport_file\fR\fR\fB\ \&.\&.\&.\fR\ 'u
\fBmysqldbimport [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIimport_file\fR\fR\fB \&.\&.\&.\fR
.SH "DESCRIPTION"
.PP
This utility imports metadata (object definitions), data, or both for one or more databases from one or more files\&.
.PP
If an object exists on the destination server with the same name as an imported object, it may be dropped first by using the
\fB\-\-drop\-first\fR
option\&.
.PP
To skip objects by type, use the
\fB\-\-skip\fR
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
\fBUPDATE\fR
statements for
BLOB
data, specify the
\fB\-\-skip\-blobs\fR
option\&.
.PP
To specify the input format, use one of the following values with the
\fB\-\-format\fR
option\&. These correspond to the output formats of the
\fBmysqldbexport\fR
utility:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBsql\fR
(default)
.sp
Input consists of SQL statements\&. For definitions, this consists of the appropriate
\fBCREATE\fR
and
\fBGRANT\fR
statements\&. For data, this is an
\fBINSERT\fR
statement (or bulk insert if the
\fB\-\-bulk\-insert\fR
option is specified)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBgrid\fR
.sp
Display output in grid or table format like that of the
\fBmysql\fR
client command\-line tool\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBcsv\fR
.sp
Input is formatted in comma\-separated values format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBraw_csv\fR
.sp
Input is a simple CSV file containing uniform rows with values separated with commas\&. The file can contain a header (the first row) that lists the table columns\&. The option
\fB\-\-table\fR
is required to use this format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBtab\fR
.sp
Input is formatted in tab\-separated format\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBvertical\fR
.sp
Display output in single\-column format like that of the
\eG
command for the
\fBmysql\fR
client command\-line tool\&.
.RE
.PP
To indicate that input in
\fBcsv\fR
or
\fBtab\fR
format does not contain column headers, specify the
\fB\-\-no\-headers\fR
option\&.
.PP
To turn off all feedback information, specify the
\fB\-\-quiet\fR
option\&.
.PP
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.PP
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
\fB\-\-new\-storage\-engine\fR
option\&. If the destination server supports the new engine, all tables use that engine\&.
.PP
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
\fB\-\-default\-storage\-engine\fR
option\&.
.PP
The
\fB\-\-new\-storage\-engine\fR
option takes precedence over
\fB\-\-default\-storage\-engine\fR
if both are given\&.
.PP
If the
\fB\-\-new\-storage\-engine\fR
or
\fB\-\-default\-storage\-engine\fR
option is given and the destination server does not support the specified storage engine, a warning is issued and the server\*(Aqs default storage engine setting is used instead\&.
Importing Data with GTIDs.PP
If you attempt to import databases on a server with GTIDs enabled (GTID_MODE = ON), a warning will be generated if the import file did not include the GTID statements generated by
\fBmysqldbexport\fR\&.
.PP
The utility will also generate a warning if you import databases on a server without GTIDs enabled and there are GTID statements present in the file\&. Use the
\fB\-\-skip\-gtid\fR
option to ignore the GTID statements\&.
.PP
To make the most use of GTIDs and export/import, you should export all of the databases on the server with the
\fB \-\-all\fR
option\&. This will generate an export file with all of the databases and the GTIDs executed to that point\&. Importing this file on another server will ensure that server has all of the data as well as all of the GTIDs recorded correctly in its logs\&.
OPTIONS.PP
\fBmysqldbimport\fR
accepts the following command\-line options:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-help
.sp
Display a help message and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-license
.sp
Display license information and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-autocommit
.sp
Enable autocommit for data import\&. By default, autocommit is off and data changes are only committed once at the end of each imported file\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-bulk\-insert, \-b
.sp
Use bulk insert statements for data\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-character\-set=<charset>
.sp
Sets the client character set\&. The default is retrieved from the server variable \&character_set_client\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-default\-storage\-engine=<def_engine>
.sp
The engine to use for tables if the destination server does not support the original storage engine on the source server\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-drop\-first, \-d
.sp
Drop each database to be imported if exists before importing anything into it\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-dryrun
.sp
Import the files and generate the statements but do not execute them\&. This is useful for testing input file validity\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-format=<format>, \-f<format>
.sp
Specify the input format\&. Permitted format values are
\fBsql\fR
(default),
\fBgrid\fR,
\fBtab\fR,
\fBcsv\fR,
\fBraw_csv\fR, and
\fBvertical\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-import=<import_type>, \-i<import_type>
.sp
Specify the import format\&. Permitted format values are:
.sp
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.B Table\ \&5.2.\ \&mysqldbimport Import Types
.TS
allbox tab(:);
lB lB.
T{
Import Type
T}:T{
Definition
T}
.T&
l l
l l
l l.
T{
definitions (default)
T}:T{
Only import the definitions (metadata) for the objects in the database
                  list
T}
T{
data
T}:T{
Only import the table data for the tables in the database list
T}
T{
both
T}:T{
Import both the definitions (metadata) and data
T}
.TE
.sp 1
If you attempt to import objects into an existing database, the result depends on the import format\&. If the format is
\fBdefinitions\fR
or
\fBboth\fR, an error occurs unless
\fB\-\-drop\-first\fR
is given\&. If the format is
\fBdata\fR, imported table data is added to existing table data\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-max\-bulk\-insert
.sp
Specify the maximum number of INSERT statements to bulk, by default 30000\&. This option is only used with
\fB\-\-bulk\-insert\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-multiprocess
.sp
Specify the number of processes to concurrently import the specified files\&. Special values: 0 (number of processes equal to the number of detected CPUs) and 1 (default \- no concurrency)\&. Multiprocessing works at the files level for any operating systems\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-new\-storage\-engine=<new_engine>
.sp
The engine to use for all tables created on the destination MySQL server\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-no\-headers, \-h
.sp
Input does not contain column headers\&. This option only applies to the
\fBcsv\fR
and
\fBtab\fR
file formats\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-quiet, \-q
.sp
Turn off all messages for quiet execution\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-server=<server>
.sp
Connection information for the server\&.
.sp
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket\&. MySQL Utilities provides a number of ways to supply this information\&. All of the methods require specifying your choice via a command\-line option such as \-\-server, \-\-master, \-\-slave, etc\&. The methods include the following in order of most secure to least secure\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example : <\fIlogin\-path\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example : <\fIconfiguration\-file\-path\fR>[:<\fIsection\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example : <\fIuser\fR>[:<\fIpasswd\fR>]@<\fIhost\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip=<skip_objects>
.sp
Specify objects to skip in the operation as a comma\-separated list (no spaces)\&. Permitted values for this list are;
\fBCREATE_DB\fR,
\fBDATA\fR,
\fBEVENTS\fR,
\fBFUNCTIONS\fR,
\fBGRANTS\fR,
\fBPROCEDURES\fR,
\fBTABLES\fR,
\fBTRIGGERS\fR, and
\fBVIEWS\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip\-blobs
.sp
Do not import
BLOB
data\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip\-gtid
.sp
Skip execution of
GTID_PURGED
statements\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip\-rpl
.sp
Do not execute replication commands\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-ca
.sp
The path to a file that contains a list of trusted SSL CAs\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-cert
.sp
The name of the SSL certificate file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl\-cert
.sp
The name of the SSL key file to use for establishing a secure connection\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-ssl
.sp
Specifies if the server connection requires use of SSL\&. If an encrypted connection cannot be established, the connection attempt fails\&. Default setting is 0 (SSL not required)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-table=<db>,<table>
.sp
Specify the table for importing\&. This option is required while using
\fB\-\-format=raw_csv\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-verbose, \-v
.sp
Specify how much information to display\&. Use this option multiple times to increase the amount of information\&. For example,
\fB\-v\fR
= verbose,
\fB\-vv\fR
= more verbose,
\fB\-vvv\fR
= debug\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-version
.sp
Display version information and exit\&.
.RE
NOTES.PP
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\&.
.PP
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\&.
.PP
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\&.
.PP
The
\fB\-\-new\-storage\-engine\fR
and
\fB\-\-default\-storage\-engine\fR
options apply to all destination tables in the operation\&.
.PP
For the
\fB\-\-format\fR
and
\fB\-\-import\fR
options, the permitted values are not case sensitive\&. In addition, values may be specified as any unambiguous prefix of a valid value\&. For example,
\fB\-\-format=g\fR
specifies the grid format\&. An error occurs if a prefix matches more than one valid value\&.
.PP
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\&.
.PP
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 will allow the utility to use the
\fBmy_print_defaults\fR
tools which is required to read the login\-path values from the login configuration file (\&.mylogin\&.cnf)\&.
.PP
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
\fBmysqldbimport\fR
utility, which means that only different files can be concurrently imported\&.
EXAMPLES.PP
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:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbimport \-\-server=root@localhost \-\-import=definitions \e\fR
          \fB\-\-format=csv data\&.csv\fR
# Source on localhost: \&.\&.\&. connected\&.
# Importing definitions from data\&.csv\&.
#\&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
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:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbimport \-\-server=root@localhost \-\-import=data \e\fR
          \fB\-\-bulk\-insert \-\-format=csv data\&.csv\fR
# Source on localhost: \&.\&.\&. connected\&.
# Importing data from data\&.csv\&.
#\&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.PP
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:
.sp
.if n \{\
.RS 4
.\}
.nf
shell> \fBmysqldbimport \-\-server=root@localhost \-\-import=both \-\-bulk\-insert \-\-format=sql data\&.sql\fR
# Source on localhost: \&.\&.\&. connected\&.
# Importing definitions and data from data\&.sql\&.
#\&.\&.\&.done\&.
.fi
.if n \{\
.RE
.\}
.sp
PERMISSIONS REQUIRED.PP
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\&.
.SH "COPYRIGHT"
.br
.PP
Copyright \(co 2006, 2016, Oracle and/or its affiliates. All rights reserved.
.PP
This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.
.PP
This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
.PP
You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.
.sp
.SH "SEE ALSO"
For more information, please refer to the MySQL Utilities and Fabric
documentation, which is available online at
http://dev.mysql.com/doc/index-utils-fabric.html
.SH AUTHOR
Oracle Corporation (http://dev.mysql.com/).