MySQL Binary Log

MySQL Binary Log

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, aDELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

• For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 15.2, “Replication Implementation”.

• Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 6.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

Running a server with binary logging enabled makes performance slightly slower. However, the benefits of the binary log in enabling you to set up replication and for restore operations generally outweigh this minor performance decrement.

For information about server options and variables affecting the operation of binary logging, seeSection 15.1.3.4, “Binary Log Options and Variables”.

The binary log is not used for statements such asSELECT or SHOW that do not modify data. If you want to log all statements (for example, to identify a problem query), use the general query log. SeeSection 5.2.3, “The General Query Log”.

//这里是最重要的, 说明了Binary log 和General log的不同，既然他是用作数据恢复和同步，那么最根本的就是记录写操作，而不需要读操作，所以所有的select都不记录在内，换句话说，也就是mysql 专门为写操作‘定制’了binary log， 所以它的格式可以定制成'binary'的， 才会有

binlog-format=STATEMENT/ ROW/MIXED. 的区别，为了方便对该log进行读写操作，特别的进行优化，甚至还有index，大家可以看到在同目录下有个mysql-bin.index. by one year later

The binary log should be protected because logged statements might contain passwords. SeeSection 5.3.2.1, “Administrator Guidelines for Password Security”.

The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported, row-based logging, statement-based logging and mixed-base logging. The binary logging format used depends on the MySQL version. For more information on logging formats, see Section 5.2.4.1, “Binary Logging Formats”.

For information about the format of the binary log itself, see http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log.

To enable the binary log, start the server with the --log-bin[=base_name] option. If nobase_name value is given, the default name is the value of thepid-file option (which by default is the name of host machine) followed by-bin. If the basename is given, the server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory. It is recommended that you specify a basename; seeSection C.5.8, “Known Issues in MySQL”, for the reason.

Note

