Chapter 17 Replication

abort-slave-event-count: Option used by mysql-test for debugging and testing of replication.

auto_increment_increment: AUTO_INCREMENT columns are incremented by this value.

auto_increment_offset: Offset added to AUTO_INCREMENT columns.

binlog_gtid_simple_recovery: Controls how binary logs are iterated during GTID recovery.

Com_change_master: Count of CHANGE MASTER TO statements.

Com_show_master_status: Count of SHOW MASTER STATUS statements.

Com_show_slave_hosts: Count of SHOW SLAVE HOSTS statements.

Com_show_slave_status: Count of SHOW SLAVE STATUS statements.

Com_slave_start: Count of START SLAVE statements.

Com_slave_stop: Count of STOP SLAVE statements.

disconnect-slave-event-count: Option used by mysql-test for debugging and testing of replication.

enforce_gtid_consistency: Prevents execution of statements that cannot be logged in a transactionally safe manner.

expire_logs_days: Purge binary logs after this many days.

gtid_executed: Global: All GTIDs in the binary log (global) or current transaction (session). Read-only.

gtid_mode: Controls whether GTID based logging is enabled and what type of transactions the logs can contain.

gtid_next: Specifies the GTID for the next statement to execute; see documentation for details.

gtid_owned: The set of GTIDs owned by this client (session), or by all clients, together with the thread ID of the owner (global). Read-only.

gtid_purged: The set of all GTIDs that have been purged from the binary log.

init_slave: Statements that are executed when a slave connects to a master.

log_bin_trust_function_creators: If equal to 0 (the default), then when --log-bin is used, creation of a stored function is allowed only to users having the SUPER privilege and only if the function created does not break binary logging.

master-info-file: The location and name of the file that remembers the master and where the I/O replication thread is in the master's binary logs.

master-retry-count: Number of tries the slave makes to connect to the master before giving up.

master_info_repository: Whether to write master status information and replication I/O thread location in the master's binary logs to a file or table.

max_relay_log_size: If nonzero, relay log is rotated automatically when its size exceeds this value. If zero, size at which rotation occurs is determined by the value of max_binlog_size.

relay_log: The location and base name to use for relay logs.

relay_log_basename: Complete path to relay log, including file name.

relay_log_index: The location and name to use for the file that keeps a list of the last relay logs.

relay_log_info_file: File in which the slave records information about the relay logs.

relay_log_info_repository: Whether to write the replication SQL thread's location in the relay logs to a file or a table.

relay_log_purge: Determines whether relay logs are purged.

relay_log_recovery: Whether automatic recovery of relay log files from master at startup is enabled; must be enabled for a crash-safe slave.

relay_log_space_limit: Maximum space to use for all relay logs.

replicate-do-db: Tells the slave SQL thread to restrict replication to the specified database.

replicate-do-table: Tells the slave SQL thread to restrict replication to the specified table.

replicate-ignore-db: Tells the slave SQL thread not to replicate to the specified database.

replicate-ignore-table: Tells the slave SQL thread not to replicate to the specified table.

replicate-rewrite-db: Updates to a database with a different name than the original.

replicate-same-server-id: In replication, if enabled, do not skip events having our server id.

replicate-wild-do-table: Tells the slave thread to restrict replication to the tables that match the specified wildcard pattern.

replicate-wild-ignore-table: Tells the slave thread not to replicate to the tables that match the given wildcard pattern.

report_host: Host name or IP of the slave to be reported to the master during slave registration.

report_password: An arbitrary password that the slave server should report to the master. Not the same as the password for the MySQL replication user account.

report_port: Port for connecting to slave reported to the master during slave registration.

report_user: An arbitrary user name that a slave server should report to the master. Not the same as the name used with the MySQL replication user account.

Rpl_semi_sync_master_clients: Number of semisynchronous slaves.

rpl_semi_sync_master_enabled: Whether semisynchronous replication is enabled on the master.

Rpl_semi_sync_master_net_avg_wait_time: The average time the master waited for a slave reply.

Rpl_semi_sync_master_net_wait_time: The total time the master waited for slave replies.

Rpl_semi_sync_master_net_waits: The total number of times the master waited for slave replies.

Rpl_semi_sync_master_no_times: Number of times the master turned off semisynchronous replication.

Rpl_semi_sync_master_no_tx: Number of commits not acknowledged successfully.

Rpl_semi_sync_master_status: Whether semisynchronous replication is operational on the master.

Rpl_semi_sync_master_timefunc_failures: Number of times the master failed when calling time functions.

rpl_semi_sync_master_timeout: Number of milliseconds to wait for slave acknowledgment.

rpl_semi_sync_master_trace_level: The semisynchronous replication debug trace level on the master.

Rpl_semi_sync_master_tx_avg_wait_time: The average time the master waited for each transaction.

Rpl_semi_sync_master_tx_wait_time: The total time the master waited for transactions.

Rpl_semi_sync_master_tx_waits: The total number of times the master waited for transactions.

rpl_semi_sync_master_wait_no_slave: Whether master waits for timeout even with no slaves.

Rpl_semi_sync_master_wait_pos_backtraverse: The total number of times the master waited for an event with binary coordinates lower than events waited for previously.

Rpl_semi_sync_master_wait_sessions: Number of sessions currently waiting for slave replies.

Rpl_semi_sync_master_yes_tx: Number of commits acknowledged successfully.

rpl_semi_sync_slave_enabled: Whether semisynchronous replication is enabled on slave.

