达梦数据库学习笔记 — 逻辑备份(dexp/dimp)

原创李真旭 2021-08-30

797

这是达梦数据库学习笔记第7篇。今天学习了解一下dm数据库的逻辑备份和恢复。

针对逻辑备份，达梦数据库也提供了类似Oracle一样的工具叫dexp和dimp；我们先来看看2个工具的功能参数：

[dmdba@mogdb bin]$ ./dexp  help
dexp V8
Format:  ./dexp  KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
 
Example: ./dexp  SYSDBA/SYSDBA GRANTS=Y TABLES=(SYSDBA.TAB1,SYSDBA.TAB2,SYSDBA.TAB3)
 
USERID              Imperative argument and be as the first argument
 
Keyword             Explanation(default value)
--------------------------------------------------------------------------------
USERID              username/password Format:USER/PWD*MPP_TYPE@SERVER:PORT#SSLPATH@SSLPWD
FILE                export file (dexp.dmp)
DIRECTORY           the path of the export file
FULL                export all database (N)
OWNER               export with the user method, Format (user1,user2,...)
SCHEMAS             export with the schema method, Format (schema1,schema2,...)
TABLES              export with the table method, Format (table1,table2,...)
FUZZY_MATCH         whether to support fuzzy matching when export with the table method (N)
QUERY               export with the table sub-set method, the select statement
PARALLEL            the thread number be used to export
TABLE_PARALLEL      the thread number be used to export one table, convert into single thread when MPP
TABLE_POOL          the buffer number of one table
EXCLUDE             ignore appointed objects 
                        Format EXCLUDE=(ROWS,INDEXES,CONSTRAINTS) or
                               EXCLUDE=TABLES:table1,table2 or
                               EXCLUDE=SCHEMAS:sch1,sch2
INCLUDE             include appointed objects 
                        Format INCLUDE=(ROWS,INDEXES,CONSTRAINTS) or 
                               INCLUDE=TABLES:table1,table2
CONSTRAINTS         export constraints (Y)
TABLESPACE          export objects with tablespace (N)
GRANTS              export grants (Y)
INDEXES             export indexes (Y)
TRIGGERS            export triggers (Y)
ROWS                export rows (Y)
LOG                 the log file of the display show
NOLOGFILE           not use log file (N)
NOLOG               log message not display on console (N)
LOG_WRITE           information write into file directly: YES(Y),NO(N)
DUMMY               user message input: print(P), all input YES(Y),all input NO(N) 
PARFILE             argument file name
FEEDBACK            show the process every x rows(0)
COMPRESS            whether to compress export data or not (N)
ENCRYPT             whether to encrypt export data or not (N)
ENCRYPT_PASSWORD    the password of encrypt
ENCRYPT_NAME        the name of encrypt
FILESIZE            the max size of single file
FILENUM             numbers of files of one template
DROP                delete origin table after exported, but no cascade (N)
DESCRIBE            describe of data file, recorded in data file
LOCAL               login with MPP_LOCAL when MPP(N)
HELP                output help information
 
 
[dmdba@mogdb bin]$ ./dimp help
dimp V8
Format:  ./dimp KEYWORD=value or KEYWORD=(value1,value2,...,vlaueN)
 
Example: ./dimp SYSDBA/SYSDBA IGNORE=Y ROWS=Y FULL=Y
 
USERID              Imperative Argument and be as the first argument
 
Keyword             Explanation(default value)
--------------------------------------------------------------------------------
USERID              username/password Format:USER/PWD*MPP_TYPE@SERVER:PORT#SSLPATH@SSLPWD
FILE                import file (dexp.dmp)
DIRECTORY           the path of the import file
FULL                import all database (N)
OWNER               import by owner method, Format (user1,user2,...)
SCHEMAS             import by schema method, Format (schema1,schema2,...)
TABLES              import by table method, Format(table1,table2,...)
PARALLEL            the thread number be used to import
TABLE_PARALLEL      the thread number be used to import one table, valid when FAST_LOAD=Y 
IGNORE              ignore create fail (N)
TABLE_EXISTS_ACTION action when table already exists [SKIP | APPEND | TRUNCATE | REPLACE]
FAST_LOAD           use dmfldr to import data(N)
FLDR_ORDER          need ordered when use dmfldr to import data (Y)
COMMIT_ROWS         the batch committed rows(5000)
EXCLUDE             ignore appointed objects 
                            EXCLUDE=(CONSTRAINTS,INDEXES,ROWS,TRIGGERS,GRANTS)
