Chapter 16. Replication

If you haven't already locked the tables on the server to prevent statements that update data from executing:

Start the command-line client and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

mysql> FLUSH TABLES WITH READ LOCK;

Remember to use SHOW MASTER STATUS and record the binary log details for use when starting up the slave. The point in time of your snapshot and the binary log position must match. See Section 16.1.1.4, “Obtaining the Master Replication Information”.

In another session, use mysqldump to create a dump either of all the databases you want to replicate, or of selected individual databases. For example:

shell> mysqldump --all-databases --lock-all-tables >dbdump.db

An alternative to using a bare dump, is to use the --master-data option, which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process.

shell> mysqldump --all-databases --master-data >dbdump.db

In the client where you acquired the read lock, release the lock:

mysql> UNLOCK TABLES;

Files relating to the mysql database.

The master.info file.

The master's binary log files.

Any relay log files.

Proven technology that has existed in MySQL since 3.23.

Less data written to log files. When updates or deletes affect many rows, this results in much less storage space required for log files. This also means that taking and restoring from backups can be accomplished more quickly.

Log files contain all statements that made any changes, so they can be used to audit the database.

Tables replicated using statement-based replication are not required to have explicit primary keys.

Statements which are unsafe for SBR.  Not all statements which modify data (such as INSERT DELETE, UPDATE, and REPLACE statements) can be replicated using statement-based replication. Any nondeterministic behavior is difficult to replicate when using statement-based replication. Examples of such DML (Data Modification Language) statements include the following:

When using statement-based replication, statements that cannot be replicated correctly using statement-based mode are logged with a warning like the one shown here:

090213 16:58:54 [Warning] Statement is not safe to log in statement format.

A similar warning is also issued to the client in such cases.

INSERT ... SELECT requires a greater number of row-level locks than with row-based replication.

UPDATE statements that require a table scan (because no index is used in the WHERE clause) must lock a greater number of rows than with row-based replication.

For InnoDB: An INSERT statement that uses AUTO_INCREMENT blocks other nonconflicting INSERT statements.

For complex statements, the statement must be evaluated and executed on the slave before the rows are updated or inserted. With row-based replication, the slave only has to run the statement to apply the differences, not the full statement.

Stored functions execute with the same NOW() value as the calling statement. However, this is not true of stored procedures.

Deterministic UDFs must be applied on the slaves.

If there is an error in evaluation on the slave, particularly when executing complex statements, then using statement-based replication may slowly increase the margin of error across the affected rows over time. See Section 16.3.1.20, “Slave Errors during Replication”.

Tables must be (nearly) identical on master and slave. See Section 16.3.1.4, “Replication with Differing Tables on Master and Slave”, for more information.

All changes can be replicated. This is the safest form of replication.

The mysql database is not replicated. The mysql database is instead seen as a node-specific database. Row-based replication is not supported on tables in this database. Instead, statements that would normally update this information — such as GRANT, REVOKE and the manipulation of triggers, stored routines (including stored procedures), and views — are all replicated to slaves using statement-based replication.

For statements like CREATE ... SELECT, a CREATE statement is generated from the table definition and replicated using the statement-based format, while the row insertions are replicated using the row-based format.

The technology is the same as in most other database management systems; knowledge about other systems transfers to MySQL.

Fewer locks are needed (and thus higher concurrency) on the master for the following types of statements:

Fewer locks are required on the slave for any INSERT, UPDATE, or DELETE statement.

RBR tends to generate more data that must be logged. This is because, when using row-based replication to replicate a DML statement (such as an UPDATE or DELETE statement), each changed row must be written to the binary log. (When using statement-based replication, only the statement is written to the binary log.) This means that, if the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that taking and restoring from backup can require more time. In addition, the binary log is locked for a longer time to write the data, which may cause concurrency problems.

All tables replicated using row-based replication must have explicit primary keys.

Deterministic UDFs that generate large BLOB values take longer to replicate with row-based replication than with statement-based replication. This is because, when using row-based replication, the BLOB column data is itself logged, rather than the statement generating the data.

You cannot examine the logs to see what statements were executed, nor can you see on the slave what statements were received from the master and executed.

However, you can see what data was changed using mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose.

When performing a bulk operation that includes nontransactional storage engines, changes are applied as the statement executes. With row-based replication logging, this means that the binary log is written while the statement is running. On the master, this does not cause problems with concurrency, because tables are locked until the bulk operation terminates. On the slave server, however, tables are not locked while the slave applies changes, because the slave does not know that those changes are part of a bulk operation.