Rpl_semi_sync_slave_status: Whether semisynchronous replication is operational on slave.

rpl_semi_sync_slave_trace_level: The semisynchronous replication debug trace level on the slave.

rpl_stop_slave_timeout: Set the number of seconds that STOP SLAVE waits before timing out.

server_uuid: The server's globally unique ID, automatically (re)generated at server start.

show-slave-auth-info: Show user name and password in SHOW SLAVE HOSTS on this master.

simplified_binlog_gtid_recovery: Renamed to binlog_gtid_simple_recovery.

skip-slave-start: If set, slave is not autostarted.

slave_load_tmpdir: The location where the slave should put its temporary files when replicating LOAD DATA statements.

slave_net_timeout: Number of seconds to wait for more data from a master/slave connection before aborting the read.

slave-skip-errors: Tells the slave thread to continue replication when a query returns an error from the provided list.

slave_checkpoint_group: Maximum number of transactions processed by a multithreaded slave before a checkpoint operation is called to update progress status. Not supported by NDB Cluster.

slave_checkpoint_period: Update progress status of multithreaded slave and flush relay log info to disk after this number of milliseconds. Not supported by NDB Cluster.

slave_compressed_protocol: Use compression of master/slave protocol.

slave_exec_mode: Allows for switching the slave thread between IDEMPOTENT mode (key and some other errors suppressed) and STRICT mode; STRICT mode is the default, except for NDB Cluster, where IDEMPOTENT is always used.

Slave_heartbeat_period: The slave's replication heartbeat interval, in seconds.

Slave_last_heartbeat: Shows when the latest heartbeat signal was received, in TIMESTAMP format.

slave_max_allowed_packet: Maximum size, in bytes, of a packet that can be sent from a replication master to a slave; overrides max_allowed_packet.

Slave_open_temp_tables: Number of temporary tables that the slave SQL thread currently has open.

slave_parallel_workers: Number of applier threads for executing replication transactions in parallel. A value of 0 disables slave multithreading. Not supported by MySQL Cluster.

slave_pending_jobs_size_max: Maximum size of slave worker queues holding events not yet applied.

Slave_received_heartbeats: Number of heartbeats received by a replication slave since previous reset.

Slave_retried_transactions: The total number of times since startup that the replication slave SQL thread has retried transactions.

slave_rows_search_algorithms: Determines search algorithms used for slave update batching. Any 2 or 3 from the list INDEX_SEARCH, TABLE_SCAN, HASH_SCAN.

Slave_rows_last_search_algorithm_used: Search algorithm most recently used by this slave to locate rows for row-based replication (index, table, or hash scan).

Slave_running: The state of this server as a replication slave (slave I/O thread status).

slave_transaction_retries: Number of times the slave SQL thread will retry a transaction in case it failed with a deadlock or elapsed lock wait timeout, before giving up and stopping.

slave_type_conversions: Controls type conversion mode on replication slave. Value is a list of zero or more elements from the list: ALL_LOSSY, ALL_NON_LOSSY. Set to an empty string to disallow type conversions between master and slave.

sql_log_bin: Controls binary logging for the current session.

sql_slave_skip_counter: Number of events from the master that a slave server should skip. Not compatible with GTID replication.

sync_master_info: Synchronize master.info to disk after every #th event.

sync_relay_log: Synchronize relay log to disk after every #th event.

sync_relay_log_info: Synchronize relay.info file to disk after every #th event.

binlog-checksum: Enable/disable binary log checksums.

binlog-do-db: Limits binary logging to specific databases.

binlog_format: Specifies the format of the binary log.

binlog-ignore-db: Tells the master that updates to the given database should not be logged to the binary log.

binlog-row-event-max-size: Binary log max event size.

Binlog_cache_disk_use: Number of transactions that used a temporary file instead of the binary log cache.

binlog_cache_size: Size of the cache to hold the SQL statements for the binary log during a transaction.

Binlog_cache_use: Number of transactions that used the temporary binary log cache.

binlog_checksum: Enable/disable binary log checksums.

binlog_direct_non_transactional_updates: Causes updates using statement format to nontransactional engines to be written directly to binary log. See documentation before using.

binlog_error_action: Controls what happens when the server cannot write to the binary log.

binlog_max_flush_queue_time: How long to read transactions before flushing to binary log.

binlog_order_commits: Whether to commit in same order as writes to binary log.

binlog_row_image: Use full or minimal images when logging row changes.

binlog_rows_query_log_events: When enabled, enables logging of rows query log events when using row-based logging. Disabled by default. Do not enable when producing logs for pre-5.6 slaves/readers.

Binlog_stmt_cache_disk_use: Number of nontransactional statements that used a temporary file instead of the binary log statement cache.

binlog_stmt_cache_size: Size of the cache to hold nontransactional statements for the binary log during a transaction.

Binlog_stmt_cache_use: Number of statements that used the temporary binary log statement cache.

binlogging_impossible_mode: Deprecated and later removed. Use binlog_error_action instead.

Com_show_binlog_events: Count of SHOW BINLOG EVENTS statements.

Com_show_binlogs: Count of SHOW BINLOGS statements.

log-bin: Specifies the base name for binary log files.

log-bin-index: Specifies the name for the binary log index file.

log_bin: Whether the binary log is enabled.

log_bin_basename: Path and base name for binary log files.

log_bin_use_v1_row_events: Whether server is using version 1 binary log row events.

