- 一次備份 n 個月
- 限制每 n 個月一次或是每次執行只備份前面符合條件的 n 個月
總之，技術細項：
- 使用 data 指令得知現況時間並計算出備份範圍
- 使用 mysqldump 搭配 --where 指令匯出指定 timestamp 資料
- 使用 tar -zcvf 備份資料為 tar.gz 格式
- 使用 gunzip -t 測試壓縮資料
- 使用 md5sum 建立壓縮檔案的驗證檔
#!/bin/bash
month_offset=0
month_range=6
current_month=`date +"%m"`
if [ ${current_month:0:1} -eq '0' ] ; then
current_month=${current_month:1:1}
fi
month_offset=$(( $(($current_month + $month_range - 1)) % $month_range ))
timestamp_begin_date=`date +"%Y-%m-01 00:00:00" -d "-$(($month_offset + $month_range)) month"`
timestamp_end_date=`date +"%Y-%m-01 00:00:00" -d "-$month_offset month"`
timestamp_init_date=`date +"%Y-%m-01" -d "-$month_offset month"`
timestamp_out_date=`date +"%Y-%m-%d" -d "$timestamp_init_date -1 day" `
job_id=`date +"%Y-%m" -d "-$(($month_offset + 1)) month"`
DIR=/data/routine
log_dir=$DIR/log
dump_dir=$DIR/db
mkdir -p $log_dir $dump_dir
echo "[INFO] Job ID: $job_id , Begin: $timestamp_begin_date , End: $timestamp_end_date"
echo "[INFO] Working Dir: $DIR , log: $log_dir , export: $dump_dir"
echo "[INFO] Output suffix: $timestamp_out_date"
echo
arget_host=MyDBServerIP
target_user=UserID
target_pass=UserPassword
target_db=DatabaseName
target_table=("table1" "table2" "table3")
for table in "${target_table[@]}" ;do
if [ ! -f "$dump_dir/$table.$timestamp_out_date.sql" ] ; then
cmd="time mysqldump -h $target_host -u $target_user -p$target_pass --where \"timestamp >= '$timestamp_begin_date' AND timestamp < '$timestamp_end_date'\" --single-transaction --databases $target_db --tables $table > $dump_dir/$table.$timestamp_out_date.sql"
echo $cmd;
fi
done
echo
echo -n "Wanna go (y/N): "
read ans
if [ "$ans" == 'Y' ] || [ "$ans" == 'y' ] ; then
for table in "${target_table[@]}" ;do
output_file=$table.$timestamp_out_date.sql
output_archive_file=$output_file.tar.gz
output_archive_check_file=$output_archive_file.md5
echo
echo "build target: $output_file"
echo "\t$output_archive_file"
echo "\t$output_archive_check_file"
echo "..."
if [ ! -f "$dump_dir/$output_file" ] ; then
cmd="time mysqldump -h $target_host -u $target_user -p$target_pass --databases $target_db --tables $table --single-transaction --where \"timestamp >= '$timestamp_begin_date' AND timestamp < '$timestamp_end_date'\" > $dump_dir/$output_file"
echo $cmd
time mysqldump -h $target_host -u $target_user -p$target_pass --databases $target_db --tables $table --single-transaction --where "timestamp >= '$timestamp_begin_date' AND timestamp < '$timestamp_end_date'" > $dump_dir/$output_file
fi
if [ ! -f "$dump_dir/$output_archive_file" ] ; then
echo "time tar -zcf \"$table.$timestamp_out_date.sql.tar.gz\" \"$table.$timestamp_out_date.sql\""
cd $dump_dir && time tar -zcf "$output_archive_file" "$output_file"
fi
if [ -f "$dump_dir/$output_archive_file" ] && [ ! -f "$dump_dir/$output_archive_check_file" ] ; then
echo "time gunzip -t $output_archive_file && time md5sum $output_archive_file > $output_archive_check_file"
cd $dump_dir && time gunzip -t $output_archive_file && time md5sum $output_archive_file > $output_archive_check_file
fi
done
fi
若還有其他興趣的話，可以再結合 AWS glacier 的上傳，例如：
aws_glacier_bin="java -jar /path/uploader-0.0.8-SNAPSHOT-jar-with-dependencies.jar "
aws_glacier_region=https://glacier.region-id.amazonaws.com
aws_glacier_dir=db-log
aws_glacier_partsize=134217728
if [ "$aws_glacier" == 'Y' ] || [ "$aws_glacier" == 'y' ] ; then
for table in "${target_table[@]}" ;do
output_file=$table.$timestamp_out_date.sql
output_archive_file=$output_file.tar.gz
output_archive_check_file=$output_archive_file.md5
echo
echo "selected: $output_archive_file"
if [ -f "$dump_dir/$output_archive_file" ] && [ -f "$dump_dir/$output_archive_check_file" ] ; then
echo "do upload: $output_archive_file"
cmd="time $aws_glacier_bin --endpoint \"$aws_glacier_region\" -v \"$aws_glacier_dir\" --multipartupload \"$output_archive_file\" --partsize $aws_glacier_partsize "
echo $cmd;
cd $dump_dir && eval $cmd;
fi
done
fi
