Linux Help

mySQL Setup Guide   
Printable Version

DJG's Setting up MySQL users and databases.
Created on November 25th, 1999.

MySQL is becoming more and more popular. So if you want to install it, get the RPM, Deb, tarball, or whatever and let's get started.

Packages needed:

Debian: mysql-client and mysql-server. Obtain it from ftp.debian.org or use apt-get install.

RedHat: MySQL and MySQL-client. Obtain it from http://www.rpmfind.net/RPM

Tarball: Obtain it from http://www.mysql.com

Now that MySQL is installed, you may want to know how to configure it. For this guide, 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 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


Having trouble? Got questions? Require further assistance? If so please feel free to visit our Help Forums and ask the experts!


Copyright © 1997 - 2014 Private World Domination Inc. All rights reserved.
Linux is a registered trademark of Linus Torvalds. All other trademarks and copyrights are the property of their respective owners.
| Contact Us | Link to Us | RSS Feed | Staff |

DNS Hosting by easyDNS