1MYSQLDBEXPORT(1) MySQL Utilities MYSQLDBEXPORT(1)
2
6 mysqldbexport - Export a list of databases in a variety of formats
7
9 mysqldbexport --server=<user>[<passwd>]@<host>:[<port>][:<socket>]
10 (<db_name>[, <db_name>])+ [--quiet | --help | --no-headers |
11 --skip=(TABLES,TRIGGERS,VIEWS,PROCEDURES,FUNCTIONS,
12 EVENTS,GRANTS,DATA,CREATE_DB)* | --skip-blobs | --help |
13 --veerbose | --version | --bulk-insert | --file-per-table |
14 --export=[DEFINITIONS|DATA|BOTH] |
15 --format=[SQL|S|GRID|G|TAB|T|CSV|C|VERTICAL|V] ] |
16 --exclude=<name>[|,--exclude=<name>]
17
19 This utility permits a database administrator to export the metadata
20 (object definitions, hence definitions) or data or both from one or
21 more databases. By default, the utility will export only definitions.
22 You can also skip objects by type using the --skip option and list the
24 objects you want to skip. This can allow you to extract a particular
25 set of objects, say, for exporting only events (by excluding all other
26 types). Similarly, you can skip creating blob UPDATE commands by speci‐
27 fying the --skip-blobs option.
28 You also have the choice to view the output in one of the following
30 formats using the --format option.
31 SQL
33 Displays the output using SQL statements. For definitions, this
35 is the appropriate CREATE and GRANT statements. For data, this
36 is an INSERT statement (or bulk insert if the --bulk-insert
37 options is specified).
38 GRID
40 Displays output formatted like that of the mysql monitor in a
42 grid or table layout.
43 CSV
45 Displays the output in a comma-separated list.
47 TAB
49 Displays the output in a tab-separated list.
51 VERTICAL
53 Displays the output in a single column similar to the \G option
55 for the mysql monitor commands.
56 You also have the option to specify how much data to display in
58 one of the following displays using the --display option.
59 BRIEF
61 Show only the minimal columns for recreating the objects.
63 FULL
65 Show the complete column list for recreating the objects.
67 NAMES
69 Show only the names of the objects.
71 Note: When combining --format and --display, the --display option is
73 ignored for SQL generation.
74 You can turn off the headers when using formats CSV and TAB by specify‐
76 ing the --no-headers option.
77 You can turn off all feedback information by specifying the --quiet
79 option.
80 You can also have the utility write the data for the tables to separate
82 files by using the --file-per-table option. This would create files
83 with a file name composed of the database and table name followed by
84 the format of the file. For example, the following command produces
85 files named db1.<table name>.csv.:
86 mysqldbexport --server=root@server1:3306 --format=csv db1 --export=data
88 You can exclude specific objects by name using the --exclude option
90 whereby you specify a name in the form of <db>.<object> or you can sup‐
91 ply a regex search pattern. For example, --exclude=db1.trig1 will
92 exclude the single trigger and --exclude=trig_ will exclude all objects
93 from all databases whose name begins with trig and has a following
94 character or digit.
95 This utility differs from mysqldump in that it can produce output in a
97 variety of formats to make your data extraction/transport much easier.
98 It permits you to export your data in the format most suitable to an
99 external tool, another MySQL server, or a yet another use without the
100 need to reformat the data.
101 You must provide login information such as user, host, password, etc.
103 for a user that has the appropriate rights to access all objects in the
104 operation. See NOTES below for more details.
105
107 --version
108 show program's version number and exit
109 --help
111 --server <server>
113 connection information for the server in the form: <user>:<pass‐
115 word>@<host>:<port>:<socket>
116 --format <format>, -f <format>
118 display the output in either SQL|S (default), GRID|G, TAB|T,
120 CSV|C, or VERTICAL|V format
121 --display <display>, -d <display>
123 control the number of columns shown: BRIEF = minimal columns for
125 object creation (default), FULL = all columns, NAMES = only
126 object names (not valid for --format=SQL)
127 --export <export>, -e <export>
129 control the export of either DATA|D = only the table data for
131 the tables in the database list, DEFINITIONS|F = export only the
132 definitions for the objects in the database list, or BOTH|B =
133 export the metadata followed by the data (default: export meta‐
134 data)
135 --bulk-insert, -b
137 Use bulk insert statements for data (default:False)
139 --file-per-table
141 Write table data to separate files. Valid only for --export=data
142 or --export=both. Files will be named <db_name>.<tbl_name>.<for‐
143 mat>. For example, a CSV export of two tables in db1, t1 and t2,
144 results in files named db1.t1.csv and db1.t2.csv. If definitions
145 are included, they are written to stdout as normal.
146 --no-headers, -h
148 do not display the column headers - ignored for GRID format
150 --quiet, -q
152 turn off all messages for quiet execution
154 --verbose, -v
156 control how much information is displayed. For example, -v =
158 verbose, -vv = more verbose, -vvv = debug
159 --skip <skip-objects>
161 specify objects to skip in the operation in the form of a
163 comma-separated list (no spaces). Valid values = TABLES, VIEWS,
164 TRIGGERS, PROCEDURES, FUNCTIONS, EVENTS, GRANTS, DATA, CREATE_DB
165 -x EXCLUDE --exclude=EXCLUDE
167 exclude one or more objects from the operation using either a
169 specific name such as db1.t1 or a REGEXP search pattern. Repeat
170 option for multiple exclusions.
171 --skip-blobs
173 Do not export blob data.
174
176 The login user must have the appropriate permissions to create new
177 objects, read the old database, access (read) the mysql database, and
178 grant privileges.
179 To export all objects from a source, the user must have SELECT and SHOW
181 VIEW privileges on the database as well as SELECT on the mysql data‐
182 base.
183 Actual privileges needed may differ from installation to installation
185 depending on the security privileges present and whether the database
186 contains certain objects such as views or events and whether binary
187 logging is turned on (hence the need for SUPER).
188 Some combinations of the options may result in errors during the opera‐
190 tion. For example, eliminating tables but not views may result in an
191 error when the view is imported on another server.
192 The --exclude option does not apply to grants.
194
196 To export the definitions of the database 'dev' from a MySQL server on
197 localhast via port 3306 producing CREATE statements, use this command:
198 $ mysqldbexport --server=root:pass@localhost \\
200 --skip=GRANTS --export=DEFINITIONS util_test
201 # Source on localhost: ... connected.
202 # Exporting metadata from util_test
203 DROP DATABASE IF EXISTS util_test;
204 CREATE DATABASE util_test;
205 USE util_test;
206 # TABLE: util_test.t1
207 CREATE TABLE `t1` (
208 `a` char(30) DEFAULT NULL
209 ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
210 # TABLE: util_test.t2
211 CREATE TABLE `t2` (
212 `a` char(30) DEFAULT NULL
213 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
214 # TABLE: util_test.t3
215 CREATE TABLE `t3` (
216 `a` int(11) NOT NULL AUTO_INCREMENT,
217 `b` char(30) DEFAULT NULL,
218 PRIMARY KEY (`a`)
219 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
220 # TABLE: util_test.t4
221 CREATE TABLE `t4` (
222 `c` int(11) NOT NULL,
223 `d` int(11) NOT NULL,
224 KEY `ref_t3` (`c`),
225 CONSTRAINT `ref_t3` FOREIGN KEY (`c`) REFERENCES `t3` (`a`)
226 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
227 # VIEW: util_test.v1
228 [...]
229 #...done.
230 Similarly, to export the data of the database 'util_test' producing
232 bulk insert statements, use this command:
233 $ mysqldbexport --server=root:pass@localhost \\
235 --export=DATA --bulk-insert util_test
236 # Source on localhost: ... connected.
237 USE util_test;
238 # Exporting data from util_test
239 # Data for table util_test.t1:
240 INSERT INTO util_test.t1 VALUES ('01 Test Basic database example'),
241 ('02 Test Basic database example'),
242 ('03 Test Basic database example'),
243 ('04 Test Basic database example'),
244 ('05 Test Basic database example'),
245 ('06 Test Basic database example'),
246 ('07 Test Basic database example');
247 # Data for table util_test.t2:
248 INSERT INTO util_test.t2 VALUES ('11 Test Basic database example'),
249 ('12 Test Basic database example'),
250 ('13 Test Basic database example');
251 # Data for table util_test.t3:
252 INSERT INTO util_test.t3 VALUES (1, '14 test fkeys'),
253 (2, '15 test fkeys'),
254 (3, '16 test fkeys');
255 # Data for table util_test.t4:
256 INSERT INTO util_test.t4 VALUES (3, 2);
257 #...done.
258
260 Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
261 This program is free software; you can redistribute it and/or modify it
263 under the terms of the GNU General Public License as published by the
264 Free Software Foundation; version 2 of the License.
265 This program is distributed in the hope that it will be useful, but
267 WITHOUT ANY WARRANTY; without even the implied warranty of MER‐
268 CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
269 Public License for more details.
270 You should have received a copy of the GNU General Public License along
272 with this program; if not, write to the Free Software Foundation, Inc.,
273 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
274
276 MySQL Utilities Team
277
279 2010, Oracle and/or its affiliates. All rights reserved.
2801.0.1 September 23, 2011 MYSQLDBEXPORT(1)