How to create MySQL Database, Tables in Linux

I have used Red Hat Linux for creating MySQL database so kindly make changes in the commands as per your distribution.

Installing MySQL

Make sure mysql package is installed in your machine which you can check using

# rpm -qa | grep mysql

If nothing comes up you can manually install using

# yum install mysql

Now once the package is installed by default

Admin user : root

Password : blank

You will have to assign a password to root

# mysqladmin -u root password 'new-password'

# mysql -u root -p
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1108
Server version: 5.0.77 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD('new-password') WHERE user='root';
mysql> FLUSH PRIVILEGES;

Creating database, tables and inserting values into the tables

# mysql -u root -p
Enter Pasword:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1108
Server version: 5.0.77 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> show databases;           -- "Will list all the databases"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+

mysql> create database mycomp;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mycomp             |
+--------------------+
mysql> use mycomp; --"Will connect to specified database"
Database changed
mysql> create table employee (SerialNo varchar(3),Name char(20),Age varchar(2));
Query OK,
0 rows affected (0.01 sec)
mysql> describe employee;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| SerialNo | varchar(3) | YES  |     | NULL    |       |
| Name     | char(20)   | YES  |     | NULL    |       |
| Age      | varchar(2) | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show tables; --To list out available tables in the selected database
+----------------+
| Tables_in_test |
+----------------+
| employee       |
+----------------+
1 row in set (0.00 sec)
mysql> INSERT INTO employee VALUES ('1','Deepak Prasad','23');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO employee VALUES ('2','Amit Dubey','24');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from employee;
+----------+---------------+------+
| SerialNo | Name          | Age  |
+----------+---------------+------+
| 1        | Deepak Prasad | 23   |
| 2        | Amit Dubey    | 24   |
+----------+---------------+------+
2 rows in set (0.00 sec)
mysql> quit Bye

Restarting mysql database

# /etc/init.d/mysqld restart
OR
 # service mysqld restart

Make sure your database is up even after restarting your linux machine

# chkconfig mysqld on