近來在練習一些 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
您好:
我參考你的備份指令可以備份
但還原時無法還原
zcat database.2014-08-17_15_19_32.gz | mysql -uroot -ppassword -Dmrbs
請問 -Dmrbs這一段該如何修改
謝謝您
rcoky:
您好:
原文寫的比較不清楚,-Dmrbs 為你要還原的資料庫名稱
zcat database.2014-08-17_15_19_32.gz | mysql -uroot -ppassword database
若是要還原新的資料庫,請先將database建立起來。
已修改,感謝指教
您好:我用下列指令要還原其中一個資料庫
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/’.
請問這要如何除錯,謝謝!
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 格式再匯入。
我是用centos 7 的mariadb
執行
zcat Mysql.2015-03-16_15_32_08.tar.gz | mysql -binary-mode=1 -uroot -p xxxxxxxxx 出現
mysql: unknown option ‘-a’
要請您再幫忙一下看是什麼問題了
感恩
rocky:
–binary-mode 這個參數,前面有兩個 – ,可能blog無法顯示出來造成您誤解
您可用 mysql –help 找到相對應的參數。
如果只要備份單一資料庫要怎麼改啊
rocky:
你可以從這邊修改
# get all databases
all_db=databasename