log_slave_updates: Whether the slave should log the updates performed by its SQL thread to its own binary log.

master_verify_checksum: Cause master to examine checksums when reading from the binary log.

max-binlog-dump-events: Option used by mysql-test for debugging and testing of replication.

max_binlog_cache_size: Can be used to restrict the total size used to cache a multi-statement transaction.

max_binlog_size: Binary log will be rotated automatically when size exceeds this value.

max_binlog_stmt_cache_size: Can be used to restrict the total size used to cache all nontransactional statements during a transaction.

slave-sql-verify-checksum: Cause slave to examine checksums when reading from the relay log.

slave_sql_verify_checksum: Cause slave to examine checksums when reading from relay log.

sporadic-binlog-dump-fail: Option used by mysql-test for debugging and testing of replication.

sync_binlog: Synchronously flush binary log to disk after every #th event.

--show-slave-auth-info

Display slave user names and passwords in the output of SHOW SLAVE HOSTS on the master server for slaves started with the --report-user and --report-password options.

auto_increment_increment

auto_increment_increment and auto_increment_offset are intended for use with master-to-master replication, and can be used to control the operation of AUTO_INCREMENT columns. Both variables have global and session values, and each can assume an integer value between 1 and 65,535 inclusive. Setting the value of either of these two variables to 0 causes its value to be set to 1 instead. Attempting to set the value of either of these two variables to an integer greater than 65,535 or less than 0 causes its value to be set to 65,535 instead. Attempting to set the value of auto_increment_increment or auto_increment_offset to a noninteger value produces an error, and the actual value of the variable remains unchanged.

These two variables affect AUTO_INCREMENT column behavior as follows:

If either of these variables is changed, and then new rows inserted into a table containing an AUTO_INCREMENT column, the results may seem counterintuitive because the series of AUTO_INCREMENT values is calculated without regard to any values already present in the column, and the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column. The series is calculated like this:

auto_increment_offset + N × auto_increment_increment

where N is a positive integer value in the series [1, 2, 3, ...]. For example:

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
+-----+
4 rows in set (0.00 sec)

mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
|  35 |
|  45 |
|  55 |
|  65 |
+-----+
8 rows in set (0.00 sec)

The values shown for auto_increment_increment and auto_increment_offset generate the series 5 + N × 10, that is, [5, 15, 25, 35, 45, ...]. The highest value present in the col column prior to the INSERT is 31, and the next available value in the AUTO_INCREMENT series is 35, so the inserted values for col begin at that point and the results are as shown for the SELECT query.

It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server. If the global value of either variable is set, its effects persist until the global value is changed or overridden by setting the session value, or until mysqld is restarted. If the local value is set, the new value affects AUTO_INCREMENT columns for all tables into which new rows are inserted by the current user for the duration of the session, unless the values are changed during that session.

The default value of auto_increment_increment is 1. See Section 17.4.1.1, “Replication and AUTO_INCREMENT”.

auto_increment_offset

This variable has a default value of 1. For more information, see the description for auto_increment_increment.

rpl_semi_sync_master_enabled

Controls whether semisynchronous replication is enabled on the master. To enable or disable the plugin, set this variable to ON or OFF (or 1 or 0), respectively. The default is OFF.

This variable is available only if the master-side semisynchronous replication plugin is installed.

rpl_semi_sync_master_timeout

A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).

This variable is available only if the master-side semisynchronous replication plugin is installed.

rpl_semi_sync_master_trace_level

The semisynchronous replication debug trace level on the master. Four levels are defined:

This variable is available only if the master-side semisynchronous replication plugin is installed.

rpl_semi_sync_master_wait_no_slave

With semisynchronous replication, for each transaction, the master waits until timeout for acknowledgment of receipt from some semisynchronous slave. If no response occurs during this period, the master reverts to normal replication. This variable controls whether the master waits for the timeout to expire before reverting to normal replication even if the slave count drops to zero during the timeout period.

If the value is ON (the default), it is permissible for the slave count to drop to zero during the timeout period (for example, if slaves disconnect). The master still waits for the timeout, so as long as some slave reconnects and acknowledges the transaction within the timeout interval, semisynchronous replication continues.

If the value is OFF, the master reverts to normal replication if the slave count drops to zero during the timeout period.

This variable is available only if the master-side semisynchronous replication plugin is installed.

--abort-slave-event-count

When this option is set to some positive integer value other than 0 (the default) it affects replication behavior as follows: After the slave SQL thread has started, value log events are permitted to be executed; after that, the slave SQL thread does not receive any more events, just as if the network connection from the master were cut. The slave thread continues to run, and the output from SHOW SLAVE STATUS displays Yes in both the Slave_IO_Running and the Slave_SQL_Running columns, but no further events are read from the relay log.

This option is used internally by the MySQL test suite for replication testing and debugging. It is not intended for use in a production setting.

--disconnect-slave-event-count

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

--log-slow-slave-statements

This command-line option was removed in MySQL 5.6.11 and replaced by the log_slow_slave_statements system variable. The system variable can be set on the command line or in option files the same way as the option, so there is no need for any changes at server startup, but the system variable also makes it possible to examine or set the value at runtime.

--log-warnings[=level]

Causes the server to record more messages to the error log about what it is doing. With respect to replication, the server generates warnings that it succeeded in reconnecting after a network or connection failure, and provides information about how each slave thread started. This option is enabled (1) by default; to disable it, use --log-warnings=0. If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written. See Section B.4.2.11, “Communication Errors and Aborted Connections”.

