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