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