--master-info-file=file_name

The name to use for the file in which the slave records information about the master. The default name is master.info in the data directory. For information about the format of this file, see Section 17.2.2.2, “Slave Status Logs”.

--master-retry-count=count

The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement (default 60). Reconnection attempts are triggered when the slave reaches its connection timeout (specified by the slave_net_timeout system variable) without receiving data from the master. The default value is 86400. A value of 0 means “infinite”; the slave attempts to connect forever.

This option is deprecated and will be removed in a future MySQL release. Applications should be updated to use the MASTER_RETRY_COUNT option of the CHANGE MASTER TO statement instead.

--max-relay-log-size=size

The size at which the server rotates relay log files automatically. If this value is nonzero, the relay log is rotated automatically when its size exceeds this value. If this value is zero (the default), the size at which relay log rotation occurs is determined by the value of max_binlog_size. For more information, see Section 17.2.2.1, “The Slave Relay Log”.

--relay-log-purge={0|1}

Disable or enable automatic purging of relay logs as soon as they are no longer needed. The default value is 1 (enabled). This is a global variable that can be changed dynamically with SET GLOBAL relay_log_purge = N. Disabling purging of relay logs when enabling the --relay-log-recovery option puts data consistency at risk.

--relay-log-space-limit=size

This option places an upper limit on the total size in bytes of all relay logs on the slave. A value of 0 means “no limit”. This is useful for a slave server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unused relay logs. Note that this limit is not absolute: There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread exceeds the limit until it becomes possible for the SQL thread to delete some relay logs because not doing so would cause a deadlock. You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread waits for free space because --relay-log-space-limit is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to ignore --relay-log-space-limit temporarily.

--replicate-do-db=db_name

The effects of this option depend on whether statement-based or row-based replication is in use.

Statement-based replication.  Tell the slave SQL thread to restrict replication to statements 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 replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while a different database (or no database) is selected.

An example of what does not work as you might expect when using statement-based replication: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:

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

The main reason for this “check just 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.

Row-based replication.  Tells the slave SQL thread to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. Suppose that the slave is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the master:

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

The february table in the sales database on the slave is changed in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, issuing the following statements on the master has no effect on the slave when using row-based replication and --replicate-do-db=sales:

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

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

Another important difference in how --replicate-do-db is handled in statement-based replication as opposed to row-based replication occurs with regard to statements that refer to multiple databases. Suppose that the slave is started with --replicate-do-db=db1, and the following statements are executed on the master:

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

If you are using statement-based replication, then both tables are updated on the slave. However, when using row-based replication, only table1 is affected on the slave; since table2 is in a different database, table2 on the slave 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 would have no effect on the slave when using statement-based replication. However, if you are using row-based replication, the UPDATE would change table1 on the slave, but not table2—in other words, only tables in the database named by --replicate-do-db are changed, and the choice of default database has no effect on this behavior.

If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

--replicate-ignore-db=db_name

As with --replicate-do-db, the effects of this option depend on whether statement-based or row-based replication is in use.

Statement-based replication.  Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name.

Row-based replication.  Tells the slave SQL thread not to update any tables in the database db_name. The default database has no effect.

When using statement-based replication, the following example does not work as you might expect. Suppose that the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master:

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

The UPDATE statement is replicated in such a case because --replicate-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 replication, the UPDATE statement's effects are not propagated to the slave, and the slave's copy of the sales.january table is unchanged; in this instance, --replicate-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored by the slave.

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

You should not use this option if you are using cross-database updates and you do not want these updates to be replicated. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

If you need cross-database updates to work, use --replicate-wild-ignore-table=db_name.% instead. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

--replicate-do-table=db_name.tbl_name

Creates a replication filter by telling the slave SQL thread to restrict replication to a given table. To specify more than one table, use this option multiple times, once for each table. This works for both cross-database updates and default database updates, in contrast to --replicate-do-db. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the --replicate-*-db options.

--replicate-ignore-table=db_name.tbl_name

Creates a replication filter by telling the slave SQL thread not to replicate any statement that updates the specified table, even if any other tables might be updated by the same statement. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-ignore-db. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the --replicate-*-db options.

--replicate-rewrite-db=from_name->to_name

Tells the slave to create a replication filter that translates the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master. To specify multiple rewrites, use this option multiple times. The server uses the first one with a from_name value that matches. The database name translation is done before the --replicate-* rules are tested.

Statements in which table names are qualified with database names when using this option do not work with table-level replication filtering options such as --replicate-do-table. Suppose we have a database named a on the master, one named b on the slave, each containing a table t, and have started the master with --replicate-rewrite-db='a->b'. At a later point in time, we execute DELETE FROM a.t. In this case, no relevant filtering rule works, for the reasons shown here:

Similarly, the --replication-rewrite-db option does not work with cross-database updates.

If you use this option on the command line and the > character is special to your command interpreter, quote the option value. For example:

shell> mysqld --replicate-rewrite-db="olddb->newdb"

--replicate-same-server-id

To be used on slave servers. Usually you should use the default setting of 0, to prevent infinite loops caused by circular replication. If set to 1, the slave does not skip events having its own server ID. Normally, this is useful only in rare configurations. Cannot be set to 1 if log_slave_updates is enabled. By default, the slave I/O thread does not write binary log events to the relay log if they have the slave's server ID (this optimization helps save disk usage). If you want to use --replicate-same-server-id, be sure to start the slave with this option before you make the slave read its own events that you want the slave SQL thread to execute.

