↓跳到主要内容

HCCDA-GaussDB-实验练习-04 数据库管理与运维实验

·2773 字·6 分钟
数据库 GaussDB 国产数据库 HCCDA GaussDB

数据库管理与运维实验

本实验旨在指导用户学习如何使用系统表查询数据库的基本信息和结构、掌握如何利用系统视图获取数据库运行时的各种状态信息、学习如何设置数据库对象的访问权限、利用\copy等工具进行导入导出操作以及进行数据库备份恢复设置。

实验目标

掌握使用GaussDB系统表查询数据库的基本信息操作、设置GaussDB数据库对象的访问权限操作、利用\copy工具进行GaussDB导入导出操作以及GaussDB备份恢复操作。

实验场景

某数据库管理员希望能够提高数据库数据安全性、操作效率和系统可靠性。他可以通过实施访问控制和权限管理，管理系统视图和系统表，确保只有授权人员可以访问和修改关键数据，并使用\copy工具进行数据的导入导出，定期备份数据库以应对数据丢失或系统故障的情况。

实验步骤

一 准备环境 #

1 预置环境 #

注意：开始实验之前请点击手册上方“预置实验环境”按钮。

  1. 预置实验环境需几分钟，成功后将会创建本实验所需的云资源（例如VPC、安全组或云服务器）。 1.
  2. 如有预置的 ECS 资源，其用户、密码信息可点击实验操作桌面下方按钮查看。

2 登录云账户 #

进入【实验操作桌面】，打开Chrome浏览器，选择“IAM 用户登录”，并在对话框中输入系统为您分配的华为云实验账号和密码进行登录。

注意：请使用实验手册上方账号信息，切勿使用您自己的华为云账号登录。

二 购买 GaussDB 实例 #

  1. 进入云数据库GaussDB服务，点击左侧的【服务列表】，选择其中【数据库】下的【云数据库GaussDB】。

  1. 进入云数据库GaussDB，点击页面右上角的【购买数据库实例】按钮。

  1. 进入购买页后，根据以下参数信息购买数据库。

  2. 主要参数如下，其他请保持默认。

  3. • 计费模式：选择【按需计费】

  4. • 区域：默认的【华北-北京四】

  5. • 实例名称：可以设置为【gauss-hccda】

  6. • 产品类型：基础版

  7. • 数据库版本：选择最新版即可

  8. • 实例类型：集中式

  9. • 部署形态：1主2备

• 性能规格、存储类型、存储空间、磁盘加密保持默认

• 虚拟私有云：选择【vpc-hce】

• 内网安全组：选择【sg-hce】

• 管理员密码：设置符合安全要求的root用户密码

  1. 确认信息无误后，点击【提交】。

注意：GaussDB实例创建需要20分钟，请耐心等待

三 通过Xfce终端登录ECS服务器，下载 gsql 客户端并解压 #

1 通过桌面上的Xfce终端登录ECS服务器

输入以下命令，并将命令中的IP地址替换为ECS弹性公网IP

ssh root@xxx.xxx.xx.x

注意：IP地址可进入华为云ECS服务查看：

ECS服务器密码如下图所示：

2 下载gsql客户端，并解压

wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1642684986086/GaussDB_opengauss_client_tools.zip
unzip GaussDB_opengauss_client_tools.zip
cd /root/GaussDB_opengauss_client_tools/Euler2.5_X86_64
cp GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz /opt
cd /opt/
tar -zxvf GaussDB-Kernel-V500R001C20-EULER-64bit-gsql.tar.gz
source gsql_env.sh

3 使用gsql客户端连接数据库，并创建数据库及对应用户

以下命令的连接数据库，xxx.xx.xx.x是GaussDB主节点的IP，yourpassword是设置的数据库密码，请根据实际情况替换。

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r

注意：GaussDB主节点的IP可通过点击GaussDB实例名称，进入信息页面查看：

CREATE DATABASE devdb ENCODING 'UTF8' template = template0;
\q

注意：命令中的IP需替换为GaussDB的主节点IP

gsql -h xxx.xx.xx.x -d devdb -p 8000 -U root -W yourpassword -r
CREATE USER hccda SYSADMIN IDENTIFIED BY "setyourpassword";
\q

登录命令，注意：命令中的IP需替换为GaussDB的主节点IP

gsql -h xxx.xx.xx.x -d devdb -p 8000 -U hccda -W yourpassword -r
\q

四 数据库对象访问权限设置 #

1 创建名为joe的用户，并将sysadmin权限授权给他

通过root用户登录数据库

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r

注意：密码可自定义，但后续命令需自行替换修改后的密码

CREATE USER joe PASSWORD 'yourpassword';
ALTER USER joe with sysadmin;

查看用户权限变化

\du

