Steps to login to the mysql database server :

Unix OS Prompt > mysql –h hostname –u root –p (in case if the mysql database is in local machine, -h parameter is not required).

e.g. given below

admin:~> mysql -u root -p

Enter password:

List down all active process:

admin:~> show full processlist

Creating a database:

create database [DBNAME];

e.g.

mysql> create database test1234;

Query OK, 1 row affected (0.05 sec)

mysql>

Identify the databases present in the mysql server:

show databases;

e.g.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| | test               |

| test1234           |

+--------------------+

4 rows in set (0.00 sec)

mysql>

In the above, the information_schema and the mysql are the default databases present in the mysql server always.

Switch to a database:

use [db name];

e.g.

mysql> use test1234;

Database changed

mysql>

List all the tables in the dabase.

show tables;

e.g.

mysql> show tables;

Empty set (0.00 sec)

Create a table in the database:

Create table <tablename> (colname coltype, col2 coltype2,..).

e.g.

mysql> create table test123(eno integer);

Query OK, 0 rows affected (0.10 sec)

mysql>

Describe a table:

describe [table name];

e.g.

mysql> desc test123;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| eno   | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql>

DROP a database:

drop database [db name];

DROP a table:

drop table [table name];

List the sessions of the database:

mysql> SHOW PROCESSLIST ;

+--------+------+---------------------+----------------+---------+------+-------+------------------+

| Id     | User | Host                | db             | Command | Time | State | Info             |

+--------+------+---------------------+----------------+---------+------+-------+------------------+

 356505 | root | localhost           | test1234       | Query   |    0 | NULL  | SHOW PROCESSLIST |

+--------+------+---------------------+----------------+---------+------+-------+------------------+

1 rows in set (0.01 sec)

Display records of a table:

SELECT * FROM <tablename>;

SELECT * FROM [table name] WHERE [field name] = "<filtercindition>”;

Change user password:

In mySQL, the authentication and permissions happens for any user happens based on the permissions given to the IP address of a host name and hence the below commands to be used.

SET PASSWORD FOR 'user'@'hostname' = PASSWORD('password');

grant usage on *.* to <username>@localhost identified by 'passwordd';

grant all privileges on databasename.* to username@localhost;

Update database permissions/privileges.

FLUSH PRIVILEGES;

DROP A Column in a table

alter table [table name] drop column [column name];

Export of a database in a dump file:

mysqldump -u root -ppassword --opt > <filename for the dump>

e.g.

admin:~> mysqldump --all-databases

-- MySQL dump 10.13  Distrib 5.1.41, for unknown-linux-gnu (x86_64)

--

-- Host: localhost    Database:

-- ------------------------------------------------------

-- Server version       5.1.41-community

--

-- Current Database: `test`

--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-07-15 20:53:43

Export of a table from database:

mysqldump -c -u username –p password DBNAME TABNAME >  tablename.sql

Restore database from dump backup:

mysql -u username -ppassword <dbname>  < DB.SQL