撒谎信

MySQL系列-join的用法

Wed, 14 Jan 2026 12:46:41 +0800

导言

在数据库操作中，JOIN是实现多表关联查询的核心机制。相较于嵌套子查询和IN操作符，合理使用JOIN能显著提升查询性能，优化执行计划，并增强SQL代码的可读性与可维护性。本文将系统解析MySQL中的JOIN语法类型，涵盖INNER JOIN、LEFT JOIN、RIGHT JOIN等核心关联方式，并通过典型业务场景示例，演示如何通过表连接构建高效的数据检索逻辑。

表结构和数据

为了更好地理解各种 JOIN 类型，我们首先创建两个简单的表并插入一些假数据。

表结构

CREATE TABLE employees (
 employeeid INT PRIMARY KEY,
 name VARCHAR(50),
 departmentid INT
);

CREATE TABLE departments (
 departmentid INT PRIMARY KEY,
 departmentname VARCHAR(50)
);

插入数据

INSERT INTO employees (employeeid, name, departmentid) VALUES
(1, '张三', 1),
(2, '李四', 2),
(3, '王五', 3),
(4, '赵六', 4);
INSERT INTO departments (departmentid, departmentname) VALUES
(1, '销售部'),
(2, '市场部'),
(3, '技术部'),
(5, '人力资源部');

实操展示

1. 内连接（INNER JOIN）

概念

INNER JOIN基于等值关联条件返回两个表的匹配记录，仅保留满足关联条件的行对组合。其执行逻辑是先生成两表笛卡尔积，再通过ON子句指定的关联条件过滤出有效匹配数据。

示例

SELECT employees.name, departments.departmentname 
FROM employees 
INNER JOIN departments ON employees.departmentid = departments.departmentid;

输出结果

name	departmentname
张三	销售部
李四	市场部
王五	技术部

2. 左连接（LEFT JOIN）

概念

LEFT JOIN（左外连接）保留左表（驱动表）即FROM表所有记录的完整集合，即使右表（关联表）不存在匹配行。当右表无对应记录时，结果集将补全右表字段的NULL值。其执行逻辑为：先根据关联条件匹配左右表数据，再强制保留左表全量记录，未匹配的右表字段以NULL填充。

示例

SELECT employees.name, departments.departmentname 
FROM employees 
LEFT JOIN departments ON employees.departmentid = departments.departmentid;

输出结果

name	departmentname
张三	销售部
李四	市场部
王五	技术部
赵六	NULL

3. 右连接（RIGHT JOIN）

概念

RIGHT JOIN（右外连接）保留右表（驱动表）即JOIN表的所有记录，即使左表（关联表）不存在匹配行。当左表无对应记录时，结果集将补全左表字段的NULL值。其执行逻辑为：先通过关联条件匹配数据，再强制保留右表全量记录，未匹配的左表字段以NULL填充，与LEFT JOIN形成对称的外连接操作。

示例

SELECT employees.name, departments.departmentname 
FROM employees 
RIGHT JOIN departments ON employees.departmentid = departments.departmentid;

输出结果

name	departmentname
张三	销售部
李四	市场部
王五	技术部
NULL	人力资源部

4. 全连接（FULL JOIN）

概念

MySQL中FULL JOIN（全连接）的语义是返回左表和右表所有基表记录的并集。当某行在任一关联表中无匹配项时，结果集对应缺失列将补全NULL值。但需注意：MySQL原生不支持FULL JOIN语法，通常通过UNION联合LEFT JOIN与RIGHT JOIN实现该效果。

示例

SELECT employees.name, departments.departmentname 
FROM employees 
LEFT JOIN departments ON employees.departmentid = departments.departmentid 
UNION 
SELECT employees.name, departments.departmentname 
FROM employees 
RIGHT JOIN departments ON employees.departmentid = departments.departmentid;

输出结果

name	departmentname
张三	销售部
李四	市场部
王五	技术部
赵六	NULL
NULL	人力资源部

总结

一句话总结：内连接保留双方都有的记录;左连接保留FROM表的记录,找不到则填充NULL;右连接保留JOIN表的记录找不到则填充NULL；全连接是保留双方的记录，找不到填充NULL

GoldenDB告警脚本部署及基本使用

Wed, 15 Jan 2025 17:48:53 +0800

GoldenDB-Sloth-告警部署和基础应用

1、上传工具包

(1) 将alarm告警工具压缩包上传至insight服务器中

2、复制文件

3、编辑配置文件

(1) 编辑告警发送格式

(2) 编辑告警发送渠道send.xml

(3) 编辑告警过滤器filters.xml

4、启动告警

检查是否启动,有进程即启动成功

5、查看日志

tail -f send_alarm.log

1、上传工具包

(1) 将alarm告警工具压缩包上传至insight服务器中

2、复制文件

(1) 解压后，将alarm/ 整个路径复制到$HOME_INSIGHT/script_monitor/scripts/ 下

3、编辑配置文件

cd $HOME_INSIGHT/script_monitor/scripts/alarm/config

(1) 编辑告警发送格式

vi dataformat.xml

详细可参考dataformat_example.xml

<dataformats>
    <dataformat>
        <platform>sxyd_sendnotice</platform>
        <data_structure>jsonstr</data_structure>
        <new_alarm>
            <dict>
                <origin>goldendb</origin>
                <mobile>${sendTarget}</mobile>
                <content>GoldenDB告警通知:-序号：${alarmId} -名称：${alarmDesc}(${alarmReasonCode}) -级别：${severity} -告警对象：${resourceName} -生成时间：${alarmTime} -告警内容：${alarmReason},${alarmDetailReason}</content>
                <needReplay>false</needReplay>
                <proId>sx</proId>
                </dict>
        </new_alarm>
        <recovery_alarm>
            <dict>
                <origin>goldendb</origin>
                <mobile>${sendTarget}</mobile>
                <content>GoldenDB告警解除通知:-序号：${alarmId} -名称：${alarmDesc}(${alarmReasonCode}) -级别：${severity} -告警对象：${resourceName} -生成时间：${alarmTime} -告警内容：${alarmReason},${alarmDetailReason}</content>
                <needReplay>false</needReplay>
                <proId>sx</proId>
                </dict>
        </recovery_alarm>
    </dataformat>
</dataformats>

(2) 编辑告警发送渠道send.xml

vi send.xml

详细可参考send_example.xml

<sending_method>
    <res_interface>
        <sender>
             <platform>发送渠道名称</platform>
             <url>发送方接口</url>
        </sender>
    </res_interface>
</sending_method>

(3) 编辑告警过滤器filters.xml

vi filters.xml

详细可参考filters_example.xml

<filters>
    <filter>
       <platform>发送渠道名称</platform>
       <class_path>过滤器路径</class_path>
       <class>过滤器类名</class>
       <过滤器参数名(如有)>参数值</过滤器参数名(如有)>
    </filter>
    <filter>
       <platform>发送渠道名称</platform>
       <class_path>过滤器路径</class_path>
       <class>过滤器类名</class>
    </filter>
</filters>

① 过滤器示例：

1) 告警通知多个手机号

第一步：

vi filter.xml

    <filter>
      <platform>发送渠道名称</platform>
      <class_path>lib.filter.gengrate_metadata</class_path>
      <class>GenerateMetaData</class>
      <old_metadata>${severity}</old_metadata>
      <new_metadata>mobile</new_metadata>
      <tranfile>config/sendTarget_mapping.txt</tranfile>
      <default>发送默认手机号</default>
      <verify>
          <policy>checkLength</policy>
          <checklength>11</checklength>
          <split_symbol>,</split_symbol>
      </verify>
</filter>

第二步：

vi plugins.xml

<plugins>
    <plugin>
        <platform>发送渠道名称<</platform>
<class_path>lib.plugins.dispatcher_plugin</class_path>
<class>DispatcherPlugin</class>
        <split_data>mobile</split_data>
<split_symbol>,</split_symbol>
    </plugin>
</plugins>

第三步：创建手机号文本文件

vi sendTarget_mapping.txt

#格式：告警级别+@#@+告警手机号
1@#@1393*******
2@#@1393*******
3@#@1393*******
4@#@1393*******
8@#@1393*******

完成.

2) 告警过滤指定租户