2 撤销joe用户的sysadmin权限，然后创建tpcds模式，并给tpcds模式下创建一张reason表。

ALTER USER joe with nosysadmin;
CREATE SCHEMA tpcds;
 CREATE TABLE tpcds.reason
(
    r_reason_sk            INTEGER               NOT NULL,
    r_reason_id            CHAR(16)              NOT NULL,
    r_reason_desc                VARCHAR(20)                  
);

假设当前会话为A，新起会话B。

ssh root@xxx.xxx.xx.x

在会话B中，使用joe用户连接数据库，验证joe用户是否拥有tpcds模式下reason表的权限。

cd /opt/
source gsql_env.sh

注意：命令中的IP需自行替换为GaussDb实例的主节点IP

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U joe -W yourpassword -r

验证joe用户是否拥有tpcds模式下reason表的权限

select * from tpcds.reason;

3 返回会话A，将模式tpcds的使用权限和表tpcds.reason的所有权限授权给用户joe

GRANT USAGE ON SCHEMA tpcds TO joe;
GRANT ALL PRIVILEGES ON tpcds.reason TO joe;

切换至会话B，验证joe用户是否拥有tpcds模式下reason表的权限。

select * from tpcds.reason;

4 返回会话A，将tpcds.reason表中r_reason_sk、r_reason_id、r_reason_desc列的查询权限，r_reason_desc的更新权限授权给joe

GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe;

切换至会话B，验证joe用户对表tpcds.reason的查询和更新权限。

验证查询权限

SELECT r_reason_sk,r_reason_id,r_reason_desc FROM tpcds.reason;

验证更新权限

UPDATE tpcds.reason SET r_reason_desc='test';

6 返回会话A，创建角色tpcds_manager，将模式tpcds的访问权限授权给角色tpcds_manager，并授予该角色在tpcds下创建对象的权限，不允许该角色中的用户将权限授权给他人

CREATE ROLE tpcds_manager PASSWORD 'yourpassword';

验证角色tpcds_manager是否添加：

\du


GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;

7 创建角色manager，将joe的权限授权给manager，并允许该角色将权限授权给其他人

CREATE ROLE manager PASSWORD 'yourpassword';
GRANT joe TO manager WITH ADMIN OPTION;

8 创建用户senior_manager，将用户manager的权限授权给该用户。

CREATE USER senior_manager PASSWORD 'yourpassword';
GRANT manager TO senior_manager;

五 数据导入导出 #

1 创建测试表

创建一个简单的测试表以便进行后续的导入导出操作。

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

2 插入测试数据

插入一些测试数据用于后续的导出和导入操作。

INSERT INTO test_table (name, age) VALUES
    ('Alice', 25),
    ('Bob', 30),
    ('Charlie', 28);

3 导出数据到文件

使用 \COPY TO 将数据导出到一个文件中，例如 CSV 格式。

\COPY test_table TO '/tmp/test_table_export.csv' CSV HEADER;

4 查看导出的文件内容

为了确保导出的文件内容正确，打开另一个窗口，使用文本编辑器或命令行工具查看 /tmp/test_table_export.csv 文件。

cat /tmp/test_table_export.csv

5 清空测试表

为了演示导入操作，回到第一个窗口，清空现有的测试表数据。

TRUNCATE TABLE test_table;

6 导入数据文件到表中

使用 \COPY FROM 将之前导出的数据文件重新导入到数据库表中。

\COPY test_table FROM '/tmp/test_table_export.csv' CSV HEADER;

7 验证数据导入

验证数据是否成功导入到数据库表中。

SELECT * FROM test_table;

六 系统表、系统视图查询 #

1 登录并连接数据库，其中数据库的IP及用户密码请根据实际情况替换（GaussDB的IP以主节点IP登录）。

cd /opt/
source gsql_env.sh
gsql -h xxx.xxx.xx.xxx -d postgres -p 8000 -U root -W password -r

2 查看可用数据库的信息。

SELECT * FROM pg_database;

可以看到在刚才步骤中创建的devdb数据库。

3 查看用户的相关信息。

SELECT * FROM pg_user;

可以看到在刚才步骤中创建的用户信息。

4 切换到joe用户，查看joe用户所能访问的表或视图。

\c - joe;


SELECT * FROM DB_ALL_TABLES;

七 数据库备份恢复操作 #

1 设置自动备份策略。

在数据库实例信息界面，在左侧导航栏，选择“备份恢复”，然后单击“修改备份策略”

修改全量备份策略保存时间为“1天”，增量备份周期为“60分钟”，并保存。

2 创建手动备份，创建名为backup01的策略。

3 备份大概需要3分钟，等待备份完成后，点击“恢复”。

恢复到“当前实例”，并确定。

等待2分钟即可完成恢复。