(mysql.info) binary-log

 
 5.12.3 The Binary Log
 ---------------------
 
 The binary log contains all statements that update data or potentially
 could have updated it (for example, a `DELETE' which matched no rows).
 Statements are stored in the form of `events' that describe the
 modifications. The binary log also contains information about how long
 each statement took that updated data.
 
 * The binary log has 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.
 
 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  query-log.
 
 The primary purpose of the binary log is to be able to update databases
 during a restore operation as fully as possible, because the binary log
 contains 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. See  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[=BASE_NAME] option, `mysqld' writes a
 log file containing all SQL commands that update data. If no BASE_NAME
 value is given, the default name is the name of the host machine
 followed by `-bin'. If the basename is given, but not as an absolute
 pathname, the server writes the file in the data directory. It is
 recommended that you specify a basename; see  open-bugs, for the
 reason.
 
 If you supply an extension in the log name (for example,
 -log-bin=BASE_NAME.EXTENSION), the extension is silently removed and
 ignored.
 
 `mysqld' appends a numeric extension to the binary log basename. The
 number increases each time the server creates a new log file, thus
 creating an ordered series of files. The server creates a new binary
 log file each time it starts or flushes the logs. The server also
 creates a new binary log file automatically when the current log's size
 reaches `max_binlog_size'. A binary log file may become larger than
 `max_binlog_size' if you are using large transactions because a
 transaction is written to the file in one piece, never split between
 files.
 
 To keep track of which binary log files have been used, `mysqld' also
 creates a binary log index file that contains the names of all used
 binary log files. By default this has the same basename 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'.
 
 Writes to the binary log file and binary log index file are handled in
 the same way as writes to `MyISAM' tables. See  full-disk.
 
 You can delete all binary log files with the `RESET MASTER' statement,
 or a subset of them with `PURGE MASTER LOGS'. See  reset, and
  replication-master-sql.
 
 The binary log format has some known limitations that can affect
 recovery from backups. See  replication-features.
 
 Binary logging for stored routines and triggers is done as described in
  stored-procedure-logging.
 
 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.
 
 If you are using replication, the options described here affect which
 statements are sent by a master server to its slaves.  There are also
 options for slave servers that control which statements received from
 the master to execute or ignore. For details, see 
 replication-options.
 
    * -binlog-do-db=DB_NAME
 
      Tell the server to restrict binary logging to updates for which
      the default database is DB_NAME (that is, the database selected by
      `USE'). All other databases that are not explicitly mentioned are
      ignored. If you use this option, you should ensure that you do
      updates only in the default database.
 
      There is an exception to this for `CREATE DATABASE', `ALTER
      DATABASE', and `DROP DATABASE' statements. The server uses the
      database named in the statement (not the default database) to
      decide whether it should log the statement.
 
      An example of what does not work as you might expect: If the
      server is started with `binlog-do-db=sales', and you run `USE
      prices; UPDATE sales.january SET amount=amount+1000;', this
      statement is _not_ written into the binary log.
 
      To log multiple databases, use multiple options, specifying the
      option once for each database.
 
    * -binlog-ignore-db=DB_NAME
 
      Tell the server to suppress binary logging of updates for which
      the default database is DB_NAME (that is, the database selected by
      `USE'). If you use this option, you should ensure that you do
      updates only in the default database.
 
      As with the -binlog-do-db option, there is an exception for the
      `CREATE DATABASE', `ALTER DATABASE', and `DROP DATABASE'
      statements. The server uses the database named in the statement
      (not the default database) to decide whether it should log the
      statement.
 
      An example of what does not work as you might expect: If the
      server is started with `binlog-ignore-db=sales', and you run `USE
      prices; UPDATE sales.january SET amount=amount+1000;', this
      statement _is_ written into the binary log.
 
      To ignore multiple databases, use multiple options, specifying the
      option once for each database.
 
 The server evaluates the options for logging or ignoring updates to the
 binary log according to the following rules. As described previously,
 there is an exception for the `CREATE DATABASE', `ALTER DATABASE', and
 `DROP DATABASE' statements. In those cases, the database being
 _created, altered, or dropped_ replaces the default database in the
 following rules.
 
   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, -binlog-ignore-db, or both).
      Is there a default 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 default database. Are there some -binlog-do-db rules?
 
         * Yes: Does the default 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 default 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 does not
 write to the binary log any statement for which the default 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. For
 example, if your slaves never run more than three days behind, once a
 day you can execute `mysqladmin flush-logs' 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 `PURGE MASTER LOGS', which
 also safely updates the binary log index file for you (and which can
 take a date argument). See  replication-master-sql.
 
 A client that has the `SUPER' privilege can disable binary logging of
 its own statements by using a `SET SQL_LOG_BIN=0' statement. See 
 set-option.
 
 You can display the contents of binary log files 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, for more information on the `mysqlbinlog'
 utility and how to use it.  `mysqlbinlog' also can be used with relay
 log files because they are written using the same format as binary log
 files.
 
 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 execution order.
 
 Updates to non-transactional tables are stored in the binary log
 immediately after execution. Within an uncommitted transaction, all
 updates (`UPDATE', `DELETE', or `INSERT') that change transactional
 tables such as `BDB' or `InnoDB' tables are cached until a `COMMIT'
 statement is received by the server.  At that point, `mysqld' writes
 the entire 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 statements. 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.
 
 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
 `ROLLBACK' statement at the end to ensure that the modifications to
 those tables are replicated.
 
 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 had 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' 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.
 
 If you are using the binary log, concurrent inserts are converted to
 normal inserts for `CREATE ...  SELECT' or `INSERT ... SELECT'
 statement. This is done to ensure that you can re-create an exact copy
 of your tables by applying the log during a backup operation.
 
 Note that the binary log format is different in MySQL 5.0 from previous
 versions of MySQL, due to enhancements in replication. See 
 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 N writes to the binary log, with the `sync_binlog'
 system variable. See  server-system-variables. 1 is the safest
 value for `sync_binlog', but also the slowest. Even with `sync_binlog'
 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 `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 the server crashes
 between those two operations, the transaction is rolled back by
 `InnoDB' at restart but still exists in the binary log. This problem
 can be solved with the -innodb-safe-binlog option, which adds
 consistency between the content of `InnoDB' tables and the binary log.
 (Note: -innodb-safe-binlog is unneeded as of MySQL 5.0; it was made
 obsolete by the introduction of XA transaction support.)
 
 For this option to provide a greater degree of safety, the MySQL server
 should also be configured to synchronize the binary log and the
 `InnoDB' logs to disk at every transaction. The `InnoDB' logs are
 synchronized by default, and `sync_binlog=1' 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 `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 synchrony 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 and
 transactions that affect `InnoDB' tables are subject to removal from
 the binary log at `InnoDB''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 `InnoDB'
 transaction. This should not happen if `sync_binlog=1' and the
 disk/filesystem do an actual sync when they are requested to (some
 don't), so the server prints an error message `The binary log <name> is
 shorter than its expected size.'. In this case, this binary log is not
 correct and replication should be restarted from a fresh snapshot of
 the master's data.
Info Catalog
(mysql.info) query-log
(mysql.info) log-files
(mysql.info) slow-query-log
automatically generated byinfo2html