vi filter.xml

    <filter>
       <platform>发送渠道名称</platform>
       <class_path>lib.filter.clusteridfilter</class_path>
       <class>ClusterIdFilter</class>
       <clusteridfilter>需要过滤的租户ID(多个逗号隔开)</clusteridfilter>
    </filter>

3) 告警过滤指定告警代码

第一步：

vi filter.xml

    <filter>
       <platform>发送渠道名称</platform>
       <class_path>lib.filter.alarmreasoncodefilter</class_path>
       <class>alarmreasoncodeFilter</class>
       <whitelistpath>config/whitelist.txt</whitelistpath>
</filter>

a. 告警过滤指定告警代码

第二步：编辑白名单文件

vi whitelist.txt
#1 is open,0 is close

#格式：告警代码+逗号+0或1
2021401,1
2000101,1
2000109,1

完成.

4、启动告警

(1) Insight会自动监听告警目录下的告警程序，只需将告警脚本重命名即可

cd  $HOME_INSIGHT/script_monitor/scripts/alarm/

cd  $HOME_INSIGHT/script_monitor/scripts/alarm/
mv  send_alarm.py*  send_alarm.py

检查是否启动,有进程即启动成功

ps -ef |grep send_alarm

5、查看日志

cd  $HOME_INSIGHT/script_monitor/scripts/alarm/log
tail -f send_alarm.log

Prometheus套装

Tue, 04 Jun 2024 15:40:16 +0800

node_exporter

下载：(目标机)

https://github.com/prometheus/node_exporter/releases

解压后启动

tar -xvf node*.tar.gz

启动：

nohup /opt/node_exporter/node_exporter --no-collector.softnet > /opt/node_exporter/node_exporter.log 2>&1 &

登录链接：http://IP:9100
账号：prometheus
密码：见如下配置

yum install httpd-tools –y
htpasswd -nBC 12 '' | tr -d ':\n'
根据提示输入密码，生成密码后，复制
ps -ef|grep node_exporter

根据配置文件修改

$2y$12$y4PaNc0UM0Jzi07jJf6zcuRFyp2GlH6F5rUKcE.xk3Aug2khcqa7m

vi 配置文件

更改密码为：123456

重新加载配置文件

ps -ef grep |grep prometheus
kill -HUP PID

同时更改Prometheus配置文件为密码访问：

- job_name: 'webserver'
    basic_auth:
      username: prometheus
      password: 123456
    static_configs:
    - targets: ['192.168.179.99:9100','192.168.179.102:9100']

opengauss_exporter

unzip opengauss_exporter_0.0.9_linux_amd64.zip

# ./opengauss_exporter --help
usage: opengauss_exporter [<flags>]
Flags:
  --help                        Show context-sensitive help (also try --help-long and --help-man).
  --version                     Show application version.
  --url=""                      openGauss database target url
  --config=""                   path to config dir or file.
  --constantLabels=""           A list of label=value separated by comma(,).
  --disable-cache               force not using cache
  --auto-discover-databases     Whether to discover the databases on a server dynamically.
  --exclude-databases="template0,template1"
                                A list of databases to remove when autoDiscoverDatabases is enabled
  --namespace="pg"              prefix of built-in metrics, (og) by default
  --web.listen-address=":9187" Address to listen on for web interface and telemetry.
  --web.telemetry-path="/metrics"
                                Path under which to expose metrics.
  --time-to-string              convert database timestamp to date string.
  --dry-run                     dry run and print default configs and user config
  --disable-settings-metrics    Do not include pg_settings metrics.
  --explain                     explain server planned queries
  --parallel=5                  Specify the parallelism. the degree of parallelism is now useful query database thread
  --log.level="info"            Only log messages with the given severity or above. Valid levels: [debug, info, warn, error, fatal]
  --log.format="logger:stderr" Set the log target and format. Example: "logger:syslog?appname=bob&local=7" or "logger:stdout?json=true"

参数配置：

方式一：直接修改postgresql.conf参数文件

password_encryption_type=1

重新加载数据库

gsql -p $port postgres -r -c “select pg_reload_conf();”

方式二：使用集群管理工具

gs_guc reload -I all -N all -c "password_encryption_type=1"

集群采用方法二：