--replicate-wild-do-table=db_name.tbl_name

Creates a replication filter by telling the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the % and _ wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. To filter statements operating on the latter objects, use one or more of the --replicate-*-db options.

Example: --replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar.

If the table name pattern is %, it matches any table name and the option also applies to database-level statements (CREATE DATABASE, DROP DATABASE, and ALTER DATABASE). For example, if you use --replicate-wild-do-table=foo%.%, database-level statements are replicated if the database name matches the pattern foo%.

To include literal wildcard characters in the database or table name patterns, escape them with a backslash. For example, to replicate all tables of a database that is named my_own%db, but not replicate tables from the my1ownAABCdb database, you should escape the _ and % characters like this: --replicate-wild-do-table=my\_own\%db. If you use the option on the command line, you might need to double the backslashes or quote the option value, depending on your command interpreter. For example, with the bash shell, you would need to type --replicate-wild-do-table=my\\_own\\%db.

--replicate-wild-ignore-table=db_name.tbl_name

Creates a replication filter which keeps the slave thread from replicating a statement in which any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates. See Section 17.2.3, “How Servers Evaluate Replication Filtering Rules”.

Example: --replicate-wild-ignore-table=foo%.bar% does not replicate updates that use a table where the database name starts with foo and the table name starts with bar.

For information about how matching works, see the description of the --replicate-wild-do-table option. The rules for including literal wildcard characters in the option value are the same as for --replicate-wild-ignore-table as well.

--skip-slave-start

Tells the slave server not to start the slave threads when the server starts. To start the threads later, use a START SLAVE statement.

--slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors]

Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.

Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.

For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.

You can also (but should not) use the very nonrecommended value of all to cause the slave to ignore all error messages and keeps going regardless of what happens. Needless to say, if you use all, there are no guarantees regarding the integrity of your data. Please do not complain (or file bug reports) in this case if the slave's data is not anywhere close to what it is on the master. You have been warned.

MySQL 5.6 as well as MySQL NDB Cluster 7.3 and later support an additional shorthand value ddl_exist_errors, which is equivalent to the error code list 1007,1008,1050,1051,1054,1060,1061,1068,1094,1146.

Examples:

--slave-skip-errors=1062,1053
--slave-skip-errors=all
--slave-skip-errors=ddl_exist_errors

--slave-sql-verify-checksum={0|1}

When this option is enabled, the slave examines checksums read from the relay log, in the event of a mismatch, the slave stops with an error.

master_info_repository

relay_log_info_repository

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

init_slave

This variable is similar to init_connect, but is a string to be executed by a slave server each time the SQL thread starts. The format of the string is the same as for the init_connect variable.

log_slow_slave_statements

When the slow query log is enabled, this variable enables logging for queries that have taken more than long_query_time seconds to execute on the slave. Note that if row-based replication is in use (binlog_format=ROW), log_slow_slave_statements has no effect. Queries are only added to the slave's slow query log when they are logged in statement format in the binary log, that is, when binlog_format=STATEMENT is set, or when binlog_format=MIXED is set and the statement is logged in statement format. Slow queries that are logged in row format when binlog_format=MIXED is set, or that are logged when binlog_format=ROW is set, are not added to the slave's slow query log, even if log_slow_slave_statements is enabled.

Setting log_slow_slave_statements has no immediate effect. The state of the variable applies on all subsequent START SLAVE statements. Also note that the global setting for long_query_time applies for the lifetime of the SQL thread. If you change that setting, you must stop and restart the slave's SQL thread to implement the change there (for example, by issuing STOP SLAVE and START SLAVE statements with the SQL_THREAD option).

master_info_repository

The setting of this variable determines whether the slave server logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info).

The setting of this variable also has a direct influence on the effect had by the setting of the sync_master_info system variable; see that variable description for further information.

For FILE logging, you can change the name of the file using the --master-info-file server option.

max_relay_log_size

If a write by a replication slave to its relay log causes the current log file size to exceed the value of this variable, the slave rotates the relay logs (closes the current file and opens the next one). If max_relay_log_size is 0, the server uses max_binlog_size for both the binary log and the relay log. If max_relay_log_size is greater than 0, it constrains the size of the relay log, which enables you to have different sizes for the two logs. You must set max_relay_log_size to between 4096 bytes and 1GB (inclusive), or to 0. The default value is 0. See Section 17.2.1, “Replication Implementation Details”.

relay_log

The base name for the relay log. The default base name is host_name-relay-bin.

The server writes the file in the data directory unless the base name is given with a leading absolute path name to specify a different directory. The server creates relay log files in sequence by adding a numeric suffix to the base name.

Due to the manner in which MySQL parses server options, if you specify this variable at server startup, you must supply a value; the default base name is used only if the option is not actually specified. If you specify the relay_log system variable at server startup without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.2, “Specifying Program Options”.

If you specify this variable, the value specified is also used as the base name for the relay log index file. You can override this behavior by specifying a different relay log index file base name using the relay_log_index system variable.

When the server reads an entry from the index file, it checks whether the entry contains a relative path. If it does, the relative part of the path is replaced with the absolute path set using the relay_log system variable. 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.

You may find the relay_log system variable useful in performing the following tasks:

You can obtain the relay log file name (and path) from the relay_log_basename system variable.

