Redirect apache

cat /usr/local/httpd/htdocs/index.html

grep date in filename

$ ls | egrep staff-list-[0-9]{2}-[A-z]{3}-[0-9]{4}.csv
staff-list-22-aug-2011.csv

MySQL get Start(Sunday) and End(Saturday) of week for a given date

select subdate(date('2011-08-06'), INTERVAL dayofweek(date('2011-08-06')) DAY) as StartOfWeek, adddate(date('2011-08-06'), INTERVAL 7-dayofweek(date('2011-08-06')) DAY) as EndOfWeek;
select subdate(date('2011-08-07'), INTERVAL dayofweek(date('2011-08-07')) DAY) as StartOfWeek, adddate(date('2011-08-07'), INTERVAL 7-dayofweek(date('2011-08-07')) DAY) as EndOfWeek;
select subdate(date('2011-08-13'), INTERVAL dayofweek(date('2011-08-13')) DAY) as StartOfWeek, adddate(date('2011-08-13'), INTERVAL 7-dayofweek(date('2011-08-13')) DAY) as EndOfWeek;

Extract single table from gzipped mysqldump file.

# with drop and create statements
$ zcat event_tables_prod1.sql.gz | sed -n "/^-- Table structure for table \`WEEKLY_EXCEPTION_REPORT\`/,/^-- Table structure for table/p" >
# without drop and create statements
$ zcat event_tables_prod1.sql.gz | sed -n "/^-- Dumping data for table \`WEEKLY_EXCEPTION_REPORT\`/,/^-- Dumping data for table /p" > exceptions_only_prod1.sql

Fix Corrupt relay logs

Master_Log_File: The name of the master binary log file from which the I/O thread is currently reading.
Relay_Log_File: The name of the relay log file from which the SQL thread is currently reading and executing.

Relay_Master_Log_File: The name of the master binary log file containing the most recent event executed by the SQL thread.


-- The status below is most often cause by network connection between slave and master being dropped while slave while slave is reading master's binary log to create the relay log.
-- The "Last_error:" message states that it could be master binary or slave relay log corruption. It is usually slave relay corruption because it is much easier to corrupt this log.

mysql dump over ssh

mysqldump -uroot -p epictide events quartz | gzip -c | ssh michaelfw@128.147.97.132 'cat > ~/dump.sql.gz'

bash sleep 45 - 60 seconds.

$ number=0
$ let number="$RANDOM % 15 + 45"
$ sleep $number

Describe a table in MS SQL

exec sp_columns ;

MySQL buffer cheat sheet

myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands.
for session
set @@local.myisam_sort_buffer_size=4294967296


Setting innodb_buffer_pool_size

InnoDB optimization basics



http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size

Transferring using ssh and pipes.

# no compression, use remote mysqlcommand to run script.
$ mysqldump -u username -p'password' db-name | ssh user@remote.box.com mysql -u username -p'password db-name

# create tarball on remote system
$ sudo tar zcvf - /wwwdata | ssh root@192.168.1.201 "cat > /backup/wwwdata.tar.gz"

# other example from http://blog.gr80.net/post/transfer-your-website-across-servers-using-ssh-tar-mysqldump/
$ sudo tar -zcf – {local_dir} | ssh {remote_user}@{remote.domain} tar -C {/path/to/remote/dir} -zxf -

$ sudo mysqldump -h{local_dbserver} -u{local_dbuser} -p{local_dbpass} {local_dbname} > – | ssh {ssh_username}@{your.domain} mysql -u{remote_dbuser} -p{remote_dbpass} {remote_dbname} < -

MySQL temp files in data

Alter table w/ keys disabled creates temp tables w/ names starting w/ #. Copies table to this and then copies over original.

enable keys - no temp files in data while "repair by sorting". Why? done in RAM.

vim use sudo to save changes.

$ vim /etc/group  :w !sudo tee % 

Verify that a file has constant number of fields.

# awk -F~ '{print NF}' < 20100912PATLAB.txt | sort | uniq
25


add -c to uniq to get count of occurrences.

# awk -F'\t' '{print NF}' < PTINFO.EXT | sort | uniq -c
4420846 38
3 39
3 40
1 41
3 42

Compare contents of 2 directories

$ MMM=mydir
$ comm -12 <(ls /usr/local/mypath/$MMM) <(ls ./$MMM)

-1 remove left hand contents
-2 remove common contents
-3 remove right hand contents

Remove spaces from filenames.

for files in file*;do mv "$files" `echo $files | tr ' ' '_'`;done

Add user to 4.x

INSERT INTO user VALUES('localhost','epictide',PASSWORD('password'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y', '', '', '', '', 0, 0, 0);

RPM remove multiple packages.

[michael@dev-tam-01 ~]$ sudo rpm -e mysql-5.0.77-4.el5_4.1
error: "mysql-5.0.77-4.el5_4.1" specifies multiple packages
[michael@dev-tam-01 ~]$
[michael@dev-tam-01 ~]$ rpm -q --queryformat "%{name}.%{arch}\n" mysql-5.0.77-4.el5_4.1
mysql.x86_64
mysql.i386
[michael@dev-tam-01 ~]$ sudo rpm -e mysql.x86_64

Notes about myisam_repair_threads

How can I speed up bulk loading or repairing of MyISAM tables?

What version is installed

1. openssh
sudo rpm -qa | grep -i openssh

2. mod_jk
telnet localhost
GET /x

2. Apache
httpd -v

3. MySQL
mysqadmin -u -p versioninfo

Add a startup script

/sbin/chkconfig --add sshd