how_to_use_exp_or_imp_of_gauss100
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
gauss100的exp或imp数据导出导入工具不是在os层运行；而是在zsql命令行中执行；首先我们获取相关帮助信息：
|
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 |
SQL> exp -h; The syntax of logic export is: Format: EXP KEYWORD=value or KEYWORD=value1,value2,...,valueN; Example: EXP TABLES=EMP,DEPT,MGR; or EXP USERS=USER_A,USER_B; or EXP DIST_RULES=RULE_1,RULE_2; Keyword Description (Default) -------------------------------------------------------------------------------------------------- USERS List of schema names. Specify a percent sign (%) to export all users. TABLES List of table names. Specify a percent sign (%) to export all tables. DIST_RULES List of distribute rule names. Specify a percent sign (%) to export all distribution rules. Supported only for sharding. TABLESPACE_FILTER List of tablespace names, the data or objects in these tablespaces will be exported. Case-sensitive words enclosed by '`' or '"'. FILE Output file (EXPDAT.DMP) FILETYPE Output file type: (TXT), BIN LOG Log file of screen output COMPRESS Compress output file (0), only for FILETYPE=BIN, values is 0~9, litter for faster compress speed, 0 is not compressed. CONTENT Specifies data to unload where the valid keyword, values are: (ALL), DATA_ONLY, and METADATA_ONLY. QUERY Predicate clause used to export a subset of a table, eg. "where rownum <= 10" SKIP_COMMENTS Do not add comments to dump file. (N) FORCE Continue even if an SQL error occurs during a table dump. (N) SKIP_ADD_DROP_TABLE Do not add a DROP TABLE statement before each CREATE TABLE statement. (N) SKIP_TRIGGERS Do not dump triggers. (N) QUOTE_NAMES Quote identifiers. (Y) TABLESPACE Default transport all tablespaces except for system reserved. (N) COMMIT_BATCH Batch commit rows, commit once if set 0. (1000) INSERT_BATCH Batch insert rows. (1) FEEDBACK Feedback row count, feedback once if set 0 (10000) PARALLEL Table data export parallelism settings, range 2~16, The default value is 0 CONSISTENT Cross - table consistency(N) CREATE_USER Export user definition(N),Used in conjunction with USERS. ROLE Export user roles expect system preset roles (N),Used in conjunction with USERS. GRANT Grant role and pemission to USER (N),Used in conjunction with USERS and ROLE. WITH_CR_MODE Export tables and indexes with CR_MODE options (N) ENCRYPT Export files will be encrypted. REMAP_TABLES Table's name will remapped to another tablename. |
从exp -h来看，帮助选型非常明确了。从上述信息来看，exp只能针对tablespace级别、用户级别、表级别进行备份；可并行；可设置dmp加密，也可以进行压缩备份；同时支持一定过滤规则；也支持batch模式。其中需要主要的是默认不会导出相关权限（grant/role）。下面进行相关测试。
+++备份用户 (默认dmp类型为txt）
|
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 |
SQL> exp users=roger CONTENT=all file="test.dmp" PARALLEL=2; Parsing export options ... Verify options ... verify schema ... Starting export ... Preparing to export ... -- EXPORT TYPE = SCHEMA -- EXPORT OBJECTS = ROGER -- FILE TYPE = TXT -- DUMP FILE = test.dmp -- LOG FILE = -- QUERY = "" -- COMPRESS = N -- CONSISTENT = N -- CONTENT_MODE = ALL -- SKIP_COMMENTS = N -- FORCE = N -- SKIP_ADD_DROP_TABLE = N -- SKIP_TRIGGERS = N -- QUOTE_NAMES = Y -- TABLESPACE = N -- COMMIT_BATCH = 1000 -- INSERT_BATCH = 1 -- FEEDBACK = 10000 -- PARALLEL = 2 -- CREATE_USER = N -- ROLE = N -- GRANT = N -- WITH_CR_MODE = N Exporting schema ROGER ... Exporting sequence of schema ROGER ... Exporting tables of schema ROGER ... Reading table objects of ROGER The order of exporting table is: TABLE NAME LEVEL ---------------------------------------------------------------- ---------- TEST 1 Exporting tables (scripts or data) of ROGER exporting table ROGER.TEST ... exporting DDL of ROGER.TEST ... exporting indexes on ROGER.TEST ... exporting constraints on ROGER.TEST ... Exporting procedures/functions/triggers of schema ROGER ... Exporting views of schema ROGER ... End of export schema ROGER ... Logical export succeeded. |
在没有压缩的情况下，可以直接strings查看表结构，相对来讲dmp结构比较简单：
|
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 |
[roger@mysqldb GaussDB_100_1.0.1-TOOLS]$ strings test.dmp --** The script is dumped by *ZSQL/EXP* tool, Zenith@Huawei Gauss Dept. --** Dumped time: 2020-02-14 11:53:58.708 -- EXPORT TYPE = SCHEMA -- EXPORT OBJECTS = ROGER -- FILE TYPE = TXT -- DUMP FILE = test.dmp -- LOG FILE = -- QUERY = "" -- COMPRESS = N -- CONSISTENT = N -- CONTENT_MODE = ALL -- SKIP_COMMENTS = N -- FORCE = N -- SKIP_ADD_DROP_TABLE = N -- SKIP_TRIGGERS = N -- QUOTE_NAMES = Y -- TABLESPACE = N -- COMMIT_BATCH = 1000 -- INSERT_BATCH = 1 -- FEEDBACK = 10000 -- PARALLEL = 2 -- CREATE_USER = N -- ROLE = N -- GRANT = N -- WITH_CR_MODE = N ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'; ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'; ALTER SESSION SET NLS_TIME_FORMAT = 'HH:MI:SS.FF AM'; ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH:MI:SS.FF AM TZR'; ALTER SESSION SET CURRENT_SCHEMA = ROGER; DROP TABLE IF EXISTS "TEST" CASCADE CONSTRAINTS; CREATE TABLE "TEST" "A" NUMBER, "B" VARCHAR(20 BYTE) TABLESPACE "USERS" INITRANS 2 MAXTRANS 255 PCTFREE 8; @@ data/_3F202DA1867E08580000.D @@ data/_D6232DA186EE86A40100.D CREATE INDEX "IDX_TEST_ID" ON "TEST"("A") TABLESPACE "USERS" INITRANS 2 PCTFREE 8; -- end of exp: 2020-02-14 11:53:59.097 [roger@mysqldb GaussDB_100_1.0.1-TOOLS]$ cd data/ [roger@mysqldb data]$ ls -ltr total 540 -rw------- 1 roger roger 275265 Feb 14 11:53 _3F202DA1867E08580000.D -rw------- 1 roger roger 273831 Feb 14 11:53 _D6232DA186EE86A40100.D [roger@mysqldb data]$ strings _3F202DA1867E08580000.D | more INSERT INTO "TEST" ("A","B") values (1,'enmotech.com'); INSERT INTO "TEST" ("A","B") values (2,'killdb.com'); INSERT INTO "TEST" ("A","B") values (1,'killdb'); ..... |
我们可以看到；在没有压缩的情况之下，dmp文件只包含表/索引等结构元数据，具体数据是单独存放在数据导出目录/data下的后缀为.Dde文件中。其中该文件中全是SQL文本。可以直接执行。
+++++ exp dmp为二进制模式
|
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 |
SQL> exp users=roger CONTENT=all file="test.dmp" PARALLEL=2 FILETYPE=BIN; ....... Exporting tables (scripts or data) of ROGER exporting table ROGER.TEST ... exporting DDL of ROGER.TEST ... data exporting success! 10002 rows are dumped. exporting indexes on ROGER.TEST ... exporting constraints on ROGER.TEST ... Exporting procedures/functions/triggers of schema ROGER ... Exporting views of schema ROGER ... End of export schema ROGER ... Logical export succeeded. [roger@mysqldb GaussDB_100_1.0.1-TOOLS]$ strings test.dmp -- EXPORT TYPE = SCHEMA -- EXPORT OBJECTS = ROGER -- FILE TYPE = BIN ...... -- WITH_CR_MODE = N ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'; ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'; ALTER SESSION SET NLS_TIME_FORMAT = 'HH:MI:SS.FF AM'; ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH:MI:SS.FF AM TZR'; ROGER7 ALTER SESSION SET CURRENT_SCHEMA = ROGER; DROP TABLE IF EXISTS "TEST" CASCADE CONSTRAINTS; CREATE TABLE "TEST" "A" NUMBER, "B" VARCHAR(20 BYTE) TABLESPACE "USERS" INITRANS 2 MAXTRANS 255 PCTFREE 8; TEST _9F1930BF86EE54EA0000.D _018231BF86D7FE080100.DS CREATE INDEX "IDX_TEST_ID" ON "TEST"("A") TABLESPACE "USERS" INITRANS 2 PCTFREE 8; [roger@mysqldb GaussDB_100_1.0.1-TOOLS]$ [roger@mysqldb GaussDB_100_1.0.1-TOOLS]$ cd data/ [roger@mysqldb data]$ ls -ltr total 740 -rw------- 1 roger roger 275265 Feb 14 11:53 _3F202DA1867E08580000.D -rw------- 1 roger roger 273831 Feb 14 11:53 _D6232DA186EE86A40100.D -rw------- 1 roger roger 100514 Feb 14 12:02 _9F1930BF86EE54EA0000.D -rw------- 1 roger roger 99582 Feb 14 12:02 _018231BF86D7FE080100.D [roger@mysqldb data]$ strings _9F1930BF86EE54EA0000.D|head -10 TEST enmotech.com killdb.com killdb killdb killdb |
我们可以看到；exp导出数据如果为bin模式，对于产生的dmp文件，我们仍然可以获取到表结构；但是其中.D文件中的实际数据则无法直接导入到数据库中；需要进行单独处理。（如果dmp损坏的话）。
++++如果启用压缩选项呢？
SQL> exp users=roger CONTENT=all file=”test.dmp” PARALLEL=2 FILETYPE=BIN compress=9;
这里的compress取值范围是0-9；数据越高表现压力比例越大；当然，压力比例越大，表现备份数据越慢；反之亦然。
|
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 |
[roger@mysqldb GaussDB_100_1.0.1-TOOLS]$ strings test.dmp -- EXPORT TYPE = SCHEMA -- EXPORT OBJECTS = ROGER -- FILE TYPE = BIN -- DUMP FILE = test.dmp -- LOG FILE = -- QUERY = "" -- COMPRESS = Y -- CONSISTENT = N -- CONTENT_MODE = ALL -- SKIP_COMMENTS = N -- FORCE = N -- SKIP_ADD_DROP_TABLE = N -- SKIP_TRIGGERS = N -- QUOTE_NAMES = Y -- TABLESPACE = N -- COMMIT_BATCH = 1000 -- INSERT_BATCH = 1 -- FEEDBACK = 10000 -- PARALLEL = 2 -- CREATE_USER = N -- ROLE = N -- GRANT = N -- WITH_CR_MODE = N ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'; ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'; ALTER SESSION SET NLS_TIME_FORMAT = 'HH:MI:SS.FF AM'; ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH:MI:SS.FF AM TZR'; ROGER7 ALTER SESSION SET CURRENT_SCHEMA = ROGER; DROP TABLE IF EXISTS "TEST" CASCADE CONSTRAINTS; CREATE TABLE "TEST" "A" NUMBER, "B" VARCHAR(20 BYTE) TABLESPACE "USERS" INITRANS 2 MAXTRANS 255 PCTFREE 8; TEST _AF7F5FDD865BDDBC0000.D _D09160DD868EE7490100.DS CREATE INDEX "IDX_TEST_ID" ON "TEST"("A") TABLESPACE "USERS" INITRANS 2 PCTFREE 8; [roger@mysqldb GaussDB_100_1.0.1-TOOLS]$ cd data/ [roger@mysqldb data]$ strings _AF7F5FDD865BDDBC0000.D |head -10 Z@X X`X XPX0XpX XHX(XhX XXX8XxX XDX$XdX XTX4XtX XLX,XlX X\X<X|X XBX"XbX XRX2XrX |
我们可以看到，启用压缩模式后，.D文件的内容基本上就是乱码了。如果dmp真的有问题，势必增加了恢复难度。
另外我们需要注意的是，默认情况下，exp是不会导出权限和角色的。
+++ roger用户模拟100w条数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> declare 2 i number; 3 begin 4 for i in 1..10000 loop 5 insert into test values(i,'killdb'); 6 end loop; 7 end; 8 / SQL> exp users=roger CONTENT=ALL file="test_batch.dmp" PARALLEL=2 TABLESPACE=y ROLE=y GRANT=y; SQL> exp users=roger CONTENT=all file="test_batch_1000.dmp" PARALLEL=2 TABLESPACE=y ROLE=y GRANT=y COMMIT_BATCH=1000 INSERT_BATCH=1000; SQL> exp users=roger CONTENT=all file="test_batch_10000.dmp" PARALLEL=2 TABLESPACE=y ROLE=y GRANT=y COMMIT_BATCH=10000 INSERT_BATCH=10000; |
下面进行imp导入：
|
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 |
SQL> imp file="test_batch.dmp" REMAP_SCHEMA=roger:enmo1 PARALLEL=2 LOG="test_batch_imp.log"; Parsing import options ... Verify options ... verify remap schema ... Starting import ... Preparing to import ... -- IMPORT TYPE = REMAP_SCHEMA -- REMAP SCHEMA = ROGER:enmo1 -- DUMP FILE = test_batch.dmp -- LOG FILE = test_batch_imp.log -- FILE TYPE = TXT -- SHOW = N -- FEEDBACK = 10000 -- PARALLEL = 2 -- DDL_PARALLEL = 1 -- CONTENT_MODE = ALL -- IGNORE = N -- CREATE_USER = N -- TIMING = OFF -- BATCH_COUNT = 10000 -- DISABLE_TRIGGER = Y -- NOLOGGING = N 10000 rows are committed ...... 490000 rows are committed 490000 rows are committed 500000 rows are committed data importing success, 1001001 rows are loaded. Logical import succeeded. SQL> imp file="test_batch_10000.dmp" REMAP_SCHEMA=roger:enmo1 PARALLEL=2 BATCH_COUNT=50000 LOG="test_batch_imp_10000.log"; Parsing import options ... Verify options ... GS-00601, Sql syntax error: BATCH_COUNT should be in [1, 10000] Logical import failed. |
默认值 batch_count为10000；其取值范围为0-10000；即默认值也是最大值。
Leave a Reply
You must be logged in to post a comment.