(mysql.info.gz) Binary log

 
 5.9.4 The Binary Log
 --------------------
 
 The binary log has replaced the old update log, which is unavailable
 starting from 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 transactionally safe.
 
 The binary log contains all statements which updated data or (starting
 from MySQL 4.1.3) could potentially have updated it (for example, a
 `DELETE' which matched no rows).
 
 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.
  Query log.
 
 The primary purpose of the binary log is to be able to update the
 database during a restore operation as fully as possible, because the
 binary log will contain all updates done after a backup was made.
 
 The binary log is also used on master replication servers as a record
 of the statements to be sent to slave servers.   Replication.
 
 Running the server with the binary log enabled makes performance about
 1% slower. However, the benefits of the binary log for restore
 operations and in allowing you to set up replication generally outweigh
 this minor performance decrement.
 
 When started with the `--log-bin[=FILE_NAME]' option, `mysqld' writes a
 log file containing all SQL commands that update data. If no FILE_NAME
 value is given, the default name is the name of the host machine
 followed by `-bin'. If file name is given, but it doesn't contain a
 path, the file is written in the data directory.  It is recommended to
 specify a filename, see  Open bugs for the reason.
 
 If you supply an extension in the log name (for example,
 `--log-bin=FILE_NAME.EXTENSION'), the extension is silently removed and
 ignored.
 
 `mysqld' appends a numeric extension to the binary log name. The number
 is incremented each time you start the server or flush the logs.  A new
 binary log also is created automatically when the current log's size
 reaches `max_binlog_size'. A binary log may become larger than
 `max_binlog_size' if you are using large transactions: A transaction is
 written to the binary log in one piece, never split between binary logs.
 
 To be able to know which different binary log files have been used,
 `mysqld' also creates a binary log index file that contains the name of
 all used binary log files. By default this has the same name as the
 binary log file, with the extension `'.index''.  You can change the
 name of the binary log index file with the
 `--log-bin-index[=FILE_NAME]' option.  You should not manually edit
 this file while `mysqld' is running; doing so would confuse `mysqld'.
 
 You can delete all binary log files with the `RESET MASTER' statement,
 or only some of them with `PURGE MASTER LOGS'.  See  `RESET'
 RESET. and  Replication Master SQL.
 
 The binary log format has some known limitations which can affect
 recovery from backups, especially in old versions. These caveats which
 also affect replication are listed at  Replication Features. One
 caveat which does not affect replication but only recovery with
 `mysqlbinlog': before MySQL 4.1, `mysqlbinlog' could not prepare output
 suitable for `mysql' if the binary log contained interlaced statements
 originating from different clients that used temporary tables of the
 same name. This is fixed in MySQL 4.1. However, the problem still
 existed for `LOAD DATA INFILE' statements until it was fixed in MySQL
 4.1.8.
 
 You can use the following options to `mysqld' to affect what is logged
 to the binary log. See also the discussion that follows this option
 list.
 
 `--binlog-do-db=DB_NAME'
      Tells the master that it should log updates to the binary log if
      the current database (that is, the one selected by `USE') is
      DB_NAME. All other databases that are not explicitly mentioned are
      ignored.  If you use this, you should ensure that you only do
      updates in the current database.
 
      Observe that there is an exception to the `CREATE/ALTER/DROP
      DATABASE' statements, which use the database manipulated to decide
      if it should log the statement rather than the current database.
 
      An example of what does not work as you might expect: If the
      server is started with `binlog-do-db=sales', and you do `USE
      prices; UPDATE sales.january SET amount=amount+1000;', this
      statement will not be written into the binary log.
 
 `--binlog-ignore-db=DB_NAME'
      Tells the master that updates where the current database (that is,
      the one selected by `USE') is DB_NAME should not be stored in the
      binary log.  If you use this, you should ensure that you only do
      updates in the current database.
 
      An example of what does not work as you might expect: If the
      server is started with `binlog-ignore-db=sales', and you do `USE
      prices; UPDATE sales.january SET amount=amount+1000;', this
      statement will be written into the binary log.
 
      Similar to the case for `--binlog-do-db', there is an exception to
      the `CREATE/ALTER/DROP DATABASE' statements, which use the
      database manipulated to decide if it should log the statement
      rather than the current database.
 
 
 To log or ignore multiple databases, specify the appropriate option
 multiple times, once for each database.
 
 The rules for logging or ignoring updates to the binary log are
 evaluated according to the following rules. Observe that there is an
 exception for `CREATE/ALTER/DROP DATABASE' statements. In those cases,
 the database being _created/altered/dropped_ replace the current
 database in the rules below.
 
   1. Are there `binlog-do-db' or `binlog-ignore-db' rules?
         * No: Write the statement to the binary log and exit.
 
         * Yes: Go to the next step.
 
   2. There are some rules (`binlog-do-db' or `binlog-ignore-db' or
      both). Is there a current database (has any database been selected
      by `USE'?)?
         * No: Do _not_ write the statement, and exit.
 
         * Yes: Go to the next step.
 
   3. There is a current database. Are there some `binlog-do-db' rules?
         * Yes: Does the current database match any of the `binlog-do-db'
           rules?
              * Yes: Write the statement and exit.
 
              * No: Do _not_ write the statement, and exit.
 
         * No: Go to the next step.
 
   4. There are some `binlog-ignore-db' rules.  Does the current
      database match any of the `binlog-ignore-db' rules?
         * Yes: Do not write the statement, and exit.
 
         * No: Write the query and exit.
 
 For example, a slave running with only `binlog-do-db=sales' will not
 write to the binary log any statement whose current database is
 different from `sales' (in other words, `binlog-do-db' can sometimes
 mean "ignore other databases").
 
 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 `mysqladmin flush-logs' once a day and then
 remove any logs that are more than three days old. You can remove them
 manually, or preferably using `PURGE MASTER LOGS' ( Replication
 Master SQL), which will also safely update the binary log index file
 for you  (and which can take a date argument since MySQL 4.1)
 
 A client with the `SUPER' privilege can disable binary logging of its
 own statements by using a `SET SQL_LOG_BIN=0' statement.  `SET'
 SET OPTION.
 
 You can examine the binary log file with the `mysqlbinlog' 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:
 
      shell> mysqlbinlog log-file | mysql -h server_name
 
 See  `mysqlbinlog' mysqlbinlog. for more information on the
 `mysqlbinlog' utility and how to use it.
 
 If you are using transactions, you must use the MySQL binary log for
 backups instead of the old update log.
 
 The binary logging is done immediately after a query completes but
 before any locks are released or any commit is done. This ensures that
 the log will be logged in the execution order.
 
 Updates to non-transactional tables are stored in the binary log
 immediately after execution.  For transactional tables such as `BDB' or
 `InnoDB' tables, all updates (`UPDATE', `DELETE', or `INSERT') that
 change tables are cached until a `COMMIT' statement is received by the
 server. At that point, `mysqld' writes the whole transaction to the
 binary log before the `COMMIT' is executed.  When the thread that
 handles the transaction starts, it allocates a buffer of
 `binlog_cache_size' to buffer queries.  If a statement is bigger than
 this, the thread opens a temporary file to store the transaction.  The
 temporary file is deleted when the thread ends.
 
 The `Binlog_cache_use' status variable shows the number of transactions
 that used this buffer (and possibly a temporary file) for storing
 statements.  The `Binlog_cache_disk_use' status variable shows how many
 of those transactions actually did have to use a temporary file. These
 two variables can be used for tuning `binlog_cache_size' to a large
 enough value that avoids the use of temporary files.
 
 The `max_binlog_cache_size' (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 will fail and roll back.
 
 If you are using the update log or binary log, concurrent inserts will
 be converted to normal inserts when using `CREATE ... SELECT' or
 `INSERT ... SELECT'.  This is to ensure that you can re-create an exact
 copy of your tables by applying the log on a backup.
 
 The binary log format is different in versions 3.23, 4.0, and 5.0.0.
 Those format changes were required to implement enhancements to
 replication.  MySQL 4.1 has the same binary log format as 4.0.  
 Replication Compatibility.
 
 By default, the binary log is not synchronized to disk at each 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 Nth binary log write, with the `sync_binlog' global variable
 (1 being the safest value, but also the slowest).  Server system
 variables.  Even with this 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 `InnoDB' tables, and the MySQL
 server processes a `COMMIT' statement, it writes the whole transaction
 to the binary log and then commits this transaction into `InnoDB'. If
 it crashes between those two operations, at restart the transaction
 will be rolled back by InnoDB but still exist in the binary log. This
 problem can be solved with the `--innodb-safe-binlog' option (available
 starting from MySQL 4.1.3), which adds consistency between the content
 of `InnoDB' tables and the binary log. For this option to really bring
 safety to you, the MySQL server should also be configured to
 synchronize to disk, at every transaction, the binary log
 (`sync_binlog=1') and (which is true by default) the `InnoDB' logs.
 The effect of this option is that at restart after a crash, after doing
 a rollback of transactions, the MySQL server will cut rolled back
 `InnoDB' transactions from the binary log. This ensures that the binary
 log reflects the exact data of `InnoDB' tables, and so, that the slave
 remains in sync with the master (not receiving a statement which has
 been rolled back).  Note that `--innodb-safe-binlog' can be used even
 if the MySQL server updates other storage engines than InnoDB. Only
 statements/transactions affecting `InnoDB' tables are subject to being
 removed from the binary log at `InnoDB''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 `InnoDB' transaction), which should not happen if
 `sync_binlog=1' and the disk/filesystem do an actual sync when they are
 requested to (some don't), it will print 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.
 
 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 `MyISAM' tables.   Full disk.
Info Catalog
(mysql.info.gz) Update log
(mysql.info.gz) Log Files
(mysql.info.gz) Slow query log
automatically generated byinfo2html