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?