Whole document tree


www.fifi.org
    Documentation
        Manpages
        GNU Info
        Debian document tree
        Whole document tree
    Trigance web page
    Public services
    User info
    Mailing lists
    Secure server
    Multilingual usage
 

Copyright (C) 2000-2008
Philippe Troin
<webmaster@fifi.org>.

Validate HTML
Validate CSS

    

Whole document tree

MySQL Reference Manual for version 3.23.51. - 2 MySQL Installation 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:

  • MySQL-VERSION.i386.rpm The MySQL server. You will need this unless you only want to connect to a MySQL server running on another machine.
  • MySQL-client-VERSION.i386.rpm The standard MySQL client programs. You probably always want to install this package.
  • MySQL-bench-VERSION.i386.rpm Tests and benchmarks. Requires Perl and msql-mysql-modules RPMs.
  • MySQL-devel-VERSION.i386.rpm Libraries and include files needed if you want to compile other MySQL clients, such as the Perl modules.
  • MySQL-VERSION.src.rpm This contains the source code for all of the above packages. It can also be used to try to build RPMs for other architectures (for example, Alpha or SPARC).

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.

Starting from 3.23.50, named pipes are only enabled if mysqld is started with --enable-named-pipe.

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://download.sourceforge.net/mirrors/mysql/

If you are interested in becoming a MySQL mirror site, you may anonymously rsync with: rsync://download.sourceforge.net/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:

  • Canada [Tryc] WWW
  • USA [Hurricane Electric/San Jose] WWW
  • USA [ValueClick, Los Angeles CA] WWW FTP
  • USA [Wisconsin University/Wisconsin] WWW FTP
  • USA [LinuxWired/Scottsdale, AZ] WWW FTP
  • USA [adgrafix.com/Boston, MA] WWW
  • USA [netNumina/Cambridge, MA] WWW
  • USA [Ahaza Systems/Seattle, WA] WWW FTP

South America:

Asia:

  • China [linuxforum.net] WWW
  • China [HKLPG/Hong Kong] WWW
  • China [Gremlins/Hong Kong] WWW FTP
  • China [shellhung.org/Hong Kong] WWW FTP
  • Indonesia [incaf.net] WWW
  • Indonesia [web.id] WWW FTP
  • Japan [Soft Agency] WWW
  • Japan [u-aizu.ac.jp/Aizu] FTP
  • South Korea [Webiiz] WWW
  • South Korea [PanworldNet] WWW
  • Singapore [HJC] WWW FTP
  • Taiwan [TTN] WWW
  • Taiwan [nctu.edu/HsinChu] WWW

