Chapter 14 Replication

The relay log consists of the events read from the binary log of the master and written by the slave I/O thread. Events in the relay log are executed on the slave as part of the SQL thread.

The master.info file contains the status and current configuration information for the slave's connectivity to the master. The file holds information on the master host name, login credentials, and coordinates indicating how far the slave has read from the master's binary log.

The relay-log.info file holds the status information about the execution point within the slave's relay log.

Each time the I/O thread starts.

When the logs are flushed; for example, with FLUSH LOGS or mysqladmin flush-logs. (This creates a new relay log only as of MySQL 4.0.14.)

When the size of the current relay log file becomes “too large,” determined as follows:

INSERT DELAYED ... VALUES(LAST_INSERT_ID()) inserts a different value on the master and the slave. (Bug #20819)

Adding an AUTO_INCREMENT column to a table with ALTER TABLE might not produce the same ordering of the rows on the slave and the master. This occurs because the order in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows must be ordered before assigning an AUTO_INCREMENT number. Assuming that you want to add an AUTO_INCREMENT column to the table t1 that has columns col1 and col2, the following statements produce a new table t2 identical to t1 but with an AUTO_INCREMENT column:

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

The instructions just given are subject to the limitations of CREATE TABLE ... LIKE: Foreign key definitions are ignored, as are the DATA DIRECTORY and INDEX DIRECTORY table options. If a table definition includes any of those characteristics, create t2 using a CREATE TABLE statement that is identical to the one used to create t1, but with the addition of the AUTO_INCREMENT column.

Regardless of the method used to create and populate the copy having the AUTO_INCREMENT column, the final step is to drop the original table and then rename the copy:

DROP t1;
ALTER TABLE t2 RENAME t1;

See also Section B.5.7.1, “Problems with ALTER TABLE”.

You must always use the same global character set and collation on the master and the slave. (These are controlled by the --character-set-server and --collation-server options.) Otherwise, you may get duplicate-key errors on the slave, because a key that is unique in the master character set might not be unique in the slave character set.

If the master is older than MySQL 4.1.3, the character set of any client should never be made different from its global value because this character set change is not known to the slave. In other words, clients should not use SET NAMES, SET CHARACTER SET, and so forth. If both the master and the slave are 4.1.3 or newer, clients can freely set session values for character set variables because these settings are written to the binary log and so are known to the slave. That is, clients can use SET NAMES or SET CHARACTER SET or can set variables such as collation_client or collation_server. However, clients are prevented from changing the global value of these variables; as stated previously, the master and slave must always have identical global character set values.

If the master has databases with a character set different from the global character_set_server value, you should design your CREATE TABLE statements so that they do not implicitly rely on the database default character set, because there currently is a bug (Bug #2326). A good workaround is to state the character set and collation explicitly in CREATE TABLE statements.

The USER(), CURRENT_USER(), UUID(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave. This is also true for CONNECTION_ID() in slave versions older than 4.1.1. The new implementation of the PASSWORD() function in MySQL 4.1 is well replicated in masters from 4.1.1 and up, but your slaves also must be 4.1.1 or above to replicate it. If you have older slaves and need to replicate PASSWORD() from your 4.1.x master, you must start your master with the --old-passwords option, so that it uses the old implementation of PASSWORD().

The PASSWORD() implementation in MySQL 4.1.0 differs from every other version of MySQL. Avoid using 4.1.0 in a replication scenario.

The GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK() functions that handle user-level locks are replicated without the slave knowing the concurrency context on the master. Therefore, these functions should not be used to insert into a master table because the content on the slave would differ. For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)).

The FOUND_ROWS() function is not replicated reliably. A workaround is to store the result of the function call in a user variable, and then use that in the INSERT statement. For example, if you wish to store the result in a table named mytable, you might normally do so like this:

SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
INSERT INTO mytable VALUES( FOUND_ROWS() );

However, if you are replicating mytable, then you should use SELECT INTO, and then store the variable in the table, like this:

SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;
INSERT INTO mytable VALUES(@found_rows);

In this way, the user variable is replicated as part of the context, and applied on the slave correctly.

In 3.23, RAND() in updates does not replicate properly. Use RAND(determinstic_expression) if you are replicating updates with RAND(). You can, for example, use UNIX_TIMESTAMP() as the argument to RAND().

LOAD DATA INFILE is handled properly, as long as the data file still resides on the master server at the time of update propagation.

LOAD DATA LOCAL INFILE is no longer skipped on the slave as it was in 3.23.

For transactions, the slave commits and then updates relay-log.info. If a crash occurs between these two operations, relay log processing will have proceeded further than the information file indicates and the slave will re-execute the events from the last transaction in the relay log after it has been restarted.

A similar problem can occur if the slave updates relay-log.info but the server host crashes before the write has been flushed to disk. Writes are not forced to disk because the server relies on the operating system to flush the file from time to time.

Use one or more ALTER TABLE statements on the master to change the names of any database objects where these names would be considered reserved words on the slave, and change any SQL statements that use the old names to use the new names instead.

In any SQL statements using these database object names, write the names as quoted identifiers using backtick characters (`).

There are problems if the slave is stopped in the middle of a BEGIN/COMMIT block because the slave restarts at the beginning of the BEGIN block.

When the storage engine type of the slave is nontransactional, transactions on the master that mix updates of transactional and nontransactional tables should be avoided because they can cause inconsistency of the data between the master transactional table and the slave nontransactional table. That is, such transactions can lead to master storage engine-specific behavior with the possible effect of replication going out of synchrony. MySQL does not issue a warning about this currently, so extra care should be taken when replicating transactional tables from the master to nontransactional ones on the slaves.

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

--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-slave-updates

Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:

A -> B -> C

Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

--log-warnings[=level]

This option causes a server to print 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/connection failure, and informs you as to how each slave thread started. This option is enabled (1) by default as of MySQL 4.0.19 and 4.1.2; to disable it, use --log-warnings=0. As of MySQL 4.0.21 and 4.1.3, aborted connections are not logged to the error log unless the value is greater than 1.

Note that the effects of this option are not limited to replication. It produces warnings across a spectrum of server activities.

--master-connect-retry=seconds

The number of seconds that the slave thread sleeps before trying to reconnect to the master in case the master goes down or the connection is lost. The value in the master.info file takes precedence if it can be read. If not set, the default is 60. Connection retries are not invoked until the slave times out reading data from the master according to the value of --slave-net-timeout. The number of reconnection attempts is limited by the --master-retry-count option.

--master-host=host_name

The host name or IP address of the master replication server. The value in master.info takes precedence if it can be read. If no master host is specified, the slave thread does not start.

--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 14.3.3, “The Slave Status Files”.

--master-password=password

The password of the account that the slave thread uses for authentication when it connects to the master. The value in the master.info file takes precedence if it can be read. If not set, an empty password is assumed.

--master-port=port_number

The TCP/IP port number that the master is listening on. The value in the master.info file takes precedence if it can be read. If not set, the compiled-in setting is assumed (normally 3306).

--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 CHANGE MASTER TO statement or --master-connect-retry option and reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400.

--master-ssl, --master-ssl-ca=file_name, --master-ssl-capath=directory_name, --master-ssl-cert=file_name, --master-ssl-cipher=cipher_list, --master-ssl-key=file_name

These options are used for setting up a secure replication connection to the master server using SSL. Their meanings are the same as the corresponding --ssl, --ssl-ca, --ssl-capath, --ssl-cert, --ssl-cipher, --ssl-key options that are described in Section 5.6.6.3, “SSL Command Options”. The values in the master.info file take precedence if they can be read.

These options are operational as of MySQL 4.1.1.

--master-user=user_name

The user name of the account that the slave thread uses for authentication when it connects to the master. This account must have the REPLICATION SLAVE privilege. FILE privilege instead.) The value in the master.info file takes precedence if it can be read. If the master user name is not set, the name test is assumed.

--read-only

Cause the slave to permit no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to TEMPORARY tables.

This option is available as of MySQL 4.0.14.

--relay-log=file_name

The basename for the relay log. The default basename is host_name-relay-bin. The server writes the file in the data directory unless the basename 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 basename.

Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log option 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.3, “Specifying Program Options”.

If you specify this option, the value specified is also used as the basename for the relay log index file. You can override this behavior by specifying a different relay log index file basename using the --relay-log-index option.

You may find the --relay-log option useful in performing the following tasks:

--relay-log-index=file_name

The name to use 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 option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log-index option 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.3, “Specifying Program Options”.

If you specify this option, the value specified is also used as the basename for the relay logs. You can override this behavior by specifying a different relay log file basename using the --relay-log option.

--relay-log-info-file=file_name

The name to use for 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 14.3.3, “The Slave Status Files”.

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

This option is available as of MySQL 4.1.1.

--max-relay-log-size=size

The size at which the server rotates relay log files automatically. For more information, see Section 14.3.1, “Replication Relay and Status Files”. Default is 1GB.

This option is available as of MySQL 4.0.14.

--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 (which is what happens before MySQL 4.0.13). 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

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. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database.

An example of what does not work as you might expect: 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 or multiple-table UPDATE statements that go across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

If you need cross-database updates to work, make sure that you have MySQL 3.23.28 or later, and use --replicate-wild-do-table=db_name.% instead. See Section 14.9, “How Servers Evaluate Replication Filtering Rules”.

--replicate-ignore-db=db_name

Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name. To specify more than one database to ignore, use this option multiple times, once for each 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 14.9, “How Servers Evaluate Replication Filtering Rules”.

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

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

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

--replicate-do-table=db_name.tbl_name

Tells the slave SQL thread to restrict replication to the specified 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 14.9, “How Servers Evaluate Replication Filtering Rules”.

--replicate-ignore-table=db_name.tbl_name

Tells 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 14.9, “How Servers Evaluate Replication Filtering Rules”.

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

Tells the slave to translate 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. This does not work for cross-database updates. 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.

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 used. Be careful that starting from MySQL 4.1, by default the slave I/O thread does not even write binary log events to the relay log if they have the slave's server id (this optimization helps save disk usage compared to 4.0). So if you want to use --replicate-same-server-id in 4.1 versions, 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

Tells 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 14.9, “How Servers Evaluate Replication Filtering Rules”.

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

Tells the slave thread not to replicate a statement where 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 14.9, “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.

--report-host=host_name

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. Note that it is not sufficient for the master to simply read the IP address of the slave from the TCP/IP socket after the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts.

This option is available as of MySQL 4.0.0.

--report-password=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 --show-slave-auth-info option is given.

--report-port=slave_port_num

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.

This option is available as of MySQL 4.0.0.

--report-user=user_name

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 --show-slave-auth-info option is given.

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

--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_compressed_protocol={0|1}

If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).

--slave-load-tmpdir=file_name

The name of the directory where the slave creates temporary files. This option is by default equal to the value of the tmpdir system variable. When the slave SQL thread replicates a LOAD DATA INFILE 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 use the --relay-log option 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) because the temporary files used to replicate LOAD DATA INFILE must survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process.

--slave-net-timeout=seconds

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 CHANGE MASTER TO statement or --master-connect-retry option and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

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

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.

Examples:

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

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.

This variable was added in MySQL 4.1.2.

rpl_recovery_rank

This variable is unused.

slave_compressed_protocol

Whether to use compression of the master/slave protocol if both the slave and the master support it. This variable was added in MySQL 4.0.3.

slave_load_tmpdir

The name of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements. This variable was added in MySQL 4.0.0.

slave_net_timeout

The number of seconds to wait for more data from a master/slave connection before aborting the read. This timeout applies only to TCP/IP connections, not to connections made using Unix socket files, named pipes, or shared memory. This variable was added in MySQL 3.23.40.

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. This variable was added in MySQL 3.23.47.

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 NDBCLUSTER's TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout, it automatically retries slave_transaction_retries times before stopping with an error. The default in MySQL 4.1 is 0. You must explicitly set the value to greater than 0 to enable the “retry” behavior, which is often desirable.

sql_slave_skip_counter

The number of events from the master that a slave server should skip. This variable was added in MySQL 3.23.33.

--log-bin[=base_name]

Enable binary logging. The server logs all statements that change data to the binary log, which is used for backup and replication. See Section 5.3.4, “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 Section B.5.8.4, “Open Issues in MySQL”, for the reason). Otherwise, MySQL uses host_name-bin as the basename.

--log-bin-index[=file_name]

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

--binlog-do-db=db_name

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

Tell the server to restrict binary logging to updates for which the default database is db_name (that is, the database selected by USE). All other databases that are not explicitly mentioned are ignored. If you use this option, you should ensure that you do updates only in the default database.

There is an exception to this for CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.

An example of what does not work as you might expect: 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.

--binlog-ignore-db=db_name

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

Tell the server to suppress binary logging of updates for which the default database is db_name (that is, the database selected by USE). If you use this option, you should ensure that you do updates only in the default database.

As with the --binlog-do-db option, there is an exception for the CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements. The server uses the database named in the statement (not the default database) to decide whether it should log the statement.

An example of what does not work as you might expect: If the server is started with binlog-ignore-db=sales, and you run USE prices; UPDATE sales.january SET amount = amount + 1000;, this statement is written into the binary log.

--max-binlog-dump-events=N

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

--sporadic-binlog-dump-fail

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

log_bin

Whether the binary log is enabled. If the --log-bin option is used, then the value of this variable is ON; otherwise it is OFF. This variable reports only on the status of binary logging (enabled or disabled); it does not actually report the value to which --log-bin is set.

See Section 5.3.4, “The Binary Log”.

log_slave_updates

Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect. This variable was added in MySQL 3.23.17. See Section 14.8.3, “Replication Slave Options and Variables”.

max_binlog_cache_size

If a multiple-statement 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 16 PB (petabytes) on 64-bit platforms. This variable was added in MySQL 3.23.29.

max_binlog_size

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.

sync_binlog

If the value of this variable is greater than 0, the MySQL 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. 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). This variable was added in MySQL 4.1.3.

If the value of sync_binlog is 0 (the default), no extra flushing is done. The server relies on the operating system to flush the file contents occasionally as for any other file.