Connecting to a database server using mysql client:
mysql -u username -p -h host_name dbname
Find out what database is the current database:
Find out the version of mysqld:
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