Scripts checksums, binlog purge

checksumreport.sh

#!/bin/bash

# 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()
{
	note=$1
	echo -e $note
}

function send_report()
{
	mydate=`date +%c`
	echo "To: dept@domain.com" >>/tmp/email
	echo "From: dept@domain.com" >>/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
	fi

	# Checksum has completed. We can proceed.
	return 0
}

function verify_checksum()
{
	# Get tables that failed checksum
	IFS=","
	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"
	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`=="

loop=1
while [ $loop -lt 6 ]; do

	check_if_finished
	checkok=$?

	if [ "$checkok" -eq 1 ]; then
		note "-- Checksum not complete. Sleeping 5 minutes.\n"
		sleep 300
	fi

	if [ "$checkok" -eq 0 ]; then
		note "-- Checksum complete."
		note "-- Verifying checksum."
		verify_checksum

		# break out of the loop
		break
	fi

	# increment counter
	((loop++))
done

note "\n== Complete - `date` =="

# Send the report
send_report

# Cleanup
rm -f $reportfile /tmp/email

consistencycheck.sh

#!/bin/bash

# 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

purgebinlogs.sh

#!/bin/bash

# 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