MYSQL排程備份並達成異地備份－shell script筆記

近來在練習一些 scirpt 的編寫，盡量讓某些簡單的需求來讓自己的工作簡單些，不見得總是需要現有套件來執行任務，也加強自己的開發能力。

 

本篇範例為使用 Mysql 進行備份需求

 

script 目的：

1. 備份所有mysql資料庫，使用show databases列出清單

2. 檢測備份目錄的寫入權限

3. 異地備份，使用scp並加入key認證使script不用輸入密碼

4. 保留5天的備份



 

 

MysqlBackup.sh

#!/bin/sh  
 # mysql_backup.sh: backup mysql databases and keep newest 5 days backup.  
 #  
 # db_user is mysql username  
 # db_passwd is mysql password  
 # db_host is mysql host  
 # 20140818 by Mr.shazi 
 # ———————————————————————————————————————— 
 db_user="root"  
 db_passwd="pass"  
 db_host="localhost"  
  
 # the directory for story your backup file.  
 backup_dir="/home/backup/"  
  
 # date format for backup file (dd-mm-yyyy)  
 time="$(date +"%Y-%m-%d_%H_%M_%S")" 
 today="$(date +"%Y-%m-%d")" 
 fpath=$backup_dir$today 
 echo $fpath  
 if [ ! -d $fpath ];then 
 mkdir $fpath 
 fi  
  
 # mysql, mysqldump and some other bin's path  
 MYSQL="/usr/bin/mysql"  
 MYSQLDUMP="/usr/bin/mysqldump"  
 MKDIR="/bin/mkdir"  
 RM="/bin/rm"  
 MV="/bin/mv"  
 GZIP="/bin/gzip"  
  
 # the directory for story the newest backup  
 test ! -d "$backup_dir/bk/" && $MKDIR "$backup_dir/bk/"  
  
 # check the directory for store backup is writeable  
 test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0  
  
 # get all databases  
 all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"  
 for db in $all_db  
 do  
 $MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db --single-transaction | $GZIP -9 > "$fpath/$db.$time.gz"  
 done  
  
 # 
 cd $backup_dir 
 tar czf Mysql.$time.tar.gz $today 
 rm -rf $today 
 mv Mysql.$time.tar.gz $backup_dir/bk/ 
  
 #scp backup to other server 
 scp $backup_dir/bk/Mysql.$time.tar.gz root@192.168.0.1:/volume/backup/bak/ 
  
 # delete the oldest backup  
 #find $backup_dir -type f -mtime +4 -name "*.gz" -exec rm -f {} \;  
 find $backup_dir/bk -name "*.gz" -type f -mtime +5 -exec rm -f {} \; > /dev/null 2>&1 
  
 exit 0;

 

 

ssh key 免認證

在要異地備份的remote server 建立key並複製到 mysql server。

 

setp.1 建立 rsa

shell# ssh-keygen -t rsa 
 Generating public/private rsa key pair. 
 Enter file in which to save the key (/root/.ssh/id_rsa): 認證key儲存位置 
 Created directory '/root/.ssh'. 
 Enter passphrase (empty for no passphrase): 不用輸入密碼 
 Enter same passphrase again: 不用again 
 Your identification has been saved in /root/.ssh/id_rsa. 
 Your public key has been saved in /root/.ssh/id_rsa.pub. 
 The key fingerprint is: 
 ae:f0:a0:d6:fa:67:92:02:1f:ba:e1:e1:ce:5f:be:04 root@shazi-web 
 The key's randomart image is: 
 +--[ RSA 2048]----+ 
 |                 | 
 |                 | 
 |                 | 
 |                 | 
 |   E    S        | 
 |. . .  .         | 
 |.= oo+  .        | 
 |=.=.B+o.         | 
 |oB+=.*+          | 
 +-----------------+ 
 

 

setp.2 到 rsa 產生的目錄下可以看到已經產生出 id_rsa / id_rsa.pub 並複製一份到 mysql server 並加入 authorized_keys

shell# scp id_dsa.pub backupuser@mysqlserver:~/id_dsa_steven.pub

 

 

排程備份 crond

shell# crontab -e 
 #mysql backup everyday at 04:00 
 0 4 */1 * * * /home/backup/MysqlBackup.sh

 

 

復原備份

shell# zcat database.2014-08-17_15_19_32.gz | mysql -uroot -ppassword -Dmrbs

 

 

---

Orignal From: MYSQL排程備份並達成異地備份－shell script筆記