Mysql database backup and recovery script
#! / 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 $1}'| grep-Ev" Database | mysql | information_schema | performance_schema "))
I, 1
For dbname in "${dblist [@]}"
Do
Echo-e "\ nfanti-- > 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 ON $dbname.* TO admin@localhost;" > > $log_path/grant.log
Echo "GRANT ALL PRIVILEGES ON $dbname.* TO admin@$mainHostIp;" > > $log_path/grant.log
Fi
Let iTunes +
Done
}
# restore
Expr 1 + $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 "\ nfanti-- > 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 iTunes +
Done
# grant
# if [- f $log_path/grant.log]; then
Echo "Please 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 ["$1" = = backup]; then
Backupdb
Else
Cd $base_path
Dblist= ($(ls))
ITunes 1
Echo "Backup history..."
For dbname in "${dblist [@]}"
Do
Echo "$I-- > $dbname"
Let iTunes +
Done
Fi
