Mysql：日志管理：二进制事务日志

mysql的二进制事务日志和其他的dbms的事务日志是类似的东西，是数据库备份、恢复、还原，高级的复制、镜像等功能的核心构件。

mysql的二进制事务日志是一系列有base_name.数字序列组成的。日志系列在日志大小超过max_binlog_size时自动产生——注意实际的二进制日志文件的大小可能超过该设置的大小，因为一个事务日志记录不能跨越文件。

mysql的二进制事务日志是独立于存储引擎的，和innodb事务存储引擎自身的事务日志不同

mysql的二进制事务日志是先于锁释放、事务commit进行记录的！记录且只记录 有效 的数据 更新！非事务表的数据更新是立即进行记录的！5.1.20后，某些系统变量的值如sql_mode、sql_auto_is_null等也进行记录，为了保持数据的一致！

mysql的二进制事务日志不是同步写入磁盘的，是缓存的；可以配置！其内存缓存不足时，使用磁盘临时文件。

设置二进制事务日志的记录的格式

命令、配置选项：【--】binlog-format={statement|row|mixed}　　

动态全局系统变量：binlog_format={statement|row|mixed}

动态会话系统变量：binlog_format={statement|row|mixed}　　--仅仅影响当前连接会话的记录格式

启用二进制事务日志：

命令、配置选项：【--】log-bin[=base_name]

截断二进制事务日志：

reset master;

purge binary logs;

记录二进制事务日志的判定规则：

第一步：是否设置了--binlog_do_db、--binlog_ignore_db 或两者？是的话进行下一步测试；　　--如果没有设置，记录所有数据库的事务

第二步：是否选择了（默认）数据库（use db_name）？是的话进行下一步测试；　　--如果没有设置，不记录事务日志，结束测试！

第三步：如果设置了--binlog-do-db，（默认）数据库是否匹配设置？是的话写事务日志，结束测试；否的话不记录事务日志，结束测试！

第三步：如果没有设置--binlog-do-db，而设置了--binlog-ignore-db，（默认）数据库是否匹配设置？是的话不写事务日志，结束测试；否的话记录事务日志，结束测试！　　--注意：--binlog-ignore-db选项在设置了--binlog-do-db时，不会起任何作用，形同虚设！

二进制事务日志的状态及调整

系统状态变量：binglog_cache_use：指示二进制日志系统缓存（内存或临时文件）中存储的事务日志数目

系统状态变量：binlog_cache_disk_use：指示二进制日志系统缓存使用的临时文件的大小

全局动态系统变量：binlog_cache_size、max_binlog_cache_size用来设置二进制日志的系统缓存，合理设置！

全局动态系统变量：sync_binlog=n：指示mysql待n次写入缓存后 才写入 磁盘。实际上，即使n=1，对于innodb来说仍有可能是数据不一致，这是需要设置--innodb-support-xa=1

附：

The Binary Log

The binary log contains all statements that update data. It also contains statements that potentially could have updated it (for example, a DELETE which matched no rows), unless row-based logging is used. 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 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 16.4, “Replication Implementation”.

  • Certain data recovery operations require use of the binary log. After a backup file 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.2.2, “Using Backups for Recovery”.

The binary log is not used for statements such as SELECT 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. See Section 5.2.3, “The General Query Log”.

The binary log should be protected because logged statements might contain passwords. See Section 5.5.6.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”.

MySQL Enterprise.  The binary log can also be used to track significant DDL events. Analyzing the binary log in this way is an integral part of the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

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 statements that update data (both DDL and DML statements). If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin. If the basename is given, but not as an absolute path name, the server writes the file in the data directory. It is recommended that you specify a basename; see Section B.1.8.2, “Additional Known Issues”, for the reason.

Note

From MySQL 5.1.18 through 5.1.22, “mysql” was used when no base_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 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.

You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. See Section 12.5.6.6, “RESET Syntax”, and Section 12.6.1.1, “PURGE BINARY LOGS Syntax”.

Writes to the binary log file and binary log index file are handled in the same way as writes to MyISAM tables. See Section B.1.4.3, “How MySQL Handles a Full Disk”.

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

Binary logging for stored routines and triggers is done as described in Section 19.6, “Binary Logging of Stored Programs”.

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 (see also Section 16.1.3.3, “Replication Slave Options and Variables”).

Evaluation of update selection options.  The server evaluates the options for logging or ignoring updates to the binary log according to 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.

Important

An exception is made in the rules just given for the CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements (see Section 16.1.3.4, “Binary Log Options and Variables”). In those cases, the database being created, altered, or dropped replaces the default database when determining whether to log or ignore updates.

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 BINARY LOGS, which also safely updates the binary log index file for you (and which can take a date argument). See Section 12.6.1.1, “PURGE BINARY LOGS Syntax”.

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 Section 5.1.4, “Session 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 example, you can update a MySQL server from the binary log as follows: 

shell> mysqlbinlog log_file | mysql -h server_name

See Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”, 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. In MySQL 5.1.22 and earlier versions of MySQL 5.1, an UPDATE statement using a stored function that modified a non-transactional table was not logged if it failed, and an INSERT ... ON DUPLICATE KEY UPDATE statement that encountered a duplicate key constraint — but which 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 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, 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 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. If you are using statement based logging then the original statement is written to the log.

Note that the binary log format is different in MySQL 5.1 from previous versions of MySQL, due to enhancements in replication. See Section 16.3.2, “Replication Compatibility Between MySQL Versions”.

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 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. 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 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).

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/file system 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.

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

  • foreign_key_checks

  • unique_checks

  • character_set_client

  • collation_connection

  • collation_database

  • collation_server

  • sql_auto_is_null

5.2.4.1. Binary Logging Formats

A number of different logging formats are used to record information in the binary log. The exact format employed depends on the version of MySQL being used. There are three logging formats:

  • Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.

  • In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.

    Support for row-based logging was added in MySQL 5.1.5.

  • A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

    Mixed logging is available beginning with MySQL 5.1.8. In MySQL 5.1.12, it become the default logging mode; in 5.1.29, the default was changed back to STATEMENT.

    Starting with MySQL 5.1.20, the logging format can also be set or limited by the storage engine being used. This helps to eliminate issues when logging, and more specifically replicating, certain statements between a master and slave which are using different storage engines.

With MySQL's classic statement-based replication, there may be issues with replicating non-deterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using the statement-based format. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning, Statement may not be safe to log in statement format.

