达梦数据库安装部署(命令行模式安装)
环境:
OS:Centos 7
DB:dm8
1.创建安装用户
groupadd dinstall
useradd -g dinstall dmdba
passwd dmdba
密码设置为dameng
2. 修改操作系统限制
vi /etc/security/limits.conf
dmdba hard nofile 65536
dmdba soft nofile 65536
dmdba hard stack 32768
dmdba soft stack 16384
然后执行sysctl -p生效
[root@host85 soft]# sysctl -p
4.关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
5.安装依赖包
yum install glibc
yum install libXp
yum install libXt
yum install libXtst
yum install unzip
6.解压安装包并挂载镜像文件
[root@localhost soft]# unzip dm8_20220525_x86_rh6_64.zip
[root@localhost soft]# cd dm8_20220525_x86_rh6_64_ent/
[root@localhost dm8_20220525_x86_rh6_64_ent]# mount -o loop dm8_20220525_x86_rh6_64.iso /mnt
##解压后的文件
[root@localhost mnt]# pwd
/mnt
[root@localhost mnt]# ls
DM8 Install.pdf DMInstall.bin
7.创建目录
[root@localhost /]#mkdir -p /dmdbms/product --数据库软件的安装目录
[root@localhost /]#mkdir -p /dmdbms/data --数据目录
[root@localhost /]#mkdir -p /dmdbms/arch --归档目录
[root@localhost /]#mkdir -p /dmdbms/backup --备份目录
[root@localhost /]#chown -R dmdba:dinstall /dmdbms --授权dmdbms给dmdba用户
8.安装数据库软件
[root@localhost mnt]# su - dmdba
[dmdba@localhost ~]$ cd /mnt
[dmdba@localhost mnt]$./DMInstall.bin -i
Please select the installer's language (E/e:English C/c:Chinese) [E/e]:e
Extract install files.........
Welcome to DM DBMS Installer
Whether to input the path of Key File? (Y/y:Yes N/n:No) [Y/y]:n
Whether to Set The TimeZone? (Y/y:Yes N/n:No) [Y/y]:y
TimeZone:
[ 1]: GTM-12=West Date Line
[ 2]: GTM-11=Samoa
[ 3]: GTM-10=Hawaii
[ 4]: GTM-09=Alaska
[ 5]: GTM-08=Pacific(America and Canada)
[ 6]: GTM-07=Arizona
[ 7]: GTM-06=Central(America and Canada)
[ 8]: GTM-05=East(America and Canada)
[ 9]: GTM-04=Atlantic(America and Canada)
[10]: GTM-03=Brasilia
[11]: GTM-02=Middle Atlantic
[12]: GTM-01=Azores
[13]: GTM=Greenwich Mean Time
[14]: GTM+01=Sarajevo
[15]: GTM+02=Cairo
[16]: GTM+03=Moscow
[17]: GTM+04=AbuDhabi
[18]: GTM+05=Islamabad
[19]: GTM+06=Dakar
[20]: GTM+07=BangKok,Hanoi
[21]: GTM+08=China
[22]: GTM+09=Seoul
[23]: GTM+10=Guam
[24]: GTM+11=Solomon
[25]: GTM+12=Fiji
[26]: GTM+13=Nukualofa
[27]: GTM+14=Kiribati
Please Select the TimeZone [21]:21
Installation Type:
1 Typical
2 Server
3 Client
4 Custom
Please Input the number of the Installation Type [1 Typical]:1
Require Space: 1585M
Please Input the install path [/home/dmdba/dmdbms]:/dmdbms/product
Available Space:117G
Please Confirm the install path(/dmdbms/product)? (Y/y:Yes N/n:No) [Y/y]:y
Pre-Installation Summary
Installation Location: /dmdbms/product
Require Space: 1585M
Available Space: 117G
Version Information:
Expire Date:
Installation Type: Typical
Confirm to Install? (Y/y:Yes N/n:No):y
2022-07-08 14:33:06
[INFO] Installing DM DBMS...
2022-07-08 14:33:06
[INFO] Installing BASE Module...
2022-07-08 14:33:29
[INFO] Installing SERVER Module...
2022-07-08 14:33:33
[INFO] Installing CLIENT Module...
2022-07-08 14:33:43
[INFO] Installing DRIVERS Module...
2022-07-08 14:34:04
[INFO] Installing MANUAL Module...
2022-07-08 14:34:07
[INFO] Installing SERVICE Module...
2022-07-08 14:34:08
[INFO] Move log file to log directory.
2022-07-08 14:34:09
[INFO] Installed DM DBMS completely.
Please execute the commands by root:
/dmdbms/product/script/root/root_installer.sh
End
根据提示在root账号下执行脚本
[root@localhost product]# /dmdbms/product/script/root/root_installer.sh
Move /dmdbms/product/bin/dm_svc.conf to /etc
Modify the files' mode of DM Server
Create the DmAPService service
Created symlink from /etc/systemd/system/multi-user.target.wants/DmAPService.service to /usr/lib/systemd/system/DmAPService.service.
Finished to create the service (DmAPService)
Start the DmAPService service
查看服务
[root@localhost product]# systemctl status DmAPService.service
● DmAPService.service - DM Assistant Plug-In Service(DmAPService).
Loaded: loaded (/usr/lib/systemd/system/DmAPService.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2022-07-08 14:35:24 CST; 19s ago
Process: 21524 ExecStart=/dmdbms/product/bin/DmAPService start (code=exited, status=0/SUCCESS)
Main PID: 21550 (dmap)
CGroup: /system.slice/DmAPService.service
└─21550 /dmdbms/product/bin/dmap
Jul 08 14:35:09 localhost.localdomain systemd[1]: Starting DM Assistant Plug-In Service(DmAPService)....
Jul 08 14:35:24 localhost.localdomain DmAPService[21524]: [33B blob data]
Jul 08 14:35:24 localhost.localdomain systemd[1]: Started DM Assistant Plug-In Service(DmAPService)..
10.dmdba账号设置环境变量
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/dmdbms/product/bin"
export DM_HOME="/dmdbms/product"
export PATH=$DM_HOME/bin:$DM_HOME/tool:$PATH
9.初始化数据库与注册服务
或是一个命令执行
su - dmdba
dminit path=/dmdbms/data charset=1 db_name=slnngk instance_name=slnngk SYSDBA_PWD="dameng123" SYSAUDITOR_PWD="dameng123"
dminit path=/dmdbms/data charset=1 case_sensitive=0 length_in_char=0 db_name=slnngk instance_name=slnngk SYSDBA_PWD="dameng123" SYSAUDITOR_PWD="dameng123"
说明:
1. 参数和值之间不能有空格
2.交换模式创建数据库或图形界面创建的数据库,系统设置的db_name和instance_name都是DAMENG, 注册服务的-p参数是DMSERVER
[root@host02 root]# cd /dmdbms/product/script/root
[root@host02 root]# ./dm_service_installer.sh -t dmserver -dm_ini /dmdbms/data/DAMENG/dm.ini -p DMSERVER
[root@host01 root]# systemctl start DmServiceDMSERVER.service
[root@host01 root]# systemctl status DmServiceDMSERVER.service
charset:字符集 值:0[GB18030],1[UTF-8],2[EUC-KR]
case_sensitive:大小写是否敏感 1敏感 0不敏感,现有库查看 取值:Y、y、1 表示敏感;N、n、0 表示不敏感。缺省值为 Y
SQL> select PARA_VALUE from v$dm_ini where PARA_NAME like '%CASE_SENSITIVE%';
LINEID PARA_VALUE
---------- ----------
1 1
LENGTH_IN_CHAR:varchar中定义的数字是字符为单位还是字节为单位 1字符为单位 0字节为单位 默认值0,现有库查看
SQL> select name, value from SYS."V$PARAMETER" t where t.NAME='LENGTH_IN_CHAR';
LINEID NAME VALUE
---------- -------------- -----
1 LENGTH_IN_CHAR 0
su - dmdba
[dmdba@localhost bin]$ cd /dmdbms/product/bin
[dmdba@localhost bin]$ ./dminit
initdb V8
db version: 0x7000c
file dm.key not found, use default license!
License will expire on 2023-05-25
input system dir: /dmdbms/data
input db name: slnngk
input port num: 5236
input page size(4, 8, 16, 32): 8
input extent size(16, 32, 64): 16
input sec priv mode(0, 1, 2): 0
input time zone(-12:59,+14:00): +8
string case sensitive? ([Y]es, [N]o): y
which charset to use? (0[GB18030], 1[UTF-8], 2[EUC-KR]): 1
length in char? ([Y]es, [N]o): n
enable database encrypt? ([Y]es, [N]o): n
input slice size(0, 512, 4096): 4096
page check mode? (0/1/2): 0
input elog path: /tmp/elog
only create huge table with delta? (0/1): 1
rlog generate for huge? (0/1): 0
pseg_mgr_flag (0/1): 0
auto_overwrite mode? (0/1/2): 0
CHARACTER type fixed storage ? ([Y]es/1, [N]o/0): n
SQL log forbid ? ([Y]es/1, [N]o/0): n
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
log file path: /dmdbms/data/slnngk/slnngk01.log
log file path: /dmdbms/data/slnngk/slnngk02.log
write to dir [/dmdbms/data/slnngk].
create dm database success. 2022-07-08 15:23:17
下面注册服务
切换至注册入口路径,执行命令,注册服务
需要用操作系统root账号登陆注册
[root@localhost root]#cd /dmdbms/product/script/root
[root@localhost root]# ./dm_service_installer.sh -t dmserver -dm_ini /dmdbms/data/slnngk/dm.ini -p slnngk
Created symlink from /etc/systemd/system/multi-user.target.wants/DmServiceslnngk.service to /usr/lib/systemd/system/DmServiceslnngk.service.
Finished to create the service (DmServiceslnngk)
启动服务
[root@localhost root]#systemctl start DmServiceslnngk.service
[root@localhost root]# systemctl status DmServiceslnngk.service
不需要服务方式启动的话,可以使用如下命令启动
su - dmdba
nohup /dmdbms/product/bin/dmserver path=/dmdbms/data/slnngk/dm.ini &
注册了服务的话,还可以使用如下方式启动
su - dmdba
cd /dmdbms/product/bin
[dmdba@host135 bin]$ ./DmServiceslnngk start
./DmServiceslnngk: line 238: ulimit: core file size: cannot modify limit: Operation not permitted
Starting DmServiceslnngk:
[ OK ]
解决办法(root账号操作):
vi /etc/security/limits.conf
添加如下两项
dmdba hard core unlimited
dmdba soft core unlimited
10.登录
验证是否安装成功,登录可以使用windows客户端,但我没有安装,我们就用服务器上的客户端进行登录.
服务启动成功后,登录达梦数据库客户端进行验证:
用户名:SYSDBA 密码:SYSDBA 端口:5236
[root@localhost dmdb]# su - dmdba
[dmdba@localhost tool]$ /dmdbms/product/tool/disql
disql V8
SQL> connect sysdba
password:
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 2.886(ms)
SQL> select username from dba_users;
LINEID USERNAME
---------- ----------
1 SYSAUDITOR
2 SYSSSO
3 SYSDBA
4 SYS
sys -----达梦数据库内置管理用户,不能登录数据库,数据库使用的大部分的数据字典和动态性能视图sys
sysdba -----数据库的管理员
sysauditor ---审计用户
syssso ---安全用户
这里可以修改数据库账号密码
alter user sysdba identified by "dameng123";
alter user sys identified by "dameng123";
alter user SYSSSO identified by "dameng123"; ##修改不了,也不能登陆
alter user SYSAUDITOR identified by "dameng123"; ##该账号修改密码需要使用该账号登陆,默认密码是:SYSAUDITOR
11.客户端工具登陆
下载windos客户端工具,通过该工具进行连接
dm8_20220519_x86_win_64_ent.zip
12.数据库开启归档模式(命令模式修改)
例如开启归档并限制归档空间为 200 GB,如下所示:
disql sysdba/dameng123
alter database mount;
alter database add archivelog 'dest=/dmdbms/arch,TYPE=local,FILE_SIZE=1024';
alter database archivelog;
alter database open;
说明:
a.ARCH_FILE_SIZE = 1024 #单个归档文件大小,单位MB,取值范围(64~2048),默认为1024MB,即1G
b.ARCH_SPACE_LIMIT = 4096 #归档文件空间限制,单位MB,取值范围(1024~4294967294),0表示无空间限制
c.设置了归档后系统会自动修改dm.ini配置文件中的参数
ARCH_INI = 1 #dmarch.ini
查看是否配置归档
select name,arch_mode from v$database;
查看是否归档,及归档的空间,路径
select arch_name,arch_type,arch_dest,arch_file_size,arch_space_limit from v$dm_arch_ini;
查看数据库实例名,状态,主机名
select instance_name,status$,host_name from v$instance;
归档切换
alter system archive log current;
13.配置文件参数vim dmmal.ini(用于后续的主备部署不需要重启主库)
[dmdba@host01 DAMENG]$ more dmmal.ini
#DaMeng Database Mail Configuration file
#this is comments
MAL_CHECK_INTERVAL = 5
MAL_COMBIN_BUF_SIZE = 0
MAL_SEND_THRESHOLD = 2048
MAL_CONN_FAIL_INTERVAL = 5
MAL_LOGIN_TIMEOUT = 15
MAL_BUF_SIZE = 100
MAL_SYS_BUF_SIZE = 0
MAL_VPOOL_SIZE = 128
MAL_COMPRESS_LEVEL = 0
MAL_TEMP_PATH =
[MAL_INST1]
MAL_INST_NAME = SLNNGK
MAL_HOST = 192.168.1.101
MAL_PORT = 61141
MAL_INST_HOST = 192.168.1.101
MAL_INST_PORT = 5236
MAL_DW_PORT = 52141
MAL_LINK_MAGIC = 0
MAL_INST_DW_PORT = 33141
注意这里需要将dm.ini文件如下设置打开
MAL_INI = 1 #dmmal.ini
或是使用系统过程进行打开设置
SP_SET_PARA_VALUE(2, 'MAL_INI', 1);
这个配置需要重启数据库的
systemctl restart DmServiceslnngk.service
13.日志目录
日志目录存放在数据库软件的安装目录下
[root@localhost log]# ls -al
total 384
drwxr-xr-x 2 dmdba dinstall 205 Jul 8 15:35 .
drwxr-xr-x 16 dmdba dinstall 251 Jul 8 14:34 ..
-rw-r--r-- 1 dmdba dinstall 0 Jul 8 14:35 DmAPService.log
-rw-r--r-- 1 dmdba dinstall 162 Jul 8 14:35 dm_dmap_202207.log
-rw-rw-r-- 1 dmdba dinstall 26381 Jul 8 15:56 dm_DMSERVER_202207.log
-rw-r--r-- 1 dmdba dinstall 5206 Jul 8 15:56 DmServiceDMSERVER.log
-rw-r--r-- 1 dmdba dinstall 388 Jul 8 15:35 dmsvc_sh.log
-rw-rw-r-- 1 dmdba dinstall 734 Jul 8 15:35 dm_unknown_202207.log
-rw-rw-r-- 1 dmdba dinstall 339650 Jul 8 14:34 install_ant.log
-rwxr-xr-x 1 dmdba dinstall 504 Jul 8 14:34 install.log
[root@localhost log]# pwd
/dmdbms/product/log
14.创建schema(与oracle一样)
创建表空间
create tablespace tps_hxl datafile '/dmdbms/data/slnngk/tps_hxl01.DBF' size 1024 autoextend on maxsize 32768; ##这里单位是M,文件目录存储在/dmdata/dmdb/DAMENG,这里设置文件最大值,不设置的话就是16TB
alter tablespace tps_hxl add datafile '/dmdbms/data/slnngk/tps_hxl02.DBF' size 1024 autoextend on;
alter tablespace tps_hxl add datafile '/dmdbms/data/slnngk/tps_hxl03.DBF' size 1024; ##默认就是自动扩展的
drop tablespace tps_hxl; ##删除表空间,数据文件一并删除掉了
create user hxl identified by dameng123 default tablespace tps_hxl default index tablespace tps_hxl;
grant dba to hxl;
使用新创建的用户的登录
disql hxl/dameng123
开发者权限
grant resource to hxl;
grant public to hxl;
grant VTI to hxl;
grant SELECT ANY VIEW to hxl; ##查询sys下的对象,比如sys.dba_segments
GRANT soi TO hxl;
–内置角色分类
DB_AUDIT 开头为审计相关角色,默认赋给了 SYSAUDITOR;
DB_POLICY 开头的为安全相关角色,默认赋给 SYSSSO;
其他 DBA、RESOURCE、PULIBC、SOI、VTI 等默认赋给了 SYSDBA。
DBA:系统管理员角色,拥有除审计和强制访问控制之外的几乎所有权限,
RESOURCE:拥有 CREATE 创建表、索引、视图等对象定义的权限和数据操作权限(DML 操作)。
PULIBC:拥有数据操作权限(增删改查操作),没有创建表、视图等对象定义权限。
SOI:具有查询系统表(SYS 开头的)查询权限。
VTI 具有查询动态视图(v$开头的)权限。
设置密码永不过期
alter user hxl limit password_life_time unlimited;
查看用户密码是否过期
select u.username,p.FAILED_NUM,p.life_time from SYSUSERS p,dba_users u where p.FAILED_NUM not in ('0') order by 1,2 ;
##life_time为0,表示不限制密码过期天数
15.创建表并写入数据
create table tb_test
(
id number,
name varchar(32)
);
insert into tb_test values(1,'name1');
insert into tb_test values(2,'name2');
insert into tb_test values(3,'name3');
insert into tb_test values(4,'name4');
insert into tb_test values(5,'name5');
获取表的ddl
select dbms_metadata.get_ddl ('TABLE','TB_TEST','HXL') FROM DUAL;
SQL> select dbms_metadata.get_ddl ('TABLE','TB_TEST','HXL') FROM DUAL;
LINEID DBMS_METADATA.GET_DDL('TABLE','TB_TEST','HXL')
---------- ------------------------------------------------------------------------------------------------------
1 CREATE TABLE "HXL"."TB_TEST"
(
"ID" NUMBER,
"NAME" VARCHAR(32)) STORAGE(ON "TPS_HXL", CLUSTERBTR) ;
used time: 77.344(ms). Execute id is 813
获取通过索引名获取索引ddl
SQL> select dbms_metadata.get_ddl ('INDEX','IDX_ID','HXL') FROM DUAL;
LINEID DBMS_METADATA.GET_DDL('INDEX','IDX_ID','HXL')
---------- ---------------------------------------------------------------------------------------
1 CREATE INDEX "IDX_ID" ON "HXL"."TB_TEST"("ID" ASC) STORAGE(ON "TPS_HXL", CLUSTERBTR) ;
used time: 4.024(ms). Execute id is 818.
SQL>
create table tb_test_01
(
id bigint identity(1,1) primary key, ##类似mysql的AUTO_INCREMENT
name varchar(32),
createdate datetime default sysdate,
updatetime datetime default sysdate,
);
insert into tb_test02(id,name) values(1,'name1');
insert into tb_test02(id,name) values(2,'name2');
insert into tb_test02(id,name) values(3,'name3');
insert into tb_test02(id,name) values(4,'name4');
insert into tb_test02(id,name) values(5,'name5');
16.开发者只需要的权限
revoke dba from hxl;
grant resource to hxl;
grant public to hxl;
17.修改连接数
修改dm.ini文件,找到如下项目进行修改
#database
MAX_SESSIONS = 3000 #Maximum number of concurrent sessions
然后进行重启动
systemctl stop DmServiceDMSERVER.service
systemctl start DmServiceDMSERVER.service
查看
disql sysdba/dameng123
SQL> SELECT SF_GET_PARA_VALUE (1, 'MAX_SESSIONS');
LINEID SF_GET_PARA_VALUE(1,'MAX_SESSIONS')
---------- -----------------------------------
1 3000
used time: 11.475(ms). Execute id is 500.
18.查看数据库字符集
select SF_GET_UNICODE_FLAG();
select UNICODE ();
##0表示 GB18030,1表示 UTF-8,2表示 EUC-KR
18.导出查询结果数据(跟oracle一致)
SQL> spool a.txt;
SQL> select * from hxl.tb_test;
LINEID ID NAME
---------- -- -----
1 1 name1
2 2 name2
3 3 name3
4 4 name4
5 5 name5
used time: 0.293(ms). Execute id is 504.
SQL> spool off;
19.使用自增列(identity)
create table test(id int identity,name varchar(20));
常用参数设置:
dm.in
ALTER_TABLE_OPT=3 ##加快带默认值的字段添加
MAX_SESSIONS = 10000 ##最大连接数
COMPATIBLE_MODE = 0 ##兼容模式
Server compatible mode, 0:none, 1:SQL92, 2:Oracle, 3:MS SQL Server, 4:MySQL, 5:DM6, 6:Teradata
连接方式:
通过ip和端口连接
disql icstock/icstock123456@192.168.1.136:5236
本地服务器的话 通过指定端口连接
disql icstock/icstock123456:5236
通过服务名连接
创建服务文件dm_svc.conf
内容如下:
mydm_dsc=(192.168.58.101:5236,192.168.58.102:5236)
SWITCH_TIME=(10000)
SWITCH_INTERVAL=(1000)
linux环境该文件放置/etc/目录下
windows环境放置在C:\Windows\System32目录下
说明安装了达梦客户端,自动会在C:\Windows\System32目录下创建dm_svc.conf文件,但是该文件编辑保持不了,我是把它拷贝出来修改后再拷贝回去就可以了。
服务名连接方式:
C:\Users\Administrator>disql.exe SYSDBA/SYSDBA@mydm_dsc
20.避免忘记sysdba密码无法登录,需要修改如下参数
SQL> sp_set_para_value(2,'ENABLE_LOCAL_OSAUTH',1);
然后重启动数据库
增加dmdba组,并添加为dmdba用户的附组
[root@DM ~]# groupadd dmdba
[root@DM ~]# usermod -G dmdba dmdba
[root@DM ~]# id dmdba
uid=1001(dmdba) gid=2001(dinstall)组=2001(dinstall),2002(dmdba)
以系统认证方式登录
[dmdba@DM ~]$ disql / as sysdba
那么就可以通过如下方式登录了
[dmdba@DM ~]$ disql / as sysdba
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
2019-07-08 数据库损坏的情况下如何获取到dbid