将 soc 剩余内存 和cpu 资源 写入mysql 数据库中 【对数据库进行增删改查 shell 脚本】

#!/bin/bash
set -x
list=(
    # $1
10.56.31.xxx
10.56.31.xxx
10.56.216.xxx
10.56.31.xxx
10.56.31.xxx
10.56.31.xxx
10.56.216.xxx
10.56.31.xxx
10.56.31.xxx
10.56.31.xxx
)

soc_information(){
    i=$1
    a=$(timeout 4 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "virsh list --all | awk '/i-/{print \$2}'")
        if [ -z ${a:0:1} ]
        then
            date_time=`date +%Y-%m-%d-%H:%M:%S`
            host_id=$(timeout 10 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "cat /var/lib/volcstack/helium.yaml | grep id|awk '{print \$2}'")
            helium_status=$(timeout 10 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "docker ps -a | grep helium|awk '{print \$8}'")
            keyong_cpu=$(timeout 10 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "sshpass -p '2ytE!r00tme' ssh -o StrictHostKeyChecking=no -p 2222 localhost  lscpu|grep -v NUMA |grep -w 'CPU(s):'| awk '{print \$2}'")
            neicun=$(timeout 10 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "cat /mnt/sys/kernel/debug/dmem/free_dpages")
            # echo $neicun#换算之前的内存
            keyong_neicun=`expr $neicun \* 2 \* 64 / 63 / 1024`
            echo "可用内存 $keyong_neicun"
            echo $keyong_cpu
            #判断数据库中是否已有数据，如果有就update没有就insert
            sql=`mysql -h localhost -u root -p'root@123' -D ceshi -e "select ip from host_table where ip='$i'"`
            echo $sql
            if [ -z $sql ]
            then
                echo '使用insert 语句'
                mysql -h localhost -u root -p'root@123' -D ceshi -e "INSERT INTO host_table  VALUES ('$i', '$host_id', '$keyong_neicun','$keyong_cpu','$keyong_cpu','$helium_status','$date_time',NULL);"

            else
                echo '使用update语句'
                mysql -h localhost -u root -p'root@123' -D ceshi -e "update  host_table set host_id='$host_id',keyong_neicun='$keyong_neicun',keyong_cpu='$keyong_cpu',zong_cpu='$keyong_cpu',helium_status='$helium_status',update_time='$date_time' where ip='$i';"
            fi
        else
            date_time=`date +%Y-%m-%d-%H:%M:%S`
            host_id=$(timeout 10 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "cat /var/lib/volcstack/helium.yaml | grep id|awk '{print \$2}'")
            helium_status=$(timeout 10 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "docker ps -a | grep helium|awk '{print \$8}'")
            neicun=$(timeout 10 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "cat /mnt/sys/kernel/debug/dmem/free_dpages")
            # echo $neicun换算之前的内存
            keyong_neicun=`expr $neicun \* 2 \* 64 / 63 / 1024`
            echo "可用内存 $keyong_neicun"
            hostcpu=$(timeout 10 sshpass -p 'root@123' ssh -o StrictHostKeyChecking=no root@$i "sshpass -p '2ytE!r00tme' ssh -o StrictHostKeyChecking=no -p 2222 localhost  lscpu|grep -v NUMA |grep -w 'CPU(s):'| awk '{print \$2}'")
                echo $hostcpu
                cpus=0
                for p in $a
                do
                    c=$(timeout 10 sshpass -p 'root@123' ssh   root@$i "virsh dominfo $p |grep 'CPU(s)'|awk '{print \$2}'")
                    # echo $c
                    cpus=`expr $cpus + $c`
                done
                # echo $cpus
                # echo "soc $i 剩余可用cpu数量为`expr $hostcpu - $cpus`"
        keyong_cpu=`expr $hostcpu - $cpus`
        echo $keyong_cpu
        
        #判断数据库中是否已有数据，如果有就update没有就insert
        sql=`mysql -h localhost -u root -p'root@123' -D ceshi -e "select ip from host_table where ip='$i'"`
        if [ -z $sql  ]
        then
                echo '使用insert 语句'
                mysql -h localhost -u root -p'root@123' -D ceshi -e "INSERT INTO host_table  VALUES ('$i', '$host_id', '$keyong_neicun','$keyong_cpu','$hostcpu','$helium_status','$date_time',NULL);"
                
        else
                echo '使用update语句'
                mysql -h localhost -u root -p'root@123' -D ceshi -e "update  host_table  set host_id='$host_id',keyong_neicun='$keyong_neicun', keyong_cpu='$keyong_cpu', zong_cpu='$hostcpu',helium_status='$helium_status',update_time='$date_time' where  ip='$i';"
                
        fi
        fi
}
start=`date "+%s"`
for i in ${list[*]}
do
{
    soc_information $i
    }&
done
wait
end=`date "+%s"`
echo "time: `expr $end - $start`"