echo "password_encryption_type=1" >> postgresql.conf

访问控制：

将opengauss_exporter部署服务器的ip地址以md5的加密方式加入白名单；

如果是部署在本地服务器，需要以md5的方式添加在host all all 127.0.0.1/32 trust前面，

否则会有FATAL：Forbid remote connection with trust method! 报错

方式一：直接修改pg_hba.conf文件，不需要加载

host dbname opengauss_exporter x.x.x.x/32 md5

方式二：使用管理工具

gs_guc reload -I all -N all -h "host all opengauss_exporter 0.0.0.0/32 md5"

配置数据库用户：

1.0.1版本的数据库，创建用户需要带有sysadmin权限；

1.1.0版本的数据库，创建用户需要带有monadmin权限的，

密码复杂度需要符合数据库密码策略

CREATE USER opengauss_exporter WITH PASSWORD 'opengauss_exporter123' MONADMIN;
grant usage on schema dbe_perf to opengauss_exporter;
grant select on pg_stat_replication to opengauss_exporter;

创建数据库：

pw_initdb -D /home/panweidb/data/panweidb --nodename panweidb - w panwei@123 --dbcompatibility=PG

create database ogexporter DBCOMPATIBILITY='PG';--此命令磐维不适用

配置监控主机环境变量

可将以下配置添加到～/.bashrc 文件，也可以在每次执行命令前执行

export DATA_SOURCE_NAME="host=x.x.x.x user=xxx password=xxx port=xxx dbname=xxx sslmode=disable"

export DATA_SOURCE_NAME="postgresql://username:password@hostname:port/dbname?sslmode=disable"

监控多实例

export DATA_SOURCE_NAME="postgresql://username:password@hostname:port/dbname?sslmode=disable,postgresql://username2:password2@hostname2:port2/dbname2?sslmode=disable"

export DATA_SOURCE_NAME="host=10.176.52.195 user=opengauss_exporter password=opengauss_exporter123 port=17700 dbname=test sslmode=disable"

启动

nohup /opt/opengauss_exporter/opengauss_exporter --config="/opt/opengauss_exporter/default_all_20240303.yaml" --log.level=debug --auto-discover-databases --exclude-databases="template0,template1" --web.listen-address=":9187" --parallel=5 >> /opt/opengauss_exporter/opengauss_exporter.log 2>&1 &

Prometheus

解压

tar -zxvf prometheus-2.51.2.linux-amd64.tar.gz
cd prometheus-2.51.2.linux-amd64

编辑配置文件（缩进要求严格，请按照原有格式进行缩进）

vim prometheus.yml

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http’.
   static_configs:
    - targets: ['localhost:9090']
   - job_name: 'Panwei'
    static_configs:
     - targets: ["IP:9187"]
       labels:
                 instance: 'panwei_primy'
  - job_name: 'node'
    basic_auth:
      username: prometheus
      password: 123456
    static_configs:
    - targets: ['localhost:9100']

启动：

nohup /opt/prometheus/prometheus --web.listen-address=:9090 --config.file=/opt/prometheus/prometheus.yml --web.enable-lifecycle > /opt/prometheus/prometheus.log 2>&1 &

--判断是否启动成功

curl http://IP:9090/metrics | wc -l

重新加载配置文件

ps -ef |grep prometheus
kill -HUP PID

Grafana

安装

yum install -y grafana-enterprise-10.4.2-1.x86_64.rpm

创建数据库存储

create user grafanaer login encrypted password 'grafanaer@123';
create database grafana owner grafanaer;

修改postgresql的pg_hba.cnf文件
vim /data/pg_data/pg_hba.conf

host all all 0.0.0.0/32 trust

---修改配置文件

vim /etc/grafana/grafana.ini

type = postgres
host = IP:17700
name = grafana
user = grafanaer
password = grafanaer@123
autoMigrateOldPanels = true

启动：

systemctl daemon-reload
systemctl enable grafana-server.service
systemctl start grafana-server
systemctl status grafana-server

打开web界面

http://xxx.xxx.xxx:3000

添加数据源

测试添加完成后，点击Dashboards

选择Prometheus 2.0 Stats，点击import

导入成功

再导入opengauss的Dashboards

Grafana报错处理：

