utdrmac

  # Encrypt initially using
  # openssl rand -base64 32 >/etc/ssl/private/mykey.bin && chmod 600 /etc/ssl/private/mykey.bin
  # openssl aes-256-cbc -a -salt -in /usr/local/mysql/keyring -out /usr/local/mysql/keyring.asc -pass file:/etc/ssl/private/mykey.bin
  echo $echo_n "Decrypting Keyring..."
  rm -f /usr/local/mysql/keyring
  if [ ! -e /usr/local/mysql/keyring.asc -o ! -e /etc/ssl/private/mykey.bin ]
  then
      echo "Encrypted keyring and/or keyfile not found. Aborting MySQL startup."
      exit 1;
  fi

utdrmac

SELECT t.table_schema, t.engine, t.table_name, c.column_name, c.column_type
FROM information_schema.tables AS t 
INNER JOIN information_schema.columns AS c ON c.table_schema = t.table_schema 
AND c.table_name = t.table_name
LEFT OUTER JOIN information_schema.innodb_sys_tables AS ist 
ON ist.name = concat(t.table_schema,'/',t.table_name)
LEFT OUTER JOIN information_schema.innodb_sys_columns AS isc 
ON isc.table_id = ist.table_id AND isc.name = c.column_name 
WHERE c.column_type IN ('time','timestamp','datetime')
AND t.table_schema NOT IN ('mysql','information_schema','performance_schema')

utdrmac

/* 
 * guider 1.0
 * Matthew Boehm <mboehm@paypal.com> <matthew@matthewboehm.com>
 *
 * GUIDer reads in a file of global identifiers (GUIDs) and retrieves
 * obfuscated data out of databases. This is done using pipe() to create
 * a producer/consumer set-up. We create a pool of mysql connections
 * and pthread out user-supplied number of threads to handle data.
 * Each thread waits and reads 1 GUID off the front of the pipe() and processes
 * it, outputting results to stdout. Once done with that set of queries, this

utdrmac

/* 
 * 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()

utdrmac

#!/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

utdrmac

#!/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

#!/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

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

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

-- 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);