GaussDB数据库备份详解(六)
GaussDB数据库备份详解(六)
本文将全面介绍GaussDB数据库的备份与恢复技术,帮助您制定合理的数据保护策略,确保业务数据安全可靠。
1 GaussDB备份恢复概述
数据是企业的核心资产,定期备份是数据库管理的基础工作。GaussDB提供了多种备份恢复方案,涵盖逻辑备份与物理备份两大类,可满足不同业务场景下的数据保护需求。通过合理的备份策略,企业能够有效预防数据丢失、人为误操作、系统故障等数据风险,确保业务连续性。
2 逻辑备份与恢复
逻辑备份是指将数据库中的逻辑数据(如表、视图等)导出为特定格式的文件,适用于数据迁移、跨版本恢复和部分数据恢复场景。
2.1 使用gs_dump进行备份
gs_dump
是GaussDB提供的逻辑备份工具,可以对单个database进行备份,支持备份不同的schema结构或数据。
基本语法:
bash
gs_dump --file=备份文件路径 --schema=模式名 --no-privileges --no-owner --username=用户名 数据库名
示例演示:
bash
# 创建备份
gs_dump --file=mes_way1.sql --schema=public --no-privileges --no-owner --username=omm mes
# 文本格式备份(方便查看和修改)
gs_dump --file=mes_way1.sql --schema=public --no-privileges --no-owner --username=omm mes
# 二进制备份(效率更高)
gs_dump --file=mes_way3.dmp --format=c --schema=mes --no-subscriptions --username=mes mes2
section子项备份:
为了更精细地控制备份过程,可以使用section参数将备份分为三个阶段:
bash
# 1. 数据备份前结构预处理
gs_dump --file=mes_way2_pre.sql --section=pre-data --schema=mes --no-subscriptions --username=mes mes2
# 2. 数据备份
gs_dump --file=mes_way2_data.sql --section=data --schema=mes --no-subscriptions --username=mes mes2
# 3. 数据备份后逻辑处理
gs_dump --file=mes_way2_post.sql --section=post-data --schema=mes --no-subscriptions --username=mes mes2
2.2 使用gs_dump进行恢复
文本格式恢复:
bash
gsql --file=mes_way1.sql --username=omm mes2
二进制格式恢复:
bash
gs_restore --username=mes -d mes3 mes_way3.dmp
section子项恢复:
bash
# 按顺序执行三个阶段的恢复
gsql --file=mes_way2_pre.sql --username=mes mes3
gsql --file=mes_way2_data.sql --username=mes mes3
gsql --file=mes_way2_post.sql --username=mes mes3
3 物理备份与恢复
物理备份直接拷贝数据库文件,恢复时更快速,适用于大数据量场景。
1、数据库对象管理及SQL语法1
基于以下学生成绩表,完成以下实验要求
--创建表
create table su(id int primary key not null,firstname varchar(50) not null,fmailyname varchar(50) not null,shorterform varchar(50) not null,mark char(1) not null,score int not null)distribute by replication;
--导入数据
insert into su values(1,'secebbie','peter','peter','S',86),(2,'tom','jerry','tom','H',63),(3,'amanda','lee','lee','H',67),(4,'homas','brooke','homas','H',67),(5,'elizabeth','katharine','elizabeth','H',67);
(1)请查询姓名和姓氏,并以姓名·姓氏的格式输出,要求首字母大写,姓名和姓氏之间使用“·”拼接
select initcap(firstname||'·'||familyname) from su;
(2)插入一条新数据(2,’tom’,’jerry’,’tom’,’H’,63),当出现主键冲突时,将mark修改为‘F’
insert into su values(2,'tom','jerry','tom','H',63) on duplicate key update mark='F';
(3)查询表,检查姓名是否是sec开头,展示姓名,判断结果result
select firstname,(case when firstname like 'sec%' then 't' else 'f' end)as result from su;
(4)查询表中所有列的数据,按照成绩进行排序,并显示名称(position),名次为连续的。要求展示所有字段,名字字段position
select * ,dense_rank() over(order by score desc) as position from su ;
2、数据库对象管理及SQL语法2
-- 当前有一张订单表lineitem,具体字段如下
L_ORDERKEY BIGINT NOT NULL --订单key
L_PARTKEY BIGINT NOT NULL --配件key
L_SUPPKEY BIGINT NOT NULL --供应商key
L_LINENUMBER BIGINT NOT NULL --流水号
L_QUANTITY float8 null --数量
L_EXTENDEPPRICE float8 NOT NULL --出厂价
L_DISCOUNT float8 NOT NULL --折扣
L_TAX float8 NOT NULL --税点
L_RETURNFLAG CHAR(1) NOT NULL --原返标志
L_LINESTATUS CHAR(1) NOT NULL --明细
L_SHIPDATE DATE NOT NULL --发货日期
L_COMMITDATE DATE NOT NULL --预计到达日期
L_ARRIVALDATE DATE NOT NULL --达到时间
L_ORDERSTRATEGY CHAR(32) NOT NULL --订单处理策略
L_TRANSPORTROUTE CHAR(32) NOT NULL --运输路径
L_COMMENT VARCHAR(64) NOT NULL --备注
-- 数据导入
(1)创建分区表,根据上述字段信息创建分区表,按L_SHIPDATE分区,按年分1993,1994,1995,1996,1997,1998,1999,分区名称分别为L_SHIPDATE_1,第二个分区名称为L_SHIPDATE_2以此类推,使用L_ORDERKEY进行哈希分布,建表完成后执行上方数据导入代码,进行数据导入
create table lineitem(xx xxx)
distribute by hash(L_ORDERKEY)
partition by range(L_SHIPDATE)
(partition L_SHIPDATE_1 values less than ('1993-01-01 00:00:00'),
partition L_SHIPDATE_2 values less than ('1994-01-01 00:00:00'),
partition L_SHIPDATE_3 values less than ('1995-01-01 00:00:00'),
partition L_SHIPDATE_4 values less than ('1996-01-01 00:00:00'),
partition L_SHIPDATE_5 values less than ('1997-01-01 00:00:00'),
partition L_SHIPDATE_6 values less than ('1998-01-01 00:00:00'),
partition L_SHIPDATE_7 values less than ('1999-01-01 00:00:00'),
partition L_SHIPDATE_8 values less than (now()));
(2)查询表的schema名称,展示表名,schema名称
select tablename,schemaname from pg_tables where tablename='lineitem';
(3)查看表分布的oid,展示表名,nodeoids
select relname,nodeoids from pg_class,pgxc_class where oid=pcrelid and relname='lineitem';
(4)查看表所在的实例信息
select relname,pgxc_node.* from pg_class,pgxc_class,pgxc_node where pg_class.oid=pgxc_class.pcrelid and cast(pgxc_class.nodeoids as varchar(20))=cast(pgxc_node.oid as varchar(20)) and relname=='lineitem';
3、用户及权限管理
(1)使用两个查询语句,查看“postgres”数据库的最大连接数和已使用连接数
--数据库的最大连接数
select datname,datconnlimit from pg_database where datname='postgres';
--数据库已使用连接数
select datname,count(*) from pg_stat_activity where datname='postgres';
(2)创建用户user_test,指定该用户具有创建数据库和创建角色的权限
create user user_test createdb createrole password 'Test@123';
(3)创建表table_test,此表中包含一个名为col_test的列,为用户user_test授权在table_test表的col_test列上的查询、更新权限
create table table_test(col_test int);
grant select(col_test),update(col_test) on table_test to user_test;
(4)收回用户user_test在table_test表在col_test列上的更新权限
revoke update(col_test) on table_test from user_test;
(5)创建角色role_test,此角色拥有审计权限
create role role_test with auditamin password 'Huawei@123';
(6)将角色role_test的权限授权给用户user_test,并允许用户将此权限再授权给其他用户或角色
grant role_test to user_test with admin option;
(7)用户user_test账号被盗,请手动锁定此账号
alter user user account lock;
(8)级联删除用户user_test,并重新创建,将账号设置为在2023年国庆节期间有效
-- 级联删除用户
drop user user_test cascade;
-- 创建用户
create user user_test createdb createrole password 'Test@123' valid begin '2023-10-01 00:00:00' valid until '2023-10-07 00:00:00';
4、行级访问控制
-- 执行以下SQL还原表和数据
-- 创建表
create table bank_card(b_number nchar(30) primary key,b_type nchar(20),b_c_id int not null);
-- 导入数据
insert into bank_card values ('62220213020000001','信用卡','1');
insert into bank_card values ('62220213020000002','信用卡','3');
insert into bank_card values ('62220213020000003','信用卡','5');
insert into bank_card values ('62220213020000004','信用卡','7');
insert into bank_card values ('62220213020000005','储蓄卡','9');
insert into bank_card values ('62220213020000006','储蓄卡','1');
insert into bank_card values ('62220213020000007','储蓄卡','3');
insert into bank_card values ('62220213020000008','储蓄卡','5');
insert into bank_card values ('62220213020000009','储蓄卡','7');
insert into bank_card values ('62220213020000010','储蓄卡','8');
(1)创建用户crecard_mger、savcard_mger,密码均为“Test@123”
create user crecard_mger password 'Test@123';
create user savcard_mger password 'Test@123';
(2)给上题中创建的两个用户授予bank_card表的读取权限
grant usage on schema root to crecard_mger,savcard_mger;
grant select on bank_card to crecard_mger,savcard_mger;
(3)打开bank_card表的行级别访问控制开关
alter table bank_card enable row level security;
(4)创建行级访问控制策略bank_card_rls,要求crecard_mger用户只能查看信用卡信息,savcard_mger用户只能查看储蓄卡信息
create row level security policy bank_card_rls on bank_card to crecard_mger,savcard_mger using(b_type=case when current_user='crecard_mger' then '信用卡' when current_user='savcard_mger' then '储蓄卡' else '' end);
(5)切换到crecard_mger用户查看bank_card表内容
--切换登录用户(DAS在右上角切换)
\c postgres crecard_mger
--查看bank_card表数据
select * from root.bank_card;
(6)使用root用户删除行级控制策略bank_card_rls,并关闭表的行级访问控制开关
--切换登录用户(DAS在右上角切换)
\c - root
--删除表的行级访问策略
drop row level security policy bank_card_rls on bank_card;
--关闭表的行级访问控制开关
alter table bank_card disable row level security;
5、触发器
(1)创建视图SELECT_SD,查看学生成绩信息,查看学生姓名,课程名称,课程成绩
create view SELECT_SD as select sname,cname,grade from studnet s,course c,elective e where e.sno=s.sno and e.cno=c.cno;
(2)编写函数FUNC_SUM,返回某个学生的分数总和
create or replace function FUNC_SUM(stuid int) returns integer
as
$$
declare result integer;
begin
select sum(grade) into result from elective where sno=stuid;
return result;
end;
$$ language plpgsql;
Select func_sum(stuid);
(3)创建触发器DELETE_ELE,在STUDENT表上绑定触发器DELETE_ELE,在删除表中某个学生时,将ELECTIVE表中该学生的选课记录一并删除
--删除elective表记录的函数
create or replace function func_delete_ele() returns trigger
as
$$
begin
delete from elective where sno=old.sno;
return old;
end;
$$ language plpgsql;
--绑定到student表的触发器
create trigger delete_ele before delete on student for each row execute procedure func_delete_ele();
6、游标
以下为表创建SQL语句(此题无数据)
create table TEACHER(
ID INTEGER NOT NULL,
NAME VARCHAR(50) NOT NULL,
DEPTNO INTEGER NOT NULL,
SALARY FLOAT NOT NULL,
TITLE VARCHAR(100) NOT NULL --职称:讲师、副教授、教授);
create table DEPARTMENT(
ID INTEGER NOT NULL,
NAME VARCHAR(50) NOT NULL);
(1)创建存储过程pro_curs_1,使用游标打印各部门总人数,按人数降序排序,打印格式如下:
部门名称 1—人数
部门名称 2—人数
打印操作可以使用DBEOUTPUT.PRINT_LINE(outputstr)接口
create or replace procedure pro_curs_1()
as
declare cursor cur1 is select d.name as dname,count(*) as perscount from teacher t,department d where t.deptno=d.id group by d.name order by perscount desc;
dname varchar(50);
perscount integer;
begin
open cur1;
loop
fetch next from cur1 into dname,perscount;
EXIT WHEN NOT FOUND;
DBE_OUTPUT.PRINT_LINE(concat(dname,'---',cast(perscount as varchar(10))));
end loop;
close cur1;
END;
/
call pro_curs_1();
(2)创建存储过程pro_curs_2,使用游标读取薪水降序排序的前三位老师和后三位老师的信息,分别获取ID,姓名,部门名称,薪水和职称,请按以下格式打印:
ID-姓名-部门名称-薪水-职称
create or replace procedure pro_curs_2()
as
declare cursor cur2 is select sid,sname,dname,salary,title from ((select * from teacher order by salary desc limit 3)union all (select from teacher order by salary limit 3)) t join department d on t.partno=d.id;
sid integer;
sname varchar(50);
dname varchar(50);
salary float;
title varchar(50);
begin
open cur2;
loop
fetch next from cur2 into sid,sname,dname,salary,title;
dbe_output.print_line(concat(cast(sid as varchar(20),'-',sname,'-',dname,'-',cast(salary as varchar(20)),'-',title)));
if cur2 % notfound then exit;
close cur2;
end if;
end loop;
end;
/
7、数据库优化
通常的SQL优化会通过参数调优的方式进行调整,例如如下参数
set enable_fast_query_shipping = off;
set enable_stream_operator = on;
--建表
create table tb_user(stu_no int,stu_name varchar(32),age int,hobby_type int) distribute hash(age);
--插入数据
insert into tb_user select id,'xiaoming'||(random()*60+10)::int,
(random()*60+10)::int,
(random()*5+1)::int,
from (select generate_series(1,100000)id) tb_user;
(1)收集tb_user的统计信息
analyze tb_user;
(2)为下面两个查询语句创建索引,让执行计划和索引最合理
SQL1:explain analyze select * from tb_user where age=29 and stu_name=‘xiaoming’;
SQL2:explain analyze select * from tb_user where stu_no=100 and age=29;
select gs_index_advise('select * from tb_user where age=29 and stu_name="xiaoming"');
create index age_name on tb_user(age,name);
select gs_index_advise('select * from tb_user where stu_no=100 and age=29');
create index no_age on tb_user(stu_no,age);
(3)在上题操作的基础上,用3种不同方式使如下SQL不走索引
explain analyze select * from tb_user where stu_no=100 and age=29;
--1.通过hint干预优化不走索引
explain analyze select /*+ tablescan(tb_user) */ * from tb_user where stu_no=100 and age=29;
--2.调大index开销
set cpu_index_tuple_cost = 100000;
--3.直接禁用索引
alter index no_age unusable;
8、论述
(1)权限管理模型RBAC和ABAC区别
RBAC:基于角色的访问控制,角色通常是指具有某些共同特征的一组人,例如:部门、地点、资历、级别、工作职责等。在系统初始时Admin根据业务需要创建多个拥有不同权限组合的不同角色,当需要赋予某个用户权限的时候,把用户归到相应角色里即可赋予符合需要的权限。
不同于常见的将用户通过某种方式关联到权限的方式,ABAC则是通过动态计算一个或一组属性来是否满足某种条件来进行授权判断(可以编写简单的逻辑)。属性通常来说分为四类:用户属性(如用户年龄),环境属性(如当期时间),操作属性(如读取)和对象属性(如一篇文章,又称资源属性),所以理论上能实现非常灵活的权限控制,几乎能满足所有类型的需求。权限判断需要实时执行,规则过多会导致性能问题
两者区别:RBAC和ABAC之间的主要区别在于RBAC基于用户角色提供对资源或信息的访问,而ABAC提供基于用户、环境或资源属性的访问权限
(2)数据库数据加密方式有哪些,至少3种
函数加密:字段级,通过调用函数,如md5()等函数对传入参数进行加密,业务感知加密,不支持密文条件安全,数据在会话中临时解密,数据库无法自动解密,防止高权限账户窃取数;
透明加密:表级,数据在文件落盘时加密,对用户及上层使用SQL的应用不感知,对于需要加密的表创建时通过TDE参数指定加密算法,数据库无感知,内存明文处理,防止基于物理磁盘的数据窃取,TDE秘钥管理分为三层,分别是根秘钥,主秘钥,数据加密秘钥。
全密态:字段级,支持密态等值查询,数据库无法解密,防止运维、管理、高权限账户窃取隐私数据,在业务中仅在DDL层做了扩展,在create table或者alter table新增列时可以将列设置为加密列,给需要的列绑定列加密密钥即可,如果DML操作于其他表一致,但需要以密态方式(-C)创建客户端连接才可以,如果是非密态模式,那么查看到的数据是密文,未指定加密的列数据均已明文处理,密钥管理分为三层,分别根密钥,主密钥,列加密密钥,密钥均存储于GaussDB client,减少攻击面;
总结来说,函数加密,是用客户把密钥给到数据库,数据库在执行函数时做一个加密动作,是在数据库里加密。透明加密是在数据库自己找一个密钥,在磁盘落盘时做加密。全密态等值是客户找到密钥之后先把数据加密,再交给数据库,全生命周期都是密文的。
客户端和服务端SSL通信加密,SSL加密支持对称加密、非对称加密,对称加密算法指的是加密和解密使用相同的秘钥,特点是算法公开,加解密速度快、效率高;非对称加密算法包含两个秘钥:公钥和私钥,公钥和私钥是一对,加密和解密使用不同的秘钥,特点是算法复杂度高、安全性更强、性能较对称加密差。
常见算法为AES、DES、MD5、SM4
3.1 使用gs_probackup进行物理备份
gs_probackup
是一个用于管理openGauss数据库备份和恢复的工具。它具有以下特点:
- 可用于备份单机数据库或者主节点数据库,为物理备份
- 可备份外部目录的内容,如脚本文件、配置文件、日志文件等
- 支持增量备份、定期备份和远程备份
- 可设置备份的留存策略
使用流程:
-
初始化备份目录:
bash
gs_probackup init -B backup-path
-
添加备份实例:
bash
gs_probackup add-instance -B backup-path -D pgdata-path --instance=instance_name
-
执行备份:
bash
gs_probackup backup -B backup-path --instance=instance_name -b backup-mode
-
验证备份:
bash
gs_probackup validate -B backup-path --instance=instance_name
3.2 使用gs_probackup进行恢复
当数据库出现故障时,可以使用以下命令进行恢复:
bash
gs_probackup restore -B backup-path --instance=instance_name -D pgdata-path
重要提示: 使用restore子命令前,应先停止gaussdb进程。
3.3 使用gs_backup进行系统级备份
gs_backup
工具可帮助openGauss备份、恢复重要数据。
备份数据库主机:
bash
gs_backup -t backup --backup-dir=BACKUPDIR -h HOSTNAME --parameter --binary --all
恢复数据库主机:
bash
gs_backup -t restore --backup-dir=BACKUPDIR -h HOSTNAME --parameter --binary --all
4 云数据库GaussDB备份恢复
对于云上的GaussDB实例,华为云提供了控制台操作方式进行备份恢复。
4.1 创建手动备份
- 登录管理控制台,进入GaussDB实例页面
- 在"实例管理"页面,选择指定的实例,在操作列选择"创建备份"
- 在创建备份弹出框中,命名该备份,选择备份方式,并添加描述
- 单击"确定",提交备份创建
手动备份特点:
- 备份名称长度在4~64个字符之间,必须以字母开头
- 手动备份会一直保存,直到用户手动删除
- 备份方式支持物理备份、快照备份和一级备份
4.2 恢复实例数据
GaussDB支持多种恢复方式,满足不同业务需求:
- 恢复到新实例:创建一个和备份时间点数据相同的新实例,不影响原实例
- 恢复到当前实例:将当前实例上的数据全部覆盖,恢复过程中数据库不可用
- 恢复到已有实例:将备份数据恢复到已有实例,会覆盖目标实例的数据
操作步骤:
- 在"备份恢复管理"页面,选择需要恢复的备份,单击操作列的"恢复"
- 选择恢复方式(新实例、当前实例或已有实例)
- 根据提示完成参数配置
- 提交恢复任务并等待完成
4.3 下载备份文件
用户可以将GaussDB的备份文件下载到本地,用于本地存储或归档。
下载步骤:
- 在"备份恢复"页面,选择需要下载的可用备份,单击操作列的"下载"
- 下载方式选择"OBS Browser+下载",单击"确定"
- 使用OBS Browser+客户端下载备份文件
5 高级备份恢复场景
5.1 增量备份策略
Roach备份工具支持增量备份,可显著减少备份所需时间和存储空间。
增量备份示例:
bash
python GaussRoach.py -t backup --master-port 6000 --media-destination /backup/media --media-type Disk --metadata-destination /backup/media/metadata --parallel-process 3 --compression-level 6 --prior-backup-key 20200629_161652
5.2 账本数据库恢复
对于账本数据库,GaussDB提供了专门的修复函数:
sql
-- 对某个DN节点进行历史表修复操作
EXECUTE DIRECT ON (datanode1) 'select pg_catalog.ledger_hist_repair(''ledgernsp'', ''usertable'');';
-- 执行全局区块表修复操作
SELECT pg_catalog.ledger_gchain_repair('ledgernsp', 'usertable');
5.3 表级时间点恢复
GaussDB支持将数据库中的单个或者某些表恢复到指定时间点,这在误删除少量数据时非常有用,无需恢复整个实例。
6 备份恢复策略最佳实践
6.1 备份策略建议
- 定期自动备份:设置定期自动备份策略,确保数据安全
- 多地存储:将备份文件存储在不同地理位置,提高容灾能力
- 定期验证:定期验证备份文件的完整性和可恢复性
- 监控告警:设置备份失败告警,及时处理备份异常
6.2 恢复演练计划
- 定期演练:每季度至少进行一次恢复演练
- 文档记录:详细记录恢复步骤和注意事项
- 团队培训:确保多名管理员掌握备份恢复技能
6.3 性能优化建议
- 业务低峰期:在业务低峰期执行备份操作
- 增量备份:结合全量备份和增量备份,平衡效率与可靠性
- 并行处理:适当使用并行备份提高效率
7 常见问题与解决方案
问题1:自动备份失败怎么办?
可能原因和解决方案:
- 网络环境稳定性不足:系统会自动重试,也可手动备份
- 实例状态异常:等待实例恢复正常后再次执行备份
- 参数修改异常:检查参数设置,恢复默认参数重启实例
问题2:如何找回误删除的数据?
- 使用备份文件恢复:通过备份恢复误删除的数据
- 表级时间点恢复:如果只是部分表数据丢失,可使用表级恢复
问题3:备份文件如何收费?
- GaussDB提供了和实例磁盘大小相同的免费存储空间
- 只有超过免费容量的存储空间才开始计费
- 手动备份不会随实例删除而自动删除
- 点赞
- 收藏
- 关注作者
评论(0)