Africa:

  • South-Africa [Mweb] WWW
  • South Africa [The Internet Solution/Johannesburg] FTP

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:

  • General stability of the thread library. A platform may have excellent reputation otherwise, but if the thread library is unstable in the code that is called by MySQL, even if everything else is perfect, MySQL will be only as stable as the thread library.
  • The ability of the kernel and/or thread library to take advantage of SMP on multi-processor systems. In other words, when a process creates a thread, it should be possible for that thread to run on a different CPU than the original process.
  • The ability of the kernel and/or the thread library to run many threads which acquire/release a mutex over a short critical region frequently without excessive context switches. In other words, if the implementation of pthread_mutex_lock() is too anxious to yield CPU, this will hurt MySQL tremendously. If this issue is not taken care of, adding extra CPUs will actually make MySQL slower.
  • General file system stability/performance.
  • Ability of the file system to deal with large files at all and deal with them efficiently, if your tables are big.
  • Our level of expertise here at MySQL AB with the platform. If we know a platform well, we introduce platform-specific optimizations/fixes enabled at compile time. We can also provide advice on configuring your system optimally for MySQL.
  • The amount of testing of similar configurations we have done internally.
  • The number of users that have successfully run MySQL on that platform in similar configurations. If this number is high, the chances of hitting some platform-specific surprise are much smaller.

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:

  • Normally, if you are beginning to use MySQL for the first time or trying to port it to some system for which there is no binary distribution, we recommend going with the stable release (currently Version 3.23.51. Note that all MySQL releases are checked with the MySQL benchmarks and an extensive test suite before each release.
  • Otherwise, if you are running an old system and want to upgrade, but don't want to take chances with a non-seamless upgrade, you should upgrade to the latest in the same branch you are using (where only the last version number is newer than yours). We have tried to fix only fatal bugs and make small, relatively safe changes to that version.

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:

  • If you want to install MySQL at some explicit location. (The standard binary distributions are ``ready to run'' at any place, but you may want to get even more flexibility).
  • To be able to satisfy different user requirements, we are providing two different binary versions; One compiled with the non-transactional table handlers, (a small, fast binary), and one configured with the most important extended options like transaction-safe tables. Both versions are compiled from the same source distribution. All native MySQL clients can connect to both MySQL versions. The extended MySQL binary distribution is marked with the -max suffix and is configured with the same options as mysqld-max. See section 4.7.5 mysqld-max, An extended mysqld server. If you want to use the MySQL-Max RPM, you must first install the standard MySQL RPM.
  • If you want to configure mysqld with some extra features that are NOT in the standard binary distributions. Here is a list of the most common extra options that you may want to use:
    • --with-berkeley-db
    • --with-innodb
    • --with-raid
    • --with-libwrap
    • --with-named-z-lib (This is done for some of the binaries)
    • --with-debug[=full]
  • The default binary distribution is normally compiled with support for all characters sets and should work on a variety of processors from the same processor family. If you want a faster MySQL server you may want to recompile it with support for only the character sets you need, use a better compiler (like pgcc) or use compiler options that are better optimized for your processor.
  • If you have found a bug and reported it to the MySQL development team you will probably receive a patch that you need to apply to the source distribution to get the bug fixed.
  • If you want to read (and/or modify) the C and C++ code that makes up MySQL, you should get a source distribution. The source code is always the ultimate manual. Source distributions also contain more tests and examples than binary distributions.

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:

  • The first number (3) describes the file format. All Version 3 releases have the same file format.
  • The second number (21) is the release level. Normally there are two to choose from. One is the release/stable branch (currently 23) and the other is the development branch (currently 4.0). Normally both are stable, but the development version may have quirks, missing documentation on new features, or may fail to compile on some systems.
  • The third number (17) is the version number within the release level. This is incremented for each new distribution. Usually you want the latest version for the release level you have chosen.
  • The suffix (beta) indicates the stability level of the release. The possible suffixes are:
    • alpha indicates that the release contains some large section of new code that hasn't been 100% tested. Known bugs (usually there are none) should be documented in the News section. See section F MySQL change history. There are also new commands and extensions in most alpha releases. Active development that may involve major code changes can occur on an alpha release, but everything will be tested before doing a release. There should be no known bugs in any MySQL release.
    • beta means that all new code has been tested. No major new features that could cause corruption on old code are added. There should be no known bugs. A version changes from alpha to beta when there haven't been any reported fatal bugs within an alpha version for at least a month and we don't plan to add any features that could make any old command more unreliable.
    • gamma is a beta that has been around a while and seems to work fine. Only minor fixes are added. This is what many other companies call a release.
    • If there is no suffix, it means that the version has been run for a while at many different sites with no reports of bugs other than platform-specific bugs. Only critical bug fixes are applied to the release. This is what we call a stable release.

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:

  • The mysqld server is installed in the `libexec' directory rather than in the `bin' directory.
  • The data directory is `var' rather than `data'.
  • mysql_install_db is installed in the `/usr/local/bin' directory rather than in `/usr/local/mysql/scripts'.
  • The header file and library directories are `include/mysql' and `lib/mysql' rather than `include' and `lib'.

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:

  • For each minor update, the last number in the version string is incremented. When there are major new features or minor incompatibilities with previous versions, the second number in the version string is incremented. When the file format changes, the first number is increased.
  • Stable tested releases are meant to appear about 1-2 times a year, but if small bugs are found, a release with only bug fixes will be released.
  • Working releases are meant to appear about every 1-8 weeks.
  • Binary distributions for some platforms will be made by us for major releases. Other people may make binary distributions for other systems but probably less frequently.
  • We usually make patches available as soon as we have located and fixed small bugs.
  • For non-critical but annoying bugs, we will make patches available if they are sent to us. Otherwise we will combine many of them into a larger patch.
  • If there is, by any chance, a fatal bug in a release we will make a new release as soon as possible. We would like other companies to do this, too.

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:

  • GNU gunzip to uncompress the distribution.
  • A reasonable tar to unpack the distribution. GNU tar is known to work. Sun tar is known to have problems.
  • A working ANSI C++ compiler. gcc >= 2.95.2, egcs >= 1.0.2 or egcs 2.91.66, SGI C++, and SunPro C++ are some of the compilers that are known to work. libg++ is not needed when using gcc. gcc 2.7.x has a bug that makes it impossible to compile some perfectly legal C++ files, such as `sql/sql_base.cc'. If you only have gcc 2.7.x, you must upgrade your gcc to be able to compile MySQL. gcc 2.8.1 is also known to have problems on some platforms so it should be avoided if there exists a new compiler for the platform.. gcc >= 2.95.2 is recommended when compiling MySQL Version 3.23.x.
  • A good make program. GNU make is always recommended and is sometimes required. If you have problems, we recommend trying GNU make 3.75 or newer.

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 3.23.51.
  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:

  • To compile just the MySQL client libraries and client programs and not the server, use the --without-server option: 
    shell> ./configure --without-server
    If you don't have a C++ compiler, mysql will not compile (it is the one client program that requires C++). In this case, you can remove the code in configure that tests for the C++ compiler and then run ./configure with the --without-server option. The compile step will still try to build mysql, but you can ignore any warnings about `mysql.cc'. (If make stops, try make -k to tell it to continue with the rest of the build even if errors occur.)
  • If you don't want your log files and database directories located under `/usr/local/var', use a configure command, something like one of these: 
    shell> ./configure --prefix=/usr/local/mysql
shell> ./configure --prefix=/usr/local \
           --localstatedir=/usr/local/mysql/data
    The first command changes the installation prefix so that everything is installed under `/usr/local/mysql' rather than the default of `/usr/local'. The second command preserves the default installation prefix, but overrides the default location for database directories (normally `/usr/local/var') and changes it to /usr/local/mysql/data.
  • If you are using Unix and you want the MySQL socket located somewhere other than the default location (normally in the directory `/tmp' or `/var/run') use a configure command like this: 
    shell> ./configure --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
    Note that the given file must be an absolute pathname! You can also later change the location `mysql.sock' by using the MySQL option files. See section A.4.5 How to Protect or change the MySQL socket file `/tmp/mysql.sock'.
  • If you want to compile statically linked programs (for example, to make a binary distribution, to get more speed, or to work around problems with some RedHat Linux distributions), run configure like this: 
    shell> ./configure --with-client-ldflags=-all-static \
           --with-mysqld-ldflags=-all-static
  • If you are using gcc and don't have libg++ or libstdc++ installed, you can tell configure to use gcc as your C++ compiler: 
    shell> CC=gcc CXX=gcc ./configure
    When you use gcc as your C++ compiler, it will not attempt to link in libg++ or libstdc++. Here is some common environment variables to set depending on the compiler you are using:
    gcc 2.7.2.1 CC=gcc CXX=gcc CXXFLAGS="-O3 -felide-constructors"
    egcs 1.0.3a CC=gcc CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti"
    gcc 2.95.2 CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti"
    pgcc 2.90.29 or newer CFLAGS="-O3 -mpentiumpro -mstack-align-double" CXX=gcc CXXFLAGS="-O3 -mpentiumpro -mstack-align-double -felide-constructors -fno-exceptions -fno-rtti"
    In most cases you can get a reasonably optimal MySQL binary by using the options from the above and adding the following options to the configure line: 
    --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static
    The full configure line would in other words be something like the following for all recent gcc versions: 
    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
    The binaries we provide on the MySQL Web site at http://www.mysql.com are all compiled with full optimization and should be perfect for most users. See section 2.2.6 MySQL Binaries Compiled by MySQL AB. There are some things you can tweak to make an even faster binary, but this is only for advanced users. See section 5.5.3 How Compiling and Linking Affects the Speed of MySQL. If the build fails and produces errors about your compiler or linker not being able to create the shared library `libmysqlclient.so.#' (`#' is a version number), you can work around this problem by giving the --disable-shared option to configure. In this case, configure will not build a shared libmysqlclient.so.# library.
  • You can configure MySQL not to use DEFAULT column values for non-NULL columns (that is, columns that are not allowed to be NULL). This causes INSERT statements to generate an error unless you explicitly specify values for all columns that require a non-NULL value. To suppress use of default values, run configure like this: 
    shell> CXXFLAGS=-DDONT_USE_DEFAULT_FIELDS ./configure
  • By default, MySQL uses the ISO-8859-1 (Latin1) character set. To change the default set, use the --with-charset option: 
    shell> ./configure --with-charset=CHARSET
    CHARSET may be one of big5, cp1251, cp1257, czech, danish, dec8, dos, euc_kr, gb2312, gbk, german1, hebrew, hp8, hungarian, koi8_ru, koi8_ukr, latin1, latin2, sjis, swe7, tis620, ujis, usa7, or win1251ukr. See section 4.6.1 The Character Set Used for Data and Sorting. If you want to convert characters between the server and the client, you should take a look at the SET OPTION CHARACTER SET command. See section 5.5.6 SET Syntax. Warning: If you change character sets after having created any tables, you will have to run myisamchk -r -q on every table. Your indexes may be sorted incorrectly otherwise. (This can happen if you install MySQL, create some tables, then reconfigure MySQL to use a different character set and reinstall it.) With the option --with-extra-charset=LIST you can define which additional character sets should be incompiled in the server. Here LIST is either a list of character set separated with space, complex to include all characters that can't be dynamically loaded or all to include all character sets into the binaries.
  • To configure MySQL with debugging code, use the --with-debug option: 
    shell> ./configure --with-debug
    This causes a safe memory allocator to be included that can find some errors and that provides output about what is happening. See section G.1 Debugging a MySQL server.
  • If your client programs are using threads, you need to also compile a thread-safe version of the MySQL client library with the --enable-thread-safe-client configure options. This will create a libmysqlclient_r library with which you should link your threaded applications. See section 8.4.7 How to Make a Threaded Client.
  • Options that pertain to particular systems can be found in the system-specific section of this manual. See section 2.6 Operating System Specific Notes.

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:

  • If configure is run after it already has been run, it may use information that was gathered during its previous invocation. This information is stored in `config.cache'. When configure starts up, it looks for that file and reads its contents if it exists, on the assumption that the information is still correct. That assumption is invalid when you reconfigure.
  • Each time you run configure, you must run make again to recompile. However, you may want to remove old object files from previous builds first, because they were compiled using different configuration options.

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:

  • If you get errors when compiling `sql_yacc.cc', such as the ones shown below, you have probably run out of memory or swap space: 
    Internal compiler error: program cc1plus got fatal signal 11
  or
Out of virtual memory
  or
Virtual memory exhausted
    The problem is that gcc requires huge amounts of memory to compile `sql_yacc.cc' with inline functions. Try running configure with the --with-low-memory option: 
    shell> ./configure --with-low-memory
    This option causes -fno-inline to be added to the compile line if you are using gcc and -O0 if you are using something else. You should try the --with-low-memory option even if you have so much memory and swap space that you think you can't possibly have run out. This problem has been observed to occur even on systems with generous hardware configurations, and the --with-low-memory option usually fixes it.
  • By default, configure picks c++ as the compiler name and GNU c++ links with -lg++. If you are using gcc, that behavior can cause problems during configuration such as this: 
    configure: error: installation or configuration problem:
C++ compiler cannot create executables.
    You might also observe problems during compilation related to g++, libg++, or libstdc++. One cause of these problems is that you may not have g++, or you may have g++ but not libg++, or libstdc++. Take a look at the `config.log' file. It should contain the exact reason why your c++ compiler didn't work! To work around these problems, you can use gcc as your C++ compiler. Try setting the environment variable CXX to "gcc -O3". For example: 
    shell> CXX="gcc -O3" ./configure
    This works because gcc compiles C++ sources as well as g++ does, but does not link in libg++ or libstdc++ by default. Another way to fix these problems, of course, is to install g++, libg++ and libstdc++.
  • If your compile fails with errors, such as any of the following, you must upgrade your version of make to GNU make: 
    making all in mit-pthreads
make: Fatal error in reader: Makefile, line 18:
Badly formed macro assignment
  or
make: file `Makefile' line 18: Must be a separator (:
  or
pthread.h: No such file or directory
    Solaris and FreeBSD are known to have troublesome make programs. GNU make Version 3.75 is known to work.
  • If you want to define flags to be used by your C or C++ compilers, do so by adding the flags to the CFLAGS and CXXFLAGS environment variables. You can also specify the compiler names this way using CC and CXX. For example: 
    shell> CC=gcc
shell> CFLAGS=-O3
shell> CXX=gcc
shell> CXXFLAGS=-O3
shell> export CC CFLAGS CXX CXXFLAGS
    See section 2.2.6 MySQL Binaries Compiled by MySQL AB, for a list of flag definitions that have been found to be useful on various systems.
  • If you get an error message like this, you need to upgrade your gcc compiler: 
    client/libmysql.c:273: parse error before `__attribute__'
    gcc 2.8.1 is known to work, but we recommend using gcc 2.95.2 or egcs 1.0.3a instead.
  • If you get errors such as those shown below when compiling mysqld, configure didn't correctly detect the type of the last argument to accept(), getsockname(), or getpeername(): 
    cxx: Error: mysqld.cc, line 645: In this statement, the referenced
     type of the pointer value "&length" is "unsigned long", which
     is not compatible with "int".
new_sock = accept(sock, (struct sockaddr *)&cAddr, &length);
    To fix this, edit the `config.h' file (which is generated by configure). Look for these lines: 
    /* Define as the base type of the last arg to accept */
#define SOCKET_SIZE_TYPE XXX
    Change XXX to size_t or int, depending on your operating system. (Note that you will have to do this each time you run configure, because configure regenerates `config.h'.)
  • The `sql_yacc.cc' file is generated from `sql_yacc.yy'. Normally the build process doesn't need to create `sql_yacc.cc', because MySQL comes with an already-generated copy. However, if you do need to re-create it, you might encounter this error: 
    "sql_yacc.yy", line xxx fatal: default action causes potential...
    This is a sign that your version of yacc is deficient. You probably need to install bison (the GNU version of yacc) and use that instead.
  • If you need to debug mysqld or a MySQL client, run configure with the --with-debug option, then recompile and link your clients with the new client library. See section G.2 Debugging a MySQL client.

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.

  • On most systems, you can force MIT-pthreads to be used by running configure with the --with-mit-threads option: 
    shell> ./configure --with-mit-threads
    Building in a non-source directory is not supported when using MIT-pthreads, because we want to minimize our changes to this code.
  • The checks that determine whether or not to use MIT-pthreads occur only during the part of the configuration process that deals with the server code. If you have configured the distribution using --without-server to build only the client code, clients will not know whether or not MIT-pthreads is being used and will use Unix socket connections by default. Because Unix sockets do not work under MIT-pthreads, this means you will need to use -h or --host when you run client programs.
  • When MySQL is compiled using MIT-pthreads, system locking is disabled by default for performance reasons. You can tell the server to use system locking with the --use-locking option.
  • Sometimes the pthread bind() command fails to bind to a socket without any error message (at least on Solaris). The result is that all connections to the server fail. For example: 
    shell> mysqladmin version
mysqladmin: connect to server at '' failed;
error: 'Can't connect to mysql server on localhost (146)'
    The solution to this is to kill the mysqld server and restart it. This has only happened to us when we have forced the server down and done a restart immediately.
  • With MIT-pthreads, the sleep() system call isn't interruptible with SIGINT (break). This is only noticeable when you run mysqladmin --sleep. You must wait for the sleep() call to terminate before the interrupt is served and the process stops.
  • When linking, you may receive warning messages like these (at least on Solaris); they can be ignored: 
    ld: warning: symbol `_iob' has differing sizes:
    (file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4;
file /usr/lib/libc.so value=0x140);
    /my/local/pthreads/lib/libpthread.a(findfp.o) definition taken
ld: warning: symbol `__iob' has differing sizes:
    (file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4;
file /usr/lib/libc.so value=0x140);
    /my/local/pthreads/lib/libpthread.a(findfp.o) definition taken
  • Some other warnings also can be ignored: 
    implicit declaration of function `int strtoll(...)'
implicit declaration of function `int strtoul(...)'
  • We haven't gotten readline to work with MIT-pthreads. (This isn't needed, but may be interesting for someone.)

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:
    • You may want to edit mysql_install_db before running it, to change the initial privileges that are installed into the grant tables. This is useful if you want to install MySQL on a lot of machines with the same privileges. In this case you probably should need only to add a few extra INSERT statements to the mysql.user and mysql.db tables!
    • If you want to change things in the grant tables after installing them, you can run mysql_install_db, then use mysql -u root mysql to connect to the grant tables as the MySQL root user and issue SQL statements to modify the grant tables directly.
    • It is possible to re-create the grant tables completely after they have already been created. You might want to do this if you've already installed the tables but then want to re-create them after editing mysql_install_db.
    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:

  • By invoking mysql.server. This script is used primarily at system startup and shutdown, and is described more fully in section 2.4.3 Starting and Stopping MySQL Automatically.
  • By invoking safe_mysqld, which tries to determine the proper options for mysqld and then runs it with those options. See section 4.7.2 safe_mysqld, the wrapper around mysqld.
  • On NT you should install mysqld as a service as follows: 
    bin\mysqld-nt --install               # Install MySQL as a service
    You can now start/stop mysqld as follows: 
    NET START mysql
NET STOP mysql
    Note that in this case you can't use any other options for mysqld! You can remove the service as follows: 
    bin\mysqld-nt --remove                # remove MySQL as a service
  • By invoking mysqld directly.

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.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:

  • All tables that uses the tis620 character set must be fixed with myisamchk -r or REPAIR TABLE.
  • If you do a DROP DATABASE on a symbolic linked database, both the link and the original database is deleted. (This didn't happen in 3.22 because configure didn't detect the readlink system call).
  • OPTIMIZE TABLE now only works for MyISAM tables. For other table types, you can use ALTER TABLE to optimize the table. During OPTIMIZE TABLE the table is now locked from other threads.
  • The MySQL client mysql is now by default started with the option --no-named-commands (-g). This option can be disabled with --enable-named-commands (-G). This may cause incompatibility problems in some cases, for example in SQL scripts that use named commands without a semicolon! Long format commands still work from the first line.
  • If you are using the german character sort order, you must repair all your tables with isamchk -r, as we have made some changes in the sort order!
  • The default return type of IF will now depend on both arguments and not only the first argument.
  • AUTO_INCREMENT will not work with negative numbers. The reason for this is that negative numbers caused problems when wrapping from -1 to 0. AUTO_INCREMENT is now for MyISAM tables handled at a lower level and is much faster than before. For MyISAM tables old numbers are also not reused anymore, even if you delete some rows from the table.
  • CASE, DELAYED, ELSE, END, FULLTEXT, INNER, RIGHT, THEN and WHEN are now reserved words.
  • FLOAT(X) is now a true floating-point type and not a value with a fixed number of decimals.
  • When declaring DECIMAL(length,dec) the length argument no longer includes a place for the sign or the decimal point.
  • A TIME string must now be of one of the following formats: [[[DAYS] [H]H:]MM:]SS[.fraction] or [[[[[H]H]H]H]MM]SS[.fraction]
  • LIKE now compares strings using the same character comparison rules as '='. If you require the old behavior, you can compile MySQL with the CXXFLAGS=-DLIKE_CMP_TOUPPER flag.
  • REGEXP is now case insensitive for normal (not binary) strings.
  • When you check/repair tables you should use CHECK TABLE or myisamchk for MyISAM tables (.MYI) and isamchk for ISAM (.ISM) tables.
  • If you want your mysqldump files to be compatible between MySQL Version 3.22 and Version 3.23, you should not use the --opt or --full option to mysqldump.
  • Check all your calls to DATE_FORMAT() to make sure there is a `%' before each format character. (Later MySQL Version 3.22 did allow this syntax.)
  • mysql_fetch_fields_direct is now a function (it was a macro) and it returns a pointer to a MYSQL_FIELD instead of a MYSQL_FIELD.
  • mysql_num_fields() can no longer be used on a MYSQL* object (it's now a function that takes MYSQL_RES* as an argument. You should now use mysql_field_count() instead.
  • In MySQL Version 3.22, the output of SELECT DISTINCT ... was almost always sorted. In Version 3.23, you must use GROUP BY or ORDER BY to obtain sorted output.
  • SUM() now returns NULL, instead of 0, if there is no matching rows. This is according to ANSI SQL.
  • An AND or OR with NULL values will now return NULL instead of 0. This mostly affects queries that use NOT on an AND/OR expression as NOT NULL = NULL. LPAD() and RPAD() will shorten the result string if it's longer than the length argument.

2.5.2 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.3 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:

  • All client code must be recompiled. If you are using ODBC, you must get the new MyODBC 2.x driver.
  • The script scripts/add_long_password must be run to convert the Password field in the mysql.user table to CHAR(16).
  • All passwords must be reassigned in the mysql.user table (to get 62-bit rather than 31-bit passwords).
  • The table format hasn't changed, so you don't have to convert any tables.

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:

  • HAVING must now be specified before any ORDER BY clause.
  • The parameters to LOCATE() have been swapped.
  • There are some new reserved words. The most notable are DATE, TIME, and TIMESTAMP.

2.5.4 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 t