建库、用户语句
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 [email protected] 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 "[email protected]"; }# message - output message on stdout
error() { echo "[email protected]" >&2; }# error - output message on stderr
die() { error "[email protected]"; 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
原文:https://www.cnblogs.com/xzlive/p/10115272.html