1MYSQLDBIMPORT(1) MySQL Utilities MYSQLDBIMPORT(1)
2
6 mysqldbimport - Import object definitions or data into a database
7
9 mysqldbimport --server=<user>[<passwd>]@<host>:[<port>][:<socket>]
10 [--quiet | --help | --no-headers | --dryrun |
11 --skip=(TABLES,TRIGGERS,VIEWS,PROCEDURES,FUNCTIONS,
12 EVENTS,GRANTS,DATA,CREATE_DB)* | --skip-blobs | --verbose |
13 --version | --bulk-insert | --drop-first ]
14 --import=[DEFINITIIONS|DATA|BOTH] |
15 --format=[SQL|S|GRID|G|TAB|T|CSV|C|VERTICAL|V] |
16 --new-storage-engine=<engine> | --default-storage-engine=<engine>
17 <file> [|,<file>]
18
20 This utility permits a database administrator to import the metadata
21 (objects) or data for one or more databases from one or more files in
22 either SQL, CSV, TAB, GRID, or VERTICAL formats. These formats are the
23 output of the mysqldbexport utility. The utility allows you to import
24 either the object definitions, the data, or both for a list of data‐
25 bases.
26 You can also skip objects by type using the --skip option and list the
28 objects you want to skip. This can allow you to extract a particular
29 set of objects, say, for importing only events (by excluding all other
30 types). Similarly, you can skip creating blob UPDATE commands by speci‐
31 fying the --skip-blobs option.
32 You also have the choice to view the output in one of the following
34 formats using the --format option.
35 SQL
37 Displays the output using SQL statements. For definitions, this
39 is the appropriate CREATE and GRANT statements. For data, this
40 is an INSERT statement (or bulk insert if the --bulk-insert
41 options is specified).
42 GRID
44 Displays output formatted like that of the mysql monitor in a
46 grid or table layout.
47 CSV
49 Displays the output in a comma-separated list.
51 TAB
53 Displays the output in a tab-separated list.
55 VERTICAL
57 Displays the output in a single column similar to the G option
59 for the mysql monitor commands.
60 You can turn off the headers when using formats CSV and TAB by specify‐
62 ing the --no-headers option.
63 You can turn off all feedback information by specifying the --quiet
65 option.
66 To change the storage engine for all tables on the destination, specify
68 the new engine with the --new-storage-engine option. If the new engine
69 specified is available on the destination, all tables will be changed
70 to use the engine.
71 Similarly, you can specify a different default storage engine with the
73 --default-storage-engine option. If the engine specified is available
74 on the destination, any table that specifies a storage engine that is
75 not on the destination will use the new default engine. Note that this
76 overrides the default storage engine mechanism on the server.
77 If the option --default-storage-engine or --new-storage-engine is sup‐
79 plied and the storage engine specified does not exist, a warning shall
80 be issued and the default storage engine setting on the server shall be
81 used instead.
82 You must provide login information such as user, host, password, etc.
84 for a user that has the appropriate rights to access all objects in the
85 operation. See NOTES below for more details.
86
88 --version
89 show program's version number and exit
90 --help
92 --server=SERVER
94 connection information for the server in the form: <user>:<pass‐
96 word>@<host>:<port>:<socket>
97 --format=FORMAT, -f FORMAT
99 display the output in either SQL|S (default), GRID|G, TAB|T,
101 CSV|C, or VERTICAL|V format
102 --import=import, -i import
104 control the import of either DATA|D = only the table data for
106 the tables in the database list, DEFINITIONS|F = import only the
107 definitions for the objects in the database list, or BOTH|B =
108 import the metadata followed by the data (default: import meta‐
109 data)
110 --drop-first, -d
112 Drop database before importing.
114 --dryrun
116 import the files and generate the statements but do not execute
117 them - useful for testing file validity
118 --bulk-insert, -b
120 Use bulk insert statements for data (default:False)
122 --no-headers, -h
124 do not display the column headers - ignored for GRID format
126 --quiet, -q
128 turn off all messages for quiet execution
130 --verbose, -v
132 control how much information is displayed. For example, -v =
134 verbose, -vv = more verbose, -vvv = debug
135 --skip <skip-objects>
137 specify objects to skip in the operation in the form of a
139 comma-separated list (no spaces). Valid values = TABLES, VIEWS,
140 TRIGGERS, PROCEDURES, FUNCTIONS, EVENTS, GRANTS, DATA, CREATE_DB
141 --skip-blobs
143 Do not import blob data.
144 --new-storage-engine=NEW_ENGINE
146 Change all tables to use this storage engine if storage engine
148 exists on the destination.
149 --default-storage-engine=DEF_ENGINE
151 Change all tables to use this storage engine if the original
153 storage engine does not exist on the destination.
154
156 The login user must have the appropriate permissions to create new
157 objects, read the old database, access (read) the mysql database, and
158 grant privileges.
159 Actual privileges needed may differ from installation to installation
161 depending on the security privileges present and whether the database
162 contains certain objects such as views or events and whether binary
163 logging is turned on (hence the need for SUPER).
164 Some combinations of the options may result in errors during the opera‐
166 tion. For example, eliminating tables but not views may result in an
167 error when the view is imported on another server.
168 The --new-storage-engine and --default-storage-engine options apply to
170 all tables in the operation.
171
173 To import the metadata of the database 'util_test' to server1 on port
174 3306 using a file in CSV format, use this command:
175 $ mysqldbimport --import=definitions --server=root@localhost \\
177 --format=csv data.csv
178 # Source on localhost: ... connected.
179 # Importing definitions from data.csv.
180 #...done.
181 Similarly, to import the data of the database 'util_test' to server1 on
183 port 3306 producing bulk insert statements, use this command:
184 $ mysqldbimport --import=data --bulk-insert \\
186 --server=root@localhost --format=csv data.csv
187 # Source on localhost: ... connected.
188 # Importing data from data.csv.
189 #...done.
190 Also, to import both the data and definitions of the database
192 'util_test' to server1 on port 3306 producing bulk insert statements
193 from a file that contains SQL statements, use this command:
194 $ mysqldbimport --import=both --bulk-insert \\
196 --server=root@localhost --format=sql data.sql
197 # Source on localhost: ... connected.
198 # Importing definitions and data from data.sql.
199 #...done.
200
202 Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
203 This program is free software; you can redistribute it and/or modify it
205 under the terms of the GNU General Public License as published by the
206 Free Software Foundation; version 2 of the License.
207 This program is distributed in the hope that it will be useful, but
209 WITHOUT ANY WARRANTY; without even the implied warranty of MER‐
210 CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
211 Public License for more details.
212 You should have received a copy of the GNU General Public License along
214 with this program; if not, write to the Free Software Foundation, Inc.,
215 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
216
218 MySQL Utilities Team
219
221 2010, Oracle and/or its affiliates. All rights reserved.
2221.0.1 September 23, 2011 MYSQLDBIMPORT(1)