Shell日常运维脚本

• 备份数据库

#!/bin/bash
#数据库备份
username=admin
password='xxxxxx'
backup_dir=/nasbackup
db_list=$(cat dblist.txt)
start_time=$(date +"%Y-%m-%d_%H:%M.%S")
echo "备份开始时间：$start_time"
for db in $db_list
do
	begin_seconds=`date +%s`;
	start_time=$(date +"%Y-%m-%d_%H:%M.%S")
	echo ${db} "begin_time:" ${start_time}
	mysqldump -u$username -p$password -h 127.0.0.1 -P 3306 $db -E --triggers --set-gtid-purged=OFF -q > $backup_dir/$db\_$start_time.sql
	#sleep 60;
	end_seconds=`date +%s`;
	echo ${db} " duration:" $((end_seconds-begin_seconds))"s"
done
end_time=$(date +"%Y-%m-%d_%H:%M.%S")
echo "备份结束时间：$end_time"

备份数据库2

#!/bin/bash
username="admin"
password="xxxxxx"
mysql_port="3306"
mysql_host="127.0.0.1"
data_dir="/u01/mysql/data"
p_dir="/nasbackup/test"
#$1为每个业态的数据,可选项[db1,db2等]
opt=$1
backup_dir=$p_dir/$1_$(date +"%Y-%m-%d")

if [[ ! -n "$1" ]];then
    echo "!!!运行备份脚本，请传入参数[tkzj,bbkq,cwgl....]!!!"
    exit 0
else
    echo "!!!$1相关的数据将进行备份!!!"
fi

if [ ! -d $backup_dir ];then
    mkdir -p $backup_dir
else
    echo "!!!备份文件夹已经存在,程序退出!!!"
    exit 0
fi

start_time=$(date +"%Y-%m-%d_%H:%M.%S")
echo "备份开始时间：$start_time"
dblist=$(ls -l $data_dir|grep $opt|grep -v "ods"|awk '{print $NF}')
for db in $dblist
do
    begin_seconds=$(date +%s)
    start_time=$(date +"%Y-%m-%d_%H:%M.%S")
    echo ${db} "begin_time:" ${start_time}
    #mysqldump -u$username -p$password -h $mysql_host -P $mysql_port $db -E --triggers -q > $backup_dir/$db\_$start_time.sql
    sleep 60;
    end_seconds=$(date +%s)
    echo ${db} " duration:" $((end_seconds-begin_seconds))"s"
done
end_time=$(date +"%Y-%m-%d_%H:%M.%S")
echo "备份结束时间：$end_time"

•  传输文件 

#!/bin/bash
start_time=$(date +"%Y-%m-%d_%H:%M.%S")
echo "传输开始时间：$start_time"
for f1 in $(ls -l /u01/mysql/data/|egrep  -v "total"|awk '{print $NF}'|sed -n '351,400p')
do
    begin_seconds=`date +%s`;
    start_time=$(date +"%Y-%m-%d_%H:%M.%S")
    echo ${f1} "begin_time:" ${start_time}
    scp -r /u01/mysql/data/$f1 10.120.160.3:/u01/160_3/
    #sleep 60
    end_seconds=`date +%s`;
    echo ${f1} " duration:" $((end_seconds-begin_seconds))"s"
done
end_time=$(date +"%Y-%m-%d_%H:%M.%S")
echo "传输结束时间：$end_time"

 

 

 cat test.sh 
#!/bin/bash
read -p "请输入开始备份数据库位点：" begin
read -p "请输入结束备份数据库位点：" end
flag_time(){
  date +"%Y-%m-%d_%H.%M.%S"
}
flag_timestamp(){
  date +"%s"
}
db_dir=/u01/mysql/data/
dbs=$(ls -l $db_dir|egrep  -v "total"|awk '{print $NF}'|sed -n "$begin,$end p")

db_list=()
db_list+=("$dbs")

echo "${db_list[*]}" > db_list.temp_${begin}-${end}

#s_dbs=$(cat db_list.temp|sed -n "$begin,$end p")

echo '开始传输时间:' $(flag_time)
for f1 in $(cat db_list.temp_${begin}-${end})
do
  echo "!!!db: "$f1 "   start_time:" $(flag_time)
  start_s=$(flag_timestamp)
  sleep 2
  #####scripts
  end_s=$(flag_timestamp)
  echo $f1 "spent time:" $((end_s-start_s))"s"
done
echo  '传输完成时间:' $(flag_time)

#rm db_list.temp_${begin}-${end}

 

• 运行pgbadger分析PgSQL日志

#/bin/bash
dbname="zedb"
log_dir="/data/pgsql_5432/pg_log"
html_dir="/nasbackup"
week=$(date -d "yesterday" +%w)
date=$(date -d "yesterday" +%Y-%m-%d)
start_time=$(date +"%Y-%m-%d_%H:%M.%S")
echo "Database log pgbadger start: " $start_time
begin_seconds=$(date +%s)
pgbadger -q $log_dir/postgresql-$week.csv -d $dbname -o $html_dir/postgresql-$dbname-$date.html -f csv
end_seconds=$(date +%s)
echo "Database log pgbadger duration:" $((end_seconds-begin_seconds))"s"

•  pg_dump批量导出表数据

$ cat table_bk.sh 
#!/bin/bash
backup_dir=/gp_backup
backup_time=$(date +'%Y%m%d')
dbname=zedb
schema=ze_ods

for table in $(cat table.list)
do
    pg_dump -d $dbname -t $schema.$table -s > $backup_dir/$table\_$backup_time.sql
#   echo $table
done