mysql-utilities工具之mysqldbexport和mysqlimport
Tags： MySQLmysql-utilitiesmysqldbexportmysqlimport
👉 本文共约3867个字，系统预计阅读时间或需15分钟。
mysqldbexport
mysqldbexport 从一个或多个数据库导出对象定义的元数据和数据。默认情况下，仅仅导出对象的定义。与mysqldump类似，但区别也大。mysqldbexport 可以有多个格式，使数据更容易提取和转移。
要排出特定对象名称，可以使用--exclude选项，格式：db.obj 。也可以提供一个搜索模式。如 --exclude=db1.trig1 ，排除单个触发器。--exclude=trig_ 排除以trig_开头的。也可以使用正则表达式。
跳过对象类型，可以使用--skip选项指定对象列表。这在为了提取某个特定对象情况下，非常有用。同样，跳过BLOB数据更新可以指定 --skip-blobs选项。
输出格式有：
- 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正确记录。
选项
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | --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 选项，参数值不区分大小写，但是不能参数值钱不能包含空格。
实例
只导出定义语句
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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. |
只导出数据，且是批量插入语句：
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 31 32 33 34 35 36 | #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选项：
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 31 32 33 34 35 36 37 38 39 | # 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 |
为当前的数据库创建一个从服务器：
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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报表，将会有警告信息产生。
选项
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | 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. |
如果你的系统有多个处理器，可以并发执行。并发导入应用于文件级别，这意味着只有不同的文件可以同时执行的。
实例
导入元数据
1 2 3 4 5 | shell> mysqldbimport --server=root@localhost --import=definitions \ --format=csv data.csv # Source on localhost: ... connected. # Importing definitions from data.csv. #...done. |
批量插入语句导入
1 2 3 4 5 6 | shell> mysqldbimport --server=root@localhost --import=data \ --bulk-insert --format=csv data.csv # Source on localhost: ... connected. # Importing data from data.csv. #...done. |
批量插入语句导入，导入定义数据和数据
1 2 3 4 5 6 | shell> mysqldbimport --server=root@localhost --import=both --bulk-insert --format=sql data.sql # Source on localhost: ... connected. # Importing definitions and data from data.sql. #...done. |
使用mysqldbexport 和 mysqldbimport 工具来创建新的从库
通过这两个工具来为现有的主从复制新加一台新的从库服务器。
环境说明
instance_3306： master
instance_3308：slave
导出数据
1 2 | # mysqldbexport --server=instance_3306 --all --export=both --rpl=master --rpl-user=root > /tmp/data.sql |
导入数据
1 2 3 4 5 6 7 | mysql -uroot -p -S /data/mydata3308/mysql.sock -e "reset master" Enter password: [root@localhost t]# mysqldbimport --server=instance_3308 /tmp/data.sql # Source on localhost: ... connected. # Importing definitions from /tmp/data.sql. #...done. |
至此，主从已经建立好了。是不是非常方便非常容易？
主从状态
instance_3306
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> show variables like "server_uuid"; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 1597393e-8760-11e4-baf3-0050569f3bb8 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 3308 | | 3308 | 3306 | 034d694d-8769-11e4-bb2d-0050569f3bb8 | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec) |
instance_3308
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | mysql> show variables like "%uuid%"; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | 034d694d-8769-11e4-bb2d-0050569f3bb8 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-3306.000002 Read_Master_Log_Pos: 262554 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 288 Relay_Master_Log_File: mysql-bin-3306.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 262554 Relay_Log_Space: 461 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3306 Master_UUID: 1597393e-8760-11e4-baf3-0050569f3bb8 Master_Info_File: /data/mydata3308/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) |
说明
data.sql文件中会包含如下语句的：
STOP SLAVE;
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;
在导入之前，先执行下reset master，是为了清除之前的主从信息。否则会报下面的错误。
ERROR: The import operation contains GTID statements that require the global gtid_executed
system variable on the target to be empty (no value). The gtid_executed value must be reset
by issuing a RESET MASTER command on the target prior to attempting the import operation.
Once the global gtid_executed value is cleared, you may retry the import.
我这里就没创建复制用户，就直接以root用户来的。
我的示例
# 只导出定义语句
mysqldbexport --server=mysql_77 --format=sql ecard --export=definitions > ecard.sql
# 只导出数据 批量插入
mysqldbexport --server=mysql_77 --format=sql ecard --export=data --bulk-insert > ecard.sql
mysqldbexport --server=mysql_77 --format=sql ecard --export=data --bulk-insert --file-per-table #一个表一个文件
若数据库中并不是所有表是innodb引擎，为确保数据一致性，需要在导入 前锁定表，加上 --locking=lock-all
# 导出结构和数据 并为当前数据库创建一个从服务器
mysqldbexport --server=mysql_77 --format=sql ecard --export=both --rpl-user=root --rpl=master > ecard.sql
mysqldbexport --server=root:lhr@192.168.66.35:3317 db1 --format=sql --export=definitions > db1.sql
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
mysqldbexport --server=root:lhr@192.168.66.35:3317 db1 --format=sql --export=data --bulk-insert > db1_data.sql
mysqldbimport --server=user:pass@host:port:socket db1.csv db2.sql db3.grid
导入数据，若一个对象已经存在于目标服务器上，将先删除再导入
选项：
-- format 格式
-- import data definitions(默认) both
-- drop - first
-- no - headers 排除表头，适用于tab csv格式
-- dryrun 预导入不执行，测试用。测试文件是否有效
-- table
--skip - blobs --skip -rpl --skip -gtid
--skip =SKIP_OBJECTS 忽略的对象 tables , views , triggers , procedures , functions , events , grants , data , create_db
mysqldbimport --server=mysql_77 --import=definitions --format=csv data.csv
mysqldbimport --server=mysql_77 --import=data --bulk-insert --format=csv data.csv
