HGDB批量执行psql命令的脚本

最新推荐文章于 2023-07-20 15:32:18 发布

张玉龙

最新推荐文章于 2023-07-20 15:32:18 发布

阅读量574

点赞数

分类专栏： shell

本文链接：https://blog.csdn.net/u010692693/article/details/73917668

版权

shell 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

存放脚本的服务器需要安装HGDB客户端

[root@localhost ~]# groupadd -g 5866 highgo
[root@localhost ~]# useradd -u 5866 -g highgo highgo; echo highgo | passwd -f --stdin highgo

[root@localhost ~]# mkdir -p /data/highgo/4.0
[root@localhost ~]# chown -R highgo:highgo /data/

[highgo@localhost 4.0]$ tar -zxvf hgdbbak.tar.gz

设置highgo用户的环境变量

export LANG=C
export HG_HOME=/data/highgo/4.0
export PGUSER=highgo
export PGPORT=5866
export PGDATA=/data/highgo/4.0/data
export PATH=$HG_HOME/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$HG_HOME/lib:$LD_LIBRARY_PATH

创建一个存放脚本的目录，要有highgo用户的属组权限

[root@hgdb01 psql]# pwd
/opt/psql
[root@hgdb01 psql]# ll
total 0
drwxrwxr-x. 2 highgo highgo 91 Jun 23 21:09 psql_alert

脚本内容，需要创建以下4个文件，都需要有highgo用户的属组权限

[root@hgdb01 psql_alert]# pwd
/opt/psql/psql_alert
[root@hgdb01 psql_alert]# ll
total 16
-rwx------. 1 highgo highgo 692 Jun 23 21:08 mode.txt
-rwx------. 1 highgo highgo 305 Jun 14 14:26 psql.exp
-rwx------. 1 highgo highgo 684 Jun 14 14:21 psql.sh
-rwx------. 1 highgo highgo 111 Jun 23 21:09 psql.sql

mode.txt 填写数据库的连接信息

[root@hgdb01 psql_alert]# cat mode.txt 
cluster 1
1 cldb_ip 192.168.16.110
1 db_name highgo
1 db_user highgo
1 db_pawd highgo

cluster 2
2 cldb_ip 192.168.16.111
2 db_name highgo
2 db_user highgo
2 db_pawd highgo

每个数据库需要填写5行信息

cluster 指定数据库的ID号，从1开始，多个数据库依次往下排序

cldb_ip 指定连接数据库的IP地址

db_name 指定需要连接的数据库名称

db_user 指定连接数据库的用户名

db_pawd 指定连接数据库的用户密码

psql -h cldb_ip -p 5866 -d db_name -U db_user -W 输入密码db_pawd

psql.sql文件指定需要执行的sql语句

[root@hgdb01 psql_alert]# cat psql.sql 
create table tb1 (id int);
insert into tb1 values (1);
insert into tb1 values (2);
insert into tb1 values (3);

psql.sh 执行程序

[root@hgdb01 psql_alert]# cat psql.sh
#!/bin/bash
basepath=$(cd `dirname $0`; pwd)
LOG=$basepath/alert_hgdb.log
cid=$(cat $basepath/mode.txt |grep ^cluster | awk '{print $2}')
for id in $cid
do
        DATE=`date "+%Y_%m_%d %H:%M:%S"`
        echo $DATE  "INFO, alter hgdb $id / $cid start" >> $LOG
        cldb_ip=$(cat $basepath/mode.txt |grep ^"$id cldb_ip" | awk '{print $3}')
        db_name=$(cat $basepath/mode.txt |grep ^"$id db_name" | awk '{print $3}')
        db_user=$(cat $basepath/mode.txt |grep ^"$id db_user" | awk '{print $3}')
        db_pawd=$(cat $basepath/mode.txt |grep ^"$id db_pawd" | awk '{print $3}')

        expect $basepath/psql.exp $cldb_ip $db_name $db_user $db_pawd $basepath
        echo $DATE  "INFO, alter hgdb $id / $cid done" >> $LOG
done

当mode.txt和psql.sql修改完成后，以highgo用户执行psql.sh，就可以批量执行相同的sql命令

[root@hgdb01 psql_alert]# su - highgo
[highgo@hgdb01 psql_alert]$ /opt/psql/psql_alert/psql.sh

psql.exp辅助执行脚本

[root@hgdb01 psql_alert]# cat psql.exp
#!/usr/bin/expect
set cldb_ip [lindex $argv 0]
set db_name [lindex $argv 1]
set db_user [lindex $argv 2]
set db_pawd [lindex $argv 3]
set basepath [lindex $argv 4]
spawn psql -h $cldb_ip -p 5866 -U $db_user -d $db_name -f $basepath/psql.sql
expect "Password for user $db_user:"
send "$db_pawd\r"
interact