MySQL Commands

To log in to the MySQL command shell, use:-

mysql -u #username# -p 

You will be prompted to enter the password for #username#

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

mysql> describe user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI | NULL    |       |
| User                  | char(16)                          | NO   | PRI | NULL    |       |
| Password              | char(41)                          | NO   |     | NULL    |       |


| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.00 sec)

mysql> create database bps;
Query OK, 1 row affected (0.02 sec)

To restore mysql database from a dump file, just type the command below:-

mysql -u #username# -p #database# < #dump_file#

CREATE USER 'bps'@'localhost' IDENTIFIED BY '***';

GRANT USAGE ON * . * TO 'bps'@'localhost' IDENTIFIED BY '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0   MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

CREATE DATABASE IF NOT EXISTS `bps` ;

GRANT ALL PRIVILEGES ON `bps` . * TO 'bps'@'localhost';

From Dev411: The Code Wiki

 % mysql -h localhost -p -u root
 Enter password: ***
 mysql> grant all on mydb.* to username@localhost identified by 'password';
 mysql> quit

If the user already exists, exclude the password component:

 mysql> grant all on mydb.* to username@localhost;

Now log in as the user and create the database:

 % mysql -h localhost -p -u username
 Enter password: password
 mysql> create database mydeb;
 Query OK, 1 row affected (0.01 sec)
 mysql> quit