for i in `cat 1110_lianjieshu`;
do
mysql -happ01.rds.mysql.aliyun.com -P3306 -udbaas -pdbaas@aly dbaas -Ne "
select distinct t4.ip,t3.port,t6.max_conn from cust_instance t1,custins_hostins_rel t2,instance t3,hostinfo t4,instance_stat t5,instance_level t6 where
t1.id=t2.custins_id and t2.hostins_id=t3.id and t3.host_id=t4.id and t6.id=t1.level_id and t3.is_deleted=0 and t1.db_type='mysql' and t5.ins_id=t3.id
ip=$(echo $line|awk '{print $1}')
port=$(echo $line|awk '{print $2}')
for i in `cat 1110_lianjieshu`;
do
mysql -happ01.rds.mysql.aliyun.com -P3306 -udbaas -pdbaas@aly dbaas -Ne "
select distinct t4.ip,t3.port,t6.max_conn from cust_instance t1,custins_hostins_rel t2,instance t3,hostinfo t4,instance_stat t5,instance_level t6 where
t1.id=t2.custins_id and t2.hostins_id=t3.id and t3.host_id=t4.id and t6.id=t1.level_id and t3.is_deleted=0 and t1.db_type='mysql' and t5.ins_id=t3.id
and t5.role=0 and t1.ins_name='$i';"|while read line ;
do
ip=$(echo $line|awk '{print $1}')
port=$(echo $line|awk '{print $2}')
# echo "add connections on $ip---$porti"
max_conn=$(echo $line|awk '{print $3}')
sql_show_max_connections="show global variables like 'max_connections'"
sql_show_max_user_connections="show global variables like 'max_user_connections'"
sql_show_max_reserved_connections="show global variables like 'reserved_connections'"
max_connections=$(ssh $ip "mysql -h127.0.0.1 -P$port -NAe \" $sql_show_max_connections\"" </dev/null |awk -F " " '{print $2'} )
max_user_connections=$(ssh $ip "mysql -h127.0.0.1 -P$port -NAe \"$sql_show_max_user_connections\"" </dev/null |awk -F " " '{print $2}')
max_reserved_connections=$(ssh $ip "mysql -h127.0.0.1 -P$port -NAe \"$sql_show_max_reserved_connections\"" </dev/null |awk -F " " '{print $2}')
cnf_reserved_connections=$(ssh ${ip} "cat /etc/my${port}.cnf " < /dev/null | grep reserved_connections | awk -F = '{print $2}' )
cnf_max_connections=$(ssh ${ip} "cat /etc/my${port}.cnf " < /dev/null | grep max_connections | awk -F = '{print $2}' )
cnf_max_user_connections=$(ssh ${ip} "cat /etc/my${port}.cnf " < /dev/null | grep max_user_connections | awk -F = '{print $2}')
num_cnf_reserved_connections=`echo ${cnf_reserved_connections}`
num_cnf_max_connections=`echo ${cnf_max_connections}`
num_cnf_max_user_connections=`echo ${cnf_max_user_connections}`
echo "$i men $max_connections $max_user_connections $max_reserved_connections $max_conn"
echo "$i cnf $num_cnf_max_connections $num_cnf_max_user_connections $num_cnf_reserved_connections"
let new_max_reserved_connections=170
let new_max_connections=new_max_reserved_connections+max_conn
let new_max_user_connections=new_max_reserved_connections+max_conn
ssh $ip "mysql -h127.0.0.1 -P$port -NAe \" set global reserved_connections=$new_max_reserved_connections\"" </dev/null
ssh $ip "mysql -h127.0.0.1 -P$port -NAe \" set global max_connections=$new_max_connections\"" </dev/null
ssh $ip "mysql -h127.0.0.1 -P$port -NAe \" set global max_user_connections=$new_max_user_connections\"" </dev/null
ssh $ip "sed -i 's/reserved_connections.*/reserved_connections = $new_max_reserved_connections/g' /etc/my${port}.cnf" </dev/null
