Text* Snippets: mysql code

Never been to TextSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

About this user

Jamie Wilkinson http://tramchase.com

« Newer Snippets

Older Snippets »

3 total XML / RSS feed

Convert MySQL tables to InnoDB

// Slower than MyISAM, but row locking is clutch if you're doing a lot of writes amirite?

for t in $(mysql --batch --column-names=false -e "show tables" mydbname |grep -v "exclude_this");
do
mysql -e "alter table $t type=InnoDB" mydbname;
done

From:
http://ludo.qix.it/archive/2005/04/convert-a-bunch-of-tables-to-innodb.html

to mysql bash database db innodb myisam by jamiew on Nov 19, 2007

Delete duplicate rows from a table

// Whoops, didja forgot to add a unique index to that column? No problem

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];
DROP TABLE old_table;
RENAME TABLE new_table TO old_table;

to mysql sql database db by jamiew on Nov 06, 2007

ssh tunnel with local port forwarding (MySQL)

// ssh tunnel remote mysql to a local port (in this case, 3307)

ssh -2 -f -C -N user@servername.com -L 3307/127.0.0.1/3306

to mysql ssh tunneling by jamiew on Apr 16, 2007