From MySQL 5.1.18 through 5.1.22, “mysql” was used when nobase_name was specified. Also in these versions, a path given as part of the--log-bin options was treated as absolute rather than relative. The previous behaviors were restored in MySQL 5.1.23. (See Bug #28603 and Bug #28597.)

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 to generate binary log file names. The number increases each time the server creates a new log file, thus creating an ordered series of files. The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size. A binary log file may become larger thanmax_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 whilemysqld is running; doing so would confusemysqld.

The term “binary log file” generally denotes an individual numbered file containing database events. The term “binary log” collectively denotes the set of numbered binary log files plus the index file.

The server evaluates the --binlog-do-db and --binlog-ignore-db options in the same way as it does the--replicate-do-db and--replicate-ignore-db options. For information about how this is done, seeSection 15.2.3.1, “Evaluation of Database-Level Replication and Binary Logging Options”.

If you are replicating from a MySQL Cluster to a standalone MySQL Server, you should be aware that the theNDB storage engine uses default values for some binary logging options (including options specific toNDB such as--ndb-log-update-as-write) that differ from those used by other storage engines. If not corrected for, these differences can lead to divergence of the master's and slave's binary logs. For more information, seeReplication from NDB to other storage engines. In particular, if you are using a nontransactional storage engine such asMyISAM on the slave, see Replication from NDB to a nontransactional storage engine.

A replication slave server by default does not write to its own binary log any data modifications that are received from the replication master. To log these modifications, start the slave with the--log-slave-updates option in addition to the --log-bin option (see Section 15.1.3.3, “Replication Slave Options and Variables”). This is done when a slave is also to act as a master to other slaves in chained replication.

You can delete all binary log files with the RESET MASTER statement, or a subset of them withPURGE BINARY LOGS. SeeSection 12.7.6.6, “RESET Syntax”, and Section 12.4.1.1, “PURGE BINARY LOGS Syntax”.

If you are using replication, you should not delete old binary log files on the master 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 executemysqladmin 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 usePURGE BINARY LOGS, which also safely updates the binary log index file for you (and which can take a date argument). SeeSection 12.4.1.1, “PURGE BINARY LOGS Syntax”.

A client that has the SUPER privilege can disable binary logging of its own statements by using aSET sql_log_bin=0 statement. SeeSection 5.1.3, “Server System Variables”.

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 a recovery operation. For example, you can update a MySQL server from the binary log as follows:

shell> mysqlbinlog log_file | mysql -h server_name

mysqlbinlog also can be used to display replication slave relay log file contents because they are written using the same format as binary log files. For more information on themysqlbinlog utility and how to use it, seeSection 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”. For more information about the binary log and recovery operations, seeSection 6.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

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 nontransactional tables are stored in the binary log immediately after execution. In MySQL 5.1.22 and earlier versions of MySQL 5.1, anUPDATE statement using a stored function that modified a nontransactional table was not logged if it failed, and anINSERT ... ON DUPLICATE KEY UPDATE statement that encountered a duplicate key constraint—but did not actually change any data—was not logged. Beginning with MySQL 5.1.23, both of these statements are written to the binary log. (Bug #23333)

Within an uncommitted transaction, all updates (UPDATE,DELETE, or INSERT) that change transactional tables such asInnoDB tables are cached until aCOMMIT statement is received by the server. At that point,mysqld writes the entire transaction to the binary log before theCOMMIT is executed.

Modifications to nontransactional tables cannot be rolled back. If a transaction that is rolled back includes modifications to nontransactional tables, the entire transaction is logged with aROLLBACK statement at the end to ensure that the modifications to those tables are replicated.

When a 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.

The Binlog_cache_use status variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. TheBinlog_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 tuningbinlog_cache_size to a large enough value that avoids the use of temporary files.

The max_binlog_cache_size system variable (default 4GB, which is also the maximum) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this many bytes, it fails and rolls back. The minimum value is 4096.

If you are using the binary log and row based logging, concurrent inserts are converted to normal inserts forCREATE ... SELECT orINSERT ... SELECT 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. If you are using statement-based logging, the original statement is written to the log.

The binary log format has some known limitations that can affect recovery from backups. SeeSection 15.4.1, “Replication Features and Issues”.

Binary logging for stored programs is done as described in Section 18.7, “Binary Logging of Stored Programs”.

Note that the binary log format differs in MySQL 5.1 from previous versions of MySQL, due to enhancements in replication. SeeSection 15.4.2, “Replication Compatibility Between MySQL Versions”.

Writes to the binary log file and binary log index file are handled in the same way as writes toMyISAM tables. SeeSection C.5.4.3, “How MySQL Handles a Full Disk”.

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 Section 5.1.3, “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 usingInnoDB tables and the MySQL server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction intoInnoDB. If the server crashes between those two operations, the transaction is rolled back byInnoDB at restart but still exists in the binary log. To resolve this, you should set--innodb_support_xa to 1. Although this option is related to the support of XA transactions in InnoDB, it also ensures that the binary log and InnoDB data files are synchronized.

For this option to provide a greater degree of safety, the MySQL server should also be configured to synchronize the binary log and theInnoDB logs to disk at every transaction. TheInnoDB 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 backInnoDB transactions from the binary log. This ensures that the binary log reflects the exact data ofInnoDB tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).

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 committedInnoDB transaction. This should not happen ifsync_binlog=1 and the disk/file system do an actual sync when they are requested to (some do not), so the server prints an error messageThe binary logfile_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.

For MySQL 5.1.20 and later (and MySQL 5.0.46 and later for backward compatibility), the session values of the following system variables are written to the binary log and honored by the replication slave when parsing the binary log:

• sql_mode (except that theNO_DIR_IN_CREATE mode is not replicated; see Section 15.4.1.34, “Replication and Variables”)

• foreign_key_checks

• unique_checks

• character_set_client

• collation_connection

• collation_database

• collation_server

• sql_auto_is_null

Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices

Previous / Next / Up / Table of Contents

User Comments

Posted by Ronald Bradford on March 2 2011 5:57am

[Delete] [Edit]

The Binary log can provide valuable information about the frequency of per table DML statements.

This simple one line Linux command can provide valuable output:

$ mysqlbinlog /path/to/mysql-bin.000999 | \
grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | \
cut -c1-100 | tr '[A-Z]' '[a-z]' | \
sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | \
sort | uniq -c | sort -nr

33389 update e_acc
17680 insert into r_b
17680 insert into e_rec
14332 insert into rcv_c
13543 update e_rec
10805 update loc
3339 insert into r_att
2781 insert into o_att
...

 MYSQl BIN-LOG 日志

2009-07-10 09:39:33

标签： mysql 数据库

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。 http://qhd2004.blog.51cto.com/629417/176013

今天没事，跑到mysql的目录下转了转，发现了好多mysql-bin.00000X的日志文件。由于偶的这个是自己测试用的，就想看看这些日志文件有什么好玩的。

 

1.查找当前有哪些二进制日志文件：

   mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       125 |
| mysql-bin.000002 |       125 |
| mysql-bin.000003 |       125 |
| mysql-bin.000004 |       125 |
| mysql-bin.000005 |      2332 |
| mysql-bin.000006 |       125 |
| mysql-bin.000007 |       539 |
| mysql-bin.000008 |      1026 |
| mysql-bin.000009 |       732 |
| mysql-bin.000010 |       293 |
| mysql-bin.000011 |       291 |
| mysql-bin.000012 |       889 |
| mysql-bin.000013 |      2710 |
| mysql-bin.000014 |       125 |
| mysql-bin.000015 |       125 |
| mysql-bin.000016 |       125 |
| mysql-bin.000017 |       125 |
| mysql-bin.000018 |      1056 |
| mysql-bin.000019 |      4255 |
| mysql-bin.000020 |       344 |
| mysql-bin.000021 |       125 |
| mysql-bin.000022 |       125 |
| mysql-bin.000023 |       125 |
| mysql-bin.000024 |       106 |
+------------------+-----------+
24 rows in set (0.17 sec)

 

还有不少呢。

2.看看日志的信息

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+--------------
-----------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info
                                   |
+------------------+-----+-------------+-----------+-------------+--------------
-----------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         106 | Server ver: 5
.1.33-community-log, Binlog ver: 4 |
| mysql-bin.000001 | 106 | Stop        |         1 |         125 |
                                   |
+------------------+-----+-------------+-----------+-------------+--------------
-----------------------------------+
2 rows in set (0.00 sec)

//by One Year Later, 默认情况下只查看 mysql-bin.000001 的， 

show binlog events IN 'mysql-bin.000002'; 就能查看其它的，以此类推

SHOW BINLOG EVENTS Syntax

SHOW BINLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify'log_name', the first binary log is displayed.

The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.7, “SELECT Syntax”.

Note: Issuing a SHOW BINLOG EVENTS with noLIMIT clause could start a very time- and resource-consuming process because the server returns to the client the complete contents of the binary log (which includes all statements executed by the server that modify data). As an alternative to SHOW BINLOG EVENTS, use themysqlbinlog utility to save the binary log to a text file for later examination and analysis. SeeSection 8.8, “mysqlbinlog — Utility for Processing Binary Log Files”. 

 mysqlbinlog 也是个不错的工具 

官网列出了一个应用，注意这些bin文件大部分是root才能读，所以运行前得sudo一下

mysqlbinlog -hexdump binlog.001  保持原始数据的dump，再debug 主从同步的时候比较重要

mysqlbinlog binlog.[0-9]*  还这可以这样，爽啊

If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe:
显示歘来的是 sql语句，所以可以让mysql直接执行

shell> mysqlbinlog binlog.000001 | mysql # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql # DANGER!!

3.删除日志（mysql下的日志是怎么个玩法和oracle的类似不，这个偶还真不知道）

日志能不能删除？偶这个是测试用的删除应该没有问题

mysql> purge binary logs to 'mysql-bin.000020';
Query OK, 0 rows affected (0.05 sec)

把第20之前的删除掉。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000020 |       344 |
| mysql-bin.000021 |       125 |
| mysql-bin.000022 |       125 |
| mysql-bin.000023 |       125 |
| mysql-bin.000024 |       106 |
+------------------+-----------+
5 rows in set (0.00 sec)

删除mysql的bin-log文件
cd /var/log/mysql
lognum=`ls -lht |awk '{print $9}'|grep -v index| sort -nr | head -n 1`
/usr/local/mysql/bin/mysql -uroot -p'passwd' -e "purge binary logs to '$lognum' "
上面的脚本来自linuxtone的{北京}silience(17991391)

本文出自 “刚刚出壳的小鸟” 博客，请务必保留此出处http://qhd2004.blog.51cto.com/629417/176013