shell脚本——mysql

很期待，学习shell脚本，减少重复工作

自动安装配置mysql脚本：

#/bin/bash
LOG_FILE=/home/hadoop1/log/installmysql.log
function RpmMysql(){
key=$1
r=$(rpm -qa $key)
if [ $r ];
then
	return 1
else 
	return 0
fi
}
mysql="mysql-server"
RpmMysql mysql
r=$?
if [ $r -eq 1 ] ;
then
	echo "$mysql was installed"
#	mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak
	service mysqld start
else
	echo "$mysql was not installed"
	yum install mysql mysql-server mysql-devel
	service mysqld start
fi
chown -R mysql:mysql /var/lib/mysql
echo "install and start mysql success"
#add user to mysql database
user1=root
password1=123456
host=192.168.0.200
user2=hadoop
password2=123456
echo "INFORM:Enter database root password"
#mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak
#mysql -uroot -p </root/addroothadoop.sql> 
user1=root
password1=123456
host=192.168.0.200
user2=hadoop
password2=123456
echo "INFORM:Enter database root password"
mysql -uroot  -e "use mysql;update user set password =password('$password1') where user='$user1';insert ignore into mysql.user(User,Password)values('$user2','password2');grant all privileges on *.* to $user2@'%' identified by '$password2';flush privileges;"
echo "configure done"

shell操作mysql 增删改查

mysql  -hhostname -Pport -uusername -ppassword  -e  相关mysql的sql语句，不用在mysql的提示符下运行mysql，即可以在shell中操作mysql的方法。

#!/bin/bash

HOSTNAME="192.168.111.84"                                           #数据库信息
PORT="3306"
USERNAME="root"
PASSWORD=""

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

#创建数据库
create_db_sql="create database IF NOT EXISTS ${DBNAME}"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

#创建表
create_table_sql="create table IF NOT EXISTS ${TABLENAME} (  name varchar(20), id int(11) default 0 )"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"

#插入数据
insert_sql="insert into ${TABLENAME} values('billchen',2)"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"

#查询
select_sql="select * from ${TABLENAME}"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_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}"

#删除数据
delete_sql="delete from ${TABLENAME}"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${delete_sql}"
mysql -h${HOSTNAME}  -P${PORT}  -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"