Skip to content

Latest commit

 

History

History
72 lines (56 loc) · 6.21 KB

binlog_row_image.md

File metadata and controls

72 lines (56 loc) · 6.21 KB
Raw
  • binlog_row_image| Property | Value | | --- | --- | | Command-Line Format | --binlog-row-image=image_type | | System Variable | binlog_row_image`` | | Scope | Global, Session | | Dynamic | Yes | | Type | Enumeration | | Default Value | full | | Valid Values | `full` (Log all columns) `minimal` (Log only changed columns, and columns needed to identify rows) `noblob` (Log all columns, except for unneeded BLOB and TEXT columns) |

For MySQL row-based replication, this variable determines how row images are written to the binary log. In MySQL row-based replication, each row change event contains two images, a “before” image whose columns are matched against when searching for the row to be updated, and an “after” image containing the changes. Normally, MySQL logs full rows (that is, all columns) for both the before and after images. However, it is not strictly necessary to include every column in both images, and we can often save disk, memory, and network usage by logging only those columns which are actually required. Note When deleting a row, only the before image is logged, since there are no changed values to propagate following the deletion. When inserting a row, only the after image is logged, since there is no existing row to be matched. Only when updating a row are both the before and after images required, and both written to the binary log. For the before image, it is necessary only that the minimum set of columns required to uniquely identify rows is logged. If the table containing the row has a primary key, then only the primary key column or columns are written to the binary log. Otherwise, if the table has a unique key all of whose columns are NOT NULL, then only the columns in the unique key need be logged. (If the table has neither a primary key nor a unique key without any NULL columns, then all columns must be used in the before image, and logged.) In the after image, it is necessary to log only the columns which have actually changed. You can cause the server to log full or minimal rows using the binlog_row_image system variable. This variable actually takes one of three possible values, as shown in the following list:

  • full: Log all columns in both the before image and the after image.
  • minimal: Log only those columns in the before image that are required to identify the row to be changed; log only those columns in the after image where a value was specified by the SQL statement, or generated by auto-increment.
  • noblob: Log all columns (same as full), except for BLOB and TEXT columns that are not required to identify rows, or that have not changed.

Note This variable is not supported by NDB Cluster; setting it has no effect on the logging of NDBtables. The default value is full. When using minimal or noblob, deletes and updates are guaranteed to work correctly for a given table if and only if the following conditions are true for both the source and destination tables:

  • All columns must be present and in the same order; each column must use the same data type as its counterpart in the other table.
  • The tables must have identical primary key definitions.

(In other words, the tables must be identical with the possible exception of indexes that are not part of the tables' primary keys.) If these conditions are not met, it is possible that the primary key column values in the destination table may prove insufficient to provide a unique match for a delete or update. In this event, no warning or error is issued; the master and slave silently diverge, thus breaking consistency. Setting this variable has no effect when the binary logging format is STATEMENT. When binlog_format isMIXED, the setting for binlog_row_image is applied to changes that are logged using row-based format, but this setting no effect on changes logged as statements. Setting binlog_row_image on either the global or session level does not cause an implicit commit; this means that this variable can be changed while a transaction is in progress without affecting the transaction.

The sets of columns recorded in the BI and AI are determined by the value of binlog_row_image. To specify the sets of columns, we define the PKE (for Primary Key Equivalent), as follows:

  • If a PK exists, the PKE is equal to the PK.
  • Otherwise, if there exists a UK where all columns have the NOT NULL attribute, then that is the PKE (if there are more than one such UKs, then one is chosen arbitrarily).
  • Otherwise, the PKE is equal to the set of all columns.

The set of columns included in the BI and AI are defined as in the following tables:

  • write event | binlog_row_image | Before image | After image | | --- | --- | --- | | minimal | - | All columns where a value was specified, and the autoincrement column if there is one | | noblob | - | All columns where a value was specified, and the autoincrement column if there is one, and all non-blob columns | | full | - | All columns |

  • update event | binlog_row_image | Before image | After image | | --- | --- | --- | | minimal | PKE | All columns where a value was specified | | noblob | PKE + all non-blob columns | All columns where a value was specified, and all non-blob columns | | full | All columns | All columns |

  • delete event | binlog_row_image | Before image | After image | | --- | --- | --- | | minimal | PKE | - | | noblob | PKE + all non-blob columns | - | | full | All columns | - |

Reference:

  1. https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_row_image
  2. https://dev.mysql.com/doc/internals/en/binlog-row-image.html