relay_log_basename

Holds the name and complete path to the relay log file. This variable is set by the server and is read only.

relay_log_index

The name for the relay log index file. The default name is host_name-relay-bin.index in the data directory, where host_name is the name of the slave server.

Due to the manner in which MySQL parses server options, if you specify this variable at server startup, you must supply a value; the default base name is used only if the option is not actually specified. If you specify the relay_log_index system variable at server startup without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.2, “Specifying Program Options”.

relay_log_info_file

The name of the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory. For information about the format of this file, see Section 17.2.2.2, “Slave Status Logs”.

relay_log_info_repository

This variable determines 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).

The setting of this variable also has a direct influence on the effect had by the setting of the sync_relay_log_info system variable; see that variable descrption for further information.

This option can be used to make a replication slave resilient to unexpected halts. See Section 17.3.2, “Handling an Unexpected Halt of a Replication Slave”, for more information.

relay_log_purge

Disables or enables automatic purging of relay log files as soon as they are not needed any more. The default value is 1 (ON).

relay_log_recovery

If enabled, this variable enables automatic relay log recovery immediately following server startup. The recovery process creates a new relay log file, initializes the SQL thread position to this new relay log, and initializes the I/O thread to the SQL thread position. Reading of the relay log from the master then continues. This global variable is read-only at runtime. Its value can set with the --relay-log-recovery option at slave startup, which should be used following an unexpected halt of a replication slave to ensure that no possibly corrupted relay logs are processed. This option can be enabled to make a slave resilient to unexpected halts. See Section 17.3.2, “Handling an Unexpected Halt of a Replication Slave” for more information.

This variable also interacts with the relay_log_purge variable, which controls purging of logs when they are no longer needed. Enabling relay_log_recovery when relay_log_purge is disabled risks reading the relay log from files that were not purged, leading to data inconsistency.

When relay_log_recovery is enabled and the slave has stopped due to errors encountered while running in multithreaded mode, you cannot execute CHANGE MASTER TO if there are any gaps in the log. The solution in this situation is to issue START SLAVE UNTIL SQL_AFTER_MTS_GAPS to ensure that all gaps are processed before switching back to single-threaded mode or executing a CHANGE MASTER TO statement.

relay_log_space_limit

The maximum amount of space to use for all relay logs.

report_host

The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server. Leave the value unset if you do not want the slave to register itself with the master.

report_password

The account password of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the master was started with --show-slave-auth-info.

Although the name of this variable might imply otherwise, report_password is not connected to the MySQL user privilege system and so is not necessarily (or even likely to be) the same as the password for the MySQL replication user account.

report_port

The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set this only if the slave is listening on a nondefault port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, do not use this option.

The default value for this option is the port number actually used by the slave. This is also the default value displayed by SHOW SLAVE HOSTS.

report_user

The account user name of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the master was started with --show-slave-auth-info.

Although the name of this variable might imply otherwise, report_user is not connected to the MySQL user privilege system and so is not necessarily (or even likely to be) the same as the name of the MySQL replication user account.

rpl_semi_sync_slave_enabled

Controls whether semisynchronous replication is enabled on the slave. To enable or disable the plugin, set this variable to ON or OFF (or 1 or 0), respectively. The default is OFF.

This variable is available only if the slave-side semisynchronous replication plugin is installed.

rpl_semi_sync_slave_trace_level

The semisynchronous replication debug trace level on the slave. See rpl_semi_sync_master_trace_level for the permissible values.

This variable is available only if the slave-side semisynchronous replication plugin is installed.

rpl_stop_slave_timeout

In MySQL 5.6.13 and higher, you can control the length of time (in seconds) that STOP SLAVE waits before timing out by setting this variable. This can be used to avoid deadlocks between STOP SLAVE and other slave SQL statements using different client connections to the slave.

The maximum and default value of rpl_stop_slave_timeout is 31536000 seconds (1 year). The minimum is 2 seconds. Changes to this variable take effect for subsequent STOP SLAVE statements.

This variable affects only the client that issues a STOP SLAVE statement. When the timeout is reached, the issuing client returns an error message stating that the command execution is incomplete. The client then stops waiting for the slave threads to stop, but the slave threads continue to try to stop, and the STOP SLAVE instruction remains in effect. Once the slave threads are no longer busy, the STOP SLAVE statement is executed and the slave stops.

slave_checkpoint_group

Sets the maximum number of transactions that can be processed by a multithreaded slave before a checkpoint operation is called to update its status as shown by SHOW SLAVE STATUS. Setting this variable has no effect on slaves for which multithreading is not enabled.

This variable works in combination with the slave_checkpoint_period system variable in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

The minimum allowed value for this variable is 32, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 1. The effective value is always a multiple of 8; you can set it to a value that is not such a multiple, but the server rounds it down to the next lower multiple of 8 before storing the value. (Exception: No such rounding is performed by the debug server.) Regardless of how the server was built, the default value is 512, and the maximum allowed value is 524280.

slave_checkpoint_period

Sets the maximum time (in milliseconds) that is allowed to pass before a checkpoint operation is called to update the status of a multithreaded slave as shown by SHOW SLAVE STATUS. Setting this variable has no effect on slaves for which multithreading is not enabled.

This variable works in combination with the slave_checkpoint_group system variable in such a way that, when either limit is exceeded, the checkpoint is executed and the counters tracking both the number of transactions and the time elapsed since the last checkpoint are reset.

