search

YohLee / Learning

学习资料
2 stars 0 forks source link

shell 远程服务器增量拷贝，mysql,psql,mongodb命令行操作 #10

Open YohLee opened 6 years ago

YohLee commented 6 years ago

###########################################远程增量拷贝文件########################################

!/bin/sh

ip=10.1.1.12 scp_dir=/root/learn/ dir=/home/admin/ curTime=date +%Y%m%d echo $curTime BN=/home/admin/scp_yml.cfg #用来保存最后一次备份的日期

num=awk 'NR==1{print $0}' $BN echo $num t2=date -d "$num" +%Y%m%d days=expr $t2 - $curTime

fileList=ssh ${ip} find ${scp_dir} -type f -ctime $days

for file in $fileList;do #循环判断文件是否存在 不存在就从机器1上考过来 if [ ! -f $file ];then scp root@$ip:$file $dir fi; done;

echo $curTime > $BN

###########################################调用mysql,greenplum命令行更改表数据########################################

!/bin/bash

###########################mysql############################### v_mysql_db='db_big' v_mysql_ip='10.1.4.5' v_mysql_u='root' v_mysql_p='123456' v_mysql_str="SELECT table_name FROM tb_data WHERE id=1;" v_url_file='/home/update_errid_oss_system.cfg' mysql -A ${v_mysql_db} -h ${v_mysql_ip} -u${v_mysql_u} -p${v_mysql_p} -e "${v_mysql_str}" | sed '1d' > ${v_url_file}

##########################greenplum############################ v_gp_db='db_gp' v_gp_ip='10.7.1.6' v_gp_u='gpadmin' v_gp_port='5432' v_gp_command=/home/get_maxid_tb.sh #output psql command shell script v_gp_max_id=/home/get_maxid_tb.result #call on get_max_id.sh to output the max _id of table from greenplum

if [ -s $v_url_file ]; then cat $v_url_file | while read line do v_gp_str="SELECT substr(max(id),10,24) as id from ods.tb${line} where createdate>(now()- INTERVAL '1 days')::TIMESTAMP;" v_table_id_gp="su - gpadmin -c \"psql -h ${v_gp_ip} -p ${v_gp_port} -U ${v_gp_u} -d ${v_gp_db} -c \\"${v_gp_str}\\"\" | sed -n '3p'" echo $v_table_id_gp > $v_gp_command chmod +x $v_gp_command . $v_gp_command > $v_gp_max_id v_max_id=awk '{print $1}' $v_gp_max_id v_update_maxid="UPDATE tb_data SET id='"${v_max_id}"' WHERE table_name='"${line}"';" mysql -A ${v_mysql_db} -h ${v_mysql_ip} -u${v_mysql_u} -p${v_mysql_p} -e "${v_update_maxid}" done echo "Success!" else echo "The config table of mysql is not empty!" fi

YohLee commented 6 years ago

!/bin/bash

###########################mysql_datasupport############################### v_mysql_db='datatest' v_mysql_ip='10.1.1.1' v_mysql_u='root' v_mysql_p='123456' v_mysql_str="SELECT table_name,real_table_name FROM md_table_conf WHERE db_id=1 AND is_all='add' AND STATUS=1;" v_url_file='/uCloudlink/greenplum/self_check/get_tablename.cfg' mysql -A ${v_mysql_db} -h ${v_mysql_ip} -u${v_mysql_u} -p${v_mysql_p} -e "${v_mysql_str}" | sed '1d' > ${v_url_file}

##########################mongodb############################ v_mongo_db='db_user' v_mongo_ip='10.1.4.4' v_mongo_u='mongotest' v_mongo_p='123456' v_mongo_port='27019'

cat $v_url_file | while read line do v_line_t=echo $line|awk '{print $1}' v_line_p=echo $line|awk '{print $2}'
echo $v_line v_table_str="SELECT tablename FROM t_source_record WHERE partition_tablename='"${v_line_p}"';" v_table_name="mysql -A ${v_mysql_db} -h ${v_mysql_ip} -u${v_mysql_u} -p${v_mysql_p} -e \"${v_table_str}\" | sed '1d'" v_execute="$v_table_name" v_mongo="mongo ${v_mongo_db} --port ${v_mongo_port} -u ${v_mongo_u} -p ${v_mongo_p} --authenticationDatabase=${v_mongo_db} --host ${v_mongo_ip} --eval \"db.${v_line_p}.stats().count\" | sed -n '3p'" v_mongo_count="$v_mongo" echo $v_mongo_count v_datetime=date -d today +"%Y-%m-%d %T" if [ -z "$v_execute" ] ; then v_insert="INSERT INTO t_source_record(systemname,tablename,partition_tablename,countrows,update_time) VALUES('oss_perflog','${v_line_t}','${v_line_p}',${v_mongo_count},'${v_datetime}');" mysql -A ${v_mysql_db} -h ${v_mysql_ip} -u${v_mysql_u} -p${v_mysql_p} -e "${v_insert}" echo "Insert " else v_update="UPDATE t_source_record SET countrows=${v_mongo_count},update_time='${v_datetime}' WHERE partition_tablename='${v_line_p}';"; mysql -A ${v_mysql_db} -h ${v_mysql_ip} -u${v_mysql_u} -p${v_mysql_p} -e "${v_update}" echo "Update" fi done