1.连接创建数据库
#!/bin/bash
mysql="mysql -uroot -p111111"
#sql="show tables from mysql"
sql="create table test.user(
id int unsigned auto_increment primary key,
username varchar(20),
password varchar(30)
)"
$mysql -e "$sql"
2.使用带参数脚本
#!/bin/bash
mysql="mysql -uroot -p111111"
case $1 in
select|*)
sql="select * from test1.user order by id"
;;
delete)
sql="delete from test1.user where id=$2"
;;
insert)
sql="insert into test1.user(id,name,password) values('$2','$3','$4')"
;;
update)
sql="select * from test1.user order by id"
;;
esac
$mysql -e "$sql"
3.将nginx访问数据按照ip访问量统计并写入数据库
#!/bin/bash
#accessdabase
datetime=`date +%Y-%m-%d`
tempfile=temp.txt
logfile="/var/log/nginx/access.log-`date +%Y%m%d`.gz"
zcat $logfile | awk '{print $1}'| sort | uniq -c | awk '{print $2":"$1}' > $tempfile
mysql="mysql -uroot -p111111"
for i in `cat $tempfile`
do
ip=`echo $i|awk -F: '{print $1}'`
count=`echo $i | awk -F: '{print $2}'`
sql="insert into test1.accesstable(access_time,access_ip,access_count) values('$datetime','$ip','$count')"
$mysql -e "$sql"
done
#sql="create table test1.accesstable(
#id int unsigned not null auto_increment primary key,
#access_time char(50),
#access_ip char(50),
#access_count int(20)
)"
sql="select * from test1.accesstable"
$mysql -e "$sql"