The minimum allowed value for this variable is 1, unless the server was built using -DWITH_DEBUG, in which case the minimum value is 0. Regardless of how the server was built, the default value is 300, and the maximum possible value is 4294967296 (4GB).

slave_compressed_protocol

Whether to use compression of the master/slave protocol if both master and slave support it. If this variable is disabled (the default), connections are uncompressed. See also Section 4.2.5, “Connection Compression Control”.

slave_exec_mode

Controls how a slave thread resolves conflicts and errors during replication. IDEMPOTENT mode causes suppression of duplicate-key and no-key-found errors; STRICT means no such suppression takes place.

IDEMPOTENT mode is intended for use in multi-master replication, circular replication, and some other special replication scenarios for NDB Cluster Replication. (See Section 18.6.10, “NDB Cluster Replication: Multi-Master and Circular Replication”, and Section 18.6.11, “NDB Cluster Replication Conflict Resolution”, for more information.) NDB Cluster ignores any value explicitly set for slave_exec_mode, and always treats it as IDEMPOTENT.

In MySQL Server 5.6, STRICT mode is the default value.

For storage engines other than NDB, IDEMPOTENT mode should be used only when you are absolutely sure that duplicate-key errors and key-not-found errors can safely be ignored. It is meant to be used in fail-over scenarios for NDB Cluster where multi-master replication or circular replication is employed, and is not recommended for use in other cases.

slave_load_tmpdir

The name of the directory where the slave creates temporary files. Setting this variable takes effect for all replication channels immediately, including running channels. The variable value is by default equal to the value of the tmpdir system variable, or the default that applies when that system variable is not specified.

When the slave SQL thread replicates a LOAD DATA statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. If the file loaded on the master is huge, the temporary files on the slave are huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some file system that has a lot of available space. In that case, the relay logs are huge as well, so you might also want to set the relay_log system variable to place the relay logs in that file system.

The directory specified by this option should be located in a disk-based file system (not a memory-based file system) so that the temporary files used to replicate LOAD DATA statements can survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process. However, replication can now continue after a restart if the temporary files have been removed.

slave_max_allowed_packet

This variable sets the maximum packet size for the slave SQL and I/O threads, so that large updates using row-based replication do not cause replication to fail because an update exceeded max_allowed_packet.

This global variable always has a value that is a positive integer multiple of 1024; if you set it to some value that is not, the value is rounded down to the next highest multiple of 1024 for it is stored or used; setting slave_max_allowed_packet to 0 causes 1024 to be used. (A truncation warning is issued in all such cases.) The default and maximum value is 1073741824 (1 GB); the minimum is 1024.

slave_net_timeout

The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

slave_parallel_workers

Sets the number of slave worker threads for executing replication events (transactions) in parallel. Setting this variable to 0 (the default) disables parallel execution. The maximum is 1024.

When parallel execution is enabled, the slave SQL thread acts as the coordinator for the slave worker threads, among which transactions are distributed on a per-database basis. This means that a worker thread on the slave can process successive transactions on a given database without waiting for updates to other databases to complete. The current implementation of multithreading on the slave assumes that the data is partitioned per database, and that updates within a given database occur in the same relative order as they do on the master, in order to work correctly. However, transactions do not need to be coordinated between any two databases.

Due to the fact that transactions on different databases can occur in a different order on the slave than on the master, checking for the most recently executed transaction does not guarantee that all previous transactions from the master have been executed on the slave. This has implications for logging and recovery when using a multithreaded slave. For information about how to interpret binary logging information when using multithreading on the slave, see Section 13.7.5.35, “SHOW SLAVE STATUS Statement”. In addition, this means that START SLAVE UNTIL is not supported with a multithreaded slave.

When multithreading is enabled, slave_transaction_retries is treated as equal to 0, and cannot be changed. (Currently, retrying of transactions is not supported with multithreaded slaves.)

