Select the id of a record that is 6 months old in MySQL

When you need the id of a single record that is 6 months old, and there's a timestamp or date column. Useful if the id is auto-incremented primary key and you need to do a DELETE query in IDs less than returned id (which would be MUCH faster than doing it by a non-indexed timestamp field).

SELECT MAX(id) FROM mail_links WHERE timestamp BETWEEN DATE_SUB(DATE_SUB(NOW(), INTERVAL 6 MONTH), INTERVAL 1 DAY) AND DATE_SUB(NOW(), INTERVAL 6 MONTH);

show number of messages in postfix queue

postqueue -p | tail -n 1 | cut -d' ' -f5

Shell script to determine if an IP is pingable

#!/bin/bash

# default host
HOST="10.0.0.1"

# check if there's a host as an argument
if [ $# -eq 1 ]
 then
 HOST=$1
fi

count=$(/sbin/ping -c 1 -t 2 $HOST | /usr/bin/grep 'received' | /sw/bin/awk -F',' '{ print $2 }' | /sw/bin/awk '{ print $1 }') ;
echo "$count"

Grab the load average as a usable number

uptime | awk -F "load average: " '{ print $2 }' | cut -d, -f1

another way:

uptime | awk '{print $8}' | cut -d "," -f 1

yet another:

uptime | sed -e "s/^.*[a-z]: //; s/,.*//"

Vacuum the SQLite database to speed up Firefox on os x

Go Tools -> Error Console and paste the line below in the "Code" field, enter and wait. It actually makes a noticeable difference.

Components.classes["@mozilla.org/browser/nav-history-service;1"].getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL("VACUUM");

Bring up a thunderbird compose message window from the command line on os x

Kinda lame, because Thunderbird can't already be open.

/Applications/Thunderbird.app/Contents/MacOS/thunderbird-bin -compose

Find the most recently touched file in a directory

ls -rt1 | tail -1

escape potential spaces and parentheses in output

ls -rt1 /Users/user/Documents/*.doc | tail -1 | sed 's/ /\\ /g' | sed 's/(/\\(/g' | sed 's/)/\\)/g'

Kill os x apps from the command line

ps axww | grep -v grep | grep -i itunes | awk '{print $1}' | xargs kill -9

Delete MYSQL tables that match a pattern. Run from the command line

Not terribly safe if you use the password on the cmd line.

mysql -u USER -pPASSWORD dbname -e "show tables" | grep "[0-9A-Z][0-9A-Z]_[0-9A-Z][0-9A-Z][0-9A-Z]_blah" | gawk '{print "drop table " $1 ";"}' | mysql -u USER -pPASSWORD dbname 

Change the gain (output volume) of an entire directory of mp3s

find . -iname '*.mp3' -exec mp3gain -r -m +6 -c {} \;

Import a csv file (with header line) into mysql with LOAD DATA

LOAD DATA LOCAL INFILE '/home/me/yo.csv' INTO TABLE 1by1_email 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY "\n"  IGNORE 1 LINES (fields,fields...);

When thunderbird magically hides your folder pane and you can't get it back

Search for 'localstore.rdf'. In it change this:

collapsed="true"

to this:

collapsed="false"

Select and recurse thru an entire category tree in mysql (this is 5 levels deep)

SELECT
root.category_code AS root_category_code,
root.category_name AS root_category_name,
sub1.category_code AS sub1_category_code,
sub1.category_name AS sub1_category_name,
sub2.category_code AS sub2_category_code,
sub2.category_name AS sub2_category_name,
sub3.category_code AS sub3_category_code,
sub3.category_name AS sub3_category_name,
sub4.category_code AS sub4_category_code,
sub4.category_name AS sub4_category_name
FROM categories_loop AS root
LEFT JOIN categories_loop AS sub1 ON sub1.category_parent = root.category_code
LEFT JOIN categories_loop AS sub2 ON sub2.category_parent = sub1.category_code
LEFT JOIN categories_loop AS sub3 ON sub3.category_parent = sub2.category_code
LEFT JOIN categories_loop AS sub4 ON sub4.category_parent = sub3.category_code
WHERE root.category_parent = '0'
ORDER BY 
root_category_code, 
sub1_category_code, 
sub2_category_code, 
sub3_category_code,
sub4_category_code

Show a table structure, with comments in MySQL

SHOW FULL COLUMNS FROM myTable

Set auto-incement value in MySQL

ALTER TABLE tbl AUTO_INCREMENT = 1000;

Start/Stop MySQL on OS X Leopard

/Library/StartupItems/MySQLCOM/MySQLCOM [start|stop|restart]

search and replace text in files on the command line

find . -name "*.php" -print | xargs sed -i 's/foo/bar/g'

use '-maxdepth 1' before -name if you want to restrict to current directory

run X programs on your mac (say, on a parallels linux host or remotely)

Run this on the mac side:

ssh -X user@host

Find and delete duplicate records in MYSQL

DELETE t2 from table AS t1, table AS t2 WHERE t1.field = t2.field AND t2.id < t1.id