Go to the first, previous, next, last section, table of contents.

2 MySQL Installation

This chapter describes how to obtain and install MySQL:

2.1 Quick Standard Installation of MySQL

2.1.1 Installing MySQL on Linux

The recommended way to install MySQL on Linux is by using an RPM file. The MySQL RPMs are currently being built on a RedHat Version 6.2 system but should work on other versions of Linux that support rpm and use glibc.

If you have problems with an RPM file, for example, if you receive the error ``Sorry, the host 'xxxx' could not be looked up'', see section 2.6.1.1 Linux Notes for Binary Distributions.

The RPM files you may want to use are:

To see all files in an RPM package, run: 

shell> rpm -qpl MySQL-VERSION.i386.rpm

To perform a standard minimal installation, run: 

shell> rpm -i MySQL-VERSION.i386.rpm MySQL-client-VERSION.i386.rpm

To install just the client package, run: 

shell> rpm -i MySQL-client-VERSION.i386.rpm

The RPM places data in `/var/lib/mysql'. The RPM also creates the appropriate entries in `/etc/rc.d/' to start the server automatically at boot time. (This means that if you have performed a previous installation, you may want to make a copy of your previously installed MySQL startup file if you made any changes to it, so you don't lose your changes.)

After installing the RPM file(s), the mysqld daemon should be running and you should now be able to start using MySQL. See section 2.4 Post-installation Setup and Testing.

If something goes wrong, you can find more information in the binary installation chapter. See section M.1 Installing a MySQL Binary Distribution.

2.1.2 Installing MySQL on Windows

The following instructions apply to precompiled binary distributions. If you download a source distribution, you will have to compile and install it yourself.

If you don't have a copy of the MySQL distribution, you should first download one from http://www.mysql.com/downloads/mysql-3.23.html.

