用shell操作mysql数据库

核心提示： #!/bin/bashlog='SQL_insert_one.sh.log' #定一个日志文件便于我们定位bugif [ ! -e $log ]; then #如果日志文件不存在就新建一个touch $logfidate=$(date "+%Y-%m-%d__%H:%M:%S&q

 #!/bin/bash

log='SQL_insert_one.sh.log' #定一个日志文件便于我们定位bug

if [ ! -e $log ]; then #如果日志文件不存在就新建一个
touch $log
fi

date=$(date "+%Y-%m-%d__%H:%M:%S") #记录打印日志的时间
echo >> $log
echo "================= $date ================" >> $log

# mysql database and table to create
HOST='10.10.10.21'
PORT='3306'
USER='root'
PWD='123456'

DBNAME="mysql" #数据库名称
TABLENAME="test_table_test" #数据库中表的名称

mysql_login="mysql -h $HOST -P $PORT -u $USER -p$PWD" #连接数据库
mysql_create_db=''
mysql_create_table=''
mysql_insert_sql=''

function mysql_insert(){

mysql_insert_sql="insert into $TABLENAME (visit_user_id, visit_user_kind, res_user_id, res_id, ur_createtime, ur_ip)
values('19334','1','66500','46256','20080905154623','');"
#mysql -h $HOST -P $PORT -u $USER -p$PWD -e "use $DBNAME; $mysql_insert_sql"
${mysql_login} -e "use $DBNAME; $mysql_insert_sql"

if [ $? -ne 0 ] ;then
echo "insert data faild" >>$log
else
echo "insert data sucess" >>$log
fi
}

function mysql_create(){
echo | ${mysql_login} #判断数据库是否连接成功
if [ $? -ne 0 ]; then
echo "login mysql ${HOST}:${PORT} failed.." >>$log
exit 1
else
echo "login mysql ${HOST}:${PORT} success!" >> $log
fi

echo "create database $DBNAME ..." >> $log #创建数据库
mysql_create_db="create database if not exists $DBNAME"
echo ${mysql_create_db} | ${mysql_login}
if [ $? -ne 0 ]; then
echo "create db ${DBNAME} failed.." >> $log
else
echo "create db ${DBNAME} success!" >> $log
fi

echo "create table $TABLENAME ..." >> $log #创建表
mysql_create_table="CREATE TABLE $TABLENAME (
ur_id int(11) NOT NULL AUTO_INCREMENT,
visit_user_id int(11) NOT NULL DEFAULT '0',
visit_user_kind char(1) NOT NULL DEFAULT '1',
res_user_id int(11) NOT NULL DEFAULT '0',
res_id int(11) NOT NULL DEFAULT '0',
ur_createtime char(14) NOT NULL DEFAULT '00000000000000',
ur_ip varchar(15) NOT NULL DEFAULT '',
PRIMARY KEY (ur_id),
KEY idx_visituserid_resid (visit_user_id,res_id),
KEY idx_resuserid_createtime (res_user_id,ur_createtime)
) ENGINE=MyISAM AUTO_INCREMENT=1168783 DEFAULT CHARSET=gbk"

echo ${mysql_create_table} | ${mysql_login} ${DBNAME}
if [ $? -ne 0 ]; then
echo "create table ${TABLENAME} fail..." >> $log
else
echo "create table ${TABLENAME} success!" >> $log
fi
}

mysql_create #调用创建数据库的函数
mysql_insert #调用插入数据的函数