Skip to content

Instantly share code, notes, and snippets.

@marshallm
Last active September 28, 2017 07:13
Show Gist options
  • Save marshallm/a48203dd317693c776cf95f3c3eee374 to your computer and use it in GitHub Desktop.
Save marshallm/a48203dd317693c776cf95f3c3eee374 to your computer and use it in GitHub Desktop.
Baseline pt-archiver bash script for MySQL
#!/bin/bash
source mysql_access
TODAY=$(date +"%Y-%m-%d_%H%M%S")
CHECK_RETAINER=$(
mysql api_test \
-u$MYSQL_USER \
-p$MYSQL_PASS \
-h$SOURCE_HOST \
--socket=$MYSQL_SOCK \
-s -N -e"SELECT IF( DATEDIFF(MAX(F1DATETIME),MIN(F2DATETIME)) > 365 ,'Pass','Fail') diff \
FROM table"
)
GET_INTERVAL=$(
mysql api_test \
-u$MYSQL_USER \
-p$MYSQL_PASS \
-h$SOURCE_HOST \
--socket=$MYSQL_SOCK \
-s -N -e"SELECT DATE_ADD(MIN(F1),INTERVAL 1 HOUR) AS DATE \
FROM table"
)
if [ $CHECK_RETAINER == "Pass" ]
then
printf "Starting archive for:\n\n Table: table_name\n Timestamp: $TODAY\n Archiving records prior to: $GET_INTERVAL\n Destination: $DEST_HOST\n" >> archive.log
../pt-archiver --source h=$SOURCE_HOST,u=$MYSQL_USER,p=$MYSQL_PASS,D=$MYSQL_SOURCE_DB,t=$MYSQL_TABLE \
--dest h=$DEST_HOST \
--file $FILE_OUT \
--commit-each \
--where "F1<'$GET_INTERVAL'" >> archive.log 2>&1
else
printf "ERROR: Archive Run For $TODAY failed:\nRetainer Check: $CHECK_RETAINER\n Attempted Interval: $GET_INTERVAL\n" >> archive.log
fi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment