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

2014-08-18 Bash, MySQL

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

 

 

8 Replies to “MYSQL排程備份並達成異地備份-shell script筆記”

  1. rocky表示:

    您好:
    我參考你的備份指令可以備份
    但還原時無法還原

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

    請問 -Dmrbs這一段該如何修改
    謝謝您

    • shazi7804表示:

      rcoky:

      您好:
      原文寫的比較不清楚,-Dmrbs 為你要還原的資料庫名稱

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

      若是要還原新的資料庫,請先將database建立起來。

      已修改,感謝指教

      • rocky表示:

        您好:我用下列指令要還原其中一個資料庫

        zcat Mysql.2015-03-10_10_21_28.tar.gz |mysql -uroot -XXXXXXX Syslog

        他出現下列錯誤訊息

        ERROR: ASCII ” appeared in the statement, but this is not allowed unless option –binary-mode is enabled and mysql is run in non-interactive mode. Set –binary-mode to 1 if ASCII ” is expected. Query: ‘2015-03-10/’.

        請問這要如何除錯,謝謝!

        • shazi7804表示:

          rocky:

          根據您的訊息,您使用的應該是mysql 5.6以上的
          提供您幾個方向嘗試

          1. 加入 –binary-mode 來處理含有blob的mysqlbinlog output

          zcat Mysql.2015-03-10_10_21_28.tar.gz |mysql –binary-mode=1 -uroot -XXXXXXX

          2. 將 databaes 由 ASCII 轉成 UTF-8 格式再匯入。

          • rocky表示:

            我是用centos 7 的mariadb

            執行
            zcat Mysql.2015-03-16_15_32_08.tar.gz | mysql -binary-mode=1 -uroot -p xxxxxxxxx 出現
            mysql: unknown option ‘-a’

            要請您再幫忙一下看是什麼問題了
            感恩

            • shazi7804表示:

              rocky:

              –binary-mode 這個參數,前面有兩個 – ,可能blog無法顯示出來造成您誤解

              您可用 mysql –help 找到相對應的參數。

  2. rocky表示:

    如果只要備份單一資料庫要怎麼改啊

    • shazi7804表示:

      rocky:

      你可以從這邊修改
      # get all databases
      all_db=databasename

給 Mr. 沙先生一點建議

彙整

分類

展開全部 | 收合全部

License

訂閱 Mr. 沙先生 的文章

輸入你的 email 用於訂閱