MySQL database backup and recovery scripts

#!/bin/bash

#mysql database backup and restore the script

# define config variable

Host=localhost

User=root

Passwd=xxxxx

#this variable for Grant

mainhostip= "127.0.0.1"

Base_path=/opt/disk-webdata/mysqlbackup

# Base_path=/media/sf_website/yii/disk-webdata/mysqlbackup

# # Backup

BackupDB () {

Bak_path= $base _path/$ (date +%y%m%d)/data

Log_path= $base _path/$ (date +%y%m%d)/log

[!-D $bak _path] && mkdir-p $bak _path

[!-D $log _path] && mkdir-p $log _path

Dblist= ($ (mysql-u$user-p$passwd-e "show databases;" | Awk-f "|" ' {print $} ' |grep-ev "Database|mysql|information_schema|performance_schema"))

I=1

For dbname in "${dblist[@]}"

Do

Echo-e "\n$i---Backup database $dbname ..."

file= $bak _path/"$dbname. sql"

Mysqldump-h$host-u$user-p$passwd-b $dbname > $file

If [-F $file];then

Success= "$i-$dbname backup success!"

Echo $success;

echo $success >> $log _path/backuplog.log

echo "GRANT all privileges in $dbname. * to [email protected];" >> $log _path/grant.log

echo "GRANT all privileges on $dbname. * to [email protected] $mainHostIp;" >> $log _path/grant.log

Fi

Let i++

Done

}

#restore

Expr 1 + $ &>/dev/null

If [$?-eq 0];then

Bak_path= "$base _path"/$1/data

Log_path= "$base _path"/$1/log

#restore

CD $bak _path

dblist= ($ (LS))

I=1

For dbname in "${dblist[@]}"

Do

Echo-e "\n$i---Restore database $dbname ..."

MYSQL-H$HOST-U$USER-P$PASSWD < $dbname

Success= "$i-$dbname Restore Success!"

Echo $success

echo $success >> $log _path/restorelog.log

Let i++

Done

#grant

# if [-F $log _path/grant.log];then

echo "ensure that [admin] user exist @[localhost & 127.0.0.1 & Mainhostip]";

Sleep 15s

Ii=1

Cat $log _path/grant.log | While Read line

Do

echo "$ii-$line"

Mysql-h$host-u$user-p$passwd-e "$line"

Let ii++

Done

# fi

elif ["$" = = Backup];then

BackupDB

Else

CD $base _path

dblist= ($ (LS))

I=1;

echo "Backup history ..."

For dbname in "${dblist[@]}"

Do

echo "$i-$dbname"

Let i++

Done

Fi

This article is from the "11002498" blog, please be sure to keep this source http://11012498.blog.51cto.com/11002498/1883947

MySQL database backup and recovery scripts