First time with MySQL !

Everybody will have a day when they decide to try something which they knew for a long time. That day is very unique, they will try their best, seek help from many, refer to many things and finally it ends up in merry when they succeed in running it. I have been learning SQL for the past 50 days but I could not have a hands on experience till today. I decided to somehow run SQL today, and the reason I am on FC4 made me to go in for MySQL. My Window’ian friends have always been complaining that I am becoming more into Open source softwares / Linux there days, and to add fire into that burning fuel I wanted to successfully run MySQL :DI began the endeavor by first installing MySQL server using the Add/Remove programs option. After successfully installing MySQL, I searched the Main Menu for a MySQL option. Not finding them there ( I know where to go now ) I opened up the terminal and typed ‘mysql’. To make me smile, MySQL started up and showed me ‘ mysql> ‘ prompt. As I assumed to have successfully got into MySQL, i tried my first command straight away !

mysql > CREATE TABLE PDATA (NAME CHAR(10), PHONENUM CHAR(10));

Like hitting me in the head, it said ” Error: No database found”. I resolved to use the help to find something

mysql> help

The help gave me many commands but there wasn’t one how to create a database. Luckily I had my LinuxGuru on line, so IM’d him to find what to do. He said I have to create a database using,

$ mysqladmin create data_base

It created the corresponding database “data_base” and gave me the result ” Query OK, 0 rows selected (0.01 sec) “. I again assumed the process has ended and I can go on with creating tables, issuing the CREATE TABLE statement. To my surprise again got an error message but this time as ” Permission denied to access database as @localhost”.

This time he suggested me to refer the MySQL site for help. Then I created the required permission for me to login as parth@localhost and access the MySQL server. Then I again tried to login as user, but still wasn’t able to as it needed a slightly different command. Finally, after again referring to the MySQL Site I was able to finally start MySQL as user and create tables and insert data into it.

The entire procedure for a first-time-run of MySQL is as follows,

1. Loging in as ‘root’ or obtaining root permission with ’su’, issue the following command at shell,

[root@localhost]$ mysql –user=root mysql

mysql > GRANT ALL PRIVILEGES ON *.* TO ‘user_name’@'localhost’
IDENTIFIED BY ‘passwd’ WITH GRANT OPTION;

This assigns ‘all’ privileges to the user ‘user_name@localhost’ who can be identified and can access mysql using the password ‘passwd’.

2. We have to create a database for the user to use, by issuing the following command as root ( or su ),

[root@localhost]$ mysqladmin create user_database

which will give the reply as ” Query OK, 0 rows affected (0,01 sec) ” on successful creation of the database.

3. Login as user by issuing the following command in the shell,

[user_name@localhost]$ mysql –user=user_name –password=passwd

The welcome text will indicate your successful login: “Welcome to MySQL Monitor.” and you will get the ‘mysql’ prompt.

4. Now, we have to change the database into ours by issuing the following command,

mysql > USE user_dbase

which will be confirmed with “Database changed”.

5. Now, you can issue the MySQL statements at the ‘mysql’ prompt and your ‘user-dbase’ will be the used database.

To Create Table : mysql > CREATE TABLE PDATA (NAME CHAR(10), PHONENUM CHAR(10));To add data into the table : mysql > INSERT INTO PDATA (NAME , PHONENUM) VALUES ( Name , 9880098800 );

To view the table : mysql > SELECT * FROM PDATA;