MySQL数据库

开发平台：

Visual C++

manual.texi：源码内容

something is using the TCP/IP port @code{mysqld} is trying to use.
See @ref{mysql_install_db} and @ref{Multiple servers}.
If @code{mysqld} is currently running, you can find out what path settings
it is using by executing this command:
@example
shell> mysqladmin variables
or
shell> mysqladmin -h 'your-host-name' variables
@end example
If @code{safe_mysqld} starts the server but you can't connect to it,
you should make sure you have an entry in @file{/etc/hosts} that looks like
this:
@example
127.0.0.1 localhost
@end example
This problem occurs only on systems that don't have a working thread
library and for which @strong{MySQL} must be configured to use MIT-pthreads.
On Windows, you can try to start @code{mysqld} as follows:
@example
C:mysqlbinmysqld --standalone --debug
@end example
This will not run in the background and it should also write a trace in
@file{mysqld.trace}, which may help you determine the source of your
problems. @xref{Windows}.
If you are using BDB (Berkeley DB) tables, you should familiarize
yourself with the different BDB specific startup options. @xref{BDB start}.
If you are using Gemini tables, refer to the Gemini-specific startup options.
@xref{GEMINI start}.
If you are using Innobase tables, refer to the Innobase-specific startup
options. @xref{INNOBASE start}.
@node Automatic start, Command-line options, Starting server, Post-installation
@subsection Starting and Stopping MySQL Automatically
@cindex starting, the server automatically
@cindex stopping, the server
@cindex server, starting and stopping
The @code{mysql.server} script can be used to start or stop the server
by invoking it with @code{start} or @code{stop} arguments:
@example
shell> mysql.server start
shell> mysql.server stop
@end example
@code{mysql.server} can be found in the @file{share/mysql} directory
under the @strong{MySQL} installation directory or in the @file{support-files}
directory of the @strong{MySQL} source tree.
Before @code{mysql.server} starts the server, it changes directory to
the @strong{MySQL} installation directory, then invokes
@code{safe_mysqld}. You might need to edit @code{mysql.server} if you
have a binary distribution that you've installed in a non-standard
location. Modify it to @code{cd} into the proper directory before it
runs @code{safe_mysqld}. If you want the server to run as some specific
user, you can change the @code{mysql_daemon_user=root} line to use
another user. You can also modify @code{mysql.server} to pass other
options to @code{safe_mysqld}.
@code{mysql.server stop} brings down the server by sending a signal to it.
You can take down the server manually by executing @code{mysqladmin shutdown}.
You might want to add these start and stop commands to the appropriate places
in your @file{/etc/rc*} files when you start using @strong{MySQL} for
production applications. Note that if you modify @code{mysql.server}, then
upgrade @strong{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 @file{/etc/rc.local} to start external scripts, you
should append the following to it:
@example
/bin/sh -c 'cd /usr/local/mysql ; ./bin/safe_mysqld --user=mysql &'
@end example
You can also add options for @code{mysql.server} in a global
@file{/etc/my.cnf} file. A typical @file{/etc/my.cnf} file might look like
this:
@example
[mysqld]
datadir=/usr/local/mysql/var
socket=/tmp/mysqld.sock
port=3306
[mysql.server]
user=mysql
basedir=/usr/local/mysql
@end example
The @code{mysql.server} script uses the following variables:
@code{user}, @code{datadir}, @code{basedir}, @code{bindir}, and
@code{pid-file}.
The following table shows which option sections each of the startup script
uses:
@multitable @columnfractions .20 .80
@item @code{mysqld} @tab @code{mysqld} and @code{server}
@item @code{mysql.server} @tab @code{mysql.server}, @code{mysqld} and @code{server}
@item @code{safe_mysqld} @tab @code{mysql.server}, @code{mysqld} and @code{server}
@end multitable
@xref{Option files}.
@findex command-line options
@cindex options, command-line
@cindex mysqld options
@node Command-line options, Option files, Automatic start, Post-installation
@subsection Command-line Options
@code{mysqld} accepts the following command-line options:
@table @code
@item --ansi
Use ANSI SQL syntax instead of MySQL syntax. @xref{ANSI mode}.
@item -b, --basedir=path
Path to installation directory. All paths are
usually resolved relative to this.
@item --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, @strong{MySQL} is
able to solve it automaticaly by using memory for small temporary
tables and switching to disk tables where necessary.
@item --bind-address=IP
IP address to bind to.
@item --character-sets-dir=path
Directory where character sets are. @xref{Character sets}.
@item --chroot=path
Chroot mysqld daemon during startup.
Recommended security measure. It will somewhat limit @code{LOAD DATA INFILE}
and @code{SELECT ... INTO OUTFILE} though.
@item -h, --datadir=path
Path to the database root.
@item --default-character-set=charset
Set the default character set. @xref{Character sets}.
@item --default-table-type=type
Set the default table type for tables. @xref{Table types}.
@item --delay-key-write-for-all-tables
Don't flush key buffers between writes for any @code{MyISAM} table.
@xref{Server parameters}.
@item --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.
@item -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!
@item --flush
Flush all changes to disk after each SQL command. Normally @strong{MySQL}
only does a write of all changes to disk after each SQL command and lets
the operating system handle the syncing to disk.
@xref{Crashing}.
@item -?, --help
Display short help and exit.
@item --init-file=file
Read SQL commands from this file at startup.
@item -L, --language=...
Client error messages in given language. May be given as a full path.
@xref{Languages}.
@item -l, --log[=file]
Log connections and queries to file. @xref{Query log}.
@item --log-isam[=file]
Log all ISAM/MyISAM changes to file (only used when debugging ISAM/MyISAM).
@item --log-slow-queries[=file]
Log all queries that have taken more than @code{long_query_time} seconds to
execute to file. @xref{Slow query log}.
@item --log-update[=file]
Log updates to @code{file.#} where @code{#} is a unique number if not given.
@xref{Update log}.
@item --log-long-format
Log some extra information to update log. If you are using
@code{--log-slow-queries} then queries that are not using indexes are logged
to the slow query log.
@item --low-priority-updates
Table-modifying operations (@code{INSERT}/@code{DELETE}/@code{UPDATE})
will have lower priority than selects.
It can also be done via @code{@{INSERT | REPLACE | UPDATE | DELETE@}
LOW_PRIORITY ...} to lower the priority of only one query, or by
@code{SET OPTION SQL_LOW_PRIORITY_UPDATES=1} to change the
priority in one thread.
@xref{Table locking}.
@item --memlock
Lock the @code{mysqld} process in memory. This works only if your system
supports the @code{mlockall()} system call. This may help if you have
a problem where the operating system is causing @code{mysqld} to swap on disk.
@item --myisam-recover [=option[,option...]]] where option is one of DEFAULT, BACKUP, FORCE or QUICK.
If this option is used, @code{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 @code{--skip-locking}).
If this is the case mysqld will run check on the table. If the table was
corrupted, @code{mysqld} will attempt to repair it.
The following options affects how the repair works.
@multitable @columnfractions .3 .7
@item DEFAULT @tab The same as not giving any option to @code{--myisam-recover}.
@item BACKUP @tab If the data table was changed during recover, save a backup of the @file{table_name.MYD} data file as @file{table_name-datetime.BAK}.
@item FORCE @tab Run recover even if we will loose more than one row from the .MYD file.
@item QUICK @tab Don't check the rows in the table if there isn't any delete blocks.
@end multitable
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
@code{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.
@item --pid-file=path
Path to pid file used by @code{safe_mysqld}.
@item -P, --port=...
Port number to listen for TCP/IP connections.
@item -o, --old-protocol
Use the 3.20 protocol for compatibility with some very old clients.
@xref{Upgrading-from-3.20}.
@item --one-thread
Only use one thread (for debugging under Linux). @xref{Debugging server}.
@item -O, --set-variable var=option
Give a variable a value. @code{--help} lists variables.
You can find a full description for all variables in the @code{SHOW VARIABLES}
section in this manual. @xref{SHOW VARIABLES}.
The tuning server parameters section includes information of how to optimize
these. @xref{Server parameters}.
@item --safe-mode
Skip some optimize stages.
Implies @code{--skip-delay-key-write}.
@item --safe-show-database
Don't show databases for which the user doesn't have any privileges.
@item --secure
IP numbers returned by the @code{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 @strong{MySQL} Version 3.21
because sometimes it takes a long time to perform backward resolutions.
@strong{MySQL} Version 3.22 caches hostnames (unless @code{--skip-host-cache}
is used) and has this option enabled by default.
@item --skip-concurrent-insert
Turn off the ability to select and insert at the same time on @code{MyISAM}
tables. (This is only to be used if you think you have found a bug
in this feature).
@item --skip-delay-key-write
Ignore the @code{delay_key_write} option for all tables.
@xref{Server parameters}.
@item -Sg, --skip-grant-tables
This option causes the server not to use the privilege system at all. This
gives everyone @emph{full access} to all databases! (You can tell a running
server to start using the grant tables again by executing @code{mysqladmin
flush-privileges} or @code{mysqladmin reload}.)
@item --skip-locking
Don't use system locking. To use @code{isamchk} or @code{myisamchk} you must
shut down the server. @xref{Stability}. Note that in @strong{MySQL} Version
3.23 you can use @code{REPAIR} and @code{CHECK} to repair/check @code{MyISAM}
tables.
@item --skip-name-resolve
Hostnames are not resolved. All @code{Host} column values in the grant
tables must be IP numbers or @code{localhost}. @xref{DNS}.
@item --skip-networking
Don't listen for TCP/IP connections at all.
All interaction with @code{mysqld} must be made via Unix sockets.
This option is highly recommended for systems where only local requests
are allowed. @xref{DNS}.
@item --skip-new
Don't use new, possible wrong routines.
Implies @code{--skip-delay-key-write}.
This will also set default table type to @code{ISAM}. @xref{ISAM}.
@item --skip-host-cache
Never use host name cache for faster name-ip resolution, but query
DNS server on every connect instead. @xref{DNS}.
@item --skip-show-database
Don't allow 'SHOW DATABASE' commands, unless the user has
@strong{process} privilege.
@item --skip-thread-priority
Disable using thread priorities for faster response time.
@item --socket=path
Socket file to use for local connections instead of default
@code{/tmp/mysql.sock}.
@item -t, --tmpdir=path
Path for temporary files. It may be useful if your default @code{/tmp}
directory resides on a partition too small to hold temporary tables.
@item -u, --user=user_name
Run @code{mysqld} daemon as user @code{user_name}. This option is
@emph{mandatory} when starting @code{mysqld} as root.
@item -V, --version
Output version information and exit.
@end table
@cindex default options
@cindex option files
@cindex creating, default startup options
@cindex startup options, default
@node Option files, , Command-line options, Post-installation
@subsection Option Files
@strong{MySQL} Version 3.22 can read default startup options for the
server and for clients from option files.
@strong{MySQL} reads default options from the following files on Unix:
@tindex .my.cnf file
@multitable @columnfractions .3 .7
@item @strong{Filename} @tab @strong{Purpose}
@item @code{/etc/my.cnf} @tab Global options
@item @code{DATADIR/my.cnf} @tab Server-specific options
@item @code{defaults-extra-file} @tab The file specified with --defaults-extra-file=#
@item @code{~/.my.cnf} @tab User-specific options
@end multitable
@code{DATADIR} is the @strong{MySQL} data directory (typically
@file{/usr/local/mysql/data} for a binary installation or
@file{/usr/local/var} for a source installation). Note that this is the
directory that was specified at configuration time, not the one specified
with @code{--datadir} when @code{mysqld} starts up! (@code{--datadir} has no
effect on where the server looks for option files, because it looks for them
before it processes any command-line arguments.)
@strong{MySQL} reads default options from the following files on Windows:
@multitable @columnfractions .3 .7
@item @strong{Filename} @tab @strong{Purpose}
@item @code{windows-system-directorymy.ini} @tab Global options
@item @code{C:my.cnf} @tab Global options
@item @code{C:mysqldatamy.cnf} @tab Server-specific options
@end multitable
Note that on Windows, you should specify all paths with @code{/} instead of
@code{}. If you use @code{}, you need to specify this twice, as
@code{} is the escape character in @strong{MySQL}.
@cindex Environment variables
@strong{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. @xref{Environment variables}.
The following programs support option files: @code{mysql},
@code{mysqladmin}, @code{mysqld}, @code{mysqldump}, @code{mysqlimport},
@code{mysql.server}, @code{myisamchk}, and @code{myisampack}.
You can use option files to specify any long option that a program supports!
Run the program with @code{--help} to get a list of available options.
An option file can contain lines of the following forms:
@table @code
@item #comment
Comment lines start with @samp{#} or @samp{;}. Empty lines are ignored.
@item [group]
@code{group} is the name of the program or group for which you want to set
options. After a group line, any @code{option} or @code{set-variable} lines
apply to the named group until the end of the option file or another group
line is given.
@item option
This is equivalent to @code{--option} on the command line.
@item option=value
This is equivalent to @code{--option=value} on the command line.
@item set-variable = variable=value
This is equivalent to @code{--set-variable variable=value} on the command line.
This syntax must be used to set a @code{mysqld} variable.
@end table
The @code{client} group allows you to specify options that apply to all
@strong{MySQL} clients (not @code{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 @samp{b},
@samp{t}, @samp{n}, @samp{r}, @samp{\}, and @samp{s} in your value string
(@samp{s} == blank).
Here is a typical global option file:
@example
[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
@end example
Here is typical user option file:
@example
[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
@end example
@tindex .my.cnf file
If you have a source distribution, you will find sample configuration
files named @file{my-xxxx.cnf} in the @file{support-files} directory.
If you have a binary distribution, look in the @file{DIR/share/mysql}
directory, where @code{DIR} is the pathname to the @strong{MySQL}
installation directory (typically @file{/usr/local/mysql}). Currently
there are sample configuration files for small, medium, large, and very
large systems. You can copy @file{my-xxxx.cnf} to your home directory
(rename the copy to @file{.my.cnf}) to experiment with this.
All @strong{MySQL} clients that support option files support the
following options:
@multitable @columnfractions .40 .60
@item --no-defaults @tab Don't read any option files.
@item --print-defaults @tab Print the program name and all options that it will get.
@item --defaults-file=full-path-to-default-file @tab Only use the given configuration file.
@item --defaults-extra-file=full-path-to-default-file @tab Read this configuration file after the global configuration file but before the user configuration file.
@end multitable
Note that the above options must be first on the command line to work!
@code{--print-defaults} may however be used directly after the
@code{--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 @strong{MySQL} clients to see
how to do this.
In shell scripts you can use the @file{my_print_defaults} command to parse the
config files:
@example
shell> my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash
@end example
The above output contains all options for the groups 'client' and 'mysql'.
@node Installing many servers, Upgrade, Post-installation, Installing
@section Installing many servers on the same machine
@cindex post-install, many servers
@cindex Installing many servers
@cindex Starting many servers
In some cases you may want to have many different @code{mysqld} deamons
(servers) running on the same machine. You may for example want to run
a new version of @strong{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:
@tindex MYSQL_UNIX_PORT environment variable
@tindex MYSQL_TCP_PORT environment variable
@tindex Environment variable, MYSQL_UNIX_PORT
@tindex Environment variable, MYSQL_TCP_PORT
@example
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 &
@end example
The environment variables appendix includes a list of other environment
variables you can use to affect @code{mysqld}. @xref{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. @xref{Option files}. In your startup script that
is executed at boot time (mysql.server?) you should specify for both
servers:
@code{safe_mysqld --default-file=path-to-option-file}
At least the following options should be different per server:
@table @code
@item port=#
@item socket=path
@item pid-file=path
@end table
The following options should be different, if they are used:
@table @code
@item log=path
@item log-bin=path
@item log-update=path
@item log-isam=path
@item bdb-logdir=path
@end table
If you want more performance, you can also specify the following differently:
@table @code
@item tmpdir=path
@item bdb-tmpdir=path
@end table
@xref{Command-line options}.
If you are installing binary @strong{MySQL} versions (.tar files) and
start them with @code{./bin/safe_mysqld} then in most cases the only
option you need to add/change is the @code{socket} and @code{port}
argument to @code{safe_mysqld}.
@node Upgrade, , Installing many servers, Installing
@section Upgrading/Downgrading MySQL
@cindex upgrading
@cindex downgrading
You can always move the @strong{MySQL} form and data files between
different versions on the same architecture as long as you have the same
base version of @strong{MySQL}. The current base version is
3. If you change the character set when running @strong{MySQL} (which may
also change the sort order), you must run @code{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
@code{mysqld} to something like @code{mysqld}-'old-version-number'. If
your new @code{mysqld} then does something unexpected, you can simply shut it
down and restart with your old @code{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 @code{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 @file{mysql.h} file and
@file{libmysqlclient.a} library to verify that they are from the new
@strong{MySQL} distribution. If not, please recompile your programs!
If you get some problems that the new @code{mysqld} server doesn't want to
start or that you can't connect without a password, check that you don't
have some old @file{my.cnf} file from your old installation! You can
check this with: @code{program-name --print-defaults}. If this outputs
anything other than the program name, you have an active @code{my.cnf}
file that will may affect things!
It is a good idea to rebuild and reinstall the @code{Msql-Mysql-modules}
distribution whenever you install a new release of @strong{MySQL},
particularly if you notice symptoms such as all your @code{DBI} scripts
dumping core after you upgrade @strong{MySQL}.
@menu
* Upgrading-from-3.22:: Upgrading from a 3.22 version to 3.23
* Upgrading-from-3.21:: Upgrading from a 3.21 version to 3.22
* Upgrading-from-3.20:: Upgrading from a 3.20 version to 3.21
* Upgrading-to-arch:: Upgrading to another architecture
@end menu
@cindex compatibility, between MySQL versions
@cindex upgrading, 3.22 to 3.23
@node Upgrading-from-3.22, Upgrading-from-3.21, Upgrade, Upgrade
@subsection Upgrading From Version 3.22 to Version 3.23
@strong{MySQL} Version 3.23 supports tables of the new @code{MyISAM} type and
the old @code{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 @code{MyISAM} (unless you start @code{mysqld} with the
@code{--default-table-type=isam} option). You can change an @code{ISAM}
table to a @code{MyISAM} table with @code{ALTER TABLE} or the Perl script
@code{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:
@itemize @bullet
@item
If you do a @code{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 @code{readlink} system call).
@item
@code{OPTIMIZE TABLE} now only works for @strong{MyISAM} tables.
For other table types, you can use @code{ALTER TABLE} to optimize the table.
During @code{OPTIMIZE TABLE} the table is now locked from other threads.
@item
The @strong{MySQL} client @code{mysql} is now by default started with the
option @code{--no-named-commands (-g)}. This option can be disabled with
@code{--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.
@item
If you are using the @code{german} character sort order, you must repair
all your tables with @code{isamchk -r}, as we have made some changes in
the sort order!
@item The default return type of @code{IF} will now depend on both arguments
and not only the first argument.
@item @code{AUTO_INCREMENT} will not work with negative numbers. The reason
for this is that negative numbers caused problems when wrapping from -1 to 0.
@code{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.
@item @code{CASE}, @code{DELAYED}, @code{ELSE}, @code{END}, @code{FULLTEXT}, @code{INNER}, @code{RIGHT}, @code{THEN} and @code{WHEN} are now reserved words.
@item @code{FLOAT(X)} is now a true floating-point type and not a value with
a fixed number of decimals.
@item When declaring @code{DECIMAL(length,dec)} the length argument no
longer includes a place for the sign or the decimal point.
@item A @code{TIME} string must now be of one of the following formats:
@code{[[[DAYS] [H]H:]MM:]SS[.fraction]} or
@code{[[[[[H]H]H]H]MM]SS[.fraction]}
@item @code{LIKE} now compares strings using the same character
comparison rules as @code{'='}. If you require the old behavior, you
can compile @strong{MySQL} with the @code{CXXFLAGS=-DLIKE_CMP_TOUPPER}
flag.
@item @code{REGEXP} is now case insensitive for normal (not binary) strings.
@item When you check/repair tables you should use @code{CHECK TABLE}
or @code{myisamchk} for @code{MyISAM} tables (@code{.MYI}) and
@code{isamchk} for ISAM (@code{.ISM}) tables.
@item If you want your @code{mysqldump} files to be compatible between
@strong{MySQL} Version 3.22 and Version 3.23, you should not use the
@code{--opt} or @code{--full} option to @code{mysqldump}.
@item Check all your calls to @code{DATE_FORMAT()} to make sure there is a
@samp{%} before each format character. (Later @strong{MySQL} Version 3.22
did allow this syntax.)
@item
@code{mysql_fetch_fields_direct} is now a function (it was a macro) and
it returns a pointer to a @code{MYSQL_FIELD} instead of a
@code{MYSQL_FIELD}.
@item
@code{mysql_num_fields()} can no longer be used on a @code{MYSQL*} object (it's
now a function that takes @code{MYSQL_RES*} as an argument. You should now
use @code{mysql_field_count()} instead.
@item
In @strong{MySQL} Version 3.22, the output of @code{SELECT DISTINCT ...} was
almost always sorted. In Version 3.23, you must use @code{GROUP BY} or
@code{ORDER BY} to obtain sorted output.
@item
@code{SUM()} now returns @code{NULL}, instead of 0, if there is no matching
rows. This is according to ANSI SQL.
@item An @code{AND} or @code{OR} with @code{NULL} values will now return
@code{NULL} instead of 0. This mostly affects queries that use @code{NOT}
on an @code{AND/OR} expression as @code{NOT NULL} = @code{NULL}.
@code{LPAD()} and @code{RPAD()} will shorten the result string if it's longer
than the length argument.
@end itemize
@cindex compatibility, between MySQL versions
@node Upgrading-from-3.21, Upgrading-from-3.20, Upgrading-from-3.22, Upgrade
@subsection Upgrading from Version 3.21 to Version 3.22
@cindex upgrading, 3.21 to 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 @code{DATE} type
columns will use the new way to store the date. You can't access these new
fields from an old version of @code{mysqld}.
After installing @strong{MySQL} Version 3.22, you should start the new server
and then run the @code{mysql_fix_privilege_tables} script. This will add the
new privileges that you need to use the @code{GRANT} command. If you forget
this, you will get @code{Access denied} when you try to use @code{ALTER
TABLE}, @code{CREATE INDEX}, or @code{DROP INDEX}. If your @strong{MySQL} root
user requires a password, you should give this as an argument to
@code{mysql_fix_privilege_tables}.
The C API interface to @code{mysql_real_connect()} has changed. If you have
an old client program that calls this function, you must place a @code{0} for
the new @code{db} argument (or recode the client to send the @code{db}
element for faster connections). You must also call @code{mysql_init()}
before calling @code{mysql_real_connect()}! This change was done to allow
the new @code{mysql_options()} function to save options in the @code{MYSQL}
handler structure.
The @code{mysqld} variable @code{key_buffer} has changed names to
@code{key_buffer_size}, but you can still use the old name in your
startup files.
@node Upgrading-from-3.20, Upgrading-to-arch, Upgrading-from-3.21, Upgrade
@subsection Upgrading from Version 3.20 to Version 3.21
@cindex upgrading, 3.20 to 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 @code{mysqld} Version 3.21 server with @code{safe_mysqld
--old-protocol} to use it with clients from a Version 3.20 distribution.
In this case, the new client function @code{mysql_errno()} will not
return any server error, only @code{CR_UNKNOWN_ERROR} (but it
works for client errors), and the server uses the old @code{password()}
checking rather than the new one.
If you are @strong{NOT} using the @code{--old-protocol} option to
@code{mysqld}, you will need to make the following changes:
@itemize @bullet
@item
All client code must be recompiled. If you are using ODBC, you must get
the new @strong{MyODBC} 2.x driver.
@item
The script @code{scripts/add_long_password} must be run to convert the
@code{Password} field in the @code{mysql.user} table to @code{CHAR(16)}.
@item
All passwords must be reassigned in the @code{mysql.user} table (to get 62-bit
rather than 31-bit passwords).
@item
The table format hasn't changed, so you don't have to convert any tables.
@end itemize
@strong{MySQL} Version 3.20.28 and above can handle the new @code{user} table
format without affecting clients. If you have a @strong{MySQL} version earlier
than Version 3.20.28, passwords will no longer work with it if you convert the
@code{user} table. So to be safe, you should first upgrade to at least Version
3.20.28 and then upgrade to Version 3.21.
@cindex Protocol mismatch
The new client code works with a 3.20.x @code{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 @code{--old-protocol} option to @code{mysqld},
old clients will issue the error message:
@example
ERROR: Protocol mismatch. Server Version = 10 Client Version = 9
@end example
The new Perl @code{DBI}/@code{DBD} interface also supports the old
@code{mysqlperl} interface. The only change you have to make if you use
@code{mysqlperl} is to change the arguments to the @code{connect()} function.
The new arguments are: @code{host}, @code{database}, @code{user},
@code{password} (the @code{user} and @code{password} arguments have changed
places).
@xref{Perl DBI Class, , Perl @code{DBI} Class}.
The following changes may affect queries in old applications:
@itemize @bullet
@item
@code{HAVING} must now be specified before any @code{ORDER BY} clause.
@item
The parameters to @code{LOCATE()} have been swapped.
@item
There are some new reserved words. The most notable are @code{DATE},
@code{TIME}, and @code{TIMESTAMP}.
@end itemize
@cindex upgrading, different architecture
@node Upgrading-to-arch, , Upgrading-from-3.20, Upgrade
@subsection Upgrading to Another Architecture
If you are using @strong{MySQL} Version 3.23, you can copy the @code{.frm},
@code{.MYI}, and @code{.MYD} files between different architectures that
support the same floating-point format. (@strong{MySQL} takes care of any
byte swapping issues.)
The @strong{MySQL} @code{ISAM} data and index files (@file{.ISD} and
@file{*.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 @code{mysqldump} instead.
By default, @code{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 @code{mysql} client.
Try @code{mysqldump --help} to see what options are available.
If you are moving the data to a newer version of @strong{MySQL}, you should use
@code{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:
@example
shell> mysqladmin -h 'other hostname' create db_name
shell> mysqldump --opt db_name
| mysql -h 'other hostname' db_name
@end example
If you want to copy a database from a remote machine over a slow network,
you can use:
@example
shell> mysqladmin create db_name
shell> mysqldump -h 'other hostname' --opt --compress db_name
| mysql db_name
@end example
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:
@example
shell> mysqldump --quick db_name | gzip > db_name.contents.gz
@end example
(The file created in this example is compressed.) Transfer the file
containing the database contents to the target machine and run these commands
there:
@example
shell> mysqladmin create db_name
shell> gunzip < db_name.contents.gz | mysql db_name
@end example
@cindex @code{mysqldump}
@cindex @code{mysqlimport}
You can also use @code{mysqldump} and @code{mysqlimport} to accomplish
the database transfer.
For big tables, this is much faster than simply using @code{mysqldump}.
In the commands shown below, @code{DUMPDIR} represents the full pathname
of the directory you use to store the output from @code{mysqldump}.
First, create the directory for the output files and dump the database:
@example
shell> mkdir DUMPDIR
shell> mysqldump --tab=DUMPDIR db_name
@end example
Then transfer the files in the @code{DUMPDIR} directory to some corresponding
directory on the target machine and load the files into @strong{MySQL}
there:
@example
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
@end example
Also, don't forget to copy the @code{mysql} database, because that's where the
grant tables (@code{user}, @code{db}, @code{host}) are stored. You may have
to run commands as the @strong{MySQL} @code{root} user on the new machine
until you have the @code{mysql} database in place.
After you import the @code{mysql} database on the new machine, execute
@code{mysqladmin flush-privileges} so that the server reloads the grant table
information.
@cindex compatibility, with ANSI SQL
@cindex standards compatibility
@cindex extensions, to ANSI SQL
@cindex ANSI SQL92, extensions to
@node Compatibility, Privilege system, Installing, Top
@chapter How Standards-compatible Is MySQL?
@menu
* Extensions to ANSI:: @strong{MySQL} extensions to ANSI SQL92
* ANSI mode:: Running @strong{MySQL} in ANSI mode
* Differences from ANSI:: @strong{MySQL} differences compared to ANSI SQL92
* Missing functions:: Functionality missing from @strong{MySQL}
* Standards:: What standards does @strong{MySQL} follow?
* Commit-rollback:: How to cope without @code{COMMIT}-@code{ROLLBACK}
@end menu
@node Extensions to ANSI, ANSI mode, Compatibility, Compatibility
@section MySQL Extensions to ANSI SQL92
@strong{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 @strong{MySQL} extensions, but is still portable, by using comments
of the form @code{/*! ... */}. In this case, @strong{MySQL} will parse and
execute the code within the comment as it would any other @strong{MySQL}
statement, but other SQL servers will ignore the extensions. For example:
@example
SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...
@end example
If you add a version number after the @code{'!'}, the syntax will only be
executed if the @strong{MySQL} version is equal to or newer than the used
version number:
@example
CREATE /*!32302 TEMPORARY */ TABLE (a int);
@end example
The above means that if you have Version 3.23.02 or newer, then @strong{MySQL}
will use the @code{TEMPORARY} keyword.
@strong{MySQL} extensions are listed below:
@itemize @bullet
@item
The field types @code{MEDIUMINT}, @code{SET}, @code{ENUM}, and the
different @code{BLOB} and @code{TEXT} types.
@item
The field attributes @code{AUTO_INCREMENT}, @code{BINARY}, @code{NULL},
@code{UNSIGNED}, and @code{ZEROFILL}.
@item
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 @code{BINARY} attribute or use the @code{BINARY} cast, which causes
comparisons to be done according to the ASCII order used on the
@strong{MySQL} server host.
@item
@strong{MySQL} maps each database to a directory under the @strong{MySQL}
data directory, and tables within a database to filenames in the database
directory.
This has a few implications:
@cindex database names, case sensitivity
@cindex table names, case sensitivity
@cindex case sensitivity, of database names
@cindex case sensitivity, of table names
@itemize @minus
@item
Database names and table names are case sensitive in @strong{MySQL} on
operating systems that have case-sensitive filenames (like most Unix
systems). @xref{Name case sensitivity}.
@item
Database, table, index, column, or alias names may begin with a digit
(but may not consist solely of digits).
@item
You can use standard system commands to backup, rename, move, delete, and copy
tables. For example, to rename a table, rename the @file{.MYD}, @file{.MYI},
and @file{.frm} files to which the table corresponds.
@end itemize
@item
In SQL statements, you can access tables from different databases
with the @code{db_name.tbl_name} syntax. Some SQL servers provide
the same functionality but call this @code{User space}.
@strong{MySQL} doesn't support tablespaces as in:
@code{create table ralph.my_table...IN my_tablespace}.
@item
@code{LIKE} is allowed on numeric columns.
@item
Use of @code{INTO OUTFILE} and @code{STRAIGHT_JOIN} in a @code{SELECT}
statement. @xref{SELECT, , @code{SELECT}}.
@item
The @code{SQL_SMALL_RESULT} option in a @code{SELECT} statement.
@item
@code{EXPLAIN SELECT} to get a description on how tables are joined.
@item
Use of index names, indexes on a prefix of a field, and use of
@code{INDEX} or @code{KEY} in a @code{CREATE TABLE}
statement. @xref{CREATE TABLE, , @code{CREATE TABLE}}.
@item
Use of @code{TEMPORARY} or @code{IF NOT EXISTS} with @code{CREATE TABLE}.
@item
Use of @code{COUNT(DISTINCT list)} where 'list' is more than one element.
@item
Use of @code{CHANGE col_name}, @code{DROP col_name}, or @code{DROP
INDEX}, @code{IGNORE} or @code{RENAME} in an @code{ALTER TABLE}
statement. @xref{ALTER TABLE, , @code{ALTER TABLE}}.
@item
Use of @code{RENAME TABLE}. @xref{RENAME TABLE, , @code{RENAME TABLE}}.
@item
Use of multiple @code{ADD}, @code{ALTER}, @code{DROP}, or @code{CHANGE}
clauses in an @code{ALTER TABLE} statement.
@item
Use of @code{DROP TABLE} with the keywords @code{IF EXISTS}.
@item
You can drop multiple tables with a single @code{DROP TABLE} statement.
@item
The @code{LIMIT} clause of the @code{DELETE} statement.
@item
The @code{DELAYED} clause of the @code{INSERT} and @code{REPLACE}
statements.
@item
The @code{LOW_PRIORITY} clause of the @code{INSERT}, @code{REPLACE},
@code{DELETE}, and @code{UPDATE} statements.
@cindex Oracle compatibility
@cindex compatibility, with Oracle
@item
Use of @code{LOAD DATA INFILE}. In many cases, this syntax is compatible with
Oracle's @code{LOAD DATA INFILE}. @xref{LOAD DATA, , @code{LOAD DATA}}.
@item
The @code{ANALYZE TABLE}, @code{CHECK TABLE}, @code{OPTIMIZE TABLE}, and
@code{REPAIR TABLE} statements.
@item
The @code{SHOW} statement.
@xref{SHOW, , @code{SHOW}}.
@item
Strings may be enclosed by either @samp{"} or @samp{'}, not just by @samp{'}.
@item
Use of the escape @samp{} character.
@item
The @code{SET OPTION} statement. @xref{SET OPTION, , @code{SET OPTION}}.
@item
You don't need to name all selected columns in the @code{GROUP BY} part.
This gives better performance for some very specific, but quite normal
queries.
@xref{Group by functions}.
@item
One can specify @code{ASC} and @code{DESC} with @code{GROUP BY}.
@item
To make it easier for users who come from other SQL environments,
@strong{MySQL} supports aliases for many functions. For example, all
string functions support both ANSI SQL syntax and ODBC syntax.
@item
@strong{MySQL} understands the @code{||} and @code{&&} operators to mean
logical OR and AND, as in the C programming language. In @strong{MySQL},
@code{||} and @code{OR} are synonyms, as are @code{&&} and @code{AND}.
Because of this nice syntax, @strong{MySQL} doesn't support
the ANSI SQL @code{||} operator for string concatenation; use
@code{CONCAT()} instead. Because @code{CONCAT()} takes any number
of arguments, it's easy to convert use of the @code{||} operator to
@strong{MySQL}.
@item
@code{CREATE DATABASE} or @code{DROP DATABASE}.
@xref{CREATE DATABASE, , @code{CREATE DATABASE}}.
@cindex PostgreSQL compatibility
@cindex compatibility, with PostgreSQL
@item
The @code{%} operator is a synonym for @code{MOD()}. That is,
@code{N % M} is equivalent to @code{MOD(N,M)}. @code{%} is supported
for C programmers and for compatibility with PostgreSQL.
@item
The @code{=}, @code{<>}, @code{<=} ,@code{<}, @code{>=},@code{>},
@code{<<}, @code{>>}, @code{<=>}, @code{AND}, @code{OR}, or @code{LIKE}
operators may be used in column comparisons to the left of the
@code{FROM} in @code{SELECT} statements. For example:
@example
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
@end example
@item
The @code{LAST_INSERT_ID()} function.
@xref{mysql_insert_id, , @code{mysql_insert_id()}}.
@item
The @code{REGEXP} and @code{NOT REGEXP} extended regular expression
operators.
@item
@code{CONCAT()} or @code{CHAR()} with one argument or more than two
arguments. (In @strong{MySQL}, these functions can take any number of
arguments.)
@item The @code{BIT_COUNT()}, @code{CASE}, @code{ELT()},
@code{FROM_DAYS()}, @code{FORMAT()}, @code{IF()}, @code{PASSWORD()},
@code{ENCRYPT()}, @code{md5()}, @code{ENCODE()}, @code{DECODE()},
@code{PERIOD_ADD()}, @code{PERIOD_DIFF()}, @code{TO_DAYS()}, or
@code{WEEKDAY()} functions.
@item
Use of @code{TRIM()} to trim substrings. ANSI SQL only supports removal
of single characters.
@item
The @code{GROUP BY} functions @code{STD()}, @code{BIT_OR()}, and
@code{BIT_AND()}.
@item
Use of @code{REPLACE} instead of @code{DELETE} + @code{INSERT}.
@xref{REPLACE, , @code{REPLACE}}.
@item
The @code{FLUSH flush_option} statement.
@item
The possiblity to set variables in a statement with @code{:=}:
@example
SELECT @@a:=SUM(total),@@b=COUNT(*),@@a/@@b AS avg FROM test_table;
SELECT @@t1:=(@@t2:=1)+@@t3:=4,@@t1,@@t2,@@t3;
@end example
@end itemize
@node ANSI mode, Differences from ANSI, Extensions to ANSI, Compatibility
@section Running MySQL in ANSI Mode
@cindex running, ANSI mode
@cindex ANSI mode, running
If you start mysqld with the @code{--ansi} option, the following behavior
of @strong{MySQL} changes:
@itemize @bullet
@item
@code{||} is string concatenation instead of @code{OR}.
@item
You can have any number of spaces between a function name and the @samp{(}.
This forces all function names to be treated as reserved words.
@item
@samp{"} will be an identifier quote character (like the @strong{MySQL}
@samp{`} quote character) and not a string quote character.
@item
@code{REAL} will be a synonym for @code{FLOAT} instead of a synonym of
@code{DOUBLE}.
@end itemize
@node Differences from ANSI, Missing functions, ANSI mode, Compatibility
@section MySQL Differences Compared to ANSI SQL92
We try to make @strong{MySQL} follow the ANSI SQL standard and the
ODBC SQL standard, but in some cases @strong{MySQL} does some things
differently:
@itemize @bullet
@item
@code{--} is only a comment if followed by a white space. @xref{Missing
comments}.
@item
For @code{VARCHAR} columns, trailing spaces are removed when the value is
stored. @xref{Bugs}.
@item
In some cases, @code{CHAR} columns are silently changed to @code{VARCHAR}
columns. @xref{Silent column changes}.
@item
Privileges for a table are not automatically revoked when you delete a
table. You must explicitly issue a @code{REVOKE} to revoke privileges for
a table. @xref{GRANT, , @code{GRANT}}.
@item
@code{NULL AND FALSE} will evaluate to @code{NULL} and not to @code{FALSE}.
This is because we don't think it's good to have to evaluate a lot of
extra conditions in this case.
@end itemize
@node Missing functions, Standards, Differences from ANSI, Compatibility
@section Functionality Missing from MySQL
@cindex missing functionality
@cindex functionality, missing
The following functionality is missing in the current version of
@strong{MySQL}. For a prioritized list indicating when new extensions
may be added to @strong{MySQL}, you should consult
@uref{http://www.mysql.com/documentation/manual.php?section=TODO, the
online @strong{MySQL} TODO list}. That is the latest version of the TODO
list in this manual. @xref{TODO}.
@menu
* Missing Sub-selects:: Sub-selects
* Missing SELECT INTO TABLE:: @code{SELECT INTO TABLE}
* Missing Transactions:: Transactions
* Missing Triggers:: Triggers
* Missing Foreign Keys:: Foreign Keys
* Missing Views:: Views
* Missing comments:: @samp{--} as the start of a comment
@end menu
@node Missing Sub-selects, Missing SELECT INTO TABLE, Missing functions, Missing functions
@subsection Sub-selects
@cindex sub-selects
The following will not yet work in @strong{MySQL}:
@example
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);
@end example
However, in many cases you can rewrite the query without a sub-select:
@example
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
@end example
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
@code{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 @strong{MySQL}.
The first option is to use a procedural programming language (such as
Perl or PHP) to submit a @code{SELECT} query to obtain the primary keys
for the records to be deleted, and then use these values to construct
the @code{DELETE} statement (@code{DELETE FROM ... WHERE ... IN (key1,
key2, ...)}).
The second option is to use interactive SQL to contruct a set of
@code{DELETE} statements automatically, using the @strong{MySQL}
extension @code{CONCAT()} (in lieu of the standard @code{||} operator).
For example:
@example
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
@end example
You can place this query in a script file and redirect input from it to
the @code{mysql} command-line interpreter, piping its output back to a
second instance of the interpreter:
@example
prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb
@end example
@strong{MySQL} only supports @code{INSERT ... SELECT ...} and
@code{REPLACE ... SELECT ...} Independent sub-selects will probably
be available in Version 4.0. You can now use the function @code{IN()} in
other contexts, however.
@node Missing SELECT INTO TABLE, Missing Transactions, Missing Sub-selects, Missing functions
@subsection @code{SELECT INTO TABLE}
@findex SELECT INTO TABLE
@strong{MySQL} doesn't yet support the Oracle SQL extension:
@code{SELECT ... INTO TABLE ...}. @strong{MySQL} supports instead the
ANSI SQL syntax @code{INSERT INTO ... SELECT ...}, which is basically
the same thing. @xref{INSERT SELECT}.
@example
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;
@end example
Alternatively, you can use @code{SELECT INTO OUTFILE...} or @code{CREATE
TABLE ... SELECT} to solve your problem.
@node Missing Transactions, Missing Triggers, Missing SELECT INTO TABLE, Missing functions
@subsection Transactions
@cindex transactions, support
As @strong{MySQL} does nowadays support transactions, the following
discussion is only valid if you are only using the non-transaction-safe
table types. @xref{COMMIT}.
The question is often asked, by the curious and the critical, ``Why is
@strong{MySQL} not a transactional database?'' or ``Why does @strong{MySQL}
not support transactions?''
@strong{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 @strong{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.
@strong{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
@strong{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 @code{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 @strong{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 @strong{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 @strong{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 @strong{MySQL}'s stability and reliability.
Lastly, in situations where integrity is of highest importance,
@strong{MySQL}'s current features allow for transaction-level or better
reliability and integrity. If you lock tables with @code{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 @code{READ} lock until they release their read
locks. With @code{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. @xref{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). @strong{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. @xref{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 @strong{MySQL}, there is a ``@strong{MySQL}
way'' as we have outlined above. For those where safety is more
important than speed, we recommend them to use the @code{BDB},
@code{GEMINI} or @code{INNOBASE} tables for all their critical
data. @xref{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.
@node Missing Triggers, Missing Foreign Keys, Missing Transactions, Missing functions
@subsection Stored Procedures and Triggers
@cindex stored procedures and triggers, defined
@cindex procedures, stored
@cindex triggers, stored
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 @strong{MySQL} might get stored procedures, see @ref{TODO}.
@node Missing Foreign Keys, Missing Views, Missing Triggers, Missing functions
@subsection Foreign Keys
@cindex foreign keys
@cindex keys, foreign
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 @code{SELECT}
statement, you do this by joining tables:
@example
SELECT * from table1,table2 where table1.id = table2.id;
@end example
@xref{JOIN, , @code{JOIN}}. @xref{example-Foreign keys}.
The @code{FOREIGN KEY} syntax in @strong{MySQL} exists only for compatibility
with other SQL vendors' @code{CREATE TABLE} commands; it doesn't do
anything. The @code{FOREIGN KEY} syntax without @code{ON DELETE ...} is
mostly used for documentation purposes. Some ODBC applications may use this
to produce automatic @code{WHERE} clauses, but this is usually easy to
override. @code{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. @strong{MySQL} only supports these clauses because some
applications require them to exist (regardless of whether or not they
work).
In @strong{MySQL}, you can work around the problem of @code{ON DELETE
...} not being implemented by adding the appropriate @code{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 @code{FOREIGN KEY} implementation so
that at least the information will be saved in the table specification file
and may be retrieved by @code{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.
@menu
* Broken Foreign KEY:: Reasons NOT to use foreign keys constraints
@end menu
@node Broken Foreign KEY, , Missing Foreign Keys, Missing Foreign Keys
@subsubsection Reasons NOT to Use Foreign Keys constraints
@cindex foreign keys, reasons not to use
There are so many problems with foreign key constraints that we don't
know where to start:
@itemize @bullet
@item
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.
@item
The speed impact is terrible for @code{INSERT} and @code{UPDATE}
statements, and in this case almost all @code{FOREIGN KEY} constraint
checks are useless because you usually insert records in the right
tables in the right order, anyway.
@item
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.
@item
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).
@item
If you use foreign key constraints you can't dump and restore tables
unless you do so in a very specific order.
@item
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.
@item
It's very easy to overlook @code{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 @code{ON DELETE} rule.
@end itemize
The only nice aspect of @code{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.
@strong{MySQL} will soon store @code{FOREIGN KEY} definitions so that a
client can ask for and receive an answer about how the original
connection was made. The current @file{.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.
@node Missing Views, Missing comments, Missing Foreign Keys, Missing functions
@subsection Views
@cindex views
@strong{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 @strong{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 @strong{MySQL} to restrict access to columns
as @strong{MySQL} has a very sophisticated privilege
system. @xref{Privilege system}.
@node Missing comments, , Missing Views, Missing functions
@subsection @samp{--} as the Start of a Comment
@cindex comments, starting
@cindex starting, comments
Some other SQL databases use @samp{--} to start comments. @strong{MySQL}
has @samp{#} as the start comment character, even if the @code{mysql}
command-line tool removes all lines that start with @samp{--}.
You can also use the C comment style @code{/* this is a comment */} with
@strong{MySQL}.
@xref{Comments}.
@strong{MySQL} Version 3.23.3 and above supports the @samp{--} 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
@code{!payment!}:
@example
UPDATE tbl_name SET credit=credit-!payment!
@end example
What do you think will happen when the value of @code{payment} is negative?
Because @code{1--1} is legal in SQL, we think it is terrible that
@samp{--} means start comment.
In @strong{MySQL} Version 3.23 you can, however, use:
@code{1-- This is a comment}
The following discussion only concerns you if you are running a @strong{MySQL}
version earlier than Version 3.23:
If you have a SQL program in a text file that contains @samp{--} comments
you should use:
@example
shell> replace " --" " #" < text-file-with-funny-comments.sql
| mysql database
@end example
instead of the usual:
@example
shell> mysql database < text-file-with-funny-comments.sql
@end example
You can also edit the command file ``in place'' to change the @samp{--}
comments to @samp{#} comments:
@example
shell> replace " --" " #" -- text-file-with-funny-comments.sql
@end example
Change them back with this command:
@example
shell> replace " #" " --" -- text-file-with-funny-comments.sql
@end example
@node Standards, Commit-rollback, Missing functions, Compatibility
@section What Standards Does MySQL Follow?
Entry level SQL92. ODBC levels 0-2.
@node Commit-rollback, , Standards, Compatibility
@section How to Cope Without @code{COMMIT}/@code{ROLLBACK}
@findex COMMIT
@findex ROLLBACK
@cindex transaction-safe tables
@cindex tables, updating
@cindex updating, tables
@cindex @code{BDB} tables
@cindex @code{GEMINI} tables
@cindex @code{INNOBASE} tables
The following mostly applies only for @code{ISAM}, @code{MyISAM}, and
@code{HEAP} tables. If you only use transaction-safe tables (@code{BDB},
@code{GEMINI} or @code{INNOBASE} tables) in an a update, you can do
@code{COMMIT} and @code{ROLLBACK} also with @strong{MySQL}.
@xref{COMMIT}.
The problem with handling @code{COMMIT}-@code{ROLLBACK} efficiently with
the above table types would require a completely different table layout
than @strong{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
@code{COMMIT}-@code{ROLLBACK.} This would also give much better performance.
Loops that need transactions normally can be coded with the help of
@code{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 @code{ROLLBACK}. Without
@code{ROLLBACK}, you can do any kind of @code{COMMIT} action with
@code{LOCK TABLES}. To support @code{ROLLBACK} with the above table
types, @strong{MySQL} would have to be changed to store all old records
that were updated and revert everything back to the starting point if
@code{ROLLBACK} was issued. For simple cases, this isn't that hard to do
(the current @code{isamlog} could be used for this purpose), but it
would be much more difficult to implement @code{ROLLBACK} for
@code{ALTER/DROP/CREATE TABLE}.
To avoid using @code{ROLLBACK}, you can use the following strategy:
@enumerate
@item
Use @code{LOCK TABLES ...} to lock all the tables you want to access.
@item
Test conditions.
@item
Update if everything is okay.
@item
Use @code{UNLOCK TABLES} to release your locks.
@end enumerate
This is usually a much faster method than using transactions with possible
@code{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:
@itemize @bullet
@item Modify fields relative to their current value.
@item Update only those fields that actually have changed.
@end itemize
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
@code{WHERE} clause in the @code{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 @code{UPDATE}
statements look something like these:
@example
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';
@end example
As you can see, this is very efficient and works even if another client has
changed the values in the @code{pay_back} or @code{money_he_owes_us} columns.
@findex mysql_insert_id()
@findex LAST_INSERT_ID()
In many cases, users have wanted @code{ROLLBACK} and/or @code{LOCK
TABLES} for the purpose of managing unique identifiers for some tables. This
can be handled much more efficiently by using an @code{AUTO_INCREMENT} column
and either the SQL function @code{LAST_INSERT_ID()} or the C API function
@code{mysql_insert_id()}. @xref{mysql_insert_id, , @code{mysql_insert_id()}}.
@cindex rows, locking
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:
@example
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
@end example
@strong{MySQL} returns 1 for the number of affected rows if the row was
found and @code{row_flag} wasn't already 1 in the original row.
You can think of it as @strong{MySQL} changed the above query to:
@example
UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;
@end example
@node Privilege system, Reference, Compatibility, Top
@chapter The MySQL Access Privilege System
@cindex system, security
@cindex access privileges
@cindex privleges, access
@cindex security system
@cindex ACLs
@strong{MySQL} has an advanced but non-standard security/privilege
system. This section describes how it works.
@menu
* General security:: General security
* Security:: How to make @strong{MySQL} secure against crackers
* Privileges options::
* What Privileges:: What the privilege system does
* User names:: @strong{MySQL} user names and passwords
* Connecting:: Connecting to the @strong{MySQL} server
* Password security:: Keeping your password secure
* Privileges provided:: Privileges provided by @strong{MySQL}
* Privileges:: How the privilege system works
* Connection access:: Access control, stage 1: Connection verification
* Request access:: Access control, stage 2: Request verification
* Privilege changes:: When privilege changes take effect
* Default privileges:: Setting up the initial @strong{MySQL} privileges
* Adding users:: Adding new user privileges to @strong{MySQL}
* Passwords:: How to set up passwords
* Access denied:: Causes of @code{Access denied} errors
@end menu
@node General security, Security, Privilege system, Privilege system
@section General Security
Anyone using @strong{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 @strong{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.
@strong{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 @strong{MySQL} clients and servers. Many of the concepts
discussed here are not specific to @strong{MySQL} at all; the same
general ideas apply to almost all applications.
When running @strong{MySQL}, follow these guidelines whenever possible:
@itemize @bullet
@item
DON'T EVER GIVE ANYONE (EXCEPT THE @strong{MySQL} ROOT USER) ACCESS TO THE
mysql.user TABLE! The encrypted password is the real password in
@strong{MySQL}. If you know this for one user you can easily login as
him if you have access to his 'host'.
@item
Learn the @strong{MySQL} access privilege system. The @code{GRANT} and
@code{REVOKE} commands are used for restricting access to @strong{MySQL}. Do
not grant any more privileges than necessary. Never grant privileges to all
hosts.
Checklist:
@itemize @minus
@item
Try @code{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 @strong{MySQL} server with full privileges!
Review the @strong{MySQL} installation instructions, paying particular
attention to the item about setting a @code{root} password.
@item
Use the command @code{SHOW GRANTS} and check to see who has access to
what. Remove those privileges that are not necessary using the @code{REVOKE}
command.
@end itemize
@item
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 @code{MD5()} or another one-way
hashing function.
@item
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.
@item
Invest in a firewall. This protects from at least 50% of all types of
exploits in any software. Put @strong{MySQL} behind the firewall or in
a demilitarized zone (DMZ).
Checklist:
@itemize @minus
@item
Try to scan your ports from the Internet using a tool such as
@code{nmap}. @strong{MySQL} uses port 3306 by default. This port should
be inaccessible from untrusted hosts. Another simple way to check whether or
not your @strong{MySQL} port is open is to type @code{telnet
server_host 3306} from some remote machine, where
@code{server_host} is the hostname of your @strong{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 @code{telnet} just hangs,
everything is OK, the port is blocked.
@end itemize
@item
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 ``@code{; 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: @code{SELECT * FROM table
WHERE ID='234'} instead of @code{SELECT * FROM table WHERE ID=234}.
@strong{MySQL} automatically converts this string to a number and
strips all non-numeric symbols from it.
Checklist:
@itemize @minus
@item
All WWW applications:
@itemize @bullet
@item
Try to enter @samp{'} and @samp{"} in all your Web forms. If you get any kind
of @strong{MySQL} error, investigate the problem right away.
@item
Try to modify any dynamic URLs by adding @code{%22} (@samp{"}), @code{%23}
(@samp{#}), and @code{%27} (@samp{'}) in the URL.
@item
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.
@item
Try to enter characters, spaces, and special symbols instead of numbers in
numeric fields. Your application should remove them before passing them to
@strong{MySQL} or your application should generate an error. Passing
unchecked values to @strong{MySQL} is very dangerous!
@item
Check data sizes before passing them to @strong{MySQL}.
@item
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.
@end itemize
@item
Users of PHP:
@itemize @bullet
@item Check out the @code{addslashes()} function.
@end itemize
@item
Users of @strong{MySQL} C API:
@itemize @bullet
@item Check out the @code{mysql_escape()} API call.
@end itemize
@item
Users of @strong{MySQL}++:
@itemize @bullet
@item Check out the @code{escape} and @code{quote} modifiers for query streams.
@end itemize
@item
Users of Perl DBI:
@itemize @bullet
@item Check out the @code{quote()} method.
@end itemize
@end itemize
@item
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. @strong{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.
@item
Learn to use the @code{tcpdump} and @code{strings} utilities. For most cases,
you can check whether or not @strong{MySQL} data streams are unencrypted
by issuing a command like the following:
@example
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings
@end example
(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.
@end itemize
@node Security, Privileges options, General security, Privilege system
@section How to Make MySQL Secure Against Crackers
@cindex crackers, security against
@cindex security, against crackers
When you connect to a @strong{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 @strong{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 @strong{MySQL} Version 3.22 and above)
to make things much harder. To make things even more secure you should
use @code{ssh} (see @uref{http://www.cs.hut.fi/ssh}). With this, you
can get an encrypted TCP/IP connection between a @strong{MySQL} server
and a @strong{MySQL} client.
To make a @strong{MySQL} system secure, you should strongly consider the
following suggestions:
@itemize @bullet
@item
Use passwords for all @strong{MySQL} users. Remember that anyone can log in
as any other person as simply as @code{mysql -u other_user db_name} if
@code{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 @code{mysql_install_db} script before
you run it, or only the password for the @strong{MySQL} @code{root} user like
this:
@example
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;
@end example
@item
Don't run the @strong{MySQL} daemon as the Unix @code{root} user.
It is very dangerous as any user with @code{FILE} privileges will be able to
create files
as @code{root} (for example, @code{~root/.bashrc}). To prevent this
@code{mysqld} will refuse to run as @code{root} unless it is specified
directly via @code{--user=root} option.
@code{mysqld} can be run as any user instead. You can also create a new
Unix user @code{mysql} to make everything even more secure. If you run
@code{mysqld} as another Unix user, you don't need to change the
@code{root} user name in the @code{user} table, because @strong{MySQL}
user names have nothing to do with Unix user names. You can edit the
@code{mysql.server} script to start @code{mysqld} as another Unix user.
Normally this is done with the @code{su} command. For more details, see
@ref{Changing MySQL user, , Changing @strong{MySQL} user}.
@item
If you put a password for the Unix @code{root} user in the @code{mysql.server}
script, make sure this script is readable only by @code{root}.
@item
Check that the Unix user that @code{mysqld} runs as is the only user with
read/write privileges in the database directories.
@item
On Unix platforms, do not run @code{mysqld} as root unless you really
need to. Consider creating a user named @code{mysql} for that purpose.
@item
Don't give the @strong{process} privilege to all users. The output of
@code{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 @code{UPDATE user SET
password=PASSWORD('not_secure')} query.
@code{mysqld} reserves an extra connection for users who have the
@strong{process} privilege, so that a @strong{MySQL} @code{root} user can log
in and check things even if all normal connections are in use.
@item
Don't give the @strong{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 @code{mysqld} daemon! To make this a bit safer, all files generated with
@code{SELECT ... INTO OUTFILE} are readable to everyone, and you can't
overwrite existing files.
@tindex /etc/passwd
The @strong{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 @code{LOAD DATA} to load @file{/etc/passwd} into a table, which
can then be read with @code{SELECT}.
@item
If you don't trust your DNS, you should use IP numbers instead of hostnames
in the grant tables. In principle, the @code{--secure} option to
@code{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!
@item
If you want to restrict the number of connections for a single user, you
can do this by setting the @code{max_user_connections} variable in
@code{mysqld}.
@end itemize
@node Privileges options, What Privileges, Security, Privilege system
@section Startup options to mysqld which concerns security
The following @code{mysqld} options affect networking security:
@table @code
@item --secure
IP numbers returned by the @code{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 @strong{MySQL} Version
3.21 because sometimes it takes a long time to perform backward resolutions.
@strong{MySQL} Version 3.22 caches hostnames and has this option enabled by
default.
@item --skip-grant-tables
This option causes the server not to use the privilege system at all. This
gives everyone @emph{full access} to all databases! (You can tell a running
server to start using the grant tables again by executing @code{mysqladmin
flush-privileges} or @code{mysqladmin reload}.)
@item --skip-name-resolve
Hostnames are not resolved. All @code{Host} column values in the grant
tables must be IP numbers or @code{localhost}.
@item --skip-networking
Don't allow TCP/IP connections over the network. All connections to
@code{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.
@item --skip-show-database
@code{SHOW DATABASE} command doesn't return anything.
@item --safe-show-database
@code{SHOW DATABASE} only returns databases for which the user have
some kind of privilege.
@end table
@node What Privileges, User names, Privileges options, Privilege system
@section What the Privilege System Does
@cindex system, privilege
@cindex privilege system
@cindex passwords, security
The primary function of the @strong{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
@strong{select}, @strong{insert}, @strong{update} and @strong{delete}.
Additional functionality includes the ability to have an anonymous user and
to grant privileges for @strong{MySQL}-specific functions such as @code{LOAD
DATA INFILE} and administrative operations.
@node User names, Connecting, What Privileges, Privilege system
@section MySQL User Names and Passwords
@cindex user names, and passwords
@cindex passwords, for users
There are several distinctions between the way user names and passwords are
used by @strong{MySQL} and the way they are used by Unix or Windows:
@itemize @bullet
@item
User names, as used by @strong{MySQL} for authentication purposes, have
nothing to do with Unix user names (login names) or Windows user names. Most
@strong{MySQL} clients by default try to log in using the current Unix user
name as the @strong{MySQL} user name, but that is for convenience only.
Client programs allow a different name to be specified with the @code{-u} or
@code{--user} options. This means that you can't make a database secure in
any way unless all @strong{MySQL} user names have passwords. Anyone may
attempt to connect to the server using any name, and they will succeed if
they specify any name that doesn't have a password.
@item
@strong{MySQL} user names can be up to 16 characters long; Unix user names
typically are limited to 8 characters.
@item
@strong{MySQL} passwords have nothing to do with Unix passwords. There is no
necessary connection between the password you use to log in to a Unix machine
and the password you use to access a database on that machine.
@item
@strong{MySQL} encrypts passwords using a different algorithm than the
one used during the Unix login process. See the descriptions of the
@code{PASSWORD()} and @code{ENCRYPT()} functions in @ref{Miscellaneous
functions}. Note that even if the password is stored 'scrambled', and
knowing your 'scrambled' password is enough to be able to connect to
the @strong{MySQL} server!
@end itemize
@node Connecting, Password security, User names, Privilege system
@section Connecting to the MySQL Server
@cindex connecting, to the server
@cindex default hostname
@cindex hostname, default
@cindex server, connecting
@strong{MySQL} client programs generally require that you specify connection
parameters when you want to access a @strong{MySQL} server: the host you want
to connect to, your user name, and your password. For example, the
@code{mysql} client can be started like this (optional arguments are enclosed
between @samp{[} and @samp{]}):
@example
shell> mysql [-h host_name] [-u user_name] [-pyour_pass]
@end example
Alternate forms of the @code{-h}, @code{-u}, and @code{-p} options are
@code{--host=host_name}, @code{--user=user_name}, and
@code{--password=your_pass}. Note that there is @emph{no space} between
@code{-p} or @code{--password=} and the password following it.
@strong{NOTE:} Specifying a password on the command line is not secure!
Any user on your system may then find out your password by typing a command
like: @code{ps auxww}. @xref{Option files}.
@code{mysql} uses default values for connection parameters that are missing
from the command line:
@itemize @bullet
@item
The default hostname is @code{localhost}.
@item
The default user name is your Unix login name.
@item
No password is supplied if @code{-p} is missing.
@end itemize
Thus, for a Unix user @code{joe}, the following commands are equivalent:
@example
shell> mysql -h localhost -u joe
shell> mysql -h localhost
shell> mysql -u joe
shell> mysql
@end example
Other @strong{MySQL} clients behave similarly.
On Unix systems, you can specify different default values to be used when you
make a connection, so that you need not enter them on the command line each
time you invoke a client program. This can be done in a couple of ways:
@itemize @bullet
@item
@tindex .my.cnf file
You can specify connection parameters in the @code{[client]} section of the
@file{.my.cnf} configuration file in your home directory. The relevant
section of the file might look like this:
@example
[client]
host=host_name
user=user_name
password=your_pass
@end example
@xref{Option files}.
@item
@tindex MYSQL_HOST environment variable
@tindex Environment variable, MYSQL_HOST
@tindex MYSQL_PWD environment variable
@tindex Environment variable, MYSQL_PWD
@tindex USER environment variable
@tindex Environment variable, USER
You can specify connection parameters using environment variables. The
host can be specified for @code{mysql} using @code{MYSQL_HOST}. The
@strong{MySQL} user name can be specified using @code{USER} (this is for
Windows only). The password can be specified using @code{MYSQL_PWD}
(but this is insecure; see the next section). @xref{Environment variables}.
@end itemize
@node Password security, Privileges provided, Connecting, Privilege system
@section Keeping Your Password Secure
It is inadvisable to specify your password in a way that exposes it to
discovery by other users. The methods you can use to specify your password
when you run client programs are listed below, along with an assessment of
the risks of each method:
@itemize @bullet
@item
Never give a normal user access to the @code{mysql.user} table. Knowing
the encrypted password for a user makes it possible to login as this
user. The passwords are only scrambled so that one shouldn't be able to
see the real password you used (if you happen to use a similar password
with your other applications).
@item
Use a @code{-pyour_pass} or @code{--password=your_pass} option on the command
line. This is convenient but insecure, because your password becomes visible
to system status programs (such as @code{ps}) that may be invoked by other
users to display command lines. (@strong{MySQL} clients typically overwrite
the command-line argument with zeroes during their initialization sequence,
but there is still a brief interval during which the value is visible.)
@item
Use a @code{-p} or @code{--password} option (with no @code{your_pass} value
specified). In this case, the client program solicits the password from
the terminal:
@findex -p option
@findex -password option
@example
shell> mysql -u user_name -p
Enter password: ********
@end example
The @samp{*} characters represent your password.
It is more secure to enter your password this way than to specify it on the
command line because it is not visible to other users. However, this method
of entering a password is suitable only for programs that you run
interactively. If you want to invoke a client from a script that runs
non-interactively, there is no opportunity to enter the password from the
terminal. On some systems, you may even find that the first line of your
script is read and interpreted (incorrectly) as your password!
@item
@tindex .my.cnf file
Store your password in a configuration file. For example, you can list your
password in the @code{[client]} section of the @file{.my.cnf} file in your
home directory:
@example
[client]
password=your_pass
@end example
If you store your password in @file{.my.cnf}, the file should not be group or
world readable or writable. Make sure the file's access mode is @code{400}
or @code{600}.
@xref{Option files}.
@item
You can store your password in the @code{MYSQL_PWD} environment variable, but
this method must be considered extremely insecure and should not be used.
Some versions of @code{ps} include an option to display the environment of
running processes; your password will be in plain sight for all to see if
you set @code{MYSQL_PWD}. Even on systems without such a version of
@code{ps}, it is unwise to assume there is no other method to observe process
environments. @xref{Environment variables}.
@end itemize
All in all, the safest methods are to have the client program prompt for the
password or to specify the password in a properly protected @file{.my.cnf}
file.
@node Privileges provided, Privileges, Password security, Privilege system
@section Privileges Provided by MySQL
@cindex privilege information, location
Information about user privileges is stored in the @code{user}, @code{db},
@code{host}, @code{tables_priv}, and @code{columns_priv} tables in the
@code{mysql} database (that is, in the database named @code{mysql}). The
@strong{MySQL} server reads the contents of these tables when it starts up
and under the circumstances indicated in @ref{Privilege changes}.
The names used in this manual to refer to the privileges provided by
@strong{MySQL} are shown below, along with the table column name associated
with each privilege in the grant tables and the context in which the
privilege applies:
@multitable @columnfractions .15 .25 .6
@item @strong{Privilege} @tab @strong{Column} @tab @strong{Context}
@item @strong{select} @tab @code{Select_priv} @tab tables
@item @strong{insert} @tab @code{Insert_priv} @tab tables
@item @strong{update} @tab @code{Update_priv} @tab tables
@item @strong{delete} @tab @code{Delete_priv} @tab tables
@item @strong{index} @tab @code{Index_priv} @tab tables
@item @strong{alter} @tab @code{Alter_priv} @tab tables
@item @strong{create} @tab @code{Create_priv} @tab databases, tables, or indexes
@item @strong{drop} @tab @code{Drop_priv} @tab databases or tables
@item @strong{grant} @tab @code{Grant_priv} @tab databases or tables
@item @strong{references} @tab @code{References_priv} @tab databases or tables
@item @strong{reload} @tab @code{Reload_priv} @tab server administration
@item @strong{shutdown} @tab @code{Shutdown_priv} @tab server administration
@item @strong{process} @tab @code{Process_priv} @tab server administration
@item @strong{file} @tab @code{File_priv} @tab file access on server
@end multitable
The @strong{select}, @strong{insert}, @strong{update}, and @strong{delete}
privileges allow you to perform operations on rows in existing tables in
a database.
@code{SELECT} statements require the @strong{select} privilege only if they
actually retrieve rows from a table. You can execute certain @code{SELECT}
statements even without permission to access any of the databases on the
server. For example, you could use the @code{mysql} client as a simple
calculator:
@example
mysql> SELECT 1+1;
mysql> SELECT PI()*2;
@end example
The @strong{index} privilege allows you to create or drop (remove) indexes.
The @strong{alter} privilege allows you to use @code{ALTER TABLE}.
The @strong{create} and @strong{drop} privileges allow you to create new
databases and tables, or to drop (remove) existing databases and tables.
Note that if you grant the @strong{drop} privilege for the @code{mysql}
database to a user, that user can drop the database in which the
@strong{MySQL} access privileges are stored!
The @strong{grant} privilege allows you to give to other users those
privileges you yourself possess.
The @strong{file} privilege gives you permission to read and write files on
the server using the @code{LOAD DATA INFILE} and @code{SELECT ... INTO
OUTFILE} statements. Any user to whom this privilege is granted can read or
write any file that the @strong{MySQL} server can read or write.
The remaining privileges are used for administrative operations, which are
performed using the @code{mysqladmin} program. The table below shows which
@code{mysqladmin} commands each administrative privilege allows you to
execute:
@multitable @columnfractions .15 .85
@item @strong{Privilege} @tab @strong{Commands permitted to privilege holders}
@item @strong{reload} @tab @code{reload}, @code{refresh},
@code{flush-privileges}, @code{flush-hosts}, @code{flush-logs}, and
@code{flush-tables}
@item @strong{shutdown} @tab @code{shutdown}
@item @strong{process} @tab @code{processlist}, @code{kill}
@end multitable
The @code{reload} command tells the server to re-read the grant tables. The
@code{refresh} command flushes all tables and opens and closes the log
files. @code{flush-privileges} is a synonym for @code{reload}. The other
@code{flush-*} commands perform functions similar to @code{refresh} but are
more limited in scope, and may be preferable in some instances. For example,
if you want to flush just the log files, @code{flush-logs} is a better choice
than @code{refresh}.
The @code{shutdown} command shuts down the server.
The @code{processlist} command displays information about the threads
executing within the server. The @code{kill} command kills server threads.
You can always display or kill your own threads, but you need the
@strong{process} privilege to display or kill threads initiated by other
users. @xref{KILL}.
It is a good idea in general to grant privileges only to those users who need
them, but you should exercise particular caution in granting certain
privileges:
@itemize @bullet
@item
The @strong{grant} privilege allows users to give away their privileges to
other users. Two users with different privileges and with the @strong{grant}
privilege are able to combine privileges.
@item
The @strong{alter} privilege may be used to subvert the privilege system
by renaming tables.
@item
The @strong{file} privilege can be abused to read any world-readable file on
the server into a database table, the contents of which can then be
accessed using @code{SELECT}. This includes the contents of all databases
hosted by the server!
@item
The @strong{shutdown} privilege can be abused to deny service to other
users entirely, by terminating the server.
@item
The @strong{process} privilege can be used to view the plain text of
currently executing queries, including queries that set or change passwords.
@item
Privileges on the @code{mysql} database can be used to change passwords
and other access privilege information. (Passwords are stored
encrypted, so a malicious user cannot simply read them to know the plain
text password). If they can access the @code{mysql.user} password
column, they can use it to log into the @strong{MySQL} server
for the given user. (With sufficient privileges, the same user can
replace a password with a different one.)
@end itemize
There are some things that you cannot do with the @strong{MySQL}
privilege system:
@itemize @bullet
@item
You cannot explicitly specify that a given user should be denied access.
That is, you cannot explicitly match a user and then refuse the connection.
@item
You cannot specify that a user has privileges to create or drop tables
in a database but not to create or drop the database itself.
@end itemize
@node Privileges, Connection access, Privileges provided, Privilege system
@section How the Privilege System Works
@cindex privilege system, described
The @strong{MySQL} privilege system ensures that all users may do exactly the
things that they are supposed to be allowed to do. When you connect to a
@strong{MySQL} server, your identity is determined by @strong{the host from
which you connect} and @strong{the user name you specify}. The system grants
privileges according to your identity and @strong{what you want to do}.
@strong{MySQL} considers both your hostname and user name in identifying you
because there is little reason to assume that a given user name belongs to
the same person everywhere on the Internet. For example, the user
@code{bill} who connects from @code{whitehouse.gov} need not be the same
person as the user @code{bill} who connects from @code{microsoft.com}.
@strong{MySQL} handles this by allowing you to distinguish users on different
hosts that happen to have the same name: you can grant @code{bill} one set
of privileges for connections from @code{whitehouse.gov}, and a different set
of privileges for connections from @code{microsoft.com}.
@strong{MySQL} access control involves two stages:
@itemize @bullet
@item
Stage 1: The server checks whether or not you are even allowed to connect.
@item
Stage 2: Assuming you can connect, the server checks each request you issue
to see whether or not you have sufficient privileges to perform it. For
example, if you try to select rows from a table in a database or drop a table
from the database, the server makes sure you have the @strong{select}
privilege for the table or the @strong{drop} privilege for the database.
@end itemize
The server uses the @code{user}, @code{db}, and @code{host} tables in the
@code{mysql} database at both stages of access control. The fields in these
grant tables are shown below:
@multitable @columnfractions .2 .25 .25 .25
@item @strong{Table name} @tab @code{user} @tab @code{db} @tab @code{host}
@item @strong{Scope fields} @tab @code{Host} @tab @code{Host} @tab @code{Host}
@item @tab @code{User} @tab @code{Db} @tab @code{Db}
@item @tab @code{Password} @tab @code{User} @tab
@item @strong{Privilege fields} @tab @code{Select_priv} @tab @code{Select_priv} @tab @code{Select_priv}
@item @tab @code{Insert_priv} @tab @code{Insert_priv} @tab @code{Insert_priv}
@item @tab @code{Update_priv} @tab @code{Update_priv} @tab @code{Update_priv}
@item @tab @code{Delete_priv} @tab @code{Delete_priv} @tab @code{Delete_priv}
@item @tab @code{Index_priv} @tab @code{Index_priv} @tab @code{Index_priv}
@item @tab @code{Alter_priv} @tab @code{Alter_priv} @tab @code{Alter_priv}
@item @tab @code{Create_priv} @tab @code{Create_priv} @tab @code{Create_priv}
@item @tab @code{Drop_priv} @tab @code{Drop_priv} @tab @code{Drop_priv}
@item @tab @code{Grant_priv} @tab @code{Grant_priv} @tab @code{Grant_priv}
@item @tab @code{References_priv} @tab @tab
@item @tab @code{Reload_priv} @tab @tab
@item @tab @code{Shutdown_priv} @tab @tab
@item @tab @code{Process_priv} @tab @tab
@item @tab @code{File_priv} @tab @tab
@end multitable
For the second stage of access control (request verification), the server
may, if the request involves tables, additionally consult the
@code{tables_priv} and @code{columns_priv} tables. The fields in these
tables are shown below:
@multitable @columnfractions .2 .25 .25
@item @strong{Table name} @tab @code{tables_priv} @tab @code{columns_priv}
@item @strong{Scope fields} @tab @code{Host} @tab @code{Host}
@item @tab @code{Db} @tab @code{Db}
@item @tab @code{User} @tab @code{User}
@item @tab @code{Table_name} @tab @code{Table_name}
@item @tab @tab @code{Column_name}
@item @strong{Privilege fields} @tab @code{Table_priv} @tab @code{Column_priv}
@item @tab @code{Column_priv} @tab
@item @strong{Other fields} @tab @code{Timestamp} @tab @code{Timestamp}
@item @tab @code{Grantor} @tab
@end multitable
Each grant table contains scope fields and privilege fields.
Scope fields determine the scope of each entry in the tables, that is, the
context in which the entry applies. For example, a @code{user} table entry
with @code{Host} and @code{User} values of @code{'thomas.loc.gov'} and
@code{'bob'} would be used for authenticating connections made to the server
by @code{bob} from the host @code{thomas.loc.gov}. Similarly, a @code{db}
table entry with @code{Host}, @code{User}, and @code{Db} fields of
@code{'thomas.loc.gov'}, @code{'bob'} and @code{'reports'} would be used when
@code{bob} connects from the host @code{thomas.loc.gov} to access the
@code{reports} database. The @code{tables_priv} and @code{columns_priv}
tables contain scope fields indicating tables or table/column combinations
to which each entry applies.
@cindex case sensitivity, in access checking
For access-checking purposes, comparisons of @code{Host} values are
case insensitive. @code{User}, @code{Password}, @code{Db}, and
@code{Table_name} values are case sensitive.
@code{Column_name} values are case insensitive in @strong{MySQL} Version
3.22.12 or later.
Privilege fields indicate the privileges granted by a table entry, that is,
what operations can be performed. The server combines the information in the
various grant tables to form a complete description of a user's privileges.
The rules used to do this are described in @ref{Request access}.
Scope fields are strings, declared as shown below; the default value for
each is the empty string:
@multitable @columnfractions .15 .15 .7
@item @strong{Field name} @tab @strong{Type}
@item @code{Host} @tab @code{CHAR(60)}
@item @code{User} @tab @code{CHAR(16)}
@item @code{Password} @tab @code{CHAR(16)}
@item @code{Db} @tab @code{CHAR(64)} @tab (@code{CHAR(60)} for the
@code{tables_priv} and @code{columns_priv} tables)
@item @code{Table_name} @tab @code{CHAR(60)}
@item @code{Column_name} @tab @code{CHAR(60)}
@end multitable
In the @code{user}, @code{db} and @code{host} tables,
all privilege fields are declared as @code{ENUM('N','Y')} --- each can have a
value of @code{'N'} or @code{'Y'}, and the default value is @code{'N'}.
In the @code{tables_priv} and @code{columns_priv} tables, the privilege
fields are declared as @code{SET} fields:
@multitable @columnfractions .2 .2 .6
@item @strong{Table name} @tab @strong{Field name} @tab @strong{Possible set elements}
@item @code{tables_priv} @tab @code{Table_priv} @tab @code{'Select', 'Insert',
'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'}
@item @code{tables_priv} @tab @code{Column_priv} @tab @code{'Select', 'Insert',
'Update', 'References'}
@item @code{columns_priv} @tab @code{Column_priv} @tab @code{'Select', 'Insert',
'Update', 'References'}
@end multitable
Briefly, the server uses the grant tables like this:
@itemize @bullet
@item
The @code{user} table scope fields determine whether to allow or reject
incoming connections. For allowed connections, any privileges granted in
the @code{user} table indicate the user's global (superuser) privileges.
These privileges apply to @strong{all} databases on the server.
@item
The @code{db} and @code{host} tables are used together:
@itemize @minus
@item
The @code{db} table scope fields determine which users can access which
databases from which hosts. The privilege fields determine which operations
are allowed.
@item
The @code{host} table is used as an extension of the @code{db} table when you
want a given @code{db} table entry to apply to several hosts. For example,
if you want a user to be able to use a database from several hosts in
your network, leave the @code{Host} value empty in the user's @code{db} table
entry, then populate the @code{host} table with an entry for each of those
hosts. This mechanism is described more detail in @ref{Request access}.
@end itemize
@item
The @code{tables_priv} and @code{columns_priv} tables are similar to
the @code{db} table, but are more fine-grained: they apply at the
table and column levels rather than at the database level.
@end itemize
Note that administrative privileges (@strong{reload}, @strong{shutdown},
etc.) are specified only in the @code{user} table. This is because
administrative operations are operations on the server itself and are not
database-specific, so there is no reason to list such privileges in the
other grant tables. In fact, only the @code{user} table need
be consulted to determine whether or not you can perform an administrative
operation.
The @strong{file} privilege is specified only in the @code{user} table, too.
It is not an administrative privilege as such, but your ability to read or
write files on the server host is independent of the database you are
accessing.
The @code{mysqld} server reads the contents of the grant tables once, when it
starts up. Changes to the grant tables take effect as indicated in
@ref{Privilege changes}.
When you modify the contents of the grant tables, it is a good idea to make
sure that your changes set up privileges the way you want. For help in
diagnosing problems, see @ref{Access denied}. For advice on security issues,
@pxref{Security}.
A useful
diagnostic tool is the @code{mysqlaccess} script, which Yves Carlier has
provided for the @strong{MySQL} distribution. Invoke @code{mysqlaccess} with
the @code{--help} option to find out how it works.
Note that @code{mysqlaccess} checks access using only the @code{user},
@code{db} and @code{host} tables. It does not check table- or column-level
privileges.
@node Connection access, Request access, Privileges, Privilege system
@section Access Control, Stage 1: Connection Verification
@cindex access control
@cindex control access
@cindex connecting, verification
@cindex testing, connection to the server
When you attempt to connect to a @strong{MySQL} server, the server accepts or
rejects the connection based on your identity and whether or not you can
verify your identity by supplying the correct password. If not, the server
denies access to you completely. Otherwise, the server accepts the
connection, then enters Stage 2 and waits for requests.
Your identity is based on two pieces of information:
@itemize @bullet
@item
The host from which you connect
@item
Your @strong{MySQL} user name
@end itemize
Identity checking is performed using the three @code{user} table scope fields
(@code{Host}, @code{User}, and @code{Password}). The server accepts the
connection only if a @code{user} table entry matches your hostname and user
name, and you supply the correct password.
Values in the @code{user} table scope fields may be specified as follows:
@itemize @bullet
@item
A @code{Host} value may be a hostname or an IP number, or @code{'localhost'}
to indicate the local host.
@item
@cindex wildcards, in @code{mysql.user} table
You can use the wild-card characters @samp{%} and @samp{_} in the @code{Host}
field.
@item
A @code{Host} value of @code{'%'} matches any hostname. A blank @code{Host}
value is equivalent to @code{'%'}. Note that these values match @emph{any
host that can create a connection to your server!}
@cindex netmask notation, in @code{mysql.user} table
@item
As of MySQL Version 3.23, for @code{Host} values specified as IP numbers, you
can specify a netmask indicating how many address bits to use for the
network number. For example:
@example
GRANT ALL PRIVILEGES on db.* to david@@'192.58.197.0/255.255.255.0';
@end example
This will allow everyone to connect from an IP where the following is true:
@example
user_ip & netmask = host_ip.
@end example
In the above example all IP:s in the interval 192.58.197.0 -
192.58.197.255 can connect to the @strong{MySQL} server.
@item
@cindex anonymous user
Wild-card characters are not allowed in the @code{User} field, but you can
specify a blank value, which matches any name. If the @code{user} table
entry that matches an incoming connection has a blank user name, the user is
considered to be the anonymous user (the user with no name), rather than the
name that the client actually specified. This means that a blank user name
is used for all further access checking for the duration of the connection
(that is, during Stage 2).
@item
The @code{Password} field can be blank. This does not mean that any password
matches, it means the user must connect without specifying a password.
@end itemize
@findex PASSWORD()
Non-blank @code{Password} values represent encrypted passwords.
@strong{MySQL} does not store passwords in plaintext form for anyone to
see. Rather, the password supplied by a user who is attempting to
connect is encrypted (using the @code{PASSWORD()} function). The
encrypted password is then used when the client/server is checking if
the password is correct (This is done without the encrypted password
ever traveling over the connection.) Note that from @strong{MySQL}'s
point of view the encrypted password is the REAL password, so you should
not give anyone access to it! In particular, don't give normal users
read access to the tables in the @code{mysql} database!
The examples below show how various combinations of @code{Host} and
@code{User} values in @code{user} table entries apply to incoming
connections:
@multitable @columnfractions .25 .15 .60
@item @code{Host} @strong{value} @tab @code{User} @strong{value} @tab @strong{Connections matched by entry}
@item @code{'thomas.loc.gov'} @tab @code{'fred'} @tab @code{fred}, connecting from @code{thomas.loc.gov}
@item @code{'thomas.loc.gov'} @tab @code{''} @tab Any user, connecting from @code{thomas.loc.gov}
@item @code{'%'} @tab @code{'fred'} @tab @code{fred}, connecting from any host
@item @code{'%'} @tab @code{''} @tab Any user, connecting from any host
@item @code{'%.loc.gov'} @tab @code{'fred'} @tab @code{fred}, connecting from any host in the @code{loc.gov} domain
@item @code{'x.y.%'} @tab @code{'fred'} @tab @code{fred}, connecting from @code{x.y.net}, @code{x.y.com},@code{x.y.edu}, etc. (this is probably not useful)
@item @code{'144.155.166.177'} @tab @code{'fred'} @tab @code{fred}, connecting from the host with IP address @code{144.155.166.177}
@item @code{'144.155.166.%'} @tab @code{'fred'} @tab @code{fred}, connecting from any host in the @code{144.155.166} class C subnet
@item @code{'144.155.166.0/255.255.255.0'} @tab @code{'fred'} @tab Same as previous example
@end multitable
Because you can use IP wild-card values in the @code{Host} field (for example,
@code{'144.155.166.%'} to match every host on a subnet), there is the
possibility that someone might try to exploit this capability by naming a
host @code{144.155.166.somewhere.com}. To foil such attempts, @strong{MySQL}
disallows matching on hostnames that start with digits and a dot. Thus, if
you have a host named something like @code{1.2.foo.com}, its name will never
match the @code{Host} column of the grant tables. Only an IP number can
match an IP wild-card value.
An incoming connection may be matched by more than one entry in the
@code{user} table. For example, a connection from @code{thomas.loc.gov} by
@code{fred} would be matched by several of the entries just shown above. How
does the server choose which entry to use if more than one matches? The
server resolves this question by sorting the @code{user} table after reading
it at startup time, then looking through the entries in sorted order when a
user attempts to connect. The first matching entry is the one that is used.
@code{user} table sorting works as follows. Suppose the @code{user} table
looks like this:
@example
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| % | root | ...
| % | jeffrey | ...
| localhost | root | ...
| localhost | | ...
+-----------+----------+-
@end example
When the server reads in the table, it orders the entries with the
most-specific @code{Host} values first (@code{'%'} in the @code{Host} column
means ``any host'' and is least specific). Entries with the same @code{Host}
value are ordered with the most-specific @code{User} values first (a blank
@code{User} value means ``any user'' and is least specific). The resulting
sorted @code{user} table looks like this:
@example
+-----------+----------+-
| Host | User | ...
+-----------+----------+-
| localhost | root | ...
| localhost | | ...
| % | jeffrey | ...
| % | root | ...
+-----------+----------+-
@end example
@cindex grant tables, sorting
@cindex sorting, grant tables
@cindex @code{user} table, sorting
When a connection is attempted, the server looks through the sorted entries
and uses the first match found. For a connection from @code{localhost} by
@code{jeffrey}, the entries with @code{'localhost'} in the @code{Host} column
match first. Of those, the entry with the blank user name matches both the
connecting hostname and user name. (The @code{'%'/'jeffrey'} entry would
have matched, too, but it is not the first match in the table.)
Here is another example. Suppose the @code{user} table looks like this:
@example
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| % | jeffrey | ...
| thomas.loc.gov | | ...
+----------------+----------+-
@end example
The sorted table looks like this:
@example
+----------------+----------+-
| Host | User | ...
+----------------+----------+-
| thomas.loc.gov | | ...
| % | jeffrey | ...
+----------------+----------+-
@end example
A connection from @code{thomas.loc.gov} by @code{jeffrey} is matched by the
first entry, whereas a connection from @code{whitehouse.gov} by
@code{jeffrey} is matched by the second.
A common misconception is to think that for a given user name, all entries
that explicitly name that user will be used first when the server attempts to
find a match for the connection. This is simply not true. The previous
example illustrates this, where a connection from @code{thomas.loc.gov} by
@code{jeffrey} is first matched not by the entry containing @code{'jeffrey'}
as the @code{User} field value, but by the entry with no user name!
If you have problems connecting to the server, print out the @code{user}
table and sort it by hand to see where the first match is being made.
@node Request access, Privilege changes, Connection access, Privilege system
@section Access Control, Stage 2: Request Verification
Once you establish a connection, the server enters Stage 2. For each request
that comes in on the connection, the server checks whether you have
sufficient privileges to perform it, based on the type of operation you wish
to perform. This is where the privilege fields in the grant tables come into
play. These privileges can come from any of the @code{user}, @code{db},
@code{host}, @code{tables_priv}, or @code{columns_priv} tables. The grant
tables are manipulated with @code{GRANT} and @code{REVOKE} commands.
@xref{GRANT, , @code{GRANT}}. (You may find it helpful to refer to
@ref{Privileges}, which lists the fields present in each of the grant
tables.)
The @code{user} table grants privileges that are assigned to you on a global
basis and that apply no matter what the current database is. For example, if
the @code{user} table grants you the @strong{delete} privilege, you can
delete rows from any database on the server host! In other words,
@code{user} table privileges are superuser privileges. It is wise to grant
privileges in the @code{user} table only to superusers such as server or
database administrators. For other users, you should leave the privileges
in the @code{user} table set to @code{'N'} and grant privileges on a
database-specific basis only, using the @code{db} and @code{host} tables.
@cindex Anonymous user
@cindex wild cards, in @code{mysql.db} table
@cindex wild cards, in @code{mysql.host} table
The @code{db} and @code{host} tables grant database-specific privileges.
Values in the scope fields may be specified as follows:
@itemize @bullet
@item
The wild-card characters @samp{%} and @samp{_} can be used in the @code{Host}
and @code{Db} fields of either table.
@item
A @code{'%'} @code{Host} value in the @code{db} table means ``any host.'' A
blank @code{Host} value in the @code{db} table means ``consult the
@code{host} table for further information.''
@item
A @code{'%'} or blank @code{Host} value in the @code{host} table means ``any
host.''
@item
A @code{'%'} or blank @code{Db} value in either table means ``any database.''
@item
A blank @code{User} value in either table matches the anonymous user.
@end itemize
@cindex grant tables, sorting
@cindex sorting, grant tables
@cindex @code{db} table, sorting
@cindex @code{host} table, sorting
The @code{db} and @code{host} tables are read in and sorted when the server
starts up (at the same time that it reads the @code{user} table). The
@code{db} table is sorted on the @code{Host}, @code{Db}, and @code{User} scope
fields, and the @code{host} table is sorted on the @code{Host} and @code{Db}
scope fields. As with the @code{user} table, sorting puts the most-specific
values first and least-specific values last, and when the server looks for
matching entries, it uses the first match that it finds.
@cindex wild cards, in @code{mysql.tables_priv} table
@cindex wild cards, in @code{mysql.columns_priv} table
The @code{tables_priv} and @code{columns_priv} tables grant table- and
column-specific privileges. Values in the scope fields may be specified as
follows:
@itemize @bullet
@item
The wild-card characters @samp{%} and @samp{_}
can be used in the @code{Host} field of either table.
@item
A @code{'%'} or blank @code{Host} value in either table means ``any host.''
@item
The @code{Db}, @code{Table_name} and @code{Column_name} fields cannot contain
wild cards or be blank in either table.
@end itemize
The @code{tables_priv} and @code{columns_priv} tables are sorted on
the @code{Host}, @code{Db}, and @code{User} fields. This is similar to
@code{db} table sorting, although the sorting is simpler because
only the @code{Host} field may contain wild cards.
The request verification process is described below. (If you are familiar
with the access-checking source code, you will notice that the description
here differs slightly from the algorithm used in the code. The description
is equivalent to what the code actually does; it differs only to make the
explanation simpler.)
For administrative requests (@strong{shutdown}, @strong{reload}, etc.), the
server checks only the @code{user} table entry, because that is the only table
that specifies administrative privileges. Access is granted if the entry
allows the requested operation and denied otherwise. For example, if you
want to execute @code{mysqladmin shutdown} but your @code{user} table entry
doesn't grant the @strong{shutdown} privilege to you, access is denied
without even checking the @code{db} or @code{host} tables. (They
contain no @code{Shutdown_priv} column, so there is no need to do so.)
For database-related requests (@strong{insert}, @strong{update}, etc.), the
server first checks the user's global (superuser) privileges by looking in
the @code{user} table entry. If the entry allows the requested operation,
access is granted. If the global privileges in the @code{user} table are
insufficient, the server determines the user's database-specific privileges
by checking the @code{db} and @code{host} tables:
@enumerate
@item
The server looks in the @code{db} table for a match on the @code{Host},
@code{Db}, and @code{User} fields. The @code{Host} and @code{User} fields are
matched to the connecting user's hostname and @strong{MySQL} user name. The
@code{Db} field is matched to the database the user wants to access. If
there is no entry for the @code{Host} and @code{User}, access is denied.
@item
If there is a matching @code{db} table entry and its @code{Host} field is
not blank, that entry defines the user's database-specific privileges.
@item
If the matching @code{db} table entry's @code{Host} field is blank, it
signifies that the @code{host} table enumerates which hosts should be allowed
access to the database. In this case, a further lookup is done in the
@code{host} table to find a match on the @code{Host} and @code{Db} fields.
If no @code{host} table entry matches, access is denied. If there is a
match, the user's database-specific privileges are computed as the
intersection (@emph{not} the union!) of the privileges in the @code{db} and
@code{host} table entries, that is, the privileges that are @code{'Y'} in both
entries. (This way you can grant general privileges in the @code{db} table
entry and then selectively restrict them on a host-by-host basis using the
@code{host} table entries.)
@end enumerate
After determining the database-specific privileges granted by the @code{db}
and @code{host} table entries, the server adds them to the global privileges
granted by the @code{user} table. If the result allows the requested
operation, access is granted. Otherwise, the server checks the user's
table and column privileges in the @code{tables_priv} and @code{columns_priv}
tables and adds those to the user's privileges. Access is allowed or denied
based on the result.
Expressed in boolean terms, the preceding description of how a user's
privileges are calculated may be summarized like this:
@example
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
@end example
It may not be apparent why, if the global @code{user} entry privileges are
initially found to be insufficient for the requested operation, the server
adds those privileges to the database-, table-, and column-specific privileges
later. The reason is that a request might require more than one type of
privilege. For example, if you execute an @code{INSERT ... SELECT}
statement, you need both @strong{insert} and @strong{select} privileges.
Your privileges might be such that the @code{user} table entry grants one
privilege and the @code{db} table entry grants the other. In this case, you
have the necessary privileges to perform the request, but the server cannot
tell that from either table by itself; the privileges granted by the entries
in both tables must be combined.
@cindex @code{host} table
@cindex tables, @code{host}
The @code{host} table can be used to maintain a list of secure servers.
At TcX, the @code{host} table contains a list of all machines on the local
network. These are granted all privileges.
You can also use the @code{host} table to indicate hosts that are @emph{not}
secure. Suppose you have a machine @code{public.your.domain} that is located
in a public area that you do not consider secure. You can allow access to
all hosts on your network except that machine by using @code{host} table
entries
like this:
@example
+--------------------+----+-
| Host | Db | ...
+--------------------+----+-
| public.your.domain | % | ... (all privileges set to 'N')
| %.your.domain | % | ... (all privileges set to 'Y')
+--------------------+----+-
@end example
@cindex privilege, changes
@cindex changes to privileges
@cindex tables, grant
@cindex grant tables
Naturally, you should always test your entries in the grant tables (for
example, using @code{mysqlaccess}) to make sure your access privileges are
actually set up the way you think they are.
@node Privilege changes, Default privileges, Request access, Privilege system
@section When Privilege Changes Take Effect
When @code{mysqld} starts, all grant table contents are read into memory and
become effective at that point.
Modifications to the grant tables that you perform using @code{GRANT},
@code{REVOKE}, or @code{SET PASSWORD} are noticed by the server immediately.
If you modify the grant tables manually (using @code{INSERT}, @code{UPDATE},
etc.), you should execute a @code{FLUSH PRIVILEGES} statement or run
@code{mysqladmin flush-privileges} or @code{mysqladmin reload} to tell the
server to reload the grant tables. Otherwise your changes will have @emph{no
effect} until you restart the server. If you change the grant tables manually
but forget to reload the privileges, you will be wondering why your changes
don't seem to make any difference!
When the server notices that the grant tables have been changed,
existing client connections are affected as follows:
@itemize @bullet
@item
Table and column privilege changes take effect with the client's next
request.
@item
Database privilege changes take effect at the next @code{USE db_name}
command.
@end itemize
Global privilege changes and password changes take effect the next time the
client connects.
@cindex privileges, default
@cindex default, privileges
@cindex root password
@cindex superuser
@cindex users, root
@cindex anonymous user
@cindex password, root user
@node Default privileges, Adding users, Privilege changes, Privilege system
@section Setting Up the Initial MySQL Privileges
After installing @strong{MySQL}, you set up the initial access privileges by
running @code{scripts/mysql_install_db}.
@xref{Quick install}.
The @code{mysql_install_db} script starts up the @code{mysqld}
server, then initializes the grant tables to contain the following set
of privileges:
@itemize @bullet
@item
The @strong{MySQL} @code{root} user is created as a superuser who can do
anything. Connections must be made from the local host.
@strong{NOTE:}
The initial @code{root} password is empty, so anyone can connect as @code{root}
@emph{without a password} and be granted all privileges.
@item
@cindex anonymous user
An anonymous user is created that can do anything with databases that have a
name of @code{'test'} or starting with @code{'test_'}. Connections must be
made from the local host. This means any local user can connect without a
password and be treated as the anonymous user.
@item
Other privileges are denied. For example, normal users can't use
@code{mysqladmin shutdown} or @code{mysqladmin processlist}.
@end itemize
@strong{NOTE:} The default privileges are different for Windows.
@xref{Windows running}.
Because your installation is initially wide open, one of the first things you
should do is specify a password for the @strong{MySQL}
@code{root} user. You can do this as follows (note that you specify the
password using the @code{PASSWORD()} function):
@example
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;
@end example
You can, in @strong{MySQL} Version 3.22 and above, use the @code{SET PASSWORD}
statement:
@example
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');
@end example
Another way to set the password is by using the @code{mysqladmin} command:
@example
shell> mysqladmin -u root password new_password
@end example
Only users with write/update access to the mysql database can change the
password for others users. All normal users (not anonymous ones) can only
change their own password with either of the above commands or with
@code{SET PASSWORD=PASSWORD('new password')}.