熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

mysql的本地備份和雙機相互備份腳本

2013-11-23 20:57:01  來源: MySQL 

  先修改腳本進行必要的配置然後以root用戶執行
   第一執行遠程備份時先用 first參數
   本地備份用local參數
   遠程備份不用參數<注意1>
  
  注意:需要在另一主機上的Mysql用戶用添加用戶
  
  需要配置的地方:
  # define host and mysql password
  REMOTE_HOST= #遠程主機名或IP
  REMOTE_PASSWORD= #遠程主機的mysql中的root@本地主機 密碼
  
  # define database path
  DB_PATH=/var/lib/mysql #本地mysql數據庫路徑
  LOCAL_PASSWORD= #本地mysql root用戶密碼
  DB_BACKUP_PATH=/root/db_bk #本地備份時數據保存的路徑
  
  NO_BACKUP_DB= 設置本地數據庫中不想備份的數據庫名用空格隔開
  
  直接下載
  #!/bin/sh
  # mysqldump_allsh
  # this scripts is used to backup MySQL database bettwen tow MySQL SERVER!
  
  # define host and mysql password
  REMOTE_HOST=
  REMOTE_PASSWORD=
  
  # define database path
  DB_PATH=/var/lib/mysql
  LOCAL_PASSWORD=
  DB_BACKUP_PATH=/root/db_bk
  
  # define which database no back up write db anme escap whit space!
  # example;
  # NO_BACKUP_DB=yns test ps_note
  # this said that if db name is yns or test or ps_note then not backup them!
  
  NO_BACKUP_DB=
  
  # get the database name and process one by one
  echo Start to Backup;
  #ls /var/lib/mysql > /tmp/mydball
  if [ $ != help ]; then
  for db in `ls $DB_PATH`; do
  if_backup=
  if [ d $DB_PATH/$db ] ; then
  for noBackDB in $NO_BACKUP_DB ; do
  if ( test $db = $noBackDB ); then
  #echo n ;
  if_backup=;
  fi
  done
  if [ $if_backup ]; then
  case $ in
  
  first)
  echo n Crate db [ $db ] on $REMOTE_HOST first!
  result=`mysqladmin h $REMOTE_HOST password=$REMOTE_PASSWORD create $db`
  if [ ! $result ]; then
  echo Done!;
  else
  echo Fialed! check your configure plz!;
  echo Remote host said: $result;
  exit
  fi
  ;;
  
  local)
  # back up to localhost dir
  if [ ! d $DB_BACKUP_PATH ]; then
  mkdir $DB_BACKUP_PATH
  fi
  echo n [ $db ]Backup to local$DB_BACKUP_PATH with gzip;
  if [ e $DB_BACKUP_PATH/$dbgz ]; then
  mv $DB_BACKUP_PATH/$dbgz $DB_BACKUP_PATH/$dbgz;
  fi
  mysqldump password=Walte#Ming $db | gzip > $DB_BACKUP_PATH/$dbgz
  echo [OK]
  ;;
  
  *)
  # Do common Backup!
  echo n [ $db ]Do Backup! ====>> $REMOTE_HOST/$db;
  mysqldump adddroptable $db password=$LOCAL_PASSWORD | mysql h $REMOTE_HOST password=$REMOTE_PASSWORD $db
  echo [OK];
  esac
  else
  echo [ $db ];
  echo ***This database set to Not backup!Operation CANCELED!;
  fi
  else
  echo [ $db ];
  echo ***Warning! Not a DataBbase Backup Canceled***;
  fi
  done
  
  else
  echo Usage: mysqldupm_allsh [firs|local|help];
  echo Some question ask for mailto:;>;
  fi
  
  # Ended all backup opretion!
  echo ;
  echo Note: if there is come wrong message white MySQL you should check yourconfigure first!;
  exit
  

From:http://tw.wingwit.com/Article/program/MySQL/201311/29339.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.