Author: paul
Date: 2006-01-28 06:53:43 +0100 (Sat, 28 Jan 2006)
New Revision: 1077
Log:
r6801@frost: paul | 2006-01-27 23:52:44 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/database-administration.xml
trunk/refman-4.1/optimization.xml
trunk/refman-5.0/database-administration.xml
trunk/refman-5.0/optimization.xml
trunk/refman-5.1/database-administration.xml
trunk/refman-5.1/optimization.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6800
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2588
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6801
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2588
Modified: trunk/refman-4.1/database-administration.xml
===================================================================
--- trunk/refman-4.1/database-administration.xml 2006-01-28 05:53:29 UTC (rev 1076)
+++ trunk/refman-4.1/database-administration.xml 2006-01-28 05:53:43 UTC (rev 1077)
@@ -10626,15 +10626,15 @@
you because there is little reason to assume that a given
username belongs to the same person everywhere on the Internet.
For example, the user <literal>joe</literal> who connects from
- <literal>office.com</literal> need not be the same person as the
- user <literal>joe</literal> who connects from
- <literal>elsewhere.com</literal>. MySQL handles this by allowing
- you to distinguish users on different hosts that happen to have
- the same name: You can grant one set of privileges for
+ <literal>office.example.com</literal> need not be the same
+ person as the user <literal>joe</literal> who connects from
+ <literal>home.example.com</literal>. MySQL handles this by
+ allowing you to distinguish users on different hosts that happen
+ to have the same name: You can grant one set of privileges for
connections by <literal>joe</literal> from
- <literal>office.com</literal>, and a different set of privileges
- for connections by <literal>joe</literal> from
- <literal>elsewhere.com</literal>.
+ <literal>office.example.com</literal>, and a different set of
+ privileges for connections by <literal>joe</literal> from
+ <literal>home.example.com</literal>.
</para>
<para>
@@ -15709,7 +15709,8 @@
<title>&title-secure-create-certs;</title>
<para>
- Here is an example for setting up SSL certificates for MySQL:
+ Here is an example of setting up SSL certificates for MySQL
+ using OpenSSL:
</para>
<programlisting>
@@ -16008,7 +16009,7 @@
<para>
The path to a directory that contains trusted SSL CA
- certificates in pem format.
+ certificates in PEM format.
</para>
</listitem>
@@ -16074,8 +16075,8 @@
</remark>
<para>
- Here is a note about how to connect to get a secure connection
- to remote MySQL server with SSH (by David Carlson
+ Here is a note that describes how to get a secure connection
+ to a remote MySQL server with SSH (by David Carlson
<email>dcarlson@stripped</email>):
</para>
@@ -16101,10 +16102,12 @@
<listitem>
<para>
Start your Windows SSH client. Set <literal>Host_Name =
- yourmysqlserver_URL_or_IP</literal>. Set
- <literal>userid=your_userid</literal> to log in to your
- server. This <literal>userid</literal> value may not be
- the same as the username of your MySQL account.
+ <replaceable>yourmysqlserver_URL_or_IP</replaceable></literal>.
+ Set
+ <literal>userid=<replaceable>your_userid</replaceable></literal>
+ to log in to your server. This <literal>userid</literal>
+ value might not be the same as the username of your MySQL
+ account.
</para>
</listitem>
@@ -16112,10 +16115,11 @@
<para>
Set up port forwarding. Either do a remote forward (Set
<literal>local_port: 3306</literal>, <literal>remote_host:
- yourmysqlservername_or_ip</literal>, <literal>remote_port:
- 3306</literal>) or a local forward (Set <literal>port:
- 3306</literal>, <literal>host: localhost</literal>,
- <literal>remote port: 3306</literal>).
+ <replaceable>yourmysqlservername_or_ip</replaceable></literal>,
+ <literal>remote_port: 3306</literal> ) or a local forward
+ (Set <literal>port: 3306</literal>, <literal>host:
+ localhost</literal>, <literal>remote port:
+ 3306</literal>).
</para>
</listitem>
@@ -16145,8 +16149,7 @@
Create a new file in Windows and link to MySQL using the
ODBC driver the same way you normally do, except type in
<literal>localhost</literal> for the MySQL host server,
- not
- <literal><replaceable>yourmysqlservername</replaceable></literal>.
+ not <replaceable>yourmysqlservername</replaceable>.
</para>
</listitem>
@@ -19805,7 +19808,7 @@
<para>
On Windows, you cannot rename the log file while the server has
- it open. You must stop the server and rename the log. Then
+ it open. You must stop the server and rename it, and then
restart the server to create a new log.
</para>
@@ -19904,26 +19907,30 @@
</indexterm>
<para>
- The binary log has replaced the old update log, which is being
- phased out of future MySQL release series after 4.1. The binary
- log contains all information that is available in the update log
- in a more efficient format and in a manner that is
- transaction-safe.
+ The binary log contains all statements that update data or
+ (starting from MySQL 4.1.3) potentially could have updated it
+ (for example, a <literal>DELETE</literal> which matched no
+ rows). Statements are stored in the form of
+ <quote>events</quote> that describe the modifications. The
+ binary log also contains information about how long each
+ statement took that updated the database.
</para>
<para>
- The binary log contains all statements which updated data or
- (starting from MySQL 4.1.3) could potentially have updated it
- (for example, a <literal>DELETE</literal> which matched no
- rows).
+ <emphasis role="bold">Note</emphasis>: The binary log has
+ replaced the old update log, which is being phased out of future
+ MySQL release series after 4.1. The binary log contains all
+ information that is available in the update log in a more
+ efficient format and in a manner that is transaction-safe. If
+ you are using transactions, you must use the MySQL binary log
+ for backups instead of the old update log.
</para>
<para>
- The binary log also contains information about how long each
- statement took that updated the database. It does not contain
- statements that do not modify any data. If you want to log all
- statements (for example, to identify a problem query) you should
- use the general query log. See <xref linkend="query-log"/>.
+ The binary log does not contain statements that do not modify
+ any data. If you want to log all statements (for example, to
+ identify a problem query), use the general query log. See
+ <xref linkend="query-log"/>.
</para>
<para>
@@ -19990,6 +19997,15 @@
</para>
<para>
+ Before MySQL 4.1.9, a write to a binary log file or binary log
+ index file that failed due to a full disk or an exceeded quota
+ resulted in corruption of the file. Starting from MySQL 4.1.9,
+ writes to the binary log file and binary log index file are
+ handled the same way as writes to <literal>MyISAM</literal>
+ tables. See <xref linkend="full-disk"/>.
+ </para>
+
+ <para>
You can delete all binary log files with the <literal>RESET
MASTER</literal> statement, or only some of them with
<literal>PURGE MASTER LOGS</literal>. See
@@ -20231,13 +20247,14 @@
<para>
If you are using replication, you should not delete old binary
log files until you are sure that no slave still needs to use
- them. One way to do this is to do <command>mysqladmin
- flush-logs</command> once a day and then remove any logs that
- are more than three days old. You can remove them manually, or
- preferably using <literal>PURGE MASTER LOGS</literal> (see
- <xref linkend="replication-master-sql"/>), which also safely
- updates the binary log index file for you (and which can take a
- date argument since MySQL 4.1)
+ them. For example, if your slaves never run more than three days
+ behind, once a day you can execute <command>mysqladmin
+ flush-logs</command> on the master and then remove any logs that
+ are more than three days old. You can remove the files manually,
+ but it is preferable to use <literal>PURGE MASTER
+ LOGS</literal>, which also safely updates the binary log index
+ file for you (and which can take a date argument as of MySQL
+ 4.1). See <xref linkend="replication-master-sql"/>.
</para>
<para>
@@ -20248,41 +20265,40 @@
</para>
<para>
- You can examine the binary log file with the
+ You can display the contents of binary log files with the
<command>mysqlbinlog</command> utility. This can be useful when
you want to reprocess statements in the log. For example, you
can update a MySQL server from the binary log as follows:
</para>
<programlisting>
-shell> <userinput>mysqlbinlog log-file | mysql -h server_name</userinput>
+shell> <userinput>mysqlbinlog <replaceable>log_file</replaceable> | mysql -h <replaceable>server_name</replaceable></userinput>
</programlisting>
<para>
See <xref linkend="mysqlbinlog"/>, for more information on the
<command>mysqlbinlog</command> utility and how to use it.
+ <command>mysqlbinlog</command> also can be used with relay log
+ files because they are written using the same format as binary
+ log files.
</para>
<para>
- If you are using transactions, you must use the MySQL binary log
- for backups instead of the old update log.
- </para>
-
- <para>
- The binary logging is done immediately after a query completes
+ Binary logging is done immediately after a statement completes
but before any locks are released or any commit is done. This
- ensures that the log is logged in the execution order.
+ ensures that the log is logged in execution order.
</para>
<para>
Updates to non-transactional tables are stored in the binary log
- immediately after execution. For transactional tables such as
- <literal>BDB</literal> or <literal>InnoDB</literal> tables, all
- updates (<literal>UPDATE</literal>, <literal>DELETE</literal>,
- or <literal>INSERT</literal>) that change tables are cached
- until a <literal>COMMIT</literal> statement is received by the
- server. At that point, <command>mysqld</command> writes the
- entire transaction to the binary log before the
+ immediately after execution. Within an uncommitted transaction,
+ all updates (<literal>UPDATE</literal>,
+ <literal>DELETE</literal>, or <literal>INSERT</literal>) that
+ change transactional tables such as <literal>BDB</literal> or
+ <literal>InnoDB</literal> tables are cached until a
+ <literal>COMMIT</literal> statement is received by the server.
+ At that point, <command>mysqld</command> writes the entire
+ transaction to the binary log before the
<literal>COMMIT</literal> is executed. When the thread that
handles the transaction starts, it allocates a buffer of
<literal>binlog_cache_size</literal> to buffer statements. If a
@@ -20292,28 +20308,36 @@
</para>
<para>
+ Modifications to non-transactional tables cannot be rolled back.
+ If a transaction that is rolled back includes modifications to
+ non-transactional tables, the entire transaction is logged with
+ a <literal>ROLLBACK</literal> statement at the end to ensure
+ that the modifications to those tables are replicated.
+ </para>
+
+ <para>
The <literal>Binlog_cache_use</literal> status variable shows
the number of transactions that used this buffer (and possibly a
temporary file) for storing statements. The
<literal>Binlog_cache_disk_use</literal> status variable shows
- how many of those transactions actually did have to use a
- temporary file. These two variables can be used for tuning
+ how many of those transactions actually had to use a temporary
+ file. These two variables can be used for tuning
<literal>binlog_cache_size</literal> to a large enough value
that avoids the use of temporary files.
</para>
<para>
- The <literal>max_binlog_cache_size</literal> (default 4GB) can
- be used to restrict the total size used to cache a
- multiple-statement transaction. If a transaction is larger than
- this, it fails and rolls back.
+ The <literal>max_binlog_cache_size</literal> system variable
+ (default 4GB) can be used to restrict the total size used to
+ cache a multiple-statement transaction. If a transaction is
+ larger than this, it fails and rolls back.
</para>
<para>
If you are using the update log or binary log, concurrent
inserts are converted to normal inserts for <literal>CREATE ...
SELECT</literal> or <literal>INSERT ... SELECT</literal>
- statement. This is done to ensure that you can re-create an
+ statements. This is done to ensure that you can re-create an
exact copy of your tables by applying the log during a backup
operation.
</para>
@@ -20327,71 +20351,62 @@
<para>
By default, the binary log is not synchronized to disk at each
- write. So if the operating system or machine (and not only the
- MySQL server) crashes, there is a chance that the last
- statements in the binary log are lost. To prevent this, you can
- force the binary log to be synchronized to disk after every
- <replaceable>N</replaceable>th binary log write using the
- <literal>sync_binlog</literal> global variable
- (<literal>1</literal> being the safest value, but also the
- slowest). See <xref linkend="server-system-variables"/>. Even
+ write. So if the operating system or machine (not only the MySQL
+ server) crashes, there is a chance that the last statements of
+ the binary log are lost. To prevent this, you can make the
+ binary log be synchronized to disk after every
+ <replaceable>N</replaceable> writes to the binary log, with the
+ <literal>sync_binlog</literal> system variable. See
+ <xref linkend="server-system-variables"/>. 1 is the safest value
+ for <literal>sync_binlog</literal>, but also the slowest. Even
with <literal>sync_binlog</literal> set to 1, there is still the
- chance of an inconsistency between tables content and binary log
- content in the event of a crash. For example, when using
- <literal>InnoDB</literal> tables, if the MySQL server processes
+ chance of an inconsistency between the table content and binary
+ log content in case of a crash. For example, if you are using
+ <literal>InnoDB</literal> tables and the MySQL server processes
a <literal>COMMIT</literal> statement, it writes the whole
transaction to the binary log and then commits this transaction
into <literal>InnoDB</literal>. If the server crashes between
- those two operations, on restart the transaction is rolled back
- by <literal>InnoDB</literal> but still exists in the binary log.
- This problem can be solved with the
+ those two operations, the transaction is rolled back by
+ <literal>InnoDB</literal> at restart but still exists in the
+ binary log. This problem can be solved with the
<option>--innodb-safe-binlog</option> option (available starting
from MySQL 4.1.3), which adds consistency between the content of
<literal>InnoDB</literal> tables and the binary log.
</para>
<para>
- For this option to provide real safety, the MySQL server should
- also be configured to synchronize to disk, with every
- transaction, the binary log (<literal>sync_binlog=1</literal>)
- and (which is true by default) the <literal>InnoDB</literal>
- logs. The effect of this option is that, when restarting after a
- crash and rolling back transactions, the MySQL server cuts
- rolled back <literal>InnoDB</literal> transactions from the
- binary log. This ensures that the binary log reflects the exact
- state of <literal>InnoDB</literal> tables, and thus, that the
- slave remains in synchrony with the master (not receiving a
- statement which has been rolled back).
+ For this option to provide a greater degree of safety, the MySQL
+ server should also be configured to synchronize the binary log
+ and the <literal>InnoDB</literal> logs to disk at every
+ transaction. The <literal>InnoDB</literal> logs are synchronized
+ by default, and <literal>sync_binlog=1</literal> can be used to
+ synchronize the binary log. The effect of this option is that at
+ restart after a crash, after doing a rollback of transactions,
+ the MySQL server cuts rolled back <literal>InnoDB</literal>
+ transactions from the binary log. This ensures that the binary
+ log reflects the exact data of <literal>InnoDB</literal> tables,
+ and so, that the slave remains in synchrony with the master (not
+ receiving a statement which has been rolled back).
</para>
<para>
Note that <option>--innodb-safe-binlog</option> can be used even
if the MySQL server updates other storage engines than
- <literal>InnoDB</literal>. Only statements/transactions
- affecting <literal>InnoDB</literal> tables are subject to being
- removed from the binary log during <literal>InnoDB</literal>
- crash recovery. If the MySQL server discovers that the binary
- log is shorter than it should have been (that is, it lacks at
+ <literal>InnoDB</literal>. Only statements and transactions that
+ affect <literal>InnoDB</literal> tables are subject to removal
+ from the binary log at <literal>InnoDB</literal>'s crash
+ recovery. If the MySQL server discovers at crash recovery that
+ the binary log is shorter than it should have been, it lacks at
least one successfully committed <literal>InnoDB</literal>
- transaction), which should not happen if
- <literal>sync_binlog=1</literal> and the disk and filesystem
- actually synchronize when requested (some do not), the server
- prints the error message <literal>The binary log
- <<replaceable>name</replaceable>> is shorter than its
- expected size</literal>. In this case, this binary log is not
- correct, and replication should be restarted using a fresh data
- snapshot from the master.
+ transaction. This should not happen if
+ <literal>sync_binlog=1</literal> and the disk/filesystem do an
+ actual sync when they are requested to (some don't), so the
+ server prints an error message <literal>The binary log
+ <name> is shorter than its expected size.</literal>. In
+ this case, this binary log is not correct and replication should
+ be restarted from a fresh snapshot of the master's data.
</para>
- <para>
- Before MySQL 4.1.9, a write to a binary log file or binary log
- index file that failed due to a full disk or an exceeded quota
- resulted in corruption of the file. Starting from MySQL 4.1.9,
- writes to the binary log file and binary log index file are
- handled the same way as writes to <literal>MyISAM</literal>
- tables. See <xref linkend="full-disk"/>.
- </para>
-
</section>
<section id="slow-query-log">
@@ -20422,7 +20437,8 @@
If no <replaceable>file_name</replaceable> value is given, the
default is the name of the host machine with a suffix of
<literal>-slow.log</literal>. If a filename is given, but not as
- an absolute pathname, the file is written in the data directory.
+ an absolute pathname, the server writes the file in the data
+ directory.
</para>
<para>
@@ -20436,8 +20452,8 @@
time to execute and are therefore candidates for optimization.
However, examining a long slow query log can become a difficult
task. To make this easier, you can process the slow query log
- using the <command>mysqldumpslow</command> command to get a
- summary of the queries that appear in the log.
+ using the <command>mysqldumpslow</command> command to summarize
+ the queries that appear in the log.
</para>
<para>
@@ -20492,7 +20508,7 @@
</indexterm>
<para>
- The MySQL Server can create a number of different log files that
+ MySQL Server can create a number of different log files that
make it easy to see what is going on. See
<xref linkend="log-files"/>. However, you must clean up these
files regularly to ensure that the logs do not take up too much
@@ -20508,16 +20524,17 @@
<para>
On a Linux (Red Hat) installation, you can use the
<literal>mysql-log-rotate</literal> script for this. If you
- installed MySQL from an RPM distribution, the script should have
- been installed automatically. You should be careful with this
- script if you are using the binary log for replication. (You
+ installed MySQL from an RPM distribution, this script should
+ have been installed automatically. You should be careful with
+ this script if you are using the binary log for replication. You
should not remove binary logs until you are certain that their
- contents have been processed by all slaves.)
+ contents have been processed by all slaves.
</para>
<para>
On other systems, you must install a short script yourself that
- you start from <command>cron</command> to handle log files.
+ you start from <command>cron</command> (or its equivalent) for
+ handling log files.
</para>
<para>
@@ -20535,11 +20552,10 @@
<listitem>
<para>
- If standard logging (<option>--log</option>) or slow query
- logging (<option>--log-slow-queries</option>) is used,
- closes and reopens the log file
- (<filename>mysql.log</filename> and
- <filename>`hostname`-slow.log</filename> as default).
+ If general query logging (<option>--log</option>) or slow
+ query logging (<option>--log-slow-queries</option>) is used,
+ the server closes and reopens the general query log file or
+ slow query log file.
</para>
</listitem>
@@ -20554,21 +20570,37 @@
</itemizedlist>
<para>
- If you are using only an update log, you have only to rename the
- log file and then flush the logs before making a backup. For
- example, you can do something like this:
+ The server creates a new binary log file when you flush the
+ logs. However, it just closes and reopens the general and slow
+ query log files. To cause new files to be created on Unix,
+ rename the current logs before flushing them. At flush time, the
+ server will open new logs with the original names. For example,
+ if the general and slow query logs are named
+ <filename>mysql.log</filename> and
+ <filename>mysql-slow.log</filename>, you can use a series of
+ commands like this:
</para>
<programlisting>
-shell> <userinput>cd mysql-data-directory</userinput>
+shell> <userinput>cd <replaceable>mysql-data-directory</replaceable></userinput>
shell> <userinput>mv mysql.log mysql.old</userinput>
+shell> <userinput>mv mysql-slow.log mysql-slow.old</userinput>
shell> <userinput>mysqladmin flush-logs</userinput>
</programlisting>
<para>
- Then make a backup and remove <filename>mysql.old</filename>.
+ At this point, you can make a backup of
+ <filename>mysql.old</filename> and
+ <filename>mysql-slow.log</filename> and then remove them from
+ disk.
</para>
+ <para>
+ On Windows, you cannot rename log files while the server has
+ them open. You must stop the server and rename them, and then
+ restart the server to create new logs.
+ </para>
+
</section>
</section>
Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml 2006-01-28 05:53:29 UTC (rev 1076)
+++ trunk/refman-4.1/optimization.xml 2006-01-28 05:53:43 UTC (rev 1077)
@@ -5008,10 +5008,12 @@
</para>
<para>
- To ensure that the update log or binary log can be used to
- re-create the original tables, MySQL does not allow concurrent
- inserts for <literal>CREATE TABLE ... SELECT</literal>
- statements.
+ If you are using the update log or binary log, concurrent
+ inserts are converted to normal inserts for <literal>CREATE ...
+ SELECT</literal> or <literal>INSERT ... SELECT</literal>
+ statements. This is done to ensure that you can re-create an
+ exact copy of your tables by applying the log during a backup
+ operation.
</para>
<para>
Modified: trunk/refman-5.0/database-administration.xml
===================================================================
--- trunk/refman-5.0/database-administration.xml 2006-01-28 05:53:29 UTC (rev 1076)
+++ trunk/refman-5.0/database-administration.xml 2006-01-28 05:53:43 UTC (rev 1077)
@@ -12630,15 +12630,15 @@
you because there is little reason to assume that a given
username belongs to the same person everywhere on the Internet.
For example, the user <literal>joe</literal> who connects from
- <literal>office.com</literal> need not be the same person as the
- user <literal>joe</literal> who connects from
- <literal>elsewhere.com</literal>. MySQL handles this by allowing
- you to distinguish users on different hosts that happen to have
- the same name: You can grant one set of privileges for
+ <literal>office.example.com</literal> need not be the same
+ person as the user <literal>joe</literal> who connects from
+ <literal>home.example.com</literal>. MySQL handles this by
+ allowing you to distinguish users on different hosts that happen
+ to have the same name: You can grant one set of privileges for
connections by <literal>joe</literal> from
- <literal>office.com</literal>, and a different set of privileges
- for connections by <literal>joe</literal> from
- <literal>elsewhere.com</literal>.
+ <literal>office.example.com</literal>, and a different set of
+ privileges for connections by <literal>joe</literal> from
+ <literal>home.example.com</literal>.
</para>
<para>
@@ -17979,7 +17979,8 @@
<title>&title-secure-create-certs;</title>
<para>
- Here is an example for setting up SSL certificates for MySQL:
+ Here is an example of setting up SSL certificates for MySQL
+ using OpenSSL:
</para>
<programlisting>
@@ -18277,7 +18278,7 @@
<para>
The path to a directory that contains trusted SSL CA
- certificates in pem format.
+ certificates in PEM format.
</para>
</listitem>
@@ -18343,8 +18344,8 @@
</indexterm>
<para>
- Here is a note about how to connect to get a secure connection
- to remote MySQL server with SSH (by David Carlson
+ Here is a note that describes how to get a secure connection
+ to a remote MySQL server with SSH (by David Carlson
<email>dcarlson@stripped</email>):
</para>
@@ -18370,10 +18371,12 @@
<listitem>
<para>
Start your Windows SSH client. Set <literal>Host_Name =
- yourmysqlserver_URL_or_IP</literal>. Set
- <literal>userid=your_userid</literal> to log in to your
- server. This <literal>userid</literal> value may not be
- the same as the username of your MySQL account.
+ <replaceable>yourmysqlserver_URL_or_IP</replaceable></literal>.
+ Set
+ <literal>userid=<replaceable>your_userid</replaceable></literal>
+ to log in to your server. This <literal>userid</literal>
+ value might not be the same as the username of your MySQL
+ account.
</para>
</listitem>
@@ -18381,10 +18384,11 @@
<para>
Set up port forwarding. Either do a remote forward (Set
<literal>local_port: 3306</literal>, <literal>remote_host:
- yourmysqlservername_or_ip</literal>, <literal>remote_port:
- 3306</literal> ) or a local forward (Set <literal>port:
- 3306</literal>, <literal>host: localhost</literal>,
- <literal>remote port: 3306</literal>).
+ <replaceable>yourmysqlservername_or_ip</replaceable></literal>,
+ <literal>remote_port: 3306</literal> ) or a local forward
+ (Set <literal>port: 3306</literal>, <literal>host:
+ localhost</literal>, <literal>remote port:
+ 3306</literal>).
</para>
</listitem>
@@ -18414,7 +18418,7 @@
Create a new file in Windows and link to MySQL using the
ODBC driver the same way you normally do, except type in
<literal>localhost</literal> for the MySQL host server,
- not <literal>yourmysqlservername</literal>.
+ not <replaceable>yourmysqlservername</replaceable>.
</para>
</listitem>
@@ -21911,7 +21915,7 @@
<para>
On Windows, you cannot rename the log file while the server has
- it open. You must stop the server and rename the log. Then
+ it open. You must stop the server and rename it, and then
restart the server to create a new log.
</para>
@@ -21936,31 +21940,29 @@
</indexterm>
<para>
- The binary log contains all information that is available in the
- update log in a more efficient format and in a manner that is
- transaction-safe.
- </para>
-
- <para>
- The binary log contains all statements which updated data or
+ The binary log contains all statements that update data or
potentially could have updated it (for example, a
<literal>DELETE</literal> which matched no rows). Statements are
stored in the form of <quote>events</quote> that describe the
- modifications.
+ modifications. The binary log also contains information about
+ how long each statement took that updated the database.
</para>
<para>
<emphasis role="bold">Note</emphasis>: The binary log has
- replaced the old update log, which is not available in MySQL
- ¤t-series;.
+ replaced the old update log, which is no longer available as of
+ MySQL 5.0. The binary log contains all information that is
+ available in the update log in a more efficient format and in a
+ manner that is transaction-safe. If you are using transactions,
+ you must use the MySQL binary log for backups instead of the old
+ update log.
</para>
<para>
- The binary log also contains information about how long each
- statement took that updated the database. It doesn't contain
- statements that don't modify any data. If you want to log all
- statements (for example, to identify a problem query) you should
- use the general query log. See <xref linkend="query-log"/>.
+ The binary log does not contain statements that do not modify
+ any data. If you want to log all statements (for example, to
+ identify a problem query), use the general query log. See
+ <xref linkend="query-log"/>.
</para>
<para>
@@ -22027,6 +22029,12 @@
</para>
<para>
+ Writes to the binary log file and binary log index file are
+ handled in the same way as writes to <literal>MyISAM</literal>
+ tables. See <xref linkend="full-disk"/>.
+ </para>
+
+ <para>
You can delete all binary log files with the <literal>RESET
MASTER</literal> statement, or only some of them with
<literal>PURGE MASTER LOGS</literal>. See
@@ -22266,13 +22274,14 @@
<para>
If you are using replication, you should not delete old binary
log files until you are sure that no slave still needs to use
- them. One way to do this is to do <command>mysqladmin
- flush-logs</command> once a day and then remove any logs that
- are more than three days old. You can remove them manually, or
- preferably using <literal>PURGE MASTER LOGS</literal> (see
- <xref linkend="replication-master-sql"/>), which also safely
- updates the binary log index file for you (and which can take a
- date argument).
+ them. For example, if your slaves never run more than three days
+ behind, once a day you can execute <command>mysqladmin
+ flush-logs</command> on the master and then remove any logs that
+ are more than three days old. You can remove the files manually,
+ but it is preferable to use <literal>PURGE MASTER
+ LOGS</literal>, which also safely updates the binary log index
+ file for you (and which can take a date argument). See
+ <xref linkend="replication-master-sql"/>.
</para>
<para>
@@ -22283,41 +22292,40 @@
</para>
<para>
- You can examine the binary log file with the
+ You can display the contents of binary log files with the
<command>mysqlbinlog</command> utility. This can be useful when
you want to reprocess statements in the log. For example, you
can update a MySQL server from the binary log as follows:
</para>
<programlisting>
-shell> <userinput>mysqlbinlog log-file | mysql -h server_name</userinput>
+shell> <userinput>mysqlbinlog <replaceable>log_file</replaceable> | mysql -h <replaceable>server_name</replaceable></userinput>
</programlisting>
<para>
See <xref linkend="mysqlbinlog"/>, for more information on the
<command>mysqlbinlog</command> utility and how to use it.
+ <command>mysqlbinlog</command> also can be used with relay log
+ files because they are written using the same format as binary
+ log files.
</para>
<para>
- If you are using transactions, you must use the MySQL binary log
- for backups instead of the old update log.
- </para>
-
- <para>
- The binary logging is done immediately after a query completes
+ Binary logging is done immediately after a statement completes
but before any locks are released or any commit is done. This
- ensures that the log is logged in the execution order.
+ ensures that the log is logged in execution order.
</para>
<para>
Updates to non-transactional tables are stored in the binary log
- immediately after execution. For transactional tables such as
- <literal>BDB</literal> or <literal>InnoDB</literal> tables, all
- updates (<literal>UPDATE</literal>, <literal>DELETE</literal>,
- or <literal>INSERT</literal>) that change tables are cached
- until a <literal>COMMIT</literal> statement is received by the
- server. At that point, <command>mysqld</command> writes the
- whole transaction to the binary log before the
+ immediately after execution. Within an uncommitted transaction,
+ all updates (<literal>UPDATE</literal>,
+ <literal>DELETE</literal>, or <literal>INSERT</literal>) that
+ change transactional tables such as <literal>BDB</literal> or
+ <literal>InnoDB</literal> tables are cached until a
+ <literal>COMMIT</literal> statement is received by the server.
+ At that point, <command>mysqld</command> writes the entire
+ transaction to the binary log before the
<literal>COMMIT</literal> is executed. When the thread that
handles the transaction starts, it allocates a buffer of
<literal>binlog_cache_size</literal> to buffer statements. If a
@@ -22327,21 +22335,29 @@
</para>
<para>
+ Modifications to non-transactional tables cannot be rolled back.
+ If a transaction that is rolled back includes modifications to
+ non-transactional tables, the entire transaction is logged with
+ a <literal>ROLLBACK</literal> statement at the end to ensure
+ that the modifications to those tables are replicated.
+ </para>
+
+ <para>
The <literal>Binlog_cache_use</literal> status variable shows
the number of transactions that used this buffer (and possibly a
temporary file) for storing statements. The
<literal>Binlog_cache_disk_use</literal> status variable shows
- how many of those transactions actually did have to use a
- temporary file. These two variables can be used for tuning
+ how many of those transactions actually had to use a temporary
+ file. These two variables can be used for tuning
<literal>binlog_cache_size</literal> to a large enough value
that avoids the use of temporary files.
</para>
<para>
- The <literal>max_binlog_cache_size</literal> (default 4GB) can
- be used to restrict the total size used to cache a
- multiple-statement transaction. If a transaction is larger than
- this, it fails and rolls back.
+ The <literal>max_binlog_cache_size</literal> system variable
+ (default 4GB) can be used to restrict the total size used to
+ cache a multiple-statement transaction. If a transaction is
+ larger than this, it fails and rolls back.
</para>
<para>
@@ -22366,67 +22382,61 @@
server) crashes, there is a chance that the last statements of
the binary log are lost. To prevent this, you can make the
binary log be synchronized to disk after every
- <replaceable>N</replaceable>th binary log write, with the
- <literal>sync_binlog</literal> global variable (1 being the
- safest value, but also the slowest). See
- <xref linkend="server-system-variables"/>. Even with
- <literal>sync_binlog</literal> set to 1, there is still the
- chance of an inconsistency between the tables content and the
- binary log content in case of crash. For example, if using
- <literal>InnoDB</literal> tables, and the MySQL server processes
+ <replaceable>N</replaceable> writes to the binary log, with the
+ <literal>sync_binlog</literal> system variable. See
+ <xref linkend="server-system-variables"/>. 1 is the safest value
+ for <literal>sync_binlog</literal>, but also the slowest. Even
+ with <literal>sync_binlog</literal> set to 1, there is still the
+ chance of an inconsistency between the table content and binary
+ log content in case of a crash. For example, if you are using
+ <literal>InnoDB</literal> tables and the MySQL server processes
a <literal>COMMIT</literal> statement, it writes the whole
transaction to the binary log and then commits this transaction
- into <literal>InnoDB</literal>. If it crashes between those two
- operations, at restart the transaction is rolled back by
- <literal>InnoDB</literal> but still exists in the binary log.
- This problem can be solved with the
+ into <literal>InnoDB</literal>. If the server crashes between
+ those two operations, the transaction is rolled back by
+ <literal>InnoDB</literal> at restart but still exists in the
+ binary log. This problem can be solved with the
<option>--innodb-safe-binlog</option> option, which adds
consistency between the content of <literal>InnoDB</literal>
tables and the binary log. (Note:
<option>--innodb-safe-binlog</option> is unneeded as of MySQL
- 5.0.3; it was made obsolete by the introduction of XA
- transaction support.)
+ 5.0; it was made obsolete by the introduction of XA transaction
+ support.)
</para>
<para>
For this option to provide a greater degree of safety, the MySQL
- server should also be configured to synchronize to disk, at
- every transaction, the binary log
- (<literal>sync_binlog=1</literal>) and (which is true by
- default) the <literal>InnoDB</literal> logs. The effect of this
- option is that at restart after a crash, after doing a rollback
- of transactions, the MySQL server cuts rolled back
- <literal>InnoDB</literal> transactions from the binary log. This
- ensures that the binary log reflects the exact data of
- <literal>InnoDB</literal> tables, and so, that the slave remains
- in synchrony with the master (not receiving a statement which
- has been rolled back).
+ server should also be configured to synchronize the binary log
+ and the <literal>InnoDB</literal> logs to disk at every
+ transaction. The <literal>InnoDB</literal> logs are synchronized
+ by default, and <literal>sync_binlog=1</literal> can be used to
+ synchronize the binary log. The effect of this option is that at
+ restart after a crash, after doing a rollback of transactions,
+ the MySQL server cuts rolled back <literal>InnoDB</literal>
+ transactions from the binary log. This ensures that the binary
+ log reflects the exact data of <literal>InnoDB</literal> tables,
+ and so, that the slave remains in synchrony with the master (not
+ receiving a statement which has been rolled back).
</para>
<para>
Note that <option>--innodb-safe-binlog</option> can be used even
if the MySQL server updates other storage engines than
- <literal>InnoDB</literal>. Only statements/transactions
- affecting <literal>InnoDB</literal> tables are subject to being
- removed from the binary log at <literal>InnoDB</literal>'s crash
- recovery. If at crash recovery the MySQL server discovers that
- the binary log is shorter than it should have been (that is, it
- lacks at least one successfully committed
- <literal>InnoDB</literal> transaction), which should not happen
- if <literal>sync_binlog=1</literal> and the disk/filesystem do
- an actual sync when they are requested to (some don't), it
- prints an error message ("The binary log <name> is shorter
- than its expected size"). In this case, this binary log is not
- correct, replication should be restarted from a fresh master's
- data snapshot.
+ <literal>InnoDB</literal>. Only statements and transactions that
+ affect <literal>InnoDB</literal> tables are subject to removal
+ from the binary log at <literal>InnoDB</literal>'s crash
+ recovery. If the MySQL server discovers at crash recovery that
+ the binary log is shorter than it should have been, it lacks at
+ least one successfully committed <literal>InnoDB</literal>
+ transaction. This should not happen if
+ <literal>sync_binlog=1</literal> and the disk/filesystem do an
+ actual sync when they are requested to (some don't), so the
+ server prints an error message <literal>The binary log
+ <name> is shorter than its expected size.</literal>. In
+ this case, this binary log is not correct and replication should
+ be restarted from a fresh snapshot of the master's data.
</para>
- <para>
- Writes to the binary log file and binary log index file are
- handled in the same way as writes to <literal>MyISAM</literal>
- tables. See <xref linkend="full-disk"/>.
- </para>
-
</section>
<section id="slow-query-log">
@@ -22457,7 +22467,8 @@
If no <replaceable>file_name</replaceable> value is given, the
default is the name of the host machine with a suffix of
<literal>-slow.log</literal>. If a filename is given, but not as
- an absolute pathname, the file is written in the data directory.
+ an absolute pathname, the server writes the file in the data
+ directory.
</para>
<para>
@@ -22471,15 +22482,14 @@
time to execute and are therefore candidates for optimization.
However, examining a long slow query log can become a difficult
task. To make this easier, you can process the slow query log
- using the <command>mysqldumpslow</command> command to get a
- summary of the queries that appear in the log.
+ using the <command>mysqldumpslow</command> command to summarize
+ the queries that appear in the log.
</para>
<para>
- In MySQL ¤t-series;'s slow query log, slow queries that do
- not use indexes are logged as well as those that do. To prevent
- queries that do not use indexes from being logged in the slow
- query log, use the
+ In MySQL ¤t-series;, slow queries that do not use indexes
+ are logged as well as those that do. To prevent queries that do
+ not use indexes from being logged in the slow query log, use the
<option>--log-queries-not-using-indexes</option> option. See
<xref linkend="server-options"/>.
</para>
@@ -22521,7 +22531,7 @@
</indexterm>
<para>
- The MySQL Server can create a number of different log files that
+ MySQL Server can create a number of different log files that
make it easy to see what is going on. See
<xref linkend="log-files"/>. However, you must clean up these
files regularly to ensure that the logs don't take up too much
@@ -22537,16 +22547,16 @@
<para>
On a Linux (Red Hat) installation, you can use the
<literal>mysql-log-rotate</literal> script for this. If you
- installed MySQL from an RPM distribution, the script should have
- been installed automatically. You should be careful with this
- script if you are using the binary log for replication; do not
- remove binary logs until you are certain that their contents
- have been processed by all slaves.
+ installed MySQL from an RPM distribution, this script should
+ have been installed automatically. You should be careful with
+ this script if you are using the binary log for replication. You
+ should not remove binary logs until you are certain that their
+ contents have been processed by all slaves.
</para>
<para>
On other systems, you must install a short script yourself that
- you start from <command>cron</command> or the equivalent for
+ you start from <command>cron</command> (or its equivalent) for
handling log files.
</para>
@@ -22564,11 +22574,10 @@
<listitem>
<para>
- If standard logging (<option>--log</option>) or slow query
- logging (<option>--log-slow-queries</option>) is used,
- closes and reopens the log file
- (<filename>mysql.log</filename> and
- <filename>`hostname`-slow.log</filename> as default).
+ If general query logging (<option>--log</option>) or slow
+ query logging (<option>--log-slow-queries</option>) is used,
+ the server closes and reopens the general query log file or
+ slow query log file.
</para>
</listitem>
@@ -22583,21 +22592,37 @@
</itemizedlist>
<para>
- If you are using only an update log, you only have to rename the
- log file and then flush the logs before making a backup. For
- example, you can do something like this:
+ The server creates a new binary log file when you flush the
+ logs. However, it just closes and reopens the general and slow
+ query log files. To cause new files to be created on Unix,
+ rename the current logs before flushing them. At flush time, the
+ server will open new logs with the original names. For example,
+ if the general and slow query logs are named
+ <filename>mysql.log</filename> and
+ <filename>mysql-slow.log</filename>, you can use a series of
+ commands like this:
</para>
<programlisting>
-shell> <userinput>cd mysql-data-directory</userinput>
+shell> <userinput>cd <replaceable>mysql-data-directory</replaceable></userinput>
shell> <userinput>mv mysql.log mysql.old</userinput>
+shell> <userinput>mv mysql-slow.log mysql-slow.old</userinput>
shell> <userinput>mysqladmin flush-logs</userinput>
</programlisting>
<para>
- Then make a backup and remove <filename>mysql.old</filename>.
+ At this point, you can make a backup of
+ <filename>mysql.old</filename> and
+ <filename>mysql-slow.log</filename> and then remove them from
+ disk.
</para>
+ <para>
+ On Windows, you cannot rename log files while the server has
+ them open. You must stop the server and rename them, and then
+ restart the server to create new logs.
+ </para>
+
</section>
</section>
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2006-01-28 05:53:29 UTC (rev 1076)
+++ trunk/refman-5.0/optimization.xml 2006-01-28 05:53:43 UTC (rev 1077)
@@ -6463,9 +6463,12 @@
</para>
<para>
- To ensure that the binary log can be used to re-create the
- original tables, MySQL does not allow concurrent inserts for
- <literal>INSERT ... SELECT</literal> statements.
+ If you are using the binary log, concurrent inserts are
+ converted to normal inserts for <literal>CREATE ...
+ SELECT</literal> or <literal>INSERT ... SELECT</literal>
+ statements. This is done to ensure that you can re-create an
+ exact copy of your tables by applying the log during a backup
+ operation.
</para>
<para>
Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml 2006-01-28 05:53:29 UTC (rev 1076)
+++ trunk/refman-5.1/database-administration.xml 2006-01-28 05:53:43 UTC (rev 1077)
@@ -12671,15 +12671,15 @@
you because there is little reason to assume that a given
username belongs to the same person everywhere on the Internet.
For example, the user <literal>joe</literal> who connects from
- <literal>office.com</literal> need not be the same person as the
- user <literal>joe</literal> who connects from
- <literal>elsewhere.com</literal>. MySQL handles this by allowing
- you to distinguish users on different hosts that happen to have
- the same name: You can grant one set of privileges for
+ <literal>office.example.com</literal> need not be the same
+ person as the user <literal>joe</literal> who connects from
+ <literal>home.example.com</literal>. MySQL handles this by
+ allowing you to distinguish users on different hosts that happen
+ to have the same name: You can grant one set of privileges for
connections by <literal>joe</literal> from
- <literal>office.com</literal>, and a different set of privileges
- for connections by <literal>joe</literal> from
- <literal>elsewhere.com</literal>.
+ <literal>office.example.com</literal>, and a different set of
+ privileges for connections by <literal>joe</literal> from
+ <literal>home.example.com</literal>.
</para>
<para>
@@ -17988,7 +17988,8 @@
<title>&title-secure-create-certs;</title>
<para>
- Here is an example for setting up SSL certificates for MySQL:
+ Here is an example of setting up SSL certificates for MySQL
+ using OpenSSL:
</para>
<programlisting>
@@ -18286,7 +18287,7 @@
<para>
The path to a directory that contains trusted SSL CA
- certificates in pem format.
+ certificates in PEM format.
</para>
</listitem>
@@ -18352,8 +18353,8 @@
</indexterm>
<para>
- Here is a note about how to connect to get a secure connection
- to remote MySQL server with SSH (by David Carlson
+ Here is a note that describes how to get a secure connection
+ to a remote MySQL server with SSH (by David Carlson
<email>dcarlson@stripped</email>):
</para>
@@ -18379,10 +18380,12 @@
<listitem>
<para>
Start your Windows SSH client. Set <literal>Host_Name =
- yourmysqlserver_URL_or_IP</literal>. Set
- <literal>userid=your_userid</literal> to log in to your
- server. This <literal>userid</literal> value may not be
- the same as the username of your MySQL account.
+ <replaceable>yourmysqlserver_URL_or_IP</replaceable></literal>.
+ Set
+ <literal>userid=<replaceable>your_userid</replaceable></literal>
+ to log in to your server. This <literal>userid</literal>
+ value might not be the same as the username of your MySQL
+ account.
</para>
</listitem>
@@ -18390,10 +18393,11 @@
<para>
Set up port forwarding. Either do a remote forward (Set
<literal>local_port: 3306</literal>, <literal>remote_host:
- yourmysqlservername_or_ip</literal>, <literal>remote_port:
- 3306</literal> ) or a local forward (Set <literal>port:
- 3306</literal>, <literal>host: localhost</literal>,
- <literal>remote port: 3306</literal>).
+ <replaceable>yourmysqlservername_or_ip</replaceable></literal>,
+ <literal>remote_port: 3306</literal> ) or a local forward
+ (Set <literal>port: 3306</literal>, <literal>host:
+ localhost</literal>, <literal>remote port:
+ 3306</literal>).
</para>
</listitem>
@@ -18423,7 +18427,7 @@
Create a new file in Windows and link to MySQL using the
ODBC driver the same way you normally do, except type in
<literal>localhost</literal> for the MySQL host server,
- not <literal>yourmysqlservername</literal>.
+ not <replaceable>yourmysqlservername</replaceable>.
</para>
</listitem>
@@ -21920,7 +21924,7 @@
<para>
On Windows, you cannot rename the log file while the server has
- it open. You must stop the server and rename the log. Then
+ it open. You must stop the server and rename it, and then
restart the server to create a new log.
</para>
@@ -21945,31 +21949,29 @@
</indexterm>
<para>
- The binary log contains all information that is available in the
- update log in a more efficient format and in a manner that is
- transaction-safe.
- </para>
-
- <para>
- The binary log contains all statements which updated data or
+ The binary log contains all statements that update data or
potentially could have updated it (for example, a
<literal>DELETE</literal> which matched no rows). Statements are
stored in the form of <quote>events</quote> that describe the
- modifications.
+ modifications. The binary log also contains information about
+ how long each statement took that updated the database.
</para>
<para>
<emphasis role="bold">Note</emphasis>: The binary log has
- replaced the old update log, which is not available in MySQL
- ¤t-series;.
+ replaced the old update log, which is no longer available as of
+ MySQL 5.0. The binary log contains all information that is
+ available in the update log in a more efficient format and in a
+ manner that is transaction-safe. If you are using transactions,
+ you must use the MySQL binary log for backups instead of the old
+ update log.
</para>
<para>
- The binary log also contains information about how long each
- statement took that updated the database. It doesn't contain
- statements that don't modify any data. If you want to log all
- statements (for example, to identify a problem query) you should
- use the general query log. See <xref linkend="query-log"/>.
+ The binary log does not contain statements that do not modify
+ any data. If you want to log all statements (for example, to
+ identify a problem query), use the general query log. See
+ <xref linkend="query-log"/>.
</para>
<para>
@@ -22036,6 +22038,12 @@
</para>
<para>
+ Writes to the binary log file and binary log index file are
+ handled in the same way as writes to <literal>MyISAM</literal>
+ tables. See <xref linkend="full-disk"/>.
+ </para>
+
+ <para>
You can delete all binary log files with the <literal>RESET
MASTER</literal> statement, or only some of them with
<literal>PURGE MASTER LOGS</literal>. See
@@ -22275,13 +22283,14 @@
<para>
If you are using replication, you should not delete old binary
log files until you are sure that no slave still needs to use
- them. One way to do this is to do <command>mysqladmin
- flush-logs</command> once a day and then remove any logs that
- are more than three days old. You can remove them manually, or
- preferably using <literal>PURGE MASTER LOGS</literal> (see
- <xref linkend="replication-master-sql"/>), which also safely
- updates the binary log index file for you (and which can take a
- date argument).
+ them. For example, if your slaves never run more than three days
+ behind, once a day you can execute <command>mysqladmin
+ flush-logs</command> on the master and then remove any logs that
+ are more than three days old. You can remove the files manually,
+ but it is preferable to use <literal>PURGE MASTER
+ LOGS</literal>, which also safely updates the binary log index
+ file for you (and which can take a date argument). See
+ <xref linkend="replication-master-sql"/>.
</para>
<para>
@@ -22292,41 +22301,40 @@
</para>
<para>
- You can examine the binary log file with the
+ You can display the contents of binary log files with the
<command>mysqlbinlog</command> utility. This can be useful when
you want to reprocess statements in the log. For example, you
can update a MySQL server from the binary log as follows:
</para>
<programlisting>
-shell> <userinput>mysqlbinlog log-file | mysql -h server_name</userinput>
+shell> <userinput>mysqlbinlog <replaceable>log_file</replaceable> | mysql -h <replaceable>server_name</replaceable></userinput>
</programlisting>
<para>
See <xref linkend="mysqlbinlog"/>, for more information on the
<command>mysqlbinlog</command> utility and how to use it.
+ <command>mysqlbinlog</command> also can be used with relay log
+ files because they are written using the same format as binary
+ log files.
</para>
<para>
- If you are using transactions, you must use the MySQL binary log
- for backups instead of the old update log.
- </para>
-
- <para>
- The binary logging is done immediately after a query completes
+ Binary logging is done immediately after a statement completes
but before any locks are released or any commit is done. This
- ensures that the log is logged in the execution order.
+ ensures that the log is logged in execution order.
</para>
<para>
Updates to non-transactional tables are stored in the binary log
- immediately after execution. For transactional tables such as
- <literal>BDB</literal> or <literal>InnoDB</literal> tables, all
- updates (<literal>UPDATE</literal>, <literal>DELETE</literal>,
- or <literal>INSERT</literal>) that change tables are cached
- until a <literal>COMMIT</literal> statement is received by the
- server. At that point, <command>mysqld</command> writes the
- whole transaction to the binary log before the
+ immediately after execution. Within an uncommitted transaction,
+ all updates (<literal>UPDATE</literal>,
+ <literal>DELETE</literal>, or <literal>INSERT</literal>) that
+ change transactional tables such as <literal>BDB</literal> or
+ <literal>InnoDB</literal> tables are cached until a
+ <literal>COMMIT</literal> statement is received by the server.
+ At that point, <command>mysqld</command> writes the entire
+ transaction to the binary log before the
<literal>COMMIT</literal> is executed. When the thread that
handles the transaction starts, it allocates a buffer of
<literal>binlog_cache_size</literal> to buffer statements. If a
@@ -22336,21 +22344,29 @@
</para>
<para>
+ Modifications to non-transactional tables cannot be rolled back.
+ If a transaction that is rolled back includes modifications to
+ non-transactional tables, the entire transaction is logged with
+ a <literal>ROLLBACK</literal> statement at the end to ensure
+ that the modifications to those tables are replicated.
+ </para>
+
+ <para>
The <literal>Binlog_cache_use</literal> status variable shows
the number of transactions that used this buffer (and possibly a
temporary file) for storing statements. The
<literal>Binlog_cache_disk_use</literal> status variable shows
- how many of those transactions actually did have to use a
- temporary file. These two variables can be used for tuning
+ how many of those transactions actually had to use a temporary
+ file. These two variables can be used for tuning
<literal>binlog_cache_size</literal> to a large enough value
that avoids the use of temporary files.
</para>
<para>
- The <literal>max_binlog_cache_size</literal> (default 4GB) can
- be used to restrict the total size used to cache a
- multiple-statement transaction. If a transaction is larger than
- this, it fails and rolls back.
+ The <literal>max_binlog_cache_size</literal> system variable
+ (default 4GB) can be used to restrict the total size used to
+ cache a multiple-statement transaction. If a transaction is
+ larger than this, it fails and rolls back.
</para>
<para>
@@ -22375,67 +22391,61 @@
server) crashes, there is a chance that the last statements of
the binary log are lost. To prevent this, you can make the
binary log be synchronized to disk after every
- <replaceable>N</replaceable>th binary log write, with the
- <literal>sync_binlog</literal> global variable (1 being the
- safest value, but also the slowest). See
- <xref linkend="server-system-variables"/>. Even with
- <literal>sync_binlog</literal> set to 1, there is still the
- chance of an inconsistency between the tables content and the
- binary log content in case of crash. For example, if using
- <literal>InnoDB</literal> tables, and the MySQL server processes
+ <replaceable>N</replaceable> writes to the binary log, with the
+ <literal>sync_binlog</literal> system variable. See
+ <xref linkend="server-system-variables"/>. 1 is the safest value
+ for <literal>sync_binlog</literal>, but also the slowest. Even
+ with <literal>sync_binlog</literal> set to 1, there is still the
+ chance of an inconsistency between the table content and binary
+ log content in case of a crash. For example, if you are using
+ <literal>InnoDB</literal> tables and the MySQL server processes
a <literal>COMMIT</literal> statement, it writes the whole
transaction to the binary log and then commits this transaction
- into <literal>InnoDB</literal>. If it crashes between those two
- operations, at restart the transaction is rolled back by
- <literal>InnoDB</literal> but still exists in the binary log.
- This problem can be solved with the
+ into <literal>InnoDB</literal>. If the server crashes between
+ those two operations, the transaction is rolled back by
+ <literal>InnoDB</literal> at restart but still exists in the
+ binary log. This problem can be solved with the
<option>--innodb-safe-binlog</option> option, which adds
consistency between the content of <literal>InnoDB</literal>
tables and the binary log. (Note:
- <option>--innodb-safe-binlog</option> is unneeded in MySQL
- ¤t-series;; it was made obsolete by the introduction of XA
- transaction support.)
+ <option>--innodb-safe-binlog</option> is unneeded as of MySQL
+ 5.0; it was made obsolete by the introduction of XA transaction
+ support.)
</para>
<para>
For this option to provide a greater degree of safety, the MySQL
- server should also be configured to synchronize to disk, at
- every transaction, the binary log
- (<literal>sync_binlog=1</literal>) and (which is true by
- default) the <literal>InnoDB</literal> logs. The effect of this
- option is that at restart after a crash, after doing a rollback
- of transactions, the MySQL server cuts rolled back
- <literal>InnoDB</literal> transactions from the binary log. This
- ensures that the binary log reflects the exact data of
- <literal>InnoDB</literal> tables, and so, that the slave remains
- in synchrony with the master (not receiving a statement which
- has been rolled back).
+ server should also be configured to synchronize the binary log
+ and the <literal>InnoDB</literal> logs to disk at every
+ transaction. The <literal>InnoDB</literal> logs are synchronized
+ by default, and <literal>sync_binlog=1</literal> can be used to
+ synchronize the binary log. The effect of this option is that at
+ restart after a crash, after doing a rollback of transactions,
+ the MySQL server cuts rolled back <literal>InnoDB</literal>
+ transactions from the binary log. This ensures that the binary
+ log reflects the exact data of <literal>InnoDB</literal> tables,
+ and so, that the slave remains in synchrony with the master (not
+ receiving a statement which has been rolled back).
</para>
<para>
Note that <option>--innodb-safe-binlog</option> can be used even
if the MySQL server updates other storage engines than
- <literal>InnoDB</literal>. Only statements/transactions
- affecting <literal>InnoDB</literal> tables are subject to being
- removed from the binary log at <literal>InnoDB</literal>'s crash
- recovery. If at crash recovery the MySQL server discovers that
- the binary log is shorter than it should have been (that is, it
- lacks at least one successfully committed
- <literal>InnoDB</literal> transaction), which should not happen
- if <literal>sync_binlog=1</literal> and the disk/filesystem do
- an actual sync when they are requested to (some don't), it
- prints an error message ("The binary log <name> is shorter
- than its expected size"). In this case, this binary log is not
- correct, replication should be restarted from a fresh master's
- data snapshot.
+ <literal>InnoDB</literal>. Only statements and transactions that
+ affect <literal>InnoDB</literal> tables are subject to removal
+ from the binary log at <literal>InnoDB</literal>'s crash
+ recovery. If the MySQL server discovers at crash recovery that
+ the binary log is shorter than it should have been, it lacks at
+ least one successfully committed <literal>InnoDB</literal>
+ transaction. This should not happen if
+ <literal>sync_binlog=1</literal> and the disk/filesystem do an
+ actual sync when they are requested to (some don't), so the
+ server prints an error message <literal>The binary log
+ <name> is shorter than its expected size.</literal>. In
+ this case, this binary log is not correct and replication should
+ be restarted from a fresh snapshot of the master's data.
</para>
- <para>
- Writes to the binary log file and binary log index file are
- handled in the same way as writes to <literal>MyISAM</literal>
- tables. See <xref linkend="full-disk"/>.
- </para>
-
</section>
<section id="slow-query-log">
@@ -22466,7 +22476,8 @@
If no <replaceable>file_name</replaceable> value is given, the
default is the name of the host machine with a suffix of
<literal>-slow.log</literal>. If a filename is given, but not as
- an absolute pathname, the file is written in the data directory.
+ an absolute pathname, the server writes the file in the data
+ directory.
</para>
<para>
@@ -22480,15 +22491,14 @@
time to execute and are therefore candidates for optimization.
However, examining a long slow query log can become a difficult
task. To make this easier, you can process the slow query log
- using the <command>mysqldumpslow</command> command to get a
- summary of the queries that appear in the log.
+ using the <command>mysqldumpslow</command> command to summarize
+ the queries that appear in the log.
</para>
<para>
- In MySQL ¤t-series;'s slow query log, slow queries that do
- not use indexes are logged as well as those that do. To prevent
- queries that do not use indexes from being logged in the slow
- query log, use the
+ In MySQL ¤t-series;, slow queries that do not use indexes
+ are logged as well as those that do. To prevent queries that do
+ not use indexes from being logged in the slow query log, use the
<option>--log-queries-not-using-indexes</option> option. See
<xref linkend="server-options"/>.
</para>
@@ -22530,7 +22540,7 @@
</indexterm>
<para>
- The MySQL Server can create a number of different log files that
+ MySQL Server can create a number of different log files that
make it easy to see what is going on. See
<xref linkend="log-files"/>. However, you must clean up these
files regularly to ensure that the logs don't take up too much
@@ -22546,16 +22556,16 @@
<para>
On a Linux (Red Hat) installation, you can use the
<literal>mysql-log-rotate</literal> script for this. If you
- installed MySQL from an RPM distribution, the script should have
- been installed automatically. You should be careful with this
- script if you are using the binary log for replication; do not
- remove binary logs until you are certain that their contents
- have been processed by all slaves.
+ installed MySQL from an RPM distribution, this script should
+ have been installed automatically. You should be careful with
+ this script if you are using the binary log for replication. You
+ should not remove binary logs until you are certain that their
+ contents have been processed by all slaves.
</para>
<para>
On other systems, you must install a short script yourself that
- you start from <command>cron</command> or the equivalent for
+ you start from <command>cron</command> (or its equivalent) for
handling log files.
</para>
@@ -22573,11 +22583,10 @@
<listitem>
<para>
- If standard logging (<option>--log</option>) or slow query
- logging (<option>--log-slow-queries</option>) is used,
- closes and reopens the log file
- (<filename>mysql.log</filename> and
- <filename>`hostname`-slow.log</filename> as default).
+ If general query logging (<option>--log</option>) or slow
+ query logging (<option>--log-slow-queries</option>) is used,
+ the server closes and reopens the general query log file or
+ slow query log file.
</para>
</listitem>
@@ -22592,21 +22601,37 @@
</itemizedlist>
<para>
- If you are using only an update log, you only have to rename the
- log file and then flush the logs before making a backup. For
- example, you can do something like this:
+ The server creates a new binary log file when you flush the
+ logs. However, it just closes and reopens the general and slow
+ query log files. To cause new files to be created on Unix,
+ rename the current logs before flushing them. At flush time, the
+ server will open new logs with the original names. For example,
+ if the general and slow query logs are named
+ <filename>mysql.log</filename> and
+ <filename>mysql-slow.log</filename>, you can use a series of
+ commands like this:
</para>
<programlisting>
-shell> <userinput>cd mysql-data-directory</userinput>
+shell> <userinput>cd <replaceable>mysql-data-directory</replaceable></userinput>
shell> <userinput>mv mysql.log mysql.old</userinput>
+shell> <userinput>mv mysql-slow.log mysql-slow.old</userinput>
shell> <userinput>mysqladmin flush-logs</userinput>
</programlisting>
<para>
- Then make a backup and remove <filename>mysql.old</filename>.
+ At this point, you can make a backup of
+ <filename>mysql.old</filename> and
+ <filename>mysql-slow.log</filename> and then remove them from
+ disk.
</para>
+ <para>
+ On Windows, you cannot rename log files while the server has
+ them open. You must stop the server and rename them, and then
+ restart the server to create new logs.
+ </para>
+
</section>
</section>
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2006-01-28 05:53:29 UTC (rev 1076)
+++ trunk/refman-5.1/optimization.xml 2006-01-28 05:53:43 UTC (rev 1077)
@@ -6471,9 +6471,12 @@
</para>
<para>
- To ensure that the binary log can be used to re-create the
- original tables, MySQL does not allow concurrent inserts for
- <literal>INSERT ... SELECT</literal> statements.
+ If you are using the binary log, concurrent inserts are
+ converted to normal inserts for <literal>CREATE ...
+ SELECT</literal> or <literal>INSERT ... SELECT</literal>
+ statements. This is done to ensure that you can re-create an
+ exact copy of your tables by applying the log during a backup
+ operation.
</para>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1077 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 28 Jan |
