An occasional outlet for my thoughts on life, technology, motorcycles, backpacking, kayaking, skydiving...

Friday, December 5, 2008

Mysql One-liners

Things I'm tired of googling for...

# pragmatic access to system variables
# 1. There are 2 types of system variables GLOBAL and SESSION
# 2. LOCAL is a synonym for SESSION
# 3. SELECT requires "@@[type].[var_name]" syntax, so you might as well use it for SET also.
SELECT @@global.thread_cache_size;
SET @@global.thread_cache_size=16;

# Investigating the tables on your DB instance
SELECT table_schema, table_name, engine FROM information_schema.tables WHERE engine != 'MyIsam';

# Change password
SET PASSWORD [FOR user] = PASSWORD('some password string')
SET PASSWORD [FOR user] = 'ExistingPasswordHashProbablyFromAnotherServer'

# empty all the tables in a schema (from BASH using my MYsql alias which includes the U & P)
schema=elections; MYsql -hdev1 -B -N -e "select concat('delete from ',table_name,';') from information_schema.tables where table_schema='$schema';" | MYsql -hdev1 $schema

No comments:

Post a Comment


About Me

My photo

Believe it or not, I've never been to jail. Not even for a night. I quit going to bars by the time my fake ID was unnecessary, and I quit hacking into businesses and governments once they started paying me.

Now I just provide for my family and try not to notice politics. I get too angry when I watch how much of my income is taken from me, how it is used, and who is doing it. For the same reason, I can't bear to acknowledge celebrities.

After high school, I went to U of K but the outdated computer engineering curriculum and the seemingly endless amounts of money to be made with my internet specific skills drew me to larger tech markets. I have worked in Silicon Valley, New York City, and have settled in Atlanta.