You should also note that enforcing foreign key relationships between tables in different databases causes multithreaded slaves to use sequential rather than parallel mode, which can have a negative impact on performance. (Bug #14092635)

slave_pending_jobs_size_max

For multithreaded slaves, this variable sets the maximum amount of memory (in bytes) available to slave worker queues holding events not yet applied. Setting this variable has no effect on slaves for which multithreading is not enabled.

The minimum possible value for this variable is 1024; the default is 16MB. The maximum possible value is 18446744073709551615 (16 exabytes). Values that are not exact multiples of 1024 are rounded down to the next-highest multiple of 1024 prior to being stored.

The value of this variable is a soft limit and can be set to match the normal workload. If an unusually large event exceeds this size, the transaction is held until all the slave workers have empty queues, and then processed. All subsequent transactions are held until the large transaction has been completed.

slave_rows_search_algorithms

When preparing batches of rows for row-based logging and replication, this variable controls how the rows are searched for matches—that is, whether or not hashing is used for searches using a primary or unique key, some other key, or using no key at all. Setting this variable takes effect for all replication channels immediately, including running channels. option.

Specify a comma-separated list of any 2 (or all 3) values from the list INDEX_SCAN, TABLE_SCAN, HASH_SCAN. The value is expected as a string, so if set at runtime rather than at server startup, the value must be quoted. In addition, the value must not contain any spaces. Possible combinations (lists) and their effects are shown in the following table:

The order in which the algorithms are specified in the list makes no difference to the order in which they are displayed by a SELECT or SHOW VARIABLES statement (which is the same as that used in the table just shown previously).

It is possible to specify a single value, but this is not optimal, because setting a single value limits searches to using only that algorithm. In particular, setting INDEX_SCAN alone is not recommended, as in that case searches are unable to find rows at all if no index is present.

slave_skip_errors

Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This variable tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the variable value.

slave_sql_verify_checksum

Cause the slave SQL thread to verify data using the checksums read from the relay log. In the event of a mismatch, the slave stops with an error.

slave_transaction_retries

If a replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or because the transaction's execution time exceeded InnoDB's innodb_lock_wait_timeout or NDB's TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout, it automatically retries slave_transaction_retries times before stopping with an error. The default value is 10.

Transactions cannot be retried when using a multithreaded slave. In other words, whenever slave_parallel_workers is greater than 0, slave_transaction_retries is treated as equal to 0, and cannot be changed.

slave_type_conversions

Controls the type conversion mode in effect on the slave when using row-based replication. In MySQL 5.6.13 and higher, its value is a comma-delimited set of zero or more elements from the list: ALL_LOSSY, ALL_NON_LOSSY, ALL_SIGNED, ALL_UNSIGNED. Set this variable to an empty string to disallow type conversions between the master and the slave. Changes require a restart of the slave to take effect.

ALL_SIGNED and ALL_UNSIGNED were added in MySQL 5.6.13 (Bug#15831300). For additional information on type conversion modes applicable to attribute promotion and demotion in row-based replication, see Row-based replication: attribute promotion and demotion.

sql_slave_skip_counter

The number of events from the master that a slave server should skip.

This option is incompatible with GTID-based replication, and must not be set to a nonzero value when gtid_mode=ON. In MySQL 5.6.10 and higher, trying to do so is specifically disallowed. (Bug #15833516) If you need to skip transactions when employing GTIDs, use gtid_executed from the master instead. See Injecting empty transactions, for information about how to do this.

sync_master_info

The effects of this variable on a replication slave depend on whether the slave's master_info_repository is set to FILE or TABLE, as explained in the following paragraphs.

master_info_repository = FILE.  If the value of sync_master_info is greater than 0, the slave synchronizes its master.info file to disk (using fdatasync()) after every sync_master_info events. If it is 0, the MySQL server performs no synchronization of the master.info file to disk; instead, the server relies on the operating system to flush its contents periodically as with any other file.

master_info_repository = TABLE.  If the value of sync_master_info is greater than 0, the slave updates its master info repository table after every sync_master_info events. If it is 0, the table is never updated.

sync_relay_log

If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk (using fdatasync()) after every sync_relay_log events are written to the relay log.

Setting sync_relay_log to 0 causes no synchronization to be done to disk; in this case, the server relies on the operating system to flush the relay log's contents from time 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 event from the relay log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

sync_relay_log_info

The default value for sync_relay_log_info is 10000.

The effects of this variable on the replication slave depend on the server's relay_log_info_repository setting (FILE or TABLE). If the setting is TABLE, the effects of the variable also depend on whether the storage engine used by the relay log info table is transactional (such as InnoDB) or not transactional (MyISAM). The effects of these factors on the behavior of the server for sync_relay_log_info values of zero and greater than zero are as follows:

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

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. See Section 17.1.2, “Replication Formats”.

--log-bin[=base_name]

Enables binary logging. With binary logging enabled, the server logs all statements that change data to the binary log, which is used for backup and replication. The binary log is a sequence of files with a base name and numeric extension. For information on the format and management of the binary log, see Section 5.4.4, “The Binary Log”.

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

If you supply a value for the --log-bin option, the value is used as the base name for the log sequence. The server creates binary log files in sequence by adding a numeric suffix to the base name. In MySQL 5.6, the default base name is the name of the process ID file, with the suffix -bin. That name can be set with the --pid-file option, and it defaults to the name of the host machine. It is recommended that you specify a base name using the --log-bin option, so that you can continue to use the same binary log file names regardless of changes to the default name.

The default location for binary log files is the data directory. You can use the --log-bin option to specify an alternative location, by adding a leading absolute path name to the base name to specify a different directory. When the server reads an entry from the binary log index file, which tracks the binary log files that have been used, it checks whether the entry contains a relative path. If it does, the relative part of the path is replaced with the absolute path set using the --log-bin option. An absolute path recorded in the binary log index file remains unchanged; in such a case, the index file must be edited manually to enable a new path or paths to be used. (In older versions of MySQL, 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 base name. The binary log file base name and any specified path are available as the log_bin_basename system variable.

--log-bin-index[=file_name]

The name for the binary log index file, which contains the names of the binary log files. By default, it has the same location and base name as the value specified for the binary log files using the --log-bin option, plus the extension .index. If you do not specify --log-bin, the default binary log index file name is binlog.index. If you omit the file name and do not specify one with --log-bin, the default binary log index file name is host_name-bin.index, using the name of the host machine.

For information on the format and management of the binary log, see Section 5.4.4, “The Binary Log”.

--binlog-do-db=db_name

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.

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

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.

Prior to MySQL 5.6.12, this option caused any statements containing fully qualified table names not to be logged if there was no default database specified (that is, when SELECT DATABASE() returned NULL). In MySQL 5.6.12 and higher, when there is no default database, no --binlog-ignore-db options are applied, and such statements are always logged. (Bug #11829838, Bug #60188)

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.

--binlog-checksum={NONE|CRC32}

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

--max-binlog-dump-events=N