Installing MySQL on Linux (for Caucus) Last revised: 6 April 2005
I. IntroductionThis 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.
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.
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.
- 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.)
- 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.
- 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
- 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.
- 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".
- 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".
- The final option is to install from source, described in detail below.
- Download the gzipped tarball from www.unixodbc.org, it will be called (for example) unixODBC-2.2.10.tar.gz.
- Unpack and untar it.gunzip unixODBC-2.2.10.tar.gz tar xvf unixODBC-2.2.10.tar
- As 'root', compile and install it.cd unixODBC-2.2.10 ./configure --prefix=/usr/local --enable-gui=no --enable-static=yes make make install
- 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
- 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:
V. Create the Caucus database
- 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
- Run the isql program (installed as part of unixODBC):export ODBCINI=/usr/local/etc/odbc.ini export ODBCSYSINI=/usr/local/etc isql myodbc3 userid passwordIf you get connected to the 'test' database, the test succeeded.
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/etcEnsure 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 firstname.lastname@example.org.
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.