mmrwoods

# dump all databases once every 24 hours
45 4 * * * root nice -n 19 su - postgres -c "pg_dumpall --clean" | gzip -9 > /var/local/backup/postgres/postgres_all.sql.gz

# vacuum all databases every night (full vacuum on Sunday night, lazy vacuum every other night)
45 3 * * 0 root nice -n 19 su - postgres -c "vacuumdb --all --full --analyze"
45 3 * * 1-6 root nice -n 19 su - postgres -c "vacuumdb --all --analyze --quiet"

# re-index all databases once a week
0 3 * * 0 root nice -n 19 su - postgres -c 'psql -t -c "select datname from pg_database order by datname;" | xargs -n 1 -I"{}" -- psql -U postgres {} -c "reindex database {};"'

mmrwoods

#!/bin/bash

# Nasty, intentionally verbose script to start/stop mongod
# Mark Woods - March 2010

RUNTIME_USER='nobody' # only applicable on Linux, otherwise runs as current user
LOGPATH='/var/log/mongodb/mongod.log'
CONFIGFILE='/etc/mongod.conf'
DBPATH='/opt/mongodb/data/db'
BINPATH='/opt/mongodb/bin/mongod'

mmrwoods

#!/bin/bash

# Create a clean SQL dump of each non-template postgres database
# Should be run as root to avoid any permissions issues

dest_dir=/var/local/backup/postgres

db_list_sql="SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn ORDER BY datname"

for db in $(su - postgres -c "psql -At -c '$db_list_sql'"); do

mmrwoods

#!/bin/bash

if [ "$1" == "" ] || [ "$2" == "" ]; then
  echo "usage: $0 [options] <host> <port>"
  echo
  echo "options:"
  echo "   -i <interval>   Number of seconds between checks [default: 5]"
  exit 1
fi

mmrwoods

#!/bin/bash

# Boostrap a new CentOS VM (also works with RHEL!)
# Has been used on CentOS 5.10, 6.2, 6.3, 6.4 and 6.5
# Based on https://gist.github.com/thickpaddy/1759284

# TODO: set ssh port

if test $(whoami) != "root" ; then
  echo "Error: $0 must be run as root, exiting..."

mmrwoods

# note: MySQL_maintenance.pl can be obtained from http://danbuettner.net/mysql-tools/

# check, optimise and repair MySQL databases (extended check on Sunday, quick every other day)
15 3 * * 0 root nice -n 19 /usr/local/sbin/MySQL_maintenance.pl --check-type=extended --optimize --repair --exclude-dbs=information_schema
15 3 * * 1-6 root nice -n 19 /usr/local/sbin/MySQL_maintenance.pl --check-type=quick --optimize --repair --exclude-dbs=information_schema > /dev/null

# dump all mysql databases once every 24 hours
15 4 * * * root nice -n 19 mysqldump --opt --all-databases --allow-keywords | gzip -9 > /var/local/backup/mysql/mysql_all.sql.gz

mmrwoods

#!/usr/bin/perl -w


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#
#	MySQL_maintenance.pl
#
#	By Dan Buettner - drbuettner-at-gmail.com
#	12 Nov 2005
#

mmrwoods

require "mysql_big_table_migration_helper"

class AddIndexOnSomeColumnToSomeTable < ActiveRecord::Migration

  extend MySQLBigTableMigrationHelper
  
  def self.up
    add_index_using_tmp_table :some_table, :some_column
  end

mmrwoods

Keybase proof

I hereby claim:

• I am mmrwoods on github.
• I am mmrwoods (https://keybase.io/mmrwoods) on keybase.
• I have a public key ASCzrDxWj3rVBEYaFP5PA0_jWGNQdhiYpwcJ8_d-9NcJowo

To claim this, I am signing this object:

mmrwoods

#!/bin/bash

# Vacuum, analyze and re-index all non-template postgres databases
# Should be run as root to avoid any permissions issues

db_list_sql="SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn ORDER BY datname"

for db in $(su - postgres -c "psql -At -c '$db_list_sql'"); do
  su - postgres -c "vacuumdb --analyze --quiet $db"
  su - postgres -c "PGOPTIONS='--client-min-messages=warning' reindexdb --quiet $db"