通过shell脚本访问mysql数据库的方法。

create table login_info (ID int auto_increment primary key not null,UserIP varchar(20),Login_Time varchar(20),Logout_Time varchar(20),User_State varchar(2));
#insert into login_info(UserIP,login_time,User_State) values (“10.168.100.236”,“2019-08-01 10:45”,“1”);
#alter table login_info modify User_State varchar(2);

#删除命令详解：delete from login_info where UserIP=“10.168.100.239”;

#COMMAND="select auth_user_query_id, auth_user_secretkey from auth_users where auth_user_name=“admin” " #查找需要的数据sql语句
#declare count=mysql -u root -D test -e "select * from login_info " --skip-column-name #执行mysql的查询，并将其记录到count中

while [ true ]
do

    login_ip=`netstat -tanlp | grep ESTABLISHED | grep 2811 | awk '{print $5}'|cut -d ":" -f1`
	for ip in  $login_ip
	do
   #设定在线状态为1
		online_flag=1
		echo $ip
		login_date=`who /var/log/wtmp | grep  $ip |tail -1|awk  '{print $3}'`
		login_time=`who /var/log/wtmp | grep $ip  |tail -1|awk  '{print $4}'`
		date_time=`echo "$login_date $login_time"`
		echo "date_time  = $date_time"
  
   #查询数据
		count=`mysql -u root -D test  -e "select * from login_info where UserIP=\"${ip}\"&&login_time=\"${date_time}\"" --skip-column-name`
		echo "count = $count "
   if [ "$count" == "" ]
   then
	echo "hello"
	mysql  -u root dap << EOF     
	insert into login_info (UserIP,Login_Time,User_State) values ("${ip}","${date_time}","$online_flag");

EOF
fi

done

#登出数据处理,查询数据库中最后两条记录
check_ip_state=`mysql -u root -D test  -e "select UserIP from login_info  order by id DESC limit 2" --skip-column-name`
for ip_state in  $check_ip_state
do
   #设定在线状态为1
   echo " ip_state = $ip_state"
   ping -c 3 $ip_state >/dev/null 2>&1
   if [ $? -ne 0 ]
   then
		logout_time=`date "+%Y-%m-%d %H:%M"`
		online_flag=0
		count=`mysql -u root -D test  -e "select * from login_info where UserIP=\"${ip}\"&&Logout_Time=\"${logout_time}\"" --skip-column-name`
		echo "count = $count "
		if [ "$count" == "" ]
		then
			
			mysql  -u root dap << EOF     
			insert into login_info (UserIP,Logout_Time,User_State) values ("${ip}","${logout_time}","$online_flag");

EOF
fi
fi

done

sleep 20

done