this panel requires angluar (deprecated)

查找配置文件目录：

cd /etc/grafana/
cp grafana.ini grafana.ini.bak
vi grafana.ini

末尾添加：

autoMigrateOldPanels = true

重启服务

systemctl disable grafana-server.service
systemctl stop grafana-server

systemctl daemon-reload
systemctl enable grafana-server.service
systemctl start grafana-server

报错网页重定向过多：df -h 查看磁盘是否占满

清理磁盘，重启服务

alertmanager安装部署

下载解压

https://github.com/prometheus/alertmanager/releases

这里以0.27.0为例

https://github.com/prometheus/alertmanager/releases/download/v0.27.0/alertmanager-0.27.0.linux-amd64.tar.gz

tar -zxvf alertmanager-0.27.0.linux-amd64.tar.gz -C /opt/
mv * /opt/alertmanager/alertmanager-0.27.0.linux-amd64/ /opt/alertmanager
cd /opt/alertmanager

启动

nohup /opt/alertmanager/alertmanager --config.file=/opt/alertmanager/alertmanager.yml > /opt/alertmanager/alertmanager.log 2>&1 &

nohup /app/alertmanager/alertmanager --web.listen-address=":9494" --config.file=/app/alertmanager/alertmanager.yml > /app/alertmanager/alertmanager.log 2>&1 &

指定端口

./alertmanager --config.file=alertmanager.yml --web.listen-address=":9093"

web查看

直接在浏览器输入：http://IP:9093，如果能打开界面，说明alertmanager配置成功

添加到prometheus

alertmanager 的默认端口是9093，需要将alertmanager添加到prometheus里统一管理

--编辑prometheus配置文件

vi /opt/prometheus.yaml

# Alertmanager configuration
alerting:
  alertmanagers:
    - static_configs:
        - targets:
          - IP:9093
# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
   - "rules/*.yml"
  # - "first_rules.yml"
  # - "second_rules.yml"

添加规则示例

mkdir /opt/prometheus/rules  --（相对prometheus路径）
vim node_rules.yml

groups:
    - name: node_rule
      rules:

      - alert: server status
        expr: up == 0
        for: 10s
        labels:
          severity: critical
          service: node
        annotations:
          summary: "{{$labels.instance}}: instance down"
          description: "{{$labels.instance}}: instance down"

      - alert: Disk Usage
        expr: 100-(node_filesystem_free_bytes{fstype=~"ext4|xfs"}/node_filesystem_size_bytes {fstype=~"ext4|xfs"}*100) > 50
        for: 1m
        labels:
          severity: warning
          type: "service"
          service: node
          oid: "1.3.6.1.4.1.98789.0.1"
        annotations:
          summary: "Disk used too high"
          description: "Service {{ $labels.instance}} : {{$value}}%)"

重新加载配置文件

ps -ef |grep prometheus
kill -HUP PID

测试：

修改告警规则：测试告警是否正常

修改磁盘超过5%就告警

出现这个说明告警正常

告警配置

下载snmp_notifier

https://github.com/maxwo/snmp_notifier/releases/download/v1.5.0/snmp_notifier-1.5.0.linux-amd64.tar.gz

解压启动snmp_notifier

tar -zxvf snmp_notifier-1.5.0.linux-amd64.tar.gz -C /opt
mv /opt/snmp_notifier-1.5.0.linux-amd64 /opt/snmp_notifier
cd /opt/snmp_notifier
nohup /opt/snmp_notifier/snmp_notifier > /opt/snmp_notifier/snmp_notifier.log 2>&1 &
nohup /app/snmp_notifier/snmp_notifier > /app/snmp_notifier/snmp_notifier.log 2>&1 &
netstat -nap |grep -i 9464

tcp6 0 0 :::9464 :::* LISTEN 14502/snmp_notifier

访问：

http://IP:9464

修改alertmanager.yml配置文件

route:
  group_by: ['alertname']
  group_wait: 30s
  group_interval: 5m
  repeat_interval: 1h
  receiver: 'snap_notifier'
receivers:
  - name: 'snap_notifier'
    webhook_configs:
    - send_resolved: true
      - url: 'http://IP:9464/alerts'
