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