O'Reilly logo

MySQL Reference Manual by Kaj Arno, David Axmark, Michael Widenius

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 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 a new compiler exists 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 the -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-constructors 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 \

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

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 &
shell> /usr/local/mysql/bin/mysqld_safe --user=mysql &
if you are running MySQL 4.x.

If you want to have support for InnoDB tables, you should edit the /etc/my.cnf file and remove the # character before the parameter that starts with innodb_.... See Section 4.1.2, and Section 7.5.2.

If you start from a source RPM, 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 these steps, then proceed to Section 2.4, for post-installation initialisation 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.

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

    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.

  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 cannot 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. See Section 2.3.3, discusses some of the more useful options.

    If configure fails, and you are going to send mail to 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

    If the compile fails, see Section 2.3.5, 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 2.7.

  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.

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

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

If that command fails immediately with mysqld daemon ended, 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.

Now proceed to Section 2.4.

Applying Patches

Sometimes patches appear on the mailing list or are placed in the patches area of the MySQL web site (http://www.mysql.com/Downloads/Patches/).

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

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 Appendix E. For a list of options supported by configure, run this command:

shell> ./configure --help

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

  • 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 want to get an embedded MySQL library (libmysqld.a) you should use the --with-embedded-server option.

  • 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 \

    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. After you have compiled MySQL, you can change these options with option files. See Section 4.1.2.

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

  • 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 \
  • 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 are some common environment variables to set depending on the compiler you are using:


    Recommended options


    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 preceding table and adding the following options to the configure line:

    --prefix=/usr/local/mysql --enable-assembler \

    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 \

    The binaries we provide on the MySQL web site at http://www.mysql.com/ are all compiled with full optimisation and should be perfect for most users. See Section 2.2.6. 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.

    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:

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

    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.

    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 sets separated with spaces, 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 D.1.

  • 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.8.

  • Options that pertain to particular systems can be found in the system-specific section of this manual. See Section 2.6.

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, first go to the directory you want to work from, and then 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

    In the preceding examples the source tree will be set up in the mysql/ or mysql-4.0/ subdirectory of your current directory.

    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 2.52, automake 1.4, libtool, and m4 to run the next set of commands.

    automake (1.5) doesn’t yet work.

    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 by 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.1.2.

  7. If you have gotten to the make stage and the distribution does not compile, please report it to . 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 that 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 email to . 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.

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 for warnings that may occur when using MIT-pthreads. For other problems, check the following list.

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 following list describes some of the problems when compiling MySQL that have been found to occur most often:

  • If you get errors when compiling sql_yacc.cc, such as the ones shown here, you have probably run out of memory or swap space:

    Internal compiler error: program cc1plus got fatal signal 11
    Out of virtual memory
    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
    make: file `Makefile' line 18: Must be a separator (:
    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, 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 here 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 */

    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 D.2.

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.

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.

  • 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 minimise our changes to this code.

  • The checks that determine whether 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 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.)

Windows Source Distribution

You will need the following:

  • VC++ 6.0 compiler (updated with 4 or 5 SP and Pre-processor package) The Pre-processor package is necessary for the macro assembler. More details at: http://msdn.microsoft.com/vstudio/sp/vs6sp5/faq.asp.

  • The MySQL source distribution for Windows, which can be downloaded from http://www.mysql.com/downloads/.

Building MySQL

  1. Create a work directory (e.g., workdir).

  2. Unpack the source distribution in the aforementioned directory.

  3. Start the VC++ 6.0 compiler.

  4. In the File menu, select Open Workspace.

  5. Open the mysql.dsw workspace you find on the work directory.

  6. From the Build menu, select the Set Active Configuration menu.

  7. Click over the screen selecting mysqld - Win32 Debug and click OK.

  8. Press F7 to begin the build of the debug server, libs, and some client applications.

  9. When the compilation finishes, copy the libs and the executables to a separate directory.

  10. Compile the release versions that you want, in the same way.

  11. Create the directory for the MySQL stuff: e.g., c:\mysql

  12. From the workdir directory copy for the c:\mysql directory the following directories:

    • Data

    • Docs

    • Share

  13. Create the directory c:\mysql\bin and copy all the servers and clients that you compiled previously.

  14. If you want, also create the lib directory and copy the libs that you compiled previously.

  15. Do a clean using Visual Studio.

Set up and start the server in the same way as for the binary Windows distribution. See Section

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required