In such cases, if you retrieve data from a table on the master (for example, using SELECT * FROM table_name), the server waits for the bulk operation to complete before executing the SELECT statement, because the table is read-locked. On the slave, the server does not wait (because there is no lock). This means that, until the bulk operation on the slave has completed, you obtain different results for the same SELECT query on the master and on the slave.

This behavior is expected to change in a future MySQL release; however, until it changes, you may prefer to use statement-based replication when your application requires concurrent large bulk inserts and selects.

RBR and primary keys.  Currently, row-based replication may fail if any table to be replicated does not have an explicit primary key. This is a known issue which we are working to fix in a future MySQL release.

RBL, RBR, and temporary tables.  As noted elsewhere in this chapter (see Section 16.3.1.22, “Replication and Temporary Tables”), temporary tables are not replicated when using the row-based format. However, you can use the mixed format; when mixed format is in effect, “safe” statements involving temporary tables are logged using the statement-based format. For more information, see Section 16.1.2.1, “Comparison of Statement-Based and Row-Based Replication”.

RBL and synchronization of nontransactional tables.  When using row-based replication of a MyISAM or other nontransactional table, changed rows are written to the transaction cache. Often, when many rows are affected, the set of changes are split into several events; when the statement commits, all of these events are written to the binary log. When executing on the slave, a table lock is taken on all tables involved, then the rows are applied in batch mode. (This may or may not be effective, depending on the engine used for the slave's copy of the table).

Latency and binary log size.  Because RBL writes changes for each row to the binary log, the size of the binary log can grow quite rapidly. When used in a replication environment, this can significantly increase the time required for making the changes on the slave that match those on the master. You should be aware of the potential for this delay in your applications.

Reading the binary log.  With the --base64-output=DECODE-ROWS and --verbose options, mysqlbinlog is able to format the contents of the binary log in a manner that is easily human-readable, in case you want to read or recover from a replication or database failure using the contents of the binary log. For more information, see Section 4.6.7.2, “mysqlbinlog Row Event Display”.

Binary log execution errors and slave_exec_mode.  If you use slave_exec_mode=IDEMPOTENT, a failure to apply changes from RBL because the original row cannot be found does not trigger an error, and does not cause replication to fail. This means that it is possible that updates are not applied on the slave, so that the master and slave are no longer synchronized. Latency issues and use of nontransactional tables when using slave_exec_mode=IDEMPOTENT and RBR can cause the master and slave to diverge even further. For more information about slave_exec_mode, see Section 5.1.4, “Server System Variables”.

Lack of binary log checksums.  No checksums are used for RBL. This means that network, disk, and other errors may not be identified when processing the binary log. To ensure that data is transmitted without network corruption, you may want to consider using SSL, which adds another layer of checksumming, for replication connections. See Section 5.5.7, “Using SSL for Secure Connections”, for more information about setting up MySQL with SSL.

Filtering based on server ID not supported.  A common practice is to filter out changes on some slaves by using a WHERE clause that includes the relation @server_id <> server-id clause with UPDATE and DELETE statements, a simple example of such a clause being WHERE @server_id <> 1. However, this does not work correctly with row-based logging. If you must use the server_id system variable for statement filtering, then you must also use --binlog_format=STATEMENT.

Database-level replication options.  The effects of the options --replicate-do-db, --replicate-ignore-db, and --replicate-rewrite-db differ considerably depending on whether row-based or statement-based logging is in use. Because of this, it is recommended to avoid the database-level options and use the table-level options such as --replicate-do-table and --replicate-ignore-table instead. For more information about these options and the impact that your choice of replication format has on how they operate, see Section 16.1.3, “Replication and Binary Logging Options and Variables”.

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 gives rise to an error, and the actual value of the variable remains unchanged.

These two variables affect AUTO_INCREMENT column behavior as follows:

Should one or both of these variables be 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. In other words, the series is calculated like so:

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 greatest 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 confine the effects of these two variables to a single table, and thus they do not take the place of the sequences offered by some other database management systems; 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 16.3.1.1, “Replication and AUTO_INCREMENT”.

auto_increment_offset

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

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

When using MySQL Cluster Replication prior to MySQL Cluster NDB 6.2.16 and MySQL Cluster NDB 6.3.13, records for “empty” epochs — that is, epochs in which no changes to NDBCLUSTER data or tables took place — were inserted into the ndb_apply_status and ndb_binlog_index tables on the slave even when --log-slave-updates was disabled (Bug#37472). Beginning with MySQL Cluster NDB 6.3.21 and MySQL Cluster NDB 6.4.1, it is possible to re-enable the older behavior by using the --ndb-log-empty-epochs option.

--log-slow-slave-statements

When the slow query log is enabled, this option enables logging for queries that have taken more than long_query_time seconds to execute on the slave.

--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 by default; to disable it, use --skip-log-warnings. 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.

This option is deprecated, and removed as of MySQL 6.0.

--master-host=host_name

The host name or IP number 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.

This option is deprecated, and removed as of MySQL 6.0.

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

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

This option is deprecated, and removed as of MySQL 6.0.

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

This option is deprecated, and removed as of MySQL 6.0.

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

You can also set the retry count by using the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement.

--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.5.7.3, “SSL Command Options”. The values in the master.info file take precedence if they can be read.

These options are deprecated, and removed as of MySQL 6.0.

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

This option is deprecated, and removed as of MySQL 6.0.

--max-relay-log-size=size

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

--read-only

Cause the slave to allow 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.

--relay-log=file_name

The basename for the relay log. The default basename is host_name-relay-bin. 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.

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

--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 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 to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. For example, 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 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 current 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 16.4.3, “How Servers Evaluate Replication 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 to not replicate any statement where the default database (that is, the one selected by USE) is db_name.

Row-based replication.  Tells the slave not to update any tables in the database db_name. The current 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 16.4.3, “How Servers Evaluate Replication Rules”.

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

--replicate-do-table=db_name.tbl_name

Tells the slave 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 cross-database updates, in contrast to --replicate-do-db. See Section 16.4.3, “How Servers Evaluate Replication Rules”.

--replicate-ignore-table=db_name.tbl_name

Tells the slave thread to not 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 16.4.3, “How Servers Evaluate Replication 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. 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

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 16.4.3, “How Servers Evaluate Replication 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're using 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 16.4.3, “How Servers Evaluate Replication 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 number 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 number 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.

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

--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 MASTER_CONNECT_RETRY option for 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

sql_slave_skip_counter

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

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

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.

rpl_recovery_rank

This variable is unused.

slave_compressed_protocol

Whether to use compression of the slave/master protocol if both the slave and the master support it.

slave_exec_mode

Controls whether IDEMPOTENT or STRICT mode is used in replication conflict resolution and error checking. IDEMPOTENT mode causes suppression of duplicate-key and no-key-found errors. This mode should be employed in multi-master replication, circular replication, and some other special replication scenarios. STRICT mode is the default, and is suitable for most other cases.

slave_load_tmpdir

The name of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements.

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 via Unix socket files, named pipes, or shared memory.

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.

sql_slave_skip_counter

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

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 value is 10.

--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. The default is 1024. See Section 16.1.2, “Replication Formats”.

--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.2.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 Additional Known Issues, 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.2.4, “The Binary Log”. If you omit the file name, and if you didn't specify one with --log-bin, MySQL uses host_name-bin.index as the file name.

Statement selection options.  The options in the following list affect which statements are written to the binary log, and thus sent by a replication master server to its slaves.

Additional server options that can be used to control logging also affect the binary log. For more information about these, see Section 5.1.2, “Server Command Options”. For more information about how the options in the previous list are applied, see Section 5.2.4, “The Binary Log”.

There are also options for slave servers that control which statements received from the master should be executed or ignored. For details, see Section 16.1.3.3, “Replication Slave Options and Variables”.

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

With no argument or an argument of 1, this option sets the log_bin_trust_function_creators system variable to 1. With an argument of 0, this option sets the system variable to 0. log_bin_trust_function_creators affects how MySQL enforces restrictions on stored function and trigger creation. See Section 18.6, “Binary Logging of Stored Programs”.

--max-binlog-dump-events

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.

binlog_cache_size

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

MySQL Enterprise For recommendations on the optimum setting for binlog_cache_size subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

binlog_format

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

You must have the SUPER privilege to set this variable, which (unlike with most system variables) is true even for the session value. See Section 16.1.2, “Replication Formats”.

The rules governing when changing this variable takes effect and how long the effect lasts are the same as for other MySQL server system variables. See Section 12.5.4, “SET Syntax”, for more information.

The default mode is STATEMENT.

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

As with other global variables, to set binlog_format globally, you must have the SUPER privilege. You must also have the SUPER privilege to set binlog_format on the session level.

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

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

The binlog format affects the behavior of the following server options:

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

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.

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). You cannot set this variable to more than 1GB or to less than 4096 bytes. The 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 logs 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 — in this case, the server relies on the operating system to flush the binary log's contents from to time as for any other file. A value of 1 is the safest choice, because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Slave_IO_State — indicates the current status of the slave. See Section 7.5.6.5, “Replication Slave I/O Thread States”, and Section 7.5.6.6, “Replication Slave SQL Thread States”, for more information.

Slave_IO_Running — shows whether the IO thread for the reading the master's binary log is running.

Slave_SQL_Running — shows whether the SQL thread for the executing events in the relay log is running.

Last_Error — shows the last error registered when processing the relay log. Ideally this should be blank, indicating no errors.

Seconds_Behind_Master — shows the number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to cope with the large number of statements from the master.

A value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the master, but there are some cases where this is not strictly true. For example, this can occur if the network connection between master and slave is broken but the slave I/O thread has not yet noticed this — that is, slave_net_timeout has not yet elapsed.

It is also possible that transient values for Seconds_Behind_Master may not reflect the situation accurately. When the slave SQL thread has caught up on I/O, Seconds_Behind_Master displays 0; but when the slave I/O thread is still queuing up a new event, Seconds_Behind_Master may show a large value until the SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute SHOW SLAVE STATUS several times in a relatively short peiod, you may see this value change back and forth repeatedly between 0 and a relatively large value.

A master server M1

A slave server S1 that has M1 as its master

A client C1 connected to M1

A client C2 connected to S1

Requests for updates sent by C1 to M1 will fail because the server is in read-only mode

Requests for retrievals sent by C1 to M1 will succeed

Making a backup on M1 is safe

Making a backup on S1 is not safe: this server is still running, and might be processing the binary log or update requests coming from client C2 (S1 might not be in a read-only state)

The master M1 will continue to operate

Making a backup on the master is not safe

The slave S1 is stopped

Making a backup on the slave S1 is safe

An insert into an AUTO_INCREMENT column caused by a stored routine or trigger running on a master that uses MySQL 5.0.60 or earlier does not replicate correctly to a slave running MySQL 5.1.12 through 5.1.23 (inclusive). (Bug#33029)

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

This assumes that the table t1 has columns col1 and 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.1.7.1, “Problems with ALTER TABLE”.

STATEMENT or MIXED format.  The CREATE TABLE ... SELECT statement is itself replicated.

ROW format.  The statement is replicated as a CREATE TABLE statement followed by a series of binwrite events (that is, binary inserts).

No IF NOT EXISTS option.  If the CREATE TABLE ... SELECT does not contain an IF NOT EXISTS option, then the statement has no effect. However, the implicit commit caused by the statement is logged. This is true regardless of the replication format, storage engine used, and the reason for which the statement failed.

Statement uses IF NOT EXISTS.  If the CREATE TABLE ... SELECT statement includes the IF NOT EXISTS option and fails, the failure is handled according to the replication format. If the row-based format is in use, the action taken depends additionally on whether or not the table to be created uses a transactional or nontransactional storage engine, and on the reason for the failure:

You must be using row-based replication. (Using MIXED for the binary logging format does not work.)

The database and table names must be the same on both the master and the slave.

The effects of the feature are always replicated.

The following statements are replicated using statement-based replication:

However, the effects of features created, modified, or dropped using these statements are replicated using row-based replication.

In the case of CREATE EVENT and ALTER EVENT:

The feature implementation resides on the slave in a renewable state so that if the master fails, the slave can be used as the master without loss of event processing.

The USER(), CURRENT_USER(), UUID(), VERSION(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave unless row-based replication is enabled. This is also true for CURRENT_USER. (See Section 16.1.2, “Replication Formats”.)

USER(), CURRENT_USER(), and CURRENT_USER are automatically replicated using row-based replication when using MIXED mode, and generate a warning in STATEMENT mode. (Bug#28086)

For NOW(), the binary log includes the timestamp and replicates correctly.

Unlike NOW(), the SYSDATE() function is not replication-safe because it is not affected by SET TIMESTAMP statements in the binary log and is nondeterministic if statement-based logging is used. This is not a problem if row-based logging is used. Another option is to start the server with the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW().

The following restriction applies to statement-based replication only, not to row-based replication. 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 master. Therefore, these functions should not be used to insert into a master's table because the content on the slave would differ. (For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)).)

When using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued.

Currently, when using STATEMENT mode, warnings are issued for DML statements containing LIMIT even when they also have an ORDER BY clause (and so are made deterministic). This is a known issue which we are working to resolve in a future MySQL release. (Bug#42851)

When using MIXED mode, the statement is now automatically replicated using row-based mode.

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, set the names off using backtick characters (`).

A statement containing only nontransactional changes was written immediately to the binary log (sometime referred to as “write-ahead”).

A statement containing only transactional changes was always cached while waiting for the transaction to be committed.

A statement containing a mix of transactional and nontransactional changes (that is, a statement updating both transaction and nontransactional tables) could lead to mismatched tables on the master and the slave.