Skip to content

Instantly share code, notes, and snippets.

erincerys / table-rotation.sql
Last active August 29, 2015 13:56
Rotate MySQL table data within the data store selectively, using a date range on a column
View table-rotation.sql
-- If your table is really large, and it very well may be if you've the need to rotate it, you'll want to make sure the column that holds the timestamp/date is indexed.
-- WARNING: This script incurs a write lock for the duration of the trigger recreation and table rename. If your application can't handle this, you might lose data!
-- Create our new skeleton table
-- Replace the interval with the timespan of data you'd like to keep, or
-- replace this statement with one that stores an id in the local variable of which all higher will be retained
SELECT MIN(id) INTO @end_id FROM x WHERE created_at >= DATE(NOW()) - INTERVAL 3 WEEK;
erincerys /
Last active August 29, 2015 14:01
Creates an SQL structure dump of a MySQL database ordered so as not to create a view before the BASE TABLE exists.
db_login="-h$db_host -u$db_user -p$db_pass "
# Dump BASE TABLES (not views)
echo "Dumping base tables..."
erincerys /
Created June 23, 2014 21:07
Backup attachment cache and MySQL schema of a Kayako helpdesk deployment to an AWS S3 bucket if there are any changes when compared to the previous backup
s3cpath="$(which python) /root/s3cmd-1.5.0-beta1/s3cmd"
if [ ! -d ${storagedir} ] ; then mkdir ${storagedir} ; fi
cd ${storagedir}
curdate=$(date -u '+%Y%m%d')
erincerys /
Created July 21, 2014 00:21
Download truecrypt 7.1a, verify the SHA256 checksum against an independent and trusted source and install the package
# You might want to change the download url to another if your system is not using the amd64 architecture
wget -c -q
TC_CHECKSUM=`sha256sum ./truecrypt-7.1a-linux-x64.tar.gz | awk '{print $1}'`
TC_VERIFIED_CHECKSUM=`curl -s | grep -A19 -i sha256 | grep 'truecrypt-7.1a-linux-x64.tar.gz$' | awk '{print $1}'`
if [ -n "${TC_VERIFIED_CHECKSUM}" ] ; then
if [ "${TC_CHECKSUM}" == "${TC_VERIFIED_CHECKSUM}" ] ; then
tar xfz truecrypt-7.1a-linux-x64.tar.gz
erincerys /
Last active August 29, 2015 14:11
An rc.local script tailored for MySQL servers running on AWS EC2 i2.xlarge instances. It will create the partition table on ephemeral storage, format the disk ext4, download a data store from S3, and start the MySQL server.
#!/bin/sh -e
# This is a draft and may not work for you.
# Dependencies:
# - aws python cli toolset
# - qpress / quicklz
# -
# Search an array for an element
erincerys /
Created January 23, 2015 17:41
A shell script to monitor a MySQL replication connection.
# Monitor and report a status code somewhere about a MySQL slave's replication connection
# Useful for when traditional monitoring cannot be achieved due to firewalling.
D=`date -u +"%Y%m%d%H%M"`
mysqlparams='-h... -ureplicator -p...'
query='show slave status\G'
erincerys /
Last active August 29, 2015 14:14
Shell script helper to close or suspend an open LUKS container
# Description:
# Take action to secure an open LUKS container
# Helpful when bound to a keyboard shortcut in your favorite DE/WM
# Must be ran as root - take care in securing access to this script
# I added a selective line to /etc/sudoers.d such as:
# %wheel ALL=(ALL) NOPASSWD: /bin/bash /path/to/script/ close /path/to/mountpoint
erincerys /
Last active September 27, 2015 02:33
Backup some directories on an EC2 instance to S3 as a tarball, prune old archives and publish result to an SNS topic
# INVOCATION: bash $0
# LOG LOCATION: $storagedir/$prefix-DATESTAMP.log
# - Install this in your crontab on an appropriate schedule. Filenames are to minute resolution.
# - Depending on the method in which AWS CLI was installed, it may not be in $PATH when cron executes. This will be evident in your local user mail and the script log
erincerys / commands.cfg
Created May 18, 2015 18:54
nagios3 notifier for pushbullet integration
View commands.cfg
# I've only included the two command additions to the this commands.cfg file
# 'notify-host-by-push' command definition
define command{
command_name notify-host-by-push
command_line /usr/bin/curl --header 'Authorization: Bearer <your_access_token_here>' -X POST --header 'Content-Type: application/json' --data-binary '{"type": "note", "title": "* $NOTIFICATIONTYPE$ Host Alert: $HOSTNAME$ is $HOSTSTATE$ **", "body": "***** Nagios ***** Notification Type: $NOTIFICATIONTYPE$ Host: $HOSTNAME$ State: $HOSTSTATE$ Address: $HOSTADDRESS$ Info: $HOSTOUTPUT$ Date/Time: $LONGDATETIME$", "email": "<>"}'
# 'notify-service-by-push' command definition - ident
define command{
erincerys /
Created September 26, 2013 16:59
Execute mysql query and write to file utilizing a streaming cursor
#! /usr/bin/env python
from sys import argv
import csv
import MySQLdb
from MySQLdb import cursors
mysql_params = [argv[1], argv[2], argv[3], argv[4], argv[5]]
output_file = argv[6]
mysql_query = argv[7]