Last active
March 16, 2024 13:36
-
-
Save lazyjerry/810949a6dc205b653faa1910d05dfc21 to your computer and use it in GitHub Desktop.
Mysql 資料備份,參考來源/修改自: https://gist.github.com/tleish/1c6e788c84f59200446b
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
#============================================================================== | |
#TITLE: mysql_backup.sh | |
#DESCRIPTION: script for automating the daily mysql backups on development computer | |
#AUTHOR: tleish | |
#DATE: 2013-12-20 | |
#VERSION: 0.4 | |
#USAGE: ./mysql_backup.sh | |
#CRON: | |
# example cron for daily db backup @ 9:15 am | |
# min hr mday month wday command | |
# 15 9 * * * /Users/[your user name]/scripts/mysql_backup.sh | |
#RESTORE FROM BACKUP | |
#$ gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname] | |
#============================================================================== | |
# CUSTOM SETTINGS | |
#============================================================================== | |
# directory to put the backup files | |
# TODO 這裡輸入路徑位置 | |
BACKUP_DIR=/backup_folder/ | |
# MYSQL Parameters | |
# TODO 這裡輸入帳號密碼 | |
MYSQL_UNAME=root | |
MYSQL_PWORD= | |
# Don't backup databases with these names | |
# Example: starts with mysql (^mysql) or ends with _schema (_schema$) | |
IGNORE_DB="(^mysql|_schema$)" | |
# include mysql and mysqldump binaries for cron bash user | |
# TODO 請確認 mysql 執行環境 | |
PATH=$PATH:/usr/local/mysql/bin | |
# Number of days to keep backups | |
# TODO 如果是定時備份則需注意要保留的檔案日期。在 backup_database() 中有 gzip 的參考 | |
KEEP_BACKUPS_FOR=30 #days | |
#============================================================================== | |
# METHODS | |
#============================================================================== | |
# YYYY-MM-DD | |
TIMESTAMP=$(date +%F) | |
function delete_old_backups() | |
{ | |
echo "Deleting $BACKUP_DIR/*.sql.gz older than $KEEP_BACKUPS_FOR days" | |
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$KEEP_BACKUPS_FOR -exec rm {} \; | |
} | |
function mysql_login() { | |
local mysql_login="-u $MYSQL_UNAME" | |
if [ -n "$MYSQL_PWORD" ]; then | |
local mysql_login+=" -p$MYSQL_PWORD" | |
fi | |
echo $mysql_login | |
} | |
function database_list() { | |
local show_databases_sql="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'" | |
echo $(mysql $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}') | |
} | |
function echo_status(){ | |
printf '\r'; | |
printf ' %0.s' {0..100} | |
printf '\r'; | |
printf "$1"'\r' | |
} | |
function backup_database(){ | |
backup_file="$BACKUP_DIR/$TIMESTAMP.$database.sql.gz" | |
output+="$database => $backup_file\n" | |
echo_status "...backing up $count of $total databases: $database" | |
local qrgs = "--databases --events --routines --triggers" | |
# 如果是備份用途的話,建議壓縮,如果是轉移需求,把 gzip 拿掉比較好檢查 | |
# $(mysqldump $qrgs $(mysql_login) $database > $backup_file) | |
$(mysqldump $qrgs $(mysql_login) $database | gzip -9 > $backup_file) | |
} | |
function backup_databases(){ | |
local databases=$(database_list) | |
local total=$(echo $databases | wc -w | xargs) | |
local output="" | |
local count=1 | |
for database in $databases; do | |
backup_database | |
local count=$((count+1)) | |
done | |
echo -ne $output | column -t | |
} | |
function hr(){ | |
printf '=%.0s' {1..100} | |
printf "\n" | |
} | |
#============================================================================== | |
# RUN SCRIPT | |
#============================================================================== | |
delete_old_backups | |
hr | |
backup_databases | |
hr | |
printf "All backed up!\n\n" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment