在mysql的用户中,如果有远程服务器需要连接服务,一般设置对应允许连接ip的话安全性会高点,而且iptables也设置只允许对应ip登录,这样安全性大大提高,如果远程服务器ip经常变动的话维护会比较麻烦,所以在远程服务器上设置ddns更新对应的ip,然后在mysql服务器上解析到ip有变动的时候,实时更新数据库中用户允许登录ip,和防火墙允许ip。
#!/bin/bash mail_list="[email protected],[email protected]" path_self=$(cd "$(dirname "$0")";pwd) #是否开起手动执行调试模式 echo_on=0 #0/1 关闭/开启 日志显示 #set domain domain=test.com #set dns server dns_server=8.8.8.8 #set getip command , only(nslookup/ping) now just nslookup which nslookup > /dev/null 2>&1 || (echo 'missing command nslookup ... try to install ...' ;sleep 3;yum install bind-utils -y) get_ip_command=nslookup #log path log_on=1 #0/1 关闭/开启 日志记录 log_path=${path_self}/iptables_mysql.log iptables_reset=0 PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin iptables_on=${path_self}/iptables_on function get_ip() { local ip ip=$(nslookup $1 $dns_server | grep Address |grep -v $dns_server | awk -F ' ' '{print $2}') echo $ip } function echo_() { if [ "$log_on" == "1" ];then echo $(date +'%y-%m-%d %H:%M:%S') $1 >> ${log_path};fi if [ "$echo_on" == "1" ];then echo $(date +'%Y-%m-%d %H:%M:%S') $1;fi } user_info=$(mysql -e "select user,host from user where User like 'node-%'" mysql | grep -v user ) #echo $user_info | while read LINE mysql -e "select user,host from user where User like 'node-%'" mysql | grep -v user | while read LINE do #local $user #local $user_ip user=$(echo $LINE |awk -F ' ' '{print $1}') user_ip=$(echo $LINE |awk -F ' ' '{print $2}') domain_ip=$(get_ip ${user}.${domain}) echo_ "INFO 检测账号:$user 允许登录IP:$user_ip 对应域名解析IP:${domain_ip}" echo ${domain_ip} |grep -q '^[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}$' && ( if [ "$user_ip" != "${domain_ip}" ];then echo_ "WARN 尝试修正IP信息……" mysql -e "update user set host = '${domain_ip}' where user = '${user}';flush privileges;" mysql if [ $? == 0 ];then echo_ "WARN 账号:$user 允许登录IP 修改为:${domain_ip}" echo "数据库账号:${user} <br>原IP:${user_ip} <br>更改为:${domain_ip}<br>修改时间:$(date +'%Y-%m-%d %H:%M:%S')" | mutt -s "DB ${user} 信息更新成功" -e 'set content_type="text/html"' ${mail_list} echo 1 > ${iptables_on} else echo_ "账号:$user 允许登录IP 修改为:${domain_ip} 失败!!!" echo "数据库账号:${user} <br>原IP:${user_ip} <br>更改为:${domain_ip}<br>修改时间:$(date +'%Y-%m-%d %H:%M:%S')" | mutt -s "DB ${user} 信息更新失败!!" -e 'set content_type="text/html"' ${mail_list} fi else echo_ "INFO 检测账号:${user} 所属IP一致。正常!" fi ) || ( echo_ "ERROR 获取域名 ${user}.${domain} 失败!!!" echo "获取域名 ${user}.${domain} 失败!!!<br>数据库账号:${user}<br>原登录IP:${user_ip}" | mutt -s "域名解析异常!! ${user}" -e 'set content_type="text/html"' ${mail_list} ) done if [ "$(cat ${iptables_on})" == "1" ];then echo_ "WARN 更新防火墙!!" /sbin/iptables -F INPUT ########重要!!!######### /sbin/iptables 其他防火墙INPUT规则 ########重要!!!######### for val in $(mysql -e "select user,host from user where User like 'test-%'" mysql | grep -v user |awk -F ' ' '{print $2}');do /sbin/iptables -I INPUT -s ${val}/32 -p tcp --dport 3306 -j ACCEPT;done /sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP echo 0 > ${iptables_on} fi
© 著作权归作者所有
下一篇: ELK② 使用
文章评论(0)