16.1.3.1 Replication and Binary Logging Option and Variable Reference

The following tables list basic information about the MySQL command-line options and system variables applicable to replication and the binary log.

Table 16.1 Summary of Replication options and variables in MySQL 5.1

Option or Variable Name
Command Line System Variable Status Variable
Option File Scope Dynamic
Notes

Com_change_master

No No Yes
No Both No

DESCRIPTION: Count of CHANGE MASTER statements

Com_show_master_status

No No Yes
No Both No

DESCRIPTION: Count of SHOW MASTER STATUS statements

Com_show_new_master

No No Yes
No Both No

DESCRIPTION: Count of SHOW NEW MASTER statements

Com_show_slave_hosts

No No Yes
No Both No

DESCRIPTION: Count of SHOW SLAVE HOSTS statements

Com_show_slave_status

No No Yes
No Both No

DESCRIPTION: Count of SHOW SLAVE STATUS statements

Com_slave_start

No No Yes
No Both No

DESCRIPTION: Count of START SLAVE statements

Com_slave_stop

No No Yes
No Both No

DESCRIPTION: Count of STOP SLAVE statements

Rpl_status

No No Yes
No Global No

DESCRIPTION: The status of fail-safe replication (not implemented)

slave_exec_mode

Yes Yes No
Yes Global Yes

DESCRIPTION: Allows for switching between idempotent mode (key and some other errors suppressed) and strict mode; strict mode is the default, except for MySQL Cluster, where idempotent is always used

Slave_open_temp_tables

No No Yes
No Global No

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

Slave_retried_transactions

No No Yes
No Global No

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

Slave_running

No No Yes
No Global No

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

abort-slave-event-count

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

disconnect-slave-event-count

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

init_slave

Yes Yes No
Yes Global Yes

DESCRIPTION: Statements that are executed when a slave connects to a master

log-slave-updates

Yes Yes No
Yes Global No

DESCRIPTION: This option tells the slave to log the updates performed by its SQL thread to its own binary log

log_slave_updates

Yes Yes No
Yes Global No

DESCRIPTION: Tells whether the slave should log the updates performed by its SQL thread to its own binary log. Read-only; set using the --log-slave-updates server option.

master-connect-retry

Yes No No
Yes No

DESCRIPTION: Number of seconds the slave thread will sleep before retrying to connect to the master in case the master goes down or the connection is lost

master-host

Yes No No
Yes No

DESCRIPTION: Master host name or IP address for replication

master-info-file

Yes No No
Yes No

DESCRIPTION: 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-password

Yes No No
Yes No

DESCRIPTION: The password the slave thread will authenticate with when connecting to master

master-port

Yes No No
Yes No

DESCRIPTION: The port the master is listening on

master-retry-count

Yes No No
Yes No

DESCRIPTION: Number of tries the slave will make to connect to the master before giving up

master-ssl

Yes No No
Yes No

DESCRIPTION: Enable the slave to connect to the master using SSL

master-ssl-ca

Yes No No
Yes No

DESCRIPTION: Master SSL CA file; applies only if master-ssl is enabled

master-ssl-capath

Yes No No
Yes No

DESCRIPTION: Master SSL CA path; applies only if master-ssl is enabled

master-ssl-cert

Yes No No
Yes No

DESCRIPTION: Master SSL certificate file name; applies only if master-ssl is enabled

master-ssl-cipher

Yes No No
Yes No

DESCRIPTION: Master SSL cipher; applies only if master-ssl is enabled

master-ssl-key

Yes No No
Yes No

DESCRIPTION: Master SSL key file name; applies only if master-ssl is enabled

master-user

Yes No No
Yes No

DESCRIPTION: The user name the slave thread will use for authentication when connecting to master. The user must have FILE privilege. If the master user is not set, user test is assumed. The value in master.info will take precedence if it can be read

relay-log

Yes Yes No
Yes Global No

DESCRIPTION: The location and base name to use for relay logs

relay-log-index

Yes Yes No
Yes Global No

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

relay-log-info-file

Yes No No
Yes No

DESCRIPTION: The location and name of the file that remembers where the SQL replication thread is in the relay logs

relay_log_info_file

Yes Yes No
Yes Global No

DESCRIPTION: The name of the file in which the slave records information about the relay logs.

relay_log_index

Yes Yes No
Yes Global No

DESCRIPTION: The name of the relay log index file.

relay_log_purge

Yes Yes No
Yes Global Yes

DESCRIPTION: Determines whether relay logs are purged

relay_log_space_limit

Yes Yes No
Yes Global No

DESCRIPTION: Maximum space to use for all relay logs

replicate-do-db

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread to restrict replication to the specified database

replicate-do-table

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread to restrict replication to the specified table

replicate-ignore-db

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread not to replicate to the specified database

replicate-ignore-table

