MySQL管理工具MySQL Utilities — mysqldbexport(8)

默北 MySQLMySQL管理工具MySQL Utilities — mysqldbexport(8)已关闭评论14,4852字数 21338阅读71分7秒阅读模式

mysqldbexport 从一个或多个数据库导出对象定义的元数据和数据。默认情况下,仅仅导出对象的定义。与mysqldump类似,但区别也大。mysqldbexport 可以有多个格式,使数据更容易提取和转移。

要排出特定对象名称,可以使用--exclude选项,格式:db.*obj* 。也可以提供一个搜索模式。如 --exclude=db1.trig1 ,排除单个触发器。--exclude=trig_ 排除以trig_开头的。也可以使用正则表达式。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

跳过对象类型,可以使用--skip选项指定对象列表。这在为了提取某个特定对象情况下,非常有用。同样,跳过BLOB数据更新可以指定 --skip-blobs选项。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

输出格式有:

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

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

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

注意:对于SQL格式输出,--display选项被忽略。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

指定 --no-headers 选项,关闭CSV或tab显示对象的格式头部。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

指定--quiet选项,关闭所有反馈信息。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

指定 --file-per-table选项,每个表数据单独保存。每个文件的名称以数据库和表名称组成。格式:db1.*table_name*.csv。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

默认情况下,复制操作是使用一致性快照来读取源数据库。要改变锁定模式,可以使用–locking选项来指定锁定类型值。值no-locks关闭锁,lock-all只使用表锁。默认是snapshot。此外,使用WRITE锁,在复制过程中将锁定目标表。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

从主或者从服务器导数据还可以包含复制语句。–rpl选项指定文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

  • 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 语句。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

如果要导出的数据库的服务器上启用了GTIDs(GTID_MODE = ON),如果只导出其中一部分数据库,将会有警告信息产生。这是因为GTID报表生成包括所有数据库的gtids,不仅仅是某个的。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

如果有启用GTID,但是使用了–skip-gtid也会收到警告。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

如果启用了GTID,最好是导出或导入所有的数据库。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

从另一台服务器上导入这些导出的数据,需要确保这些数据以及日志中所有的GTIDs正确记录。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

选项

  --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).

必需提供连接参数和赋予要访问对象的适当权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

从源服务器上导出所有对象,必须要有:SELECT、SHOW VIEW权限,同时还需要有mysql数据库的SELECT权限。实际所需的权限可能会有所不同,以实际情况为准。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

对于 --format, --export 和 --display 选项,参数值不区分大小写,但是不能参数值钱不能包含空格。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

实例

只导出定义语句文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

# 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.

只导出数据,且是批量插入语句:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

# 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选项:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

# 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

为当前的数据库创建一个从服务器:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/

# 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 。实物都是对立存在的。 有男就有女,有矛就有盾.

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
默北
  • 本文由 发表于 23/01/2015 01:00:06
  • 转载请务必保留本文链接:https://www.ttlsa.com/mysql/mysql-utilities-mysqldbexport/