.TH "MYSQLDBEXPORT" "1" "September 23, 2011" "1.0.1" "MySQL Utilities"
.SH NAME
mysqldbexport \- Export a list of databases in a variety of formats
.
.nr rst2man-indent-level 0
.
.de1 rstReportMargin
\\$1 \\n[an-margin]
level \\n[rst2man-indent-level]
level margin: \\n[rst2man-indent\\n[rst2man-indent-level]]
-
\\n[rst2man-indent0]
\\n[rst2man-indent1]
\\n[rst2man-indent2]
..
.de1 INDENT
.\" .rstReportMargin pre:
. RS \\$1
. nr rst2man-indent\\n[rst2man-indent-level] \\n[an-margin]
. nr rst2man-indent-level +1
.\" .rstReportMargin post:
..
.de UNINDENT
. RE
.\" indent \\n[an-margin]
.\" old: \\n[rst2man-indent\\n[rst2man-indent-level]]
.nr rst2man-indent-level -1
.\" new: \\n[rst2man-indent\\n[rst2man-indent-level]]
.in \\n[rst2man-indent\\n[rst2man-indent-level]]u
..
.\" Man page generated from reStructeredText.
.
.SH SYNOPSIS
.sp
.nf
.ft C
mysqldbexport \-\-server=<user>[<passwd>]@<host>:[<port>][:<socket>]
            (<db_name>[, <db_name>])+ [\-\-quiet | \-\-help | \-\-no\-headers |
            \-\-skip=(TABLES,TRIGGERS,VIEWS,PROCEDURES,FUNCTIONS,
            EVENTS,GRANTS,DATA,CREATE_DB)* | \-\-skip\-blobs | \-\-help |
            \-\-veerbose | \-\-version | \-\-bulk\-insert | \-\-file\-per\-table |
            \-\-export=[DEFINITIONS|DATA|BOTH] |
            \-\-format=[SQL|S|GRID|G|TAB|T|CSV|C|VERTICAL|V] ] |
            \-\-exclude=<name>[|,\-\-exclude=<name>]
