Useful MySQL shell commands

by Stephen on November 4, 2009 · 1 comment

in MySQL, Server Admin Tips

We rarely have to use MySQL via command line but if you happen to, here are some basic but useful MySQL commands.

 

 

How to login to MySQL:

mysql -u root -p

 

How to create a MySQL database via Shell:

After you logged in, execute this command (replace dbname with the name of the database you want to create):

mysql> create database dbname;
Output:
Query OK, 1 row affected (0.01 sec)

 

How to delete a MySQL database via Shell:

mysql> drop database dbname;
Output:
Query OK, 0 rows affected (0.05 sec)

 

How to create a MySQL User via Shell:

(replace dbusername with the username you want to create)

mysql> create user dbusername;

 

How to set a password for the MySQL User you just created:

(replace passhere with the password you would like to set)

mysql> set password for dbusername = password('passhere');

 

How to grant privileges for the username to the database:

mysql> grant all privileges on dbname.* to dbusername@localhost identified by 'passhere';

 

How to display database tables in a MySQL database?

 
First enter the database by executing:

mysql> use dbname;

 
Now show tables:

mysql> show tables; 

 
To search for a specific table without remembering its exact name:
(where keyword is the partial name you remember)

mysql> show tables in dbname like '%keyword'; 

 

How to exit MySQL via Shell:

mysql> exit

{ 1 comment… read it below or add one }

Ameas May 13, 2011 at 10:53 am

Thank you very much. ^ _ ^

Reply

Leave a Comment

Previous post:

Next post: