(mysql.info.gz) Binary log
Info Catalog
(mysql.info.gz) Update log
(mysql.info.gz) Log Files
(mysql.info.gz) Slow query 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