Misc

Connecting to a database server using mysql client:

mysql -u username -p -h host_name dbname

Find out what database is the current database:

SELECT DATABASE();

Find out the version of mysqld:

SELECT VERSION();

Running SQL from command line:

mysql -e "SELECT VERSION()"

Dumping the current db schema after a release

mysqldump --single-transaction -d -R -u mg -p -h 192.168.30.62 mg > 13.01.sql

Starting mysql with specific .cnf file:

/usr/bin/mysqld_safe --defaults-file=/etc/my.stg1.cnf --init-file=/etc/my.init_staging.sql

The script /etc/my.init_staging.sql contains SQL to be executed at startup time.

How to find detail information about a table (how many indexes it has)?

SHOW CREATE TABLE table_name;

Option File
Misc2

show index from tbname
show status
show variables
show table status
show databases
show tables
show column from tbname
show fields
show keys
show index

If opened_tables is too big, then table_cache is too small

If key_reads is big, then key_cache is too small. The cache hit rate is calculated by key_reads / key_read_requests

If handler_read_rnd is big, then you have a lot of query that requires MySQL to scan the whole tables or you have joins that does not use keys properly

Full join: join that do not use indexes

How can we determine if a table exist?

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';