Binary Installation of ExtSQL for Linux/UNIX (MySQL compatible) 


                           Introduction

Obviously, the information stored in a database can well be considered
the 'company jewels' and you don't want it broken by some untested
piece of software! 

This document describes how to do a binary install of Extended usage
statistics for SQL (ExtSQL) on a system running Linux/Unix.  The
ExtSQL subsystem is just a change to the MySQL server daemon (mysqld)
itself.  It was designed to be easy to install and also easy to
remove.  To use this procedure you need TWO things:

a) A working version of MySQL that you installed starting with a BINARY 
distribution of MySQL from the MySQL web site, http://www.mysql.com/.

b) A download of the appropriate ExtSQL binary for your MySQL version
from our web site, http://www.ExtSQL.com/. The download version should
match your MySQL version EXACTLY.

Besides the actual binary, the download package will also include a
README (these instructions) and the do_conf file. 

The 'do_conf-' file contains the exact configure commands used for the
binary build we used for testing and if you selected the 'Standard'
configuration, it should match what is being used on your system.  You
should check this carefully against your running configuration.  Any
special notes we have concerning build configuration would appear as
comments in this file.

These procedures are intended for installing the binary version of ExtSQL.
if you are installing ExtSQL from the source files you need different
installation instructions, check http://www.ExtSQL.com/.

                         Release Notes (none)


                    Controlling the MySQL Service

You must be able to stop/start the MySQL service prior to installing
ExtSQL. On many servers MySQL starts automatically at boot time. Make
sure you can start/stop it manually. For 'most' linux/Unix packages
MySQL should be controllable as a standard service,e.g.  on linux(as
root):

service mysql stop   ## should shut the server down.  
service mysql start  ## should start the server.

You should then be able to confirm it is up/down by attempting a
connection and having it succeed/fail.


                      Installing the ExtSQL server

Please, let me repeat again, make sure you start from a binary MySQL 
installation that is working correctly. The file locations given in these
instructions are based on the file locations normally used by MySQL in a 
binary installation. File locations can be different when MySQL is installed
from a source distribution. These instructions assume a RHEL4 Linux MySQL 
RPM installation.  

The 'mysql' executable is located at: /usr/libexec/mysqld
The data directories: /var/lib/mysql

The directions below assume those locations.  You would use the values
appropriate for your system.

1. If possible, try your first install of the new mysqld on a
non-production server that mimics your primary installation.


2. Backup all your databases.


3. Make a backup copy of your current mysqld:

   cd /usr/libexec
   cp -a mysqld mysqld.sav


4. ExtSQL prints status information to the MySQL error
file.  In another window you can monitor that file by typing:

   tail -f /var/lib/mysql/your-hostname.err &


5. At this point we are ready to install and activate the new server.
We will use the basic commands available with MySQL to stop and start
the server.  If your installation uses other wrapper programs -- you
should probably use those. Stop your MySQL server:

   service mysql stop

060612 07:58:08 [Note] /usr/libexec/mysqld: Shutdown complete
060612 07:58:08  mysqld ended


6. Copy the new mysqld into place:

   cp -a /downloads/extsql-my-bin-5.0.45-rhel4-x86-1b-1a-1.1b  mysqld


7. Start the new server:

   service mysql start

071121  7:42:47 [Note] ExtSQL build: stats version: 5.0.45-rhel4-x86-1b-1a-1.1b
060612  7:58:38 [Note] ExtSQL disabled. No license key found for binary build, 
                      make sure to define ExtSQL_key in /etc/my.cnf.


8. Take a breather!  The preceding two lines confirm that your server
does contain the ExtSQL subsystem and that it is currently disabled
since no license key was found.  We recommend you run for at least
a day with this configuration and convince yourself things are
operating normally.  You should see NO performance impact on the
server.

If an error occurs during start this indicates a problem with the
download.  If you DO NOT see the message above, then it indicates you
have overwritten the WRONG mysqld on your host. To get back to a known
good configuration all you have to do is restore your version of the
mysqld and restart the server.


9. You are now ready to start recording statistical data.  Depending
on local policies and your preference perform either of the following:

Change /etc/my.cnf and add the following TWO lines. They MUST BE in
the [mysqld] section of the config file:

NOTE - DO NOT use what is printed below for the extsql_key value.
Login to your account at http://www.ExtSQL.com/ and view your licenses
and use the key value displayed there!

extsql_key="95-75-62931-37-144-32" 
extsql_class_list="user, max-100, time-120, units-h,(Com_insert, Com_select, Com_update, Com_delete, Com_replace, Slow_queries)"

NOTE - enter extsql_class_list exactly as above and it should be ONE
physical line in the file.  If you change the /etc/my.cnf be sure to
remove this line if you revert to a standard mysqld.  It will not
understand this additional parameter and will generate an error.

Because of the length of the extsql_class_list it's easy to introduce
an error that can cause the server to not start due to my.cnf syntax.
You can run an easy check of your config file syntax by giving the
command: ./mysqld --help --verbose

If you have a syntax error in your config file, it will be reported.
This command will NOT confirm you have a valid license key or a valid
extsql_class_list -- but your server will restart.

For a more complete listing of currently supported tracking variables
and example configurations go to: http://www.ExtSQL.com/tracking.php


10.  Restart the server. When it restarts you should now see:

060612  8:26:12  mysqld started
060612  8:26:12 [Note] ExtSQL build:  ExtSQL version: extsql-my-bin-5.0.45-rhel4-x86-1b-1a-1.1b
060612  8:26:12 [Note] ExtSQL ACTIVE tracking memory: 435600 bytes for 1 classes

Any error during server initialization or operation will be noted to
the error log and the subsystem will disable itself. 


11.  From the SQL command line you can now monitor your server with commands like:

SHOW STATISTICS   # summary information about ExtSQL and usage

SHOW STATISTICS * FROM user    # information on total user activity

SHOW STATISTICS * FROM user HISTORY  # show historical information

SHOW STATISTICS * FROM user LIKE 'biguser' HISTORY  # just one user

SHOW STATISTICS * FROM user WHERE Com_select > 100 HISTORY  # just heavy users


For ExtSQL 5.0.x: (Supports INFORMATION_SCHEMA. Full SQL syntax available.)

use INFORMATION_SCHEMA;   # make it the default, show available tables
SHOW TABLES LIKE 'EXTSTATS%';
DESCRIBE EXTSTATS_user;   # show column definitions

SELECT user, hours, Com_select, Com_insert FROM EXTSTATS_user; # shows all times

SELECT user, hours, Com_select, Com_insert FROM EXTSTATS_user 
       WHERE hours='0000-00-00';  # summary data only

SELECT user, hours, Questions FROM EXTSTATS_user 
       WHERE Questions > 400 AND user='domenic' ORDER BY hours;



                               Using ExtSQL

After a successful install, check for the most current usage and configuration 
instructions at: http://www.ExtSQL.com/showPage.php?Page=documentation