MySQL 常用的指令

Login MySQL:  Two ways to login mysql
mysql --host=host_name --user=user_name --password=your_password db_name

mysql [-h hostname] [-u username] [-pyourpassword]

ex: mysql -h 192.168.1.1 -u root -proot , notice; -p and password without space

for safety reason, leave -p with blank and system will prompt you enter password.



MySQL command line:
Note that all text commands must be first on line and end with ';'
?                (\?) Synonym for `help'.
clear          (\c) Clear command.
connect     (\r) Reconnect to the server. Optional arguments are db and host.
exit            (\q) Exit mysql. Same as quit.
help          (\h) Display this help.
print           (\p) Print current command.
quit            (\q) Quit mysql.
status        (\s) Get status information from the server.
system      (\!) Execute a system shell command.
tee            (\T) Set outfile [to_outfile]. Append everything into given outfile.
use            (\u) Use another database. Takes database name as argument.

Databases:
show current database:  select database();

create database [database_name];

drop database [database_name];

show databases;

use [database_name];

Tables:
Create Table

Alter Table

show tables;

desc [table_name];

drop table  [table_name];

Others;
show engines; 

show status;

show processlist; 

SHOW INDEX FROM tablename; 顯示表中的索引信息。

list mysql user: 
select user,host from mysql.user;
show grants for 'root'@'host';





MySQL BACKUP:
mysqldump
mysqldump --tab=/path/to/some/dir --opt --full

SELECT INTO OUTFILE
SELECT * FROM [table_name]  into outfile 'filename.csv'