Chapter聽6.聽Replication - MySQL 5.0参考手册英文版

Robustness is increased with a master/slave setup. In the event of problems with the master, you can switch to the slave as a backup.

Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers. SELECT queries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of synchrony. This load-balancing strategy is effective if non-updating queries dominate, but that is the normal case.

Another benefit of using replication is that you can perform database backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made. See Section聽5.9.1, 鈥淒atabase Backups鈥�.

Known issue: In MySQL 5.0.17, the syntax for CREATE TRIGGER changed to include a DEFINER clause for specifying which access privileges to check at trigger invocation time. (See Section聽18.1, 鈥�CREATE TRIGGER Syntax鈥�, for more information.) However, if you attempt to replicate from a master server older than MySQL 5.0.17 to a slave running MySQL 5.0.17 through 5.0.19, replication of CREATE TRIGGER statements fails on the slave with a Definer not fully qualified error. A workaround is to create triggers on the master using a version-specific comment embedded in each CREATE TRIGGER statement:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER ... ;

CREATE TRIGGER statements written this way will replicate to newer slaves, which pick up the DEFINER clause from the comment and execute successfully.

This slave problem is fixed as of MySQL 5.0.20.

Replication of AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values is done correctly, subject to the following exceptions.

INSERT DELAYED ... VALUES(LAST_INSERT_ID()) inserts a different value on the master and the slave. (Bug#20819) This is fixed in MySQL 5.1 when using row-based or mixed-format binary logging.

Before MySQL 5.0.26, a stored procedure that uses LAST_INSERT_ID() does not replicate properly.

When a statement uses a stored function that inserts into an AUTO_INCREMENT column, the generated AUTO_INCREMENT value is not written into the binary log, so a different value can in some cases be inserted on the slave.

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.

Important: To guarantee the same ordering on both master and slave, all columns of t1 must be referenced in the ORDER BY clause.

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, 鈥淧roblems with ALTER TABLE鈥�.

Certain functions do not replicate well under some conditions:

As a workaround for the preceding limitations, you can use the strategy of saving the problematic function result in a user variable and referring to the variable in a later statement. For example, the following single-row INSERT is problematic due to the reference to the UUID() function:

INSERT INTO t VALUES(UUID());

To work around the problem, do this instead:

SET @my_uuid = UUID();
INSERT INTO t VALUES(@my_uuid);

That sequence of statements replicates because the value of @my_uuid is stored in the binary log as a user-variable event prior to the INSERT statement and is available for use in the INSERT.

The same idea applies to multiple-row inserts, but is more cumbersome to use. For a two-row insert, you can do this:

SET @my_uuid1 = UUID(); @my_uuid2 = UUID();
INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);

However, if the number of rows is large or unknown, the workaround is difficult or impracticable. For example, you cannot convert the following statement to one in which a given individual user variable is associated with each row:

INSERT INTO t2 SELECT UUID(), * FROM t1;

User privileges are replicated only if the mysql database is replicated. That is, the GRANT, REVOKE, SET PASSWORD, CREATE USER, and DROP USER statements take effect on the slave only if the replication setup includes the mysql database.

If you're replicating all databases, but don't want statements that affect user privileges to be replicated, set up the slave to not replicate the mysql database, using the --replicate-wild-ignore-table=mysql.% option. The slave will recognize that issuing privilege-related SQL statements won't have an effect, and thus not execute those statements.

The FOREIGN_KEY_CHECKS, SQL_MODE, UNIQUE_CHECKS, and SQL_AUTO_IS_NULL variables are all replicated in MySQL 5.0. The storage_engine system variable (also known as table_type) is not yet replicated, which is a good thing for replication between different storage engines.

Starting from MySQL 5.0.3 (master and slave), replication works even if the master and slave have different global character set variables. Starting from MySQL 5.0.4 (master and slave), replication works even if the master and slave have different global time zone variables.

The following applies to replication between MySQL servers that use different character sets:

If the master uses MySQL 4.1, the same system time zone should be set for both master and slave. Otherwise some statements will not be replicated properly, such as statements that use the NOW() or FROM_UNIXTIME() functions. You can set the time zone in which MySQL server runs by using the --timezone=timezone_name option of the mysqld_safe script or by setting the TZ environment variable. Both master and slave should also have the same default connection time zone setting; that is, the --default-time-zone parameter should have the same value for both master and slave. Note that this is not necessary when the master is MySQL 5.0 or later.

