[转]Mysql Database Server Configuration in Debian

Mysql Database Server Configuration in Debian

From :     

Introduction

MySQL is a fast, stable and true multi-user, multi-threaded SQL database server. (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 Installation in Debian

If you want to install the Mysql base 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. For this article, I assume that you have the programs: 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 :

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 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 > / 2>&1 &

to this:

/usr/bin/safe_mysqld --skip-networking > / 2>&1 &

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

If you don't want do do from command line and you want use web for simple use of check for tools.I would personall suggest install phpmyadmin from debian packages and it is very easy to use.