inhibit_rules:
  - source_match:
      severity: 'critical'
    target_match:
      severity: 'warning'
    equal: ['alertname', 'dev', 'instance']

本地启动snaptrapd接受报警信息

snmptrapd -m ALL -f -Of -Lo -c /opt/software/snmp_notifier-1.2.1/scripts/snmptrapd.conf

邮件告警

仅需要修改alertmanager.yml文件即可

global:
  resolve_timeout: 5m
  smtp_from: 'nair@xxx.cn'
  smtp_smarthost: 'smtp.xxx.cn:587'
  smtp_auth_username: 'nair@xxx.cn'
  smtp_auth_password: 'xxxxxxx'
  smtp_hello: 'xxx.cn'

route:
  group_by: ['alertname']
  group_wait: 5s
  group_interval: 5s
  repeat_interval: 1m
  receiver: 'email'
  routes:
  - receiver: email
    group_wait: 10s
    group_interval: 20s
    repeat_interval: 30s
    match_re:
      service: mysql|redis|postgres|node
      severity: critical

  - receiver: email
    group_wait: 1m
    group_interval: 1m
    repeat_interval: 1m
    match_re:
      service: mysql|redis|postgres|node
      severity: error

  - receiver: email
    group_wait: 1h
    group_interval: 1h
    repeat_interval: 1h
    match_re:
      service: mysql|redis|postgres|node
      severity: warning

receivers:
- name: 'email'
  email_configs:
  - to: 'xxx@xxxemail.cn'
    send_resolved: true
inhibit_rules:
  - source_match:
      severity: 'critical'
    target_match:
      severity: 'warning'
    equal: ['alertname', 'dev', 'instance']

opengauss常用命令

Tue, 04 Jun 2024 15:38:48 +0800

======库外操作======

--检查端口

netstat -anp | grep LISTEN | grep 端口
netstat -tuln | grep 端口
nmap -p 17700-17710 localhost
nmap -p 18800-18801 指定IP
telnet 指定IP 17705

--网卡下线脚本

##内容头
!/bin/bash
ifdown bond1
sleep 180
ifup bond1
##内容尾

赋权：chmod 775 file_name
执行：nohup sh file_name &

--设置数据库登录用户权限(pg_hba.conf)配置客户端认证方式

下面示例中配置允许P地址为10.10.0.30的客户端以“jack”用户访问本机

gs_guc set -N all -I all -h "host all jack 10.10.0.30/32 sha256"

TYPE DATABASE USER ADDRESS METHOD
"local" is for Unix domain socket connections only
#表示只允许以安装时-U参数指定的用户从服务器本机进行连接。
local all all trust
IPv4 local connections:
#表示允许omm用户从10.10.0.50主机上连接到任意数据库，使用sha256算法对密码进行加密。
host all omm 10.10.0.50/32 sha256
#表示允许任何用户从10.10.0.0/24网段的主机上连接到任意数据库，使用sha256算法对密码进行加密，并且经过SSL加密传输。
hostssl all all 10.10.0.0/24 sha256
#表示允许任何用户从10.10.0.0/24网段的主机上连接到任意数据库，使用Kerberos认证方式，当前版本暂不支持客户端kerberos认证。
host all all 10.10.0.0/24 gss include_realm=1 krb_realm=HADOOP.COM

--登录数据库

gsql -d db_name -p 数据库主节点端口号
gsql -h <IP 地址> -p <端口号> -d <数据库名> -U <用户名>

--读主库参数

gsql -d postgres -p 17700 -c "show synchronous_standby_names"
gsql -d postgres -p 17700 -c "show listen_addresses"

--读备库参数

gsql -d postgres -p 17700 -c "show synchronous_standby_names"

--查看集群状态

gs_om -t status --detail
gs_ctl -t status --detail

--查看集群vip节点

ip a|grep -i 10.*

--逻辑导出

gs_dumpall -f /database/backup/backup_all_20240428.sql -p 17700

--集群重启

cm_ctl stop && cm_ctl start

--备库节点1-switchover切换为主数据节点-切库

cm_ctl switchover -n 1 -D /database/panweidb/data

--关闭集群指定节点Down

cm_ctl stop -n 1 -D /database/panweidb/data

