Binary Installation of ExtSQL for Windows (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 Microsoft Windows.  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.

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)


                       Stopping the MySQL Service

You must stop the MySQL service prior to installing ExtSQL. To view installed 
services: click on Start > Control Panel > Administrative Tools > Services.

Look for MySQL in the list of services and left click to select it. If the 
service is running it will say "Started" in the Status column. Once you have 
selected MySQL you should see links in the upper left corner of the Services
window to Stop, Pause, or Restart the MySQL service. Click Stop. Once the 
service has stopped the Status column will be blank.

This is also where you can find out which version of mysqld is running. Right
click on the MySQL service and select Properties. On the General tab look at 
the box labeled "Path to executable." The first part of the path should look 
similar to this: "C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt"
MySQL can have two mysqld executables in its bin directory: mysqld.exe
and mysqld-nt.exe. In the example above, mysqld-nt.exe is the one being used.

Also, in that same box labelled "Path to executable" you should also see
something like '--defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini"
The location of the 'my.ini' file, which configures MySQL at startup will be
important when we activate ExtSQL tracking below.


                      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 also assume MySQL is installed 
as a Windows service.

We recommend the following steps below. We assume the base install directory 
for MySQL is "C:\Program Files\MySQL\MySQL Server X.X." (X.X would be the MySQL
version number such as "4.1"):

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:
   
   Go to the bin directory under MySQL.
   Copy and paste the mysqld.
   Rename the copy to mysqld.sav or something similar.


4. ExtSQL prints status information to the MySQL error
file. It is located the data directory under MySQL.


5. At this point we are ready to install and activate the new server.
   Make sure you have stopped the MySQL server before proceeding.  


6. Copy the new mysqld to the bin directory under MySQL.


7. Make sure you made the mysqld copy in step 3 and then delete the original mysqld.


8. Rename the new mysqld to the name of the mysqld you saw in the path to
the executable you saw under Services. 


9. Start the new server by selecting MySQL under Services and then clicking Start 
in the upper left corner of the Services window. The ExtSQL subsystem is currently 
disabled. 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. To make sure you are running ExtSQL give the following
SQL command:  "SHOW STATISTICS;"

A non-ExtSQL binary will give you a syntax error.  If you are running
ExtSQL the response should be: "ExtSQL: disabled"

If an error occurs during start this indicates a problem with the build.  To 
get back to a known good configuration all you have to do is restore your original
version of the mysqld and restart the server.


10. You are now ready to start recording statistical data. Add the
following lines to the server section "[mysqld]" of the my.ini file in
the MySQL Server directory:

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

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


11. Restart the MySQL server.

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

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