CONVERT_TZ(...,...,@@global.time_zone) is not properly replicated. CONVERT_TZ(...,...,@@session.time_zone) is properly replicated only if the master and slave are from MySQL 5.0.4 or newer.

Session variables are not replicated properly when used in statements that update tables. For example, SET MAX_JOIN_SIZE=1000 followed by INSERT INTO mytable VALUES(@@MAX_JOIN_SIZE) will not insert the same data on the master and the slave. This does not apply to the common sequence of SET TIME_ZONE=... followed by INSERT INTO mytable VALUES(CONVERT_TZ(...,...,@@time_zone)), which replicates correctly as of MySQL 5.0.4.

It is possible to replicate transactional tables on the master using non-transactional tables on the slave. For example, you can replicate an InnoDB master table as a MyISAM slave table. However, if you do this, 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.

Update statements that refer to user-defined variables (that is, variables of the form @var_name) are replicated correctly in MySQL 5.0. However, this is not true for versions prior to 4.1. Note that user variable names are case insensitive starting in MySQL 5.0. You should take this into account when setting up replication between MySQL 5.0 and older versions.

Non-delayed INSERT statements that refer to RAND() or user-defined variables replicate correctly. However, changing the statements to use INSERT DELAYED can result in different results on master and slave.

Slaves can connect to masters using SSL.

Views are always replicated to slaves. Views are filtered by their own name, not by the tables they refer to. This means that a view can be replicated to the slave even if the view contains a table that would normally be filtered out by replication-ignore-table rules. Care should therefore be taken to ensure that views do not replicate table data that would normally be filtered for security reasons.

In MySQL 5.0 (starting from 5.0.3), there is a global system variable slave_transaction_retries: If the replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or because it exceeded the InnoDB innodb_lock_wait_timeout or the NDBCluster TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout value, the transaction automatically retries slave_transaction_retries times before stopping with an error. The default value is 10. Starting from MySQL 5.0.4, the total retry count can be seen in the output of SHOW STATUS; see Section聽5.2.5, 鈥淪tatus Variables鈥�.

If a DATA DIRECTORY or INDEX DIRECTORY table option is used in a CREATE TABLE statement on the master server, the table option is also used on the slave. This can cause problems if no corresponding directory exists in the slave host filesystem or if it exists but is not accessible to the slave server. MySQL supports an sql_mode option called NO_DIR_IN_CREATE. If the slave server is run with this SQL mode enabled, it ignores the DATA DIRECTORY and INDEX DIRECTORY table options when replicating CREATE TABLE statements. The result is that MyISAM data and index files are created in the table's database directory.

It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, left to the will of the query optimizer. (This is in general not a good practice, even outside of replication.) For a detailed explanation of this issue, see Section聽B.1.8.1, 鈥淥pen Issues in MySQL鈥�.

