MYSQL basic command

MySQL Commands List

Here you will find a collection of basic MySQL statements

General Commands

USE database_name	Change to this database. You need to change to some database when you first connect to MySQL mysql> SELECT DATABASE();
DESCRIBE table_name
SET PASSWORD=PASSWORD('new_password')
OPTIMIZE [table]
SHOW variables
SHOW DATABASES	Lists all MySQL databases on the system. To find out which database is currently selected, use the DATABASE() function
show tables  [FROM database_name]	Lists all tables from the current database or from the database given in the command
SHOW FIELDS FROM table_name
SHOW COLUMNS FROM table_name	These commands all give a list of all columns (fields) from the given table, along with column type and other info.
SHOW INDEX FROM table_name	Lists all indexes from this tables.
show open tables
show procedure status
show function status
show binary log	Lists the binary log files on the server
show master log	Lists the binary log files on the server

How to troubleshoot mysql database server high cpu usage/slowness

1. Firstly find out what's causing server CPU high usage
• vmstat 2 20top -b -n 5
2. check mysql error log , slow query log etc from /etc/my.cnf
• innodb_buffer_pool_size=20000M 
4. mysql > show engine innodb status\G

Other administration commands

• display in vertical : -E, --vertical
• variables : --print-defaults
• save output : -tee=
• save html : -H
• alive : mysqladmin -p ping
• status : mysqladmin -p ,
• How to Find out current Status of MySQL server?
• mysqladmin -u root -p extended-status
• How to check MySQL version?
• : mysqladmin -u root -p version
• How to stop MYSQL?
• : mysqladmin -p shutdown
• How to set root password?
• : mysqladmin -u root password YOURNEWPASSWORD
• How to check all the running Process of MySQL server?
• mysqladmin -u root -p processlist
• How to kill a process


• : mysqladmin kill id
• How to create a job
• http://www.mysqltutorial.org/mysql-triggers/working-mysql-

Configuration Files

• my.cnf - usually located in /etc/my.cnf, /etc/mysql/my.cnf

Directories

• basedir ($MYSQL_HOME)
  - e.g. /opt/mysql-5.1.16-beta-linux-i686-glib23
• datadir (defaults to $MYSQL_HOME/data)
• tmpdir (important as mysql behaves unpredictability if full)
• innodb_[...]_home_dir
  - mysql> SHOW GLOBAL VARIABLES LIKE '%dir'