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

shazi7804

10 年前

近來在練習一些 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 database

分享此文：