====== MySQL Commands ======

===== Log in to MySQL command shell =====

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

  mysql -u #username# -p 

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


===== Basic commands =====

  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)
  

===== Create Database =====

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


===== Restore Dump file =====

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

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


===== Create User =====

  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';



===== Creating a MySQL User and Database =====

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