sql (默认)以SQL语句方式输出。对于对象定义，以适当的 CREATE 和 GRANT 语句。对于数据，以 INSERT 语句。(或者批量插入 --bulk-insert指定该选项。.
grid网格化输出，类似于mysql命令行客户端输出
csv逗号分隔格式输出
tab制表符格式输出
vertical类似于mysql命令行客户端\G执行的输出

指定要显示多少数据，可以使用 --display 选项：

brief只显示重建对象的最小列
full完整显示重建对象的列列表
names只显示对象名称

注意：对于SQL格式输出，--display选项被忽略。

指定 --no-headers 选项，关闭CSV或tab显示对象的格式头部。

指定--quiet选项，关闭所有反馈信息。

指定 --file-per-table选项，每个表数据单独保存。每个文件的名称以数据库和表名称组成。格式：db1.table_name.csv。

默认情况下，复制操作是使用一致性快照来读取源数据库。要改变锁定模式，可以使用–locking选项来指定锁定类型值。值no-locks关闭锁，lock-all只使用表锁。默认是snapshot。此外，使用WRITE锁，在复制过程中将锁定目标表。

从主或者从服务器导数据还可以包含复制语句。–rpl选项指定

master包含 CHANGE MASTER 语句，使目标服务器作为指定--server选项的从。在导数据之前，执行 STOP SLAVE 语句。在完成后执行 CHANGE MASTER 和 START SLAVE语句。slave包含 CHANGE MASTER 语句，使目标服务器成为与–server选项指定的服务器的同一个主服务器的从。只对当前服务器是从有效。
both同时包含 'master' 和 'slave' 信息的 CHANGE MASTER 语句，可以成为当前服务器的新从，也可以成为主。

–repl-user选项指定复制的用户名和密码。如果指定了--rpl-file选项，复制语句信息写入到文件中，而不是输出流。--comment-rpl选项，注释掉CHANGE MASTER 语句。

如果要导出的数据库的服务器上启用了GTIDs（GTID_MODE = ON），如果只导出其中一部分数据库，将会有警告信息产生。这是因为GTID报表生成包括所有数据库的gtids，不仅仅是某个的。

如果有启用GTID，但是使用了–skip-gtid也会收到警告。

如果启用了GTID，最好是导出或导入所有的数据库。

从另一台服务器上导入这些导出的数据，需要确保这些数据以及日志中所有的GTIDs正确记录。

选项

  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --ssl-ca=SSL_CA       The path to a file that contains a list of trusted SSL
                        CAs.
  --ssl-cert=SSL_CERT   The name of the SSL certificate file to use for
                        establishing a secure connection.
  --ssl-key=SSL_KEY     The name of the SSL key file to use for establishing a
                        secure connection.
  --character-set=CHARSET
                        sets the client character set. The default is
                        retrieved from the server variable
                        'character_set_client'.
  -f FORMAT, --format=FORMAT
                        display the output in either sql (default), grid, tab,
                        csv, or vertical format
  -d DISPLAY, --display=DISPLAY
                        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)
  -e EXPORT, --export=EXPORT
                        control the export of either 'data' = only the table
                        data for the tables in the database list,
                        'definitions' = export only the definitions for the
                        objects in the database list, or 'both' = export the
                        metadata followed by the data (default: export
                        definitions)
  -b, --bulk-insert     use bulk insert statements for data (default:False)
  -h, --no-headers      do not show column headers (only applies to formats:
                        tab, csv).
  --skip-blobs          do not export blob data.
  --file-per-table      write table data to separate files. Valid only for
                        --export=data or --export=both.
  -x EXCLUDE, --exclude=EXCLUDE
                        exclude one or more objects from the operation using
                        either a specific name (e.g. db1.t1), a LIKE pattern
                        (e.g. db1.t% or db%.%) or a REGEXP search pattern. To
                        use a REGEXP search pattern for all exclusions, you
                        must also specify the --regexp option. Repeat the
                        --exclude option for multiple exclusions.
  -a, --all             include all databases
  --skip=SKIP_OBJECTS   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
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  -G, --basic-regexp, --regexp
                        use 'REGEXP' operator to match pattern. Default is to
                        use 'LIKE'.
  --locking=LOCKING     choose the lock type for the operation: no-locks = do
                        not use any table locks, lock-all = use table locks
                        but no transaction and no consistent read, snaphot
                        (default): consistent read using a single transaction.
  --rpl-user=RPL_USER   the user and password for the replication user
                        requirement, in the form: <user>[:<password>] or
                        <login-path>. E.g. rpl:passwd
  --rpl=RPL_MODE, --replication=RPL_MODE
                        include replication information. Choices: 'master' =
                        include the CHANGE MASTER command using the source
                        server as the master, 'slave' = include the CHANGE
                        MASTER command for the source server's master (only
                        works if the source server is a slave), and 'both' =
                        include 'master' and 'slave' options where applicable.
  --rpl-file=RPL_FILE, --replication-file=RPL_FILE
                        path and file name to place the replication
                        information generated. Valid on if the --rpl option is
                        specified.
  --skip-gtid           skip creation of GTID_PURGED statements.
  --comment-rpl         place the replication statements in comment
                        statements. Valid only with --rpl option.
  --skip-fkey-checks    skip creation of foreign key disable/enable
                        statements.
  --multiprocess=MULTIPROCESS
                        use multiprocessing, number of processes to use for
                        concurrent execution. Special values: 0 (number of
                        processes equal to the CPUs detected) and 1 (default -
                        no concurrency).
  --output-file=OUTPUT_FILE
                        path and file name to store the generated output, by
                        default the standard output (no file).

--version show program's version number and exit

--help display a help message and exit

--license display program's license and exit

--server=SERVER connection information for the server in the form:

<user>[:<password>]@<host>[:<port>][:<socket>] or

<login-path>[:<port>][:<socket>] or <config-

path>[<[group]>].

--ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL

CAs.

--ssl-cert=SSL_CERT The name of the SSL certificate file to use for

establishing a secure connection.

--ssl-key=SSL_KEY The name of the SSL key file to use for establishing a

secure connection.

--character-set=CHARSET

sets the client character set. The default is

retrieved from the server variable

'character_set_client'.

-f FORMAT, --format=FORMAT

display the output in either sql (default), grid, tab,

csv, or vertical format

-d DISPLAY, --display=DISPLAY

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)

-e EXPORT, --export=EXPORT

control the export of either 'data' = only the table

data for the tables in the database list,

'definitions' = export only the definitions for the

objects in the database list, or 'both' = export the

metadata followed by the data (default: export

definitions)

-b, --bulk-insert use bulk insert statements for data (default:False)