Using LOAD TABLE FROM MASTER where the master is running MySQL 4.1 and the slave is running MySQL 5.0 may corrupt the table data, and is not supported. (Bug#16261)

The following applies only if either the master or the slave is running MySQL version 5.0.3 or older: If on the master a LOAD DATA INFILE is interrupted (integrity constraint violation, killed connection, and so on), the slave skips the LOAD DATA INFILE entirely. This means that if this command permanently inserted or updated table records before being interrupted, these modifications are not replicated to the slave.

Some forms of the FLUSH statement are not logged because they could cause problems if replicated to a slave: FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK. For a syntax example, see Section聽13.5.5.2, 鈥�FLUSH Syntax鈥�. The FLUSH TABLES, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements are written to the binary log and thus replicated to slaves. This is not normally a problem because these statements do not modify table data. However, this can cause difficulties under certain circumstances. If you replicate the privilege tables in the mysql database and update those tables directly without using GRANT, you must issue a FLUSH PRIVILEGES on the slaves to put the new privileges into effect. In addition, if you use FLUSH TABLES when renaming a MyISAM table that is part of a MERGE table, you must issue FLUSH TABLES manually on the slaves. These statements are written to the binary log unless you specify NO_WRITE_TO_BINLOG or its alias LOCAL.

When a server shuts down and restarts, its MEMORY (HEAP) tables become empty. The master replicates this effect to slaves as follows: The first time that the master uses each MEMORY table after startup, it logs an event that notifies the slaves that the table needs to be emptied by writing a DELETE statement for that table to the binary log. See Section聽14.4, 鈥淭he MEMORY (HEAP) Storage Engine鈥�, for more information about MEMORY tables.

Temporary tables are replicated except in the case where you shut down the slave server (not just the slave threads) and you have replicated temporary tables that are used in updates that have not yet been executed on the slave. If you shut down the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use the following procedure:

The syntax for multiple-table DELETE statements that use table aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the true table name to refer to any table from which rows should be deleted:

DELETE test FROM test AS t1, test2 WHERE ...

In MySQL 4.1, you must use the alias:

DELETE t1 FROM test AS t1, test2 WHERE ...

If you use such DELETE statements, the change in syntax means that a 4.0 master cannot replicate to 4.1 (or higher) slaves.

It is safe to connect servers in a circular master/slave relationship if you use the --log-slave-updates option. That means that you can create a setup such as this:

A -> B -> C -> A

However, many statements do not work correctly in this kind of setup unless your client code is written to take care of the potential problems that can occur from updates that occur in different sequence on different servers.

Server IDs are encoded in binary log events, so server A knows when an event that it reads was originally created by itself and does not execute the event (unless server A was started with the --replicate-same-server-id option, which is meaningful only in rare cases). Thus, there are no infinite loops. This type of circular setup works only if you perform no conflicting updates between the tables. In other words, if you insert data in both A and C, you should never insert a row in A that may have a key that conflicts with a row inserted in C. You should also not update the same rows on two servers if the order in which the updates are applied is significant.

If a statement on a slave produces an error, the slave SQL thread terminates, and the slave writes a message to its error log. You should then connect to the slave manually and determine the cause of the problem. (SHOW SLAVE STATUS is useful for this.) Then fix the problem (for example, you might need to create a non-existent table) and run START SLAVE.

It is safe to shut down a master server and restart it later. When a slave loses its connection to the master, the slave tries to reconnect immediately and retries periodically if that fails. The default is to retry every 60 seconds. This may be changed with the --master-connect-retry option. A slave also is able to deal with network connectivity outages. However, the slave notices the network outage only after receiving no data from the master for slave_net_timeout seconds. If your outages are short, you may want to decrease slave_net_timeout. See Section聽5.2.3, 鈥淪ystem Variables鈥�.

Shutting down the slave (cleanly) is also safe because it keeps track of where it left off. Unclean shutdowns might produce problems, especially if the disk cache was not flushed to disk before the system went down. Your system fault tolerance is greatly increased if you have a good uninterruptible power supply. Unclean shutdowns of the master may cause inconsistencies between the content of tables and the binary log in master; this can be avoided by using InnoDB tables and the --innodb-safe-binlog option on the master. See Section聽5.11.3, 鈥淭he Binary Log鈥�.

Note: --innodb-safe-binlog is unneeded as of MySQL 5.0.3, having been made obsolete by the introduction of XA transaction support.

A crash on the master side can result in the master's binary log having a final position less than the most recent position read by the slave, due to the master's binary log file not being flushed. This can cause the slave not to be able to replicate when the master comes back up. Setting sync_binlog=1 in the master my.cnf file helps to minimize this problem because it causes the master to flush its binary log more frequently.

Due to the non-transactional nature of MyISAM tables, it is possible to have a statement that only partially updates a table and returns an error code. This can happen, for example, on a multiple-row insert that has one row violating a key constraint, or if a long update statement is killed after updating some of the rows. If that happens on the master, the slave thread exits and waits for the database administrator to decide what to do about it unless the error code is legitimate and execution of the statement results in the same error code on the slave. If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the --slave-skip-errors option.

If you update transactional tables from non-transactional tables inside a BEGIN/COMMIT sequence, updates to the binary log may be out of synchrony with table states if the non-transactional table is updated before the transaction commits. This occurs because the transaction is written to the binary log only when it is committed.

In situations where transactions mix updates to transactional and non-transactional tables, the order of statements in the binary log is correct, and all needed statements are written to the binary log even in case of a ROLLBACK. However, when a second connection updates the non-transactional table before the first connection's transaction is complete, statements can be logged out of order, because the second connection's update is written immediately after it is performed, regardless of the state of the transaction being performed by the first connection.

Floating-point values are approximate, so comparisons involving them are inexact. This is true for operations that use floating-point values explicitly, or values that are converted to floating-point implicitly. Comparisons of floating-point values might yield different results on master and slave servers due to differences in computer architecture, the compiler used to build MySQL, and so forth. See Section聽12.2.2, 鈥淭ype Conversion in Expression Evaluation鈥�, and Section聽B.1.5.8, 鈥淧roblems with Floating-Point Comparisons鈥�.

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

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

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

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

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

--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.8.7.3, 鈥淪SL Command Options鈥�. The values in the master.info file take precedence if they can be read.

--master-user=user_name

The username 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 username is not set, the name test is assumed.

--max-relay-log-size=size

The size at which the server rotates relay log files automatically. For more information, see Section聽6.3.4, 鈥淩eplication Relay and Status Files鈥�. The default size is 1GB.

--read-only

When this option is given, the server allows no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. As of MySQL 5.0.16, this option does not apply to TEMPORARY tables.

--relay-log=file_name

The name for the relay log. The default name is host_name-relay-bin.nnnnnn, where host_name is the name of the slave server host and nnnnnn indicates that relay logs are created in numbered sequence. You can specify the option to create hostname-independent relay log names, or if your relay logs tend to be big (and you don't want to decrease max_relay_log_size) and you need to put them in some area different from the data directory, or if you want to increase speed by balancing load between disks.

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

--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 not needed any more. 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 temporarily ignore --relay-log-space-limit.

--replicate-do-db=db_name

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

If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section聽6.9, 鈥淗ow Servers Evaluate Replication Rules鈥�.

--replicate-do-table=db_name.tbl_name

Tell 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聽6.9, 鈥淗ow Servers Evaluate Replication Rules鈥�.

--replicate-ignore-db=db_name

Tells the slave to not 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聽6.9, 鈥淗ow Servers Evaluate Replication 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聽6.9, 鈥淗ow 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聽6.9, 鈥淗ow 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. 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. Note that 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). So 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聽6.9, 鈥淗ow 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聽6.9, 鈥淗ow 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=slave_name

The hostname 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 non-default 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 username 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.

id="optvar_slave_compressed_protocol" --show-slave-auth-info

Display slave usernames 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 filesystem 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 filesystem.

The directory specified by this option should be located in a disk-based filesystem (not a memory-based filesystem) 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 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. Error Codes and Messages, lists server error codes.

You can also (but should not) use the very non-recommended 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

No: Permit the statement and proceed to the table-checking stage.

Yes: Test the options using the same rules as for the --binlog-do-db and --binlog-ignore-db options to determine whether to permit or ignore the statement. What is the result of the test?

No --replicate-* options at all

The slave executes all statements that it receives from the master.

--replicate-*-db options, but no table options

The slave permits or ignores statements using the database options. Then it executes all statements permitted by those options because there are no table restrictions.

--replicate-*-table options, but no database options

All statements are permitted at the database-checking stage because there are no database conditions. The slave executes or ignores statements based on the table options.

A mix of database and table options

The slave permits or ignores statements using the database options. Then it evaluates all statements permitted by those options according to the table options. In some cases, this process can yield what might seem a counterintuitive result. Consider the following set of options:

[mysqld]
replicate-do-db    = db1
replicate-do-table = db2.mytbl2

Suppose that db1 is the default database and the slave receives this statement:

INSERT INTO mytbl1 VALUES(1,2,3);

The database is db1, which matches the --replicate-do-db option at the database-checking stage. The algorithm then proceeds to the table-checking stage. If there were no table options, the statement would be executed. However, because the options include a 鈥�do鈥� table option, the statement must match if it is to be executed. The statement does not match, so it is ignored. (The same would happen for any table in db1.)

safe_writer_connect()

safe_reader_connect()

safe_reader_statement()

safe_writer_statement()

If N = 0 (which means we have no replication), our system can handle about 1200/11 = 109 writes per second.

If N = 1, we get up to 184 writes per second.

If N = 8, we get up to 400 writes per second.

If N = 17, we get up to 480 writes per second.

Eventually, as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only eight servers, we increase it nearly four times.

What is the read/write ratio on your system?

How much more write load can one server handle if you reduce the reads?

For how many slaves do you have bandwidth available on your network?

To tell a slave to change its master, use the CHANGE MASTER TO statement.

A good way to keep your applications informed as to the location of the master is by having a dynamic DNS entry for the master. With bind you can use nsupdate to dynamically update your DNS.

Run your slaves with the --log-bin option and without --log-slave-updates. In this way, the slave is ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO statement on the other slaves. For example, assume that you have the following setup:

       WC
        \
         v
 WC----> M
       / | \
      /  |  \
     v   v   v
    S1   S2  S3

In this diagram, M means the master, S the slaves, WC the clients issuing database writes and reads; clients that issue only database reads are not represented, because they need not switch. S1, S2, and S3 are slaves running with --log-bin and without --log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless --log-slave-updates is specified, the binary log on each slave is empty initially. If for some reason M becomes unavailable, you can pick one of the slaves to become the new master. For example, if you pick S1, all WC should be redirected to S1, which will log updates to its binary log. S2 and S3 should then replicate from S1.

The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. Suppose that S1 has --log-slave-updates enabled. Then it will write updates that it receives from M to its own binary log. When S2 changes from M to S1 as its master, it may receive updates from S1 that it has already received from M

Make sure that all slaves have processed any statements in their relay log. On each slave, issue STOP SLAVE IO_THREAD, then check the output of SHOW PROCESSLIST until you see Has read all relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave S1 being promoted to become the master, issue STOP SLAVE and RESET MASTER.

On the other slaves S2 and S3, use STOP SLAVE and CHANGE MASTER TO MASTER_HOST='S1' (where 'S1' represents the real hostname of S1). To CHANGE MASTER, add all information about how to connect to S1 from S2 or S3 (user, password, port). In CHANGE MASTER, there is no need to specify the name of S1's binary log or binary log position to read from: We know it is the first binary log and position 4, which are the defaults for CHANGE MASTER. Finally, use START SLAVE on S2 and S3.

Then instruct all WC to direct their statements to S1. From that point on, all updates statements sent by WC to S1 are written to the binary log of S1, which then contains every update statement sent to S1 since M died.

The result is this configuration:

       WC
      /
      |
 WC   |  M(unavailable)
  \   |
   \  |
    v v
     S1<--S2  S3
      ^       |
      +-------+

When M is up again, you must issue on it the same CHANGE MASTER as that issued on S2 and S3, so that M becomes a slave of S1 and picks up all the WC writes that it missed while it was down. To make M a master again (because it is the most powerful machine, for example), use the preceding procedure as if S1 was unavailable and M was to be the new master. During this procedure, do not forget to run RESET MASTER on M before making S1, S2, and S3 slaves of M. Otherwise, they may pick up old WC writes from before the point at which M became unavailable.

Note that there is no synchronization between the different slaves to a master. Some slaves might be ahead of others. This means that the concept outlined in the previous example might not work. In practice, however, the relay logs of different slaves will most likely not be far behind the master, so it would work, anyway (but there is no guarantee).

Verify that the master has binary logging enabled by issuing a SHOW MASTER STATUS statement. If logging is enabled, Position is non-zero. If binary logging is not enabled, verify that you are running the master with the --log-bin and --server-id options.

Verify that the slave is running. Use SHOW SLAVE STATUS to check whether the Slave_IO_Running and Slave_SQL_Running values are both Yes. If not, verify the options that were used when starting the slave server. For example, --skip-slave-start prevents the slave threads from starting until you issue a START SLAVE statement.

If the slave is running, check whether it established a connection to the master. Use SHOW PROCESSLIST, find the I/O and SQL threads and check their State column to see what they display. See Section聽6.3, 鈥淩eplication Implementation Details鈥�. If the I/O thread state says Connecting to master, verify the privileges for the replication user on the master, the master hostname, your DNS setup, whether the master is actually running, and whether it is reachable from the slave.

If the slave was running previously but has stopped, the reason usually is that some statement that succeeded on the master failed on the slave. This should never happen if you have taken a proper snapshot of the master, and never modified the data on the slave outside of the slave thread. If the slave stops unexpectedly, it is a bug or you have encountered one of the known replication limitations described in Section聽6.7, 鈥淩eplication Features and Known Problems鈥�. If it is a bug, see Section聽6.12, 鈥淗ow to Report Replication Bugs or Problems鈥�, for instructions on how to report it.

MySQL Enterprise For immediate notification whenever a slave stops, subscribe to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.

If a statement that succeeded on the master refuses to run on the slave, try the following procedure if it is not feasible to do a full database resynchronization by deleting the slave's databases and copying a new snapshot from the master:

auto_increment_increment controls the increment between successive AUTO_INCREMENT values.

auto_increment_offset determines the starting point for AUTO_INCREMENT column values.

Set auto_increment_increment to N on each master.

Set each of the N masters to have a different auto_increment_offset, using the values 1, 2, 鈥�, N.