Binary Log Options and Variables

You can use the mysqld options and system variables that are described in this section to affect the operation of the binary log as well as to control which statements are written to the binary log. For additional information about the binary log, see , "The Binary Log". For additional information about using MariaDB server options and system variables, see , "Server Command Options", and , "Server System Variables".

Startup options used with binary logging. The following list describes startup options for enabling and configuring the binary log. System variables used with binary logging are discussed later in this section.

• --binlog-row-event-max-size=N

  Command-Line Format	--binlog-row-event-max-size=#
  Option-File Format	binlog-row-event-max-size
  	Permitted Values
  	Platform Bit Size	32
  	Type	numeric
  	Default	1024
  	Range	4294967295
  	Permitted Values
  	Platform Bit Size	64
  	Type	numeric
  	Default	1024
  	Range	18446744073709547520

  Specify the maximum size of a row-based binary log event, in bytes. Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256. The default is 1024. See , "Replication Formats".

• --log-bin[=base_name]

  Command-Line Format	--log-bin
  Option-File Format	log-bin
  Variable Name	log_bin
  Variable Scope	Global
  Dynamic Variable	No
  	Permitted Values
  	Type	file name
  	Default	OFF

  Enable binary logging. The server logs all statements that change data to the binary log, which is used for backup and replication. See , "The Binary Log".

  The option value, if given, is the basename for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the basename. It is recommended that you specify a basename (see "Known Issues in MySQL", for the reason). Otherwise, MariaDB uses host_name-bin as the basename.

  In MariaDB 5.6.5 and later, when the server reads an entry from the index file, it checks whether the entry contains a relative path, and if it does, the relative part of the path in replaced with the absolute path set using the --log-bin option. An absolute path remains unchanged; in such a case, the index must be edited manually to enable the new path or paths to be used. Previous to MariaDB 5.6.5, manual intervention was required whenever relocating the binary log or relay log files. (Bug #11745230, Bug #12133)

  Setting this option causes the log_bin system variable to be set to ON (or 1), and not to the basename. Beginning with MariaDB 5.6.2, the binary log filename (with path) is available as the log_bin_basename system variable.

• --log-bin-index[=file_name]

  Command-Line Format	--log-bin-index=name
  Option-File Format	log-bin-index
  	Permitted Values
  	Type	file name
  	Default	OFF

  The index file for binary log file names. See , "The Binary Log". If you omit the file name, and if you did not specify one with --log-bin, MariaDB uses host_name-bin.index as the file name.

• --log-bin-trust-function-creators[={0|1}]

  Command-Line Format	--log-bin-trust-function-creators
  Option-File Format	log-bin-trust-function-creators
  Option Sets Variable	Yes, log_bin_trust_function_creators
  Variable Name	log_bin_trust_function_creators
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Type	boolean
  	Default	FALSE

  This option sets the corresponding log_bin_trust_function_creators system variable. If no argument is given, the option sets the variable to 1. log_bin_trust_function_creators affects how MariaDB enforces restrictions on stored function and trigger creation. See , "Binary Logging of Stored Programs".

• --log-short-format

  Command-Line Format	--log-short-format
  Option-File Format	log-short-format
  	Permitted Values
  	Type	boolean
  	Default	FALSE

  Log less information to the binary log and slow query log, if they have been activated.

Statement selection options. The options in the following list affect which statements are written to the binary log, and thus sent by a replication master server to its slaves. There are also options for slave servers that control which statements received from the master should be executed or ignored. For details, see , "Replication Slave Options and Variables".

• --binlog-do-db=db_name

  Command-Line Format	--binlog-do-db=name
  Option-File Format	binlog-do-db
  	Permitted Values
  	Type	string

  This option affects binary logging in a manner similar to the way that --replicate-do-db affects replication.

  The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of --replicate-do-db depend on whether statement-based or row-based replication is in use. You should keep in mind that the format used to log a given statement may not necessarily be the same as that indicated by the value of binlog_format. For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for --binlog-do-db always apply in determining whether or not the statement is logged.

  Statement-based logging. Only those statements are written to the binary log where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not cause cross-database statements such as UPDATE some_db.some_table SET foo='bar' to be logged while a different database (or no database) is selected.Warning

  To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, the list will be treated as the name of a single database if you supply a comma-separated list.

  An example of what does not work as you might expect when using statement-based logging: If the server is started with --binlog-do-db=sales and you issue the following statements, the UPDATE statement is not logged:

  USE prices;
  UPDATE sales.january SET amount=amount+1000;
  

  The main reason for this "just check the default database" behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

  Another case which may not be self-evident occurs when a given database is replicated even though it was not specified when setting the option. If the server is started with --binlog-do-db=sales, the following UPDATE statement is logged even though prices was not included when setting --binlog-do-db:

  USE sales;
  UPDATE prices.discounts SET percentage = percentage + 10;
  

  Because sales is the default database when the UPDATE statement is issued, the UPDATE is logged.

  Row-based logging. Logging is restricted to database db_name. Only changes to tables belonging to db_name are logged; the default database has no effect on this. Suppose that the server is started with --binlog-do-db=sales and row-based logging is in effect, and then the following statements are executed:

  USE prices;
  UPDATE sales.february SET amount=amount+100;
  

  The changes to the february table in the sales database are logged in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, when using the row-based logging format and --binlog-do-db=sales, changes made by the following UPDATE are not logged:

  USE prices;
  UPDATE prices.march SET amount=amount-25;
  

  Even if the USE prices statement were changed to USE sales, the UPDATE statement's effects would still not be written to the binary log.

  Another important difference in --binlog-do-db handling for statement-based logging as opposed to the row-based logging occurs with regard to statements that refer to multiple databases. Suppose that the server is started with --binlog-do-db=db1, and the following statements are executed:

  USE db1;
  UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
  

  If you are using statement-based logging, the updates to both tables are written to the binary log. However, when using the row-based format, only the changes to table1 are logged; table2 is in a different database, so it is not changed by the UPDATE. Now suppose that, instead of the USE db1 statement, a USE db4 statement had been used:

  USE db4;
  UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
  

  In this case, the UPDATE statement is not written to the binary log when using statement-based logging. However, when using row-based logging, the change to table1 is logged, but not that to table2-in other words, only changes to tables in the database named by --binlog-do-db are logged, and the choice of default database has no effect on this behavior.

• --binlog-ignore-db=db_name

  Command-Line Format	--binlog-ignore-db=name
  Option-File Format	binlog-ignore-db
  	Permitted Values
  	Type	string

  This option affects binary logging in a manner similar to the way that --replicate-ignore-db affects replication.

  The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of --replicate-ignore-db depend on whether statement-based or row-based replication is in use. You should keep in mind that the format used to log a given statement may not necessarily be the same as that indicated by the value of binlog-format. For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect, so the following statement-based rules for --binlog-ignore-db always apply in determining whether or not the statement is logged.

  Statement-based logging. Tells the server to not log any statement where the default database (that is, the one selected by USE) is db_name.

  Row-based format. Tells the server not to log updates to any tables in the database db_name. The current database has no effect.

  When using statement-based logging, the following example does not work as you might expect. Suppose that the server is started with --binlog-ignore-db=sales and you issue the following statements:

  USE prices;
  UPDATE sales.january SET amount=amount+1000;
  

  The UPDATE statement is logged in such a case because --binlog-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based logging, the UPDATE statement's effects are not written to the binary log, which means that no changes to the sales.january table are logged; in this instance, --binlog-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored for purposes of binary logging.

  To specify more than one database to ignore, use this option multiple times, once for each database. Because database names can contain commas, the list will be treated as the name of a single database if you supply a comma-separated list.

  You should not use this option if you are using cross-database updates and you do not want these updates to be logged.

Checksum options. Beginning with MariaDB 5.6.2, MariaDB supports reading and writing of binary log checksums. These are enabled using the two options listed here:

• --binlog-checksum={NONE|CRC32}

  Version Introduced	5.6.2
  Command-Line Format	--binlog-checksum=type
  Option-File Format	binlog-checksum
  	Permitted Values
  	Type	string
  	Default	NONE
  	Valid Values	NONE CRC32

  Enabling this option causes the master to write checksums for events written to the binary log. Set to NONE (the default) to disable, or the name of the algorithm to be used for generating checksums; currently, only CRC32 checksums are supported.

  This option was added in MariaDB 5.6.2.

• --master-verify-checksum={0|1}

  Version Introduced	5.6.2
  Command-Line Format	--master-verify-checksum=name
  Option-File Format	master-verify-checksum
  Option Sets Variable	Yes, master_verify_checksum
  	Permitted Values
  	Type	boolean
  	Default	0
  	Valid Values	0 1

  Enabling this option causes the master to verify events from the binary log using checksums, and to stop with an error in the event of a mismatch. Disabled by default.

  This option was added in MariaDB 5.6.2.

To control reading of checksums by the slave (from the relay) log, use the --slave-sql-verify-checksum option.

Options for logging slave status to tables. MariaDB 5.6 and later supports logging of replication slave status information to tables rather than files. Writing of the master info log and the relay log info log can be configured separately using two server options added in MariaDB 5.6.2 and listed here:

• --master-info-repository={FILE|TABLE}

  Version Introduced	5.6.2
  Command-Line Format	--master-info-repository=FILE|TABLE
  Option-File Format	master-info-repository
  Option Sets Variable	Yes, master_info_repository
  	Permitted Values
  	Type	string
  	Default	FILE
  	Valid Values	FILE TABLE

  This option causes the server to write its master info log to a file or a table. The name of the file defaults to master.info; you can change the name of the file using the --master-info-file server option.

  The default value for this option is FILE. If you use TABLE, the log is written to the slave_master_info table in the MariaDB database.

  The --master-info-repository option was added in MariaDB 5.6.2.

• --relay-log-info-repository={FILE|TABLE}

  Version Introduced	5.6.2
  Command-Line Format	--relay-log-info-repository=FILE|TABLE
  Option-File Format	relay-log-info-repository
  Option Sets Variable	Yes, relay_log_info_repository
  	Permitted Values
  	Type	string
  	Default	FILE
  	Valid Values	FILE TABLE

  This option causes the server to log its relay log info to a file or a table. The name of the file defaults to relay-log.info; you can change the name of the file using the --relay-log-info-file server option.

  The default value for this option is FILE. If you use TABLE, the log is written to the slave_relay_log_info table in the MariaDB database.

  The --relay-log-info-repository option was added in MariaDB 5.6.2.

For more information, see , "Replication Relay and Status Logs".

Testing and debugging options. The following binary log options are used in replication testing and debugging. They are not intended for use in normal operations.

• --max-binlog-dump-events=N

  Command-Line Format	--max-binlog-dump-events=#
  Option-File Format	max-binlog-dump-events
  	Permitted Values
  	Type	numeric
  	Default	0

  This option is used internally by the MariaDB test suite for replication testing and debugging.

• --sporadic-binlog-dump-fail

  Command-Line Format	--sporadic-binlog-dump-fail
  Option-File Format	sporadic-binlog-dump-fail
  	Permitted Values
  	Type	boolean
  	Default	FALSE

  This option is used internally by the MariaDB test suite for replication testing and debugging.

• --binlog-rows-query-log-events

  Version Introduced	5.6.2
  Command-Line Format	--binlog-rows-query-log-events
  Option-File Format	binlog-rows-query-log-events
  Option Sets Variable	Yes, binlog_rows_query_log_events
  	Permitted Values
  	Type	boolean
  	Default	FALSE

  Added in MariaDB 5.6.2, this option enables binlog_rows_query_log_events. Must be set to OFF (the default) when generating logs for a MariaDB 5.6.1 or earlier slave server or version of mysqlbinlog.

System variables used with the binary log. The following list describes system variables for controlling binary logging. They can be set at server startup and some of them can be changed at runtime using SET. Server options used to control binary logging are listed earlier in this section.

• log_bin_basename

  Version Introduced	5.6.2
  Variable Name	log_bin_basename
  Variable Scope	Global
  Dynamic Variable	No
  	Permitted Values
  	Type	file name
  	Default	datadir + '/' + hostname + '-bin'

  Holds the name and complete path to the binary log file. Unlike the log-bin system variable, log_bin_basename reflects the name set with the --log-bin server option.

  The log_bin_basename system variable was added in MariaDB 5.6.2.

• binlog_cache_size

  Command-Line Format	--binlog_cache_size=#
  Option-File Format	binlog_cache_size
  Option Sets Variable	Yes, binlog_cache_size
  Variable Name	binlog_cache_size
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Platform Bit Size	32
  	Type	numeric
  	Default	32768
  	Range	4294967295
  	Permitted Values
  	Platform Bit Size	64
  	Type	numeric
  	Default	32768
  	Range	18446744073709547520

  The size of the cache to hold changes to the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (--log-bin option). If you often use large transactions, you can increase this cache size to get better performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable. See , "The Binary Log".

  binlog_cache_size sets the size for the transaction cache only; the size of the statement cache is governed by the binlog_stmt_cache_size system variable.

• binlog_checksum

  Version Introduced	5.6.2
  Variable Name	binlog_checksum
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Type	string
  	Default	NONE
  	Valid Values	NONE CRC32

  When enabled, this variable causes the master to write a checksum for each event in the binary log. binlog_checksum is disabled by default, and currently supports the values NONE and CRC32.

  When binlog_checksum is disabled (value NONE), the server verifies that it is writing only complete events to the binary log by writing and checking the event length (rather than a checksum) for each event.

  Changing the value of this variable causes the binary log to be rotated; checksums are always written to an entire binary log file, and never to only part of one.

  This variable was added in MariaDB 5.6.2.

  In MariaDB 5.6.6 and later, setting this variable on the master to a value unrecognized by the slave causes the slave to set its own binlog_checksum value to NONE, and to stop replication with an error. (Bug #13553750, Bug #61096)

• binlog_direct_non_transactional_updates

  Command-Line Format	--binlog_direct_non_transactional_updates[=value]
  Option-File Format	binlog_direct_non_transactional_updates
  Option Sets Variable	Yes, binlog_direct_non_transactional_updates
  Variable Name	binlog_direct_non_transactional_updates
  Variable Scope	Global, Session
  Dynamic Variable	Yes
  	Permitted Values
  	Type	boolean
  	Default	OFF

  Due to concurrency issues, a slave can become inconsistent when a transaction contains updates to both transactional and non-transactional tables. MariaDB tries to preserve causality among these statements by writing non-transactional statements to the transaction cache, which is flushed upon commit. However, problems arise when modifications done to nontransactional tables on behalf of a transaction become immediately visible to other connections because these changes may not be written immediately into the binary log.

  The binlog_direct_non_transactional_updates variable offers one possible workaround to this issue. By default, this variable is disabled. Enabling binlog_direct_non_transactional_updates causes updates to nontransactional tables to be written directly to the binary log, rather than to the transaction cache.

  binlog_direct_non_transactional_updates works only for statements that are replicated using the statement-based binary logging format; that is, it works only when the value of binlog_format is STATEMENT, or when binlog_format is MIXED and a given statement is being replicated using the statement-based format. This variable has no effect when the binary log format is ROW, or when binlog_format is set to MIXED and a given statement is replicated using the row-based format.Important

  Before enabling this variable, you must make certain that there are no dependencies between transactional and nontransactional tables; an example of such a dependency would be the statement INSERT INTO myisam_table SELECT * FROM innodb_table. Otherwise, such statements are likely to cause the slave to diverge from the master.

  In MariaDB 5.6, this variable has no effect when the binary log format is ROW or MIXED. (Bug #51291)

• binlog_format

  Command-Line Format	--binlog-format=format
  Option-File Format	binlog-format=format
  Option Sets Variable	Yes, binlog_format
  Variable Name	binlog_format
  Variable Scope	Global, Session
  Dynamic Variable	Yes
  	Permitted Values
  	Type	enumeration
  	Default	STATEMENT
  	Valid Values	ROW STATEMENT MIXED

  This variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED. See , "Replication Formats". binlog_format is set by the --binlog-format option at startup, or by the binlog_format variable at runtime.Note

  While you can change the logging format at runtime, it is not recommended that you change it while replication is ongoing. This is due in part to the fact that slaves do not honor the master's binlog_format setting; a given MariaDB Server can change only its own logging format.

  In MariaDB 5.6, the default format is STATEMENT.

  You must have the SUPER privilege to set either the global or session binlog_format value.

  The rules governing when changes to this variable take effect and how long the effect lasts are the same as for other MariaDB server system variables. See , "SET Syntax", for more information.

  When MIXED is specified, statement-based replication is used, except for cases where only row-based replication is guaranteed to lead to proper results. For example, this happens when statements contain user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always uses statement-based replication for stored functions and triggers.

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

  • From within a stored function or a trigger.
  • If the session is currently in row-based replication mode and has open temporary tables.
  • From within a transaction.

  Trying to switch the format in those cases results in an error.

  The binary log format affects the behavior of the following server options:

  • --replicate-do-db
  • --replicate-ignore-db
  • --binlog-do-db
  • --binlog-ignore-db

  These effects are discussed in detail in the descriptions of the individual options.

• master_verify_checksum

  Version Introduced	5.6.2
  Variable Name	master_verify_checksum
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Type	boolean
  	Default	0
  	Valid Values	0 1

  Enabling this variable causes the master to examine checksums when reading from the binary log. master_verify_checksum is disabled by default; in this case, the master uses the event length from the binary log to verify events, so that only complete events are read from the binary log.

  This variable was added in MariaDB 5.6.2.

• master_info_repository

  Version Introduced	5.6.2
  Variable Name	master_info_repository
  Variable Scope	Global
  Dynamic Variable	No
  	Permitted Values
  	Type	string
  	Default	FILE
  	Valid Values	FILE TABLE

  This variable shows whether the slave logs master status and connection information to a file (master.info) or to a table (mysql.slave_master_info_repository). This variable is read-only. Use the --master-info-repository server option to set the logging mode to FILE or TABLE.

  This variable was added in MariaDB 5.6.2.

• max_binlog_cache_size

  Command-Line Format	--max_binlog_cache_size=#
  Option-File Format	max_binlog_cache_size
  Option Sets Variable	Yes, max_binlog_cache_size
  Variable Name	max_binlog_cache_size
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Type	numeric
  	Default	18446744073709547520
  	Range	18446744073709547520

  If a transaction requires more than this many bytes of memory, the server generates a Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage error. The minimum value is 4096. The maximum and default values are 4GB on 32-bit platforms and 16PB (petabytes) on 64-bit platforms.

  max_binlog_cache_size sets the size for the transaction cache only; the upper limit for the statement cache is governed by the max_binlog_stmt_cache_size system variable.

  In MariaDB 5.6, the visibility to sessions of max_binlog_cache_size matches that of the binlog_cache_size system variable; in other words, changing its value effects only new sessions that are started after the value is changed.

• max_binlog_stmt_cache_size

  Version Introduced	5.6.1
  Command-Line Format	--max_binlog_stmt_cache_size=#
  Option-File Format	max_binlog_stmt_cache_size
  Option Sets Variable	Yes, max_binlog_stmt_cache_size
  Variable Name	max_binlog_stmt_cache_size
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Type	numeric
  	Default	18446744073709547520
  	Range	18446744073709547520

  If nontransaction statements within a transaction require more than this many bytes of memory, the server generates an error. The minimum value is 4096. The maximum and default values are 4GB on 32-bit platforms and 16PB (petabytes) on 64-bit platforms.

  max_binlog_stmt_cache_size sets the size for the transaction cache only; the upper limit for the transaction cache is governed exclusively by the max_binlog_cache_size system variable.

• max_binlog_size

  Command-Line Format	--max_binlog_size=#
  Option-File Format	max_binlog_size
  Option Sets Variable	Yes, max_binlog_size
  Variable Name	max_binlog_size
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Type	numeric
  	Default	1073741824
  	Range	1073741824

  If a write to the binary log causes the current log file size to exceed the value of this variable, the server rotates the binary logs (closes the current file and opens the next one). The minimum value is 4096 bytes. The maximum and default value is 1GB.

  A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

  If max-relay-log-size is 0, the value of max_binlog_size applies to relay logs as well.

• relay_log_info_repository

  Version Introduced	5.6.2
  Variable Name	relay_log_info_repository
  Variable Scope	Global
  Dynamic Variable	No
  	Permitted Values
  	Type	string
  	Default	FILE
  	Valid Values	FILE TABLE

  This variable shows whether the slave's position in the relay logs is written to a file (relay-log.info) or to a table (mysql.slave_relay_log_info). This variable is read-only. Use the --relay-log-info-repository server option to set the logging mode to FILE or TABLE.

  This variable was added in MariaDB 5.6.2.

• binlog_row_image

  Version Introduced	5.6.2
  Command-Line Format	--binlog-row-image=image_type
  Variable Name	binlog_row_image=image_type
  Variable Scope	Global, Session
  Dynamic Variable	Yes
  	Permitted Values
  	Type	enumeration
  	Default	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

  In MariaDB 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, MariaDB 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.

  In MariaDB 5.6, 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 that are actually changed.
  • 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.

  The default value is full. In MariaDB 5.5 and earlier, full row images are always used for both before images and after images. If you need to replicate from a MariaDB 5.6 (or later) master to a slave running a previous version of MySQL, the master should always use this value.

  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 is MIXED, 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.

• binlog_stmt_cache_size

  Version Introduced	5.6.1
  Command-Line Format	--binlog_stmt_cache_size=#
  Option-File Format	binlog_stmt_cache_size
  Option Sets Variable	Yes, binlog_stmt_cache_size
  Variable Name	binlog_stmt_cache_size
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Platform Bit Size	32
  	Type	numeric
  	Default	32768
  	Range	4294967295
  	Permitted Values
  	Platform Bit Size	64
  	Type	numeric
  	Default	32768
  	Range	18446744073709547520

  This variable determines the size of the cache for the binary log to hold nontransactional statements issued during a transaction. Separate binary log transaction and statement caches are allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (--log-bin option). If you often use large nontransactional statements during transactions, you can increase this cache size to get better performance. The Binlog_stmt_cache_use and Binlog_stmt_cache_disk_use status variables can be useful for tuning the size of this variable. See , "The Binary Log".

  The binlog_cache_size system variable sets the size for the transaction cache.

• sync_binlog

  Command-Line Format	--sync-binlog=#
  Option-File Format	sync_binlog
  Option Sets Variable	Yes, sync_binlog
  Variable Name	sync_binlog
  Variable Scope	Global
  Dynamic Variable	Yes
  	Permitted Values
  	Platform Bit Size	32
  	Type	numeric
  	Default	0
  	Range	0 .. 4294967295
  	Permitted Values
  	Platform Bit Size	64
  	Type	numeric
  	Default	0
  	Range	0 .. 18446744073709547520

  If the value of this variable is greater than 0, the MariaDB server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk-in this case, the server relies on the operating system to flush the binary log's contents from to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

• binlog_rows_query_log_events

  Version Introduced	5.6.2
  Variable Name	binlog_rows_query_log_events
  Variable Scope	Global, Session
  Dynamic Variable	Yes
  	Permitted Values
  	Type	boolean
  	Default	FALSE

  The binlog_rows_query_log_events system variable affects row-based logging only. When enabled, it causes a MariaDB 5.6.2 or later server to write informational log events such as row query log events into its binary log. This information can be used for debugging and related purposes; such as obtaining the original query issued on the master when it cannot be reconstructed from the row updates.

  These events are normally ignored by MariaDB 5.6.2 and later programs reading the binary log and so cause no issues when replicating or restoring from backup. This is not true for a mysqld or mysqlbinlog from MariaDB 5.6.1 or earlier: When the older version of the program reading the log encounters an informational log event, it fails, and stops reading at that point. To make the binary log readable by slave replication MariaDB servers and other readers (for example, mysqlbinlog) from a MariaDB 5.6.1 or earlier distribution, binlog_rows_query_log_events must be disabled during logging.

Retornar