Skip to content

Instantly share code, notes, and snippets.

@rootpd
Created November 15, 2022 09:07
Show Gist options
  • Save rootpd/435cc17669ece1abfc7e9ed331d221e7 to your computer and use it in GitHub Desktop.
Save rootpd/435cc17669ece1abfc7e9ed331d221e7 to your computer and use it in GitHub Desktop.
Script to rotate api_logs table and store the exported dump to the Google Cloud Storage
#!/bin/bash
set -e
helpFunction()
{
echo ""
echo "Usage: $0 -c PATH_TO_CONFIG_FILE -n DATABASE_NAME -b BUCKET_FOLDER [ -d DATE_TO_EXPORT ]"
echo -e "\t-b Bucket folder selecting target project (dennikn.sk, denikn.cz)"
echo -e "\t-c Path to mysql defaults config file containing host, user and password."
echo -e "\t-d Target date you want to export, preferably in Y-m-d format (e.g. 2019-12-31). If not provided, previous day is used."
echo -e "\t-n Name of the database to export (e.g. predplatne)"
echo ""
exit 1 # Exit script after printing help
}
DEFAULTS_FILE=
BUCKET_FOLDER=
DATABASE_NAME=
DATE_FROM=`date --date="-1 day" +"%Y-%m-%d"`
while getopts :d:c:b:n: option
do
case "${option}" in
b) BUCKET_FOLDER=${OPTARG} ;;
c) DEFAULTS_FILE=${OPTARG} ;;
d) DATE_FROM=`date --date="${OPTARG}" +"%Y-%m-%d"` ;;
n) DATABASE_NAME=${OPTARG} ;;
:)
echo ""
echo "Missing option argument for -$OPTARG" >&2
helpFunction
exit 1
;;
?) helpFunction ;;
esac
done
shift $((OPTIND-1))
if [ -z ${DEFAULTS_FILE} ]; then
echo ""
echo "Missing or empty PATH_TO_CONFIG_FILE."
helpFunction
fi
if [ -z ${BUCKET_FOLDER} ]; then
echo ""
echo "Missing or empty BUCKET_FOLDER."
helpFunction
fi
if [ -z ${DATABASE_NAME} ]; then
echo ""
echo "Missing or empty DATABASE_NAME."
helpFunction
fi
DATE_TO=`date --date="${DATE_FROM} +1 day" +"%Y-%m-%d"`
EXPORT_FILE="api_logs.`date --date="${DATE_FROM}" +"%Y%m%d"`.sql.gz"
EXPORT_BUCKET="gs://nlogger-archive/${BUCKET_FOLDER}/predplatne/api_logs"
WHERE_COND="created_at >= '${DATE_FROM}' AND created_at < '${DATE_TO}'"
echo "Exporting api_logs ($DATE_FROM) to file ${EXPORT_FILE}"
mysqldump --defaults-extra-file=${DEFAULTS_FILE} ${DATABASE_NAME} --tables api_logs --where="${WHERE_COND}" | gzip > ${EXPORT_FILE}
echo "Uploading exported file to Google Cloud Storage (${EXPORT_BUCKET})"
gsutil cp ${EXPORT_FILE} ${EXPORT_BUCKET}
echo "Removing dumped records from DB (${WHERE_COND})"
mysql --defaults-extra-file=${DEFAULTS_FILE} ${DATABASE_NAME} -e "DELETE FROM api_logs WHERE ${WHERE_COND}"
echo "Removing local file ${EXPORT_FILE}"
rm ${EXPORT_FILE}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment