Source 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 safely build & install Extended usage statistics for SQL (ExtSQL) on a system running 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 compiled and installed starting with a SOURCE distribution of MySQL from the MySQL web site, http://www.mysql.com/. b) A source download of the ExtSQL patches from our web site, http://www.extsql.com/. The download version should match your MySQL version EXACTLY. The procedures below will NOT work if you already have a binary distribution! In that case you need different installation instructions, check http://www.extsql.com/. If you don't want to build MySQL there are binary versions available for download for older versions of MySQL and different combinations of Operating System and machine architecture. Check http://www.extsql.com/ Building ExtSQL Please, let me repeat again, make sure you start from a MySQL source distribution you have built and installed yourself and is operating normally! If you do not want to use the source, the download web site has compiled binaries for various OS/hardware platforms available for evaluation and licensing. NOTE: There are significant differences in the build process for MySQL 4.x and MySQL 5.x releases -- be sure to note difference below! In our test environment we use Microsoft Visual Studio for development. Visual Studio 2003 is used for all 32 bit builds and Visual Studio 2008 was used for 64 bit builds. Again, the scope of our instructions is limited to what is necessary to add the ExtSQL extensions to an existing Windows build. Setting up the Windows build environment for MySQL can be a little confusing, please consult the MySQL web site for help on that http://www.mysql.com/. 0. There is no easy way to back out the patches to your source tree once they are applied. Make a backup copy of your current source distribution build tree. If you do have a problem during the build process, you will not lose your existing working version. 1. Download the source patches for ExtSQL from http://www.extsql.com/ for your version of the server and unpack the .zip file in the top of your existing MySQL distribution. You should see something like: ExtSQL 5.0.x ----------- patch-5.0.3.0b compat-5.0.5a README.source_extsql ExtSQL 4.1.x ----------- patch-4.1.3.0 compat-4.7 README.source_extsql lex_hash.h sql_yacc.h sql_yacc.yy sql_yacc.cpp In both cases the first two are both patch files. The 'patch-' file contains the bulk of the changes that make up ExtSQL. The second file starting with 'compat-' has more version specific changes. It contains unique fixes for that specific version of MySQL (normally limited to Makefile changes). As always, please view the README before doing anything! It contains the release notes and a description of the most current procedures. 2a. ExtSQL 4.1.x ONLY - Copy the source files (*.h,*.yy,*.cpp) specifed above to the sql subdirectory, they are products of running lex/yacc on the grammar file. The sql_yacc.yy will not actually be used in a 4.1.x windows build (since yacc/bison is not present), but we supply it for reference purposes. 3. Apply the patches. Make sure to apply the 'compat-' file FIRST. NOTE: Obviously, the 'patch' command is not a native windows utility. We recommend copying the source tree to a Linux/Unix system and performing the patch operation there. patch -V t -p0 -lNu < compat-5.0.5a patch -V t -p0 -lNu < patch-5.0.3.0b You should NOT see any failure messages. EXCEPT ExtSQL 4.1.x ONLY - you will see a previously applied patch error on sql_yacc.yy. The download version already has the patch applied and the message can be ignored. The file is not used in the build in any case. 4. To see the correct ExtSQL version info, edit the extsql.cpp file and look for the string: "\nExtSQL version: __STATS_VERSION__\n" Replace __STATS_VERSION__ with the info you would like displayed. 5. ExtSQL 5.0.x ONLY - Run the configure command. You may use whatever options you desire. It should not affect your ability to build ExtSql. But, you must edit the sql/extsql.h file and uncomment the following line so that EXTSQL_50 is defined: // #define EXTSQL_50 1 -- external define set during build during configure. After that run: win\build-vs71.bat 6. Build Clean the solution FIRST in ALL cases. This will remove all intermediate build products. Make sure to use the 'Release' option when configuring your build in Visual Studio as mentioned below. For ExtSQL 4.1.x: File -> Open Solution -> get to top directory and you should see mysql.sln (has graphic). Then Build -> Config -> choose 'Release' from list. Then in right pane (Solution Explorer), click on '+' in front of mysqld project, it should expand to show source file. Click right, Add -> Existing Item (dialog will popup, select extsql.cpp Then Build -> Build Solution For ExtSQL 5.0.x: File -> Open Solution -> get to top directory and you should see mysql.sln (has graphic). Then Build -> Config -> choose 'Release' from list. Then in right pane (Solution Explorer), click on '+' in front of mysqld project, it should expand to show source file. Click right, Add -> Existing Item (dialog will popup, select extsql.cc Then Build -> Build Solution. Installing the ExtSQL server The whole point of this procedure is again safety. You will manually install just the new mysqld. We recommend the following steps (below we assume the base install directory for MySQL is C:\mysql): 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 C:\mysql\bin copy mysqld.exe mysqld.exe.sav 4. ExtSQL prints status information to the MySQL error file. You should locate that file for your installation environment. 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 is installed as a Windows Service you can use the services admin screen to stop it. Or you can stop your MySQL server with something like: C:\mysql\bin\mysqladmin -uroot -p"password" shutdown 6. Copy the new mysqld into place (depending on your build configuration the name of the executable mysqld may differ): copy -a \path\to\your\source-installation\sql\release\mysqld.exe C:\mysql\bin\mysqld.exe 7. Start the new server manually or through services and confirm operation: /usr/local/mysql/bin/mysqld_safe --user=$mysql_user $other_args & 071121 7:42:47 [Note] ExtSQL build: stats version: 5.0.45-winxp-x86-2-5a-3.0b 060612 7:58:38 [Note] ExtSQL disabled by user, no extsql_class_list defined 8. Take a breather! The preceding two lines confirm that your server was built with the ExtSQL subsystem and that it is currently disabled since no statistics were requested. 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 build. 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 C:\windows\my.ini (again, use the appropriate path to your ini file) and add the following line. It MUST BE in the [mysqld] section of the config file: extsql_class_list="user, max-100, time-120, units-m,(Com_insert, Com_select, Com_update, Com_delete, Com_replace, Slow_queries)" NOTE - enter it 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. OR You can restart the server and add it as a command line option (again as ONE physical line): mysqld_safe --user=mysql --extsql_class_list="user,max-100, time-120,units-m,(Com_insert,Com_select,Com_update,Com_delete,Com_replace,Slow_queries)" & 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. When the server 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-winxp-x86-2-5a-3.0b 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. On windows you may see an error if ODBC is the default user (the ExtSQL extensions recognize only a user named 'root' by default). To correct this add another line to the MySQL config file: extsql_users="ODBC" (you can add more usernames, comma separated) 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/