Installing a Second MySQL

* I needed to set up several different instances of MySQL on the same Linux hosts--we needed completely separate DBMS for two separate projects.

* The following tricks were gleaned from various Google searches. Through Google, all things are possible. I have attributed helpful sites here and there but not everywhere. http://www.analysisandsolutions.com/code/mybasic.htm
was useful.

* The first DB was installed in /usr/local/mysql in the usual way.

* For the second installation, I first created a new user, "mysql_two". Put the mysql binary under this directory but don't install it yet.

* I then added entries to the /etc/sudoers file for each user who needed access to this second DB. This looks like this:
[shell-prompt> more /etc/sudoers
...
# User privilege specification
root ALL=(ALL) ALL
joeuser ALL=(mysql_two) ALL
moeuser ALL=(mysql_two) ALL

* This allows joeuser and moeuser to run all commands as mysql_two through the sudo command. For example,

[joeuser@gomoejoe> sudo -H -u mysql_two /bin/bash

allows joeuser to login as user mysql_two using the joeuser password. For multiple users, this is convenient since you don't need to share passwords. You can also be more conservative and grant permission to run only specific commands.

* The -H option in sudo sets the $HOME to mysql_two and will source /home/mysql_two/.bashrc, which willbe useful in a minute.

* Now the next problem is that you already have MySQL installed, so you need to specify different data directories, port numbers and so forth for installation #2. All of these are defined in /etc/my.cnf by default (cat this to see).

* To override these /etc/my.cnf settings for mysql_two, I used the $MYSQL_HOME environment variable. See
http://dev.mysql.com/doc/refman/5.0/en/option-files.html. I added this env variable to the /home/mysql_two/.bashrc file:

export MYSQL_HOME=$HOME

* Catting $MYSQL_HOME/my.cnf gives
-----------------------------------------
[mysqld]
datadir=/usr/loocal/mysql_two/data
socket=/usr/local/mysql_two/mysql.sock

[mysql.server]
user=mysql_two
basedir=/usr/local/mysql_two

[safe_mysqld]
err-log=/usr/local/mysql_two/mysqld.log
pid-file=/usr/local/mysql_two/mysqld.pid
port=44070

-----------------------------------------

* Note there is a problem with this file, so keep reading.

* You can now install mysql_two. You need to vary the usual MySQL INSTALL_BINARY instructions a bit. Do the following:

[root@gomoejoe root]# mkdir /usr/local/mysql_two
[root@gomoejoe root]# chown -R mysql_two:mysql_two /usr/local/mysql_two/
[root@gomoejoe root]# su - mysql_two
[mysql_two@gomoejoe mysql_two]$ cd mysql
[mysql_two@gomoejoe mysql]$ ./scripts/mysql_install_db

Note these directories correspond to your $HOME/my.cnf settings. I put this in /usr/local so that I could run multiple mysqls on several machines with NFS mounted files. You can simplify this by just using /home/mysql_two if you are not using NFS.

* Now verify your installation with "$HOME/mysql/bin/mysqladmin -version" from the mysql_two account. You will get the following error:

[mysql_two@gomoejoe mysql]$ ./bin/mysqladmin version
./bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!

* http://www.tech-recipes.com/mysql_tips762.html
had the answer. With Google and a firm place to type, I can move the Internet. I added the [client] line to $HOME/my.cnf, so it now looks like this:

[mysql_two@gridfarm005 mysql]$ more /home/mysql_two/my.cnf
[mysqld]
datadir=/usr/local/mysql_two/data
socket=/usr/local/mysql_two/mysql.sock

[mysql.server]
user=mysql_two
basedir=/usr/local/mysql_two

[client]
socket=/usr/local/mysql_two/mysql.sock
port=44070

[safe_mysqld]
err-log=/usr/local/mysql_two/mysqld.log
pid-file=/usr/local/mysql_two/mysqld.pid
port=44070


* mysqladmin -version now works.