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

最新推荐文章于 2024-02-21 22:39:28 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-02-21 22:39:28 发布

阅读量285

点赞数

分类专栏： MySQL数据库运维文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_43346403/article/details/129141115

版权

MySQL数据库运维专栏收录该内容

125 篇文章 6 订阅

订阅专栏

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;
--启动从库。