Linux/UNIX Source Installation of MySQL Extended Statistics 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 MySQL Extended Statistics on a system running Linux/Unix. 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 Extended Statistics patches from our web site, http://www.extendedstatistics.com/download.html . The download version should match your MySQL version EXACTLY. The procedures below will NOT work if you already have a binary distribution or if you using Windows! In that case you need different installation instructions, check http://www.extendedstatistics.com/documentation.html 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.extendedstatistics.com/download.html The MySQL Extended Statistics 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. Building Extended Statistics Please, let me repeat that 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. There is no easy way to back out the patches once applied. 0. 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. NOTE - make SURE 1. Download the source patches for Extended Statistics from http://www.extendedstatistics.com/download.html for your version of the server and put the .gz file in the top of your MySQL distribution. 1a. Type 'make distclean' at the top of your distribution tree. This will remove all prior build products. 2. Give the command: tar -zxvf mysql_patch-4.1.2.2.tar.gz to unpack. You should see something like: patch-4.1.2.2 and POTENTIALLY compat-4.1.3 README.ExtendedStatistics The first two are both patch files. The 'patch-' file contains the bulk of the changes that make up Extended Statistics. The second file starting with 'compat-' MAY be present. 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. 3. Apply the patches. Make sure to apply the 'patch-' file FIRST. patch -V t -p0 -lNu < patch-4.1.2.2 patch -V t -p0 -lNu < compat-4.1.3 (only if compat file is present). You should NOT see any failure messages. 4. Rerun your configure command with whatever options you normally use, e.g. CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors \ -fno-exceptions -fno-rtti" ./configure \ --prefix=/usr/local/mysql --enable-assembler \ --with-mysqld-ldflags=-all-static \ --with-extra-charsets=complex \ --enable-thread-safe-client --enable-local-infile 5. Then type 'make' as usual to build mysqld. It should complete normally and you should see a new mysqld binary in the build directory sql. As a sanity check the size and date of the file compared with the backup copy of your last build. This file should have 'todays' date and be slightly larger in size. Installing the Extended Statistics mysqld The whole point of this procedure is again safety. Instead of doing a 'make install' which normally copies a lot of files to different destinations -- you will install just the new mysqld. We recommend the following steps (below we assume the base directory for MySQL is /usr/local/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 /usr/local/mysql/libexec cp -a mysqld mysqld.sav 4. Extended Statistics prints status information to the MySQL error file. In another window you can monitor that file by typing: tail -f /usr/local/mysql/data/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: /usr/local/mysql/bin/mysqladmin -uroot -p"password" shutdown 060612 07:58:08 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 060612 07:58:08 mysqld ended 6. Copy the new mysqld into place: cp -a /path/to/your/installation/sql/mysqld mysqld 7. Start the new server: /usr/local/mysql/bin/mysqld_safe --user=$mysql_user $other_args & 071121 7:42:47 [Note] Extended statistics build: stats version: 4.1.20-x86-rhel4-4.1-4.1.2.2 060612 7:58:38 [Note] Extended statistics disabled by user, no statistics_class_list defined 8. Take a breather! The preceding two lines confirm that your server was built with the Extended Statistics subsystem and that it is currently disabled since no statistics were requested. We recommend you run for at lease 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 /etc/my.cnf and add the following line: statistics_class_list="user,max-100,time-120,units-m,(Com_insert,Com_select, Com_update,Com_delete,Com_replace,Slow_queries,Bytes_sent,Bytes_received)" NOTE - enter it exactly as above, no extra white space, 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 --statistics_class_list="user,max-100, time-120,units-m,(Com_insert,Com_select,Com_update,Com_delete, Com_replace,Slow_queries,Bytes_sent,Bytes_received)" & 10. When the server restarts you should now see: 060612 8:26:12 mysqld started 060612 8:26:12 [Note] Extended statistics build: stats version: 4.1.20-x86-rhel4-4.1-4.1.2.2 060612 8:26:12 [Note] Extended statistics 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. Using Extended Statistics After a successful install, check for the most current usage and configuration instructions at: http://www.extendedstatistics.com/documentation.html Version: $Id: linux_install_src.txt,v 1.1 2007/11/25 13:56:26 thebook Exp $