Yes No No
Yes No

DESCRIPTION: Tells the slave SQL thread not to replicate to the specified table

replicate-rewrite-db

Yes No No
Yes No

DESCRIPTION: Updates to a database with a different name than the original

replicate-same-server-id

Yes No No
Yes No

DESCRIPTION: In replication, if set to 1, do not skip events having our server id

replicate-wild-do-table

Yes No No
Yes No

DESCRIPTION: Tells the slave thread to restrict replication to the tables that match the specified wildcard pattern

replicate-wild-ignore-table

Yes No No
Yes No

DESCRIPTION: Tells the slave thread not to replicate to the tables that match the given wildcard pattern

report-host

Yes Yes No
Yes Global No

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

report-password

Yes Yes No
Yes Global No

DESCRIPTION: 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

Yes Yes No
Yes Global No

DESCRIPTION: Port for connecting to slave reported to the master during slave registration

report-user

Yes Yes No
Yes Global No

DESCRIPTION: 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_recovery_rank

No Yes No
No Global Yes

DESCRIPTION: Not used; removed in later versions

show-slave-auth-info

Yes No No
Yes No

DESCRIPTION: Show user name and password in SHOW SLAVE HOSTS on this master

skip-slave-start

Yes No No
Yes No

DESCRIPTION: If set, slave is not autostarted

slave-load-tmpdir

Yes Yes No
Yes Global No

DESCRIPTION: The location where the slave should put its temporary files when replicating a LOAD DATA INFILE statement

slave-skip-errors

Yes Yes No
Yes Global No

DESCRIPTION: Tells the slave thread to continue replication when a query returns an error from the provided list

slave_compressed_protocol

Yes Yes No
Yes Global Yes

DESCRIPTION: Use compression on master/slave protocol

slave-max-allowed-packet

Yes No No
Yes No

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

slave_max_allowed_packet

No Yes No
No Global Yes

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

slave_net_timeout

Yes Yes No
Yes Global Yes

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

slave_transaction_retries

Yes Yes No
Yes Global Yes

DESCRIPTION: 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

Yes Yes No
Yes Global No

DESCRIPTION: 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_slave_skip_counter

No Yes No
No Global Yes

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

sync_binlog

Yes Yes No
Yes Global Yes

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

have_row_based_replication

No Yes No
No Global No

DESCRIPTION: Shows whether row-based replication is supported


Section 16.1.3.2, “Replication Master Options and Variables”, provides more detailed information about options and variables relating to replication master servers. For more information about options and variables relating to replication slaves, see Section 16.1.3.3, “Replication Slave Options and Variables”.

Table 16.2 Summary of Binary Logging options and variables in MySQL 5.1

Option or Variable Name
Command Line System Variable Status Variable
Option File Scope Dynamic
Notes

Binlog_cache_disk_use

No No Yes
No Global No

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

Binlog_cache_use

No No Yes
No Global No

DESCRIPTION: Number of transactions that used the temporary binary log cache

Com_show_binlog_events

No No Yes
No Both No

DESCRIPTION: Count of SHOW BINLOG EVENTS statements

Com_show_binlogs

No No Yes
No Both No

DESCRIPTION: Count of SHOW BINLOGS statements

binlog-do-db

Yes No No
Yes No

DESCRIPTION: Limits binary logging to specific databases

binlog-ignore-db

Yes No No
Yes No

DESCRIPTION: Tells the master that updates to the given database should not be logged to the binary log

binlog-row-event-max-size

Yes No No
Yes No

DESCRIPTION: Binary log max event size

binlog_cache_size

Yes Yes No
Yes Global Yes

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

binlog_format

Yes Yes No
Yes Both Yes

DESCRIPTION: Specifies the format of the binary log

binlog_direct_non_transactional_updates

Yes Yes No
Yes Both Yes

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

log-bin-use-v1-row-events

Yes Yes No
Yes Global No

DESCRIPTION: Use version 1 binary log row events

log_bin_use_v1_row_events

Yes Yes No
Yes Global No

DESCRIPTION: Shows whether server is using version 1 binary log row events

max-binlog-dump-events

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication

max_binlog_cache_size

Yes Yes No
Yes Global Yes

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

max_binlog_size

Yes Yes No
Yes Global Yes

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

sporadic-binlog-dump-fail

Yes No No
Yes No

DESCRIPTION: Option used by mysql-test for debugging and testing of replication


Section 16.1.3.4, “Binary Log Options and Variables”, provides more detailed information about options and variables relating to binary logging. For additional general information about the binary log, see Section 5.2.4, “The Binary Log”.

For information about the sql_log_bin and sql_log_off variables, see Section 5.1.4, “Server System Variables”.

For a table showing all command-line options, system and status variables used with mysqld, see Section 5.1.1, “Server Option and Variable Reference”.