shell作业mysql自动创建查看以及备份脚本

前提：已经安装并且配置好了数据库软件

1.要求：新建tom,harry,natasha三个数据库，然后备份，新建表格并且查看

脚本如下

#!/bin/bash

MYUSER=root
MYPASS=westos
MYCMD="mysql -u$MYUSER -p$MYPASS"
MYDUMP="mysqldump -u$MYUSER -p$MYPASS"
DBPATH=/home/backup

for dbname in tom harry natasha
do
	$MYCMD -e "create database $dbname"
done

[ ! -d "$DBPATH" ] && mkdir -p $DBPATH
for dbname in `mysql -uroot -pwestos -e "show databases;" | sed '1,2d' |egrep -v "mysql|schema"`
do
	$MYDUMP $DBNAME | gzip > $DBPATH/${dbname}_$(date +%F).sql.gz
done

for dbname in tom harry natasha
do
    echo ========${dbname}.test========
    $MYCMD -e "use $dbname;select * from ${dbname}.test;"
done

运行一下后

查看数据库()

查看备份文件夹

2.要求：数据库备份，执行script.sh $dbpasswd 备份数据库中所有库到/mnt/mysqldump目录中，备份文件名称为“库名称.sql”，当此文件存在时进入交互模式，询问动作，输入“s”跳过备份，输入“b”，备份“库名称.sql”为“库名称_backup.sql”,输入“O”时，覆盖原文件,e表示退出

#!/bin/bash
mkdir -p /mnt/mysqldump
DATABASE=`mysql -uroot -pwestos -e "show databases;" | sed '1,2d' | egrep -v "mysql|schema"`

for MYSQL_NAME in $DATABASE
do
     if [ -e /mnt/mysqldump/${MYSQL_NAME}.sql ];then
	read -p "${MYSQL_NAME} has been dumped!
	[S]kip [B]ackup [O]verwrite [E]xit
	please input the action:" ACTION   
	
	case $ACTION in 
	s|S)
	;;
	b|B)
	mysqldump -uroot -p$1 $MYSQL_NAME > /mnt/mysqldump/${MYSQL_NAME}_backup.sql
	;;
	o|O)
	mysqldump -uroot -p$1 $MYSQL_NAME > /mnt/mysqldump/${MYSQL_NAME}.sql
	;;
	e|E)
	echo -e "Bye"
	exit 0
	esac
  else
	mysqldump -uroot -p$1 $MYSQL_NAME > /mnt/mysqldump/${MYSQL_NAME}.sql
	echo -e "$MYSQL_NAME is backuped!"
	fi
done

试着运行一下

因为刚才第一个脚本已经备份过了，所以这里是这样～