Basic setup of MySQL in GNU/Linux

MySQL is a robust, lean database ideal for use on the web. In fact many of the forums and blogs use this database to store and manage the content. Here I will explain how I set up the MySQL database on my machine. The main reason for documenting this is because of the difficulty I encountered in connecting to the database in the first place. I was getting a error message when I tried connecting to the database using a user name even after successfully creating the database. So this post is more like a reference point for me rather than one targeted for general read.

I first downloaded and installed the following packages : mysql-server, mysql-client, mysql-admin. The latter package is a GUI front-end for easy administration of the database.

Next I opened up a terminal and executed the following command:

$ sudo mysql
Password : *****
mysql>_

... and I was dropped into the mysql prompt. Here I granted full rights on the database to myself by using the following command:

mysql> GRANT ALL PRIVILEGES ON *.* TO ravi@localhost 
     IDENTIFIED BY 'password'
     WITH GRANT OPTION;
mysql> quit

Since I was setting up the database for local use, I gave the machine name as localhost. If your machine is a part of the LAN and you have set up a domain, then change 'localhost' to your machine name as recognised by the domain.

Once I have given full rights to myself, I logged into the mysql database with my username as follows:

$ mysql -h localhost -u ravi -p
Enter Password: *****
mysql>_

The password is the one which was provided while giving GRANT access to my username previously and is different from my Linux account password. From now on, I can execute further commands to manipulate the database as well create and modify tables from the mysql prompt.

Some basic SQL manipulations which I found useful

Creation of database

mysql> CREATE DATABASE mydatabase

Deleting a database

mysql> DROP DATABASE mydatabase

Show which all tables are there in a given database

mysql> SHOW TABLES;

Select another database for use

mysql> USE my_other_database

MySQL Administrator - A very good GUI front-end for mysql administration

Once I have completed the above tasks, I fired up this GUI (mysql-admin) and I was presented with the login screen for accessing the database (see figure below).

Fig: mysql administrator GUI login interface

Fig: The administration interface after logging in

I entered my login name as well as the mysql password and was able to access the interface from which I was able to manipulate the database as well as the tables depending upon the rights allotted to me.
Resources
Official MySQL documentation online.
Also read : MySQL Cheat Sheet