MySQL Notes

Running as non-root user

When running MySQL as a non-root user, it is important to both configure and run the database as the unix user it will be run under.

Users and Passwords

There are unix users which the MySQL database process runs as. For example MySQL usually runs as the non-unix-root user mysql.

Each database has sql users which controls access to the database instance.

These are two different namespaces -- there is both a unix root user and a per-mysql instance root user. The normal recommendation is to use people's unix usernames in the database so that collisions are taken care of and people don't have to remember multiple usernames. However DO NOT use your unix passwords in the database. The password storage and encryption is not as strong as our Kerberos/AFS installation. If you re-use your unix password in the DB your risk having it compromised and loosing the real security the CS environment offers.

AFS

MySQL will not run with the modifiable state in AFS because it needs to lock files. You can install MySQL into AFS, but the data needs to be on a local disk. To do this you can configure mysql something like ...

./configure --prefix=/somewhere/in/afs/mysql-4.1.10 \
	    --sharedstatedir=/scratch/UNIX-USER/mysql-4.1.10/com \
	    --localstatedir=/scratch/UNIX-USER/mysql-4.1.10/var
% mkdir -p /scratch/UNIX-USER/mysql-4.1.10/{var,com}

Another note about the Local AFS environment... It is not practicable to have a long-running server type service in the AFS environment. The reason for this is that processes on a system need AFS Tokens to access data in AFS. When you logoff, the tokens for accessing AFS are destroyed as a security measure. While it is possible to work around this, it also poses a security risk -- anyone who compromises the DB server will be able to access anything you can in AFS. And, ultimately, you will still have the same problem as the AFS tokens expire.

The better solution for this is to either install MySQL on the local disk of a CS computer. This means that the server needs no authentication, the data is local, and it just works. To do this, the configuration would be something like:

./configure --prefix=/scratch/UNIX-USER/mysql-4.1.10

Another alternative, as in the example above, is to make a mixed-mode installation. Place the db software installation in AFS, and the db data on a local disk. If you do that you'll need to put the installation somewhere which is public-read ... such as ~/public/ so that the running mysql server can still access its own executables once you logout (and the AFS tokens are destroyed). If you have MySQL databases on several computers this could be a timesaver, as you don't need to install MySQL software on each computer.

My SQL Bugs ...

Either bugs, or bad decisions, or whatever. The MySQL documentation says that it will look for a configuration file in the PREFIX/etc/ directory. Then it will look in the PREFIX/var/ directory, and then in the user's home directory.

Instead of checking in PREFIX/etc/, it always checks for a config file in /etc/. This is problematic:

1. If the system is also running mysql ... how do you use different setings for your own db?
2. The system will configure itself properly without an /etc/my.cnf, but will then fail if you try to run the server.
3. If you want to split code/data between two locations (such as AFS and alocal disk) it just won't work without some hackery.

If you want to use the "mixed mode" configuration there is an easy workaround for this problem. You need to create a symbolic link for the com and var directories which exist on the local disks into AFS:

% cd /path/into/afs/mysql-4.1.10
% ln -s /scratch/UNIX-USER/mysql-4.1.10/{var,com} ./

You should do this before creating the database.

Installation & Testing

After configuring and installing MySQL from the source tree you will need to initialize the database. If you are running as the UNIX root you can specify an arbitrary non-root user to run the database engine as. If you are running as an ordinary user you will have to use your own username so the database can access the files. I've marked the name of the user as UNIX-USER below

% cd /root/of/mysql-4.1.10
% ./bin/mysql_install_db --user=UNIX-USER

To continue further you will need to start the database server running:

% ./bin/mysqld_safe --user=UNIX-USER

Now that the server is up you will need to set the password for the SQL root user. To do this you can use the mysqladmin admin tool:

% ./bin/mysqladmin -u root password 'PASSWORD'

Lastly, you can test the MySQL instance to make sure it is working correctly and talking with clients:

% ./bin/mysql_client_test -u root -p
Password: 

This will run, produce a lot of output, and eventually complete with the output of

!!! SUCCESS !!!

To shut the system down after tests are complete use the admin tool:

% ./bin/mysqladmin -u root -p shutdown
Password: 

Brief notes on User admin for MySQL

MySQL users are identified and verified by a triumvirate of Host, User[name], and Password. Corresponding command-line options exist in most of the tools to allow specifiying how you want to be identified, or which instance you want to connect as.

-h user

-- what sql host to use

-u user

-- what sql user to use

-p

-- prompt for password instead of failing authentication

I mention this because of property of the MySQL instance creation is that it creates mulitiple sql root accounts, one for local users and another for network users. If you don't secure BOTH root accounts the DB will not be secure.

You will need to set the password for the root users of the MySQL database engine instance. Note that this is a bit insecure since other users of the system may be able to momentarily observe the new password on the command line.

% ./bin/mysqladmin -u root password 'NEW PASS WORD'

Once an initial password is set you can also set a password from sql:

% ./bin/mysql -u root -p
Password: NEW PASS WORD
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');

To set a initial password for a user

% ./bin/mysqladmin -u SQL-USER password 'NEW PASS WORD'

To set a new password for a user, you'll need to provide password identification for that user so you can then set a new password:

% ./bin/mysqladmin -u SQL-USER -p password 'NEW PASS WORD'
Password: ....

To list users and if they have a password...

% ./bin/mysql -u root -p
Password:
mysql> SELECT Host, User, Password FROM mysql.user;
+-------------------+------+-------------------------------------------+
| Host              | User | Password                                  |
+-------------------+------+-------------------------------------------+
| localhost         | root | *CFA9B6705C7FB82E4CBDE4E1189F937A08227785 |
| growl.cs.wisc.edu | root |                                           |
| growl.cs.wisc.edu |      |                                           |
| localhost         |      |                                           |
+-------------------+------+-------------------------------------------+
4 rows in set (0.00 sec)

To set a password for an arbitrary user .. such as the 2nd root instance on machine to the MySQL Installation is really secure ...

% ./bin/mysql -u root -p
Password:
mysql> SET PASSWORD FOR 'root'@'growl.cs.wisc.edu' = PASSWORD('newpwd');
Query OK, 0 rows affected (0.02 sec)

% ./bin/mysql -u root -p
Password:
mysql> SELECT Host, User, Password FROM mysql.user;
+-------------------+------+-------------------------------------------+
| Host              | User | Password                                  |
+-------------------+------+-------------------------------------------+
| localhost         | root | *CFA9B6705C7FB82E4CBDE4E1189F937A08227785 |
| growl.cs.wisc.edu | root | *CFA9B6705C7FB82E4CBDE4E1189F937A08227785 |
| growl.cs.wisc.edu |      |                                           |
| localhost         |      |                                           |
+-------------------+------+-------------------------------------------+
4 rows in set (0.00 sec)

% ./bin/mysql -u SQL-USER -p
Password:
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
    -> WHERE User = '';
mysql> FLUSH PRIVILEGES;

My SQL Documentation

MySQL has some great documentation. Unfortunately some of their HTML is marked up so bad as to be almost unreadable.

• Adding Users
• Connecting to the Server
• Client and Utility Programs
• Database Administration
• Default Privileges
• Unix Post-Installation Procedures