Mysql in Linux : Tutorial for impatient geeks

Mysql is a Relational Database Management system (RDBMS) , In RDBMS data is stored in the form of rows and columns which is in the form of tables and relation can be determined from the table structure. Mysql was owned by Swedish Company Mysql AB , Currently Oracle Corporation hold the ownership for Mysql.

Mysql seems best choice for supporting light and medium traffic webservers running through LAMP stack. It is also used for Heavy traffic webservers by distributing the queries using Master-Slave or Master-Master architecture .

• Installing Mysql : Lets start by installing Mysql Database server

In CentOS /Redhat/Fedora  Mysql can be installed through Yum Repository

# yum install mysql-server

On Ubuntu Mysql can be installed using aptitude

# aptitude install mysql-server

 

• Staring Mysql Service and Log In Mysql Prompt

Start Mysql service to initialize the database daemon

# /etc/init.d/mysqld start

By default mysql doesn’t have any default password and mysql prompt can be opened directly using linux’s root user

# mysql -uroot -p

You have to press enter after when prompted for password you will see am mysql prompt ie example below.

mysql>

Tip : to exit mysql prompt type exit or quit

For security purpose you may want to assign password for mysql prompt for root user , you can assign password using mysqladmin command from linux shell

#mysqladmin -uroot password YOURPASSWORD

Now you can login using newly assigned password again in mysql

# mysql -uroot -pYOURPASSWORD

 

• Playing with Database

Database is like a container which can contain many tables , these tables contain the data      stored in the form of rows and columns

Lets create our first database named testdb

mysql> create database testdb;

We have created the database but to create , modify or delete tables we need to go insde the database , to go inside the database we will use mysql’s USE directive

mysql> use testdb;

We can display all databases within mysql engine using SHOW directive

mysql> show databases;

 

• Playing with tables

Before doing any table related operating remember to select the database using USE directive as described above.

We will create a table test_table within database testdb with attributes name and pincode in below example

mysql> use testdb;mysql> create table test_table(city varchar(20), pincode INT);

The above statement will create a mysql table named test_table with attribute city and and pincode , In the above statemet you can see keyword varchar(20) and INT , these are called variables and define the type of attribute.

INT is used to define a numeric value ie Integer , data coming under pincode can be integer only .

VARCHAR is used to define a variable character ,it can be mixture of alphabets and nmeric values. (20) implies city attribute can be no longer then 20 characters.

To display all tables in a database we can use SHOW directive

mysql> SHOW TABLES;

To show the structure of a table in database we can use DESC directive

mysql> desc test_table;

Lets do an INSERT Operation on table test_table and fill it with some data

mysql> INSERT INTO test_table(city,pincode) values(‘Columbus’,52737);

Now we can show table content by using SELECT directive and see the inserted data

mysql> SELECT * from test_table;

* This is just a scratch of Mysql and their is lot to be covered ie Mysql engine , Information schema , roles , privileges , JOINS , security , server configuration ,complex table and database operation etc