-h, --no-headers do not show column headers (only applies to formats:

tab, csv).

--skip-blobs do not export blob data.

--file-per-table write table data to separate files. Valid only for

--export=data or --export=both.

-x EXCLUDE, --exclude=EXCLUDE

exclude one or more objects from the operation using

either a specific name (e.g. db1.t1), a LIKE pattern

(e.g. db1.t% or db%.%) or a REGEXP search pattern. To

use a REGEXP search pattern for all exclusions, you

must also specify the --regexp option. Repeat the

--exclude option for multiple exclusions.

-a, --all include all databases

--skip=SKIP_OBJECTS 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

-v, --verbose control how much information is displayed. e.g., -v =

verbose, -vv = more verbose, -vvv = debug

-q, --quiet turn off all messages for quiet execution.

-G, --basic-regexp, --regexp

use 'REGEXP' operator to match pattern. Default is to

use 'LIKE'.

--locking=LOCKING choose the lock type for the operation: no-locks = do

not use any table locks, lock-all = use table locks

but no transaction and no consistent read, snaphot

(default): consistent read using a single transaction.

--rpl-user=RPL_USER the user and password for the replication user

requirement, in the form: <user>[:<password>] or

<login-path>. E.g. rpl:passwd

--rpl=RPL_MODE, --replication=RPL_MODE

include replication information. Choices: 'master' =

include the CHANGE MASTER command using the source

server as the master, 'slave' = include the CHANGE

MASTER command for the source server's master (only

works if the source server is a slave), and 'both' =

include 'master' and 'slave' options where applicable.

--rpl-file=RPL_FILE, --replication-file=RPL_FILE

path and file name to place the replication

information generated. Valid on if the --rpl option is

specified.

--skip-gtid skip creation of GTID_PURGED statements.

--comment-rpl place the replication statements in comment

statements. Valid only with --rpl option.

--skip-fkey-checks skip creation of foreign key disable/enable

statements.

--multiprocess=MULTIPROCESS

use multiprocessing, number of processes to use for

concurrent execution. Special values: 0 (number of

processes equal to the CPUs detected) and 1 (default -

no concurrency).

--output-file=OUTPUT_FILE

path and file name to store the generated output, by

default the standard output (no file).

必需提供连接参数和赋予要访问对象的适当权限。

从源服务器上导出所有对象，必须要有：SELECT、SHOW VIEW权限，同时还需要有mysql数据库的SELECT权限。实际所需的权限可能会有所不同，以实际情况为准。

对于 --format, --export 和 --display 选项，参数值不区分大小写，但是不能参数值钱不能包含空格。

实例

只导出定义语句

