Installing MySQL on Solaris (for Caucus)
Last revised: 22 July 2003

I. Introduction
Caucus 4.5 adds the capability to record user "events" in a relational database ("RDBMS") such as MySQL.  This document details how to install MySQL and ODBC on Solaris, and how to configure them and Caucus accordingly.

If you already have MySQL installed on your server, go straight to step V.

II. Download and install MySQL
There are several ways to install MySQL, including building from source or installing a binary package.  These instructions detail installing from binary packages, which we believe is simplest.  Adapt these instructions accordingly if you are using a different download method.

  1. MySQL server:  from www.mysql.com/downloads/mysql-4.0.html, skip down to the Solaris section, and click on "Pick a mirror" for the appropriate "standard" distribution for your Solaris box.  Download the MySQL server.  You should get a file with a name like "mysql-standard-4.0.14-sun-solaris2.8-sparc.tar.gz".

  2. ODBC driver:  from www.mysql.com/downloads/api-myodbc-3.51.html, skip down to the Solaris section, and click on "Pick a mirror" for the appropriate distribution for your Solaris box.  Download the ODBC driver.  You should get a file with a name like "MyODBC-3.51.06-sun-solaris2.8-sparc.tar.gz".

  3. Install the server as root.  Gunzip and untar the server kit.  Follow the instructions in the file INSTALL-BINARY.

  4. Install the ODBC driver as root.  Gunzip and untar the ODBC driver kit.  Follow the instructions in the file INSTALL-BINARY.

  5. Start the server daemon.  Make sure it gets started when your box boots.  Most MySQL distributions contain a boot-time file, such as /etc/init.d/mysql.  Otherwise you can adapt your own to run the MySQL "bin/mysqld_safe" script.

III. Secure MySQL
There is a great deal of literature about how to properly secure MySQL depending on your intended use and circumstances.  The MySQL Reference Manual is an excellent place to start. 

In these instructions, we assume that MySQL should only be accessible from the local host (not by other clients over the network), and the absolute minimum of 'root' level access.

  1. Always start mysql for local access only.  As root, edit the mysql startup file (/etc/init.d/mysql or other), and find the line that actually runs mysqld_safe.  Add the "--skip-network" option.  The result should look something like: 
       $bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file --skip-networking &

    Stop and restart mysql: 

       /etc/init.d/mysql stop
   /etc/init.d/mysql start

  2. Limit root and non-essential access.  Change the default (empty) root password.  Delete any non-localhost 'root' users, and any 'blank' users. 
       mysqladmin -u root password new_password

   mysql -u root --password=new_password
   mysql> use mysql;
   mysql> delete from user where host = '%';
   mysql> delete from user where user = '';
   mysql> delete from db where user = '';
   mysql> select user, host from user;
    If you see any 'root' users other than root@localhost, delete them -- e.g. 
       mysql> delete from user where user = 'root' and host='myhost.com'
    Flush the privileges, and you're done. 
       mysql> flush privileges;
   mysql> quit

IV. Install ODBC Driver Manager
We recommend the "platform independent ODBC" package from www.iodbc.org.  Again, we recommend a specific series of instructions, but adapt as appropriate for your server.

  1. Click on "iODBC Driver Manager" and download the full source archive (e.g. libiodbc-3.0.6.tar.gz).

  2. Compile and install it: 
       gunzip  libiodbc-3.0.6.tar.gz
   tar xvf libiodbc-3.0.6.tar
   cd      libiodbc-3.0.6
   ./configure
   make
   make install

  3. Set up the odbc.ini file.  Copy the MySQL odbc.ini file from the MyODBC driver kit to /usr/local/etc.  (If you already have a /usr/local/etc/odbc.ini file, add the MySQL file contents to your file.)

  4. Define the environment variables for the ODBC driver manager (either system-wide, or just for the unix 'root' and 'caucus' userids): 
       export ODBCINI=/usr/local/etc/odbc.ini
   export ODBCSYSINI=/usr/local/etc

  5. Test it.  You should be able to run /usr/local/bin/odbctest, and then respond to the prompt with
      DSN=myodbc3;USER=root;PASSWORD=new_password
    and perform simple SQL queries.  If you run into problems, consult the text files in the directory where you got the MySQL odbc.ini file for further information.

V. Create the Caucus database
Caucus can be installed multiple times on a single server, in which case each installation gets its own unix userid.  Therefore we need distinct MySQL databases for each such installation.  The convention is to call the database "caucus_xyz", where "xyz" is the unix userid where Caucus is installed.  We also recommend creating a userid "in" MySQL that matches the unix userid.

Thus, for the simplest case of a single installation in unix userid "xyz", do the following as the 'root' MySQL user: 

   mysql> create database caucus_xyz;
   mysql> grant all on caucus_xyz.* to xyz@localhost identified by 'somepassword';
   mysql> quit

VI. Configure Caucus
At last!  Logged in as the unix caucus userid, edit the file SWEB/swebd.conf.  Look for the ODBC section, and define the ConnectString, ODBCINI, and ODBCSYSINI parameters appropriately.  Continuing our example, they would look like: 

   ConnectString DSN=myodbc3;DB=caucus_xyz;UID=xyz;PWD=somepassword
   ODBCINI       /usr/local/etc/odbc.ini
   ODBCSYSINI    /usr/local/etc
Ensure that swebd.conf is not world readable(!).

In order to use the event-logging feature of Caucus with MySQL, you must have a customer number with a "900" value.  Login to Caucus on your browser with a manager userid, and go to the "manage" page.  You should see something like "your customer number is 05901LX.  The middle digit must be "9" to use the event-logging feature.  If this is not the case, please contact us at support@caucuscare.com.

Login to Caucus and logout; then use the command line 'mysql' tool to examine the contents of the events table.  You should see (at the minimum) a 'login' event and a 'logout' event; the latter records the duration of your session in seconds.  For more details, see Caucus Statistics and the Events table.