#需要变为主库的节点执行

#主备切换

gs_ctl switchover -D /database/panweidb/data --主备切换
gs_om -t status --detail
gs_om -t refreshconf
cm_ctl set --cmsPromoteMode=PRIMARY_F -I 1--更新默认信息为1节点

切库后查看集群状态及vip节点

--主备节点切换为正常状态

cm_ctl switchover -n 1 -D /database/panweidb/data

切库后查看集群状态及vip节点

--查看数据库参数

cm_ctl list --param --server | grep enable_transaction_read_only
cm_ctl list --param --server | grep datastorage_threshold_value_check

--集群参数在线修改

gs_guc reload -Z cmserver -N all -I all -c "datastorage_threshold_value_check=90"

--查看业务中断时长

gsql -U test_ha -d postgres -h 10.xx.xx.5 -p 17700 -c "select * from test_ha.test_ha order by 1 desc limit 500;"

--初始化数据库 DB

pw_initdb -D /home/panweidb/data/panweidb --nodename panweidb - w panwei@123 --dbcompatibility=A
pw_initdb -D db_path/data/db_name --nodename node_name - w password --dbcompatibility=A

#dbcompatibility=A 为DB的兼容模式取值范围：A、B、C、PG。分别表示兼容O、MY、TD和POSTGRES

--查看用户计划任务

crontab -l

--编辑用户计划任务

crontab -e

======库内操作======

--查询数据库用户

SELECT rolname FROM pg_roles;

--更改用户密码有效期

ALTER USER USER_NAME WITH VALID BEGIN '2016-11-10 08:00:00' VALID UNTIL '2017-11-10 08:00:00';

--更改用户截止日期

ALTER USER USER_NAME WITH VALID UNTIL '2999-11-10 08:00:00';
ALTER USER  WITH VALID UNTIL '2999-11-10 08:00:00';

--查询慢SQL

SELECT activity.*, stats.* FROM pg_stat_activity AS activity
JOIN pg_stat_statements AS stats ON activity.queryid = stats.queryid
WHERE activity.state = 'active' AND activity.query_start < NOW() - INTERVAL '5 minutes' AND stats.total_time > 1000;

--查询正在执行的慢SQL

SELECT pid,query,query_id,  usename, application_name, client_addr, state, query_start, now() - query_start AS query_duration
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '1 minute'
ORDER BY query_duration DESC;

--关闭慢SQL

SELECT pg_terminate_backend(<pid>);

--复制槽检查逻辑通道

select * from pg_create_logical_replication_slot('mdb_202405301556533_slot','wal2json');

--设置大小写不敏感

set lower_case_table_names = 1;

--授权用户SCHEMA中的ALL权限

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA SCHEMA_NAME TO USER_NAME;

--查询用户在schema中的权限

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'SCHEMA_NAME'
AND grantee = 'USER_NAME' GROUP BY grantee, privilege_type;

--回收用户在SCHEMA的权限

REVOKE  truncate ON ALL TABLES IN SCHEMA SCHEMA_NAME FROM USER_NAME;

--授权用户z在库中的权限

GRANT CREATE ON SCHEMA SCHEMA_NAME TO USER_NAME;
GRANT CREATE ON DATABASE DATABASE_NAME TO USER_NAME;

--查询当前连接数：

SELECT count(*) AS num_connections FROM pg_stat_activity;

--查询数据库大小：

SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size;

--查询表空间使用情况：

SELECT
  spcname AS tablespace_name,
  pg_size_pretty(pg_tablespace_size(spcname)) AS space_used
FROM pg_tablespace;

--查看数据库备节点同异配置

select * from pg_stat_replication;

--创建database

CREATE DATABASE testdb2 OWNER db_user;

#使用模板创建database

create database db_name owner db_user TEMPLATE temp_name;

#查看数据库列表

\l+

#修改数据库名

ALTER DATABASE testdb1 RENAME TO testdb3;

#删除数据库

DROP DATABASE DATABASE_NAME;

--创建表及用户

create user user_name  password 'Test@1234' with sysadmin;--同时赋权超级管理员
create table test_ha.test_ha (instr_time timestamp);--创建表并指定用户
create database db_name owner user_name;--创建库并指定所有者