#mysqldbexport --server=instance_3306  --format=sql  ttlsa_com --export=definitions   
# Source on localhost: ... connected.
# Exporting metadata from ttlsa_com
DROP DATABASE IF EXISTS `ttlsa_com`;
CREATE DATABASE `ttlsa_com`;
USE `ttlsa_com`;
# TABLE: ttlsa_com.bbs_categories
CREATE TABLE `bbs_categories` (
  `cid` smallint(5) NOT NULL AUTO_INCREMENT,
  `pid` smallint(5) NOT NULL DEFAULT '0',
  `cname` varchar(30) DEFAULT NULL COMMENT '分类名称',
  `content` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `ico` varchar(128) DEFAULT NULL,
  `master` varchar(100) NOT NULL,
  `permit` varchar(255) DEFAULT NULL,
  `listnum` mediumint(8) unsigned DEFAULT '0',
  `clevel` varchar(25) DEFAULT NULL,
  `cord` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`cid`,`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_comments
CREATE TABLE `bbs_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `content` text,
  `replytime` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`fid`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_favorites
CREATE TABLE `bbs_favorites` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`id`,`uid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_forums
CREATE TABLE `bbs_forums` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `cid` smallint(5) NOT NULL DEFAULT '0',
  `uid` mediumint(8) NOT NULL DEFAULT '0',
  `ruid` mediumint(8) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `content` text,
  `addtime` int(10) DEFAULT NULL,
  `updatetime` int(10) DEFAULT NULL,
  `lastreply` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `comments` smallint(8) DEFAULT '0',
  `favorites` int(10) unsigned DEFAULT '0',
  `closecomment` tinyint(1) DEFAULT NULL,
  `is_top` tinyint(1) NOT NULL DEFAULT '0',
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `ord` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`,`cid`,`uid`),
  KEY `updatetime` (`updatetime`),
  KEY `ord` (`ord`)
) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_links
CREATE TABLE `bbs_links` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `logo` varchar(200) DEFAULT NULL,
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_notifications
CREATE TABLE `bbs_notifications` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) DEFAULT NULL,
  `suid` int(11) DEFAULT NULL,
  `nuid` int(11) NOT NULL DEFAULT '0',
  `ntype` tinyint(1) DEFAULT NULL,
  `ntime` int(10) DEFAULT NULL,
  PRIMARY KEY (`nid`,`nuid`)
) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_page
CREATE TABLE `bbs_page` (
  `pid` tinyint(6) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `content` text,
  `go_url` varchar(100) DEFAULT NULL,
  `add_time` int(10) DEFAULT NULL,
  `is_hidden` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_settings
CREATE TABLE `bbs_settings` (
  `id` tinyint(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  `type` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`title`,`type`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_tags
CREATE TABLE `bbs_tags` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag_title` varchar(30) NOT NULL,
  `forums` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_title` (`tag_title`)
) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_tags_relation
CREATE TABLE `bbs_tags_relation` (
  `tag_id` int(10) NOT NULL DEFAULT '0',
  `fid` int(10) DEFAULT NULL,
  KEY `tag_id` (`tag_id`),
  KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_users
CREATE TABLE `bbs_users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `openid` char(32) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `homepage` varchar(50) DEFAULT NULL,
  `money` int(11) DEFAULT '100',
  `signature` text,
  `forums` int(11) DEFAULT '0',
  `replies` int(11) DEFAULT '0',
  `notices` smallint(5) DEFAULT '0',
  `follows` int(11) NOT NULL DEFAULT '0',
  `regtime` int(10) DEFAULT NULL,
  `lastlogin` int(10) DEFAULT NULL,
  `lastpost` int(10) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `gid` tinyint(3) NOT NULL DEFAULT '3',
  `ip` char(15) DEFAULT NULL,
  `location` varchar(128) DEFAULT NULL,
  `token` varchar(40) DEFAULT NULL,
  `introduction` text,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_user_follow
CREATE TABLE `bbs_user_follow` (
  `follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `follow_uid` int(10) unsigned NOT NULL DEFAULT '0',
  `addtime` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_user_groups
CREATE TABLE `bbs_user_groups` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `group_name` varchar(50) DEFAULT NULL,
  `usernum` int(11) NOT NULL,
  PRIMARY KEY (`gid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.data
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
# TABLE: ttlsa_com.t_data
CREATE TABLE `t_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#...done.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

#mysqldbexport --server=instance_3306 --format=sql ttlsa_com --export=definitions

# Source on localhost: ... connected.

# Exporting metadata from ttlsa_com

DROP DATABASE IF EXISTS `ttlsa_com`;

CREATE DATABASE `ttlsa_com`;

USE `ttlsa_com`;

# TABLE: ttlsa_com.bbs_categories

CREATE TABLE `bbs_categories` (

`cid` smallint(5) NOT NULL AUTO_INCREMENT,

`pid` smallint(5) NOT NULL DEFAULT '0',

`cname` varchar(30) DEFAULT NULL COMMENT '分类名称',

`content` varchar(255) DEFAULT NULL,

`keywords` varchar(255) DEFAULT NULL,

`ico` varchar(128) DEFAULT NULL,

`master` varchar(100) NOT NULL,

`permit` varchar(255) DEFAULT NULL,

`listnum` mediumint(8) unsigned DEFAULT '0',

`clevel` varchar(25) DEFAULT NULL,

`cord` smallint(6) DEFAULT NULL,

PRIMARY KEY (`cid`,`pid`)

) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_comments

CREATE TABLE `bbs_comments` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`fid` int(11) NOT NULL DEFAULT '0',

`uid` int(11) NOT NULL DEFAULT '0',

`content` text,

`replytime` char(10) DEFAULT NULL,

PRIMARY KEY (`id`,`fid`,`uid`)

) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_favorites

CREATE TABLE `bbs_favorites` (

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`uid` mediumint(8) unsigned NOT NULL DEFAULT '0',

`favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',

`content` mediumtext NOT NULL,

PRIMARY KEY (`id`,`uid`),

KEY `uid` (`uid`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_forums

CREATE TABLE `bbs_forums` (

`fid` int(11) NOT NULL AUTO_INCREMENT,

`cid` smallint(5) NOT NULL DEFAULT '0',

`uid` mediumint(8) NOT NULL DEFAULT '0',

`ruid` mediumint(8) DEFAULT NULL,

`title` varchar(128) DEFAULT NULL,

`keywords` varchar(255) DEFAULT NULL,

`content` text,

`addtime` int(10) DEFAULT NULL,

`updatetime` int(10) DEFAULT NULL,

`lastreply` int(10) DEFAULT NULL,

`views` int(10) DEFAULT '0',

`comments` smallint(8) DEFAULT '0',

`favorites` int(10) unsigned DEFAULT '0',

`closecomment` tinyint(1) DEFAULT NULL,

`is_top` tinyint(1) NOT NULL DEFAULT '0',

`is_hidden` tinyint(1) NOT NULL DEFAULT '0',

`ord` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`fid`,`cid`,`uid`),

KEY `updatetime` (`updatetime`),

KEY `ord` (`ord`)

) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_links

CREATE TABLE `bbs_links` (

`id` smallint(6) NOT NULL AUTO_INCREMENT,

`name` varchar(100) DEFAULT NULL,

`url` varchar(200) DEFAULT NULL,

`logo` varchar(200) DEFAULT NULL,

`is_hidden` tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_notifications

CREATE TABLE `bbs_notifications` (

`nid` int(11) NOT NULL AUTO_INCREMENT,

`fid` int(11) DEFAULT NULL,

`suid` int(11) DEFAULT NULL,

`nuid` int(11) NOT NULL DEFAULT '0',

`ntype` tinyint(1) DEFAULT NULL,

`ntime` int(10) DEFAULT NULL,

PRIMARY KEY (`nid`,`nuid`)

) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_page

CREATE TABLE `bbs_page` (

`pid` tinyint(6) NOT NULL AUTO_INCREMENT,

`title` varchar(100) DEFAULT NULL,

`content` text,

`go_url` varchar(100) DEFAULT NULL,

`add_time` int(10) DEFAULT NULL,

`is_hidden` tinyint(1) DEFAULT '0',

PRIMARY KEY (`pid`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_settings

CREATE TABLE `bbs_settings` (

`id` tinyint(5) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL DEFAULT '',

`value` text NOT NULL,

`type` tinyint(3) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`,`title`,`type`)

) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_tags

CREATE TABLE `bbs_tags` (

`tag_id` int(10) NOT NULL AUTO_INCREMENT,

`tag_title` varchar(30) NOT NULL,

`forums` int(10) NOT NULL DEFAULT '0',

PRIMARY KEY (`tag_id`),

UNIQUE KEY `tag_title` (`tag_title`)

) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_tags_relation

CREATE TABLE `bbs_tags_relation` (

`tag_id` int(10) NOT NULL DEFAULT '0',

`fid` int(10) DEFAULT NULL,

KEY `tag_id` (`tag_id`),

KEY `fid` (`fid`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_users

CREATE TABLE `bbs_users` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) DEFAULT NULL,

`password` char(32) DEFAULT NULL,

`openid` char(32) NOT NULL,

`email` varchar(50) DEFAULT NULL,

`avatar` varchar(100) DEFAULT NULL,

`homepage` varchar(50) DEFAULT NULL,

`money` int(11) DEFAULT '100',

`signature` text,

`forums` int(11) DEFAULT '0',

`replies` int(11) DEFAULT '0',

`notices` smallint(5) DEFAULT '0',

`follows` int(11) NOT NULL DEFAULT '0',

`regtime` int(10) DEFAULT NULL,

`lastlogin` int(10) DEFAULT NULL,

`lastpost` int(10) DEFAULT NULL,

`qq` varchar(20) DEFAULT NULL,

`group_type` tinyint(3) NOT NULL DEFAULT '0',

`gid` tinyint(3) NOT NULL DEFAULT '3',

`ip` char(15) DEFAULT NULL,

`location` varchar(128) DEFAULT NULL,

`token` varchar(40) DEFAULT NULL,

`introduction` text,

`is_active` tinyint(1) NOT NULL DEFAULT '1',

PRIMARY KEY (`uid`,`group_type`)

) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_user_follow

CREATE TABLE `bbs_user_follow` (

`follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uid` int(10) unsigned NOT NULL DEFAULT '0',

`follow_uid` int(10) unsigned NOT NULL DEFAULT '0',

`addtime` int(10) NOT NULL DEFAULT '0',

PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_user_groups

CREATE TABLE `bbs_user_groups` (

`gid` int(11) NOT NULL AUTO_INCREMENT,

`group_type` tinyint(3) NOT NULL DEFAULT '0',

`group_name` varchar(50) DEFAULT NULL,

`usernum` int(11) NOT NULL,

PRIMARY KEY (`gid`,`group_type`)

) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.data

CREATE TABLE `data` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`value` char(30) NOT NULL DEFAULT '',

`count` int(11) DEFAULT NULL,

PRIMARY KEY (`value`),

KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

# TABLE: ttlsa_com.t_data

CREATE TABLE `t_data` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`value` char(30) NOT NULL DEFAULT '',

`count` int(11) DEFAULT NULL,

PRIMARY KEY (`value`),

KEY `id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#...done.

只导出数据，且是批量插入语句：

#mysqldbexport --server=instance_3306  --format=sql  ttlsa_com --export=data --bulk-insert
# Source on localhost: ... connected.
USE `ttlsa_com`;
# Exporting data from ttlsa_com
# Data for table `ttlsa_com`.`bbs_categories`:
# Table bbs_categories has no data.
# Data for table `ttlsa_com`.`bbs_comments`:
# Table bbs_comments has no data.
# Data for table `ttlsa_com`.`bbs_favorites`:
# Table bbs_favorites has no data.
# Data for table `ttlsa_com`.`bbs_forums`:
# Table bbs_forums has no data.
# Data for table `ttlsa_com`.`bbs_links`:
# Table bbs_links has no data.
# Data for table `ttlsa_com`.`bbs_notifications`:
# Table bbs_notifications has no data.
# Data for table `ttlsa_com`.`bbs_page`:
# Table bbs_page has no data.
# Data for table `ttlsa_com`.`bbs_settings`:
# Table bbs_settings has no data.
# Data for table `ttlsa_com`.`bbs_tags`:
# Table bbs_tags has no data.
# Data for table `ttlsa_com`.`bbs_tags_relation`:
# Table bbs_tags_relation has no data.
# Data for table `ttlsa_com`.`bbs_users`:
# Table bbs_users has no data.
# Data for table `ttlsa_com`.`bbs_user_follow`:
# Table bbs_user_follow has no data.
# Data for table `ttlsa_com`.`bbs_user_groups`:
# Table bbs_user_groups has no data.
# Data for table `ttlsa_com`.`data`:
INSERT INTO `ttlsa_com`.`data` VALUES  (1, 'value-000', 1),  (2, 'value-001', 1),  (3, 'value-002', 1),  (4, 'value-003', 1),  (5, 'value-004', 1),  (6, 'value-005', 1);
# Data for table `ttlsa_com`.`t_data`:
# Table t_data has no data.
#...done.

#mysqldbexport --server=instance_3306 --format=sql ttlsa_com --export=data --bulk-insert

# Source on localhost: ... connected.

USE `ttlsa_com`;

# Exporting data from ttlsa_com

# Data for table `ttlsa_com`.`bbs_categories`:

# Table bbs_categories has no data.

# Data for table `ttlsa_com`.`bbs_comments`:

# Table bbs_comments has no data.

# Data for table `ttlsa_com`.`bbs_favorites`:

# Table bbs_favorites has no data.

# Data for table `ttlsa_com`.`bbs_forums`:

# Table bbs_forums has no data.

# Data for table `ttlsa_com`.`bbs_links`:

# Table bbs_links has no data.

# Data for table `ttlsa_com`.`bbs_notifications`:

# Table bbs_notifications has no data.

# Data for table `ttlsa_com`.`bbs_page`:

# Table bbs_page has no data.

# Data for table `ttlsa_com`.`bbs_settings`:

# Table bbs_settings has no data.

# Data for table `ttlsa_com`.`bbs_tags`:

# Table bbs_tags has no data.

# Data for table `ttlsa_com`.`bbs_tags_relation`:

# Table bbs_tags_relation has no data.

# Data for table `ttlsa_com`.`bbs_users`:

# Table bbs_users has no data.

# Data for table `ttlsa_com`.`bbs_user_follow`:

# Table bbs_user_follow has no data.

# Data for table `ttlsa_com`.`bbs_user_groups`:

# Table bbs_user_groups has no data.

# Data for table `ttlsa_com`.`data`:

INSERT INTO `ttlsa_com`.`data` VALUES (1, 'value-000', 1), (2, 'value-001', 1), (3, 'value-002', 1), (4, 'value-003', 1), (5, 'value-004', 1), (6, 'value-005', 1);

# Data for table `ttlsa_com`.`t_data`:

# Table t_data has no data.

#...done.

如果数据库中并不是所有的表是innodb引擎的，为了确保数据的一致性，需要在导出前锁定表。可以加上--locking=lock-all选项：


# mysqldbexport --server=instance_3306  --format=sql  ttlsa_com --export=data --bulk-insert  --locking=lock-all -vvv
# Source on localhost: ... connected.
# LOCK STRING: LOCK TABLE ttlsa_com.bbs_categories READ, ttlsa_com.bbs_comments READ, ttlsa_com.bbs_favorites READ, ttlsa_com.bbs_forums READ, ttlsa_com.bbs_links READ, ttlsa_com.bbs_notifications READ, ttlsa_com.bbs_page READ, ttlsa_com.bbs_settings READ, ttlsa_com.bbs_tags READ, ttlsa_com.bbs_tags_relation READ, ttlsa_com.bbs_users READ, ttlsa_com.bbs_user_follow READ, ttlsa_com.bbs_user_groups READ, ttlsa_com.data READ, ttlsa_com.t_data READ, mysql.proc READ, mysql.procs_priv READ, mysql.event READ
# UNLOCK STRING: UNLOCK TABLES
USE `ttlsa_com`;
# Exporting data from ttlsa_com
# Data for table `ttlsa_com`.`bbs_categories`:
# Table bbs_categories has no data.
# Data for table `ttlsa_com`.`bbs_comments`:
# Table bbs_comments has no data.
# Data for table `ttlsa_com`.`bbs_favorites`:
# Table bbs_favorites has no data.
# Data for table `ttlsa_com`.`bbs_forums`:
# Table bbs_forums has no data.
# Data for table `ttlsa_com`.`bbs_links`:
# Table bbs_links has no data.
# Data for table `ttlsa_com`.`bbs_notifications`:
# Table bbs_notifications has no data.
# Data for table `ttlsa_com`.`bbs_page`:
# Table bbs_page has no data.
# Data for table `ttlsa_com`.`bbs_settings`:
# Table bbs_settings has no data.
# Data for table `ttlsa_com`.`bbs_tags`:
# Table bbs_tags has no data.
# Data for table `ttlsa_com`.`bbs_tags_relation`:
# Table bbs_tags_relation has no data.
# Data for table `ttlsa_com`.`bbs_users`:
# Table bbs_users has no data.
# Data for table `ttlsa_com`.`bbs_user_follow`:
# Table bbs_user_follow has no data.
# Data for table `ttlsa_com`.`bbs_user_groups`:
# Table bbs_user_groups has no data.
# Data for table `ttlsa_com`.`data`:
INSERT INTO `ttlsa_com`.`data` VALUES  (1, 'value-000', 1),  (2, 'value-001', 1),  (3, 'value-002', 1),  (4, 'value-003', 1),  (5, 'value-004', 1),  (6, 'value-005', 1);
# Data for table `ttlsa_com`.`t_data`:
# Table t_data has no data.
#...done.
Time: 0.41 sec

# mysqldbexport --server=instance_3306 --format=sql ttlsa_com --export=data --bulk-insert --locking=lock-all -vvv

# Source on localhost: ... connected.

# LOCK STRING: LOCK TABLE ttlsa_com.bbs_categories READ, ttlsa_com.bbs_comments READ, ttlsa_com.bbs_favorites READ, ttlsa_com.bbs_forums READ, ttlsa_com.bbs_links READ, ttlsa_com.bbs_notifications READ, ttlsa_com.bbs_page READ, ttlsa_com.bbs_settings READ, ttlsa_com.bbs_tags READ, ttlsa_com.bbs_tags_relation READ, ttlsa_com.bbs_users READ, ttlsa_com.bbs_user_follow READ, ttlsa_com.bbs_user_groups READ, ttlsa_com.data READ, ttlsa_com.t_data READ, mysql.proc READ, mysql.procs_priv READ, mysql.event READ

# UNLOCK STRING: UNLOCK TABLES

USE `ttlsa_com`;

# Exporting data from ttlsa_com

# Data for table `ttlsa_com`.`bbs_categories`:

# Table bbs_categories has no data.

# Data for table `ttlsa_com`.`bbs_comments`:

# Table bbs_comments has no data.

# Data for table `ttlsa_com`.`bbs_favorites`:

# Table bbs_favorites has no data.

# Data for table `ttlsa_com`.`bbs_forums`:

# Table bbs_forums has no data.

# Data for table `ttlsa_com`.`bbs_links`:

# Table bbs_links has no data.

# Data for table `ttlsa_com`.`bbs_notifications`:

# Table bbs_notifications has no data.

# Data for table `ttlsa_com`.`bbs_page`:

# Table bbs_page has no data.

# Data for table `ttlsa_com`.`bbs_settings`:

# Table bbs_settings has no data.

# Data for table `ttlsa_com`.`bbs_tags`:

# Table bbs_tags has no data.

# Data for table `ttlsa_com`.`bbs_tags_relation`:

# Table bbs_tags_relation has no data.

# Data for table `ttlsa_com`.`bbs_users`:

# Table bbs_users has no data.

# Data for table `ttlsa_com`.`bbs_user_follow`:

# Table bbs_user_follow has no data.

# Data for table `ttlsa_com`.`bbs_user_groups`:

# Table bbs_user_groups has no data.

# Data for table `ttlsa_com`.`data`:

INSERT INTO `ttlsa_com`.`data` VALUES (1, 'value-000', 1), (2, 'value-001', 1), (3, 'value-002', 1), (4, 'value-003', 1), (5, 'value-004', 1), (6, 'value-005', 1);

# Data for table `ttlsa_com`.`t_data`:

# Table t_data has no data.

#...done.

Time: 0.41 sec

为当前的数据库创建一个从服务器：


# mysqldbexport --server=instance_3306  --format=sql  ttlsa_com --export=both --rpl-user=root  --rpl=master
# Source on localhost: ... connected.
STOP SLAVE;
# Exporting metadata from ttlsa_com
DROP DATABASE IF EXISTS `ttlsa_com`;
CREATE DATABASE `ttlsa_com`;
USE `ttlsa_com`;
# TABLE: ttlsa_com.bbs_categories
CREATE TABLE `bbs_categories` (
  `cid` smallint(5) NOT NULL AUTO_INCREMENT,
  `pid` smallint(5) NOT NULL DEFAULT '0',
  `cname` varchar(30) DEFAULT NULL COMMENT '分类名称',
  `content` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `ico` varchar(128) DEFAULT NULL,
  `master` varchar(100) NOT NULL,
  `permit` varchar(255) DEFAULT NULL,
  `listnum` mediumint(8) unsigned DEFAULT '0',
  `clevel` varchar(25) DEFAULT NULL,
  `cord` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`cid`,`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_comments
CREATE TABLE `bbs_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `content` text,
  `replytime` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`fid`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_favorites
CREATE TABLE `bbs_favorites` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`id`,`uid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_forums
CREATE TABLE `bbs_forums` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `cid` smallint(5) NOT NULL DEFAULT '0',
  `uid` mediumint(8) NOT NULL DEFAULT '0',
  `ruid` mediumint(8) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `content` text,
  `addtime` int(10) DEFAULT NULL,
  `updatetime` int(10) DEFAULT NULL,
  `lastreply` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `comments` smallint(8) DEFAULT '0',
  `favorites` int(10) unsigned DEFAULT '0',
  `closecomment` tinyint(1) DEFAULT NULL,
  `is_top` tinyint(1) NOT NULL DEFAULT '0',
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `ord` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`,`cid`,`uid`),
  KEY `updatetime` (`updatetime`),
  KEY `ord` (`ord`)
) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_links
CREATE TABLE `bbs_links` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `logo` varchar(200) DEFAULT NULL,
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_notifications
CREATE TABLE `bbs_notifications` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) DEFAULT NULL,
  `suid` int(11) DEFAULT NULL,
  `nuid` int(11) NOT NULL DEFAULT '0',
  `ntype` tinyint(1) DEFAULT NULL,
  `ntime` int(10) DEFAULT NULL,
  PRIMARY KEY (`nid`,`nuid`)
) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_page
CREATE TABLE `bbs_page` (
  `pid` tinyint(6) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `content` text,
  `go_url` varchar(100) DEFAULT NULL,
  `add_time` int(10) DEFAULT NULL,
  `is_hidden` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_settings
CREATE TABLE `bbs_settings` (
  `id` tinyint(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  `type` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`title`,`type`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_tags
CREATE TABLE `bbs_tags` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag_title` varchar(30) NOT NULL,
  `forums` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_title` (`tag_title`)
) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_tags_relation
CREATE TABLE `bbs_tags_relation` (
  `tag_id` int(10) NOT NULL DEFAULT '0',
  `fid` int(10) DEFAULT NULL,
  KEY `tag_id` (`tag_id`),
  KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_users
CREATE TABLE `bbs_users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `openid` char(32) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `homepage` varchar(50) DEFAULT NULL,
  `money` int(11) DEFAULT '100',
  `signature` text,
  `forums` int(11) DEFAULT '0',
  `replies` int(11) DEFAULT '0',
  `notices` smallint(5) DEFAULT '0',
  `follows` int(11) NOT NULL DEFAULT '0',
  `regtime` int(10) DEFAULT NULL,
  `lastlogin` int(10) DEFAULT NULL,
  `lastpost` int(10) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `gid` tinyint(3) NOT NULL DEFAULT '3',
  `ip` char(15) DEFAULT NULL,
  `location` varchar(128) DEFAULT NULL,
  `token` varchar(40) DEFAULT NULL,
  `introduction` text,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_user_follow
CREATE TABLE `bbs_user_follow` (
  `follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `follow_uid` int(10) unsigned NOT NULL DEFAULT '0',
  `addtime` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.bbs_user_groups
CREATE TABLE `bbs_user_groups` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `group_name` varchar(50) DEFAULT NULL,
  `usernum` int(11) NOT NULL,
  PRIMARY KEY (`gid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
# TABLE: ttlsa_com.data
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
# TABLE: ttlsa_com.t_data
CREATE TABLE `t_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#...done.
USE `ttlsa_com`;
# Exporting data from ttlsa_com
# Data for table `ttlsa_com`.`bbs_categories`:
# Data for table `ttlsa_com`.`bbs_comments`:
# Data for table `ttlsa_com`.`bbs_favorites`:
# Data for table `ttlsa_com`.`bbs_forums`:
# Data for table `ttlsa_com`.`bbs_links`:
# Data for table `ttlsa_com`.`bbs_notifications`:
# Data for table `ttlsa_com`.`bbs_page`:
# Data for table `ttlsa_com`.`bbs_settings`:
# Data for table `ttlsa_com`.`bbs_tags`:
# Data for table `ttlsa_com`.`bbs_tags_relation`:
# Data for table `ttlsa_com`.`bbs_users`:
# Data for table `ttlsa_com`.`bbs_user_follow`:
# Data for table `ttlsa_com`.`bbs_user_groups`:
# Data for table `ttlsa_com`.`data`:
INSERT INTO `ttlsa_com`.`data` VALUES (1, 'value-000', 1);
INSERT INTO `ttlsa_com`.`data` VALUES (2, 'value-001', 1);
INSERT INTO `ttlsa_com`.`data` VALUES (3, 'value-002', 1);
INSERT INTO `ttlsa_com`.`data` VALUES (4, 'value-003', 1);
INSERT INTO `ttlsa_com`.`data` VALUES (5, 'value-004', 1);
INSERT INTO `ttlsa_com`.`data` VALUES (6, 'value-005', 1);
# Data for table `ttlsa_com`.`t_data`:
#...done.
# Connecting to the current server as master
CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin-3306.000002', MASTER_LOG_POS = 262554;
START SLAVE;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

# mysqldbexport --server=instance_3306 --format=sql ttlsa_com --export=both --rpl-user=root --rpl=master

# Source on localhost: ... connected.

STOP SLAVE;

# Exporting metadata from ttlsa_com

DROP DATABASE IF EXISTS `ttlsa_com`;

CREATE DATABASE `ttlsa_com`;

USE `ttlsa_com`;

# TABLE: ttlsa_com.bbs_categories

CREATE TABLE `bbs_categories` (

`cid` smallint(5) NOT NULL AUTO_INCREMENT,

`pid` smallint(5) NOT NULL DEFAULT '0',

`cname` varchar(30) DEFAULT NULL COMMENT '分类名称',

`content` varchar(255) DEFAULT NULL,

`keywords` varchar(255) DEFAULT NULL,

`ico` varchar(128) DEFAULT NULL,

`master` varchar(100) NOT NULL,

`permit` varchar(255) DEFAULT NULL,

`listnum` mediumint(8) unsigned DEFAULT '0',

`clevel` varchar(25) DEFAULT NULL,

`cord` smallint(6) DEFAULT NULL,

PRIMARY KEY (`cid`,`pid`)

) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_comments

CREATE TABLE `bbs_comments` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`fid` int(11) NOT NULL DEFAULT '0',

`uid` int(11) NOT NULL DEFAULT '0',

`content` text,

`replytime` char(10) DEFAULT NULL,

PRIMARY KEY (`id`,`fid`,`uid`)

) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_favorites

CREATE TABLE `bbs_favorites` (

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`uid` mediumint(8) unsigned NOT NULL DEFAULT '0',

`favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',

`content` mediumtext NOT NULL,

PRIMARY KEY (`id`,`uid`),

KEY `uid` (`uid`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_forums

CREATE TABLE `bbs_forums` (

`fid` int(11) NOT NULL AUTO_INCREMENT,

`cid` smallint(5) NOT NULL DEFAULT '0',

`uid` mediumint(8) NOT NULL DEFAULT '0',

`ruid` mediumint(8) DEFAULT NULL,

`title` varchar(128) DEFAULT NULL,

`keywords` varchar(255) DEFAULT NULL,

`content` text,

`addtime` int(10) DEFAULT NULL,

`updatetime` int(10) DEFAULT NULL,

`lastreply` int(10) DEFAULT NULL,

`views` int(10) DEFAULT '0',

`comments` smallint(8) DEFAULT '0',

`favorites` int(10) unsigned DEFAULT '0',

`closecomment` tinyint(1) DEFAULT NULL,

`is_top` tinyint(1) NOT NULL DEFAULT '0',

`is_hidden` tinyint(1) NOT NULL DEFAULT '0',

`ord` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`fid`,`cid`,`uid`),

KEY `updatetime` (`updatetime`),

KEY `ord` (`ord`)

) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_links

CREATE TABLE `bbs_links` (

`id` smallint(6) NOT NULL AUTO_INCREMENT,

`name` varchar(100) DEFAULT NULL,

`url` varchar(200) DEFAULT NULL,

`logo` varchar(200) DEFAULT NULL,

`is_hidden` tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_notifications

CREATE TABLE `bbs_notifications` (

`nid` int(11) NOT NULL AUTO_INCREMENT,

`fid` int(11) DEFAULT NULL,

`suid` int(11) DEFAULT NULL,

`nuid` int(11) NOT NULL DEFAULT '0',

`ntype` tinyint(1) DEFAULT NULL,

`ntime` int(10) DEFAULT NULL,

PRIMARY KEY (`nid`,`nuid`)

) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_page

CREATE TABLE `bbs_page` (

`pid` tinyint(6) NOT NULL AUTO_INCREMENT,

`title` varchar(100) DEFAULT NULL,

`content` text,

`go_url` varchar(100) DEFAULT NULL,

`add_time` int(10) DEFAULT NULL,

`is_hidden` tinyint(1) DEFAULT '0',

PRIMARY KEY (`pid`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_settings

CREATE TABLE `bbs_settings` (

`id` tinyint(5) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL DEFAULT '',

`value` text NOT NULL,

`type` tinyint(3) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`,`title`,`type`)

) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_tags

CREATE TABLE `bbs_tags` (

`tag_id` int(10) NOT NULL AUTO_INCREMENT,

`tag_title` varchar(30) NOT NULL,

`forums` int(10) NOT NULL DEFAULT '0',

PRIMARY KEY (`tag_id`),

UNIQUE KEY `tag_title` (`tag_title`)

) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_tags_relation

CREATE TABLE `bbs_tags_relation` (

`tag_id` int(10) NOT NULL DEFAULT '0',

`fid` int(10) DEFAULT NULL,

KEY `tag_id` (`tag_id`),

KEY `fid` (`fid`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_users

CREATE TABLE `bbs_users` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) DEFAULT NULL,

`password` char(32) DEFAULT NULL,

`openid` char(32) NOT NULL,

`email` varchar(50) DEFAULT NULL,

`avatar` varchar(100) DEFAULT NULL,

`homepage` varchar(50) DEFAULT NULL,

`money` int(11) DEFAULT '100',

`signature` text,

`forums` int(11) DEFAULT '0',

`replies` int(11) DEFAULT '0',

`notices` smallint(5) DEFAULT '0',

`follows` int(11) NOT NULL DEFAULT '0',

`regtime` int(10) DEFAULT NULL,

`lastlogin` int(10) DEFAULT NULL,

`lastpost` int(10) DEFAULT NULL,

`qq` varchar(20) DEFAULT NULL,

`group_type` tinyint(3) NOT NULL DEFAULT '0',

`gid` tinyint(3) NOT NULL DEFAULT '3',

`ip` char(15) DEFAULT NULL,

`location` varchar(128) DEFAULT NULL,

`token` varchar(40) DEFAULT NULL,

`introduction` text,

`is_active` tinyint(1) NOT NULL DEFAULT '1',

PRIMARY KEY (`uid`,`group_type`)

) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_user_follow

CREATE TABLE `bbs_user_follow` (

`follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uid` int(10) unsigned NOT NULL DEFAULT '0',

`follow_uid` int(10) unsigned NOT NULL DEFAULT '0',

`addtime` int(10) NOT NULL DEFAULT '0',

PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.bbs_user_groups

CREATE TABLE `bbs_user_groups` (

`gid` int(11) NOT NULL AUTO_INCREMENT,

`group_type` tinyint(3) NOT NULL DEFAULT '0',

`group_name` varchar(50) DEFAULT NULL,

`usernum` int(11) NOT NULL,

PRIMARY KEY (`gid`,`group_type`)

) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

# TABLE: ttlsa_com.data

CREATE TABLE `data` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`value` char(30) NOT NULL DEFAULT '',

`count` int(11) DEFAULT NULL,

PRIMARY KEY (`value`),

KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

# TABLE: ttlsa_com.t_data

CREATE TABLE `t_data` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`value` char(30) NOT NULL DEFAULT '',

`count` int(11) DEFAULT NULL,

PRIMARY KEY (`value`),

KEY `id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#...done.

USE `ttlsa_com`;

# Exporting data from ttlsa_com

# Data for table `ttlsa_com`.`bbs_categories`:

# Data for table `ttlsa_com`.`bbs_comments`:

# Data for table `ttlsa_com`.`bbs_favorites`:

# Data for table `ttlsa_com`.`bbs_forums`:

# Data for table `ttlsa_com`.`bbs_links`:

# Data for table `ttlsa_com`.`bbs_notifications`:

# Data for table `ttlsa_com`.`bbs_page`:

# Data for table `ttlsa_com`.`bbs_settings`:

# Data for table `ttlsa_com`.`bbs_tags`:

# Data for table `ttlsa_com`.`bbs_tags_relation`:

# Data for table `ttlsa_com`.`bbs_users`:

# Data for table `ttlsa_com`.`bbs_user_follow`:

# Data for table `ttlsa_com`.`bbs_user_groups`:

# Data for table `ttlsa_com`.`data`:

INSERT INTO `ttlsa_com`.`data` VALUES (1, 'value-000', 1);

INSERT INTO `ttlsa_com`.`data` VALUES (2, 'value-001', 1);

INSERT INTO `ttlsa_com`.`data` VALUES (3, 'value-002', 1);

INSERT INTO `ttlsa_com`.`data` VALUES (4, 'value-003', 1);

INSERT INTO `ttlsa_com`.`data` VALUES (5, 'value-004', 1);

INSERT INTO `ttlsa_com`.`data` VALUES (6, 'value-005', 1);

# Data for table `ttlsa_com`.`t_data`:

#...done.

# Connecting to the current server as master

CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin-3306.000002', MASTER_LOG_POS = 262554;

START SLAVE;

既然有 mysqldbexport 那就有mysqldbimport 。实物都是对立存在的。有男就有女，有矛就有盾.

mysqldbimport

mysqldbimport 顾名思义，导入。mysqldbexport 的反面。将mysqldbimport导出的数据导入到另一个数据库服务器上。

如果一个对象已经存在于目标服务器上，那么将先删除再导入的。

跳过对象类型，可以使用–skip选项指定对象列表。这在为了提取某个特定对象情况下，非常有用。同样，跳过BLOB数据更新可以指定 –skip-blobs选项。

指定输入的格式，与mysqldbexport导出的格式对应：

sql (默认)以SQL语句方式输出。对于对象定义，以适当的 CREATE 和 GRANT 语句。对于数据，以 INSERT 语句。(或者批量插入 --bulk-insert指定该选项。.
grid网格化输出，类似于mysql命令行客户端输出
csv逗号分隔格式输出
raw_csv 输入一个简单的csv文件，包含用逗号分隔的行值。该文件可以包含行头信息。--table选项需要此格式。
tab制表符格式输出
vertical类似于mysql命令行客户端\G执行的输出

指定 –no-headers 选项，关闭CSV或tab显示对象的格式头部。

指定–quiet选项，关闭所有反馈信息。

默认情况下，创建的表的存储引起与原始表一样。可以使用--new-storage-engine选项来指定要使用的引擎。如果目标服务器支持该引擎，所有的表将使用该引擎的。

如果目标服务器不支持原表所使用的存储引起，可以使用--default-storage-engine选项来指定默认使用的引擎。

--new-storage-engine选项优先级高于 --default-storage-engine。

如果要导入的数据库的服务器上启用了GTIDs（GTID_MODE = ON），如果mysqlexport导出的数据没有包含GTID报表，将会有警告信息产生。

选项


Usage: mysqldbimport --server=user:pass@host:port:socket db1.csv db2.sql db3.grid

mysqldbimport - import metadata and data from files

Options:
  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --server=SERVER       connection information for the server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --ssl-ca=SSL_CA       The path to a file that contains a list of trusted SSL
                        CAs.
  --ssl-cert=SSL_CERT   The name of the SSL certificate file to use for
                        establishing a secure connection.
  --ssl-key=SSL_KEY     The name of the SSL key file to use for establishing a
                        secure connection.
  --character-set=CHARSET
                        sets the client character set. The default is
                        retrieved from the server variable
                        'character_set_client'.
  -f FORMAT, --format=FORMAT
                        the input file format in either sql (default), grid,
                        tab, csv, raw_csv or vertical format
  -i IMPORT_TYPE, --import=IMPORT_TYPE
                        control the import of either 'data' = only the table
                        data for the tables in the database list,
                        'definitions' = import only the definitions for the
                        objects in the database list, or 'both' = import the
                        metadata followed by the data (default: import
                        definitions)
  -d, --drop-first      drop database before importing.
  -b, --bulk-insert     use bulk insert statements for data (default:False)
  -h, --no-headers      files do not contain column headers (only applies to
                        formats: tab, csv).
  --dryrun              预导入不执行，测试用。测试文件是否有效。
  --table=TABLE         destination table in the form: <db>.<table>.
  --skip-blobs          do not import blob data.
  --skip-rpl            do not execute replication commands.
  --skip-gtid           do not execute the GTID_PURGED statements.
  --skip=SKIP_OBJECTS   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
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  --new-storage-engine=NEW_ENGINE
                        change all tables to use this storage engine if
                        storage engine exists on the destination.
  --default-storage-engine=DEF_ENGINE
                        change all tables to use this storage engine if the
                        original storage engine does not exist on the
                        destination.
  --multiprocess=MULTIPROCESS
                        use multiprocessing, number of processes to use for
                        concurrent execution. Special values: 0 (number of
                        processes equal to the CPUs detected) and 1 (default -
                        no concurrency).
  --autocommit          use autocommit, by default autocommit is off and
                        transactions are only committed once at the end of
                        each imported file.
  --max-bulk-insert=MAX_BULK_INSERT
                        maximum bulk insert size, by default 30000.

Usage: mysqldbimport --server=user:pass@host:port:socket db1.csv db2.sql db3.grid

mysqldbimport - import metadata and data from files

Options:

--version show program's version number and exit

--help display a help message and exit

--license display program's license and exit

--server=SERVER connection information for the server in the form:

<user>[:<password>]@<host>[:<port>][:<socket>] or

<login-path>[:<port>][:<socket>] or <config-

path>[<[group]>].

--ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL

CAs.

--ssl-cert=SSL_CERT The name of the SSL certificate file to use for

establishing a secure connection.

--ssl-key=SSL_KEY The name of the SSL key file to use for establishing a

secure connection.

--character-set=CHARSET

sets the client character set. The default is

retrieved from the server variable

'character_set_client'.

-f FORMAT, --format=FORMAT

the input file format in either sql (default), grid,

tab, csv, raw_csv or vertical format

-i IMPORT_TYPE, --import=IMPORT_TYPE

control the import of either 'data' = only the table

data for the tables in the database list,

'definitions' = import only the definitions for the

objects in the database list, or 'both' = import the

metadata followed by the data (default: import

definitions)

-d, --drop-first drop database before importing.

-b, --bulk-insert use bulk insert statements for data (default:False)

-h, --no-headers files do not contain column headers (only applies to

formats: tab, csv).

--dryrun 预导入不执行，测试用。测试文件是否有效。

--table=TABLE destination table in the form: <db>.<table>.

--skip-blobs do not import blob data.

--skip-rpl do not execute replication commands.

--skip-gtid do not execute the GTID_PURGED statements.

--skip=SKIP_OBJECTS 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

-v, --verbose control how much information is displayed. e.g., -v =

verbose, -vv = more verbose, -vvv = debug

-q, --quiet turn off all messages for quiet execution.

--new-storage-engine=NEW_ENGINE

change all tables to use this storage engine if

storage engine exists on the destination.

--default-storage-engine=DEF_ENGINE

change all tables to use this storage engine if the

original storage engine does not exist on the

destination.

--multiprocess=MULTIPROCESS

use multiprocessing, number of processes to use for

concurrent execution. Special values: 0 (number of

processes equal to the CPUs detected) and 1 (default -

no concurrency).

--autocommit use autocommit, by default autocommit is off and

transactions are only committed once at the end of

each imported file.

--max-bulk-insert=MAX_BULK_INSERT

maximum bulk insert size, by default 30000.

如果你的系统有多个处理器，可以并发执行。并发导入应用于文件级别，这意味着只有不同的文件可以同时执行的。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

mysql-utilities工具之mysqldbexport和mysqlimport

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： MySQL mysql-utilities mysqldbexport mysqlimport

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 9月
一	二	三	四	五	六	日
	1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

AI (2)
ClickHouse (6)
DM达梦 (9)
Docker (46)
GBase (37)
Greenplum (109)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (5)
MySQL (211)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (11)
Oracle (635)
OS (383)
PostgreSQL (259)
Python (26)
Redis (21)
SQL Server (157)
SQLite (1)
StarRocks (21)
TDSQL (5)
TiDB (18)
YMatrix (5)
中间件 (10)
云 (10)
人大金仓KingBase (9)
其它 (83)
培训考证 (45)
大数据 (6)
存储 (8)
巡检监控 (47)
工具 (100)
数据迁移 (16)
架构篇 (12)
生活 (10)
电脑 (18)
网络 (94)
育儿 (5)
视频 (17)
面试笔试 (38)

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

mysql-utilities工具之mysqldbexport和mysqlimport

mysqldbexport

输出格式有：

指定要显示多少数据，可以使用 --display 选项：

选项

实例

mysqldbimport

指定输入的格式，与mysqldbexport导出的格式对应：

选项

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

mysql-utilities工具之mysqldbexport和mysqlimport

mysqldbexport

输出格式有：

指定要显示多少数据，可以使用 --display 选项：

选项

实例

mysqldbimport

指定输入的格式，与mysqldbexport导出的格式对应：

选项

相关文章

您可能还喜欢...

MySQL主从复制报错Failed to flush master info

MySQL的审计功能实现方案

【DB宝35】使用MySQL 8.0 克隆(clone)插件快速添加MGR节点

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

发表回复取消回复