GRANTS              import grants (Y)
CONSTRAINTS         import constraints (Y)
INDEXES             import indexes (Y)
TRIGGERS            import triggers (Y)
ROWS                import rows (Y)
LOG                 the log file of the display show
NOLOGFILE           not use log file(N)
NOLOG               log message not display on console(N)
LOG_WRITE           information write into file directly(N): YES(Y),NO(N)
DUMMY               user message input(P): print(P), all input Y(Y),all input NO(N) 
PARFILE             argument file name
FEEDBACK            show the process every x rows(0)
COMPILE             compile procedure, package and function ... (Y)
INDEXFILE           write constraints/indexes information into the file
INDEXFIRST          fisrt import indexes,then import data(N)
REMAP_SCHEMA        Format (SOURCE_SCHEMA:TARGET_SCHEMA)
                    make form SOURCE_SCHEMA's data to TARGET_SCHEMA 
ENCRYPT_PASSWORD    the password of encrypt
ENCRYPT_NAME        the name of ENCRYPT
SHOW/DESCRIBE       output the info of the appointed file(N)
LOCAL               login with MPP_LOCAL when MPP(N)
TASK_THREAD_NUMBER  set the number of task thread for dmfldr
BUFFER_NODE_SIZE    set the size of buffer node for dmfldr
TASK_SEND_NODE_NUMBER set the number of send node for dmfldr[16,65535]
LOB_NOT_FAST_LOAD   not use dmfldr when lob in table, dmfldr commits lob data every row
PRIMARY_CONFLICT    way of resolving primary key conflict[IGNORE|OVERWRITE], report error if not setting
TABLE_FIRST         import table first (N)
HELP                output help information

从该工具的帮助信息来看，居然支持parallel操作，其中table_parallel是MPP分布式环境下的参数。简单总结一下有几个几个主要特点：

1）支持表或者用户和表空间级别的数据导出备份

2）支持query查询备份，支持paralle并行备份；

3）支持对象的exclude和include操作

4）支持其他元数据的备份导出，包括权限、index、triggers约束等，属性Y 表示是默认值。

5）支持压缩和加密

6）可以限制dmp文件大小

支持remmap_schema操作，类似Oracle impdp的remap功能，不过不支持remap_table和remap_tablespace；

8）支持batch commit；支持table_exists_action参数，这也是Oracle impdp才有的参数；而且value属性值都一样。

支持远端备份

看上去有点结合了Oracle exp+expdp的功能，不过功能上差别还是很大的；比如不支持通过dblink进行不落地导入；无法对导出和导入任务进行暂时挂起操作。

这里我们通过上述工具来进行一下用户级别的数据备份和恢复操作验证。

1、备份用户数据

[dmdba@mogdb ~]$ dexp benchmarksql/benchmarksql owner=benchmarksql file=benchmarksql.dmp PARALLEL=2 COMPRESS=y log=dexp_benchmarksql.log  
dexp V8
exporting NO. 1 SCHEMA : BENCHMARKSQL 
start export schema[BENCHMARKSQL].....
export NO. 1 SEQUENCE : BMSQL_HIST_ID_SEQ
----- export total 1 SEQUENCE  ----- 
----- export total 0 VIEW  ----- 
----- export total 0 TRIGGER  ----- 
。。。。。。
export total 1 SCHEMA
all the export process spent total  135.221 s
terminate export success without warning
[dmdba@mogdb ~]$ ls -ltr
total 1553140
-rw-r--r--. 1 dmdba dinstall 1590405008 Aug 26 02:43 benchmarksql.dmp
-rw-r--r--. 1 dmdba dinstall       4476 Aug 26 02:43 dexp_benchmarksql.log
[dmdba@mogdb ~]$
SQL> drop user benchmarksql cascade;
executed successfully
used time: 93.033(ms). Execute id is 9806.
SQL> create user benchmarksql identified by benchmarksql;
executed successfully
used time: 7.032(ms). Execute id is 9807.
SQL>  grant resource,dba to benchmarksql;
executed successfully
used time: 12.856(ms). Execute id is 9808.
SQL> select sum(bytes/1024/1024) from dba_segments where owner='BENCHMARKSQL';
LINEID     SUM(BYTES/1024/1024)
---------- --------------------
1          NULL
used time: 259.304(ms). Execute id is 9809.

