Skip to content

Instantly share code, notes, and snippets.

Avatar

Matthew utdrmac

View GitHub Profile
View vip_move
// If the host with the VIP is up, this removes it
ip addr del 10.20.43.184/32 dev eth2
// Add VIP to new host
ip addr add 10.20.43.184/32 dev eth2
/sbin/arping -c 5 -U -I eth2 10.20.43.184
@utdrmac
utdrmac / max_auto_increment_tables.sql
Created Aug 19, 2014
Find db.table.columns that are > 90% auto_increment capacity
View max_auto_increment_tables.sql
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE,
(CASE DATA_TYPE
WHEN 'tinyint' THEN 255
WHEN 'smallint' THEN 65535
WHEN 'mediumint' THEN 16777215
WHEN 'int' THEN 4294967295
WHEN 'bigint' THEN 18446744073709551615
END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
) AS MAX_VALUE,
AUTO_INCREMENT AS CURRENT_VALUE,
@utdrmac
utdrmac / monitor_mysql.pl
Last active Jan 26, 2016
Perl Script to Monitor MySQL Replication and Send Email on Failure
View monitor_mysql.pl
#!/usr/bin/perl
use DBI;
use Email::MIME;
use Log::Log4perl qw(get_logger :levels);
use Data::Dumper;
use strict;
# For backgrounding
@utdrmac
utdrmac / order-by-rand.sql
Last active Aug 29, 2015
Solution to ORDER BY RAND()
View order-by-rand.sql
-- Option 1
SELECT *
FROM title t
WHERE kind_id = 1 AND id >= FLOOR(1 + RAND() * (SELECT MAX(id) FROM title)) LIMIT 1;
-- Option 2
SELECT id, title
FROM title t RIGHT JOIN
(SELECT CEIL(RAND() * (SELECT MAX(id) FROM title WHERE kind_id = 1)) AS id) h USING (id);
@utdrmac
utdrmac / size-innodb-log-files.sh
Last active Oct 21, 2019
Sizing InnoDB Log Files
View size-innodb-log-files.sh
currentSize=`mysql $DSN -BNe "SELECT ROUND(@@innodb_log_file_size/1024/1024, 0)"` \
currentNum=`mysql $DSN -BNe "SELECT @@innodb_log_files_in_group"` \
totalCurSize=`mysql $DSN -BNe "SELECT ROUND((@@innodb_log_file_size * @@innodb_log_files_in_group)/1024/1024, 2)"` \
startSeq=`mysql $DSN -BNe "SHOW ENGINE InnoDB STATUS\G" | grep sequence | grep -o "[0-9]\+$"`; sleep 60; \
endSeq=`mysql $DSN -BNe "SHOW ENGINE InnoDB STATUS\G" | grep sequence | grep -o "[0-9]\+$"`; \
logSizeReq=`echo "scale=2; ((($endSeq-$startSeq)/1024/1024)*60)/$currentNum" | bc`; \
echo; echo "innodb_log_file_size is ${currentSize}MB * ${currentNum} log files = ${totalCurSize}MB"; \
echo "innodb_log_file_size setting should be at least ${logSizeReq}MB for ${currentNum} log files."; echo;
@utdrmac
utdrmac / per-engine-size.sql
Created Feb 4, 2015
Per-Engine Size Usage
View per-engine-size.sql
SELECT count(*) TABLES,
concat(round(sum(table_rows)/1000000,2),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac, engine
FROM information_schema.TABLES
WHERE table_type != 'VIEW' AND table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
GROUP BY engine;
@utdrmac
utdrmac / pushbullet_slave_monitor.sh
Last active Dec 30, 2015
Pushbullet Slave Watcher
View pushbullet_slave_monitor.sh
#!/bin/bash
# A title for the notification
title="Slave - ${HOSTNAME}"
# PushBullet API Token
# https://www.pushbullet.com/#settings/account
token="C17CdC7b0XXXXXXXX1idCdATgVjbfEY"
# Send a notification every X minutes
@utdrmac
utdrmac / find_checksums.sh
Created Jan 20, 2016
Finding Tables for Checksum
View find_checksums.sh
#!/bin/bash
mysql -e "TRUNCATE TABLE percona.checksums"
pt-table-checksum --no-check-binlog-format --no-check-replication-filters --engines ndbcluster --tables $below
# 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
@utdrmac
utdrmac / checksumreport.sh
Last active Dec 26, 2016
Scripts checksums, binlog purge
View 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
View gracewatch.c
/*
* Gracewatch 1.0
* Matthew Boehm <matthew@matthewboehm.com>
*
* Gracewatch is a multi-threaded MySQL monitoring solution developed for
* a client that had no in-house monitoring team.
*
* Using libConfig (http://www.hyperrealm.com/libconfig/), gracewatch reads
* a list of servers and credentials and spawns a pthread for each server.
* The thread connects to the host and every minute preforms a mysql_ping()