Skip to content

Instantly share code, notes, and snippets.

@borama
Last active June 9, 2023 08:06
Show Gist options
  • Save borama/06609442f00b4f108cb545677e6a7e62 to your computer and use it in GitHub Desktop.
Save borama/06609442f00b4f108cb545677e6a7e62 to your computer and use it in GitHub Desktop.
Shell script to export data from the given table partition and delete it aferwards
#!/bin/bash
# "panic button" when run in a for loop:
# exit
database="MY_DATABASE"
export_dir="/mnt/backups/my-archive"
set -e
# reset max exec. time on exit
function cleanup {
local exit_status=$?
mysql -e "SET GLOBAL max_execution_time = 120000;"
exit $exit_status
}
trap cleanup INT TERM EXIT
start_time=$SECONDS
# switch off query time limit
mysql -e "SET GLOBAL max_execution_time = 0;"
if [ $# -lt 2 ]; then
echo "usage export-partition.sh <db_table_name> <time_string>"
echo "e.g. export-partition.sh yearly_archived_table y2011"
echo "or export-partition.sh monthly_archived_table m201911"
echo "or export-partition.sh weekly_archived_table w201950"
exit 1
fi
table=$1
shift
time_string=$1
shift
temp_table="${table}_${time_string}"
export_file_name=$temp_table
# count rows in partition
echo "Counting rows in partition ${table}#${time_string}..."
rows=`mysql $database -Nse "SELECT COUNT(*) FROM $table PARTITION($time_string)"`
echo "Found $rows row in partition"
echo "Exchanging ${table}#${time_string} partition to ${temp_table}"
mysql $database -e "CREATE TABLE $temp_table LIKE $table"
mysql $database -e "ALTER TABLE $temp_table REMOVE PARTITIONING"
mysql $database -e "ALTER TABLE $table EXCHANGE PARTITION $time_string WITH TABLE $temp_table"
# sanity check
count=`mysql $database -Nse "SELECT COUNT(*) FROM $temp_table"`
echo "Found $count rows in $temp_table"
if [ $rows = $count ]; then
echo "Sanity check OK!"
else
echo "Sanity check failed: $rows rows vs. $count count"
exit 1
fi
echo "Exporting $temp_table to $export_dir/$table/$export_file_name.gz"
mkdir -p $export_dir/$table
mysqldump --create-options --single-transaction \
$database $temp_table | gzip -f > $export_dir/$table/$export_file_name.gz
ls -lh $export_dir/$table/$export_file_name.gz
echo "Dropping table $temp_table"
mysql $database -e "DROP TABLE $temp_table"
echo "Dropping partition ${table}#${time_string}"
mysql $database -e "ALTER TABLE $table DROP PARTITION $time_string"
elapsed_time=$(($SECONDS - $start_time))
echo "Done in $(($elapsed_time / 60)) min $(($elapsed_time % 60)) sec"
echo
@borama
Copy link
Author

borama commented Feb 7, 2020

This likely won't make much sense without reading the accompanying arcticle at dev.to.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment