.TH "MYSQLDBEXPORT" "1" "May 09, 2012" "1.0.3" "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 [options] db_name ...
.ft P
.fi
.SH DESCRIPTION
.sp
This utility exports metadata (object definitions) or data or both from one
or more databases. By default, the export includes only definitions.
.sp
\fBmysqldbexport\fP differs from \fBmysqldump\fP 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 other use
without the need to reformat the data.
.sp
To exclude specific objects by name, use the \fI\%--exclude\fP option with
a name in \fIdb\fP.*obj* format, or you can supply a search pattern. For example,
\fI\-\-exclude=db1.trig1\fP excludes the single trigger and
\fI\-\-exclude=trig_\fP excludes all objects from all databases having a
name that begins with \fBtrig\fP and has a following character.
.sp
To skip objects by type, use the \fI\-\-skip\fP option
with a list of the objects to skip. This enables you to extract a
particular set of objects, say, for exporting only events (by
excluding all other types). Similarly, to skip creation of \fBUPDATE\fP
statements for \fBBLOB\fP data, specify the \fI\-\-skip\-blobs\fP option.
.sp
To specify how to display output, use one of the following values
with the \fI\-\-format\fP option:
.INDENT 0.0
.TP
.B \fBsql\fP (default)
Display output using SQL statements. For definitions, this consists of
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 option is specified).
.TP
.B \fBgrid\fP
Display output in grid or table format like that of the
\fBmysql\fP monitor.
.TP
.B \fBcsv\fP
Display output in comma\-separated values format.
.TP
.B \fBtab\fP
Display output in tab\-separated format.
.TP
.B \fBvertical\fP
Display output in single\-column format like that of the \fB\eG\fP command
for the \fBmysql\fP monitor.
.UNINDENT
.sp
To specify how much data to display, use one of the following values
with the \fI\%--display\fP option:
.INDENT 0.0
.TP
.B \fBbrief\fP
Display only the minimal columns for recreating the objects.
.TP
.B \fBfull\fP
Display the complete column list for recreating the objects.
.TP
.B \fBnames\fP
Display only the object names.
.UNINDENT
.sp
Note: For SQL\-format output, the \fI\%--display\fP option is ignored.
.sp
To turn off the headers for \fBcsv\fP or \fBtab\fP display format, specify
the \fI\-\-no\-headers\fP option.
.sp
To turn off all feedback information, specify the \fI\-\-quiet\fP option.
.sp
To write the data for individual tables to separate files, use the
\fI\%--file-per-table\fP option. The name of each file is composed of the
database and table names followed by the file format.  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
By default, the operation uses a consistent snapshot to read the source
databases. To change the locking mode, use the \fI\%--locking\fP option
with a locking type value.  Use a value of \fBno\-locks\fP to turn off locking
altogether or \fBlock\-all\fP to use only table locks. The default value is
\fBsnapshot\fP. Additionally, the utility uses WRITE locks to lock the
destination tables during the copy.
.sp
You can include replication statements for exporting data among a master and
slave or between slaves. The \fI\%--rpl\fP option permits you to select
from the following replication statements to include in the export.
.INDENT 0.0
.TP
.B \fBmaster\fP
Include the \fBCHANGE MASTER\fP statement to start a new slave with the current
server acting as the master. This places the appropriate STOP and
START slave statements in the export whereby the \fBSTOP SLAVE\fP statement is
placed at the start of the export and the \fBCHANGE MASTER\fP followed by the
\fBSTART SLAVE\fP statements are placed after the export stream.
.TP
.B \fBslave\fP
Include the \fBCHANGE MASTER\fP statement to start a new slave using the
current server\(aqs master information. This places the appropriate STOP and
START slave statements in the export whereby the \fBSTOP SLAVE\fP statment is
placed at the start of the export and the \fBCHANGE MASTER\fP followed by
the \fBSTART SLAVE\fP statements are placed after the export stream.
.TP
.B \fBboth\fP
Include both the \(aqmaster\(aq and \(aqslave\(aq information for \fBCHANGE MASTER\fP
statements for either spawning a new slave with the current server\(aqs master
or using the current server as the master. All statements generated are
labeled and commented to enable the user to choose which to include when
imported.
.UNINDENT
.sp
To include the replication user in the \fBCHANGE MASTER\fP statement,
use the \fI\-\-rpl\-user\fP option to specify the user and password. If
this option is omitted, the utility attempts to identify the replication
user. In the event that there are multiple candidates or the user requires a
password, these statements are placed inside comments for the \fBCHANGE
MASTER\fP statement.
.sp
You can also use the \fI\%--comment-rpl\fP option to place the replication
statements inside comments for later examination.
.sp
If you specify the \fI\%--rpl-file\fP option, the utility writes the
replication statements to the file specified instead of including them
in the export stream.
.SH OPTIONS
.sp
\fBmysqldbexport\fP accepts the following command\-line options:
.INDENT 0.0
.TP
.B \-\-help
Display a help message and exit.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-bulk\-insert, \-b
Use bulk insert statements for data.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-comment\-rpl
Place the replication statements in comment statements. Valid only with
the \fI\%--rpl\fP option.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-display=<display>, \-d<display>
Control the number of columns shown. Permitted display values are \fBbrief\fP
(minimal columns for object creation), \fBfull* (all columns), and **names\fP
(only object names; not valid for \fI\-\-format=sql\fP). The default is
\fBbrief\fP.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-exclude=<exclude>, \-x<exclude>
Exclude one or more objects from the operation using either a specific name
such as \fBdb1.t1\fP or a search pattern.  Use this option multiple times
to specify multiple exclusions. By default, patterns use \fBLIKE\fP matching.
With the \fI\-\-regexp\fP option, patterns use \fBREGEXP\fP matching.
.sp
This option does not apply to grants.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-export=<export>, \-e<export>
Specify the export format. Permitted format values are \fBdefinitions\fP =
export only the definitions (metadata) for the objects in the database list,
\fBdata\fP = export only the table data for the tables in the database list,
and \fBboth\fP = export the definitions and the data. The default is
\fBdefinitions\fP.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-file\-per\-table
Write table data to separate files. This is Valid only if the export
output includes data (that is, if \fI\-\-export=data\fP
or \fI\-\-export=both\fP are given). This option produces files named
\fIdb_name\fP.*tbl_name*.*format*. For example, a \fBcsv\fP export of two tables
named \fBt1\fP and \fBt2\fP in database \fBd1\fP, results in files named
\fBdb1.t1.csv\fP and \fBdb1.t2.csv\fP. If table definitions are included in the
export, they are written to stdout as usual.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-format=<format>, \-f<format>
Specify the output display format. Permitted format values are
\fBsql\fP, \fBgrid\fP, \fBtab\fP, \fBcsv\fP, and \fBvertical\fP. The default is
\fBsql\fP.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-locking=<locking>
Choose the lock type for the operation. Permitted lock values are
\fBno\-locks\fP (do not use any table locks), \fBlock\-all\fP (use table locks
but no transaction and no consistent read), and \fBsnapshot\fP (consistent
read using a single transaction). The default is \fBsnapshot\fP.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-no\-headers, \-h
Do not display column headers. This option applies only for \fBcsv\fP and
\fBtab\fP output.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-quiet, \-q
Turn off all messages for quiet execution.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-regexp, \-\-basic\-regexp, \-G
Perform pattern matches using the \fBREGEXP\fP operator. The default is
to use \fBLIKE\fP for matching.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-rpl=<dump_option>, \-\-replication=<dump_option>
Include replication information. Permitted values are \fBmaster\fP (include
the \fBCHANGE MASTER\fP statement using the source server as the master),
\fBslave\fP (include the \fBCHANGE MASTER\fP statement using the destination
server\(aqs master information), and \fBboth\fP (include the \fBmaster\fP and
\fBslave\fP options where applicable).
.UNINDENT
.INDENT 0.0
.TP
.B \-\-rpl\-file=RPL_FILE, \-\-replication\-file=RPL_FILE
The path and file name where the generated replication information should
be written. Valid only with the \fI\%--rpl\fP option.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-rpl\-user=<user[:password]>
The user and password for the replication user requirement; for example,
\fBrpl:passwd\fP. The default is \fBrpl:rpl\fP.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-server=<server>
Connection information for the server in
<\fIuser\fP>[:<\fIpasswd\fP>]@<\fIhost\fP>[:<\fIport\fP>][:<\fIsocket\fP>] format.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-skip=<skip\-objects>
Specify objects to skip in the operation as a comma\-separated list
(no spaces). Permitted values are \fBCREATE_DB\fP, \fBDATA\fP, \fBEVENTS\fP,
\fBFUNCTIONS\fP, \fBGRANTS\fP, \fBPROCEDURES\fP, \fBTABLES\fP, \fBTRIGGERS\fP,
and \fBVIEWS\fP.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-skip\-blobs
Do not export \fBBLOB\fP data.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-verbose, \-v
Specify how much information to display. Use this option
multiple times to increase the amount of information.  For example,
\fI\-v\fP = verbose, \fI\-vv\fP = more verbose, \fI\-vvv\fP =
debug.
.UNINDENT
.INDENT 0.0
.TP
.B \-\-version
Display version information and exit.
.UNINDENT
.SH NOTES
.sp
You must provide connection parameters (user, host, password, and
so forth) for an account that has the appropriate privileges to
access all objects in the operation.
.sp
To export all objects from a source database, the user must have these
privileges: \fBSELECT\fP and \fBSHOW VIEW\fP on the database as well as
\fBSELECT\fP on the \fBmysql\fP 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.
.sp
Some combinations of the options may result in errors when the export is
imported later. For example, eliminating tables but not views may result in
an error when a view is imported on another server.
.sp
For the \fI\-\-format\fP, \fI\%--export\fP, and \fI\%--display\fP
options, the permitted values are not case sensitive. In addition, values
may be specified as any unambiguous prefix of a valid value.  For example,
\fI\-\-format=g\fP specifies the grid format. An error occurs if a
prefix matches more than one valid value.
.SH EXAMPLES
.sp
To export the definitions of the database \fBdev\fP from a MySQL server on the
local host via port 3306, producing output consisting of \fBCREATE\fP
statements, use this command:
.sp
.nf
.ft C
$ mysqldbexport \-\-server=root:pass@localhost \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 \fButil_test\fP, producing bulk
insert statements, use this command:
.sp
.nf
.ft C
$ mysqldbexport \-\-server=root:pass@localhost \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
.sp
If the database to be exported does not contain only InnoDB tables and you
want to ensure data integrity of the exported data by locking the tables
during the read step, add a \fI\-\-locking=lock\-all\fP option to the
command:
.sp
.nf
.ft C
$ mysqldbexport \-\-server=root:pass@localhost \e
  \-\-export=DATA \-\-bulk\-insert util_test \-\-locking=lock\-all
# 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
.sp
To export a database and include the replication commands to use
the current server as the master (for example, to start a new slave using the
current server as the master), use the following command:
.sp
.nf
.ft C
$ mysqldbexport \-\-server=root@localhost:3311 util_test \e
  \-\-export=both \-\-rpl\-user=rpl:rpl \-\-rpl=master \-v
# Source on localhost: ... connected.
#
# Stopping slave
STOP SLAVE;
#
# 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;
#...done.
# 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);
INSERT INTO util_test.t1 VALUES (\(aq02 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq03 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq04 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq05 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq06 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq07 Test Basic database example\(aq);
#...done.
#
# Connecting to the current server as master
CHANGE MASTER TO MASTER_HOST = \(aqlocalhost\(aq,
  MASTER_USER = \(aqrpl\(aq,
  MASTER_PASSWORD = \(aqrpl\(aq,
  MASTER_PORT = 3311,
  MASTER_LOG_FILE = \(aqclone\-bin.000001\(aq ,
  MASTER_LOG_POS = 106;
#
# Starting slave
START SLAVE;
#
.ft P
.fi
.sp
Similarly, to export a database and include the replication
commands to use the current server\(aqs master (for example, to start a new
slave using the same the master), use the following command:
.sp
.nf
.ft C
$ mysqldbexport \-\-server=root@localhost:3311 util_test \e
  \-\-export=both \-\-rpl\-user=rpl:rpl \-\-rpl=slave \-v
# Source on localhost: ... connected.
#
# Stopping slave
STOP SLAVE;
#
# 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;
#...done.
# 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);
INSERT INTO util_test.t1 VALUES (\(aq02 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq03 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq04 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq05 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq06 Test Basic database example\(aq);
INSERT INTO util_test.t1 VALUES (\(aq07 Test Basic database example\(aq);
#...done.
#
# Connecting to the current server\(aqs master
CHANGE MASTER TO MASTER_HOST = \(aqlocalhost\(aq,
  MASTER_USER = \(aqrpl\(aq,
  MASTER_PASSWORD = \(aqrpl\(aq,
  MASTER_PORT = 3310,
  MASTER_LOG_FILE = \(aqclone\-bin.000001\(aq ,
  MASTER_LOG_POS = 1739;
#
# Starting slave
START SLAVE;
#
.ft P
.fi
.SH COPYRIGHT
.sp
Copyright (c) 2010, 2012, 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.
.\" 
.