Prior to MySQL 5.1.36, this warning read, Statement is not safe to log in statement format. (Bug#42415)

You can avoid these issues by using MySQL's row-based replication instead.

5.2.4.2. Setting The Binary Log Format

The default binary logging format depends on the version of MySQL you are using:

  • For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default.

  • For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.

Exception.  For all MySQL Cluster releases using version 6.1 or later of the NDBCLUSTER storage engine (even those releases based on MySQL 5.1.29 and later), the default binary log format is MIXED. See Section 17.9, “MySQL Cluster Replication”.

You can force the replication format by starting the MySQL server with --binlog-format=type. When set, all replication slaves connecting to the server will read the events according to this setting. The supported values for type are:

  • ROW causes replication to be row-based.

  • STATEMENT causes replication to be statement-based. This is the default for MySQL 5.1.11 and earlier, and MySQL 5.1.29 and later.

  • MIXED causes replication to use mixed format. This is the default for MySQL 5.1.12 to 5.1.28.

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable. (To change the global value, you must have need the SUPER privilege. This is also true for the SESSION value as of MySQL 5.1.29.)

To switch to statement-based format, use either of these statements: 

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 1;

To switch to row-based format, use either of these statements: 

mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 2;

To switch to mixed format, use either of these statements: 

mysql> SET GLOBAL binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 3;

An individual client can control the logging format for its own statements by setting the session value of binlog_format. For example: 

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

In addition to switching the logging format manually, a slave server may switch the format automatically. This happens when the server is running in either STATEMENT or MIXED format and encounters a row in the binary log that is written in ROW logging format. In that case, the slave switches to row-based replication temporarily for that event, and switches back to the previous format afterwards.

There are two reasons why you might want to set replication logging on a per-connection basis:

  • A thread that makes many small changes to the database might want to use row-based logging. A thread that performs updates that match many rows in the WHERE clause might want to use statement-based logging because it will be more efficient to log a few statements than many rows.

  • Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.

There are exceptions when you cannot switch the replication format at runtime:

  • From within a stored function or a trigger.

  • If the NDBCLUSTER storage engine is enabled.

  • If the session is currently in row-based replication mode and has open temporary tables.

Trying to switch the format in any of these cases results in an error.

Switching the replication format at runtime is not recommended when any temporary tables exist, because temporary tables are logged only when using statement-based replication, whereas with row-based replication they are not logged. With mixed replication, temporary tables are usually logged; exceptions happen with user-defined functions (UDFs) and with the UUID() function.

With the binlog format set to ROW, many changes are written to the binary log using the row-based format. Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

The --binlog-row-event-max-size option is available for servers that are capable of row-based replication. Rows are stored into the binary log in chunks having a size in bytes not exceeding the value of this option. The value must be a multiple of 256. The default value is 1024.

Warning

When using statement-based logging in a replication scenario, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication. For a detailed explanation of this issue, see Section B.1.8.2, “Additional Known Issues”.

5.2.4.3. Mixed Binary Logging Format

When running in MIXED mode, automatic switching from statement-based to row-based replication takes place under the following conditions:

  • When a DML statement updates an NDBCLUSTER table

  • When a function contains UUID()

  • When 2 or more tables with AUTO_INCREMENT columns are updated

  • When any INSERT DELAYED is executed

  • When the body of a view requires row-based replication, the statement creating the view also uses it — for example, this occurs when the statement creating a view uses the UUID() function

  • When a call to a UDF is involved

  • If a statement is logged by row and the client that executed the statement has any temporary tables, then logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that client are dropped

    This is true whether or not any temporary tables are actually logged

    Temporary tables cannot be logged using the row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe, and we approximate this condition by treating all statements made by that client as unsafe until the client no longer holds any temporary tables

  • Beginning with MySQL 5.1.23:

    • When FOUND_ROWS() or ROW_COUNT() is used (Bug#12092, Bug#30244)

    • When USER(), CURRENT_USER(), or CURRENT_USER is used (Bug#28086)

  • Beginning with MySQL 5.1.24, when a statement refers to one or more system variables. (Bug#31168)

  • Beginning with MySQL 5.1.34, when the LOAD_FILE() function is used. (Bug#39701)

Note

Starting with MySQL 5.1.20 a warning is generated if you try to log execute a statement in statement-logging mode that should be logged in row-logging mode. The warning is shown both in the client (in the output of SHOW WARNINGS) and through the mysqld error log. A warning is added to the SHOW WARNINGS table each time a statement is executed. However, only the first statement that generated the warning for each client session is logged to the mysqld error log to prevent flooding the error log.

Starting with MySQL 5.1.20, in addition to the decisions above, individual engines can also determine the logging format used when information in a table is updated. The following table lists the logging formats supported by each storage engine:

Storage Engine Row Logging Supported Statement Logging Supported
ARCHIVE Yes Yes
BLACKHOLE No Yes
CSV Yes Yes
EXAMPLE Yes No
FEDERATED Yes Yes
HEAP Yes Yes
InnoDB Yes Yes
MyISAM Yes Yes
MERGE Yes Yes
NDBCLUSTER Yes No

A given storage engine can support either or both logging formats; the logging capabilities of an individual engine can be further defined as follows:

  • If an engine supports row-based logging, then the engine is said to be row-logging capable.

  • If an engine supports statement-based logging, then the engine is said to be statement-logging capable.

When determining the logging mode to be used, the capabilities of all the tables affected by the event are combined. The set of affected tables is then marked according to these rules:

  • A set of tables is defined as row logging restricted if the tables are row logging capable but not statement logging capable.

  • A set of tables is defined as statement logging restricted if the tables are statement logging capable but not row logging capable.

Once the determination of the possible logging formats required by the statement is complete it is compared to the current binlog_format setting. The following table is used to decide how the information is recorded in the binary log or, if appropriate, whether an error is raised. In the table, a safe operation is defined as one that is deterministic. A number of rules decide whether the statement is deterministic or not, as shown in the following table (where RLC stands for “row-logging capable” and SLC stands for “statement-logging capable”).

Condition Action
Safe/unsafe binlog_format RLC SLC Error/Warning Logged as
Safe STATEMENT N N Error: not loggable  
Safe STATEMENT N Y   STATEMENT
Safe STATEMENT Y N Error: not loggable  
Safe STATEMENT Y Y   STATEMENT
Safe MIXED N N Error: not loggable  
Safe MIXED N Y   STATEMENT
Safe MIXED Y N   ROW
Safe MIXED Y Y   STATEMENT
Safe ROW N N Error: not loggable  
Safe ROW N Y Error: not loggable  
Safe ROW Y N   ROW
Safe ROW Y Y   ROW
Unsafe STATEMENT N N Error: not loggable  
Unsafe STATEMENT N Y Warning: unsafe STATEMENT
Unsafe STATEMENT Y N Error: not loggable  
Unsafe STATEMENT Y Y Warning: unsafe STATEMENT
Unsafe MIXED N N Error: not loggable  
Unsafe MIXED N Y Error: not loggable  
Unsafe MIXED Y N   ROW
Unsafe MIXED Y Y   ROW
Unsafe ROW N N Error: not loggable  
Unsafe ROW N Y Error: not loggable  
Unsafe ROW Y N   ROW
Unsafe ROW Y Y   ROW

When a warning is produced by the determination, a standard MySQL warning is produced (and is available using SHOW WARNINGS). The information is also written to the mysqld error log. Only one error for each error instance per client connection is logged. The log message will include the SQL statement that was attempted.

If a slave server was started with --log-warnings enabled, the slave prints messages to the error log to provide information about its status, such as the binary log and relay log coordinates where it starts its job, when it is switching to another relay log, when it reconnects after a disconnect, and so forth.

5.2.4.4. Logging Format for Changes to mysql Database Tables

The contents of the grant tables in the mysql database can be modified directly (for example, with INSERT or DELETE) or indirectly (for example, with GRANT or CREATE USER). As of MySQL 5.1.17, statements that affect mysql database tables are written to the binary log using the following rules:

  • Data manipulation statements that change data in mysql database tables directly are logged according to the setting of the binlog_format system variable. This pertains to statements such as INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, and TRUNCATE.

  • Statements that change the mysql database indirectly are logged as statements regardless of the value of binlog_format. This pertains to statements such as GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all forms).

CREATE TABLE ... SELECT is a combination of data definition and data manipulation. The CREATE TABLE part is logged using statement format and the SELECT part is logged according to the value of binlog_format.

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jinzhenshui/p/1507664.html