Printed from http://kimbriggs.com

MySQL Quick Setup Guide
(Suse) Linux Development Machine

Note: I have an updated version for Ubuntu MySQL setup.

Another installment in the KB "linux at home" series of help files. This decribes setting up a MySQL server on a home network file-server so that it can be accessed by other computers on the network.

Initial Setup of MySQL under Linux

Here is what will happen (should happen) whether you do the setup manually (probably not reading about MySQL here) or have it done automatically through various package management systems. I got this list from somewhere in the book "MySQL" by Paul DuBois. It is a very complete reference for using MySQL databases.

  1. Create a login account for the user and group used for running the MySQL server. Linux RPM = Done Automatically and the user and group are usually called "mysql".
  2. Obtain and unpack the MySQL software (or use your package management system to do it automagically).
  3. Run mysql_install_db script to initialize the data directory. Linux RPM = Done Automatically.
  4. Start the server. /path/to/mysqld# msyqld start. For Suse, Yast...System...Runlevel Editor (Services).
  5. Read Chapter 11 "General MySQL Administration" (that's what it says).

Initial MySQL Server Setup for Suse Linux 9.3

Use Yast...Add/Remove Software to install the MySQL server software. You will find that there is a user called "mysql" in the /etc/passwd file and that the directory /var/lib/mysql exists and is EMPTY.

Go to Yast...System...Services and start the MySQL service for the first time (i.e., "enable"). You should see some messages. Pay attention to the one in all capitals.

Creating/updating privelge database.
Installing all prepared tables.
Fill help tables.
PLEASE REMEMBER TO SET A PASSWORD FOR THE MYSQL ROOT USER
To do so, start the server and issue these commands
myshell# /usr/bin/mysqladmin -u root password 'new_password'
myshell# /usr/bin/mysqladmin -u root -h host_name password 'new_password'

Soapbox: So, as far as I can tell, the mysql "administrator/superuser" account is called "root" just to add some interesting ambiguity between MySQL and the linux system. There is no relationship between the two, but it's a challenge at times to remember that.

You can now go and check out the /var/lib/mysql directory and see that it got filled up with the "mysql" directory (the database about the databases), the "test" directory and a few files.

Connecting to the MySQL Server (Here Down = Any Linux)

If you haven't issued the passwords for the MySQL root user yet, you can access the database as a super-user with no password. So, in case it isn't said enough, the default install is COMPLETELY INSECURE. Use these commands to see some information from the mysql.user table.

me@myhost$ mysql
mysql> use mysql;
mysql> select user, host, password from user;

Note: If the above doesn't work, then MySQL is probably complaining you didn't supply the null password.

my_shell$ mysql -h localhost -u root -p
mysql> [MySQL asks for password, just hit Enter/Return for "no password"]
mysql> use mysql;
mysql> select user, host, password from user;

Give Root User a Password and Delete Anonymous User

  1. Give the MySQL root user a password using the two commands in the Yast instructions above.
  2. Delete the anonlymous user with these two commands:
    mysql> delete from user where user = ''; (it's and empty string)
    mysql> delete from db where user = '';

Granting Access and Connecting Remotely

Note:Before MySQL version 5.02 there will be no "create user" statement, so the next easiest thing to do seems to be implicitly create a non-root user using a "grant" statement. Below is a list of the steps I took to get my local database server accessible on my LAN. It is not necessary, but it is suggested, to use the same usernames as the linux login account, because MySQL will use this as a default if one is not specified (I probably answered the soapbox question above, but it is still confusing).

  1. Log on as root and create working databases to have something to "grant" on.

    mysql> create database database_name;

  2. Grant everything except grant permissions to non-root account, implicitly created (you include the single quotes):

    mysql> grant all on database_name.* to 'username'@'localhost' identified by 'a_password';

  3. Repeat it for a non-local account. It seems to use the fully-qualified name from the /etc/hosts file and not work with aliases. So, if you have a local domain name:

    mysql> grant all on database_name.* to 'username'@'host_name.domain_name' identified by 'a_password';

  4. Connect using the MySQL client to the remote server:

    myshell# mysql -h host_name.domain_name -u user_name -p


    And you should get prompted for your password to log on.

Networking Notes, etc.

I am not the one to tell you how to set up a network, but the above definitely won't work until each machine can connect to (ping) the database server. Here are the files I know of that you have to have set up correctly (there are likely others), through Yast or some other means:

I have lost the mysql.sock file more than once (probably in the dryer). If you see this filename in an error message, you may be able to simply restart the MySQL server (service) to re-create it. I have no idea why this happens.

Good luck,
Kim

Spider Oak Online Backup

 
CC License Ubuntu OpenOffice GIMP Graphics Geany Editor Firefox php.net Play Ogg what's this?