--元命令帮助（库内）

panweidb=# \?

#列举表

\dt

#查看表结构

\d table_name

#列举 schema

\dn

#查看索引

\di

#切换数据库(切库)

\c db_name

--查看数据库运行参数当前取值

SHOW max_connections;#查看指定参数
SELECT * FROM pg_settings WHERE NAME='max_connections';#查看指定参数
SELECT * FROM pg_settings;#列举所有参数
SHOW all;#列举所有参数

--赋权

GRANT SELECT ON ALL TABLES IN SCHEMA scf_manage_uat_db001 TO bigetlview;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA scf_manage_uat_db001 TO bigetlview;--表权限
GRANT USAGE ON SCHEMA scf_manage_uat_db001 TO bigetlview;--使用schema权限
GRANT REPLICATION TO bigetlview;
GRANT gs_role_replication TO bigetlview;
GRANT ALL PRIVILEGES ON DATABASE scf_manage_uat_db001 TO bigetlview;--库权限
GRANT ALL PRIVILEGES TO bigetlview;--所有权限
ALTER USER bigetlview SYSADMIN;--超级管理员权限

--解除只读

alter system set default_transaction_read_only=off;
default_transaction_read_only

--查看只读

SHOW transaction_read_only;

Oracle-rman异机恢复

Tue, 12 Dec 2023 15:57:09 +0800

rman异机恢复

环境准备

主机

操作系统版本

数据库版本

数据库状态

源端

19c

192.168.174.130

RHEL7.6

Oracle19c

实例正常运行

目标端

test

192.168.174.140

RHEL7.6

Oracle19c

仅安装数据库软件

源端

创建文件夹用来存储备份文件

su - oracle
mkdir -p /home/oracle/backup
sqlplus / as sysdba

create directory backup as '/home/oracle/backup';

创建参数文件

sqlplus / as sysdba

create pfile='/home/oracle/backup/initorcl.ora' from spfile;

开启归档模式

archive log list;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
archive log list;

rman备份数据库

rman target /

run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup database format '/home/oracle/backup/db_%d_%T_%U.bak' plus archivelog format '/home/oracle/backup/ar_%d_%T_%U.bak';
backup current controlfile format '/home/oracle/backup/control_%d_%T_%U.bak';
release channel c1;
release channel c2;
}

传输文件

目标端

su - oracle
mkdir -p /home/oracle/backup

源端

cd /home/oracle/backup
scp * oracle@192.168.174.140:/home/oracle/backup

传输密码文件

源端

scp /u01/app/oracle/product/19c/dbhome_1/dbs/orapworcl oracle@192.168.174.140:/u01/app/oracle/product/19c/dbhome_1/dbs

目标端

准备工作

查看源端的参数文件

cat /home/oracle/backup/initorcl.ora

orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=603979776
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=16777216
orcl.__large_pool_size=16777216
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=637534208
orcl.__sga_target=956301312
orcl.__shared_io_pool_size=50331648
orcl.__shared_pool_size=251658240
orcl.__streams_pool_size=0
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_ll1nyqb3_.ctl','/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_ll1nyqbl_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1507m
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

创建参数文件中用到的目录

su - oracle
mkdir -p /u01/app/oracle/admin/orcl/adump
mkdir -p /u01/app/oracle/oradata/ORCL/controlfile
mkdir -p /u01/app/oracle/fast_recovery_area

编辑环境变量

su - oracle
export ORACLE_SID=orcl

创建spfile文件，启动数据库到nomount状态

sqlplus / as sysdba

create spfile from pfile='/home/oracle/backup/initorcl.ora';

startup nomount

rman设置dbid

源端使用rman连接查询dbid

目标端

rman target /

# 设置dbid

set dbid=1677331735

恢复控制文件

# 恢复控制文件

# 参数文件中指定的控制文件的路径要存在

restore controlfile from '/home/oracle/backup/control_ORCL_20231008_0b28db5v_1_1.bak';

控制文件恢复后数据库启动到mount状态

alter database mount;

恢复数据库

rman target /
list backup
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
restore database;
recover database;
}

出现这个是正常情况，提示缺少归档日志。

启动到open状态

alter database open resetlogs