当前位置：首页 >

GBase 8a 数据导入导出

发布时间：2024/1/1 43 豆豆

生活随笔收集整理的这篇文章主要介绍了 GBase 8a 数据导入导出小编觉得挺不错的,现在分享给大家,帮大家做个参考.

表结构导出工具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 begindeclare errno int;declare msg text;declare exit handler for sqlexceptionbeginget 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_typefrom 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> 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;

总结

以上是生活随笔为你收集整理的GBase 8a 数据导入导出的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。

数据
GBase