参考:http://blog.163.com/xychenbaihu@yeah/blog/static/132229655201141142057473/
增加删除库、创建用户并授权的功能,库名作为参数传递给脚本:
#!/bin/bash
HOSTNAME="192.168.100.123" #数据库Server信息
PORT="3306"
USERNAME="root"
PASSWORD="youpassword"
DBNAME=$1 #要创建的数据库的库名称
#DBNAME="test_db_name" #要创建的数据库的库名称
TABLENAME="test_table_name" #要创建的数据库的表的名称
MYSQL_CMD="mysql -h${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD}"
echo ${MYSQL_CMD}
echo "drop database ${DBNAME}"
create_db_sql="drop database IF EXISTS ${DBNAME}"
echo ${create_db_sql} | ${MYSQL_CMD} #创建数据库
if [ $? -ne 0 ] #判断是否创建成功
then
echo "drop databases ${DBNAME} failed ..."
exit 1
fi
echo "create database ${DBNAME}"
create_db_sql="create database IF NOT EXISTS ${DBNAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"
echo ${create_db_sql} | ${MYSQL_CMD} #创建数据库
if [ $? -ne 0 ] #判断是否创建成功
then
echo "create databases ${DBNAME} failed ..."
exit 1
fi
echo "create user ${DBNAME}"
create_db_sql="grant all privileges on ${DBNAME}.* to ${DBNAME}@'%' identified by 'yourpassword'"
echo ${create_db_sql} | ${MYSQL_CMD} #创建数据库
if [ $? -ne 0 ] #判断是否创建成功
then
echo "create user ${DBNAME} failed ..."
exit 1
fi
#echo "create table ${TABLENAME}"
#create_table_sql="create table ${TABLENAME}(
#name char(6) NOT NULL,
#id int default 0
#)ENGINE=MyISAM DEFAULT CHARSET=latin1"
#echo ${create_table_sql} | ${MYSQL_CMD} ${DBNAME} #在给定的DB上,创建表
#if [ $? -ne 0 ] #判断是否创建成功
#then
# echo "create table ${DBNAME}.${TABLENAME} fail ..."
#fi
用法:
create_db_mysql_remote.sh mydb0607
如果mysql中已经有了一个mydb0607,会先drop掉,再创建一个空的mydb0607库,同时创建一个名为mydb0607的用户,对这个mydb0607库有所有权限。
如果机器里未安装mysql,脚本执行会报错。可以从其他相同操作系统安装了mysql的机器里/usr/bin/下复制mysq过来,赋予可执行权限,即可。