整体来讲效率还不错。对于一些大对象比如lob之类没有进行测试；估计是快不起来的；原理应该跟Oracle一样，如果不分区，那么可能性能都较差。

这里我们再单独创建个小表通过dexp备份出来，研究一下dmp文件格式：

SQL> create table BENCHMARKSQL.test0826(a int,b varchar2(20));
executed successfully
used time: 73.064(ms). Execute id is 9821.
SQL> insert into BENCHMARKSQL.test0826 values(10,'www.enmotech.com');
affect rows 1
 
used time: 1.464(ms). Execute id is 9822.
SQL> commit;
executed successfully
used time: 2.006(ms). Execute id is 9823.
 
[dmdba@mogdb ~]$ dexp benchmarksql/benchmarksql tables=test0826 file=test0826.dmp PARALLEL=2  log=dexp_test0826.log          
dexp V8
 
[WARNING]FILE "dexp_test0826.log" has already existed
whether to overwrite(y/n, 1/0):y         
 
---- [2021-08-26 03:20:59]export table:TEST0826 -----
 
the privilege of the object at the export mode...
 
table :TEST0826 export terminate, total export 1 rows
 
all the export process spent total    0.081 s
 
terminate export success without warning
 
[dmdba@mogdb ~]$ strings test0826_2.dmp 
TEST0826
TEST0826K
CREATE TABLE "TEST0826"
"A" INT,
"B" VARCHAR2(20)) STORAGE(NOBRANCH) ;
TEST0826
www.enmotech.com
BENCHMARKSQL
BENCHMARKSQL0
TEST0826
[dmdba@mogdb ~]$ od -x -N 5120 test0826.dmp    
0000000 0012 0000 0001 0000 0003 0000 0000 0000
0000020 0000 0000 0000 0000 0000 0000 0000 0000
*
0000400 0000 0000 0000 0000 0000 0000 bd00 0010
0000420 0000 0000 0100 0000 0000 0000 0000 0000
0000440 0000 0000 0000 0000 0000 0000 0000 0000
*
0001440 0001 0000 0000 0000 0000 0000 0000 0000
0001460 0000 0000 0000 0000 0000 0000 0000 0000
*
0002060 0000 0000 0100 00bc 0000 0000 0000 0000
0002100 0000 0000 0000 0000 0000 0000 0000 0000
*
0010000 0002 ffff 05a7 0000 0001 0000 0086 0000
0010020 0000 0000 0800 0000 5400 5345 3054 3238
0010040 0d36 ff00 08ff 0000 5400 5345 3054 3238
0010060 4b36 0000 4300 4552 5441 2045 4154 4c42
0010100 2045 5422 5345 3054 3238 2236 0a0d 0d28
0010120 220a 2241 4920 544e 0d2c 220a 2242 5620
0010140 5241 4843 5241 2832 3032 2929 5320 4f54
0010160 4152 4547 4e28 424f 4152 434e 2948 3b20
0010200 000e ffff 0002 0002 ffff 05a7 0000 0002
0010220 0000 0037 0000 0001 0000 0800 0000 5400
0010240 5345 3054 3238 0236 3100 1030 7700 7777
0010260 652e 6d6e 746f 6365 2e68 6f63 9b6d 78a0
0010300 d5c6 f20c 0085 0000 0010 0000 0000 0c00
0010320 4200 4e45 4843 414d 4b52 5153 0c4c 4200
0010340 4e45 4843 414d 4b52 5153 304c d816 009c
0010360 0000 0010 0000 0000 a700 0005 0800 5400
0010400 5345 3054 3238 0236 0000 0000 0000 0010
0010420 0000 0000 0000 8600 0010 0000 0000 0000
0010437

