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 : *****
... 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;
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: *****
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
Deleting a database
mysql> CREATE DATABASE mydatabase
Show which all tables are there in a given database
mysql> DROP DATABASE mydatabase
Select another database for use
mysql> SHOW TABLES;
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).