1MYSQLDBEXPORT(1) MySQL Utilities MYSQLDBEXPORT(1)
2
6 mysqldbexport - Export Object Definitions or Data from a Database
7
9 mysqldbexport [options] db_name ...
10
12 This utility exports metadata (object definitions) or data or both from
13 one or more databases. By default, the export includes only
14 definitions.
15 mysqldbexport differs from mysqldump in that it can produce output in a
17 variety of formats to make your data extraction/transport much easier.
18 It permits you to export your data in the format most suitable to an
19 external tool, another MySQL server, or other use without the need to
20 reformat the data.
21 To exclude specific objects by name, use the --exclude option with a
23 name in db.*obj* format, or you can supply a search pattern. For
24 example, --exclude=db1.trig1 excludes the single trigger and
25 --exclude=trig_ excludes all objects from all databases having a name
26 that begins with trig and has a following character.
27 To skip objects by type, use the --skip option with a list of the
29 objects to skip. This enables you to extract a particular set of
30 objects, say, for exporting only events (by excluding all other types).
31 Similarly, to skip creation of UPDATE statements for BLOB data, specify
32 the --skip-blobs option.
33 To specify how to display output, use one of the following values with
35 the --format option:
36 · sql (default)
38 Display output using SQL statements. For definitions, this consists
40 of the appropriate CREATE and GRANT statements. For data, this is
41 an INSERT statement (or bulk insert if the --bulk-insert option is
42 specified).
43 · grid
45 Display output in grid or table format like that of the mysql
47 client command-line tool.
48 · csv
50 Display output in comma-separated values format.
52 · tab
54 Display output in tab-separated format.
56 · vertical
58 Display output in single-column format like that of the \G command
60 for the mysql client command-line tool.
61 To specify how much data to display, use one of the following values
63 with the --display option:
64 · brief
66 Display only the minimal columns for recreating the objects.
68 · full
70 Display the complete column list for recreating the objects.
72 · names
74 Display only the object names.
76 Note
78 The --display option is ignored when combined with the SQL-format
79 output type.
80 To turn off the headers for csv or tab display format, specify the
82 --no-headers option.
83 To turn off all feedback information, specify the --quiet option.
85 To write the data for individual tables to separate files, use the
87 --file-per-table option. The name of each file is composed of the
88 database and table names followed by the file format. For example, the
89 following command produces files named db1.*table_name*.csv:
90 mysqldbexport --server=root@server1:3306 --format=csv db1 --export=data
92 By default, the operation uses a consistent snapshot to read the source
94 databases. To change the locking mode, use the --locking option with a
95 locking type value. Use a value of no-locks to turn off locking
96 altogether or lock-all to use only table locks. The default value is
97 snapshot. Additionally, the utility uses WRITE locks to lock the
98 destination tables during the copy.
99 You can include replication statements for exporting data among a
101 master and slave or between slaves. The --rpl option permits you to
102 select from the following replication statements to include in the
103 export.
104 · master
106 Include the CHANGE MASTER statement to make the destination server
108 a slave of the server specified in the --server option. This places
109 the appropriate STOP and START slave statements in the export
110 whereby the STOP SLAVE statement is placed at the start of the
111 export and the CHANGE MASTER followed by the START SLAVE statements
112 are placed after the export stream.
113 · slave
115 Include the CHANGE MASTER statement to make the destination server
117 a slave connected to the same master as the server specified in the
118 --server option. It only works if the current server is a slave.
119 This places the appropriate STOP and START slave statements in the
120 export whereby the STOP SLAVE statement is placed at the start of
121 the export and the CHANGE MASTER followed by the START SLAVE
122 statements are placed after the export stream.
123 · both
125 Include both the 'master' and 'slave' information for CHANGE MASTER
127 statements for either spawning a new slave with the current
128 server's master or using the current server as the master. All
129 statements generated are labeled and commented to enable the user
130 to choose which to include when imported.
131 To include the replication user in the CHANGE MASTER statement, use the
133 --rpl-user option to specify the user and password. If this option is
134 omitted, the utility attempts to identify the replication user. In the
135 event that there are multiple candidates or the user requires a
136 password, these statements are placed inside comments for the CHANGE
137 MASTER statement.
138 You can also use the --comment-rpl option to place the replication
140 statements inside comments for later examination.
141 If you specify the --rpl-file option, the utility writes the
143 replication statements to the file specified instead of including them
144 in the export stream.
145 If you attempt to export databases on a server with GTIDs enabled
147 (GTID_MODE = ON), a warning will be generated if the export does not
148 include all databases. This is because the GTID statements generated
149 include the GTIDs for all databases and not only those databases in the
150 export.
151 The utility will also generate a warning if you export databases on a
153 GTID enabled server but use the --skip-gtid option.
154 To make the most use of GTIDs and export/import, you should export all
156 of the databases on the server with the --all option. This will
157 generate an export file with all of the databases and the GTIDs
158 executed to that point.
159 Importing this file on another server will ensure that server has all
161 of the data as well as all of the GTIDs recorded correctly in its logs.
162 OPTIONS.PP mysqldbexport accepts the following command-line options:
163 · --help
165 Display a help message and exit.
167 · --license
169 Display license information and exit.
171 · --bulk-insert, -b
173 Use bulk insert statements for data.
175 · --character-set=<charset>
177 Sets the client character set. The default is retrieved from the
179 server variable character_set_client.
180 · --comment-rpl
182 Place the replication statements in comment statements. Valid only
184 with the --rpl option.
185 · --display=<display>, -d<display>
187 Control the number of columns shown. Permitted display values are
189 brief (minimal columns for object creation), full* (all columns),
190 and **names (only object names; not valid for --format=sql). The
191 default is brief.
192 · --exclude=<exclude>, -x<exclude>
194 Exclude one or more objects from the operation using either a
196 specific name such as db1.t1 or a search pattern. Use this option
197 multiple times to specify multiple exclusions. By default, patterns
198 use LIKE matching. With the --regexp option, patterns use REGEXP
199 matching.
200 This option does not apply to grants.
202 · --export=<export>, -e<export>
204 Specify the export format. Permitted format values are definitions
206 = export only the definitions (metadata) for the objects in the
207 database list, data = export only the table data for the tables in
208 the database list, and both = export the definitions and the data.
209 The default is definitions.
210 · --file-per-table
212 Write table data to separate files. This is Valid only if the
214 export output includes data (that is, if --export=data or
215 --export=both are given). This option produces files named
216 db_name.*tbl_name*.*format*. For example, a csv export of two
217 tables named t1 and t2 in database d1, results in files named
218 db1.t1.csv and db1.t2.csv. If table definitions are included in the
219 export, they are written to stdout as usual.
220 · --format=<format>, -f<format>
222 Specify the output display format. Permitted format values are sql,
224 grid, tab, csv, and vertical. The default is sql.
225 · --locking=<locking>
227 Choose the lock type for the operation. Permitted lock values are
229 no-locks (do not use any table locks), lock-all (use table locks
230 but no transaction and no consistent read), and snapshot
231 (consistent read using a single transaction). The default is
232 snapshot.
233 · --multiprocess
235 Specify the number of processes to concurrently export the
237 specified databases. Special values: 0 (number of processes equal
238 to the number of detected CPUs) and 1 (default - no concurrency).
239 Multiprocessing works at the database level for Windows and at the
240 table level for Non-Windows (POSIX) systems.
241 · --no-headers, -h
243 Do not display column headers. This option applies only for csv and
245 tab output.
246 · --output-file
248 Specify the path and file name to store the generated export
250 output. By default the standard output is used (no file).
251 · --quiet, -q
253 Turn off all messages for quiet execution.
255 · --regexp, --basic-regexp, -G
257 Perform pattern matches using the REGEXP operator. The default is
259 to use LIKE for matching.
260 · --rpl=<rpl_mode>, --replication=<rpl_mode>
262 Include replication information. Permitted values are master (make
264 destination a slave of the source server), slave (make destination
265 a slave of the same master as the source - only works if the source
266 server is a slave), and both (include the master and slave options
267 where applicable).
268 · --rpl-file=RPL_FILE, --replication-file=RPL_FILE
270 The path and file name where the generated replication information
272 should be written. Valid only with the --rpl option.
273 · --rpl-user=<replication_user>
275 The user and password for the replication user requirement, in the
277 format: <user>[:<password>] or <login-path>. For example,
278 rpl:passwd. The default is None.
279 · --server=<server>
281 Connection information for the server.
283 To connect to a server, it is necessary to specify connection
285 parameters such as user name, host name, password, and either a
286 port or socket. MySQL Utilities provides a number of ways to
287 provide this information. All of the methods require specifying
288 your choice via a command-line option such as --server, --master,
289 --slave, etc. The methods include the following in order of most
290 secure to least secure.
291 · Use login-paths from your .mylogin.cnf file (encrypted, not
293 visible). Example : <login-path>[:<port>][:<socket>]
294 · Use a configuration file (unencrypted, not visible) Note:
296 available in release-1.5.0. Example :
297 <configuration-file-path>[:<section>]
298 · Specify the data on the command-line (unencrypted, visible).
300 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
301 · --ssl-ca
304 The path to a file that contains a list of trusted SSL CAs.
306 · --ssl-cert
308 The name of the SSL certificate file to use for establishing a
310 secure connection.
311 · --ssl-cert
313 The name of the SSL key file to use for establishing a secure
315 connection.
316 · --ssl
318 Specifies if the server connection requires use of SSL. If an
320 encrypted connection cannot be established, the connection attempt
321 fails. Default setting is 0 (SSL not required).
322 · --skip=<skip-objects>
324 Specify objects to skip in the operation as a comma-separated list
326 (no spaces). Permitted values are CREATE_DB, DATA, EVENTS,
327 FUNCTIONS, GRANTS, PROCEDURES, TABLES, TRIGGERS, and VIEWS.
328 · --skip-blobs
330 Do not export BLOB data.
332 · --skip-gtid
334 Skip creation of GTID_PURGED statements.
336 · --all
338 Generate an export file with all of the databases and the GTIDs
340 executed to that point.
341 · --verbose, -v
343 Specify how much information to display. Use this option multiple
345 times to increase the amount of information. For example, -v =
346 verbose, -vv = more verbose, -vvv = debug.
347 · --version
349 Display version information and exit.
351 NOTES.PP You must provide connection parameters (user, host, password,
352 and so forth) for an account that has the appropriate privileges to
353 access all objects in the operation.
354 To export all objects from a source database, the user must have these
356 privileges: SELECT and SHOW VIEW on the database as well as SELECT on
357 the mysql database.
358 Actual privileges needed may differ from installation to installation
360 depending on the security privileges present and whether the database
361 contains certain objects such as views or events.
362 Some combinations of the options may result in errors when the export
364 is imported later. For example, eliminating tables but not views may
365 result in an error when a view is imported on another server.
366 For the --format, --export, and --display options, the permitted values
368 are not case sensitive. In addition, values may be specified as any
369 unambiguous prefix of a valid value. For example, --format=g specifies
370 the grid format. An error occurs if a prefix matches more than one
371 valid value.
372 The path to the MySQL client tools should be included in the PATH
374 environment variable in order to use the authentication mechanism with
375 login-paths. This will allow the utility to use the my_print_defaults
376 tools which is required to read the login-path values from the login
377 configuration file (.mylogin.cnf).
378 If any database identifier specified as an argument contains special
380 characters or is a reserved word, then it must be appropriately quoted
381 with backticks (`). In turn, names quoted with backticks must also be
382 quoted with single or double quotes depending on the operating system,
383 i.e. (") in Windows or (') in non-Windows systems, in order for the
384 utilities to read backtick quoted identifiers as a single argument. For
385 example, to export a database with the name weird`db.name, it must be
386 specified as argument using the following syntax (in non-Windows):
387 '`weird``db.name`'.
388 Keep in mind that you can only take advantage of multiprocessing if
390 your system has multiple CPUs available for concurrent execution. Also
391 note that multiprocessing is applied at a different level according to
392 the operating system where the mysqldbexport utility is executed (due
393 to python limitations). In particular, it is applied at the database
394 level for Windows (i.e., different databases are concurrently exported)
395 and at the table level for Non-Windows (POSIX) systems (i.e., different
396 tables within the same database are concurrently exported).
397 EXAMPLES.PP To export the definitions of the database dev from a MySQL
398 server on the local host via port 3306, producing output consisting of
399 CREATE statements, use this command:
400 shell> mysqldbexport --server=root:pass@localhost \
402 --skip=GRANTS --export=DEFINITIONS util_test
403 # Source on localhost: ... connected.
404 # Exporting metadata from util_test
405 DROP DATABASE IF EXISTS util_test;
406 CREATE DATABASE util_test;
407 USE util_test;
408 # TABLE: util_test.t1
409 CREATE TABLE `t1` (
410 `a` char(30) DEFAULT NULL
411 ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
412 # TABLE: util_test.t2
413 CREATE TABLE `t2` (
414 `a` char(30) DEFAULT NULL
415 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
416 # TABLE: util_test.t3
417 CREATE TABLE `t3` (
418 `a` int(11) NOT NULL AUTO_INCREMENT,
419 `b` char(30) DEFAULT NULL,
420 PRIMARY KEY (`a`)
421 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
422 # TABLE: util_test.t4
423 CREATE TABLE `t4` (
424 `c` int(11) NOT NULL,
425 `d` int(11) NOT NULL,
426 KEY `ref_t3` (`c`),
427 CONSTRAINT `ref_t3` FOREIGN KEY (`c`) REFERENCES `t3` (`a`)
428 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
429 # VIEW: util_test.v1
430 [...]
431 #...done.
432 Similarly, to export the data of the database util_test, producing bulk
434 insert statements, use this command:
435 shell> mysqldbexport --server=root:pass@localhost \
437 --export=DATA --bulk-insert util_test
438 # Source on localhost: ... connected.
439 USE util_test;
440 # Exporting data from util_test
441 # Data for table util_test.t1:
442 INSERT INTO util_test.t1 VALUES ('01 Test Basic database example'),
443 ('02 Test Basic database example'),
444 ('03 Test Basic database example'),
445 ('04 Test Basic database example'),
446 ('05 Test Basic database example'),
447 ('06 Test Basic database example'),
448 ('07 Test Basic database example');
449 # Data for table util_test.t2:
450 INSERT INTO util_test.t2 VALUES ('11 Test Basic database example'),
451 ('12 Test Basic database example'),
452 ('13 Test Basic database example');
453 # Data for table util_test.t3:
454 INSERT INTO util_test.t3 VALUES (1, '14 test fkeys'),
455 (2, '15 test fkeys'),
456 (3, '16 test fkeys');
457 # Data for table util_test.t4:
458 INSERT INTO util_test.t4 VALUES (3, 2);
459 #...done.
460 If the database to be exported does not contain only InnoDB tables and
462 you want to ensure data integrity of the exported data by locking the
463 tables during the read step, add a --locking=lock-all option to the
464 command:
465 shell> mysqldbexport --server=root:pass@localhost \
467 --export=DATA --bulk-insert util_test --locking=lock-all
468 # Source on localhost: ... connected.
469 USE util_test;
470 # Exporting data from util_test
471 # Data for table util_test.t1:
472 INSERT INTO util_test.t1 VALUES ('01 Test Basic database example'),
473 ('02 Test Basic database example'),
474 ('03 Test Basic database example'),
475 ('04 Test Basic database example'),
476 ('05 Test Basic database example'),
477 ('06 Test Basic database example'),
478 ('07 Test Basic database example');
479 # Data for table util_test.t2:
480 INSERT INTO util_test.t2 VALUES ('11 Test Basic database example'),
481 ('12 Test Basic database example'),
482 ('13 Test Basic database example');
483 # Data for table util_test.t3:
484 INSERT INTO util_test.t3 VALUES (1, '14 test fkeys'),
485 (2, '15 test fkeys'),
486 (3, '16 test fkeys');
487 # Data for table util_test.t4:
488 INSERT INTO util_test.t4 VALUES (3, 2);
489 #...done.
490 To export a database and include the replication commands to use the
492 current server as the master (for example, to start a new slave using
493 the current server as the master), use the following command:
494 shell> mysqldbexport --server=root@localhost:3311 util_test \
496 --export=both --rpl-user=rpl:rpl --rpl=master -v
497 # Source on localhost: ... connected.
498 #
499 # Stopping slave
500 STOP SLAVE;
501 #
502 # Source on localhost: ... connected.
503 # Exporting metadata from util_test
504 DROP DATABASE IF EXISTS util_test;
505 CREATE DATABASE util_test;
506 USE util_test;
507 # TABLE: util_test.t1
508 CREATE TABLE `t1` (
509 `a` char(30) DEFAULT NULL
510 ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
511 #...done.
512 # Source on localhost: ... connected.
513 USE util_test;
514 # Exporting data from util_test
515 # Data for table util_test.t1:
516 INSERT INTO util_test.t1 VALUES ('01 Test Basic database example');
517 INSERT INTO util_test.t1 VALUES ('02 Test Basic database example');
518 INSERT INTO util_test.t1 VALUES ('03 Test Basic database example');
519 INSERT INTO util_test.t1 VALUES ('04 Test Basic database example');
520 INSERT INTO util_test.t1 VALUES ('05 Test Basic database example');
521 INSERT INTO util_test.t1 VALUES ('06 Test Basic database example');
522 INSERT INTO util_test.t1 VALUES ('07 Test Basic database example');
523 #...done.
524 #
525 # Connecting to the current server as master
526 CHANGE MASTER TO MASTER_HOST = 'localhost',
527 MASTER_USER = 'rpl',
528 MASTER_PASSWORD = 'rpl',
529 MASTER_PORT = 3311,
530 MASTER_LOG_FILE = 'clone-bin.000001' ,
531 MASTER_LOG_POS = 106;
532 #
533 # Starting slave
534 START SLAVE;
535 #
536 Similarly, to export a database and include the replication commands to
538 use the current server's master (for example, to start a new slave
539 using the same the master), use the following command:
540 shell> mysqldbexport --server=root@localhost:3311 util_test \
542 --export=both --rpl-user=rpl:rpl --rpl=slave -v
543 # Source on localhost: ... connected.
544 #
545 # Stopping slave
546 STOP SLAVE;
547 #
548 # Source on localhost: ... connected.
549 # Exporting metadata from util_test
550 DROP DATABASE IF EXISTS util_test;
551 CREATE DATABASE util_test;
552 USE util_test;
553 # TABLE: util_test.t1
554 CREATE TABLE `t1` (
555 `a` char(30) DEFAULT NULL
556 ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
557 #...done.
558 # Source on localhost: ... connected.
559 USE util_test;
560 # Exporting data from util_test
561 # Data for table util_test.t1:
562 INSERT INTO util_test.t1 VALUES ('01 Test Basic database example');
563 INSERT INTO util_test.t1 VALUES ('02 Test Basic database example');
564 INSERT INTO util_test.t1 VALUES ('03 Test Basic database example');
565 INSERT INTO util_test.t1 VALUES ('04 Test Basic database example');
566 INSERT INTO util_test.t1 VALUES ('05 Test Basic database example');
567 INSERT INTO util_test.t1 VALUES ('06 Test Basic database example');
568 INSERT INTO util_test.t1 VALUES ('07 Test Basic database example');
569 #...done.
570 #
571 # Connecting to the current server's master
572 CHANGE MASTER TO MASTER_HOST = 'localhost',
573 MASTER_USER = 'rpl',
574 MASTER_PASSWORD = 'rpl',
575 MASTER_PORT = 3310,
576 MASTER_LOG_FILE = 'clone-bin.000001' ,
577 MASTER_LOG_POS = 1739;
578 #
579 # Starting slave
580 START SLAVE;
581 #
582 PERMISSIONS REQUIRED.PP The user must have permission to read all
584 databases. Since we are using the root account for these examples (and
585 you typically would), permissions are not generally a problem.
586
588 Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
589 reserved.
590 This documentation is free software; you can redistribute it and/or
592 modify it only under the terms of the GNU General Public License as
593 published by the Free Software Foundation; version 2 of the License.
594 This documentation is distributed in the hope that it will be useful,
596 but WITHOUT ANY WARRANTY; without even the implied warranty of
597 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
598 General Public License for more details.
599 You should have received a copy of the GNU General Public License along
601 with the program; if not, write to the Free Software Foundation, Inc.,
602 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
603 http://www.gnu.org/licenses/.
604
607 For more information, please refer to the MySQL Utilities and Fabric
608 documentation, which is available online at
609 http://dev.mysql.com/doc/index-utils-fabric.html
610
612 Oracle Corporation (http://dev.mysql.com/).
613MySQL 1.5.6 09/15/2015 MYSQLDBEXPORT(1)