类别:原创 服务器
本文mysql已安装好并运行正常,mysql的用户名为 root 密码为rootroot
[root@jedy ~]#
[root@jedy ~]# mkdir /script
[root@jedy ~]# vim /script/ip_range.sh \\ 创建脚本
#!/bin/sh
\\注意逗号(,),千万不能少
TIME=`date +"%Y-%m-%d %H:%M:%S"`
DIR=/script/ip_range
rm -rf $DIR
mkdir $DIR
touch ${DIR}/ip_apnic
FILE=${DIR}/ip_apnic
HOSTNAME="127.0.0.1"
PORT="3306"
USERNAME="root"
PASSWORD="rootroot"
DBNAME="ip_range_db"
TABLENAME="ip_range_table"
#创建数据库
#create_db_sql="create database ${DBNAME}"
#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#删除表
#delete_sql="drop tables ${TABLENAME}"
#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${delete_sql}"
#创建表
#create_table_sql="create table ${TABLE} IF NOT EXISTS ${TABLENAME} ( city varchar(50), ip varchar(20),ip_range varchar(50), uptime varchar(20) defau
lt 0 )"
#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#删除数据
delete_sql="delete from ${TABLENAME}"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${delete_sql}"
#更新数据
#update_sql="update ${TABLENAME} set id=3"
#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${update_sql}"
#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
#查询
#select_sql="select * from ${TABLENAME}"
#mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
wget http://ftp.apnic.net/apnic/stats/apnic/delegated-apnic-latest -O $FILE
grep -E 'apnic|CN|ipv4|' $FILE | cut -f 4,5 -d'|'|sed -e 's/|/ /g' | while read ip cnt
do
echo $ip:$cnt
mask=$(cat << EOF | bc | tail -1
pow=32;
define log2(x) {
if (x<=1) return (pow);
pow--;
return(log2(x/2));
}
log2($cnt)
EOF
)\\ 这里要注意,)必须放在 EOF 的下一行,并且EOF前不能有任何字符,这一点 bash version3 无所谓,但是bash version4就会报错:warning: here-document at line 56 delimited by end-of-file (wanted `EOF')
#echo $ip/$mask>> ${DIR}/cn.net
#IPNAME=`whois $ip@whois.apnic.net | grep netname | awk '{print $2}'`
whois $ip@whois.apnic.net | grep -E 'inetnum|netname' | paste -s -d"\t\n"| awk '{print $2$3$4,$6}' | while read ip_range IPNAME
do
echo "$IPNAME $ip/$mask $ip_range" >> ${DIR}/$IPNAME
#cat cn.net |sed -e 's/\// /g' | while read ip mask
#do
#echo $ip/$mask
#IPNAME=`whois $ip@whois.apnic.net | grep netname | awk '{print $2}'`
#echo "$IPNAME $ip/$mask" >> apnic/$IPNAME
#done
#插入数据
insert_sql="insert into ${TABLENAME} (city,ip,ip_range,uptime) values('$IPNAME','$ip/$mask','$ip_range','$TIME')"
mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
done
done
[root@jedy ~]# mysql -uroot -prootroot -e "create database ip_range_db" \\ 创建数据库和表
[root@jedy ~]# mysql -uroot -prootroot ip_range_db -e "create table ip_range_table ( city varchar(50), ip varchar(20),ip_range varchar(50), uptime varchar(20))" \\注意逗号(,),千万不能少
[root@jedy ~]# chmod 700 /script/ip_range.sh
[root@jedy ~]# /script/ip_range.sh \\ 运行脚本,查看mysql的内容
[root@jedy ~]# crontab –e
* * * * 0 /script/ip_range.sh \\ 加入自动化任务
[root@jedy ~]#
[root@jedy ~]#
[root@jedy ~]#
[root@jedy ~]#
[root@jedy ~]#