48.MySQL-Utilities工具包之-mysqldbexport导出DDL/DML

1.导出数据库和表的结构:DDL。 
mysqldbexport --server=root:rootroot@localhost:3306:/tmp/mysql.sock  --format=sql test --export=definitions  > test.sql 
[root@mysql1 ~]# mysqldbexport --server=root:rootroot@localhost:3306:/tmp/mysql.sock  --format=sql test --export=definitions 

--导出内容如下：

# WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# WARNING: A partial export from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don''t want to generate the GTID statement, use the --skip-gtid option. To export all databases, use the --all and --export=both options.
SET @MYSQLUTILS_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN = 0;
SET @@GLOBAL.GTID_PURGED = 'daf4393d-22e8-11ed-a240-000c296a9b44:1-91';
# Exporting metadata from `test`
DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE `test`;
# TABLE: `test`.`mf_advisorshareholder`
CREATE TABLE `mf_advisorshareholder` (
  `ID` bigint(20) NOT NULL COMMENT 'ID',
  `InvestAdvisorCode` int(11) NOT NULL COMMENT '基金公司代码',
  `ChangeDate` datetime DEFAULT NULL COMMENT '全称更改日期',
  `InfoSource` varchar(50) DEFAULT NULL COMMENT '信息来源',
  `SerialNumber` int(11) DEFAULT NULL COMMENT '序号',
  `Name` varchar(100) DEFAULT NULL COMMENT '无形资产名称',
  `InstitutionCode` int(11) DEFAULT NULL COMMENT '机构编码',
  `CompanyCode` int(11) DEFAULT NULL COMMENT '公司代码',
  `HoldingShares` decimal(18,4) DEFAULT NULL COMMENT '持有份额(份)',
  `HoldingRatio` decimal(18,6) DEFAULT NULL COMMENT '持有比例',
  `IfExisted` tinyint(3) DEFAULT NULL COMMENT '是否存在',
  `XGRQ` datetime NOT NULL COMMENT '修改日期',
  `JSID` bigint(20) NOT NULL COMMENT 'JSID',
  UNIQUE KEY `IX_MF_AdvisorShareholder_JSID` (`JSID`),
  UNIQUE KEY `PK_MF_AdvisorShareholder` (`ID`),
  UNIQUE KEY `IX_MF_AdvisorShareholder` (`InvestAdvisorCode`,`ChangeDate`,`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# TABLE: `test`.`t2`
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# TABLE: `test`.`t3`
CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# TABLE: `test`.`test1`
CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
   
2.只导出数据库的数据:DML。 
mysqldbexport --server=root:rootroot@localhost:3306:/tmp/mysql.sock --format=sql test --export=data --bulk-insert >itpuxdb_data.sql  

[root@mysql1 ~]# 
[root@mysql1 ~]# more test_data.sql 
# WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# WARNING: A partial export from a server that has GTIDs enabled will by default include the GTIDs of all transactions, 
#even those that changed suppressed parts of the database. If you don''t want to generate the GTID statement, use the --skip-gtid option. To export all databases, use the --all and --export=both options.
SET @MYSQLUTILS_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN = 0;
SET @@GLOBAL.GTID_PURGED = 'daf4393d-22e8-11ed-a240-000c296a9b44:1-91';
USE `test`;
# Exporting data from `test`
# Data for table `test`.`mf_advisorshareholder`:
# Table `mf_advisorshareholder` has no data.
# Data for table `test`.`t2`:
INSERT INTO `test`.`t2` VALUES  (1, NULL, 18),  (2, 'xsq2', 18),  (3, 'xsq3', 18),  (4, 'xsq4', 18),  (5, 'xsq5', 18),  (6, 'xsq6', 18),  (7, 'xsq7', 18),  (8, 'xsq8', 18),  (9, 'xsq19', 19
),  (9, 'xsq19', 19),  (10, 'xsq10', 10),  (11, 'xsq11', 11),  (11, 'xsq11', 11),  (1, NULL, 18),  (2, 'xsq2', 18),  (3, 'xsq3', 18),  (4, 'xsq4', 18),  (5, 'xsq5', 18),  (6, 'xsq6', 18),  
(7, 'xsq7', 18),  (8, 'xsq8', 18),  (9, 'xsq19', 19),  (9, 'xsq19', 19),  (10, 'xsq10', 10),  (11, 'xsq11', 11),  (11, 'xsq11', 11),  (1, NULL, 18),  (2, 'xsq2', 18),  (3, 'xsq3', 18),  (4,
 'xsq4', 18),  (5, 'xsq5', 18),  (6, 'xsq6', 18),  (7, 'xsq7', 18),  (8, 'xsq8', 18),  (9, 'xsq19', 19),  (9, 'xsq19', 19),  (10, 'xsq10', 10),  (11, 'xsq11', 11),  (11, 'xsq11', 11),  (1, 
NULL, 18),  (2, 'xsq2', 18),  (3, 'xsq3', 18),  (4, 'xsq4', 18),  (5, 'xsq5', 18),  (6, 'xsq6', 18),  (7, 'xsq7', 18),  (8, 'xsq8', 18),  (9, 'xsq19', 19),  (9, 'xsq19', 19),  (10, 'xsq10',
 10),  (11, 'xsq11', 11),  (11, 'xsq11', 11),  (1, NULL, 18),  (2, 'xsq2', 18),  (3, 'xsq3', 18),  (4, 'xsq4', 18),  (5, 'xsq5', 18),  (6, 'xsq6', 18),  (7, 'xsq7', 18),  (8, 'xsq8', 18),  
(9, 'xsq19', 19),  (9, 'xsq19', 19),  (10, 'xsq10', 10),  (11, 'xsq11', 11),  (11, 'xsq11', 11),  (1, NULL, 18),  (2, 'xsq2', 18),  (3, 'xsq3', 18),  (4, 'xsq4', 18),  (5, 'xsq5', 18),  (6,
 'xsq6', 18),  (7, 'xsq7', 18),  (8, 'xsq8', 18),  (9, 'xsq19', 19),  (9, 'xsq19', 19),  (10, 'xsq10', 10),  (11, 'xsq11', 11),  (11, 'xsq11', 11),  (1, NULL, 18),  (2, 'xsq2', 18),  (3, 'x
sq3', 18),  (4, 'xsq4', 18),  (5, 'xsq5', 18),  (6, 'xsq6', 18),  (7, 'xsq7', 18),  (8, 'xsq8', 18),  (9, 'xsq19', 19),  (9, 'xsq19', 19),  (10, 'xsq10', 10),  (11, 'xsq11', 11),  (11, 'xsq
11', 11),  (1, NULL, 18),  (2, 'xsq2', 18),  (3, 'xsq3', 18),  (4, 'xsq4', 18),  (5, 'xsq5', 18),  (6, 'xsq6', 18),  (7, 'xsq7', 18),  (8, 'xsq8', 18),  (9, 'xsq19', 19),  (9, 'xsq19', 19),
  (10, 'xsq10', 10),  (11, 'xsq11', 11),  (11, 'xsq11', 11);
# Data for table `test`.`t3`:
INSERT INTO `test`.`t3` VALUES  (1);
# Data for table `test`.`test1`:
INSERT INTO `test`.`test1` VALUES  (2, 'xxx222'),  (3, 'xxx3');
# Data for table `test`.`test2`:
INSERT INTO `test`.`test2` VALUES  (1, 'xxx1', NULL),  (2, 'xx2', NULL);
# Data for table `test`.`test3`:
INSERT INTO `test`.`test3` VALUES  (1, 'xxx1', NULL),  (2, 'xx2', NULL);
# Data for table `test`.`test4`:
INSERT INTO `test`.`test4` VALUES  (1, 'xxx1', NULL),  (2, 'xx2', NULL);

   
3.给当前库创建一个从库需要的SQL语句生成。 
但是这里只导出了一个数据库。test数据库。
mysqldbexport --server=root:rootroot@localhost:3306:/tmp/mysql.sock --format=sql test --export=both --rpl-user=root --rpl=master >test_data_rep.sql

[root@mysql1 ~]# more test_data_rep.sql
# WARNING: Using a password on the command line interface can be insecure.
# Source on localhost: ... connected.
# WARNING: A partial export from a server that has GTIDs enabled will by default include the GTIDs of all transactions, 
#even those that changed suppressed parts of the database. If you don''t want to generate the GTID statement, use the --skip-gtid option. To export all databases, use the --all and --export=both options.
STOP SLAVE;
SET @MYSQLUTILS_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN = 0;
SET @@GLOBAL.GTID_PURGED = 'daf4393d-22e8-11ed-a240-000c296a9b44:1-91';
# Exporting metadata from `test`
DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE `test`;
# TABLE: `test`.`mf_advisorshareholder`
CREATE TABLE `mf_advisorshareholder` (
  `ID` bigint(20) NOT NULL COMMENT 'ID',
  `InvestAdvisorCode` int(11) NOT NULL COMMENT '基金公司代码',
  `ChangeDate` datetime DEFAULT NULL COMMENT '全称更改日期',
  `InfoSource` varchar(50) DEFAULT NULL COMMENT '信息来源',
  `SerialNumber` int(11) DEFAULT NULL COMMENT '序号',
  `Name` varchar(100) DEFAULT NULL COMMENT '无形资产名称',
  `InstitutionCode` int(11) DEFAULT NULL COMMENT '机构编码',
  `CompanyCode` int(11) DEFAULT NULL COMMENT '公司代码',
  `HoldingShares` decimal(18,4) DEFAULT NULL COMMENT '持有份额(份)',
  `HoldingRatio` decimal(18,6) DEFAULT NULL COMMENT '持有比例',
  `IfExisted` tinyint(3) DEFAULT NULL COMMENT '是否存在',
  `XGRQ` datetime NOT NULL COMMENT '修改日期',
  `JSID` bigint(20) NOT NULL COMMENT 'JSID',
  UNIQUE KEY `IX_MF_AdvisorShareholder_JSID` (`JSID`),
  UNIQUE KEY `PK_MF_AdvisorShareholder` (`ID`),
  UNIQUE KEY `IX_MF_AdvisorShareholder` (`InvestAdvisorCode`,`ChangeDate`,`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
...........
...........--建表和插入语句。
SET @@SESSION.SQL_LOG_BIN = @MYSQLUTILS_TEMP_LOG_BIN;
# Connecting to the current server as master
CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_AUTO_POSITION=1;
START SLAVE; 
--启动从库。

4.总结

在实际生成环境中可能数据库非常大，我们很少使用这个方法生成创建从库的语句，然后在从库执行。如果是初始搭建主从，无数据则可以使用这个方法。其导出DDL/DML的功能还是很使用。