Useful MySQL shell commands
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
