Shulou(Shulou.com)06/01 Report--

Backup:

mysqldump -uroot -p** -A -B -F --master-data=2 --single-transaction --events > /root/all.sql

Recovery:

mysql -uroot -pVaren2100*

Using location recovery:

mysqlbinlog --start-position="154" --stop-position="789694" /var/lib/mysql/mysql-bin.000002 | mysql -uroot -p***

bin-log specifies the recovery database

Sub-library backup:

#!/ bin/sh

BACKPATH=/root/gool/backup

MYUSER=root

MYPASS=gool

MYCMD="mysql -u$MYUSER -p$MYPASS"

MYDUMP="mysqldump -u$MYUSER -p$MYPASS -x -B -F -R"

[ ! -d $BACKPATH ] && mkdir -p $BACKPATH

DBLIST=`$MYCMD -e "show databases;"|sed 1d|egrep -v "_schema|mysql|sys"`

for dbname in $DBLIST

do

$MYDUMP $dbname|gzip > /root/gool/backup/${dbname}_$(date +%F).sql.gz

done

Sub-database and sub-table backup:

#!/ bin/sh

BACKPATH=/root/gool/backup

MYUSER=root

MYPASS=gool

MYCMD="mysql -u$MYUSER -p$MYPASS"

MYDUMP="mysqldump -u$MYUSER -p$MYPASS -x -F -R"

[ ! -d $BACKPATH ] && mkdir -p $BACKPATH

DBLIST=`$MYCMD -e "show databases;"|sed 1d|egrep -v "_schema|mysql|sys"`

for dbname in $DBLIST

do

TBLIST=`$MYCMD -e "show tables from $dbname;"|sed 1d`

for tablename in $TBLIST

do

mkdir -p $BACKPATH/$dbname

$MYDUMP $dbname $tablename |gzip > $BACKPATH/${dbname}/${tablename}_$(date +%F).sql.gz

done

done

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.