[dmdba@mogdb ~]$ dexp benchmarksql/benchmarksql tables=test0826 file=test0826_2.dmp PARALLEL=2 COMPRESS=y log=dexp_test0826.log                   
dexp V8
 
---- [2021-08-26 03:06:29]export table:TEST0826 -----
 
the privilege of the object at the export mode...
 
table :TEST0826 export terminate, total export 1 rows
 
all the export process spent total    0.056 s
 
 
terminate export success without warning
 
 
[dmdba@mogdb ~]$ strings test0826_2.dmp |more
X1_Q)Oa(Ga
GO/=x
Xb,p
cb04
`(//
[dmdba@mogdb ~]$ od -x -N 5120 test0826_2.dmp     
0000000 0012 0000 0001 0000 0003 0000 0000 0000
0000020 0000 0000 0000 0000 0000 0000 0000 0000
*
0000400 0000 0000 0000 0000 0000 0000 e500 0010
0000420 0000 0000 0100 0000 0000 0000 0000 0000
0000440 0000 0000 0000 0000 0000 0000 0000 0000
*
0001420 0000 0000 0000 0000 0001 0000 0000 0000
0001440 0001 0000 0000 0000 0000 0000 0000 0000
0001460 0000 0000 0000 0000 0000 0000 0000 0000
*
0002060 0000 0000 0100 00e5 0000 0000 0000 0000
0002100 0000 0000 0000 0000 0000 0000 0000 0000
*
0010000 0002 ffff 05a7 0000 0001 0000 009e 0000
0010020 0000 0000 1000 0000 7800 0b9c 0d71 310e
0010040 30b0 0332 0a00 0223 7811 e39c f865 5fff
0010060 8180 a181 0e62 2177 9f2f 06e1 2303 0666
0010100 652e c126 b040 3158 515f 4f29 2861 6147
0010120 a7c9 d0c2 2e00 8aa8 47e0 2f4f 783d 541a
0010140 1214 e1f4 f70b c645 734d 88a6 921c 9e69
0010160 2b63 dcd3 d263 63c3 2a62 e4da a7fc e659
0010200 ac6e 9fe9 733f 649b cefe 35b1 5d11 58c3
0010220 2c62 9070 680f 1319 0003 f6ac 8f2a 0002
0010240 ffff 05a7 0000 0002 0000 0047 0000 0001
0010260 0000 1000 0000 7800 0b9c 0d71 310e 30b0
0010300 0332 0a00 0223 7811 639c 3062 1034 2860
0010320 2f2f 4bd7 cbcd 2fcd 4d49 d0ce ce4b 05cf
0010340 3f00 068f 9bc7 78a0 d5c6 f20c 0085 0000
0010360 0010 0000 0000 1400 7800 739c f572 f673
0010400 75f0 f20c 0e0e 01f4 1600 0310 147c 7800
0010420 739c f572 f673 75f0 f20c 0e0e 01f4 1600
0010440 0310 307c d816 009c 0000 0010 0000 0000
0010460 a700 0005 1000 7800 0b9c 0d71 310e 30b0
0010500 0332 0a00 0223 0211 0000 0000 0000 0010
0010520 0000 0000 0000 9e00 0010 0000 0000 0000
0010537

如果使用compress压缩选项；文件存储格式看上去要复杂一些，具体算法不清楚。如果不进行compress压缩；看上去就是单纯的16进制文件；文件前面部分是一些通用信息，后面才是表数据。通过对比发现好像就是处理了后面表数据部分内容。

如果不压缩，那么即使dmp损坏，应该也比较容易处理。

达梦我和达梦的故事

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

达梦数据库学习笔记 — 逻辑备份(dexp/dimp)

评论