1MYSQLDBIMPORT(1) MySQL Utilities MYSQLDBIMPORT(1)
2
6 mysqldbimport - Import Object Definitions or Data into a Databases
7
9 mysqldbimport [options] import_file ...
10
12 This utility imports metadata (object definitions) or data or both for
13 one or more databases from one or more files.
14 If an object exists on the destination server with the same name as an
16 imported object, it is dropped first before importing the new object.
17 To skip objects by type, use the --skip option with a list of the
19 objects to skip. This enables you to extract a particular set of
20 objects, say, for importing only events (by excluding all other types).
21 Similarly, to skip creation of UPDATE statements for BLOB data, specify
22 the --skip-blobs option.
23 To specify the input format, use one of the following values with the
25 --format option. These correspond to the output formats of the
26 mysqldbexport utility:
27 · sql (default)
29 Input consists of SQL statements. For definitions, this consists of
31 the appropriate CREATE and GRANT statements. For data, this is an
32 INSERT statement (or bulk insert if the --bulk-insert option is
33 specified).
34 · grid
36 Display output in grid or table format like that of the mysql
38 client command-line tool.
39 · csv
41 Input is formatted in comma-separated values format.
43 · raw_csv
45 Input is a simple CSV file containing uniform rows with values
47 separated with commas. The file can contain a header (the first
48 row) that lists the table columns. The option --table is required
49 to use this format.
50 · tab
52 Input is formatted in tab-separated format.
54 · vertical
56 Display output in single-column format like that of the \G command
58 for the mysql client command-line tool.
59 To indicate that input in csv or tab format does not contain column
61 headers, specify the --no-headers option.
62 To turn off all feedback information, specify the --quiet option.
64 By default, the utility creates each table on the destination server
66 using the same storage engine as the original table. To override this
67 and specify the storage engine to use for all tables created on the
68 destination server, use the --new-storage-engine option. If the
69 destination server supports the new engine, all tables use that engine.
70 To specify the storage engine to use for tables for which the
72 destination server does not support the original storage engine on the
73 source server, use the --default-storage-engine option.
74 The --new-storage-engine option takes precedence over
76 --default-storage-engine if both are given.
77 If the --new-storage-engine or --default-storage-engine option is given
79 and the destination server does not support the specified storage
80 engine, a warning is issued and the server's default storage engine
81 setting is used instead.
82 You must provide connection parameters (user, host, password, and so
84 forth) for an account that has the appropriate privileges to access all
85 objects in the operation. For details, see NOTES.
86 If you attempt to import databases on a server with GTIDs enabled
88 (GTID_MODE = ON), a warning will be generated if the import file did
89 not include the GTID statements generated by mysqldbexport.
90 The utility will also generate a warning if you import databases on a
92 server without GTIDs enabled and there are GTID statements present in
93 the file. Use the --skip-gtid option to ignore the GTID statements.
94 To make the most use of GTIDs and export/import, you should export all
96 of the databases on the server with the
97 --all option. This will generate an export file with all of the
98 databases and the GTIDs executed to that point. Importing this file on
99 another server will ensure that server has all of the data as well as
100 all of the GTIDs recorded correctly in its logs. OPTIONS.PP
101 mysqldbimport accepts the following command-line options:
102 · --help
104 Display a help message and exit.
106 · --license
108 Display license information and exit.
110 · --autocommit
112 Enable autocommit for data import. By default, autocommit is off
114 and data changes are only committed once at the end of each
115 imported file.
116 · --bulk-insert, -b
118 Use bulk insert statements for data.
120 · --character-set=<charset>
122 Sets the client character set. The default is retrieved from the
124 server variable character_set_client.
125 · --default-storage-engine=<def_engine>
127 The engine to use for tables if the destination server does not
129 support the original storage engine on the source server.
130 · --drop-first, -d
132 Drop each database to be imported if exists before importing
134 anything into it.
135 · --dryrun
137 Import the files and generate the statements but do not execute
139 them. This is useful for testing input file validity.
140 · --format=<format>, -f<format>
142 Specify the input format. Permitted format values are sql
144 (default), grid, tab, csv, raw_csv, and vertical.
145 · --import=<import_type>, -i<import_type>
147 Specify the import format. Permitted format values are:
149 Table 5.1. mysqldbimport Import Types
151 ┌──────────────────────┬────────────────────────────┐
152 │Import Type │ Definition │
153 ├──────────────────────┼────────────────────────────┤
154 │definitions (default) │ Only import the │
155 │ │ definitions (metadata) for │
156 │ │ the objects in the │
157 │ │ database │
158 │ │ list │
159 ├──────────────────────┼────────────────────────────┤
160 │data │ Only import the table data │
161 │ │ for the tables in the │
162 │ │ database list │
163 ├──────────────────────┼────────────────────────────┤
164 │both │ Import both the │
165 │ │ definitions (metadata) and │
166 │ │ data │
167 └──────────────────────┴────────────────────────────┘
168 If you attempt to import objects into an existing database, the
169 result depends on the import format. If the format is definitions
170 or both, an error occurs unless --drop-first is given. If the
171 format is data, imported table data is added to existing table
172 data.
173 · --max-bulk-insert
175 Specify the maximum number of INSERT statements to bulk, by default
177 30000. This option is only used with --bulk-insert.
178 · --multiprocess
180 Specify the number of processes to concurrently import the
182 specified files. Special values: 0 (number of processes equal to
183 the number of detected CPUs) and 1 (default - no concurrency).
184 Multiprocessing works at the files level for any operating systems.
185 · --new-storage-engine=<new_engine>
187 The engine to use for all tables created on the destination MySQL
189 server.
190 · --no-headers, -h
192 Input does not contain column headers. This option only applies to
194 the csv and tab file formats.
195 · --quiet, -q
197 Turn off all messages for quiet execution.
199 · --server=<server>
201 Connection information for the server.
203 To connect to a server, it is necessary to specify connection
205 parameters such as user name, host name, password, and either a
206 port or socket. MySQL Utilities provides a number of ways to
207 provide this information. All of the methods require specifying
208 your choice via a command-line option such as --server, --master,
209 --slave, etc. The methods include the following in order of most
210 secure to least secure.
211 · Use login-paths from your .mylogin.cnf file (encrypted, not
213 visible). Example : <login-path>[:<port>][:<socket>]
214 · Use a configuration file (unencrypted, not visible) Note:
216 available in release-1.5.0. Example :
217 <configuration-file-path>[:<section>]
218 · Specify the data on the command-line (unencrypted, visible).
220 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
221 · --skip=<skip_objects>
224 Specify objects to skip in the operation as a comma-separated list
226 (no spaces). Permitted values for this list are; CREATE_DB, DATA,
227 EVENTS, FUNCTIONS, GRANTS, PROCEDURES, TABLES, TRIGGERS, and VIEWS.
228 · --skip-blobs
230 Do not import BLOB data.
232 · --skip-gtid
234 Skip execution of GTID_PURGED statements.
236 · --skip-rpl
238 Do not execute replication commands.
240 · --ssl-ca
242 The path to a file that contains a list of trusted SSL CAs.
244 · --ssl-cert
246 The name of the SSL certificate file to use for establishing a
248 secure connection.
249 · --ssl-cert
251 The name of the SSL key file to use for establishing a secure
253 connection.
254 · --ssl
256 Specifies if the server connection requires use of SSL. If an
258 encrypted connection cannot be established, the connection attempt
259 fails. Default setting is 0 (SSL not required).
260 · --table=<db>,<table>
262 Specify the table for importing. This option is required while
264 using --format=raw_csv.
265 · --verbose, -v
267 Specify how much information to display. Use this option multiple
269 times to increase the amount of information. For example, -v =
270 verbose, -vv = more verbose, -vvv = debug.
271 · --version
273 Display version information and exit.
275 NOTES.PP The login user must have the appropriate permissions to create
276 new objects, access (read) the mysql database, and grant privileges. If
277 a database to be imported already exists, the user must have read
278 permission for it, which is needed to check the existence of objects in
279 the database.
280 Actual privileges needed may differ from installation to installation
282 depending on the security privileges present and whether the database
283 contains certain objects such as views or events and whether binary
284 logging is enabled.
285 Some combinations of the options may result in errors during the
287 operation. For example, excluding tables but not views may result in an
288 error when a view is imported.
289 The --new-storage-engine and --default-storage-engine options apply to
291 all destination tables in the operation.
292 For the --format and --import options, the permitted values are not
294 case sensitive. In addition, values may be specified as any unambiguous
295 prefix of a valid value. For example, --format=g specifies the grid
296 format. An error occurs if a prefix matches more than one valid value.
297 When importing data and including the GTID commands, you may encounter
299 an error similar to "GTID_PURGED can only be set when GTID_EXECUTED is
300 empty". This occurs because the destination server is not in a clean
301 replication state. To solve this problem, you can issue a "RESET
302 MASTER" command on the destination prior to executing the import.
303 The path to the MySQL client tools should be included in the PATH
305 environment variable in order to use the authentication mechanism with
306 login-paths. This will allow the utility to use the my_print_defaults
307 tools which is required to read the login-path values from the login
308 configuration file (.mylogin.cnf).
309 Keep in mind that you can only take advantage of multiprocessing if
311 your system has multiple CPUs available for concurrent execution. Also
312 note that multiprocessing is applied at the file level for the
313 mysqldbimport utility, which means that only different files can be
314 concurrently imported. EXAMPLES.PP To import the metadata from the
315 util_test database to the server on the local host using a file in CSV
316 format, use this command:
317 shell> mysqldbimport --server=root@localhost --import=definitions \
319 --format=csv data.csv
320 # Source on localhost: ... connected.
321 # Importing definitions from data.csv.
322 #...done.
323 Similarly, to import the data from the util_test database to the server
325 on the local host, importing the data using bulk insert statements, use
326 this command:
327 shell> mysqldbimport --server=root@localhost --import=data \
329 --bulk-insert --format=csv data.csv
330 # Source on localhost: ... connected.
331 # Importing data from data.csv.
332 #...done.
333 To import both data and definitions from the util_test database,
335 importing the data using bulk insert statements from a file that
336 contains SQL statements, use this command:
337 shell> mysqldbimport --server=root@localhost --import=both --bulk-insert --format=sql data.sql
339 # Source on localhost: ... connected.
340 # Importing definitions and data from data.sql.
341 #...done.
342 PERMISSIONS REQUIRED.PP You also need permissions to create the new
344 data directory and write data to it.
345
347 Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
348 reserved.
349 This documentation is free software; you can redistribute it and/or
351 modify it only under the terms of the GNU General Public License as
352 published by the Free Software Foundation; version 2 of the License.
353 This documentation is distributed in the hope that it will be useful,
355 but WITHOUT ANY WARRANTY; without even the implied warranty of
356 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
357 General Public License for more details.
358 You should have received a copy of the GNU General Public License along
360 with the program; if not, write to the Free Software Foundation, Inc.,
361 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
362 http://www.gnu.org/licenses/.
363
366 For more information, please refer to the MySQL Utilities and Fabric
367 documentation, which is available online at
368 http://dev.mysql.com/doc/index-utils-fabric.html
369
371 Oracle Corporation (http://dev.mysql.com/).
372MySQL 1.5.6 09/15/2015 MYSQLDBIMPORT(1)