Installing MySQL on Linux (for Caucus)
Last revised: 6 April 2005

I. Introduction
Some of the extended capabilities of Caucus 4.x need MySQL and ODBC.  For example, Caucus 4.5 adds the capability to record user "events" in a relational database ("RDBMS") such as MySQL.  Caucus 5.0 will require MySQL.

This document details how to install MySQL and ODBC on Linux, and how to configure them and Caucus accordingly.  If you already have MySQL and ODBC installed on your server, go straight to step V.

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

  1. From www.mysql.com/downloads/mysql-4.1.html, download the Linux RPM files listed below.  (The precise version numbers may vary; this is just an example.)
      Category sample file
      Server 4.1 MySQL-server-4.1.11-0.i386.rpm
      Libraries and header files 4.1   MySQL-devel-4.1.11-0.i386.rpm
      Dynamic client libraries 4.1 MySQL-shared-4.1.11-0.i386.rpm
      Client programs 4.1 MySQL-client-4.1.11-0.i386.rpm
      MySQL ODBC Connector 3.51   MyODBC-3.51.06-1.i386.rpm

    (Get the last file from www.mysql.com/downloads/api-myodbc-3.51.html.)

  2. As root, install the rpm's: 
       rpm -i MySQL-server-4.1.11-0.i386.rpm
   rpm -i MySQL-client-4.1.11-0.i386.rpm
   rpm -i MySQL-devel-4.1.11-0.i386.rpm
   rpm -i MySQL-shared-4.1.11-0.i386.rpm
   rpm -i MyODBC-3.51.06-1.i386.rpm --nodeps --noscripts
    (If you already have an earlier version of MySQL installed, you should use "rpm -U" instead of "rpm -i", and you may need to add "--nodeps" to several of the rpm commands.  But don't worry about it unless "rpm -i" complains!)

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 /etc/init.d/mysql, 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
ODBC is the "connector" between Caucus and MySQL.  We recommend the "unixODBC" package from www.unixodbc.org. 

  1. You may already have unixODBC installed.  Look for libodbcinst.so, typically in either /usr/lib or /usr/local/lib.  If you have an RPM-based system such as RedHat, try "rpm -qa unixodbc".
  2. If unixODBC is not installed, you may be able to install it from your update service.  Again, for RedHat, as 'root' try "up2date -u unixODBC".
  3. The final option is to install from source, described in detail below.
    1. Download the gzipped tarball from www.unixodbc.org, it will be called (for example) unixODBC-2.2.10.tar.gz.
    2. Unpack and untar it. 
         gunzip  unixODBC-2.2.10.tar.gz
   tar xvf unixODBC-2.2.10.tar
    3. As 'root', compile and install it. 
         cd  unixODBC-2.2.10
   ./configure --prefix=/usr/local --enable-gui=no --enable-static=yes
   make
   make install

    4. Set up the odbc.ini file.  This is basically the configuration file for the ODBC connector (driver manager).  It should typically be placed (by you) in either /etc or /usr/local/etc (the latter is usually preferred).

      You can (usually) get it from the MySQL distribution (often in /usr/share/doc/packages/MyODBC/odbc.ini). (If you already have a /usr/local/etc/odbc.ini file, add the MySQL file contents to your file.)

      If you cannot find it, create one with the following text: 

         [ODBC Data Sources]
   myodbc3     = MySQL ODBC 3.51 Driver DSN
                                                                                   
   [myodbc3]
   Driver       = /usr/lib/libmyodbc3.so
   Description  = MySQL ODBC 3.51 Driver DSN
   SERVER       = localhost
   PORT         =
   USER         = root
   Password     =
   Database     = test
   OPTION       = 3
   SOCKET       =
   Trace        = off

    5. 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

    To confirm that your MySQL -> MyODBC -> unixODBC configuration is working correctly, try the following (optional) test:

    1. Use the mysql client to create a userid (with password) that has access to the 'test' database (which was set up by your MySQL install).  E.g. 
         mysql -u root -p
   (enter mysql root password)
   grant all on test.* to userid@localhost identified by 'password'
   quit

   mysql -u userid -p
   (enter userid's password)
   use test;
   show tables;
   quit
    2. Run the isql program (installed as part of unixODBC): 
         export ODBCINI=/usr/local/etc/odbc.ini
   export ODBCSYSINI=/usr/local/etc
   isql myodbc3 userid password
      If you get connected to the 'test' database, the test succeeded.

    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 distinct MySQL userid 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 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.