MySQL数据库

开发平台：

Visual C++

manual.txt：源码内容

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.
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
===================================
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*.
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.
How Standards-compatible Is MySQL?
**********************************
MySQL Extensions to ANSI SQL92
==============================
*MySQL* includes some extensions that you probably will not find in
other SQL databases. Be warned that if you use them, your code will
not be portable to other SQL servers. In some cases, you can write
code that includes *MySQL* extensions, but is still portable, by using
comments of the form `/*! ... */'. In this case, *MySQL* will parse and
execute the code within the comment as it would any other *MySQL*
statement, but other SQL servers will ignore the extensions. For
example:
SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...
If you add a version number after the `'!'', the syntax will only be
executed if the *MySQL* version is equal to or newer than the used
version number:
CREATE /*!32302 TEMPORARY */ TABLE (a int);
The above means that if you have Version 3.23.02 or newer, then *MySQL*
will use the `TEMPORARY' keyword.
*MySQL* extensions are listed below:
* The field types `MEDIUMINT', `SET', `ENUM', and the different
`BLOB' and `TEXT' types.
* The field attributes `AUTO_INCREMENT', `BINARY', `NULL',
`UNSIGNED', and `ZEROFILL'.
* All string comparisons are case insensitive by default, with sort
ordering determined by the current character set (ISO-8859-1
Latin1 by default). If you don't like this, you should declare
your columns with the `BINARY' attribute or use the `BINARY' cast,
which causes comparisons to be done according to the ASCII order
used on the *MySQL* server host.
* *MySQL* maps each database to a directory under the *MySQL* data
directory, and tables within a database to filenames in the
database directory.
This has a few implications:
- Database names and table names are case sensitive in *MySQL*
on operating systems that have case-sensitive filenames (like
most Unix systems). *Note Name case sensitivity::.
- Database, table, index, column, or alias names may begin with
a digit (but may not consist solely of digits).
- You can use standard system commands to backup, rename, move,
delete, and copy tables. For example, to rename a table,
rename the `.MYD', `.MYI', and `.frm' files to which the
table corresponds.
* In SQL statements, you can access tables from different databases
with the `db_name.tbl_name' syntax. Some SQL servers provide the
same functionality but call this `User space'. *MySQL* doesn't
support tablespaces as in: `create table ralph.my_table...IN
my_tablespace'.
* `LIKE' is allowed on numeric columns.
* Use of `INTO OUTFILE' and `STRAIGHT_JOIN' in a `SELECT' statement.
*Note `SELECT': SELECT.
* The `SQL_SMALL_RESULT' option in a `SELECT' statement.
* `EXPLAIN SELECT' to get a description on how tables are joined.
* Use of index names, indexes on a prefix of a field, and use of
`INDEX' or `KEY' in a `CREATE TABLE' statement. *Note `CREATE
TABLE': CREATE TABLE.
* Use of `TEMPORARY' or `IF NOT EXISTS' with `CREATE TABLE'.
* Use of `COUNT(DISTINCT list)' where 'list' is more than one
element.
* Use of `CHANGE col_name', `DROP col_name', or `DROP INDEX',
`IGNORE' or `RENAME' in an `ALTER TABLE' statement. *Note `ALTER
TABLE': ALTER TABLE.
* Use of `RENAME TABLE'. *Note `RENAME TABLE': RENAME TABLE.
* Use of multiple `ADD', `ALTER', `DROP', or `CHANGE' clauses in an
`ALTER TABLE' statement.
* Use of `DROP TABLE' with the keywords `IF EXISTS'.
* You can drop multiple tables with a single `DROP TABLE' statement.
* The `LIMIT' clause of the `DELETE' statement.
* The `DELAYED' clause of the `INSERT' and `REPLACE' statements.
* The `LOW_PRIORITY' clause of the `INSERT', `REPLACE', `DELETE',
and `UPDATE' statements.
* Use of `LOAD DATA INFILE'. In many cases, this syntax is
compatible with Oracle's `LOAD DATA INFILE'. *Note `LOAD DATA':
LOAD DATA.
* The `ANALYZE TABLE', `CHECK TABLE', `OPTIMIZE TABLE', and `REPAIR
TABLE' statements.
* The `SHOW' statement. *Note `SHOW': SHOW.
* Strings may be enclosed by either `"' or `'', not just by `''.
* Use of the escape `' character.
* The `SET OPTION' statement. *Note `SET OPTION': SET OPTION.
* You don't need to name all selected columns in the `GROUP BY' part.
This gives better performance for some very specific, but quite
normal queries. *Note Group by functions::.
* One can specify `ASC' and `DESC' with `GROUP BY'.
* To make it easier for users who come from other SQL environments,
*MySQL* supports aliases for many functions. For example, all
string functions support both ANSI SQL syntax and ODBC syntax.
* *MySQL* understands the `||' and `&&' operators to mean logical OR
and AND, as in the C programming language. In *MySQL*, `||' and
`OR' are synonyms, as are `&&' and `AND'. Because of this nice
syntax, *MySQL* doesn't support the ANSI SQL `||' operator for
string concatenation; use `CONCAT()' instead. Because `CONCAT()'
takes any number of arguments, it's easy to convert use of the
`||' operator to *MySQL*.
* `CREATE DATABASE' or `DROP DATABASE'. *Note `CREATE DATABASE':
CREATE DATABASE.
* The `%' operator is a synonym for `MOD()'. That is, `N % M' is
equivalent to `MOD(N,M)'. `%' is supported for C programmers and
for compatibility with PostgreSQL.
* The `=', `<>', `<=' ,`<', `>=',`>', `<<', `>>', `<=>', `AND',
`OR', or `LIKE' operators may be used in column comparisons to the
left of the `FROM' in `SELECT' statements. For example:
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
* The `LAST_INSERT_ID()' function. *Note `mysql_insert_id()':
mysql_insert_id.
* The `REGEXP' and `NOT REGEXP' extended regular expression
operators.
* `CONCAT()' or `CHAR()' with one argument or more than two
arguments. (In *MySQL*, these functions can take any number of
arguments.)
* The `BIT_COUNT()', `CASE', `ELT()', `FROM_DAYS()', `FORMAT()',
`IF()', `PASSWORD()', `ENCRYPT()', `md5()', `ENCODE()', `DECODE()',
`PERIOD_ADD()', `PERIOD_DIFF()', `TO_DAYS()', or `WEEKDAY()'
functions.
* Use of `TRIM()' to trim substrings. ANSI SQL only supports removal
of single characters.
* The `GROUP BY' functions `STD()', `BIT_OR()', and `BIT_AND()'.
* Use of `REPLACE' instead of `DELETE' + `INSERT'. *Note `REPLACE':
REPLACE.
* The `FLUSH flush_option' statement.
* The possiblity to set variables in a statement with `:=':
SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
Running MySQL in ANSI Mode
==========================
If you start mysqld with the `--ansi' option, the following behavior of
*MySQL* changes:
* `||' is string concatenation instead of `OR'.
* You can have any number of spaces between a function name and the
`('. This forces all function names to be treated as reserved
words.
* `"' will be an identifier quote character (like the *MySQL* ``'
quote character) and not a string quote character.
* `REAL' will be a synonym for `FLOAT' instead of a synonym of
`DOUBLE'.
MySQL Differences Compared to ANSI SQL92
========================================
We try to make *MySQL* follow the ANSI SQL standard and the ODBC SQL
standard, but in some cases *MySQL* does some things differently:
* `--' is only a comment if followed by a white space. *Note Missing
comments::.
* For `VARCHAR' columns, trailing spaces are removed when the value
is stored. *Note Bugs::.
* In some cases, `CHAR' columns are silently changed to `VARCHAR'
columns. *Note Silent column changes::.
* Privileges for a table are not automatically revoked when you
delete a table. You must explicitly issue a `REVOKE' to revoke
privileges for a table. *Note `GRANT': GRANT.
* `NULL AND FALSE' will evaluate to `NULL' and not to `FALSE'. This
is because we don't think it's good to have to evaluate a lot of
extra conditions in this case.
Functionality Missing from MySQL
================================
The following functionality is missing in the current version of
*MySQL*. For a prioritized list indicating when new extensions may be
added to *MySQL*, you should consult the online *MySQL* TODO list
(http://www.mysql.com/documentation/manual.php?section=TODO). That is
the latest version of the TODO list in this manual. *Note TODO::.
Sub-selects
-----------
The following will not yet work in *MySQL*:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2 where table1.id=table2.id);
However, in many cases you can rewrite the query without a sub-select:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL
For more complicated subqueries you can often create temporary tables
to hold the subquery. In some cases, however this option will not
work. The most frequently encountered of these cases arises with
`DELETE' statements, for which standard SQL does not support joins
(except in sub-selects). For this situation there are two options
available until subqueries are supported by *MySQL*.
The first option is to use a procedural programming language (such as
Perl or PHP) to submit a `SELECT' query to obtain the primary keys for
the records to be deleted, and then use these values to construct the
`DELETE' statement (`DELETE FROM ... WHERE ... IN (key1, key2, ...)').
The second option is to use interactive SQL to contruct a set of
`DELETE' statements automatically, using the *MySQL* extension
`CONCAT()' (in lieu of the standard `||' operator). For example:
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
You can place this query in a script file and redirect input from it to
the `mysql' command-line interpreter, piping its output back to a
second instance of the interpreter:
prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb
*MySQL* only supports `INSERT ... SELECT ...' and `REPLACE ... SELECT
...' Independent sub-selects will probably be available in Version 4.0.
You can now use the function `IN()' in other contexts, however.
`SELECT INTO TABLE'
-------------------
*MySQL* doesn't yet support the Oracle SQL extension: `SELECT ... INTO
TABLE ...'. *MySQL* supports instead the ANSI SQL syntax `INSERT INTO
... SELECT ...', which is basically the same thing. *Note INSERT
SELECT::.
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;
Alternatively, you can use `SELECT INTO OUTFILE...' or `CREATE TABLE
... SELECT' to solve your problem.
Transactions
------------
As *MySQL* does nowadays support transactions, the following discussion
is only valid if you are only using the non-transaction-safe table
types. *Note COMMIT::.
The question is often asked, by the curious and the critical, "Why is
*MySQL* not a transactional database?" or "Why does *MySQL* not support
transactions?"
*MySQL* has made a conscious decision to support another paradigm for
data integrity, "atomic operations." It is our thinking and experience
that atomic operations offer equal or even better integrity with much
better performance. We, nonetheless, appreciate and understand the
transactional database paradigm and plan, within the next few releases,
to introduce transaction-safe tables on a per table basis. We will be
giving our users the possibility to decide if they need the speed of
atomic operations or if they need to use transactional features in their
applications.
How does one use the features of *MySQL* to maintain rigorous integrity
and how do these features compare with the transactional paradigm?
First, in the transactional paradigm, if your applications are written
in a way that is dependent on the calling of "rollback" instead of
"commit" in critical situations, then transactions are more convenient.
Moreover, transactions ensure that unfinished updates or corrupting
activities are not committed to the database; the server is given the
opportunity to do an automatic rollback and your database is saved.
*MySQL*, in almost all cases, allows you to solve for potential
problems by including simple checks before updates and by running simple
scripts that check the databases for inconsistencies and automatically
repair or warn if such occurs. Note that just by using the *MySQL* log
or even adding one extra log, one can normally fix tables perfectly
with no data integrity loss.
Moreover, fatal transactional updates can be rewritten to be atomic. In
fact,we will go so far as to say that all integrity problems that
transactions solve can be done with `LOCK TABLES' or atomic updates,
ensuring that you never will get an automatic abort from the database,
which is a common problem with transactional databases.
Not even transactions can prevent all loss if the server goes down. In
such cases even a transactional system can lose data. The difference
between different systems lies in just how small the time-lap is where
they could lose data. No system is 100% secure, only "secure enough."
Even Oracle, reputed to be the safest of transactional databases, is
reported to sometimes lose data in such situations.
To be safe with *MySQL*, you only need to have backups and have the
update logging turned on. With this you can recover from any situation
that you could with any transactional database. It is, of course,
always good to have backups, independent of which database you use.
The transactional paradigm has its benefits and its drawbacks. Many
users and application developers depend on the ease with which they can
code around problems where an abort appears to be, or is necessary, and
they may have to do a little more work with *MySQL* to either think
differently or write more. If you are new to the atomic operations
paradigm, or more familiar or more comfortable with transactions, do not
jump to the conclusion that *MySQL* has not addressed these issues.
Reliability and integrity are foremost in our minds. Recent estimates
indicate that there are more than 1,000,000 mysqld servers currently
running, many of which are in production environments. We hear very,
very seldom from our users that they have lost any data, and in almost
all of those cases user error is involved. This is, in our opinion, the
best proof of *MySQL*'s stability and reliability.
Lastly, in situations where integrity is of highest importance,
*MySQL*'s current features allow for transaction-level or better
reliability and integrity. If you lock tables with `LOCK TABLES', all
updates will stall until any integrity checks are made. If you only
obtain a read lock (as opposed to a write lock), then reads and inserts
are still allowed to happen. The new inserted records will not be seen
by any of the clients that have a `READ' lock until they release their
read locks. With `INSERT DELAYED' you can queue inserts into a local
queue, until the locks are released, without having the client wait for
the insert to complete. *Note INSERT DELAYED::.
"Atomic," in the sense that we mean it, is nothing magical. It only
means that you can be sure that while each specific update is running,
no other user can interfere with it, and there will never be an
automatic rollback (which can happen on transaction based systems if
you are not very careful). *MySQL* also guarantees that there will not
be any dirty reads. You can find some example of how to write atomic
updates in the commit-rollback section. *Note Commit-rollback::.
We have thought quite a bit about integrity and performance, and we
believe that our atomic operations paradigm allows for both high
reliability and extremely high performance, on the order of three to
five times the speed of the fastest and most optimally tuned of
transactional databases. We didn't leave out transactions because they
are hard to do. The main reason we went with atomic operations as
opposed to transactions is that by doing this we could apply many speed
optimizations that would not otherwise have been possible.
Many of our users who have speed foremost in their minds are not at all
concerned about transactions. For them transactions are not an issue.
For those of our users who are concerned with or have wondered about
transactions vis-a-vis *MySQL*, there is a "*MySQL* way" as we have
outlined above. For those where safety is more important than speed,
we recommend them to use the `BDB', `GEMINI' or `INNOBASE' tables for
all their critical data. *Note Table types::.
One final note: We are currently working on a safe replication schema
that we believe to be better than any commercial replication system we
know of. This system will work most reliably under the atomic
operations, non-transactional, paradigm. Stay tuned.
Stored Procedures and Triggers
------------------------------
A stored procedure is a set of SQL commands that can be compiled and
stored in the server. Once this has been done, clients don't need to
keep reissuing the entire query but can refer to the stored procedure.
This provides better performance because the query has to be parsed
only once, and less information needs to be sent between the server and
the client. You can also raise the conceptual level by having libraries
of functions in the server.
A trigger is a stored procedure that is invoked when a particular event
occurs. For example, you can install a stored procedure that is
triggered each time a record is deleted from a transaction table and
that automatically deletes the corresponding customer from a customer
table when all his transactions are deleted.
The planned update language will be able to handle stored procedures,
but without triggers. Triggers usually slow down everything, even
queries for which they are not needed.
To see when *MySQL* might get stored procedures, see *Note TODO::.
Foreign Keys
------------
Note that foreign keys in SQL are not used to join tables, but are used
mostly for checking referential integrity (foreign key constraints). If
you want to get results from multiple tables from a `SELECT' statement,
you do this by joining tables:
SELECT * from table1,table2 where table1.id = table2.id;
*Note `JOIN': JOIN. *Note example-Foreign keys::.
The `FOREIGN KEY' syntax in *MySQL* exists only for compatibility with
other SQL vendors' `CREATE TABLE' commands; it doesn't do anything.
The `FOREIGN KEY' syntax without `ON DELETE ...' is mostly used for
documentation purposes. Some ODBC applications may use this to produce
automatic `WHERE' clauses, but this is usually easy to override.
`FOREIGN KEY' is sometimes used as a constraint check, but this check
is unnecessary in practice if rows are inserted into the tables in the
right order. *MySQL* only supports these clauses because some
applications require them to exist (regardless of whether or not they
work).
In *MySQL*, you can work around the problem of `ON DELETE ...' not
being implemented by adding the appropriate `DELETE' statement to an
application when you delete records from a table that has a foreign key.
In practice this is as quick (in some cases quicker) and much more
portable than using foreign keys.
In the near future we will extend the `FOREIGN KEY' implementation so
that at least the information will be saved in the table specification
file and may be retrieved by `mysqldump' and ODBC. At a later stage we
will implement the foreign key constraints for application that can't
easily be coded to avoid them.
Reasons NOT to Use Foreign Keys constraints
...........................................
There are so many problems with foreign key constraints that we don't
know where to start:
* Foreign key constraints make life very complicated, because the
foreign key definitions must be stored in a database and
implementing them would destroy the whole "nice approach" of using
files that can be moved, copied, and removed.
* The speed impact is terrible for `INSERT' and `UPDATE' statements,
and in this case almost all `FOREIGN KEY' constraint checks are
useless because you usually insert records in the right tables in
the right order, anyway.
* There is also a need to hold locks on many more tables when
updating one table, because the side effects can cascade through
the entire database. It's MUCH faster to delete records from one
table first and subsequently delete them from the other tables.
* You can no longer restore a table by doing a full delete from the
table and then restoring all records (from a new source or from a
backup).
* If you use foreign key constraints you can't dump and restore
tables unless you do so in a very specific order.
* It's very easy to do "allowed" circular definitions that make the
tables impossible to re-create each table with a single create
statement, even if the definition works and is usable.
* It's very easy to overlook `FOREIGN KEY ... ON DELETE' rules when
one codes an application. It's not unusual that one loses a lot of
important information just because a wrong or misused `ON DELETE'
rule.
The only nice aspect of `FOREIGN KEY' is that it gives ODBC and some
other client programs the ability to see how a table is connected and
to use this to show connection diagrams and to help in building
applicatons.
*MySQL* will soon store `FOREIGN KEY' definitions so that a client can
ask for and receive an answer about how the original connection was
made. The current `.frm' file format does not have any place for it.
At a later stage we will implement the foreign key constraints for
application that can't easily be coded to avoid them.
Views
-----
*MySQL* doesn't yet support views, but we plan to implement these to
about 4.1.
Views are mostly useful in letting user access a set of relations as one
table (in read-only mode). Many SQL databases doesn't allow one to
update any rows in a view, but you have to do the updates in the
separate tables.
As *MySQL* is mostly used in applications and on web system where the
application write has full control on the database usage, most of our
users haven't regarded views to be very important. (At least no one has
been interested enough of this to be prepared to finance the
implementation of views).
One doesn't need views in *MySQL* to restrict access to columns as
*MySQL* has a very sophisticated privilege system. *Note Privilege
system::.
`--' as the Start of a Comment
------------------------------
Some other SQL databases use `--' to start comments. *MySQL* has `#' as
the start comment character, even if the `mysql' command-line tool
removes all lines that start with `--'. You can also use the C comment
style `/* this is a comment */' with *MySQL*. *Note Comments::.
*MySQL* Version 3.23.3 and above supports the `--' comment style only
if the comment is followed by a space. This is because this degenerate
comment style has caused many problems with automatically generated SQL
queries that have used something like the following code, where we
automatically insert the value of the payment for `!payment!':
UPDATE tbl_name SET credit=credit-!payment!
What do you think will happen when the value of `payment' is negative?
Because `1--1' is legal in SQL, we think it is terrible that `--' means
start comment.
In *MySQL* Version 3.23 you can, however, use: `1-- This is a comment'
The following discussion only concerns you if you are running a *MySQL*
version earlier than Version 3.23:
If you have a SQL program in a text file that contains `--' comments
you should use:
shell> replace " --" " #" < text-file-with-funny-comments.sql
| mysql database
instead of the usual:
shell> mysql database < text-file-with-funny-comments.sql
You can also edit the command file "in place" to change the `--'
comments to `#' comments:
shell> replace " --" " #" -- text-file-with-funny-comments.sql
Change them back with this command:
shell> replace " #" " --" -- text-file-with-funny-comments.sql
What Standards Does MySQL Follow?
=================================
Entry level SQL92. ODBC levels 0-2.
How to Cope Without `COMMIT'/`ROLLBACK'
=======================================
The following mostly applies only for `ISAM', `MyISAM', and `HEAP'
tables. If you only use transaction-safe tables (`BDB', `GEMINI' or
`INNOBASE' tables) in an a update, you can do `COMMIT' and `ROLLBACK'
also with *MySQL*. *Note COMMIT::.
The problem with handling `COMMIT'-`ROLLBACK' efficiently with the
above table types would require a completely different table layout
than *MySQL* uses today. The table type would also need extra threads
that do automatic cleanups on the tables, and the disk usage would be
much higher. This would make these table types about 2-4 times slower
than they are today.
For the moment, we prefer implementing the SQL server language
(something like stored procedures). With this you would very seldom
really need `COMMIT'-`ROLLBACK.' This would also give much better
performance.
Loops that need transactions normally can be coded with the help of
`LOCK TABLES', and you don't need cursors when you can update records
on the fly.
We at TcX had a greater need for a real fast database than a 100%
general database. Whenever we find a way to implement these features
without any speed loss, we will probably do it. For the moment, there
are many more important things to do. Check the TODO for how we
prioritize things at the moment. (Customers with higher levels of
support can alter this, so things may be reprioritized.)
The current problem is actually `ROLLBACK'. Without `ROLLBACK', you can
do any kind of `COMMIT' action with `LOCK TABLES'. To support
`ROLLBACK' with the above table types, *MySQL* would have to be changed
to store all old records that were updated and revert everything back
to the starting point if `ROLLBACK' was issued. For simple cases, this
isn't that hard to do (the current `isamlog' could be used for this
purpose), but it would be much more difficult to implement `ROLLBACK'
for `ALTER/DROP/CREATE TABLE'.
To avoid using `ROLLBACK', you can use the following strategy:
1. Use `LOCK TABLES ...' to lock all the tables you want to access.
2. Test conditions.
3. Update if everything is okay.
4. Use `UNLOCK TABLES' to release your locks.
This is usually a much faster method than using transactions with
possible `ROLLBACK's, although not always. The only situation this
solution doesn't handle is when someone kills the threads in the middle
of an update. In this case, all locks will be released but some of the
updates may not have been executed.
You can also use functions to update records in a single operation.
You can get a very efficient application by using the following
techniques:
* Modify fields relative to their current value.
* Update only those fields that actually have changed.
For example, when we are doing updates to some customer information, we
update only the customer data that has changed and test only that none
of the changed data, or data that depend on the changed data, has
changed compared to the original row. The test for changed data is done
with the `WHERE' clause in the `UPDATE' statement. If the record wasn't
updated, we give the client a message: "Some of the data you have
changed have been changed by another user". Then we show the old row
versus the new row in a window, so the user can decide which version of
the customer record he should use.
This gives us something that is similar to column locking but is
actually even better, because we only update some of the columns, using
values that are relative to their current values. This means that
typical `UPDATE' statements look something like these:
UPDATE tablename SET pay_back=pay_back+'relative change';
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_he_owes_us=money_he_owes_us+'new_money'
WHERE
customer_id=id AND address='old address' AND phone='old phone';
As you can see, this is very efficient and works even if another client
has changed the values in the `pay_back' or `money_he_owes_us' columns.
In many cases, users have wanted `ROLLBACK' and/or `LOCK TABLES' for
the purpose of managing unique identifiers for some tables. This can be
handled much more efficiently by using an `AUTO_INCREMENT' column and
either the SQL function `LAST_INSERT_ID()' or the C API function
`mysql_insert_id()'. *Note `mysql_insert_id()': mysql_insert_id.
At MySQL AB, we have never had any need for row-level locking because
we have always been able to code around it. Some cases really need row
locking, but they are very few. If you want row-level locking, you can
use a flag column in the table and do something like this:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
*MySQL* returns 1 for the number of affected rows if the row was found
and `row_flag' wasn't already 1 in the original row.
You can think of it as *MySQL* changed the above query to:
UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;
The MySQL Access Privilege System
*********************************
*MySQL* has an advanced but non-standard security/privilege system.
This section describes how it works.
General Security
================
Anyone using *MySQL* on a computer connected to the Internet should
read this section to avoid the most common security mistakes.
In discussing security, we emphasize the necessity of fully protecting
the entire server host (not simply the *MySQL* server) against all types
of applicable attacks: eavesdropping, altering, playback, and denial of
service. We do not cover all aspects of availability and fault tolerance
here.
*MySQL* uses Access Control Lists (ACLs) security for all connections,
queries, and other operations that a user may attempt to perform. There
is also some support for SSL-encrypted connections between *MySQL*
clients and servers. Many of the concepts discussed here are not
specific to *MySQL* at all; the same general ideas apply to almost all
applications.
When running *MySQL*, follow these guidelines whenever possible:
* DON'T EVER GIVE ANYONE (EXCEPT THE *MySQL* ROOT USER) ACCESS TO THE
mysql.user TABLE! The encrypted password is the real password in
*MySQL*. If you know this for one user you can easily login as him
if you have access to his 'host'.
* Learn the *MySQL* access privilege system. The `GRANT' and
`REVOKE' commands are used for restricting access to *MySQL*. Do
not grant any more privileges than necessary. Never grant
privileges to all hosts.
Checklist:
- Try `mysql -u root'. If you are able to connect successfully
to the server without being asked for a password, you have
problems. Any user (not just root) can connect to your
*MySQL* server with full privileges! Review the *MySQL*
installation instructions, paying particular attention to the
item about setting a `root' password.
- Use the command `SHOW GRANTS' and check to see who has access
to what. Remove those privileges that are not necessary using
the `REVOKE' command.
* Do not keep any plain-text passwords in your database. When your
computer becomes compromised, the intruder can take the full list
of passwords and use them. Instead use `MD5()' or another one-way
hashing function.
* Do not use passwords from dictionaries. There are special programs
to break them. Even passwords like "xfish98" are very bad. Much
better is "duag98" which contains the same word "fish" but typed
one key to the left on a standard QWERTY keyboard. Another method
is to use "Mhall" which is taken from the first characters of of
each word in the sentence "Mary had a little lamb." This is easy
to remember and type, but hard to guess for someone who does not
know it.
* Invest in a firewall. This protects from at least 50% of all types
of exploits in any software. Put *MySQL* behind the firewall or in
a demilitarized zone (DMZ).
Checklist:
- Try to scan your ports from the Internet using a tool such as
`nmap'. *MySQL* uses port 3306 by default. This port should
be inaccessible from untrusted hosts. Another simple way to
check whether or not your *MySQL* port is open is to type
`telnet server_host 3306' from some remote machine, where
`server_host' is the hostname of your *MySQL* server. If you
get a connection and some garbage characters, the port is
open, and should be closed on your firewall or router, unless
you really have a good reason to keep it open. If `telnet'
just hangs, everything is OK, the port is blocked.
* Do not trust any data entered by your users. They can try to trick
your code by entering special or escaped character sequences in
Web forms, URLs, or whatever application you have built. Be sure
that your application remains secure if a user enters something
like "`; DROP DATABASE mysql;'". This is an extreme example, but
large security leaks and data loss may occur as a result of
hackers using similar techniques, if you do not prepare for them.
Also remember to check numeric data. A common mistake is to
protect only strings. Sometimes people think that if a database
contains only publicly available data that it need not be
protected. This is incorrect. At least denial-of-service type
attacks can be performed on such databases. The simplest way to
protect from this type of attack is to use apostrophes around the
numeric constants: `SELECT * FROM table WHERE ID='234'' instead of
`SELECT * FROM table WHERE ID=234'. *MySQL* automatically
converts this string to a number and strips all non-numeric
symbols from it.
Checklist:
- All WWW applications:
* Try to enter `'' and `"' in all your Web forms. If you
get any kind of *MySQL* error, investigate the problem
right away.
* Try to modify any dynamic URLs by adding `%22' (`"'),
`%23' (`#'), and `%27' (`'') in the URL.
* Try to modify datatypes in dynamic URLs from numeric
ones to character ones containing characters from
previous examples. Your application should be safe
against this and similar attacks.
* Try to enter characters, spaces, and special symbols
instead of numbers in numeric fields. Your application
should remove them before passing them to *MySQL* or
your application should generate an error. Passing
unchecked values to *MySQL* is very dangerous!
* Check data sizes before passing them to *MySQL*.
* Consider having your application connect to the database
using a different user name than the one you use for
administrative purposes. Do not give your applications
any more access privileges than they need.
- Users of PHP:
* Check out the `addslashes()' function.
- Users of *MySQL* C API:
* Check out the `mysql_escape()' API call.
- Users of *MySQL*++:
* Check out the `escape' and `quote' modifiers for query
streams.
- Users of Perl DBI:
* Check out the `quote()' method.
* Do not transmit plain (unencrypted) data over the Internet. These
data are accessible to everyone who has the time and ability to
intercept it and use it for their own purposes. Instead, use an
encrypted protocol such as SSL or SSH. *MySQL* supports internal
SSL connections as of Version 3.23.9. SSH port-forwarding can be
used to create an encrypted (and compressed) tunnel for the
communication.
* Learn to use the `tcpdump' and `strings' utilities. For most cases,
you can check whether or not *MySQL* data streams are unencrypted
by issuing a command like the following:
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings
(This works under Linux and should work with small modifications
under other systems). Warning: If you do not see data this
doesn't always actually mean that it is encrypted. If you need
high security, you should consult with a security expert.
How to Make MySQL Secure Against Crackers
=========================================
When you connect to a *MySQL* server, you normally should use a
password. The password is not transmitted in clear text over the
connection, however the encryption algorithm is not very strong, and
with some effort a clever attacker can crack the password if he is able
to sniff the traffic between the client and the server. If the
connection between the client and the server goes through an untrusted
network, you should use an *SSH* tunnel to encrypt the communication.
All other information is transferred as text that can be read by anyone
who is able to watch the connection. If you are concerned about this,
you can use the compressed protocol (in *MySQL* Version 3.22 and above)
to make things much harder. To make things even more secure you should
use `ssh' (see `http://www.cs.hut.fi/ssh'). With this, you can get an
encrypted TCP/IP connection between a *MySQL* server and a *MySQL*
client.
To make a *MySQL* system secure, you should strongly consider the
following suggestions:
* Use passwords for all *MySQL* users. Remember that anyone can log
in as any other person as simply as `mysql -u other_user db_name'
if `other_user' has no password. It is common behavior with
client/server applications that the client may specify any user
name. You can change the password of all users by editing the
`mysql_install_db' script before you run it, or only the password
for the *MySQL* `root' user like this:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;
* Don't run the *MySQL* daemon as the Unix `root' user. It is very
dangerous as any user with `FILE' privileges will be able to
create files as `root' (for example, `~root/.bashrc'). To prevent
this `mysqld' will refuse to run as `root' unless it is specified
directly via `--user=root' option.
`mysqld' can be run as any user instead. You can also create a new
Unix user `mysql' to make everything even more secure. If you run
`mysqld' as another Unix user, you don't need to change the `root'
user name in the `user' table, because *MySQL* user names have
nothing to do with Unix user names. You can edit the
`mysql.server' script to start `mysqld' as another Unix user.
Normally this is done with the `su' command. For more details, see
*Note Changing *MySQL* user: Changing MySQL user.
* If you put a password for the Unix `root' user in the
`mysql.server' script, make sure this script is readable only by
`root'.
* Check that the Unix user that `mysqld' runs as is the only user
with read/write privileges in the database directories.
* On Unix platforms, do not run `mysqld' as root unless you really
need to. Consider creating a user named `mysql' for that purpose.
* Don't give the *process* privilege to all users. The output of
`mysqladmin processlist' shows the text of the currently executing
queries, so any user who is allowed to execute that command might
be able to see if another user issues an `UPDATE user SET
password=PASSWORD('not_secure')' query.
`mysqld' reserves an extra connection for users who have the
*process* privilege, so that a *MySQL* `root' user can log in and
check things even if all normal connections are in use.
* Don't give the *file* privilege to all users. Any user that has
this privilege can write a file anywhere in the file system with
the privileges of the `mysqld' daemon! To make this a bit safer,
all files generated with `SELECT ... INTO OUTFILE' are readable to
everyone, and you can't overwrite existing files.
The *file* privilege may also be used to read any file accessible
to the Unix user that the server runs as. This could be abused,
for example, by using `LOAD DATA' to load `/etc/passwd' into a
table, which can then be read with `SELECT'.
* If you don't trust your DNS, you should use IP numbers instead of
hostnames in the grant tables. In principle, the `--secure'
option to `mysqld' should make hostnames safe. In any case, you
should be very careful about creating grant table entries using
hostname values that contain wild cards!
* If you want to restrict the number of connections for a single
user, you can do this by setting the `max_user_connections'
variable in `mysqld'.
Startup options to mysqld which concerns security
=================================================
The following `mysqld' options affect networking security:
`--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 and has this option enabled by default.
`--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-name-resolve'
Hostnames are not resolved. All `Host' column values in the grant
tables must be IP numbers or `localhost'.
`--skip-networking'
Don't allow TCP/IP connections over the network. All connections
to `mysqld' must be made via Unix sockets. This option is
unsuitable for systems that use MIT-pthreads, because the
MIT-pthreads package doesn't support Unix sockets.
`--skip-show-database'
`SHOW DATABASE' command doesn't return anything.
`--safe-show-database'
`SHOW DATABASE' only returns databases for which the user have
some kind of privilege.
What the Privilege System Does
==============================
The primary function of the *MySQL* privilege system is to authenticate
a user connecting from a given host, and to associate that user with
privileges on a database such as *select*, *insert*, *update* and
*delete*.
Additional functionality includes the ability to have an anonymous user
and to grant privileges for *MySQL*-specific functions such as `LOAD
DATA INFILE' and administrative operations.
MySQL User Names and Passwords
==============================
There are several distinctions between the way user names and passwords
are used by *MySQL* and the way they are used by Unix or Windows:
* User names, as used by *MySQL* for authentication purposes, have
nothing to do with Unix user names (login names) or Windows user
names. Most *MySQL* clients by default try to log in using the
current Unix user name as the *MySQL* user name, but that is for