Below is the list of changes that have just been committed into a local
mysqldoc repository of paul. When paul does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.1302 04/02/22 21:42:06 paul@stripped +1 -0
Install/admin chapter edits.
Docs/manual.texi
1.1204 04/02/22 21:42:03 paul@stripped +409 -292
Install/admin chapter edits.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: paul
# Host: ice.snake.net
# Root: /Volumes/ice2/MySQL/bk/mysqldoc
--- 1.1203/Docs/manual.texi Sun Feb 22 12:11:04 2004
+++ 1.1204/Docs/manual.texi Sun Feb 22 21:42:03 2004
@@ -855,7 +855,7 @@
@item Automatic recovery of @code{MyISAM} tables --- Gamma
This status applies only to the new code in the @code{MyISAM} storage
engine that checks if the table was closed properly on open and
-executes an automatic check/repair of the table if it wasn't.
+executes an automatic check or repair of the table if it wasn't.
@item Bulk-insert --- Alpha
New feature in @code{MyISAM} tables in @code{MySQL} 4.0 for faster
@@ -9693,11 +9693,17 @@
for @code{mysqld} and then runs it with those options. This script is
used on systems based on BSD Unix. It also is invoked by @code{mysql.server}.
@xref{mysqld_safe, , @code{mysqld_safe}}.
+
+@item
+On Mac OS X, you can install a separate MySQL Startup Item package to
+enable the automatic startup of MySQL on system bootup.
+See @ref{Mac OS X installation} for details.
+
@end itemize
-The @code{mysql.server} and @code{mysqld_safe} scripts can be used to start
-the server automatically at system startup time. @code{mysql.server} also can
-be used to stop the server.
+The @code{mysql.server} and @code{mysqld_safe} scripts and the Mac OS X
+Startup Item can be used to start the server automatically at system startup
+time. @code{mysql.server} also can be used to stop the server.
The @code{mysql.server} script can be used to start or stop the server
by invoking it with @code{start} or @code{stop} arguments:
@@ -9717,25 +9723,21 @@
install it manually. See @ref{Linux-RPM} for more information on the
Linux RPM packages.
-On Mac OS X, you can install a separate MySQL Startup Item package to
-enable the automatic startup of MySQL on system bootup.
-See @ref{Mac OS X installation} for details.
-
-Before @code{mysql.server} starts the server, it changes the directory to
-the MySQL installation directory, then invokes @code{mysqld_safe}.
-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{mysqld_safe}. If you want the
-server to run as some specific user, add an appropriate @code{user} line
-to the @file{/etc/my.cnf} file, as shown later in this section.
+Before @code{mysql.server} starts the server, it changes location to the
+MySQL installation directory, then invokes @code{mysqld_safe}. It is
+possible, if you've installed a binary distribution of MySQL in a
+non-standard location, that you'll need to edit @code{mysql.server}. Modify
+it to @code{cd} into the proper directory before it runs @code{mysqld_safe}.
+If you want the server to run as some specific user, add an appropriate
+@code{user} option to the @file{/etc/my.cnf} file, as shown later in this
+section.
@code{mysql.server stop} brings down the server by sending a signal to it.
-You can also stop the server manually by executing
-@code{mysqladmin shutdown}.
+You can also stop the server manually by executing @code{mysqladmin
+shutdown}.
-You need to add these start and stop commands to the appropriate places
-in your @file{/etc/rc*} files when you want to start up MySQL automatically
-on your server.
+To start and stop MySQL automatically on your server, you need to add start
+and stop commands to the appropriate places in your @file{/etc/rc*} files.
On most current Linux distributions, it is sufficient to copy the file
@code{mysql.server} into the @file{/etc/init.d} directory (or
@@ -9746,9 +9748,9 @@
shell> chkconfig --add mysql.server
@end example
-On FreeBSD startup scripts generally should go in
-@file{/usr/local/etc/rc.d/}. The @code{rc(8)} manual page also states that
-scripts in this directory are only executed, if their basename matches the
+On FreeBSD, startup scripts generally should go in
+@file{/usr/local/etc/rc.d/}. The @code{rc(8)} manual page states that
+scripts in this directory are executed only if their basename matches the
shell globbing pattern @code{*.sh}. Any other files or directories present
within the directory are silently ignored. In other words, on FreeBSD you
should install the file @file{mysql.server} as
@@ -9780,10 +9782,13 @@
@end example
The @code{mysql.server} script understands the following options:
-@code{datadir}, @code{basedir}, and @code{pid-file}.
+@code{datadir}, @code{basedir}, and @code{pid-file}. If specified, they
+must be placed in a option file, not on the command line.
+@code{mysql.server} understands only @code{start} and @code{stop} as
+command-line arguments.
-The following table shows which option groups each startup script
-reads from option files:
+The following table shows which option groups the server and each startup
+script reads from option files:
@multitable @columnfractions .15 .85
@item @strong{Script} @tab @strong{Option groups}
@@ -9792,6 +9797,11 @@
@item @code{mysqld_safe} @tab @code{[mysqld]}, @code{[server]}, and @code{[mysqld_safe]}
@end multitable
+@code{[mysqld-major-version]} means that servers having versions 4.0.x, 4.1.x,
+5.0.x, and so forth will read the @code{[mysqld-4.0]}, @code{[mysqld-4.1]},
+@code{[mysqld-5.0]}, and so forth.
+This feature was added in MySQL 4.0.14.
+
For backward compatibility, @code{mysql.server} also reads the
@code{[mysql_server]} group and @code{mysqld_safe} also reads the
@code{[safe_mysqld]} group. However, you should update your option
@@ -9805,48 +9815,103 @@
@cindex server, starting problems
@cindex problems, starting the server
+If you have problems starting the server, here are some things you can try:
+
+@itemize @bullet
+@item
+Make sure to specify any storage engine-specific options needed by the storage
+engines you are using.
+
+@item
+Make sure the server knows where to find the data directory.
+
+@item
+Verify that the network interfaces the server wants to use are available.
+
+@item
+Check the ownership and permissions of the data directory and its contents.
+They must be set such that the server can access and modify them.
+
+@end itemize
+
+Some storage engines have options that control their behavior.
If you are going to use storage engines that support transactional tables
-(@code{InnoDB}, @code{BDB}),
-you should first create a @file{my.cnf} file and set startup options
+(@code{InnoDB}, @code{BDB}), be sure you have them configured the
+way you want before starting the server.
+You can create a @file{my.cnf} file and set startup options
for the engines you plan to use. @xref{Table types}.
+If you are using @code{InnoDB} tables, refer to the @code{InnoDB}-specific startup
+options. @xref{InnoDB start}.
+If you are using @code{BDB} (Berkeley DB) tables, you should familiarise
+yourself with the different @code{BDB}-specific startup options.
+@xref{BDB start}.
-When the @code{mysqld} server starts, it changes location to the
-data directory. This is where it expects to write log files and the pid
-(process ID) file, and where it expects to find databases.
-
-The data directory location is hardwired in when the distribution is
-compiled. However, if @code{mysqld} expects to find the data directory
-somewhere other than where it really is on your system, it will not work
-properly. If you have problems with incorrect paths, you can find out
-what options @code{mysqld} allows and what the default path settings are by
-invoking @code{mysqld} with the @code{--help} option. You can override the
-defaults by specifying the correct pathnames as command-line arguments to
-@code{mysqld}. (These options can be used with @code{mysqld_safe} as well.)
-
-Normally you should need to tell @code{mysqld} only the base directory under
-which MySQL is installed. You can do this with the @code{--basedir}
-option. You can also use @code{--help} to check the effect of changing path
-options (note that @code{--help} @strong{must} be the final option of the
-@code{mysqld} command). For example:
+When the @code{mysqld} server starts, it changes location to the data
+directory. This is where it expects to find databases and where it expects
+to write log files. On Unix, the server also writes the pid (process ID)
+file in the data directory.
+
+The data directory location is hardwired in when the server is compiled.
+This is where the server expects to find the data directory by default. If
+the data directory is located somewhere else on your system, the server will
+not work properly. You can find out what the default path settings are by
+invoking @code{mysqld} with the @code{--verbose} and @code{--help} options.
+(Prior to MySQL 4.1, omit the @code{--verbose} option.)
+
+If the defaults don't match the MySQL installation layout on your system,
+you can override them by specifying options on the command line to
+@code{mysqld} or @code{mysqld_safe}. You can also list the option in an
+option file.
+
+To specify the location of the data directory explicitly, use the
+@code{--datadir} option. However, normally you can tell @code{mysqld} the
+location of the base directory under which MySQL is installed and it will
+look for the data directory there. You can do this with the @code{--basedir}
+option.
+
+To check the effect of specifying path options, invoke @code{mysqld} with
+those options followed by the @code{--verbose} and @code{--help} options.
+For example, change location into the directory where @code{mysqld} is
+installed, then run this command:
@example
-shell> EXECDIR/mysqld --basedir=/usr/local --help
+shell> ./mysqld --basedir=/usr/local --verbose --help
@end example
-@code{EXECDIR} represents the pathname to the directory where @code{mysqld}
-is installed.
+This command will show the effect of starting the server with a base
+directory of @file{/usr/local}. You can specify other options such as
+@code{--datadir} as well, but note that @code{--verbose} and @code{--help}
+must be the last options. (Prior to MySQL 4.1, omit the @code{--verbose}
+option.)
+
Once you determine the path settings you want, start the server without
-the @code{--help} option.
+@code{--verbose} and @code{--help}.
+
+If @code{mysqld} is currently running, you can find out what path settings
+it is using by executing this command:
+
+@example
+shell> mysqladmin variables
+@end example
+
+or:
+
+@example
+shell> mysqladmin -h 'your-host-name' variables
+@end example
Whichever method you use to start the server, if it fails to start up
-correctly, check the log file to see if you can find out why. Log files
+correctly, check the error log file to see if you can find out why. Log files
are located in the data directory (typically
-@file{/usr/local/mysql/data} for a binary distribution,
-@file{/usr/local/var} for a source distribution, and
-@file{\mysql\data\mysql.err} on Windows). Look in the data directory for
+@file{C:\mysql\data} on Windows,
+@file{/usr/local/mysql/data} for a Unix binary distribution, and
+@file{/usr/local/var} for a Unix source distribution.
+Look in the data directory for
files with names of the form @file{host_name.err} and
-@file{host_name.log} where @code{host_name} is the name of your server
-host. Then check the last few lines of these files:
+@file{host_name.log}, where @code{host_name} is the name of your server
+host. (Older servers on Windows use @file{mysql.err} as the error log name.)
+Then check the last few lines of these files.
+On Unix, you can use @code{tail} to display the last few lines:
@example
shell> tail host_name.err
@@ -9854,65 +9919,58 @@
@end example
Look for something like the following in the log file:
+
@example
000729 14:50:10 bdb: Recovery function for LSN 1 27595 failed
000729 14:50:10 bdb: warning: ./test/t1.db: No such file or directory
000729 14:50:10 Can't init databases
@end example
-This means that you didn't start @code{mysqld} with @code{--bdb-no-recover}
-and Berkeley DB found something wrong with its log files when it
-tried to recover your databases. To be able to continue, you should
-move away the old Berkeley DB log file from the database directory to
-some other place, where you can later examine it. The log files are
-named @file{log.0000000001}, where the number will increase over time.
-
-If you are running @code{mysqld} with @code{BDB} table support and @code{mysqld} core
-dumps at start this could be because of some problems with the @code{BDB}
-recovery log. In this case you can try starting @code{mysqld} with
-@code{--bdb-no-recover}. If this helps, then you should remove all
-@file{log.*} files from the data directory and try starting @code{mysqld}
-again.
-
-If you get the following error, it means that some other program (or another
-@code{mysqld} server) is already using the TCP/IP port or socket
-@code{mysqld} is trying to use:
+This means that you didn't start @code{mysqld} with the
+@code{--bdb-no-recover} option and Berkeley DB found something wrong with
+its own log files when it tried to recover your databases. To be able to
+continue, you should move away the old Berkeley DB log files from the
+database directory to some other place, where you can later examine them.
+The BDB log files are named in sequence beginning with
+@file{log.0000000001}, where the number increases over time.
+
+If you are running @code{mysqld} with @code{BDB} table support and
+@code{mysqld} dumps core at startup, this could be due to problems with the
+@code{BDB} recovery log. In this case, you can try starting @code{mysqld}
+with @code{--bdb-no-recover}. If that helps helps, then you should remove
+all BDB log files from the data directory and try starting @code{mysqld}
+again without the @code{--bdb-no-recover} option.
+
+If either of the following errors occur, it means that some other program
+(perhaps another @code{mysqld} server) is already using the TCP/IP port or
+socket @code{mysqld} is trying to use:
@example
Can't start server: Bind on TCP/IP port: Address already in use
-@end example
-or:
-@example
Can't start server: Bind on unix socket...
@end example
-Use @code{ps} to make sure that you don't have another @code{mysqld} server
-running. If you can't find another server running, you can try to execute
-the command @code{telnet your-host-name tcp-ip-port-number} and press
-Enter a couple of times. If you don't get an error message like
-@code{telnet: Unable to connect to remote host: Connection refused},
-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
-@end example
-
-or:
-
-@example
-shell> mysqladmin -h 'your-host-name' variables
-@end example
-
-If you get @code{Errcode 13}, which means @code{Permission denied}, when
-starting @code{mysqld} this means that you didn't have the right to
-read/create files in the MySQL database or log directory. In this case
-you should either start @code{mysqld} as the @code{root} user or change the
-permissions for the involved files and directories so that you have the
-right to use them.
+Use @code{ps} to determine whether you have another @code{mysqld} server
+running. If so, shut down the server before starting @code{mysqld} again.
+(If another server is running, and you really want to run multiple servers,
+you can find information about this in @ref{Multiple servers}.)
+
+If no other server is running, try to execute the command @code{telnet
+your-host-name tcp-ip-port-number}. (The default MySQL port number is
+3306.) Then press Enter a couple of times. If you don't get an error
+message like @code{telnet: Unable to connect to remote host: Connection
+refused}, some other program is using the TCP/IP port that @code{mysqld} is
+trying to use. You'll need to track down what program this is and disable
+it, or else tell @code{mysqld} to listen to a different port with the
+@code{--port} option. In this case, you'll also need to specify the port
+number for client programs when connecting to the server via TCP/IP.
+
+If you get @code{Errcode 13} (which means @code{Permission denied}) when
+starting @code{mysqld}, this means that the access privileges of the data
+directory or its contents do not allow the server access. In this case you
+change the permissions for the involved files and directories so that the
+server has the right to use them. You can also start the server as
+@code{root}, but this can raise security issues and should be avoided.
If @code{mysqld_safe} 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
@@ -9928,13 +9986,6 @@
If you can't get @code{mysqld} to start you can try to make a trace file
to find the problem. @xref{Making trace files}.
-If you are using @code{InnoDB} tables, refer to the @code{InnoDB}-specific startup
-options. @xref{InnoDB start}.
-
-If you are using @code{BDB} (Berkeley DB) tables, you should familiarise
-yourself with the different @code{BDB}-specific startup options.
-@xref{BDB start}.
-
@node Upgrade, Operating System Specific Notes, Post-installation, Installing
@section Upgrading/Downgrading MySQL
@@ -9950,17 +10001,19 @@
Before you do an upgrade, you should back up your old databases.
You can always move the MySQL format files and datafiles between different
-versions on the same architecture as long as you have the same base
-version of MySQL. The current base version is 4. If you change the
-character set when running MySQL,
-you must run @code{myisamchk -r -q --set-character-set=charset} on all
-tables. Otherwise, your indexes may not be ordered correctly, because
-changing the character set may also change the sort order.
+versions on the same architecture as long as you stay within versions for
+the same release series of MySQL. The current production release series is
+4.0. If you change the character set when running MySQL, you must run
+@code{myisamchk -r -q --set-character-set=charset} on all tables.
+Otherwise, your indexes may not be ordered correctly, because changing the
+character set may also change the sort order.
If you are cautious about using 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}.
+@code{mysqld} before installing a newer one. For example, if you are using
+MySQL 4.0.18 and want to upgrade to 4.1.1, rename your current server from
+@code{mysqld} to @code{mysqld-4.0.18}. If your new @code{mysqld} then does
+something unexpected, you can simply shut it down and restart with your old
+@code{mysqld}.
If, after an upgrade, you experience problems with recompiled client programs,
such as @code{Commands out of sync} or unexpected core dumps, you probably have
@@ -21450,17 +21503,21 @@
@cindex OpenSSL
@cindex SSL and X509 Basics
-This section discusses how to set up secure (encrypted) connections between
-MySQL clients and the server using the Secure Sockets Layer (SSL) protocol.
+Beginning with version 4.0.0, MySQL has support for secure (encrypted)
+connections between MySQL clients and the server using the Secure Sockets
+Layer (SSL) protocol. This section discusses how to use SSL connections.
It also describes a way to set up SSH on Windows.
The standard configuration of MySQL is intended to be as fast as possible,
so encrypted connections are not used by default. Doing so would make the
client/server protocol much slower. Encrypting data is a CPU-intensive
operation that requires the computer to do additional work and can delay
-other MySQL tasks. Nevertheless, some applications require the security
-provided by encrypted connections. In such cases, the extra computation is
-warranted.
+other MySQL tasks. For applications that require the security provided by
+encrypted connections, the extra computation is warranted.
+
+MySQL allows encryption to be enabled on a per-connection basis. You can
+choose a normal uncrypted connection or a secure encrypted SSL connection
+according the requirements of individual applications.
@menu
* Secure basics:: Basic SSL Concepts
@@ -21474,8 +21531,7 @@
@node Secure basics, Secure requirements, Secure connections, Secure connections
@subsubsection Basic SSL Concepts
-Beginning with version 4.0.0,
-MySQL has support for SSL encrypted connections. To understand how MySQL
+To understand how MySQL
uses SSL, it's necessary to explain some basic SSL and X509 concepts. People
who are already familiar with them can skip this part.
@@ -21483,9 +21539,9 @@
server. This means that someone with access to the network could watch all
your traffic and look at the data being sent or received. They could even
change the data while it is in transit between client and server. To improve
-security a little, you can use the @code{--compress} option when invoking
-the client to compress client/server traffic. However, this will not foil a
-determined attacker.
+security a little, you can compress client/server traffic by using the
+@code{--compress} option when invoking client programs. However, this will
+not foil a determined attacker.
@cindex What is encryption
When you need to move information over a network in a secure fashion,
@@ -21497,13 +21553,13 @@
SSL is a protocol that uses different encryption algorithms to ensure that
data received over a public network can be trusted. It has mechanisms to
-detect any change, loss, or replay of data. SSL also incorporates algorithms
+detect any data change, loss, or replay. SSL also incorporates algorithms
that provide identity verification using the X509 standard.
@cindex What is an X509/Certificate?
X509 makes it possible to identify someone on the Internet. It is most
commonly used in e-commerce applications. In basic terms, there should be
-some company (called a ``Certificate Authority'') that assigns electronic
+some company called a ``Certificate Authority'' (or CA) that assigns electronic
certificates to anyone who needs them. Certificates rely on asymmetric
encryption algorithms that have two encryption keys (a public key and a
secret key). A certificate owner can show the certificate to another party
@@ -21518,15 +21574,16 @@
@node Secure requirements, Secure create certs, Secure basics, Secure connections
@subsubsection Requirements
-To use SSL connections, your version of MySQL must be 4.0.0 or newer and your
-system must be able to support OpenSSL.
+To use SSL connections between the MySQL server and client programs, your
+system must be able to support OpenSSL and your version of MySQL must be
+4.0.0 or newer.
To get secure connections to work with MySQL, you must do the following:
@enumerate
@item
Install the OpenSSL library. We have tested MySQL with OpenSSL 0.9.6.
-@uref{http://www.openssl.org/}.
+If you need OpenSSL, visit @uref{http://www.openssl.org}.
@item
When you configure MySQL, run the @code{configure} script with the
@code{--with-vio} and @code{--with-openssl} options.
@@ -21536,8 +21593,8 @@
This is necessary if your grant tables date from a version prior to MySQL
4.0.0. The upgrade procedure is described in @ref{Upgrading-grant-tables}.
@item
-You can check whether a running @code{mysqld} server supports OpenSSL by
-examining the value of the @code{have_openssl} system variable:
+To check whether a running @code{mysqld} server supports OpenSSL,
+examine the value of the @code{have_openssl} system variable:
@example
mysql> SHOW VARIABLES LIKE 'have_openssl';
@@ -21747,7 +21804,7 @@
' > $DIR/my.cnf
@end example
-To test SSL connections, start the server as follows (where @code{$DIR} is the
+To test SSL connections, start the server as follows, where @code{$DIR} is the
pathname to the directory where the sample @file{my.cnf} option file is
located:
@@ -21776,17 +21833,18 @@
@findex GRANT statement
MySQL can check X509 certificate attributes in addition to the usual
-authentication for client connections that is based on the username and
-password. All the usual options are still required (username, password, IP
-address mask, database/table name).
+authentication that is based on the username and password. To specify
+SSL-related options for a MySQL account, use the @code{REQUIRE} clause of
+the @code{GRANT} statement.
There are different possibilities for limiting connection types for an account:
@itemize @bullet
@item
-Without any SSL or X509 options, unencrypted connections are allowed if the
-username and password are valid. Encrypted connections can be used as well
-if the client has the proper certificate and key files.
+If an account has no SSL or X509 requirements, unencrypted connections are
+allowed if the username and password are valid. However, encrypted
+connections also can be used at the client's option, if the client has the
+proper certificate and key files.
@item
@code{REQUIRE SSL} option limits the server to allow only SSL
@@ -21799,7 +21857,7 @@
@end example
@item
-@code{REQUIRE X509} means that the client should have a valid certificate
+@code{REQUIRE X509} means that the client must have a valid certificate
but that the exact certificate, issuer, and subject do not matter.
The only requirement is that it should be possible to verify its
signature with one of the CA certificates.
@@ -21813,7 +21871,7 @@
@code{REQUIRE ISSUER 'issuer'} places the restriction on connection attempts
that the client must present a valid X509 certificate issued by CA
@code{'issuer'}. If the client presents a certificate that is valid but has
-a different @code{'issuer'}, the server rejects the connection. Use of X509
+a different issuer, the server rejects the connection. Use of X509
certificates always implies encryption, so the @code{SSL} option is
unneccessary.
@@ -21830,7 +21888,7 @@
@code{REQUIRE SUBJECT 'subject'} places the restriction on connection
attempts that the client must present a valid X509 certificate with subject
@code{'subject'} on it. If the client presents a certificate that is valid
-but has a different @code{'subject'}, the server rejects the connection.
+but has a different subject, the server rejects the connection.
@example
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@@'localhost'
@@ -21868,7 +21926,7 @@
-> AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
@end example
-Starting from MySQL 4.0.4 the @code{AND} keyword is optional between
+Starting from MySQL 4.0.4, the @code{AND} keyword is optional between
@code{REQUIRE} options.
The order of the options does not matter, but no option can be specified
@@ -21888,13 +21946,17 @@
@table @code
@item --ssl
-For the server, specifies that the server allows SSL connections.
-For a client program, allows the client to connect to the server using SSL.
-This option itself is not sufficient to cause an SSL connection to be used.
+For the server, this option specifies that the server allows SSL connections.
+For a client program, it allows the client to connect to the server using SSL.
+This option is not sufficient in itself to cause an SSL connection to be used.
You must also specify the
@code{--ssl-ca}, @code{--ssl-cert}, and @code{--ssl-key} options.
-Note that this option doesn't @strong{require} an SSL connection.
+This option is more often used in its opposite form to indicate that SSL
+should @strong{not} be used. To do this, specify the option as
+@code{--skip-ssl} or @code{--ssl=0}.
+
+Note that use of @code{--ssl} doesn't @strong{require} an SSL connection.
For example, if the server or client are compiled without SSL support,
a normal unencrypted connection will be used.
@@ -21904,9 +21966,6 @@
Then use this account to connect to the server, with both a server and client
that have SSL support enabled.
-You can use this option to indicate that the connection should not use SSL.
-Do this by specifying the option as @code{--skip-ssl} or @code{--ssl=0}.
-
@item --ssl-ca=file_name
The path to a file with a list of trusted SSL CAs.
@@ -21981,6 +22040,9 @@
@node Disaster Prevention, Localisation, User Account Management, MySQL Database Administration
@section Disaster Prevention and Recovery
+@c TODO: a lot of the information here assumes MyISAM implicitly and won't
+@c necessarily work for other storage engines.
+
@c help_category Recovery
This section discusses how to make database backups and how to perform
@@ -22003,19 +22065,20 @@
Because MySQL tables are stored as files, it is easy to do a
backup. To get a consistent backup, do a @code{LOCK TABLES} on the
-relevant tables followed by @code{FLUSH TABLES} for the tables.
+relevant tables, followed by @code{FLUSH TABLES} for the tables.
@xref{LOCK TABLES, , @code{LOCK TABLES}}.
@xref{FLUSH, , @code{FLUSH}}.
-You only need a read lock; this allows other threads to continue to
+You only need a read lock; this allows other clients to continue to
query the tables while you are making a copy of the files in the
database directory. The @code{FLUSH TABLES} statement is needed to ensure that
the all active index pages are written to disk before you start the backup.
-Starting from MySQL 3.23.56 and 4.0.12, @code{BACKUP TABLE} will not allow you
-to overwrite existing files because this would be a security risk.
If you want to make an SQL level backup of a table, you can use
-@code{SELECT INTO OUTFILE} or @code{BACKUP TABLE}.
+@code{SELECT INTO ... OUTFILE} or @code{BACKUP TABLE}.
+For @code{SELECT INTO ... OUTFILE}, the output file cannot already exist.
+For @code{BACKUP TABLE}, the same is true as of
+MySQL 3.23.56 and 4.0.12, because this would be a security risk.
@xref{SELECT, , @code{SELECT}}.
@xref{BACKUP TABLE, , @code{BACKUP TABLE}}.
@@ -22039,7 +22102,7 @@
You can also simply copy all table files (@file{*.frm}, @file{*.MYD}, and
@file{*.MYI} files) as long as the server isn't updating anything.
-The script @code{mysqlhotcopy} does use this method. (But note that these
+The @code{mysqlhotcopy} script uses this method. (But note that these
methods
will not work if your database contains @code{InnoDB} tables. @code{InnoDB}
does not store table contents in database directories, and @code{mysqlhotcopy}
@@ -22054,65 +22117,78 @@
you executed @code{mysqldump}.
@end enumerate
-If your MySQL server is a slave, whatever backup method you choose,
-when you backup your slave's data, you should also backup the
+If your MySQL server is a slave replication server, then regardless of the
+backup method you choose, you should also backup the
@file{master.info} and @file{relay-log.info} files
-which are always needed to resume replication after you restore
+when you backup your slave's data. These files
+are always needed to resume replication after you restore
the slave's data. If your slave is subject to replicating @code{LOAD
-DATA INFILE} commands, you should also backup the @file{SQL_LOAD-*} files
-which may exist in the directory specified by the @code{--slave-load-tmpdir}
+DATA INFILE} commands, you should also back up any @file{SQL_LOAD-*} files
+that may exist in the directory specified by the @code{--slave-load-tmpdir}
option. (This location defaults to the value of the @code{tmpdir} variable
-if not specified.) The slave will need these files to resume
+if not specified.) The slave needs these files to resume
replication of any interrupted @code{LOAD DATA INFILE} operations.
If you have to restore something, try to recover your tables using
@code{REPAIR TABLE} or @code{myisamchk -r} first. That should work in
-99.9% of all cases. If @code{myisamchk} fails, try the following
-procedure (this will work only if you have started MySQL with
-@code{--log-bin}, see @ref{Binary log}):
+99.9% of all cases. If @code{myisamchk} fails, try the following procedure.
+Note that it will work only if you have enabled binary logging by starting
+MySQL with the @code{--log-bin} option; see @ref{Binary log}.
@enumerate
@item
Restore the original @code{mysqldump} backup, or binary backup.
@item
-Execute the following command to re-run the updates in the binary log:
+Execute the following command to re-run the updates in the binary logs:
@example
shell> mysqlbinlog hostname-bin.[0-9]* | mysql
@end example
-In your case you may want to re-run only certain binlogs, from
-certain positions (usually you want to re-run all binlogs from the
-date of the restored backup, possibly excepted some wrong queries).
-See @ref{mysqlbinlog} for more information on the @code{mysqlbinlog}
-utility and how to use it.
+In your case you may want to re-run only certain binary logs, from
+certain positions (usually you want to re-run all binary logs from the
+date of the restored backup, excepting possibly some incorrect queries).
+See @ref{mysqlbinlog, , @code{mysqlbinlog}} for more information on
+the @code{mysqlbinlog} utility and how to use it.
-If you are using the update log (which is removed in MySQL 5.0.0)
-you can execute the content of the update log like this:
+If you are using the update logs instead,
+you can process their contents like this:
@example
shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
@end example
+
+@code{ls} is used to sort the update log filenamess into the right order.
+
@end enumerate
-@code{ls} is used to get all the update log files in the right order.
+You can also do selective backups of individual files:
-You can also do selective backups with @code{SELECT * INTO OUTFILE 'file_name'
-FROM tbl_name} and restore with @code{LOAD DATA INFILE 'file_name' REPLACE
-...} To avoid duplicate records, you need a @code{PRIMARY KEY} or a
-@code{UNIQUE} key in the table. The @code{REPLACE} keyword causes old records
+@itemize @bullet
+@item
+To dump the table, use
+@code{SELECT * INTO OUTFILE 'file_name' FROM tbl_name}.
+
+@item
+To reload the table, use and restore with
+@code{LOAD DATA INFILE 'file_name' REPLACE ...}
+To avoid duplicate records, the table must have a @code{PRIMARY KEY} or a
+@code{UNIQUE} index. The @code{REPLACE} keyword causes old records
to be replaced with new ones when a new record duplicates an old record on
a unique key value.
-If you get performance problems in making backups on your system, you can
-solve this by setting up replication and do the backups on the slave
-instead of on the master. @xref{Replication Intro}.
+@end itemize
+
+If you have performance problems with your server while making backups, one
+strategy that can help is to set up replication and perform backups on the
+slave rather than on the master.
+@xref{Replication Intro}.
-If you are using a Veritas filesystem, you can do:
+If you are using a Veritas filesystem, you can make a backup like this:
@enumerate
@item
-From a client (or Perl), execute: @code{FLUSH TABLES WITH READ LOCK}.
+From a client program, execute: @code{FLUSH TABLES WITH READ LOCK}.
@item
From another shell, execute: @code{mount vxfs snapshot}.
@@ -22121,43 +22197,47 @@
From the first client, execute: @code{UNLOCK TABLES}.
@item
-Copy files from snapshot.
+Copy files from the snapshot.
@item
-Unmount snapshot.
+Unmount the snapshot.
@end enumerate
@node Table maintenance, Maintenance regimen, Backup, Disaster Prevention
@subsection Using @code{myisamchk} for Table Maintenance and Crash Recovery
-Starting with MySQL Version 3.23.13, you can check @code{MyISAM}
-tables with the @code{CHECK TABLE} command. @xref{CHECK TABLE}. You can
-repair tables with the @code{REPAIR TABLE} command. @xref{REPAIR TABLE}.
-
-To check/repair @code{MyISAM} tables (@file{.MYI} and @file{.MYD}) you should
-use the @code{myisamchk} utility. To check/repair @code{ISAM} tables
-(@file{.ISM} and @file{.ISD}) you should use the @code{isamchk}
-utility. @xref{Table types}.
-In the following text we will talk about @code{myisamchk}, but everything
-also applies to the old @code{isamchk}.
+The following text discusses how to use @code{myisamchk} to check or repair
+@code{MyISAM} tables (tables with @file{.MYI} and @file{.MYD} files).
+The same concepts apply to using @code{isamchk} to check or repair
+@code{ISAM} tables (tables with @file{.ISM} and @file{.ISD} files).
+@xref{Table types}.
You can use the @code{myisamchk} utility to get information about your
-database tables, check and repair them, or optimize them. The following
+database tables or to check, repair, or optimize them. The following
sections describe how to invoke @code{myisamchk} (including a
description of its options), how to set up a table maintenance schedule,
and how to use @code{myisamchk} to perform its various functions.
-You can, in most cases, also use the command @code{OPTIMIZE TABLES} to
+Note that even though table repair with @code{myisamchk} is quite secure, it's
+always a good idea to make a backup @emph{before} doing a repair (or any
+maintenance operation that could make a lot of changes to a table)
+
+An alternative to using @code{myisamchk} for checking or repairing
+@code{MyISAM} tables is to use the @code{CHECK TABLE} or @code{REPAIR TABLE}
+statements, which are available as of MySQL 3.23.13.
+See @ref{CHECK TABLE, , @code{CHECK TABLE}} and @ref{REPAIR TABLE, ,
+@code{REPAIR TABLE}}.
+
+In most cases, you can also use the @code{OPTIMIZE TABLES} statement to
optimize and repair tables, but this is not as fast or reliable (in case
-of real fatal errors) as @code{myisamchk}. On the other hand,
+of really serious problems) as @code{myisamchk}. On the other hand,
@code{OPTIMIZE TABLE} is easier to use and you don't have to worry about
flushing tables.
@xref{OPTIMIZE TABLE, , @code{OPTIMIZE TABLE}}.
-Even though the repair in @code{myisamchk} is quite secure, it's always a
-good idea to make a backup @emph{before} doing a repair (or anything that could
-make a lot of changes to a table)
+The @code{mysqlcheck} client program provides a command-line interface to the
+@code{CHECK TABLE}, @code{REPAIR TABLE}, and @code{OPTIMIZE TABLE} statements.
@menu
* myisamchk syntax:: @code{myisamchk} Invocation Syntax
@@ -22183,25 +22263,28 @@
@end example
The @code{options} specify what you want @code{myisamchk} to do. They are
-described here. (You can also get a list of options by invoking
-@code{myisamchk --help}.) With no options, @code{myisamchk} simply checks your
-table. To get more information or to tell @code{myisamchk} to take corrective
-action, specify options as described here and in the following sections.
+described in the following sections. You can also get a list of options by
+invoking @code{myisamchk --help}.
-@code{tbl_name} is the database table you want to check/repair. If you run
+With no options, @code{myisamchk} simply checks your table as the default
+operation. To get more information or to tell @code{myisamchk} to take
+corrective action, specify options as described in the following discussion.
+
+@code{tbl_name} is the database table you want to check or repair. If you run
@code{myisamchk} somewhere other than in the database directory, you must
-specify the path to the file, because @code{myisamchk} has no idea where your
-database is located. Actually, @code{myisamchk} doesn't care whether
-the files you are working on are located in a database directory; you can
-copy the files that correspond to a database table into another location and
+specify the path to the database directory, because @code{myisamchk} has no
+idea where the
+database is located. In fact @code{myisamchk} doesn't actually care whether
+the files you are working on are located in a database directory. You can
+copy the files that correspond to a database table into some other location and
perform recovery operations on them there.
You can name several tables on the @code{myisamchk} command-line if you
-wish. You can also specify a name as an index file
-name (with the @file{.MYI} suffix), which allows you to specify all
-tables in a directory by using the pattern @file{*.MYI}.
-For example, if you are in a database directory, you can check all the
-tables in the directory like this:
+wish. You can also specify a table by naming its index file (the file with
+the @file{.MYI} suffix). This allows you to specify all tables in a
+directory by using the pattern @file{*.MYI}. For example, if you are in a
+database directory, you can check all the @code{MyISAM} tables in that
+directory like this:
@example
shell> myisamchk *.MYI
@@ -22221,41 +22304,48 @@
shell> myisamchk /path/to/datadir/*/*.MYI
@end example
-The recommended way to quickly check all tables is:
+The recommended way to quickly check all @code{MyISAM} and @code{ISAM}
+tables is:
@example
-myisamchk --silent --fast /path/to/datadir/*/*.MYI
-isamchk --silent /path/to/datadir/*/*.ISM
+shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI
+shell> isamchk --silent /path/to/datadir/*/*.ISM
@end example
-If you want to check all tables and repair all tables that are corrupted,
-you can use the following line:
+If you want to check all @code{MyISAM} and @code{ISAM} tables and repair any
+that are corrupted, you can use the following commands:
@example
-myisamchk --silent --force --fast --update-state -O key_buffer=64M \
+shell> myisamchk --silent --force --fast --update-state -O key_buffer=64M \
-O sort_buffer=64M -O read_buffer=1M -O write_buffer=1M \
/path/to/datadir/*/*.MYI
-isamchk --silent --force -O key_buffer=64M -O sort_buffer=64M \
- -O read_buffer=1M -O write_buffer=1M /path/to/datadir/*/*.ISM
+shell> isamchk --silent --force -O key_buffer=64M -O sort_buffer=64M \
+ -O read_buffer=1M -O write_buffer=1M /path/to/datadir/*/*.ISM
@end example
-The above assumes that you have more than 64 M free.
+The above assumes that you have more than 64M free.
+For more information about memory allocation with @code{myisamchk}, see
+@ref{myisamchk memory}.
+
+You must ensure that no other program is using the tables while you are
+running @code{myisamchk}.
-Note that if you get an error like:
+When you run @code{myisamchk}, it may display the following error message:
@example
-myisamchk: warning: 1 clients is using or hasn't closed the table properly
+warning: 1 clients is using or hasn't closed the table properly
@end example
This means that you are trying to check a table that has been updated by
another program (like the @code{mysqld} server) that hasn't yet closed
the file or that has died without closing the file properly.
-If @code{mysqld} is running, you must force a sync/close of all
-tables with @code{FLUSH TABLES} and ensure that no one is using the
-tables while you are running @code{myisamchk}. In MySQL Version 3.23
-the easiest way to avoid this problem is to use @code{CHECK TABLE}
-instead of @code{myisamchk} to check tables.
+If @code{mysqld} is running, you must force it to flush any table
+modifications that are still buffered in memory by using @code{FLUSH
+TABLES}. You should then ensure that no one is using the tables while you
+are running @code{myisamchk}. In MySQL Version 3.23 the easiest way to
+avoid this problem is to use @code{CHECK TABLE} instead of @code{myisamchk}
+to check tables.
@node myisamchk general options, myisamchk check options, myisamchk syntax, Table maintenance
@subsubsection General Options for @code{myisamchk}
@@ -22263,71 +22353,87 @@
@cindex options, @code{myisamchk}
@cindex @code{myisamchk}, options
-@code{myisamchk} supports the following options.
+The options described in this section can be used for any
+type of table maintenance operation performed by @code{myisamchk}.
+The following sections describe options that pertain only to specific
+operations, such as table checking or repairing.
@table @code
-@item -# or --debug=debug_options
+
+@item --debug=debug_options, -# debug_options
Output debug log. The @code{debug_options} string often is
@code{'d:t:o,filename'}.
-@item -? or --help
+
+@item --help, -?
Display a help message and exit.
-@item -O name=value, --set-variable=name=value
+
+@item --set-variable=name=value, -O name=value
Set the value of a variable.
Please note that @code{--set-variable=name=value} and @code{-O name=value}
syntax is deprecated as of MySQL 4.0. Use @code{--name=value} instead.
The possible variables and their default values
-for myisamchk can be examined with @code{myisamchk --help}:
-@multitable @columnfractions .20 .10
+for @code{myisamchk} can be examined with @code{myisamchk --help}:
+
+@multitable @columnfractions .25 .10
@item @strong{Variable} @tab @strong{Value}
-@item key_buffer_size @tab 523264
-@item read_buffer_size @tab 262136
-@item write_buffer_size @tab 262136
-@item sort_buffer_size @tab 2097144
-@item sort_key_blocks @tab 16
-@item decode_bits @tab 9
+@item @code{key_buffer_size} @tab 523264
+@item @code{read_buffer_size} @tab 262136
+@item @code{write_buffer_size} @tab 262136
+@item @code{sort_buffer_size} @tab 2097144
+@item @code{sort_key_blocks} @tab 16
+@item @code{decode_bits} @tab 9
@end multitable
@code{sort_buffer_size} is used when the keys are repaired by sorting
keys, which is the normal case when you use @code{--recover}.
@code{key_buffer_size} is used when you are checking the table with
-@code{--extended-check} or when the keys are repaired by inserting key
-row by row in to the table (like when doing normal inserts). Repairing
+@code{--extended-check} or when the keys are repaired by inserting keys
+row by row into the table (like when doing normal inserts). Repairing
through the key buffer is used in the following cases:
@itemize @bullet
+
@item
If you use @code{--safe-recover}.
+
@item
-If the temporary files needed to sort the keys would be more than twice
-as big as when creating the key file directly. This is often the case
-when you have big @code{CHAR}, @code{VARCHAR} or @code{TEXT} keys as the
-sort needs to store the whole keys during sorting. If you have lots
-of temporary space and you can force @code{myisamchk} to repair by sorting
-you can use the @code{--sort-recover} option.
+If the temporary files needed to sort the keys would be more than twice as
+big as when creating the key file directly. This is often the case when you
+have large key values for @code{CHAR}, @code{VARCHAR}, or @code{TEXT}
+columns, because the sort operation needs to store the complete key values
+as it proceeds. If you have lots of temporary space and you can force
+@code{myisamchk} to repair by sorting you can use the @code{--sort-recover}
+option.
+
@end itemize
Reparing through the key buffer takes much less disk space than using
sorting, but is also much slower.
-If you want a faster repair, set the above variables to about 1/4 of your
-available memory. You can set both variables to big values, as only one
-of the above buffers will be used at a time.
+If you want a faster repair, set the @code{key_buffer_size} and
+@code{sort_buffer_size} variables to about 25% of your available memory.
+You can set both variables to large values, because only one of them is at a
+time.
-@item -s or --silent
+@item --silent, -s
Silent mode. Write output only when errors occur. You can use @code{-s}
twice (@code{-ss}) to make @code{myisamchk} very silent.
-@item -v or --verbose
+
+@item --verbose, -v
Verbose mode. Print more information. This can be used with @code{-d} and
-@code{-e}. Use @code{-v} multiple times (@code{-vv}, @code{-vvv}) for more
-verbosity!
-@item -V or --version
+@code{-e}. Use @code{-v} multiple times (@code{-vv}, @code{-vvv}) for even
+more output.
+
+@item --version, -V
Print the @code{myisamchk} version and exit.
-@item -w or, --wait
-Instead of giving an error if the table is locked, wait until the table
-is unlocked before continuing. Note that if you are running @code{mysqld}
-on the table with @code{--skip-external-locking}, the table can only be locked
-by another @code{myisamchk} command.
+
+@item --wait, -w
+Instead of terminating with an error if the table is locked, wait until the
+table is unlocked before continuing. Note that if you are running
+@code{mysqld} with the @code{--skip-external-locking} option, the table can
+only be locked by another @code{myisamchk} command.
+
@end table
@@ -22337,46 +22443,57 @@
@cindex check options, myisamchk
@cindex tables, checking
+The following options are used for table checking operations:
+
@table @code
-@item -c or --check
-Check table for errors. This is the default operation if you are not
-giving @code{myisamchk} any options that override this.
-@item -e or --extend-check
-Check the table very thoroughly (which is quite slow if you have many
-indexes). This option should only be used in extreme cases. Normally,
-@code{myisamchk} or @code{myisamchk --medium-check} should, in most
-cases, be able to find out if there are any errors in the table.
+@item --check, -c
+Check the table for errors. This is the default operation if you specify no
+option that selects an operation type explicitly.
-If you are using @code{--extended-check} and have much memory, you should
-increase the value of @code{key_buffer_size} a lot!
+@item --check-only-changed, -C
+Check only tables that have changed since the last check.
+
+@item --extend-check, -e
+Check the table very thoroughly. This is quite slow if the table has many
+indexes. This option should only be used in extreme cases. Normally,
+@code{myisamchk} or @code{myisamchk --medium-check} should
+be able to determine whether there are any errors in the table.
+
+If you are using @code{--extended-check} and have plenty of memory, setting
+the @code{key_buffer_size} variable to a large value will help the repair
+operation run faster.
-@item -F or --fast
+@item --fast, -F
Check only tables that haven't been closed properly.
-@item -C or --check-only-changed
-Check only tables that have changed since the last check.
-@item -f or --force
+
+@item --force, -f
Restart @code{myisamchk} with @code{-r} (repair) on the table, if
@code{myisamchk} finds any errors in the table.
-@item -i or --information
+
+@item --information, -i
Print informational statistics about the table that is checked.
-@item -m or --medium-check
+
+@item --medium-check, -m
Faster than extended-check, but only finds 99.99% of all errors.
Should, however, be good enough for most cases.
-@item -U or --update-state
+
+@item --read-only, -T
+Don't mark table as checked. This is useful if you use @code{myisamchk}
+to check a table that is in use by some other application that doesn't
+use locking (like @code{mysqld --skip-external-locking}).
+
+@item --update-state, -U
Store in the @file{.MYI} file when the table was checked and if the table crashed. This should be used to get full benefit of the
@code{--check-only-changed} option, but you shouldn't use this
option if the @code{mysqld} server is using the table and you are
running @code{mysqld} with @code{--skip-external-locking}.
-@item -T or --read-only
-Don't mark table as checked. This is useful if you use @code{myisamchk}
-to check a table that is in use by some other application that doesn't
-use locking (like @code{mysqld --skip-external-locking}).
+
@end table
@node myisamchk repair options, myisamchk other options, myisamchk check options, Table maintenance
-@subsubsection Repair Options for myisamchk
+@subsubsection Repair Options for @code{myisamchk}
@cindex repair options, myisamchk
@cindex files, repairing
@@ -50706,7 +50823,7 @@
The index is stored in a file with the @file{.MYI} (MYIndex) extension,
and the data is stored in a file with the @file{.MYD} (MYData) extension.
-You can check/repair @code{MyISAM} tables with the @code{myisamchk}
+You can check or repair @code{MyISAM} tables with the @code{myisamchk}
utility. @xref{Crash recovery}. You can compress @code{MyISAM} tables with
@code{myisampack} to take up much less space.
@xref{myisampack, , @code{myisampack}}.
@@ -55815,7 +55932,7 @@
@code{ISAM} uses a @code{B-tree} index. The index is stored in a file
with the @file{.ISM} extension, and the data is stored in a file with
the @file{.ISD} extension.
-You can check/repair @code{ISAM} tables with the @code{isamchk} utility.
+You can check or repair @code{ISAM} tables with the @code{isamchk} utility.
@xref{Crash recovery}.
@code{ISAM} has the following features/properties:
| Thread |
|---|
| • bk commit - mysqldoc tree (paul:1.1302) | paul | 23 Feb |
