MySQL Quick Setup Guide: Debian or Ubuntu Linux Development Server

Update: 2010-02: 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. There previous method I used to set the MySQL root user password does not work any longer. I use a GUI tool from MySQL, but links to the MySQL documentation for servers are below

What Happens During Setup of MySQL under Linux

Here is what happens 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 debs & RPMs = 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 debs & RPMs = Done Automatically.
4. Start the server. For Debian and Ubuntu done automatically. Seen in System/Desktop -> Administration -> Services

Debian vs. Ubuntu: Ubuntu linux is a derivative of Debian GNU/linux and much is the same when setting up MySQL. Two differences I can thing of: (1) Under Ubuntu linux, instead of logging in as the root user and issuing the commands listed, you would preface the commands with "sudo" (super-user do) and enter your password then. (2) The default MySQL settings under Ubuntu include having 2 or 3 extra server processes for clustering starting up that you don't need to do development work. You can uncheck all except the original "mysqld" process.

Initial MySQL Server Setup

1. Install the Software

   root@myhost# apt-get install mysql-server mysql-admin mysql-query-browser phpmyadmin

2. A Simple Test

   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 -h localhost -u root -p
Enter password: [Hit Enter/Return for NULL password, get welcomed by MySQL]
mysql> use mysql;
mysql> select user, host, password from user;

3. Delete the Anonymous User IF There is One

   I think this is deprecated, but if you see the anonymous user (no username) in the test above, here is how to delete the rogue account, once you are logged in as the root user.

   mysql> delete from user where user = '';
mysql> delete from db where user = '';

4. Give Root User a Password

   Update 2010-02: There is a long way to do this on a server. Here are the most recent help pages from MySQL:

   MySQL 5.0 Reset Root Password  |  MySQL 5.1 Reset Root Password

   If you are on a desktop development machine, the easiest thing to do is to install the package "mysql-admin". Click on "User Administration" and then the user "root" to update the password.

   

5. Granting Access and Connecting Remotely

   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. I use fixed IPs to make things easier for myself. Theorectically, you could do these things with the GUI MSQL Administrator, but as of Ubuntu 9.10 it is still prone to random crashes and it is unclear to me how to add a remote user using it.

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

   mysql> create database DATABASE_NAME;

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

   mysql> grant all on DATABASE_NAME.* to 'USER_NAME'@'localhost' identified by 'MY_PASSWORD';

   Repeat it for a non-local account. You can use host names if you maintain DNS. Otherwise, my fixed IPs are fine.

   mysql> grant all on DB_NAME.* to 'REMOTE_USER1'@'CLIENT1_IP' identified by 'A_PASSWORD';
mysql> grant all on DB_NAME.* to 'REMOTE_USER2'@'CLIENT2_IP' identified by 'A_PASSWORD';

   Connect using the MySQL client to the remote server:

   me@hyhost$ mysql -h SERVER_IP -u REMOTE_USER1 -p

   And you should get prompted for your password to log on. To be clear, when you use a remote computer and connect via PHPMyAdmin, then you only put REMOTE_USER1 in the username field, and do not include the IP address.

Notes

The software I listed at the top has some redundancy in it. MySQL Admin and MySQL Query Browser are the MySQL GUI products for interacting with the MySQL server. The program phpMyAdmin is a web-based client that can do most of the jobs of both MySQL Admin and MySQL Query Browser. I have come to use it even on my local machine(s) because it is what I have available to use in a shared hosting environment anyway, so I need to learn to use less programs.

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.

root@myhost# /etc/init.d/mysql restart