If you plan to connect to MySQL from some other program, you will probably also need the MyODBC driver. You can find this at the MyODBC download page (http://www.mysql.com/downloads/api-myodbc.html).

To install either distribution, unzip it in some empty directory and run the Setup.exe program.

By default, MySQL-Windows is configured to be installed in `C:\mysql'. If you want to install MySQL elsewhere, install it in `C:\mysql' first, then move the installation to where you want it. If you do move MySQL, you must indicate where everything is located by supplying a --basedir option when you start the server. For example, if you have moved the MySQL distribution to `D:\programs\mysql', you must start mysqld like this: 

C:\> D:\programs\mysql\bin\mysqld --basedir D:\programs\mysql

Use mysqld --help to display all the options that mysqld understands!

With all newer MySQL versions, you can also create a `C:\my.cnf' file that holds any default options for the MySQL server. Copy the file `\mysql\my-xxxxx.cnf' to `C:\my.cnf' and edit it to suit your setup. Note that you should specify all paths with `/' instead of `\'. If you use `\', you need to specify it twice, because `\' is the escape character in MySQL. See section 4.1.2 my.cnf Option Files.

Starting with MySQL 3.23.38, the Windows distribution includes both the normal and the MySQL-Max binaries. The main benefit of using the normal mysqld.exe binary is that it's a little faster and uses less resources.

Here is a list of the different MySQL servers you can use:

mysqld Compiled with full debugging and automatic memory allocation checking, symbolic links, BDB and InnoDB tables.
mysqld-opt Optimized binary with no support for transactional tables.
mysqld-nt Optimized binary for NT with support for named pipes. You can run this version on Win98, but in this case no named pipes are created and you must have TCP/IP installed.
mysqld-max Optimized binary with support for symbolic links, BDB and InnoDB tables.
mysqld-max-nt Like mysqld-max, but compiled with support for named pipes.

All of the above binaries are optimized for the Pentium Pro processor but should work on any Intel processor >= i386.

NOTE: If you want to use InnoDB tables, there are certain startup options that must be specified in your `my.ini' file! See section 7.6.2 InnoDB startup options.

2.2 General Installation Issues

2.2.1 How to Get MySQL

Check the MySQL home page for information about the current version and for downloading instructions.

Our main download mirror is located at:

http://mirrors.sunsite.dk/mysql/

If you are interested in becoming a MySQL mirror site, you may anonymously rsync with: rsync://sunsite.dk/ftp/mirrors/mysql/. Please send e-mail to webmaster@mysql.com notifying us of your mirror to be added to the list below.

If you have problems downloading from our main site, try using one of the mirrors listed below.

Please report bad or out-of-date mirrors to webmaster@mysql.com.

Europe:

North America:

South America:

Asia:

Africa:

2.2.2 Operating Systems Supported by MySQL

We use GNU Autoconf, so it is possible to port MySQL to all modern systems with working Posix threads and a C++ compiler. (To compile only the client code, a C++ compiler is required but not threads.) We use and develop the software ourselves primarily on Sun Solaris (Versions 2.5 - 2.7) and SuSE Linux Version 7.x.

Note that for many operating systems, the native thread support works only in the latest versions. MySQL has been reported to compile successfully on the following operating system/thread package combinations:

Note that not all platforms are suited equally well for running MySQL. How well a certain platform is suited for a high-load mission critical MySQL server is determined by the following factors:

Based on the above criteria, the best platforms for running MySQL at this point are x86 with SuSE Linux 7.1, 2.4 kernel and ReiserFS (or any similar Linux distribution) and Sparc with Solaris 2.7 or 2.8. FreeBSD comes third, but we really hope it will join the top club once the thread library is improved. We also hope that at some point we will be able to include all other platforms on which MySQL compiles, runs ok, but not quite with the same level of stability and performance, into the top category. This will require some effort on our part in cooperation with the developers of the OS/library components MySQL depends upon. If you are interested in making one of those components better, are in a position to influence their development, and need more detailed instructions on what MySQL needs to run better, send an e-mail to internals@lists.mysql.com.

Please note that the comparison above is not to say that one OS is better or worse than the other in general. We are talking about choosing a particular OS for a dedicated purpose - running MySQL, and compare platforms in that regard only. With this in mind, the result of this comparison would be different if we included more issues into it. And in some cases, the reason one OS is better than the other could simply be that we have put forth more effort into testing on and optimizing for that particular platform. We are just stating our observations to help you make a decision on which platform to use MySQL on in your setup.

2.2.3 Which MySQL Version to Use

The first decision to make is whether you want to use the latest development release or the last stable release:

The second decision to make is whether you want to use a source distribution or a binary distribution. In most cases you should probably use a binary distribution, if one exists for your platform, as this generally will be easier to install than a source distribution.

In the following cases you probably will be better off with a source installation:

The MySQL naming scheme uses release numbers that consist of three numbers and a suffix. For example, a release name like mysql-3.21.17-beta is interpreted like this:

All versions of MySQL are run through our standard tests and benchmarks to ensure that they are relatively safe to use. Because the standard tests are extended over time to check for all previously found bugs, the test suite keeps getting better.

Note that all releases have been tested at least with:

An internal test suite
This is part of a production system for a customer. It has many tables with hundreds of megabytes of data.
The MySQL benchmark suite
This runs a range of common queries. It is also a test to see whether the latest batch of optimizations actually made the code faster. See section 5.1.4 The MySQL Benchmark Suite.
The crash-me test
This tries to determine what features the database supports and what its capabilities and limitations are. See section 5.1.4 The MySQL Benchmark Suite.

Another test is that we use the newest MySQL version in our internal production environment, on at least one machine. We have more than 100 gigabytes of data to work with.

2.2.4 Installation Layouts

This section describes the default layout of the directories created by installing binary and source distributions.

A binary distribution is installed by unpacking it at the installation location you choose (typically `/usr/local/mysql') and creates the following directories in that location:

Directory Contents of directory
`bin' Client programs and the mysqld server
`data' Log files, databases
`include' Include (header) files
`lib' Libraries
`scripts' mysql_install_db
`share/mysql' Error message files
`sql-bench' Benchmarks

A source distribution is installed after you configure and compile it. By default, the installation step installs files under `/usr/local', in the following subdirectories:

Directory Contents of directory
`bin' Client programs and scripts
`include/mysql' Include (header) files
`info' Documentation in Info format
`lib/mysql' Libraries
`libexec' The mysqld server
`share/mysql' Error message files
`sql-bench' Benchmarks and crash-me test
`var' Databases and log files

Within an installation directory, the layout of a source installation differs from that of a binary installation in the following ways:

You can create your own binary installation from a compiled source distribution by executing the script `scripts/make_binary_distribution'.

2.2.5 How and When Updates Are Released

MySQL is evolving quite rapidly here at MySQL AB and we want to share this with other MySQL users. We try to make a release when we have very useful features that others seem to have a need for.

We also try to help out users who request features that are easy to implement. We take note of what our licensed users want to have, and we especially take note of what our extended e-mail supported customers want and try to help them out.

No one has to download a new release. The News section will tell you if the new release has something you really want. See section F MySQL change history.

We use the following policy when updating MySQL:

The current stable release is Version 3.23; We have already moved active development to Version 4.0. Bugs will still be fixed in the stable version. We don't believe in a complete freeze, as this also leaves out bug fixes and things that ``must be done.'' ``Somewhat frozen'' means that we may add small things that ``almost surely will not affect anything that's already working.''

2.2.6 MySQL Binaries Compiled by MySQL AB

As a service, we at MySQL AB provide a set of binary distributions of MySQL that are compiled at our site or at sites where customers kindly have given us access to their machines.

These distributions are generated with scripts/make_binary_distribution and are configured with the following compilers and options:

SunOS 4.1.4 2 sun4c with gcc 2.7.2.1
CC=gcc CXX=gcc CXXFLAGS="-O3 -felide-constructors" ./configure --prefix=/usr/local/mysql --disable-shared --with-extra-charsets=complex --enable-assembler
SunOS 5.5.1 (and above) sun4u with egcs 1.0.3a or 2.90.27 or gcc 2.95.2 and newer
CC=gcc CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-low-memory --with-extra-charsets=complex --enable-assembler
SunOS 5.6 i86pc with gcc 2.8.1
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory --with-extra-charsets=complex
Linux 2.0.33 i386 with pgcc 2.90.29 (egcs 1.0.3a)
CFLAGS="-O3 -mpentium -mstack-align-double" CXX=gcc CXXFLAGS="-O3 -mpentium -mstack-align-double -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --with-extra-charsets=complex
Linux 2.2.x with x686 with gcc 2.95.2
CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared --with-extra-charset=complex
SCO 3.2v5.0.4 i386 with gcc 2.7-95q4
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex
AIX 2 4 with gcc 2.7.2.2
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex
OSF1 V4.0 564 alpha with gcc 2.8.1
CC=gcc CFLAGS=-O CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory --with-extra-charsets=complex
Irix 6.3 IP32 with gcc 2.8.0
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex
BSDI BSD/OS 3.1 i386 with gcc 2.7.2.1
CC=gcc CXX=gcc CXXFLAGS=-O ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex
BSDI BSD/OS 2.1 i386 with gcc 2.7.2
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex

Anyone who has more optimal options for any of the configurations listed above can always mail them to the developer's mailing list at internals@lists.mysql.com.

RPM distributions prior to MySQL Version 3.22 are user-contributed. Beginning with Version 3.22, the RPMs are generated by us at MySQL AB.

If you want to compile a debug version of MySQL, you should add --with-debug or --with-debug=full to the above configure lines and remove any -fomit-frame-pointer options.

2.3 Installing a MySQL Source Distribution

Before you proceed with the source installation, check first to see if our binary is available for your platform and if it will work for you. We put in a lot of effort into making sure that our binaries are built with the best possible options.

You need the following tools to build and install MySQL from source:

If you are using a recent version of gcc, recent enough to understand -fno-exceptions option, it is VERY IMPORTANT that you use it. Otherwise, you may compile a binary that crashes randomly. We also recommend that you use -felide-contructors and -fno-rtti along with -fno-exceptions. When in doubt, do the following: 


CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static

On most systems this will give you a fast and stable binary.

If you run into problems, PLEASE ALWAYS USE mysqlbug when posting questions to mysql@lists.mysql.com. Even if the problem isn't a bug, mysqlbug gathers system information that will help others solve your problem. By not using mysqlbug, you lessen the likelihood of getting a solution to your problem! You will find mysqlbug in the `scripts' directory after you unpack the distribution. See section 1.2.22.3 How to Report Bugs or Problems.

2.3.1 Quick Installation Overview

The basic commands you must execute to install a MySQL source distribution are: 

shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> scripts/mysql_install_db
shell> chown -R root  /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &

If you want have support for InnoDB tables, you should edit the /etc/my.cnf file and remove the # character before the parameters that starts with innodb_.... See section 4.1.2 my.cnf Option Files. See section 7.6.2 InnoDB startup options.

If you start from a source RPM, then do the following: 

shell> rpm --rebuild MySQL-VERSION.src.rpm

This will make a binary RPM that you can install.

You can add new users using the bin/mysql_setpermission script if you install the DBI and Msql-Mysql-modules Perl modules.

A more detailed description follows.

To install a source distribution, follow the steps below, then proceed to section 2.4 Post-installation Setup and Testing, for post-installation initialization and testing:

  1. Pick the directory under which you want to unpack the distribution, and move into it.
  2. Obtain a distribution file from one of the sites listed in section 2.2.1 How to Get MySQL.
  3. If you are interested in using Berkeley DB tables with MySQL, you will need to obtain a patched version of the Berkeley DB source code. Please read the chapter on Berkeley DB tables before proceeding. See section 7.5 BDB or Berkeley_DB Tables. MySQL source distributions are provided as compressed tar archives and have names like `mysql-VERSION.tar.gz', where VERSION is a number like 4.0.0-alpha.
  4. Add a user and group for mysqld to run as: 
    shell> groupadd mysql
shell> useradd -g mysql mysql
    These commands add the mysql group, and the mysql user. The syntax for useradd and groupadd may differ slightly on different versions of Unix. They may also be called adduser and addgroup. You may wish to call the user and group something else instead of mysql.
  5. Unpack the distribution into the current directory: 
    shell> gunzip < /path/to/mysql-VERSION.tar.gz | tar xvf -
    This command creates a directory named `mysql-VERSION'.
  6. Change into the top-level directory of the unpacked distribution: 
    shell> cd mysql-VERSION
    Note that currently you must configure and build MySQL from this top-level directory. You can not build it in a different directory.
  7. Configure the release and compile everything: 
    shell> ./configure --prefix=/usr/local/mysql
shell> make
    When you run configure, you might want to specify some options. Run ./configure --help for a list of options. section 2.3.3 Typical configure Options, discusses some of the more useful options. If configure fails, and you are going to send mail to mysql@lists.mysql.com to ask for assistance, please include any lines from `config.log' that you think can help solve the problem. Also include the last couple of lines of output from configure if configure aborts. Post the bug report using the mysqlbug script. See section 1.2.22.3 How to Report Bugs or Problems. If the compile fails, see section 2.3.5 Problems Compiling?, for help with a number of common problems.
  8. Install everything: 
    shell> make install
    You might need to run this command as root.
  9. Create the MySQL grant tables (necessary only if you haven't installed MySQL before): 
    shell> scripts/mysql_install_db
    Note that MySQL versions older than Version 3.22.10 started the MySQL server when you run mysql_install_db. This is no longer true!
  10. Change ownership of binaries to root and ownership of the data directory to the user that you will run mysqld as: 
    shell> chown -R root  /usr/local/mysql
shell> chown -R mysql /usr/local/mysql/var
shell> chgrp -R mysql /usr/local/mysql
    The first command changes the owner attribute of the files to the root user, the second one changes the owner attribute of the data directory to the mysql user, and the third one changes the group attribute to the mysql group.
  11. If you want to install support for the Perl DBI/DBD interface, see section M.2 Perl Installation Comments.
  12. If you would like MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the support-files/mysql.server script itself and in section 2.4.3 Starting and Stopping MySQL Automatically.

After everything has been installed, you should initialize and test your distribution: 

shell> /usr/local/mysql/bin/safe_mysqld --user=mysql &

If that command fails immediately with mysqld daemon ended then you can find some information in the file `mysql-data-directory/'hostname'.err'. The likely reason is that you already have another mysqld server running. See section 4.1.4 Running Multiple MySQL Servers on the Same Machine.

See section 2.4 Post-installation Setup and Testing.

2.3.2 Applying Patches

Sometimes patches appear on the mailing list or are placed in the patches area of the MySQL Web site.

To apply a patch from the mailing list, save the message in which the patch appears in a file, change into the top-level directory of your MySQL source tree, and run these commands: 

shell> patch -p1 < patch-file-name
shell> rm config.cache
shell> make clean

Patches from the FTP site are distributed as plain text files or as files compressed with gzip. Apply a plain patch as shown above for mailing list patches. To apply a compressed patch, change into the top-level directory of your MySQL source tree and run these commands: 

shell> gunzip < patch-file-name.gz | patch -p1
shell> rm config.cache
shell> make clean

After applying a patch, follow the instructions for a normal source install, beginning with the ./configure step. After running the make install step, restart your MySQL server.

You may need to bring down any currently running server before you run make install. (Use mysqladmin shutdown to do this.) Some systems do not allow you to install a new version of a program if it replaces the version that is currently executing.

2.3.3 Typical configure Options

The configure script gives you a great deal of control over how you configure your MySQL distribution. Typically you do this using options on the configure command line. You can also affect configure using certain environment variables. See section H Environment Variables. For a list of options supported by configure, run this command: 

shell> ./configure --help

Some of the more commonly-used configure options are described below:

2.3.4 Installing from the Development Source Tree

CAUTION: You should read this section only if you are interested in helping us test our new code. If you just want to get MySQL up and running on your system, you should use a standard release distribution (either a source or binary distribution will do).

To obtain our most recent development source tree, use these instructions:

  1. Download BitKeeper from http://www.bitmover.com/cgi-bin/download.cgi. You will need Bitkeeper 2.0 or newer to access our repository.
  2. Follow the instructions to install it.
  3. After BitKeeper is installed, use this command if you want to clone the MySQL 3.23 branch: 
    shell> bk clone bk://work.mysql.com:7000 mysql
    To clone the 4.0 branch, use this command instead: 
    shell> bk clone bk://work.mysql.com:7001 mysql-4.0
    The initial download of the source tree may take a while, depending on the speed of your connection; be patient.
  4. You will need GNU autoconf, automake, libtool, and m4 to run the next set of commands. If you get some strange error during this stage, check that you really have libtool installed! 
    shell> cd mysql
shell> bk -r edit
shell> aclocal; autoheader; autoconf;  automake;
shell> ./configure  # Add your favorite options here
shell> make
    A collection of our standard configure scripts is located in the `BUILD/' subdirectory. If you are lazy, you can use `BUILD/compile-pentium-debug'. To compile on a different architecture, modify the script removing flags that are Pentium-specific.
  5. When the build is done, run make install. Be careful with this on a production machine; the command may overwrite your live release installation. If you have another installation of MySQL, we recommand that you run ./configure with different values for the prefix, tcp-port, and unix-socket-path options than those used for your production server.
  6. Play hard with your new installation and try to make the new features crash. Start by running make test. See section 9.3.2 MySQL Test Suite.
  7. If you have gotten to the make stage and the distribution does not compile, please report it to bugs@lists.mysql.com. If you have installed the latest versions of the required GNU tools, and they crash trying to process our configuration files, please report that also. However, if you execute aclocal and get a command not found error or a similar problem, do not report it. Instead, make sure all the necessary tools are installed and that your PATH variable is set correctly so your shell can find them.
  8. After the initial bk clone operation to get the source tree, you should run bk pull periodically to get the updates.
  9. You can examine the change history for the tree with all the diffs by using bk sccstool. If you see some funny diffs or code that you have a question about, do not hesitate to send e-mail to internals@lists.mysql.com. Also, if you think you have a better idea on how to do something, send an email to the same address with a patch. bk diffs will produce a patch for you after you have made changes to the source. If you do not have the time to code your idea, just send a description.
  10. BitKeeper has a nice help utility that you can access via bk helptool.

2.3.5 Problems Compiling?

All MySQL programs compile cleanly for us with no warnings on Solaris using gcc. On other systems, warnings may occur due to differences in system include files. See section 2.3.6 MIT-pthreads Notes for warnings that may occur when using MIT-pthreads. For other problems, check the list below.

The solution to many problems involves reconfiguring. If you do need to reconfigure, take note of the following:

To prevent old configuration information or object files from being used, run these commands before rerunning configure: 

shell> rm config.cache
shell> make clean

Alternatively, you can run make distclean.

The list below describes some of the problems compiling MySQL that have been found to occur most often:

2.3.6 MIT-pthreads Notes

This section describes some of the issues involved in using MIT-pthreads.

Note that on Linux you should NOT use MIT-pthreads but install LinuxThreads! See section 2.6.1 Linux Notes (All Linux Versions).

If your system does not provide native thread support, you will need to build MySQL using the MIT-pthreads package. This includes older FreeBSD systems, SunOS 4.x, Solaris 2.4 and earlier, and some others. See section 2.2.2 Operating Systems Supported by MySQL.

2.4 Post-installation Setup and Testing

Once you've installed MySQL (from either a binary or source distribution), you need to initialize the grant tables, start the server, and make sure that the server works okay. You may also wish to arrange for the server to be started and stopped automatically when your system starts up and shuts down.

Normally you install the grant tables and start the server like this for installation from a source distribution: 

shell> ./scripts/mysql_install_db
shell> cd mysql_installation_directory
shell> ./bin/safe_mysqld --user=mysql &

For a binary distribution (not RPM or pkg packages), do this: 

shell> cd mysql_installation_directory
shell> ./bin/mysql_install_db
shell> ./bin/safe_mysqld --user=mysql &

This creates the mysql database which will hold all database privileges, the test database which you can use to test MySQL and also privilege entries for the user that run mysql_install_db and a root user (without any passwords). This also starts the mysqld server.

mysql_install_db will not overwrite any old privilege tables, so it should be safe to run in any circumstances. If you don't want to have the test database you can remove it with mysqladmin -u root drop test.

Testing is most easily done from the top-level directory of the MySQL distribution. For a binary distribution, this is your installation directory (typically something like `/usr/local/mysql'). For a source distribution, this is the main directory of your MySQL source tree.

In the commands shown below in this section and in the following subsections, BINDIR is the path to the location in which programs like mysqladmin and safe_mysqld are installed. For a binary distribution, this is the `bin' directory within the distribution. For a source distribution, BINDIR is probably `/usr/local/bin', unless you specified an installation directory other than `/usr/local' when you ran configure. EXECDIR is the location in which the mysqld server is installed. For a binary distribution, this is the same as BINDIR. For a source distribution, EXECDIR is probably `/usr/local/libexec'.

Testing is described in detail below:

  1. If necessary, start the mysqld server and set up the initial MySQL grant tables containing the privileges that determine how users are allowed to connect to the server. This is normally done with the mysql_install_db script: 
    shell> scripts/mysql_install_db
    Typically, mysql_install_db needs to be run only the first time you install MySQL. Therefore, if you are upgrading an existing installation, you can skip this step. (However, mysql_install_db is quite safe to use and will not update any tables that already exist, so if you are unsure of what to do, you can always run mysql_install_db.) mysql_install_db creates six tables (user, db, host, tables_priv, columns_priv, and func) in the mysql database. A description of the initial privileges is given in section 4.3.4 Setting Up the Initial MySQL Privileges. Briefly, these privileges allow the MySQL root user to do anything, and allow anybody to create or use databases with a name of 'test' or starting with 'test_'. If you don't set up the grant tables, the following error will appear in the log file when you start the server: 
    mysqld: Can't find file: 'host.frm'
    The above may also happen with a binary MySQL distribution if you don't start MySQL by executing exactly ./bin/safe_mysqld! See section 4.7.2 safe_mysqld, the wrapper around mysqld. You might need to run mysql_install_db as root. However, if you prefer, you can run the MySQL server as an unprivileged (non-root) user, provided that user can read and write files in the database directory. Instructions for running MySQL as an unprivileged user are given in section A.3.2 How to Run MySQL As a Normal User. If you have problems with mysql_install_db, see section 2.4.1 Problems Running mysql_install_db. There are some alternatives to running the mysql_install_db script as it is provided in the MySQL distribution: For more information about these alternatives, see section 4.3.4 Setting Up the Initial MySQL Privileges.
  2. Start the MySQL server like this: 
    shell> cd mysql_installation_directory
shell> bin/safe_mysqld &
    If you have problems starting the server, see section 2.4.2 Problems Starting the MySQL Server.
  3. Use mysqladmin to verify that the server is running. The following commands provide a simple test to check that the server is up and responding to connections: 
    shell> BINDIR/mysqladmin version
shell> BINDIR/mysqladmin variables
    The output from mysqladmin version varies slightly depending on your platform and version of MySQL, but should be similar to that shown below: 
    shell> BINDIR/mysqladmin version
mysqladmin  Ver 8.14 Distrib 3.23.32, for linux on i586
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          3.23.32-debug
Protocol version        10
Connection              Localhost via Unix socket
TCP port                3306
UNIX socket             /tmp/mysql.sock
Uptime:                 16 sec

Threads: 1  Questions: 9  Slow queries: 0  Opens: 7  Flush tables: 2  Open tables: 0 Queries per second avg: 0.000  Memory in use: 132K  Max memory used: 16773K
    To get a feeling for what else you can do with BINDIR/mysqladmin, invoke it with the --help option.
  4. Verify that you can shut down the server: 
    shell> BINDIR/mysqladmin -u root shutdown
  5. Verify that you can restart the server. Do this using safe_mysqld or by invoking mysqld directly. For example: 
    shell> BINDIR/safe_mysqld --log &
    If safe_mysqld fails, try running it from the MySQL installation directory (if you are not already there). If that doesn't work, see section 2.4.2 Problems Starting the MySQL Server.
  6. Run some simple tests to verify that the server is working. The output should be similar to what is shown below: 
    shell> BINDIR/mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
+-----------+

shell> BINDIR/mysqlshow mysql
Database: mysql
+--------------+
|    Tables    |
+--------------+
| columns_priv |
| db           |
| func         |
| host         |
| tables_priv  |
| user         |
+--------------+

shell> BINDIR/mysql -e "select host,db,user from db" mysql
+------+--------+------+
| host | db     | user |
+------+--------+------+
| %    | test   |      |
| %    | test_% |      |
+------+--------+------+
    There is also a benchmark suite in the `sql-bench' directory (under the MySQL installation directory) that you can use to compare how MySQL performs on different platforms. The `sql-bench/Results' directory contains the results from many runs against different databases and platforms. To run all tests, execute these commands: 
    shell> cd sql-bench
shell> run-all-tests
    If you don't have the `sql-bench' directory, you are probably using an RPM for a binary distribution. (Source distribution RPMs include the benchmark directory.) In this case, you must first install the benchmark suite before you can use it. Beginning with MySQL Version 3.22, there are benchmark RPM files named `mysql-bench-VERSION-i386.rpm' that contain benchmark code and data. If you have a source distribution, you can also run the tests in the `tests' subdirectory. For example, to run `auto_increment.tst', do this: 
    shell> BINDIR/mysql -vvf test < ./tests/auto_increment.tst
    The expected results are shown in the `./tests/auto_increment.res' file.

2.4.1 Problems Running mysql_install_db

The purpose of the mysql_install_db script is to generate new MySQL privilege tables. It will not affect any other data! It will also not do anything if you already have MySQL privilege tables installed!

If you want to re-create your privilege tables, you should take down the mysqld server, if it's running, and then do something like: 

mv mysql-data-directory/mysql mysql-data-directory/mysql-old
mysql_install_db

This section lists problems you might encounter when you run mysql_install_db:

mysql_install_db doesn't install the grant tables
You may find that mysql_install_db fails to install the grant tables and terminates after displaying the following messages: 
starting mysqld daemon with databases from XXXXXX
mysql daemon ended
In this case, you should examine the log file very carefully! The log should be located in the directory `XXXXXX' named by the error message, and should indicate why mysqld didn't start. If you don't understand what happened, include the log when you post a bug report using mysqlbug! See section 1.2.22.3 How to Report Bugs or Problems.
There is already a mysqld daemon running
In this case, you probably don't have to run mysql_install_db at all. You have to run mysql_install_db only once, when you install MySQL the first time.
Installing a second mysqld daemon doesn't work when one daemon is running
This can happen when you already have an existing MySQL installation, but want to put a new installation in a different place (for example, for testing, or perhaps you simply want to run two installations at the same time). Generally the problem that occurs when you try to run the second server is that it tries to use the same socket and port as the old one. In this case you will get the error message: Can't start server: Bind on TCP/IP port: Address already in use or Can't start server : Bind on unix socket.... See section 4.1.3 Installing Many Servers on the Same Machine.
You don't have write access to `/tmp'
If you don't have write access to create a socket file at the default place (in `/tmp') or permission to create temporary files in `/tmp,' you will get an error when running mysql_install_db or when starting or using mysqld. You can specify a different socket and temporary directory as follows: 
shell> TMPDIR=/some_tmp_dir/
shell> MYSQL_UNIX_PORT=/some_tmp_dir/mysqld.sock
shell> export TMPDIR MYSQL_UNIX_PORT
See section A.4.5 How to Protect or change the MySQL socket file `/tmp/mysql.sock'. `some_tmp_dir' should be the path to some directory for which you have write permission. See section H Environment Variables. After this you should be able to run mysql_install_db and start the server with these commands: 
shell> scripts/mysql_install_db
shell> BINDIR/safe_mysqld &
mysqld crashes immediately
If you are running RedHat Version 5.0 with a version of glibc older than 2.0.7-5, you should make sure you have installed all glibc patches! There is a lot of information about this in the MySQL mail archives. Links to the mail archives are available online at http://www.mysql.com/documentation/. Also, see section 2.6.1 Linux Notes (All Linux Versions). You can also start mysqld manually using the --skip-grant-tables option and add the privilege information yourself using mysql: 
shell> BINDIR/safe_mysqld --skip-grant-tables &
shell> BINDIR/mysql -u root mysql
From mysql, manually execute the SQL commands in mysql_install_db. Make sure you run mysqladmin flush-privileges or mysqladmin reload afterward to tell the server to reload the grant tables.

2.4.2 Problems Starting the MySQL Server

If you are going to use tables that support transactions (BDB, InnoDB), you should first create a my.cnf file and set startup options for the table types you plan to use. See section 7 MySQL Table Types.

Generally, you start the mysqld server in one of three ways:

When the mysqld daemon starts up, it changes directory to the data directory. This is where it expects to write log files and the pid (process ID) file, and where it expects to find databases.

The data directory location is hardwired in when the distribution is compiled. However, if mysqld expects to find the data directory somewhere other than where it really is on your system, it will not work properly. If you have problems with incorrect paths, you can find out what options mysqld allows and what the default path settings are by invoking mysqld with the --help option. You can override the defaults by specifying the correct pathnames as command-line arguments to mysqld. (These options can be used with safe_mysqld as well.)

Normally you should need to tell mysqld only the base directory under which MySQL is installed. You can do this with the --basedir option. You can also use --help to check the effect of changing path options (note that --help must be the final option of the mysqld command). For example: 

shell> EXECDIR/mysqld --basedir=/usr/local --help

Once you determine the path settings you want, start the server without the --help option.

Whichever method you use to start the server, if it fails to start up correctly, check the log file to see if you can find out why. Log files are located in the data directory (typically `/usr/local/mysql/data' for a binary distribution, `/usr/local/var' for a source distribution, `\mysql\data\mysql.err' on Windows.) Look in the data directory for files with names of the form `host_name.err' and `host_name.log' where host_name is the name of your server host. Then check the last few lines of these files: 

shell> tail host_name.err
shell> tail host_name.log

If you find something like the following in the log file: 

000729 14:50:10  bdb:  Recovery function for LSN 1 27595 failed
000729 14:50:10  bdb:  warning: ./test/t1.db: No such file or directory
000729 14:50:10  Can't init databases

This means that you didn't start mysqld with --bdb-no-recover and Berkeley DB found something wrong with its log files when it tried to recover your databases. To be able to continue, you should move away the old Berkeley DB log file from the database directory to some other place, where you can later examine these. The log files are named `log.0000000001', where the number will increase over time.

If you are running mysqld with BDB table support and mysqld core dumps at start this could be because of some problems with the BDB recover log. In this case you can try starting mysqld with --bdb-no-recover. If this helps, then you should remove all `log.*' files from the data directory and try starting mysqld again.

If you get the following error, it means that some other program (or another mysqld server) is already using the TCP/IP port or socket mysqld is trying to use: 

Can't start server: Bind on TCP/IP port: Address already in use
  or
Can't start server : Bind on unix socket...

Use ps to make sure that you don't have another mysqld server running. If you can't find another server running, you can try to execute the command telnet your-host-name tcp-ip-port-number and press RETURN a couple of times. If you don't get an error message like telnet: Unable to connect to remote host: Connection refused, something is using the TCP/IP port mysqld is trying to use. See section 2.4.1 Problems Running mysql_install_db and section 4.1.4 Running Multiple MySQL Servers on the Same Machine.

If mysqld is currently running, you can find out what path settings it is using by executing this command: 

shell> mysqladmin variables

or 

shell> mysqladmin -h 'your-host-name' variables

If safe_mysqld starts the server but you can't connect to it, you should make sure you have an entry in `/etc/hosts' that looks like this: 

127.0.0.1       localhost

This problem occurs only on systems that don't have a working thread library and for which MySQL must be configured to use MIT-pthreads.

If you can't get mysqld to start you can try to make a trace file to find the problem. See section G.1.2 Creating trace files.

If you are using InnoDB tables, refer to the InnoDB-specific startup options. See section 7.6.2 InnoDB startup options.

If you are using BDB (Berkeley DB) tables, you should familiarize yourself with the different BDB specific startup options. See section 7.5.3 BDB startup options.

2.4.3 Starting and Stopping MySQL Automatically

The mysql.server and safe_mysqld scripts can be used to start the server automatically at system startup time. mysql.server can also be used to stop the server.

The mysql.server script can be used to start or stop the server by invoking it with start or stop arguments: 

shell> mysql.server start
shell> mysql.server stop

mysql.server can be found in the `share/mysql' directory under the MySQL installation directory or in the `support-files' directory of the MySQL source tree.

Before mysql.server starts the server, it changes directory to the MySQL installation directory, then invokes safe_mysqld. You might need to edit mysql.server if you have a binary distribution that you've installed in a non-standard location. Modify it to cd into the proper directory before it runs safe_mysqld. If you want the server to run as some specific user, add an appropriate user line to the `/etc/my.cnf' file, as shown later in this section.

mysql.server stop brings down the server by sending a signal to it. You can take down the server manually by executing mysqladmin shutdown.

You might want to add these start and stop commands to the appropriate places in your `/etc/rc*' files when you start using MySQL for production applications. Note that if you modify mysql.server, then upgrade MySQL sometime, your modified version will be overwritten, so you should make a copy of your edited version that you can reinstall.

If your system uses `/etc/rc.local' to start external scripts, you should append the following to it: 

/bin/sh -c 'cd /usr/local/mysql ; ./bin/safe_mysqld --user=mysql &'

You can also add options for mysql.server in a global `/etc/my.cnf' file. A typical `/etc/my.cnf' file might look like this: 

[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql

[mysql.server]
basedir=/usr/local/mysql

The mysql.server script understands the following options: datadir, basedir, and pid-file.

The following table shows which option groups each of the startup scripts read from option files:

Script Option groups
mysqld mysqld and server
mysql.server mysql.server, mysqld, and server
safe_mysqld mysql.server, mysqld, and server

See section 4.1.2 my.cnf Option Files.

2.5 Upgrading/Downgrading MySQL

You can always move the MySQL form and data files between different versions on the same architecture as long as you have the same base version of MySQL. The current base version is 3. If you change the character set when running MySQL (which may also change the sort order), you must run myisamchk -r -q on all tables. Otherwise your indexes may not be ordered correctly.

If you are afraid of new versions, you can always rename your old mysqld to something like mysqld-'old-version-number'. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld!

When you do an upgrade you should also back up your old databases, of course.

If after an upgrade, you experience problems with recompiled client programs, like Commands out of sync or unexpected core dumps, you probably have used an old header or library file when compiling your programs. In this case you should check the date for your `mysql.h' file and `libmysqlclient.a' library to verify that they are from the new MySQL distribution. If not, please recompile your programs!

If you get some problems that the new mysqld server doesn't want to start or that you can't connect without a password, check that you don't have some old `my.cnf' file from your old installation! You can check this with: program-name --print-defaults. If this outputs anything other than the program name, you have an active my.cnf file that will affect things!

It is a good idea to rebuild and reinstall the Msql-Mysql-modules distribution whenever you install a new release of MySQL, particularly if you notice symptoms such as all your DBI scripts dumping core after you upgrade MySQL.

2.5.1 Upgrading From Version 3.23 to Version 4.0

You can use your old data files without any modification with Version 4.0. If you want to move your data from a MySQL 4.0 server to an older server, you have to use mysqldump.

Old clients should work with a Version 4.0 server without any problems.

The following lists tell what you have to watch out for when upgrading to Version 4.0;

2.5.2 Upgrading From Version 3.22 to Version 3.23

MySQL Version 3.23 supports tables of the new MyISAM type and the old ISAM type. You don't have to convert your old tables to use these with Version 3.23. By default, all new tables will be created with type MyISAM (unless you start mysqld with the --default-table-type=isam option). You can change an ISAM table to a MyISAM table with ALTER TABLE table_name TYPE=MyISAM or the Perl script mysql_convert_table_format.

Version 3.22 and 3.21 clients will work without any problems with a Version 3.23 server.

The following lists tell what you have to watch out for when upgrading to Version 3.23:

2.5.3 Upgrading from Version 3.21 to Version 3.22

Nothing that affects compatibility has changed between Version 3.21 and 3.22. The only pitfall is that new tables that are created with DATE type columns will use the new way to store the date. You can't access these new fields from an old version of mysqld.

After installing MySQL Version 3.22, you should start the new server and then run the mysql_fix_privilege_tables script. This will add the new privileges that you need to use the GRANT command. If you forget this, you will get Access denied when you try to use ALTER TABLE, CREATE INDEX, or DROP INDEX. If your MySQL root user requires a password, you should give this as an argument to mysql_fix_privilege_tables.

The C API interface to mysql_real_connect() has changed. If you have an old client program that calls this function, you must place a 0 for the new db argument (or recode the client to send the db element for faster connections). You must also call mysql_init() before calling mysql_real_connect()! This change was done to allow the new mysql_options() function to save options in the MYSQL handler structure.

The mysqld variable key_buffer has changed names to key_buffer_size, but you can still use the old name in your startup files.

2.5.4 Upgrading from Version 3.20 to Version 3.21

If you are running a version older than Version 3.20.28 and want to switch to Version 3.21, you need to do the following:

You can start the mysqld Version 3.21 server with safe_mysqld --old-protocol to use it with clients from a Version 3.20 distribution. In this case, the new client function mysql_errno() will not return any server error, only CR_UNKNOWN_ERROR (but it works for client errors), and the server uses the old password() checking rather than the new one.

If you are NOT using the --old-protocol option to mysqld, you will need to make the following changes:

MySQL Version 3.20.28 and above can handle the new user table format without affecting clients. If you have a MySQL version earlier than Version 3.20.28, passwords will no longer work with it if you convert the user table. So to be safe, you should first upgrade to at least Version 3.20.28 and then upgrade to Version 3.21.

The new client code works with a 3.20.x mysqld server, so if you experience problems with 3.21.x, you can use the old 3.20.x server without having to recompile the clients again.

If you are not using the --old-protocol option to mysqld, old clients will issue the error message: 

ERROR: Protocol mismatch. Server Version = 10 Client Version = 9

The new Perl DBI/DBD interface also supports the old mysqlperl interface. The only change you have to make if you use mysqlperl is to change the arguments to the connect() function. The new arguments are: host, database, user, password (the user and password arguments have changed places). See section 8.2.2 The DBI Interface.

The following changes may affect queries in old applications:

2.5.5 Upgrading to Another Architecture

If you are using MySQL Version 3.23, you can copy the .frm, .MYI, and .MYD files between different architectures that support the same floating-point format. (MySQL takes care of any byte swapping issues.)

The MySQL ISAM data and index files (`.ISD' and `*.ISM', respectively) are architecture-dependent and in some cases OS-dependent. If you want to move your applications to another machine that has a different architecture or OS than your current machine, you should not try to move a database by simply copying the files to the other machine. Use mysqldump instead.

By default, mysqldump will create a file full of SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client.

Try mysqldump --help to see what options are available. If you are moving the data to a newer version of MySQL, you should use mysqldump --opt with the newer version to get a fast, compact dump.

The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database is located: 

shell> mysqladmin -h 'other hostname' create db_name
shell> mysqldump --opt db_name \
        | mysql -h 'other hostname' db_name

If you want to copy a database from a remote machine over a slow network, you can use: 

shell> mysqladmin create db_name
shell> mysqldump -h 'other hostname' --opt --compress db_name \
        | mysql db_name

You can also store the result in a file, then transfer the file to the target machine and load the file into the database there. For example, you can dump a database to a file on the source machine like this: 

shell> mysqldump --quick db_name | gzip > db_name.contents.gz

(The file created in this example is compressed.) Transfer the file containing the database contents to the target machine and run these commands there: 

shell> mysqladmin create db_name
shell> gunzip < db_name.contents.gz | mysql db_name

You can also use mysqldump and mysqlimport to accomplish the database transfer. For big tables, this is much faster than simply using mysqldump. In the commands shown below, DUMPDIR represents the full pathname of the directory you use to store the output from mysqldump.

First, create the directory for the output files and dump the database: 

shell> mkdir DUMPDIR
shell> mysqldump --tab=DUMPDIR db_name

Then transfer the files in the DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there: 

shell> mysqladmin create db_name           # create database
shell> cat DUMPDIR/*.sql | mysql db_name   # create tables in database
shell> mysqlimport db_name DUMPDIR/*.txt   # load data into tables

Also, don't forget to copy the mysql database, because that's where the grant tables (user, db, host) are stored. You may have to run commands as the MySQL root user on the new machine until you have the mysql database in place.

After you import the mysql database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information.

2.6 Operating System Specific Notes

2.6.1 Linux Notes (All Linux Versions)

The notes below regarding glibc apply only to the situation when you build MySQL yourself. If you are running Linux on an x86 machine, in most cases it is much better for you to just use our binary. We link our binaries against the best patched version of glibc we can come up with and with the best compiler options, in an attempt to make it suitable for a high-load server. So if you read the text below, and are in doubt about what you should do, try our binary first to see if it meets your needs, and worry about your own build only after you have discovered that our binary is not good enough. In that case, we would appreciate a note about it, so we can build a better binary next time. For a typical user, even for setups with a lot of concurrent connections and/or tables exceeding 2GB limit, our binary in most cases is the best choice.

MySQL uses LinuxThreads on Linux. If you are using an old Linux version that doesn't have glibc2, you must install LinuxThreads before trying to compile MySQL. You can get LinuxThreads at http://www.mysql.com/Downloads/Linux.

NOTE: We have seen some strange problems with Linux 2.2.14 and MySQL on SMP systems; If you have a SMP system, we recommend you to upgrade to Linux 2.4 ASAP! Your system will be faster and more stable by doing this!

Note that glibc versions before and including Version 2.1.1 have a fatal bug in pthread_mutex_timedwait handling, which is used when you do INSERT DELAYED. We recommend you to not use INSERT DELAYED before upgrading glibc.

If you plan to have 1000+ concurrent connections, you will need to make some changes to LinuxThreads, recompile it, and relink MySQL against the new `libpthread.a'. Increase PTHREAD_THREADS_MAX in `sysdeps/unix/sysv/linux/bits/local_lim.h' to 4096 and decrease STACK_SIZE in `linuxthreads/internals.h' to 256 KB. The paths are relative to the root of glibc Note that MySQL will not be stable with around 600-1000 connections if STACK_SIZE is the default of 2 MB.

If you have a problem with that MySQL can't open enough files, or connections, it may be that you haven't configured Linux to handle enough files.

In Linux 2.2 and forwards, you can check the number of allocated file handlers by doing: 

cat /proc/sys/fs/file-max
cat /proc/sys/fs/dquot-max 
cat /proc/sys/fs/super-max

If you have more than 16M of memory, you should add something like the following in your boot script (`/etc/rc/boot.local' on SuSE): 

echo 65536 > /proc/sys/fs/file-max
echo 8192 > /proc/sys/fs/dquot-max
echo 1024 > /proc/sys/fs/super-max

You can also run the above from the command line as root, but in this case your old limits will be used next time your computer reboots.

You should also add /etc/my.cnf: 

[safe_mysqld]
open-files-limit=8192

The above should allow MySQL to create up to 8192 connections + files.

The STACK_SIZE constant in LinuxThreads controls the spacing of thread stacks in the address space. It needs to be large enough so that there will be plenty of room for the stack of each individual thread, but small enough to keep the stack of some thread from running into the global mysqld data. Unfortunately, the Linux implementation of mmap(), as we have experimentally discovered, will successfully unmap an already mapped region if you ask it to map out an address already in use, zeroing out the data on the entire page, instead of returning an error. So, the safety of mysqld or any other threaded application depends on the "gentleman" behavior of the code that creates threads. The user must take measures to make sure the number of running threads at any time is sufficiently low for thread stacks to stay away from the global heap. With mysqld, you should enforce this "gentleman" behavior by setting a reasonable value for the max_connections variable.

If you build MySQL yourself and do not want to mess with patching LinuxThreads, you should set max_connections to a value no higher than 500. It should be even less if you have a large key buffer, large heap tables, or some other things that make mysqld allocate a lot of memory or if you are running a 2.2 kernel with a 2GB patch. If you are using our binary or RPM version 3.23.25 or later, you can safely set max_connections at 1500, assuming no large key buffer or heap tables with lots of data. The more you reduce STACK_SIZE in LinuxThreads the more threads you can safely create. We recommend the values between 128K and 256K.

If you use a lot of concurrent connections, you may suffer from a "feature" in the 2.2 kernel that penalizes a process for forking or cloning a child in an attempt to prevent a fork bomb attack. This will cause MySQL not to scale well as you increase the number of concurrent clients. On single CPU systems, we have seen this manifested in a very slow thread creation, which means it may take a long time to connect to MySQL (as long as 1 minute), and it may take just as long to shut it down. On multiple CPU systems, we have observed a gradual drop in query speed as the number of clients increases. In the process of trying to find a solution, we have received a kernel patch from one of our users, who claimed it made a lot of difference for his site. The patch is available here (http://www.mysql.com/Downloads/Patches/linux-fork.patch). We have now done rather extensive testing of this patch on both development and production systems. It has significantly improved MySQL performance without causing any problems and we now recommend it to our users who are still running high-load servers on 2.2 kernels. This issue has been fixed in the 2.4 kernel, so if you are not satisfied with the current performance of your system, rather than patching your 2.2 kernel, it might be easier to just upgrade to 2.4, which will also give you a nice SMP boost in addition to fixing this fairness bug.

We have tested MySQL on the 2.4 kernel on a 2 CPU machine and found MySQL scales MUCH better - there was virtually no slowdown on query throughput all the way up to 1000 clients, and MySQL scaling factor ( computed as the ratio of maximum throughput to the throughput with one client) was 180%. We have observed similar results on a 4-CPU system - virtually no slowdown as the number of clients was increased up to 1000, and 300% scaling factor. So for a high-load SMP server we would definitely recommend the 2.4 kernel at this point. We have discovered that it is essential to run mysqld process with the highest possible priority on the 2.4 kernel to achieve maximum performance. This can be done by adding renice -20 $$ command to safe_mysqld. In our testing on a 4-CPU machine, increasing the priority gave 60% increase in throughput with 400 clients.

We are currently also trying to collect more info on how well