建库、用户语句

create database test_hb;

create user ' test_hb'@'%' identified by '123456';

grant all privileges on  test_hb.* to ' test_hb'@'%';

grant all privileges on  test_hb.* to  test_hb@localhost identified by '123456';

flush privileges;

#!/bin/sh

# $Id: mysql_backup 

# define restricted path

PATH="/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/mysql3306/bin"

# adirname - return absolute dirname of given file

adirname() { odir=`pwd`; cd `dirname $1`; pwd; cd "${odir}"; }

# ---------

# constants

# ---------

MYNAM=`basename "$0"`

MYDIR=`adirname "$0"`

MYTMP="${MYDIR}/tmp"

MYLCK="${MYTMP}/${MYNAM}.lock"

MYPRG="/usr/local/mysql3306/bin/mysql -uroot -p123456 -P 3306 -h 127.0.0.1 --local-infile=1 -Bse"		# mysql client

MYDMP="/usr/local/mysql3306/bin/mysqldump -uroot -p123456 -P 3306 -h 127.0.0.1 -Q -R -v --opt"	# mysqldump

ROTATE=`date +%d`

MYBAK="/home/backup/mysql/${ROTATE}"		# destination dir

DT=""

# ---------

# functions

# ---------

message() { echo "$@"; }		# message - output message on stdout

error() { echo "$@" >&2; }		# error - output message on stderr

die() { error "$@"; exit 1; }		# die - output message on stderr and exit

usage() {

	error "Usage: ${MYNAM} <options>"

	error ""

	error "Available options:"

	error ""

	error "  -h              This help screen"

	error ""

	error "  -q              suppress non-error messages"

	error "  -d db1,db2,...  To dump several databases."

	error "  -t              File name with datetime"

	error "  -p path         Dump to the directory"

	die

}

# parse command line options

while getopts d:p:hqt opt; do

	case "${opt}" in

		q)

			V='-q'

		;;

		t)

			DT=".`date +%Y%m%d%H%M`"

		;;

		p)

			DP=${OPTARG}

		;;

		d)

			DBS=(${OPTARG//,/ })

		;;

		h)

			usage

		;;

		*)

			usage

		;;

	esac

done

[ -n "${DP}" ] && MYBAK="${DP}"

if [ ! -d ${MYTMP} ] ; then

  mkdir -p ${MYTMP}

fi

if [ ! -d ${MYBAK} ] ; then

  mkdir -p ${MYBAK}

fi

# ----

# main

# ----

# perform some locking (as good as it gets in a shell)

[ -s "${MYLCK}" ] && kill -0 `cat "${MYLCK}"` 2>/dev/null &&

        die "${MYNAM}: already running!"

echo "$$" > "${MYLCK}"

# get list of databases

mydbs=""

if [ "x$DBS" == "x" ] ; then

  mydbs=`${MYPRG} "SHOW DATABASES"`

  [ -z "${mydbs}" ] && die "${MYNAM}: unable to get list of databases."

else

  S=0

  E=${#DBS[@]}

  while [ ${S} -lt ${E} ]

  do

    mydb=`${MYPRG} "SHOW DATABASES like \"${DBS[${S}]}\""`

    if [ -z "${mydb}" ] ; then

	DBS[${S}]=""

    else

	mydbs=${mydbs}" "${mydb}

    fi

    S=`expr ${S} + 1`

  done

fi

# process databases

for db in ${mydbs}; do

	# skip special database(s)

	[ "$db" == "information_schema" ] && continue

	[ "$db" == "test" ] && continue

	[ "$db" == "car_source" ] && continue

	# verbose?

	[ "$V" == "-q" ] || echo "Processing database ${db}..."

	[ "$V" == "-q" ] || echo

	# make dir

	mkdir -p "${MYBAK}/${db}"

	${MYDMP} "${db}" 2>"${MYBAK}/${db}/${db}${DT}.log" | gzip > "${MYBAK}/${db}/${db}${DT}.sql.gz"

	# get list of tables

	mytbs=`${MYPRG} "SHOW TABLES FROM \\\`${db}\\\`"`

	[ -z "${mytbs}" ] && message "${MYNAM}: unable to get list of tables for ${db}."

	# process tables

	for tb in ${mytbs}; do

#		MYTIME=`date +%Y%m%d%H%M`

		[ "$V" == "-q" ] || echo "Processing ${db}.${tb}..."

		[ "$V" == "-q" ] || echo "- Dumping..."

		${MYDMP} "${db}" "${tb}" 2>"${MYBAK}/${db}/${db}.${tb}${DT}.log" | gzip > "${MYBAK}/${db}/${db}.${tb}${DT}.sql.gz"

		[ "$V" == "-q" ] || echo "- Checking..."

		${MYPRG} "CHECK TABLE \`${db}\`.\`${tb}\`" | egrep "[^:alnum:\._-]error[^:alnum:\._-]" |

			grep -v "doesn't support check"

		[ "$V" == "-q" ] || echo "- Analyzing..."

		${MYPRG} "ANALYZE LOCAL TABLE \`${db}\`.\`${tb}\`" > /dev/null

		[ "$V" == "-q" ] || echo "- Optimizing..."

		${MYPRG} "OPTIMIZE LOCAL TABLE \`${db}\`.\`${tb}\`" > /dev/null

		[ "$V" == "-q" ] || echo

	done

	# verbose?

	[ "$V" == "-q" ] || echo

done

# cleanup

touch -t `/bin/date --date "3 days ago" "+%Y%m%d%H%M"` /dev/shm/${MYNAM}.start

find /home/backup/mysql/ ! -newer /dev/shm/${MYNAM}.start -type f -exec rm {} \;

find /home/backup/mysql/ -type d -empty -print0 | xargs -0 -n 100 rm -rf &> /dev/null

zcat dataname.sql.gz|mysql -uroot -p123456 dataname