GBase 8a 数据导入导出

原创已于 2023-01-29 14:31:22 修改 · 5.2k 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2023-01-18 12:12:38 首次发布

GBase8a 专栏收录该内容

17 篇文章

订阅专栏

文章介绍了如何使用gcdump工具导出MySQL数据库的表结构和数据，包括命令行选项如`-A`、`-u`、`-p`等，并展示了数据导出和导入的示例，包括使用`SELECTINTOOUTFILE`和`LOADDATAINFILE`命令。此外，还强调了在操作前进行数据备份的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表结构导出工具gcdump

数据导出

数据导入

表结构导出工具gcdump

-A, --all-databases 导出所有用户数据库结构
-u, --user=name 连接数据库的数据库用户名
-p, --password[=name] 连接数据库的用户密码
-P, --port=# 连接数据库的端口
-B, --databases 导出指定的数据库结构
-f, --force 导出过程中忽略 sql 错误
--ignore-table=database.table 指定不要转储的表，该参数每次只能指定一个表，如果需要忽略多个表，使用多个参数指定。
-n, --no-create-db 不输出建库语句'CREATE DATABASE IF NOT EXISTS db_name;' 语句
-t, --no-create-info 不输出建表语句
-q, --quick 导出结果不缓存，直接输出
-Q, --quote-names 输出的表名和列名带引用符号（`）
-r, --result-file=name 导出结果输出到指定的文件中
-R, --routines 导出存储过程和函数
-W, --fixed-vc-name=name 指定导出的 VC 名字，一次只能导出一个 VC 的数据库对象，如果不指定该参数，该参数默认为 default vc
-X, --xml 导出文件格式为 xml
-I, --colId 导出表结构含 TID 和 UID，同 show full create table

[gbase@hw-01 ~]$ cd /opt/gbase/192.168.0.213/gcluster/server/bin/
[gbase@hw-01 bin]$ ./gcdump -ugbase -pgbase2011 -B test > /home/gbase/test_db.sql
[gbase@hw-01 bin]$ cat /home/gbase/test_db.sql

DROP DATABASE IF EXISTS `test`;

CREATE DATABASE  IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8;

USE `test`;

--
-- Dumping events for database 'test'
--
/*! DROP EVENT IF EXISTS `import_audit_log` */;
DELIMITER ;;
/*! CREATE EVENT `import_audit_log` ON SCHEDULE EVERY 60 MINUTE STARTS '2023-01-05 21:12:48' ON COMPLETION NOT PRESERVE ENABLE LOCAL DO begin
	declare errno int;
	declare msg text;
	declare exit handler for sqlexception
	begin
		get diagnostics condition 1 errno = gbase_errno, msg = message_text;
		create table if not exists import_audit_log_errors(
		err_time datetime,
		hostname varchar(64),
		err_no int,
		msg_txt varchar(1024)
		) CHARSET=utf8mb4;
		insert into import_audit_log_errors values (now(), @@hostname, errno, substr(msg, 0, 1024));
	end;
	create table if not exists audit_log_express (
	hostname varchar(64),
	thread_id int,
	taskid bigint,
	start_time datetime,
	uid bigint, user varchar(16),
	host_ip varchar(32),
	query_time time, rows bigint,
	table_list varchar(4096),
	sql_text varchar(8191),
	ql_type varchar(16),
	sql_command varchar(32),
	operators varchar(256),
	status varchar(16),
	conn_type varchar(16)
	) CHARSET=utf8mb4;
	set self sql_mode = '';
	create self table gbase.audit_log_bak2 like gbase.audit_log;
	set self sql_mode = default;
    rename self table gbase.audit_log to gbase.audit_log_bak1,   -- 这里不知道为什么 rename之后 数据没有进去，所以在后面又insert了一次
	gbase.audit_log_bak2 to gbase.audit_log;
	repair table gbase.audit_log;
	insert into gbase.audit_log_bak1 select * from gbase.audit_log;
	set _gbase_query_path = on;
	insert into audit_log_express select
	@@hostname as hostname,
	thread_id,
	taskid,
	start_time,
	uid,
	user,
	host_ip,
	query_time,
	rows,
	substr(table_list, 0, 4096),
	substr(sql_text, 0, 8191),
	sql_type,
	sql_command,
	operators,
	status,
	conn_type
	from gbase.audit_log_bak1;
	drop self table gbase.audit_log_bak1;
end */ ;;
DELIMITER ;
/*! SET TIME_ZONE= @save_time_zone */ ;

USE `test`;
DROP TABLE IF EXISTS `audit_log_express`;
CREATE TABLE `audit_log_express` (
  `hostname` varchar(64) DEFAULT NULL,
  `thread_id` int(11) DEFAULT NULL,
  `taskid` bigint(20) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `uid` bigint(20) DEFAULT NULL,
  `user` varchar(16) DEFAULT NULL,
  `host_ip` varchar(32) DEFAULT NULL,
  `query_time` time DEFAULT NULL,
  `rows` bigint(20) DEFAULT NULL,
  `table_list` varchar(4096) DEFAULT NULL,
  `sql_text` varchar(8191) DEFAULT NULL,
  `ql_type` varchar(16) DEFAULT NULL,
  `sql_command` varchar(32) DEFAULT NULL,
  `operators` varchar(256) DEFAULT NULL,
  `status` varchar(16) DEFAULT NULL,
  `conn_type` varchar(16) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8mb4 TABLESPACE='sys_tablespace';
DROP TABLE IF EXISTS `import_audit_log_errors`;
CREATE TABLE `import_audit_log_errors` (
  `err_time` datetime DEFAULT NULL,
  `hostname` varchar(64) DEFAULT NULL,
  `err_no` int(11) DEFAULT NULL,
  `msg_txt` varchar(1024) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8mb4 TABLESPACE='sys_tablespace';
DROP TABLE IF EXISTS `outtable`;
CREATE TABLE `outtable` (
  `no` int(11) DEFAULT NULL,
  `text` varchar(10) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace';
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `no` int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace';
DROP TABLE IF EXISTS `t5`;
CREATE TABLE `t5` (
  `no` int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace';

数据导出

select * from test.outtable into outfile '/home/gbase/outtable.GZ' FIELDS TERMINATED BY '|'  FIELDS ESCAPED BY '\\'   optionally DOUBLE_ENCLOSED BY '\''   WRITEMODE BY overwrites;

--如果在导出后 发现数据中包含转译符号\ 那么需要将转译符号置空 否则导入的数据会多一个\
select * from test.t1 into outfile '/home/gbase/t1.data4' FIELDS TERMINATED BY '|'  FIELDS ESCAPED BY ''

outfile '/home/gbase/outtable.GZ' 以gzip方式压缩

FIELDS TERMINATED BY '|' 分割符为 |

FIELDS ESCAPED BY '\\' 转译符为 \

optionally DOUBLE_ENCLOSED BY '\'' 包围符为 ' 只对字符串类型起作用

WRITEMODE BY overwrites 导出文件覆盖已有文件

gbase> create table outtable(no int,text varchar(10));
gbase> insert into test.outtable(no,text) values(1,'a''b'),(2,'a\\b'),(3,'a''\\b');
gbase> select * from outtable;
+------+------+
| no   | text |
+------+------+
|    1 | a'b  |
|    2 | a\b  |
|    3 | a'\b |
+------+------+
gbase> select * from test.outtable into outfile '/home/gbase/outtable.GZ' FIELDS TERMINATED BY '|'  FIELDS ESCAPED BY '\\'   optionally DOUBLE_ENCLOSED BY '\''   WRITEMODE BY overwrites;
gbase> select * from test.outtable into outfile '/home/gbase/outtable.data' FIELDS TERMINATED BY '|'  FIELDS ESCAPED BY '\\'   optionally DOUBLE_ENCLOSED BY '\''   WRITEMODE BY overwrites;

[gbase@hw-01 home]$ cd /home/gbase/outtable.data/
[gbase@hw-01 outtable.data]$ ll
-rw------- 1 gbase gbase 29 Jan 18 11:06 outtable.data
[gbase@hw-01 outtable.data]$ cat outtable.data 
1|'a\'b'
2|'a\\b'
3|'a\'\\b'

[gbase@hw-01 home]$ cd /home/gbase/outtable.GZ/
[gbase@hw-01 outtable.GZ]$ ll
-rw------- 1 gbase gbase 39 Jan 18 11:05 outtable.GZ

数据导入

如果有原始数据，记得先备份一下

备份表结构
[gbase@bogon ~]$ gcdump -ugbase -pgbase2011 -B test > /home/gbase/test_db_ddl.bak
备份数据
gbase> select * from test.audit_log_expressinto outfile '/home/gbase/audit_log_express.data' FIELDS TERMINATED BY '|'  FIELDS ESCAPED BY '\\'   optionally DOUBLE_ENCLOSED BY '\''   WRITEMODE BY overwrites;

导入表结构

#这些是数据导出时的数据  不是备份的数据
[gbase@bogon ~]$ cd /home/gbase/
[gbase@bogon ~]$ ll
total 8
-rw-r--r-- 1 gbase gbase   29 Jan 17 22:06 outtable.data
-rw-r--r-- 1 gbase gbase 3546 Jan 17 21:52 test_db.sql

gbase> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| gbase              |
| gctmpdb            |
| gclusterdb         |
+--------------------+
gbase> \q

[gbase@bogon ~]$ gccli -ugbase -pgbase2011 < /home/gbase/test_db.sql 
[gbase@bogon ~]$ gccli -uroot -p

gbase> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| gbase              |
| gctmpdb            |
| gclusterdb         |
| test               |
+--------------------+

gbase> use test;
gbase> show tables;
+-------------------------+
| Tables_in_test          |
+-------------------------+
| audit_log_express       |
| import_audit_log_errors |
| outtable                |
| t2                      |
| t5                      |
+-------------------------+

导入数据

gbase> use test;
gbase> select * from outtable;
Empty set (Elapsed: 00:00:00.00)

gbase> LOAD DATA INFILE 'file://192.168.61.173/home/gbase/outtable.data' INTO TABLE test.outtable data_format 3 fields terminated by '|';

gbase> select * from outtable;                                                                                                           
+------+----------+
| no   | text     |
+------+----------+
|    2 | 'a\\b'   |
|    1 | 'a\'b'   |
|    3 | 'a\'\\b' |
+------+----------+

LOAD DATA INFILE 'file://192.168.61.173/home/gbase/outtable.GZ' INTO TABLE test.outtable data_format 3 fields terminated by '|' enclosed by '\'';trace 1 trace_path '/home/gbase/loader_logs' FILE_FORMAT GZIP;