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;
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]';
page revision: 12, last edited: 24 May 2017 19:09