MySQL Database Server Installation and configuration

MySQL is a fast, stable and true multi-user, multi-threaded SQL database server. SQL (Structured Query Language) is the most popular database query language in the world. The main goals of MySQL are speed, robustness and ease of use.




Mysql Database Installation in Debian


If you want to install the Mysql base system as well as a textual client run the following command from your shell

#apt-get install mysql-server-4.1 mysql-client-4.1


Now that MySQL is installed, you may want to know how to configure it.

Configuring Mysql Database

We assume that mysqladmin and mysql, which should have been installed when you got the MySQL packages. First, if you haven’t done this already, set the root password for MySQL. You can do this by typing:


#mysqladmin -u root password ‘passwordyouwant’

Now that the root password is set, connect to your MySQL server:

#mysql -u root -p

It will prompt you for a password. Make sure to enter the one you just/previously set. You should now be left at a prompt which looks like this:


mysql>

At this point, you will create basic permissions for a user and database. For my setup, I want to allow access to localhost to all databases, and a computer which is also on the network, which is referred to as “windowsbox” will have access to all databases.

To access the user, host databases, etc… type this;

mysql> use mysql;

Database changed

mysql>

To give localhost permission to access all databases, enter this:

mysql> insert into
-> host(host,db,Select_priv, Insert_priv, Update_priv,
-> Delete_priv, Create_priv, Drop_priv)
-> values(‘localhost’,'%’,'Y’,'Y’,'Y’,'Y’,'Y’,'Y’);


Note, the ‘%’ can be replaced with a database name. The ‘%’ is a wildcard.

Following the previous format, to allow access from another hostname (in this case “windowsbox”) add this:

mysql> insert into
-> host(host,db,Select_priv, Insert_priv, Update_priv,
-> Delete_priv, Create_priv, Drop_priv)
-> values(‘windowsbox’,'%’,'Y’,'Y’,'Y’,'Y’,'Y’,'Y’);


Again, ‘%’ is used as a Wild-Card.

To create a user ‘djg’ who can access the MySQL server from localhost, type this:


mysql> insert into
-> user (host, user, password)
-> values(‘localhost’,'djg’,password(‘mypassword’));



To give the user access from another hostname, domain, etc… add other entries accordingly. For example,to give user djg access from windowsbox:


mysql> insert into
-> user (host, user, password)
-> values(‘windowsbox’,'djg’,password(‘mypassword’));


Now… to give the user permissions to access a database from localhost, add this entry and change with your appropriate information:


mysql> insert into
-> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
-> values (‘localhost’,'mydatabase’,'djg’,'Y’,'Y’,'Y’,'Y’,'Y’,'Y’);


To give the user permissions from windowsbox, add this:

mysql> insert into
-> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
-> values (‘windowsbox’,'mydatabase’,'djg’,'Y’,'Y’,'Y’,'Y’,'Y’,'Y’);


Now, type: quit and you will exit mysql.

Finally, create the actual database (in this case, ‘mydatabase’) type this:

#mysqladmin -u root -p create mydatabase

After prompting you for a password, it should create the database. At this point, you must reload MySQL. Type:

#mysqladmin -u root -p reload

After prompting you for a password it should reload MySQL.


Congratulations. If all goes well you have set up a user and database with MySQL. You may now create /edit/delete/etc tables as much as you’d like.

Also, please note that by default, MySQL will open up network port 3306 to allow remote requests.

If you do not want this port open, append “--skip-networking” when running safe_mysqld to start
the daemon. Debian users can edit /etc/init.d/mysqld and change this line:

/usr/bin/safe_mysqld > /www.null 2>&1 &

to this:

/usr/bin/safe_mysqld --skip-networking > /www.null 2>&1 &

Now whenever running /etc/init.d/mysql start, it will not open up port 3306.

If you want o install mysql database in ubuntu you can use the same procedure.

Categories: mysql