MySQL数据库

开发平台：

Visual C++

INSTALL-SOURCE：源码内容

'dummy' `c_asm.h' file with:
touch include/c_asm.h
CC=gcc CFLAGS=-I./include
CXX=gcc CXXFLAGS=-O3
./configure --prefix=/usr/local/mysql
Note that the following problems with the `ld' program can be fixed by
downloading the latest DEC (Compaq) patch kit from:
`http://ftp.support.compaq.com/public/unix/'.
On OSF1 V4.0D and compiler "DEC C V5.6-071 on Digital Unix V4.0 (Rev.
878)" the compiler had some strange behavior (undefined `asm' symbols).
`/bin/ld' also appears to be broken (problems with `_exit undefined'
errors occuring while linking `mysqld'). On this system, we have
managed to compile *MySQL* with the following `configure' line, after
replacing `/bin/ld' with the version from OSF 4.0C:
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
With the Digital compiler "C++ V6.1-029", the following should work:
CC=cc -pthread
CFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all -arch host
CXX=cxx -pthread
CXXFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all -arch host -noexceptions -nortti
export CC CFLAGS CXX CXXFLAGS
./configure --prefix=/usr/mysql/mysql --with-mysqld-ldflags=-all-static --disable-shared --with-named-thread-libs="-lmach -lexc -lc"
In some versions of OSF1, the `alloca()' function is broken. Fix this
by removing the line in `config.h' that defines `'HAVE_ALLOCA''.
The `alloca()' function also may have an incorrect prototype in
`/usr/include/alloca.h'. This warning resulting from this can be
ignored.
`configure' will use the following thread libraries automatically:
`--with-named-thread-libs="-lpthread -lmach -lexc -lc"'.
When using `gcc', you can also try running `configure' like this:
shell> CFLAGS=-D_PTHREAD_USE_D4 CXX=gcc CXXFLAGS=-O3 ./configure ....
If you have problems with signals (*MySQL* dies unexpectedly under high
load), you may have found an OS bug with threads and signals. In this
case you can tell *MySQL* not to use signals by configuring with:
shell> CFLAGS=-DDONT_USE_THR_ALARM
CXXFLAGS=-DDONT_USE_THR_ALARM
./configure ...
This doesn't affect the performance of *MySQL*, but has the side effect
that you can't kill clients that are "sleeping" on a connection with
`mysqladmin kill' or `mysqladmin shutdown'. Instead, the client will
die when it issues its next command.
With `gcc' 2.95.2, you will probably run into the following compile
error:
sql_acl.cc:1456: Internal compiler error in `scan_region', at except.c:2566
Please submit a full bug report.
To fix this you should change to the `sql' directory and do a "cut and
paste" of the last `gcc' line, but change `-O3' to `-O0' (or add `-O0'
immediately after `gcc' if you don't have any `-O' option on your
compile line.) After this is done you can just change back to the
top-level directly and run `make' again.
SGI-Irix Notes
--------------
If you are using Irix Version 6.5.3 or newer `mysqld' will only be able
to create threads if you run it as a user with `CAP_SCHED_MGT'
privileges (like `root') or give the `mysqld' server this privilege
with the following shell command:
shell> chcap "CAP_SCHED_MGT+epi" /opt/mysql/libexec/mysqld
You may have to undefine some things in `config.h' after running
`configure' and before compiling.
In some Irix implementations, the `alloca()' function is broken. If the
`mysqld' server dies on some `SELECT' statements, remove the lines from
`config.h' that define `HAVE_ALLOC' and `HAVE_ALLOCA_H'. If
`mysqladmin create' doesn't work, remove the line from `config.h' that
defines `HAVE_READDIR_R'. You may have to remove the `HAVE_TERM_H' line
as well.
SGI recommends that you install all of the patches on this page as a
set:
http://support.sgi.com/surfzone/patches/patchset/6.2_indigo.rps.html
At the very minimum, you should install the latest kernel rollup, the
latest `rld' rollup, and the latest `libc' rollup.
You definately need all the POSIX patches on this page, for pthreads
support:
http://support.sgi.com/surfzone/patches/patchset/6.2_posix.rps.html
If you get the something like the following error when compiling
`mysql.cc':
"/usr/include/curses.h", line 82: error(1084): invalid combination of type
then type the following in the top-level directory of your *MySQL*
source tree:
shell> extra/replace bool curses_bool < /usr/include/curses.h > include/curses.h
shell> make
There have also been reports of scheduling problems. If only one
thread is running, things go slow. Avoid this by starting another
client. This may lead to a 2-to-10-fold increase in execution speed
thereafter for the other thread. This is a poorly understood problem
with Irix threads; you may have to improvise to find solutions until
this can be fixed.
If you are compiling with `gcc', you can use the following `configure'
command:
CC=gcc CXX=gcc CXXFLAGS=-O3
./configure --prefix=/usr/local/mysql --with-thread-safe-client --with-named-thread-libs=-lpthread
FreeBSD Notes
-------------
FreeBSD 3.x is recommended for running *MySQL* since the thread package
is much more integrated.
The easiest and therefor the preferred way to install is to use the
mysql-server and mysql-client ports available on
`http://www.freebsd.org'.
Using these gives you:
* A working *MySQL* with all optimizations known to work on your
version of FreeBSD enabled.
* Automatic configuration and build.
* Startup scripts installed in /usr/local/etc/rc.d.
* Ability to see which files that are installed with pkg_info -L.
And to remove them all with pkg_delete if you no longer want
*MySQL* on that machine.
It is recomended you use MIT-pthreads on FreeBSD 2.x and native threads
on Versions 3 and up. It is possible to run with native threads on
some late 2.2.x versions but you may encounter problems shutting down
mysqld.
The *MYSQL* Makefiles require GNU make (`gmake') to work. If you want
to compile *MYSQL* you need to install GNU make first.
Be sure to have your name resolver setup correct. Otherwise you may
experience resolver delays or failures when connecting to mysqld.
Make sure that the `localhost' entry in the `/etc/hosts' file is
correct (otherwise you will have problems connecting to the database).
The `/etc/hosts' file should start with a line:
127.0.0.1 localhost localhost.your.domain
If you notice that `configure' will use MIT-pthreads, you should read
the MIT-pthreads notes. *Note MIT-pthreads::.
If you get an error from `make install' that it can't find
`/usr/include/pthreads', `configure' didn't detect that you need
MIT-pthreads. This is fixed by executing these commands:
shell> rm config.cache
shell> ./configure --with-mit-threads
FreeBSD is also known to have a very low default file handle limit.
*Note Not enough file handles::. Uncomment the ulimit -n section in
safe_mysqld or raise the limits for the mysqld user in /etc/login.conf
(and rebuild it witg cap_mkdb /etc/login.conf.) Also be sure you set the
appropriate class for this user in the password file if you are not
using the default (use: chpass mysqld-user-name). *Note safe_mysqld::.
If you get problems with the current date in *MySQL*, setting the `TZ'
variable will probably help. *Note Environment variables::.
To get a secure and stable system you should only use FreeBSD kernels
that are marked `-STABLE'
NetBSD notes
------------
To compile on NetBSD you need GNU `make'. Otherwise the compile will
crash when `make' tries to run `lint' on C++ files.
OpenBSD Notes
-------------
* Menu:
* OpenBSD 2.5:: OpenBSD 2.5 Notes
* OpenBSD 2.8:: OpenBSD 2.8 Notes
OpenBSD 2.5 Notes
.................
On OpenBSD Version 2.5, you can compile *MySQL* with native threads
with the following options:
CFLAGS=-pthread CXXFLAGS=-pthread ./configure --with-mit-threads=no
OpenBSD 2.8 Notes
.................
Our users have reported that OpenBSD 2.8 has a threading bug which
causes problems with MySQL. The OpenBSD Developers have fixed the
problem, but as of January 25th, 2001, it's only available in the
"-current" branch. The symptoms of this threading bug are: slow
response, high load, high cpu usage, and crashes.
BSD/OS Notes
------------
* Menu:
* BSDI2:: BSD/OS 2.x notes
* BSDI3:: BSD/OS 3.x notes
* BSDI4:: BSD/OS 4.x notes
BSD/OS Version 2.x Notes
........................
If you get the following error when compiling *MySQL*, your `ulimit'
value for virtual memory is too low:
item_func.h: In method `Item_func_ge::Item_func_ge(const Item_func_ge &)':
item_func.h:28: virtual memory exhausted
make[2]: *** [item_func.o] Error 1
Try using `ulimit -v 80000' and run `make' again. If this doesn't work
and you are using `bash', try switching to `csh' or `sh'; some BSDI
users have reported problems with `bash' and `ulimit'.
If you are using `gcc', you may also use have to use the
`--with-low-memory' flag for `configure' to be able to compile
`sql_yacc.cc'.
If you get problems with the current date in *MySQL*, setting the `TZ'
variable will probably help. *Note Environment variables::.
BSD/OS Version 3.x Notes
........................
Upgrade to BSD/OS Version 3.1. If that is not possible, install
BSDIpatch M300-038.
Use the following command when configuring *MySQL*:
shell> env CXX=shlicc++ CC=shlicc2
./configure
--prefix=/usr/local/mysql
--localstatedir=/var/mysql
--without-perl
--with-unix-socket-path=/var/mysql/mysql.sock
The following is also known to work:
shell> env CC=gcc CXX=gcc CXXFLAGS=-O3
./configure
--prefix=/usr/local/mysql
--with-unix-socket-path=/var/mysql/mysql.sock
You can change the directory locations if you wish, or just use the
defaults by not specifying any locations.
If you have problems with performance under heavy load, try using the
`--skip-thread-priority' option to `mysqld'! This will run all threads
with the same priority; on BSDI Version 3.1, this gives better
performance (at least until BSDI fixes their thread scheduler).
If you get the error `virtual memory exhausted' while compiling, you
should try using `ulimit -v 80000' and run `make' again. If this
doesn't work and you are using `bash', try switching to `csh' or `sh';
some BSDI users have reported problems with `bash' and `ulimit'.
BSD/OS Version 4.x Notes
........................
BSDI Version 4.x has some thread-related bugs. If you want to use
*MySQL* on this, you should install all thread-related patches. At least
M400-023 should be installed.
On some BSDI Version 4.x systems, you may get problems with shared
libraries. The symptom is that you can't execute any client programs,
for example, `mysqladmin'. In this case you need to reconfigure not to
use shared libraries with the `--disable-shared' option to configure.
Some customers have had problems on BSDI 4.0.1 that the `mysqld' binary
after a while can't open tables. This is because some library/system
related bug causes `mysqld' to change current directory without asking
for this!
The fix is to either upgrade to 3.23.34 or after running `configure'
remove the line `#define HAVE_REALPATH' from `config.h' before running
make.
Note that the above means that you can't symbolic link a database
directories to another database directory or symbolic link a table to
another database on BSDI! (Making a symbolic link to another disk is
ok).
SCO Notes
---------
The current port is tested only on a "sco3.2v5.0.4" and "sco3.2v5.0.5"
system. There has also been a lot of progress on a port to "sco
3.2v4.2".
For the moment the recommended compiler on OpenServer is gcc 2.95.2.
With this you should be able to compile *MySQL* with just:
CC=gcc CXX=gcc ./configure ... (options)
1. For OpenServer 5.0.X you need to use GDS in Skunkware 95 (95q4c).
This is necessary because GNU `gcc' 2.7.2 in Skunkware 97 does not
have GNU `as'. You can also use `egcs' 1.1.2 or newer
`http://www.egcs.com/'. If you are using `egcs' 1.1.2 you have to
execute the following command:
shell> cp -p /usr/include/pthread/stdtypes.h /usr/local/lib/gcc-lib/i386-pc-sco3.2v5.0.5/egcs-2.91.66/include/pthread/
2. You need the port of GCC 2.5.x for this product and the Development
system. They are required on this version of SCO Unix. You cannot
just use the GCC Dev system.
3. You should get the FSU Pthreads package and install it first.
This can be found at
`http://www.cs.wustl.edu/~schmidt/ACE_wrappers/FSU-threads.tar.gz'.
You can also get a precompiled package from
`http://www.mysql.com/Downloads/SCO/FSU-threads-3.5c.tar.gz'.
4. FSU Pthreads can be compiled with SCO Unix 4.2 with tcpip. Or
OpenServer 3.0 or Open Desktop 3.0 (OS 3.0 ODT 3.0), with the SCO
Development System installed using a good port of GCC 2.5.x ODT or
OS 3.0 you will need a good port of GCC 2.5.x There are a lot of
problems without a good port. The port for this product requires
the SCO Unix Development system. Without it, you are missing the
libraries and the linker that is needed.
5. To build FSU Pthreads on your system, do the following:
a. Run `./configure' in the `threads/src' directory and select
the SCO OpenServer option. This command copies
`Makefile.SCO5' to `Makefile'.
b. Run `make'.
c. To install in the default `/usr/include' directory, login as
root, then `cd' to the `thread/src' directory, and run `make
install'.
6. Remember to use GNU `make' when making *MySQL*.
7. If you don't start `safe_mysqld' as root, you probably will get
only the default 110 open files per process. `mysqld' will write
a note about this in the log file.
8. With SCO 3.2V5.0.5, you should use FSU Pthreads version 3.5c or
newer. You should also use gcc 2.95.2 or newer!
The following `configure' command should work:
shell> ./configure --prefix=/usr/local/mysql --disable-shared
9. With SCO 3.2V4.2, you should use FSU Pthreads version 3.5c or
newer. The following `configure' command should work:
shell> CFLAGS="-D_XOPEN_XPG4" CXX=gcc CXXFLAGS="-D_XOPEN_XPG4"
./configure
--with-debug --prefix=/usr/local/mysql
--with-named-thread-libs="-lgthreads -lsocket -lgen -lgthreads"
--with-named-curses-libs="-lcurses"
You may get some problems with some include files. In this case,
you can find new SCO-specific include files at
`http://www.mysql.com/Downloads/SCO/SCO-3.2v4.2-includes.tar.gz'.
You should unpack this file in the `include' directory of your
*MySQL* source tree.
SCO development notes:
* *MySQL* should automatically detect FSU Pthreads and link `mysqld'
with `-lgthreads -lsocket -lgthreads'.
* The SCO development libraries are re-entrant in FSU Pthreads. SCO
claims that its libraries' functions are re-entrant, so they must
be reentrant with FSU Pthreads. FSU Pthreads on OpenServer tries
to use the SCO scheme to make re-entrant libraries.
* FSU Pthreads (at least the version at `http://www.mysql.com/')
comes linked with GNU `malloc'. If you encounter problems with
memory usage, make sure that `gmalloc.o' is included in
`libgthreads.a' and `libgthreads.so'.
* In FSU Pthreads, the following system calls are pthreads-aware:
`read()', `write()', `getmsg()', `connect()', `accept()',
`select()', and `wait()'.
If you want to install DBI on SCO, you have to edit the `Makefile' in
DBI-xxx and each subdirectory.
Note that the following assumes gcc 2.95.2 or newer:
OLD: NEW:
CC = cc CC = gcc
CCCDLFLAGS = -KPIC -W1,-Bexport CCCDLFLAGS = -fpic
CCDLFLAGS = -wl,-Bexport CCDLFLAGS =
LD = ld LD = gcc -G -fpic
LDDLFLAGS = -G -L/usr/local/lib LDDLFLAGS = -L/usr/local/lib
LDFLAGS = -belf -L/usr/local/lib LDFLAGS = -L/usr/local/lib
LD = ld LD = gcc -G -fpic
OPTIMISE = -Od OPTIMISE = -O1
OLD:
CCCFLAGS = -belf -dy -w0 -U M_XENIX -DPERL_SCO5 -I/usr/local/include
NEW:
CCFLAGS = -U M_XENIX -DPERL_SCO5 -I/usr/local/include
This is because the Perl dynaloader will not load the `DBI' modules if
they were compiled with `icc' or `cc'.
Perl works best when compiled with `cc'.
SCO Unixware Version 7.0 Notes
------------------------------
You must use a version of *MySQL* at least as recent as Version 3.22.13
because that version fixes some portability problems under Unixware.
We have been able to compile *MySQL* with the following `configure'
command on Unixware Version 7.0.1:
CC=cc CXX=CC ./configure --prefix=/usr/local/mysql
If you want to use `gcc', you must use `gcc' 2.95.2 or newer.
IBM-AIX notes
-------------
Automatic detection of `xlC' is missing from Autoconf, so a `configure'
command something like this is needed when compiling *MySQL* (The
example uses the IBM compiler):
export CC="xlc_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192 "
export CXX="xlC_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192"
export CFLAGS="-I /usr/local/include"
export LDLFAGS="-L /usr/local/lib"
export CPPFLAGS=$CFLAGS
export CXXFLAGS=$CFLAGS
./configure --prefix=/usr/local
--localstatedir=/var/mysql
--sysconfdir=/etc/mysql
--sbindir='/usr/local/bin'
--libexecdir='/usr/local/bin'
--enable-thread-safe-client
--enable-large-files
Above are the options used to compile the *MySQL* distribution that can
be found at www-frec.bull.com (http://www-frec.bull.com/).
If you change the `-O3' to `-O2' in the above configure line, you must
also remove the `-qstrict' option (this is a limitation in the IBM C
compiler).
If you are using `gcc' or `egcs' to compile *MySQL*, you *MUST* use the
`-fno-exceptions' flag, as the exception handling in `gcc'/`egcs' is
not thread safe! (This is tested with `egcs' 1.1.). There are also
some known problems with IBM's assembler, which may cause it to
generate bad code when used with gcc.
We recommend the following `configure' line with `egcs' and `gcc 2.95'
on AIX:
CC="gcc -pipe -mcpu=power2 -Wa,-many"
CXX="gcc -pipe -mcpu=power2 -Wa,-many"
CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti"
./configure --prefix=/usr/local/mysql --with-low-memory
The `-Wa,-many' is necessary for the compile to be successful. IBM is
aware of this problem but is in to hurry to fix it because of the
workaround available. We don't know if the `-fno-exceptions' is
required with `gcc 2.95', but as *MySQL* doesn't use exceptions and the
above option generates faster code, we recommend that you should always
use this option with `egcs / gcc'.
If you have problems with signals (*MySQL* dies unexpectedly under high
load) you may have found an OS bug with threads and signals. In this
case you can tell *MySQL* not to use signals by configuring with:
shell> CFLAGS=-DDONT_USE_THR_ALARM CXX=gcc
CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti -DDONT_USE_THR_ALARM"
./configure --prefix=/usr/local/mysql --with-debug --with-low-memory
This doesn't affect the performance of *MySQL*, but has the side effect
that you can't kill clients that are "sleeping" on a connection with
`mysqladmin kill' or `mysqladmin shutdown'. Instead, the client will
die when it issues its next command.
On some versions of AIX, linking with `libbind.a' makes `getservbyname'
core dump. This is an AIX bug and should be reported to IBM.
HP-UX Version 10.20 Notes
-------------------------
There are a couple of small problems when compiling *MySQL* on HP-UX.
We recommend that you use `gcc' instead of the HP-UX native compiler,
because `gcc' produces better code!
We recommend using gcc 2.95 on HP-UX. Don't use high optimization
flags (like -O6) as this may not be safe on HP-UX.
Note that MIT-pthreads can't be compiled with the HP-UX compiler
because it can't compile `.S' (assembler) files.
The following configure line should work:
CFLAGS="-DHPUX -I/opt/dce/include" CXXFLAGS="-DHPUX -I/opt/dce/include -felide-constructors -fno-exceptions -fno-rtti" CXX=gcc ./configure --with-pthread --with-named-thread-libs='-ldce' --prefix=/usr/local/mysql --disable-shared
If you are compiling `gcc' 2.95 yourself, you should NOT link it with
the DCE libraries (`libdce.a' or `libcma.a') if you want to compile
*MySQL* with MIT-pthreads. If you mix the DCE and MIT-pthreads
packages you will get a `mysqld' to which you cannot connect. Remove
the DCE libraries while you compile `gcc' 2.95!
HP-UX Version 11.x Notes
------------------------
For HPUX Version 11.x we recommend *MySQL* Version 3.23.15 or later.
Because of some critical bugs in the standard HPUX libraries, one should
install the following patches before trying to run MySQL on HPUX 11.0:
PHKL_22840 Streams cumulative
PHNE_22397 ARPA cumulative
This will solve a problem that one gets `EWOULDBLOCK' from `recv()' and
`EBADF' from `accept()' in threaded applications.
If you are using `gcc' 2.95.1 on a unpatched HPUX 11.x system, you will
get the error:
In file included from /usr/include/unistd.h:11,
from ../include/global.h:125,
from mysql_priv.h:15,
from item.cc:19:
/usr/include/sys/unistd.h:184: declaration of C function ...
/usr/include/sys/pthread.h:440: previous declaration ...
In file included from item.h:306,
from mysql_priv.h:158,
from item.cc:19:
The problem is that HP-UX doesn't define `pthreads_atfork()'
consistently. It has conflicting prototypes in
`/usr/include/sys/unistd.h':184 and `/usr/include/sys/pthread.h':440 (I
post the details below).
One solution is to copy `/usr/include/sys/unistd.h' into
`mysql/include' and edit `unistd.h' and change it to match the
definition in `pthread.h'. Here's the diff:
183,184c183,184
< extern int pthread_atfork(void (*prepare)(), void (*parent)(),
< void (*child)());
---
> extern int pthread_atfork(void (*prepare)(void), void (*parent)(void),
> void (*child)(void));
After this, the following configure line should work:
CFLAGS="-fomit-frame-pointer -O6 -fpic" CXX=gcc CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti -O6" ./configure --prefix=/usr/local/mysql --disable-shared
Here is some information that a HPUX Version 11.x user sent us about
compiling *MySQL* with HPUX:x compiler:
Environment:
proper compilers.
setenv CC cc
setenv CXX aCC
flags
setenv CFLAGS -D_REENTRANT
setenv CXXFLAGS -D_REENTRANT
setenv CPPFLAGS -D_REENTRANT
% aCC -V
aCC: HP ANSI C++ B3910B X.03.14.06
% cc -V /tmp/empty.c
cpp.ansi: HP92453-01 A.11.02.00 HP C Preprocessor (ANSI)
ccom: HP92453-01 A.11.01.00 HP C Compiler
cc: "/tmp/empty.c", line 1: warning 501: Empty source file.
configuration:
./configure --with-pthread
--prefix=/source-control/mysql
--with-named-thread-libs=-lpthread
--with-low-memory
added '#define _CTYPE_INCLUDED' to include/m_ctype.h. This
symbol is the one defined in HP's /usr/include/ctype.h:
/* Don't include std ctype.h when this is included */
#define _CTYPE_H
#define __CTYPE_INCLUDED
#define _CTYPE_INCLUDED
#define _CTYPE_USING /* Don't put names in global namespace. */
* I had to use the compile-time flag `-D_REENTRANT' to get the
compiler to recognize the prototype for `localtime_r'.
Alternatively I could have supplied the prototype for
`localtime_r'. But I wanted to catch other bugs without needing to
run into them. I wasn't sure where I needed it, so I added it to
all flags.
* The optimization flags used by *MySQL* (-O3) are not recognized by
HP's compilers. I did not change the flags.
Mac OS X Notes
--------------
* Menu:
* Mac OS X Public Data::
* Mac OS X Server::
Mac OS X Public beta
....................
*MySQL* should work without any probelms on Mac OS X public beta.
(Darwin); You don't need the pthread patches for this os!
Mac OS X Server
...............
Before trying to configure *MySQL* on Mac OS X server you must first
first install the pthread package from
`http://www.prnet.de/RegEx/mysql.html'. Note that this is not neeaded
Our binary for Mac OS X is compiled on Rhapsody 5.5 with the following
configure line:
CC=gcc CFLAGS="-O2 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O2 -fomit-frame-pointer" ./configure --prefix=/usr/local/mysql "--with-comment=Official MySQL binary" --with-extra-charsets=complex --disable-shared
You might want to also add aliases to your shell's resource file to
access `mysql' and `mysqladmin' from the command line:
alias mysql '/usr/local/mysql/bin/mysql'
alias mysqladmin '/usr/local/mysql/bin/mysqladmin'
BeOS Notes
----------
We are really interested in getting *MySQL* to work on BeOS, but
unfortunately we don't have any person who knows BeOS or has time to do
a port.
We are interested in finding someone to do a port, and we will help them
with any techincal questions they may have while doing the port.
We have previously talked with some BeOS developers that have said that
*MySQL* is 80% ported to BeOS, but we haven't heard from these in a
while.
Windows Notes
=============
This section describes installation and use of *MySQL* on Windows. This
is also described in the `README' file that comes with the *MySQL*
Windows distribution.
* Menu:
* Windows installation:: Installing *MySQL* on Windows
* Win95 start:: Starting *MySQL* on Win95 / Win98
* NT start:: Starting *MySQL* on NT / Win2000
* Windows running:: Running *MySQL* on Windows
* Windows and SSH:: Connecting to a remote *MySQL* from Windows with SSH
* Windows symbolic links:: Splitting data across different disks under Win32
* Windows compiling:: Compiling MySQL clients on Windows.
* Windows and BDB tables.:: Windows and BDB Tables
* Windows vs Unix:: *MySQL*-Windows compared to Unix *MySQL*
Installing MySQL on Windows
---------------------------
If you don't have a copy of the MySQL distribution, you should first
download one from `http://www.mysql.com/'.
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', then move the installation to where you want it. If you do
move *MySQL*, you must tell `mysqld' where everything is by supplying
options to `mysqld'. Use `C:mysqlbinmysqld --help' to display all
options! For example, if you have moved the *MySQL* distribution to
`D:programsmysql', you must start `mysqld' with:
`D:programsmysqlbinmysqld --basedir D:programsmysql'
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
`mysqlmy-xxxxx.cnf' to `C:my.cnf' and edit this to suit your setup.
Note that you should specify all paths with `/' instead of `'. If you
use `', you need to specify this twice, as `' is the escape character
in *MySQL*. *Note Option files::.
Starting MySQL on Windows 95 or Windows 98
------------------------------------------
*MySQL* uses TCP/IP to connect a client to a server. (This will allow
any machine on your network to connect to your *MySQL* server.) Because
of this, you must install TCP/IP on your machine before starting
*MySQL*. You can find TCP/IP on your Windows CD-ROM.
Note that if you are using an old Win95 release (for example OSR2), it's
likely that you have an old Winsock package! *MySQL* requires Winsock
2! You can get the newest Winsock from Microsoft
(http://www.microsoft.com). Win98 has as default the new Winsock 2
library, so the above doesn't apply for Win98.
There are 2 different *MySQL* servers you can use:
`mysqld' Compiled with full debugging and automatic memory
allocation checking
`mysqld-opt' Optimized for a Pentium processor.
Both of the above should work on any Intel processor >= i386.
To start the `mysqld' server, you should start an MS-DOS window and
type:
C:mysqlbinmysqld
This will start `mysqld' in the background without a window.
You can kill the *MySQL* server by executing:
C:mysqlbinmysqladmin -u root shutdown
Note that Win95/Win98 don't support creation of named pipes. On
Win95/Win98, you can only use named pipes to connect to a remote
*MySQL* running on an NT server.
If `mysqld' doesn't start, please check whether or not the
`mysqlmysql.err' file contains any reason for this. You can also try
to start it with `mysqld --standalone'; In this case you may get some
useful information on the screen that may help solve this.
The last option is to start `mysqld' with `--debug'. In this case
`mysqld' will write a log file in `mysqld.trace' that should contain
the reason why `mysqld' doesn't start. If you make a bug report about
this, please only send the lines to the mailing list where something
seems to go wrong!
Starting MySQL on NT or Windows 2000
------------------------------------
The Win95/Win98 section also applies to *MySQL* on NT/Win2000, with the
following differences:
To get *MySQL* to work with TCP/IP on NT, you must install service pack
3 (or newer)!
Note that everything in the following that applies for NT also applies
for Win2000!
For NT/Win2000, the server name is `mysqld-nt'. Normally you should
install *MySQL* as a service on NT/Win2000:
C:mysqlbinmysqld-nt --install
(You could use the `mysqld' or `mysqld-opt' servers on NT, but those
cannot be started as a service or use named pipes.)
You can start and stop the *MySQL* service with:
NET START mysql
NET STOP mysql
Note that in this case you can't use any other options for `mysqld-nt'!
You can also run `mysqld-nt' as a stand-alone program on NT if you need
to start `mysqld-nt' with any options! If you start `mysqld-nt'
without options on NT, `mysqld-nt' tries to starts itself as a service
with the default service options. If you have stopped `mysqld-nt', you
have to start it with `NET START mysql'.
The service is installed with the name `MySql'. Once installed, it must
be started using the Services Control Manager (SCM) Utility (found in
Control Panel) or by using the `NET START MySQL' command. If any
options are desired, they must be specified as "Startup parameters" in
the SCM utility before you start the *MySQL* service. Once running,
`mysqld-nt' can be stopped using `mysqladmin' or from the SCM utility
or by using the command `NET STOP MySQL'. If you use SCM to stop
`mysqld-nt', there is a strange message from SCM about `mysqld shutdown
normally'. When run as a service, `mysqld-nt' has no access to a
console and so no messages can be seen.
On NT you can get the following service error messages:
Permission Denied Means that it cannot find `mysqld-nt.exe'.
Cannot Register Means that the path is incorrect.
Failed to install Means that the service is already installed or
service. that the Service Control Manager is in bad state.
If you have problems installing `mysqld-nt' as a service, try starting
it with the full path:
C:mysqlbinmysqld-nt --install
If this doesn't work, you can get `mysqld-nt' to start properly by
fixing the path in the registry!
If you don't want to start `mysqld-nt' as a service, you can start it as
follows:
C:mysqlbinmysqld-nt --standalone
or
C:mysqlbinmysqld --standalone --debug
The last version gives you a debug trace in `C:mysqld.trace'.
Running MySQL on Windows
------------------------
*MySQL* supports TCP/IP on all Windows platforms and named pipes on NT.
The default is to use named pipes for local connections on NT and
TCP/IP for all other cases if the client has TCP/IP installed. The
host name specifies which protocol is used:
*Host name* *Protocol*
NULL (none) On NT, try named pipes first; if that doesn't
work, use TCP/IP. On Win95/Win98, TCP/IP is used.
. Named pipes
localhost TCP/IP to current host
hostname TCP/IP
You can force a *MySQL* client to use named pipes by specifying the
`--pipe' option or by specifying `.' as the host name. Use the
`--socket' option to specify the name of the pipe.
You can test whether or not *MySQL* is working by executing the
following commands:
C:mysqlbinmysqlshow
C:mysqlbinmysqlshow -u root mysql
C:mysqlbinmysqladmin version status proc
C:mysqlbinmysql test
If `mysqld' is slow to answer to connections on Win95/Win98, there is
probably a problem with your DNS. In this case, start `mysqld' with
`--skip-name-resolve' and use only `localhost' and IP numbers in the
*MySQL* grant tables. You can also avoid DNS when connecting to a
`mysqld-nt' *MySQL* server running on NT by using the `--pipe' argument
to specify use of named pipes. This works for most *MySQL* clients.
There are two versions of the *MySQL* command-line tool:
`mysql' Compiled on native Windows, which offers very limited
text editing capabilities.
`mysqlc' Compiled with the Cygnus GNU compiler and libraries,
which offers `readline' editing.
If you want to use `mysqlc.exe', you must copy
`C:mysqllibcygwinb19.dll' to `windowssystem' (or similar place).
The default privileges on Windows give all local users full privileges
to all databases. To make *MySQL* more secure, you should set a
password for all users and remove the row in the `mysql.user' table
that has `Host='localhost'' and `User='''.
You should also add a password for the `root' user. (The following
example starts by removing the anonymous user, that allows anyone to
access the 'test' database.):
C:mysqlbinmysql mysql
mysql> DELETE FROM user WHERE Host='localhost' AND User='';
mysql> QUIT
C:mysqlbinmysqladmin reload
C:mysqlbinmysqladmin -u root password your_password
After you've set the password, if you want to take down the `mysqld'
server, you can do so using this command:
mysqladmin --user=root --password=your_password shutdown
If you are using the old shareware version of *MySQL* Version 3.21
under Windows, the above command will fail with an error: `parse error
near 'SET OPTION password''. This is because the old shareware version,
which is based on *MySQL* Version 3.21, doesn't have the `SET PASSWORD'
command. The fix is in this case to upgrade to the Version 3.22
shareware.
With the newer *MySQL* versions you can easily add new users and change
privileges with `GRANT' and `REVOKE' commands. *Note GRANT::.
Connecting to a Remote MySQL from Windows with SSH
--------------------------------------------------
Here is a note about how to connect to get a secure connection to
remote MySQL server with SSH (by David Carlson <dcarlson@mplcomm.com>):
* Install an SSH client on your windows machine - As a user, the
best non-free one I've found is from `secureCRT' from
`http://www.vandyke.com/'. Another option is `f-secure' from
`http://www.f-secure.com/'. You can also find some free ones on
*Google* at
`http://directory.google.com/Top/Computers/Security/Products_and_Tools/Cryptography/SSH/Clients/Windows/'.
* Start your windows SSH client. Set `Host_Name =
yourmysqlserver_URL_or_IP'. Set `userid=your_userid' to log in to
your server (probably not the same as your *MySQL* login/ password.
* Set up port forwarding. Either do a remote forward (Set
`local_port: 3306', `remote_host: yourmysqlservername_or_ip',
`remote_port: 3306' ) or a local forward (Set `port: 3306',
`host: localhost', `remote port: 3306').
* Save everything, otherwise you'll have to redo it the next time.
* Log in to your server with SSH session you just created.
* Start some ODBC application on your windows machine (for example
Access).
* Create a new file in windows and link to *MySQL* using the ODBC
driver the same way you normally do, EXCEPT type in `localhost'
for the *MySQL* host server - not `yourmysqlservername'.
You should now have your ODBC connection to *MySQL* encrypted using SSH.
Splitting Data Across Different Disks Under Windows
---------------------------------------------------
On windows *MySQL* Version 3.23.16 and above is compiled with the
`-DUSE_SYMDIR' option. This allows you to put a database on different
disk by adding a symbolic link to it (in a similar manner that symbolic
links works on Unix).
On windows you make a symbolic link to a database by creating a file
that contains the path to the destination directory and saving this in
the `mysql_data' directory under the filename `database.sym'. Note
that the symbolic link will only be used if the directory
`mysql_data_dirdatabase' doesn't exist.
For example, if you want to have database `foo' on `D:datafoo', you
should create the file `C:mysqldatafoo.sym' that contains the text
`D:datafoo'. After this, all tables created in the database `foo'
will be created in `D:datafoo'.
Compiling MySQL Clients on Windows
----------------------------------
In your source files, you should include `windows.h' before you include
`mysql.h':
#if defined(_WIN32) || defined(_WIN64)
#include <windows.h>
#endif
#include <mysql.h>
You can either link your code with the dynamic `libmysql.lib' library,
which is just a wrapper to load in `libmysql.dll' on demand, or link
with the static `mysqlclient.lib' library.
Note that as the mysqlclient libraries are compiled as threaded
libraries, you should also compile your code to be multi-threaded!
Windows and BDB Tables
----------------------
We will shortly do a full test on the new BDB interface on Windows.
When this is done we will start to release binary distributions (for
Windows and Unix) of *MySQL* that will include support for BDB tables.
MySQL-Windows Compared to Unix MySQL
------------------------------------
*MySQL*-Windows has by now proven itself to be very stable. This version
of *MySQL* has the same features as the corresponding Unix version with
the following exceptions:
*Win95 and threads*
Win95 leaks about 200 bytes of main memory for each thread
creation. Because of this, you shouldn't run `mysqld' for an
extended time on Win95 if you do many connections, because each
connection in *MySQL* creates a new thread! WinNT and Win98 don't
suffer from this bug.
*Concurrent reads*
*MySQL* depends on the `pread()' and `pwrite()' calls to be able
to mix `INSERT' and `SELECT'. Currently we use mutexes to emulate
`pread()'/`pwrite()'. We will, in the long run, replace the file
level interface with a virtual interface so that we can use the
`readfile()'/`writefile()' interface on NT to get more speed. The
current implementation will however limit the number of open files
*MySQL* can use to 1024, which means that you will not be able to
run as many concurrent threads on NT as on Unix.
*Blocking read*
*MySQL* uses a blocking read for each connection. This means that:
* A connection will not be disconnected automatically after 8
hours, as happens with the Unix version of *MySQL*.
* If a connection hangs, it's impossible to break it without
killing *MySQL*.
* `mysqladmin kill' will not work on a sleeping connection.
* `mysqladmin shutdown' can't abort as long as there are
sleeping connections.
We plan to fix this when our windows developers have figured out a
nice workaround for this.
*UDF functions*
For the moment, *MySQL*-Windows does not support user-definable
functions.
*`DROP DATABASE'*
You can't drop a database that is in use by some thread.
*Killing *MySQL* from the task manager*
You can't kill *MySQL* from the task manager or with the shutdown
utility in Win95. You must take it down with `mysqladmin shutdown'.
*Case-insensitive names*
Filenames are case insensitive on Windows, so database and table
names are also case insensitive in *MySQL* for Windows. The only
restriction is that database and table names must be given in the
same case throughout a given statement. *Note Name case
sensitivity::.
*The `' directory character*
Pathname components in Win95 are separated by the `' character,
which is also the escape character in *MySQL*. If you are using
`LOAD DATA INFILE' or `SELECT ... INTO OUTFILE', you must double
the `' character or use Unix style filenames `/' characters:
LOAD DATA INFILE "C:\tmp\skr.txt" INTO TABLE skr;
SELECT * INTO OUTFILE 'C:/tmp/skr.txt' FROM skr;
*`Can't open named pipe' error*
If you use a MySQL 3.22 version on NT with the newest
mysql-clients you will get the following error:
error 2017: can't open named pipe to host: . pipe...
This is because the release version of *MySQL* uses named pipes on
NT by default. You can avoid this error by using the
`--host=localhost' option to the new *MySQL* clients or create a
file `C:my.cnf' that contains the following information:
[client]
host = localhost
*`Access denied for user' error*
If you get the error `Access denied for user: 'some-user@unknown'
to database 'mysql'' when accessing a *MySQL* server on the same
machine, this means that *MySQL* can't resolve your host name
properly.
To fix this, you should create a file `windowshosts' with the
following information:
127.0.0.1 localhost
*`ALTER TABLE'*
While you are doing an `ALTER TABLE' the table is locked from usage
by other threads. This has to do with the fact that you on Windows
can't delete a file that is in use by another threads. (We may in
the future find some way to go around this problem).
*`DROP TABLE' on a table that is in use by a `MERGE' table will not work.*
The `MERGE' handler does it table mapping hidden from *MySQL*.
Because windows doesn't allow one to drop files that are open, you
have to first flush all `MERGE' tables (with `FLUSH TABLES') or
drop the `MERGE' table before drooping the table. We will fix
this at the same time we introduce `VIEW''s.
Here are some open issues for anyone who might want to help us with the
Windows release:
* Make a single-user `MYSQL.DLL' server. This should include
everything in a standard *MySQL* server, except thread creation.
This will make *MySQL* much easier to use in applications that
don't need a true client/server and don't need to access the
server from other hosts.
* Add some nice start and shutdown icons to the *MySQL* installation.
* Create a tool to manage registry entries for the *MySQL* startup
options. The registry entry reading is already coded into
`mysqld.cc', but it should be recoded to be more parameter
oriented. The tool should also be able to update the `my.cnf'
file if the user prefers to use this instead of the registry.
* When registering `mysqld' as a service with `--install' (on NT) it
would be nice if you could also add default options on the command
line. For the moment, the workaround is to update the `C:my.cnf'
file instead.
* When you suspend a laptop running Win95, the `mysqld' daemon
doesn't accept new connections when the laptop is resumed. We
don't know if this is a problem with Win95, TCP/IP, or *MySQL*.
* It would be real nice to be able to kill `mysqld' from the task
manager. For the moment, you must use `mysqladmin shutdown'.
* Port `readline' to Windows for use in the `mysql' command line
tool.
* GUI versions of the standard *MySQL* clients (`mysql',
`mysqlshow', `mysqladmin', and `mysqldump') would be nice.
* It would be nice if the socket read and write functions in `net.c'
were interruptible. This would make it possible to kill open
threads with `mysqladmin kill' on Windows.
* Documentation of which Windows programs work with
*MySQL*-Windows/*MyODBC* and what must be done to get them working.
* `mysqld' always starts in the "C" locale and not in the default
locale. We would like to have `mysqld' use the current locale for
the sort order.
* Implement UDF functions with `.DLL's.
* Add macros to use the faster thread-safe increment/decrement
methods provided by Windows.
Other Windows-specific issues are described in the `README' file that
comes with the *MySQL*-Windows distribution.
OS/2 Notes
==========
*MySQL* uses quite a few open files. Because of this, you should add
something like the following to your `CONFIG.SYS' file:
SET EMXOPT=-c -n -h1024
If you don't do this, you will probably run into the following error:
File 'xxxx' not found (Errcode: 24)
When using *MySQL* with OS/2 Warp 3, FixPack 29 or above is required.
With OS/2 Warp 4, FixPack 4 or above is required. This is a requirement
of the Pthreads library. *MySQL* must be installed in a partition that
supports long filenames such as HPFS, FAT32, etc.
The `INSTALL.CMD' script must be run from OS/2's own `CMD.EXE' and may
not work with replacement shells such as `4OS2.EXE'.
The `scripts/mysql-install-db' script has been renamed. It is now called
`install.cmd' and is a REXX script, which will set up the default
*MySQL* security settings and create the WorkPlace Shell icons for
*MySQL*.
Dynamic module support is compiled in but not fully tested. Dynamic
modules should be compiled using the Pthreads run-time library.
gcc -Zdll -Zmt -Zcrtdll=pthrdrtl -I../include -I../regex -I..
-o example udf_example.cc -L../lib -lmysqlclient udf_example.def
mv example.dll example.udf
*Note:* Due to limitations in OS/2, UDF module name stems must not
exceed 8 characters. Modules are stored in the `/mysql2/udf' directory;
the `safe-mysqld.cmd' script will put this directory in the
`BEGINLIBPATH' environment variable. When using UDF modules, specified
extensions are ignored -- it is assumed to be `.udf'. For example, in
Unix, the shared module might be named `example.so' and you would load
a function from it like this:
CREATE FUNCTION metaphon RETURNS STRING SONAME "example.so";
Is OS/2, the module would be named `example.udf', but you would not
specify the module extension:
CREATE FUNCTION metaphon RETURNS STRING SONAME "example";
MySQL Binaries
==============
As a service, we at MySQL AB provides 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 sun4u with `egcs' 1.0.3a
`CC=gcc CFLAGS="-O6 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6
-fomit-frame-pointer -felide-constructors -fno-exceptions
-fno-rtti" ./configure --prefix=/usr/local/mysql --with-low-memory
--with-extra-charsets=complex'
SunOS 5.6 sun4u with `egcs' 2.90.27
`CC=gcc CFLAGS="-O6 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6
-fomit-frame-pointer -felide-constructors -fno-exceptions
-fno-rtti" ./configure --prefix=/usr/local/mysql --with-low-memory
--with-extra-charsets=complex'
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="-O6 -mpentium -mstack-align-double -fomit-frame-pointer"
CXX=gcc CXXFLAGS="-O6 -mpentium -mstack-align-double
-fomit-frame-pointer -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="-O6 -mpentiumpro -fomit-frame-pointer" CXX=gcc
CXXFLAGS="-O6 -mpentiumpro -fomit-frame-pointer
-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, some 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.
Post-installation Setup and Testing
===================================
* Menu:
* mysql_install_db:: Problems running `mysql_install_db'
* Starting server:: Problems starting the *MySQL* server
* Automatic start:: Starting and stopping *MySQL* automatically
* Command-line options:: Command-line options
* Option files:: Option files
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, 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
*Note Default 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'!
*Note safe_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 *Note Changing *MySQL* user:
Changing MySQL user.
If you have problems with `mysql_install_db', see *Note
`mysql_install_db': 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 *Note Default
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 *Note Starting
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
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 *Note Starting 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.
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'! *Note Bug reports::.
*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...'. *Note Installing many servers::.
*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
`some_tmp_dir' should be the path to some directory for which you
have write permission. *Note 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
at the online *MySQL* documentation page
(http://www.mysql.com/documentation/). Also, see *Note Linux::.
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.
Problems Starting the MySQL Server
----------------------------------
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 *Note
Automatic start::.
* By invoking `safe_mysqld', which tries to determine the proper
options for `mysqld' and then runs it with those options. *Note
safe_mysqld::.
* On NT you should install `mysqld' as a service as follows:
binmysqld-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:
binmysqld-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,
`mysqlmysql.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 started mysqld with `--bdb-no-recover' and
Berkeley DB found something wrong with it's 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
*Note mysql_install_db:: and *Note Multiple servers::.
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.
On Windows, you can try to start `mysqld' as follows:
C:mysqlbinmysqld --standalone --debug
This will not run in the background and it should also write a trace in
`mysqld.trace', which may help you determine the source of your
problems. *Note Windows::.
If you are using BDB (Berkeley DB) tables, you should familiarize
yourself with the different BDB specific startup options. *Note BDB
start::.
If you are using Gemini tables, refer to the Gemini-specific startup
options. *Note GEMINI start::.
If you are using Innobase tables, refer to the Innobase-specific startup
options. *Note INNOBASE start::.
Starting and Stopping MySQL Automatically
-----------------------------------------
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, you can change the
`mysql_daemon_user=root' line to use another user. You can also modify
`mysql.server' to pass other options to `safe_mysqld'.
`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=/tmp/mysqld.sock
port=3306
[mysql.server]
user=mysql
basedir=/usr/local/mysql
The `mysql.server' script uses the following variables: `user',
`datadir', `basedir', `bindir', and `pid-file'.
The following table shows which option sections each of the startup
script uses:
`mysqld' `mysqld' and `server'
`mysql.server' `mysql.server', `mysqld' and `server'
`safe_mysqld' `mysql.server', `mysqld' and `server'
*Note Option files::.
Command-line Options
--------------------
`mysqld' accepts the following command-line options:
`--ansi'
Use ANSI SQL syntax instead of MySQL syntax. *Note ANSI mode::.
`-b, --basedir=path'
Path to installation directory. All paths are usually resolved
relative to this.
`--big-tables'
Allow big result sets by saving all temporary sets on file. It
solves most 'table full' errors, but also slows down the queries
where in-memory tables would suffice. Since Version 3.23.2,
*MySQL* is able to solve it automaticaly by using memory for small
temporary tables and switching to disk tables where necessary.
`--bind-address=IP'
IP address to bind to.
`--character-sets-dir=path'
Directory where character sets are. *Note Character sets::.
`--chroot=path'
Chroot mysqld daemon during startup. Recommended security
measure. It will somewhat limit `LOAD DATA INFILE' and `SELECT ...
INTO OUTFILE' though.
`-h, --datadir=path'
Path to the database root.
`--default-character-set=charset'
Set the default character set. *Note Character sets::.
`--default-table-type=type'
Set the default table type for tables. *Note Table types::.
`--delay-key-write-for-all-tables'
Don't flush key buffers between writes for any `MyISAM' table.
*Note Server parameters::.
`--enable-locking'
Enable system locking. Note that if you use this option on a
system which a not fully working lockd() (as on Linux) you will
easily get mysqld to deadlock.
`-T, --exit-info'
This is a bit mask of different flags one can use for debugging the
mysqld server; One should not use this option if one doesn't know
exactly what it does!
`--flush'
Flush all changes to disk after each SQL command. Normally *MySQL*
only does a write of all changes to disk after each SQL command
and lets the operating system handle the syncing to disk. *Note
Crashing::.
`-?, --help'
Display short help and exit.
`--init-file=file'
Read SQL commands from this file at startup.
`-L, --language=...'
Client error messages in given language. May be given as a full
path. *Note Languages::.
`-l, --log[=file]'
Log connections and queries to file. *Note Query log::.
`--log-isam[=file]'
Log all ISAM/MyISAM changes to file (only used when debugging
ISAM/MyISAM).
`--log-slow-queries[=file]'
Log all queries that have taken more than `long_query_time'
seconds to execute to file. *Note Slow query log::.
`--log-update[=file]'
Log updates to `file.#' where `#' is a unique number if not given.
*Note Update log::.
`--log-long-format'
Log some extra information to update log. If you are using
`--log-slow-queries' then queries that are not using indexes are
logged to the slow query log.
`--low-priority-updates'
Table-modifying operations (`INSERT'/`DELETE'/`UPDATE') will have
lower priority than selects. It can also be done via `{INSERT |
REPLACE | UPDATE | DELETE} LOW_PRIORITY ...' to lower the priority
of only one query, or by `SET OPTION SQL_LOW_PRIORITY_UPDATES=1'
to change the priority in one thread. *Note Table locking::.
`--memlock'
Lock the `mysqld' process in memory. This works only if your
system supports the `mlockall()' system call. This may help if
you have a problem where the operating system is causing `mysqld'
to swap on disk.
`--myisam-recover [=option[,option...]]] where option is one of DEFAULT, BACKUP, FORCE or QUICK.'
If this option is used, `mysqld' will on open check if the table is
marked as crashed or if if the table wasn't closed properly (The
last option only works if you are running with `--skip-locking').
If this is the case mysqld will run check on the table. If the
table was corrupted, `mysqld' will attempt to repair it.
The following options affects how the repair works.
DEFAULT The same as not giving any option to
`--myisam-recover'.
BACKUP If the data table was changed during recover,
save a backup of the `table_name.MYD' data
file as `table_name-datetime.BAK'.
FORCE Run recover even if we will loose more than
one row from the .MYD file.
QUICK Don't check the rows in the table if there
isn't any delete blocks.
Before a table is automaticly repaired, mysqld will add a note
about this in the error log. If you want to be able to recover
from most things without user intervention, you should use the
options `BACKUP,FORCE'. This will force a repair of a table even
if some rows would be deleted, but it will keep the old data file
as a backup so that you can later examine what happened.
`--pid-file=path'
Path to pid file used by `safe_mysqld'.
`-P, --port=...'
Port number to listen for TCP/IP connections.
`-o, --old-protocol'
Use the 3.20 protocol for compatibility with some very old clients.
*Note Upgrading-from-3.20::.
`--one-thread'
Only use one thread (for debugging under Linux). *Note Debugging
server::.
`-O, --set-variable var=option'
Give a variable a value. `--help' lists variables. You can find a
full description for all variables in the `SHOW VARIABLES' section
in this manual. *Note SHOW VARIABLES::. The tuning server
parameters section includes information of how to optimize these.
*Note Server parameters::.
`--safe-mode'
Skip some optimize stages. Implies `--skip-delay-key-write'.
`--safe-show-database'
Don't show databases for which the user doesn't have any
privileges.
`--secure'
IP numbers returned by the `gethostbyname()' system call are
checked to make sure they resolve back to the original hostname.
This makes it harder for someone on the outside to get access by
pretending to be another host. This option also adds some sanity
checks of hostnames. The option is turned off by default in
*MySQL* Version 3.21 because sometimes it takes a long time to
perform backward resolutions. *MySQL* Version 3.22 caches
hostnames (unless `--skip-host-cache' is used) and has this option
enabled by default.
`--skip-concurrent-insert'
Turn off the ability to select and insert at the same time on
`MyISAM' tables. (This is only to be used if you think you have
found a bug in this feature).
`--skip-delay-key-write'
Ignore the `delay_key_write' option for all tables. *Note Server
parameters::.
`-Sg, --skip-grant-tables'
This option causes the server not to use the privilege system at
all. This gives everyone _full access_ to all databases! (You can
tell a running server to start using the grant tables again by
executing `mysqladmin flush-privileges' or `mysqladmin reload'.)
`--skip-locking'
Don't use system locking. To use `isamchk' or `myisamchk' you must
shut down the server. *Note Stability::. Note that in *MySQL*
Version 3.23 you can use `REPAIR' and `CHECK' to repair/check
`MyISAM' tables.
`--skip-name-resolve'
Hostnames are not resolved. All `Host' column values in the grant
tables must be IP numbers or `localhost'. *Note DNS::.
`--skip-networking'
Don't listen for TCP/IP connections at all. All interaction with
`mysqld' must be made via Unix sockets. This option is highly
recommended for systems where only local requests are allowed.
*Note DNS::.
`--skip-new'
Don't use new, possible wrong routines. Implies
`--skip-delay-key-write'. This will also set default table type
to `ISAM'. *Note ISAM::.
`--skip-host-cache'
Never use host name cache for faster name-ip resolution, but query
DNS server on every connect instead. *Note DNS::.
`--skip-show-database'
Don't allow 'SHOW DATABASE' commands, unless the user has
*process* privilege.
`--skip-thread-priority'
Disable using thread priorities for faster response time.
`--socket=path'
Socket file to use for local connections instead of default
`/tmp/mysql.sock'.
`-t, --tmpdir=path'
Path for temporary files. It may be useful if your default `/tmp'
directory resides on a partition too small to hold temporary
tables.
`-u, --user=user_name'
Run `mysqld' daemon as user `user_name'. This option is
_mandatory_ when starting `mysqld' as root.
`-V, --version'
Output version information and exit.
Option Files
------------
*MySQL* Version 3.22 can read default startup options for the server
and for clients from option files.
*MySQL* reads default options from the following files on Unix:
*Filename* *Purpose*
`/etc/my.cnf' Global options
`DATADIR/my.cnf' Server-specific options
`defaults-extra-file' The file specified with -defaults-extra-file=#
`~/.my.cnf' User-specific options
`DATADIR' is the *MySQL* data directory (typically
`/usr/local/mysql/data' for a binary installation or `/usr/local/var'
for a source installation). Note that this is the directory that was
specified at configuration time, not the one specified with `--datadir'
when `mysqld' starts up! (`--datadir' has no effect on where the
server looks for option files, because it looks for them before it
processes any command-line arguments.)
*MySQL* reads default options from the following files on Windows:
*Filename* *Purpose*
`windows-system-directorymy.ini'Global options
`C:my.cnf' Global options
`C:mysqldatamy.cnf' Server-specific options
Note that on Windows, you should specify all paths with `/' instead of
`'. If you use `', you need to specify this twice, as `' is the
escape character in *MySQL*.
*MySQL* tries to read option files in the order listed above. If
multiple option files exist, an option specified in a file read later
takes precedence over the same option specified in a file read earlier.
Options specified on the command line take precedence over options
specified in any option file. Some options can be specified using
environment variables. Options specified on the command line or in
option files take precedence over environment variable values. *Note
Environment variables::.
The following programs support option files: `mysql', `mysqladmin',
`mysqld', `mysqldump', `mysqlimport', `mysql.server', `myisamchk', and
`myisampack'.
You can use option files to specify any long option that a program
supports! Run the program with `--help' to get a list of available
options.
An option file can contain lines of the following forms:
`#comment'
Comment lines start with `#' or `;'. Empty lines are ignored.
`[group]'
`group' is the name of the program or group for which you want to
set options. After a group line, any `option' or `set-variable'
lines apply to the named group until the end of the option file or
another group line is given.
`option'
This is equivalent to `--option' on the command line.
`option=value'
This is equivalent to `--option=value' on the command line.
`set-variable = variable=value'
This is equivalent to `--set-variable variable=value' on the
command line. This syntax must be used to set a `mysqld' variable.
The `client' group allows you to specify options that apply to all
*MySQL* clients (not `mysqld'). This is the perfect group to use to
specify the password you use to connect to the server. (But make sure
the option file is readable and writable only to yourself.)
Note that for options and values, all leading and trailing blanks are
automatically deleted. You may use the escape sequences `b', `t',
`n', `r', `\', and `s' in your value string (`s' == blank).
Here is a typical global option file:
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
port=3306
socket=/tmp/mysql.sock
set-variable = key_buffer_size=16M
set-variable = max_allowed_packet=1M
[mysqldump]
quick
Here is typical user option file:
[client]
# The following password will be sent to all standard MySQL clients
password=my_password
[mysql]
no-auto-rehash
set-variable = connect_timeout=2
[mysql-hot-copy]
interactive-timeout
If you have a source distribution, you will find sample configuration
files named `my-xxxx.cnf' in the `support-files' directory. If you
have a binary distribution, look in the `DIR/share/mysql' directory,
where `DIR' is the pathname to the *MySQL* installation directory
(typically `/usr/local/mysql'). Currently there are sample
configuration files for small, medium, large, and very large systems.
You can copy `my-xxxx.cnf' to your home directory (rename the copy to
`.my.cnf') to experiment with this.
All *MySQL* clients that support option files support the following
options:
-no-defaults Don't read any option files.
-print-defaults Print the program name and all options
that it will get.
-defaults-file=full-path-to-default-fileOnly use the given configuration file.
-defaults-extra-file=full-path-to-default-fileRead this configuration file after the
global configuration file but before the
user configuration file.
Note that the above options must be first on the command line to work!
`--print-defaults' may however be used directly after the
`--defaults-xxx-file' commands.
Note for developers: Option file handling is implemented simply by
processing all matching options (that is, options in the appropriate
group) before any command-line arguments. This works nicely for
programs that use the last instance of an option that is specified
multiple times. If you have an old program that handles
multiply-specified options this way but doesn't read option files, you
need add only two lines to give it that capability. Check the source
code of any of the standard *MySQL* clients to see how to do this.
In shell scripts you can use the `my_print_defaults' command to parse
the config files:
shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash
The above output contains all options for the groups 'client' and
'mysql'.
Installing many servers on the same machine
===========================================
In some cases you may want to have many different `mysqld' deamons
(servers) running on the same machine. You may for example want to run
a new version of *MySQL* for testing together with an old version that
is in production. Another case is when you want to give different
users access to different mysqld servers that they manage themself.
One way to get a new server running is by starting it with a different
socket and port as follows:
shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
shell> MYSQL_TCP_PORT=3307
shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT
shell> scripts/mysql_install_db
shell> bin/safe_mysqld &
The environment variables appendix includes a list of other environment
variables you can use to affect `mysqld'. *Note Environment variables::.
The above is the quick and dirty way that one commonly use for testing.
The nice thing with this is that all connections you do in the above
shell will automaticly be directed to the new running server!
If you need to do this more permanently, you should create an own option
file for each server. *Note Option files::. In your startup script that
is executed at boot time (mysql.server?) you should specify for both
servers:
`safe_mysqld --default-file=path-to-option-file'
At least the following options should be different per server:
`port=#'
`socket=path'
`pid-file=path'
The following options should be different, if they are used:
`log=path'
`log-bin=path'
`log-update=path'
`log-isam=path'
`bdb-logdir=path'
If you want more performance, you can also specify the following
differently:
`tmpdir=path'
`bdb-tmpdir=path'
*Note Command-line options::.
If you are installing binary *MySQL* versions (.tar files) and start
them with `./bin/safe_mysqld' then in most cases the only option you
need to add/change is the `socket' and `port' argument to `safe_mysqld'.
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
may 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*.
* Menu:
* Upgrading-from-3.22:: Upgrading from a 3.22 version to 3.23
* Upgrading-from-3.21:: Upgrading from a 3.21 version to 3.22
* Upgrading-from-3.20:: Upgrading from a 3.20 version to 3.21
* Upgrading-to-arch:: Upgrading to another architecture
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' 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:
* 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.
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.
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). *Note Perl `DBI' Class: Perl DBI Class.
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'.
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.