mysql数据库备份与恢复脚本

8767564323

#!/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 "\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 ON $dbname.* TO admin@localhost;" >> $log_path/grant.log
      echo "GRANT ALL PRIVILEGES ON $dbname.* TO admin@$mainHostIp;" >> $log_path/grant.log
    fi
    let i++
  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 "\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 "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))
  i=1;
  echo "Backup history ..."
  for dbname in "${dblist[@]}"
  do
    echo "$i --> $dbname"
    let i++
  done

fi