.ft P
.fi
.SH DESCRIPTION
.sp
This utility permits a database administrator to export the metadata
(object definitions, hence definitions) or data or both from one or more
databases. By default, the utility will export only definitions.
.sp
You can also skip objects by type using the \fI\-\-skip\fP option
and list the objects you want to skip. This can allow you to extract a
particular set of objects, say, for exporting only events (by
excluding all other types). Similarly, you can skip creating blob
UPDATE commands by specifying the \fI\-\-skip\-blobs\fP option.
.sp
You also have the choice to view the output in one of the following
formats using the \fI\-\-format\fP option.
.INDENT 0.0
.TP
.B \fBSQL\fP
.sp
Displays the output using SQL statements. For definitions, this is
the appropriate \fBCREATE\fP and \fBGRANT\fP statements. For data, this
is an \fBINSERT\fP statement (or bulk insert if the
\fI\-\-bulk\-insert\fP options is specified).
.TP
.B \fBGRID\fP
.sp
Displays output formatted like that of the mysql monitor in a grid
or table layout.
.TP
.B \fBCSV\fP
.sp
Displays the output in a comma\-separated list.
.TP
.B \fBTAB\fP
.sp
Displays the output in a tab\-separated list.
.TP
.B \fBVERTICAL\fP
.sp
Displays the output in a single column similar to the \fB\eG\fP option
for the mysql monitor commands.
.sp
You also have the option to specify how much data to display in one
of the following displays using the \fI\%--display\fP option.
.TP
.B \fBBRIEF\fP
.sp
Show only the minimal columns for recreating the objects.
.TP
.B \fBFULL\fP
.sp
Show the complete column list for recreating the objects.
.TP
.B \fBNAMES\fP
.sp
Show only the names of the objects.
.UNINDENT
.sp
Note: When combining \fI\-\-format\fP and \fI\%--display\fP, the
\fI\%--display\fP option is ignored for SQL generation.
.sp
You can turn off the headers when using formats CSV and TAB by
specifying the \fI\-\-no\-headers\fP option.
.sp
You can turn off all feedback information by specifying the
\fI\-\-quiet\fP option.
.sp
You can also have the utility write the data for the tables to separate files
by using the \fI\%--file-per-table\fP option. This would create files with a
file name composed of the database and table name followed by the format of the
file. For example, the following command produces files named db1.<table
name>.csv.:
.sp
.nf
.ft C
mysqldbexport \-\-server=root@server1:3306 \-\-format=csv db1 \-\-export=data
.ft P
.fi
.sp
You can exclude specific objects by name using the \fI\-\-exclude\fP option
whereby you specify a name in the form of <db>.<object> or you can supply a
regex search pattern. For example, \fI\-\-exclude=db1.trig1\fP will exclude
the single trigger and \fI\-\-exclude=trig_\fP will exclude all objects from
all databases whose name begins with trig and has a following character or
digit.
.sp
This utility differs from mysqldump in that it can produce output in a
variety of formats to make your data extraction/transport much easier. It
permits you to export your data in the format most suitable to an external
tool, another MySQL server, or a yet another use without the need to
reformat the data.
.sp
You must provide login information such as user, host, password, etc. for a
user that has the appropriate rights to access all objects in the operation.
See \fI\%NOTES\fP below for more details.
.SH OPTIONS
.INDENT 0.0
.TP
.B \-\-version
.
show program\(aqs version number and exit
.UNINDENT
.INDENT 0.0
.TP
.B \-\-help
.UNINDENT
.INDENT 0.0
.TP
.B \-\-server <server>
.sp
connection information for the server in the form:
<user>:<password>@<host>:<port>:<socket>
.UNINDENT
.INDENT 0.0
.TP
.B \-\-format <format>, \-f <format>
.sp
display the output in either SQL|S (default), GRID|G, TAB|T, CSV|C,
or VERTICAL|V format
.UNINDENT
.INDENT 0.0
.TP
.B \-\-display <display>, \-d <display>
.sp
control the number of columns shown: BRIEF = minimal columns for
object creation (default), FULL = all columns, NAMES = only object
names (not valid for \-\-format=SQL)
.UNINDENT
.INDENT 0.0
.TP
.B \-\-export <export>, \-e <export>
.sp
control the export of either DATA|D = only the table data for the
tables in the database list, DEFINITIONS|F = export only the
definitions for the objects in the database list, or BOTH|B =
export the metadata followed by the data (default: export metadata)
.UNINDENT
.INDENT 0.0
.TP
.B \-\-bulk\-insert, \-b
.sp
Use bulk insert statements for data (default:False)
.UNINDENT
.INDENT 0.0
.TP
.B \-\-file\-per\-table
.
Write table data to separate files. Valid only for \fI\-\-export=data\fP
or \fI\-\-export=both\fP. Files will be named
<db_name>.<tbl_name>.<format>. For example, a CSV export of two tables in
db1, t1 and t2, results in files named db1.t1.csv and db1.t2.csv. If
definitions are included, they are written to stdout as normal.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-no\-headers, \-h
.sp
do not display the column headers \- ignored for GRID format
.UNINDENT
.INDENT 0.0
.TP
.B \-\-quiet, \-q
.sp
turn off all messages for quiet execution
.UNINDENT
.INDENT 0.0
.TP
.B \-\-verbose, \-v
.sp
control how much information is displayed. For example, \-v =
verbose, \-vv = more verbose, \-vvv = debug
.UNINDENT
.INDENT 0.0
.TP
.B \-\-skip <skip\-objects>
.sp
specify objects to skip in the operation in the form of a
comma\-separated list (no spaces). Valid values = TABLES, VIEWS,
TRIGGERS, PROCEDURES, FUNCTIONS, EVENTS, GRANTS, DATA, CREATE_DB
.UNINDENT
.INDENT 0.0
.TP
.B \-x EXCLUDE \-\-exclude=EXCLUDE
.sp
exclude one or more objects from the operation using either a specific name
such as db1.t1 or a REGEXP search pattern. Repeat option for multiple
exclusions.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-skip\-blobs
.
Do not export blob data.
.UNINDENT
.SH NOTES
.sp
The login user must have the appropriate permissions to create new
objects, read the old database, access (read) the mysql database, and
grant privileges.
.sp
To export all objects from a source, the user must have \fBSELECT\fP and
\fBSHOW VIEW\fP privileges on the database as well as \fBSELECT\fP on the
mysql database.
.sp
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 turned on (hence the need for \fBSUPER\fP).
.sp
Some combinations of the options may result in errors during the operation.
For example, eliminating tables but not views may result in an error when the
view is imported on another server.
.sp
The \fI\-\-exclude\fP option does not apply to grants.
.SH EXAMPLES
.sp
To export the definitions of the database \(aqdev\(aq from a MySQL server on
localhast via port 3306 producing \fBCREATE\fP statements, use this command:
.sp
.nf
.ft C
$ mysqldbexport \-\-server=root:pass@localhost \e\e
  \-\-skip=GRANTS \-\-export=DEFINITIONS util_test
# Source on localhost: ... connected.
# Exporting metadata from util_test
DROP DATABASE IF EXISTS util_test;
CREATE DATABASE util_test;
USE util_test;
# TABLE: util_test.t1
CREATE TABLE \(gat1\(ga (
  \(gaa\(ga char(30) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
# TABLE: util_test.t2
CREATE TABLE \(gat2\(ga (
  \(gaa\(ga char(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# TABLE: util_test.t3
CREATE TABLE \(gat3\(ga (
  \(gaa\(ga int(11) NOT NULL AUTO_INCREMENT,
  \(gab\(ga char(30) DEFAULT NULL,
  PRIMARY KEY (\(gaa\(ga)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
# TABLE: util_test.t4
CREATE TABLE \(gat4\(ga (
  \(gac\(ga int(11) NOT NULL,
  \(gad\(ga int(11) NOT NULL,
  KEY \(garef_t3\(ga (\(gac\(ga),
  CONSTRAINT \(garef_t3\(ga FOREIGN KEY (\(gac\(ga) REFERENCES \(gat3\(ga (\(gaa\(ga)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# VIEW: util_test.v1
[...]
#...done.
.ft P
.fi
.sp
Similarly, to export the data of the database \(aqutil_test\(aq producing bulk
insert statements, use this command:
.sp
.nf
.ft C
$ mysqldbexport \-\-server=root:pass@localhost \e\e
  \-\-export=DATA \-\-bulk\-insert util_test
# Source on localhost: ... connected.
USE util_test;
# Exporting data from util_test
# Data for table util_test.t1:
INSERT INTO util_test.t1 VALUES  (\(aq01 Test Basic database example\(aq),
  (\(aq02 Test Basic database example\(aq),
  (\(aq03 Test Basic database example\(aq),
  (\(aq04 Test Basic database example\(aq),
  (\(aq05 Test Basic database example\(aq),
  (\(aq06 Test Basic database example\(aq),
  (\(aq07 Test Basic database example\(aq);
# Data for table util_test.t2:
INSERT INTO util_test.t2 VALUES  (\(aq11 Test Basic database example\(aq),
  (\(aq12 Test Basic database example\(aq),
  (\(aq13 Test Basic database example\(aq);
# Data for table util_test.t3:
INSERT INTO util_test.t3 VALUES  (1, \(aq14 test fkeys\(aq),
  (2, \(aq15 test fkeys\(aq),
  (3, \(aq16 test fkeys\(aq);
# Data for table util_test.t4:
INSERT INTO util_test.t4 VALUES  (3, 2);
#...done.
.ft P
.fi
.SH COPYRIGHT
.sp
Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
.sp
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.
.sp
This program 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.
.sp
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110\-1301 USA
.SH AUTHOR
MySQL Utilities Team
.SH COPYRIGHT
2010, Oracle and/or its affiliates. All rights reserved.
.\" Generated by docutils manpage writer.
.\" 
.