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