mysql根据sql语句备份,mysql 遍历所有的库并根据表和sql语句备份

建库、用户语句

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} "

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

还原数据库时，到备份目录找到最新的备份。dataname.sql.gz 结尾

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