Skip to content

Instantly share code, notes, and snippets.

Last active December 26, 2016 17:44
What would you like to do?
Scripts checksums, binlog purge
# This script will use the results from pt-table-checksum ran on the master and verify
# if any chunks are actually different. This is necessary because of the way NDB
# bundles up transactions within a single epoch. If a table is found to be inconsistent,
# pt-table-sync will use an explicit full table lock on the master during the comparison.
# Save output to log file
reportfile=$(mktemp /tmp/checksumreport.XXXXX)
exec 2>&1 >$reportfile
function note()
echo -e $note
function send_report()
mydate=`date +%c`
echo "To:" >>/tmp/email
echo "From:" >>/tmp/email
echo "Subject: Checksum Report for $mydate" >>/tmp/email
cat $reportfile >>/tmp/email
# We use ssmtp in lieu of setting up a full sendmail to
# simply relay to local accounts on local MX host
cat /tmp/email | ssmtp -t
function check_if_finished()
# Check if checksum completed
elapsed=$(mysql -BNe "select TIME_TO_SEC(TIMEDIFF(NOW(), ts)) from percona.checksums WHERE tbl = 'checksum_status'")
if [ "$elapsed" = "" ]; then
# Checksum has not completed
return 1
# Checksum has completed. We can proceed.
return 0
function verify_checksum()
# Get tables that failed checksum
tables=($(mysql -BNe "SELECT GROUP_CONCAT(DISTINCT(CONCAT(db,'.',tbl))) FROM percona.checksums WHERE this_crc <> master_crc"))
for i in "${tables[@]}"; do
note "\n-- Verifying consistency via lock-syncing of '$i'..."
pt-table-sync --sync-to-master --wait 120 -t $i --execute --verbose --lock 2 h=localhost,u=checksum,p=checksum1
note "-- Done"
# Main
# We loop/sleep 5 times waiting for checksum script on master to complete.
# If after 5 sleeps, 30 minutes, still not done, send notice and exit
note "== Begin - `date`=="
while [ $loop -lt 6 ]; do
if [ "$checkok" -eq 1 ]; then
note "-- Checksum not complete. Sleeping 5 minutes.\n"
sleep 300
if [ "$checkok" -eq 0 ]; then
note "-- Checksum complete."
note "-- Verifying checksum."
# break out of the loop
# increment counter
note "\n== Complete - `date` =="
# Send the report
# Cleanup
rm -f $reportfile /tmp/email
# This script will execute a consistency check between master and slaves.
# It is a simple wrapper for pt-table-checksum.
# Temp log file
checklog=$(mktemp /tmp/consistencycheck.XXXXX)
exec 2>&1 >$checklog
# Truncate checksums
mysql -v -e "TRUNCATE TABLE percona.checksums"
# Run checksums
pt-table-checksum --no-check-binlog-format --no-check-replication-filters --engines ndbcluster --tables Archive.a_agents_promotion,Archive.a_bar_games
# Mark checksums completed
mysql -v -e "INSERT INTO percona.checksums VALUES('percona','checksum_status',1,NULL,NULL,NULL,NULL,0,0,0,0,NOW())"
# Log output to syslog
logger -t consistency -f $checklog
# Cleanup
rm -f $checklog
# Get all NDB tables
# mysql information_schema -BNe "SELECT CONCAT(table_schema,'.',table_name) FROM tables
# WHERE table_schema NOT IN ('mysql',' ndbinfo','percona','information_schema')
# AND engine = 'ndbcluster'" >/root/ndb_tables.txt
# Remove filtered tables
# for i in `grep replicate_ignore_table /etc/mysql/my.cnf | awk '{print $3}'`; do sed -i "/$i/d" ndb_tables.txt; done
# Concat to 1 line, comma-separated
# cat ndb_tables_to_checksum.txt | while read line; do echo -n "${line},"; done
# To save disk space, purge out all but the most recent 7 binary logs.
# If replication has broken, do not delete any binlogs.
# Temp file for output
thislog=$(mktemp /tmp/purgebinlogs.XXXXX)
exec 2>&1 >>$thislog
# Do the purging
oak-purge-master-logs -S /opt/mysql/run/mysqld.sock -u percona -p Percona2015 -r 7 -n 1 --pro-slaves --skip-show-slave-hosts --verbose
# Log to syslog
logger -t purgebinlogs -f $thislog
# Cleanup
rm -f $thislog
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment