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 sync. 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 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, “Database Backups”.

A thread that does a lot of small changes to the database might want to use row-based logging, while a thread that does a lot of heavy-duty searching might want to use statement-based logging.

Some statements require a lot of execution on the master, but create a small result set. It might therefore be beneficial to replicated them row-based.

ANALYZE

REPAIR

OPTIMIZE

Replication is done correctly with AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values.

The USER(), UUID(), and LOAD_FILE() functions are replicated without changes and thus do not work reliably on the slave.

The following restriction applies to statement-based replication only, not to row-based replication. The functions handling user-level locks: GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), IS_USED_LOCK() are replicated without the slave knowing the concurrency context on master; so these functions should not be used to insert into a master's table as the content on slave would differ (i.e. do not do INSERT INTO mytable VALUES(GET_LOCK(...))).

The FOREIGN_KEY_CHECKS, SQL_MODE, UNIQUE_CHECKS, and SQL_AUTO_IS_NULL variables are all replicated in MySQL 5.1. The TABLE_TYPE, also known as STORAGE_ENGINE variable is not yet replicated, which is a good thing for replication between different storage engines.

Replication works even if the master and slave have different global character set variables, and even if the master and slave have different global timezone variables.

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

For both master and slave the same system time zone should be set. Otherwise some statements, for example statements using NOW() or FROM_UNIXTIME() functions, won't be replicated properly. One could 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.

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

Session variables are not replicated properly when used in statements which update tables; for example: SET MAX_JOIN_SIZE=1000; INSERT INTO mytable VALUES(@MAX_JOIN_SIZE); will not insert the same data on master and on slave. This does not apply to the common SET TIME_ZONE=...; INSERT INTO mytable VALUES(CONVERT_TZ(...,...,@time_zone)).

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. This issue is on our TODO and will be fixed in the near future.

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

Slaves can connect to masters using SSL.

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 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. Starting from MySQL 5.0.4, the total count of retries can be seen in the output of SHOW STATUS; see Section 5.3.4, “Server Status Variables”.

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

The following restriction applies to statement-based replication only, not to row-based replication: It is possible for the data on the master and slave to become different if a query 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 A.8.1, “Open Issues in MySQL”.

FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged, as any of these could cause problems if replicated to a slave.) For a syntax example, see Section 13.5.5.2, “FLUSH Syntax”. 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.

MySQL only supports one master and many slaves. In the future we plan to add a voting algorithm for changing the master automatically in the event of problems with the current master. We also plan to introduce agent processes to help perform load balancing by sending SELECT queries to different slaves.

When a server shuts down and restarts, its MEMORY tables become empty. The master replicates this effect as follows: The first time that the master uses each MEMORY table after startup, it notifies the slaves that the table needs to be emptied by writing a DELETE FROM statement for that table to the binary log. See Section 15.4, “The MEMORY (HEAP) Storage Engine”, for more information.

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:

We plan to fix this problem in the near future.

It is safe to connect servers in a circular master/slave relationship with the --log-slave-updates option specified. Note, however, that 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.

This means that you can create a setup such as this:

A -> B -> C -> A

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 the 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, fix the problem (for example, a non-existent table), and then run START SLAVE.

It is safe to shut down a master server and restart it later. If a slave loses its connection to the master, the slave tries to reconnect immediately. If that fails, the slave retries periodically. (The default is to retry every 60 seconds. This may be changed with the --master-connect-retry option.) The slave also is able to deal with network connectivity outages. However, the slave does notice 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.3.3, “Server System Variables”.

Shutting down the slave (cleanly) is also safe, as it keeps track of where it left off. Unclean shutdowns might produce problems, especially if 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, “The Binary Log”. (Note: --innodb-safe-binlog is not needed in MySQL 5.1, having been made obsolete by the introduction of XA transaction support.)

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 the statement execution results in the same error code. 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 sync if the non-transactional table is updated before the transaction commits. This is 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, 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.

--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, updates received from a master server by a slave are not logged to its binary log. This option tells the slave to log the updates performed by its SQL thread to the slave's 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 a setup like this:

A -> B -> C

That is, 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.

--log-warnings

Makes the slave print more messages to the error log about what it is doing. For example, it warns you 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 the slave thread sleeps before retrying to connect 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.

--master-host=host

The hostname or IP number of the master replication server. If this option is not given, the slave thread does not start. The value in master.info takes precedence if it can be read.

--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 mysql.info in the data directory.

--master-password=password

The password of the account that the slave thread uses for authentication when connecting 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 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. If you have not tinkered with configure options, this should be 3306.

--master-retry-count=count

The number of times the slave tries to connect to the master before giving up.

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

--master-user=username

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

--max-relay-log-size=size

To rotate the relay log automatically. See Section 5.3.3, “Server System Variables”.

--read-only

This option causes the slave not to allow any updates except from slave threads or from users having the SUPER privilege. This can be useful to ensure that a slave server accepts no updates from clients. 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 location and name that should be used for the relay log index file. The default name is host_name-relay-bin.index, 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}

Disables or enables 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.

--relay-log-space-limit=size

Places an upper limit on the total size of all relay logs on the slave (a value of 0 means “unlimited”). 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. (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

Tells 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. If you need cross-database updates to work, use --replicate-wild-do-table=db_name.%. See Section 6.10, “How Servers Evaluate Replication Rules”.

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;

If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead.

The main reason for this “just check the default database” behavior is that it is difficult from the statement alone to know whether or not 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.

--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 6.10, “How 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.10, “How 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 not 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.

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

--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.10, “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 to not 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.10, “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.

--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 was the default database on the master. This does not work for cross-database updates. Note that the database name translation is done before --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 can should the default setting of 0, to prevent infinite loops in circular replication. If set to 1, this 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 which you want the slave SQL thread to execute.

--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-port=slave_port

The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set it 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, leave this option unset.

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

--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 to-be-loaded file from the relay log into temporary files, then loads these into the table. If the file loaded on the master was 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, you may also use the --relay-log option to place the relay logs in that filesystem, because the relay logs are huge as well. --slave-load-tmpdir should point to a disk-based filesystem, not a memory-based one: The slave needs the temporary files used to replicate LOAD DATA INFILE to survive a machine's restart. 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 aborting the read, considering the connection broken, and trying to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the --master-connect-retry option.

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

Normally, replication stops when an error occurs, which 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 sync 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. The server error codes are listed in Appendix B, Error Codes and Messages.

You can also (but should not) use the very non-recommended value of all which ignores all error messages and keeps going regardless of what happens. Needless to say, if you use it, we make no guarantees regarding integrity 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

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.

You should 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 on the other slaves. For example, assume that you have the following setup:

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

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. 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, and S2 and S3 should then replicate from S1.

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.

Proven technology (existed in MySQL since 3.23).

Smaller log files (when using updates or deletes that affects many rows, much smaller log files). Because log files are smaller, they take up less storage space and are faster to back up.

Log files contain all statements that made any changes, which allow them to be used to audit the database.

Log files can be used for point-in-time recovery, not just for replication purposes. See Section 5.9.3, “Point-in-Time Recovery”.

Slave may be a newer version of MySQL with a different row structure.

Not all UPDATE statements can be replicated: Any non-deterministic behavior, for example when using random functions in an SQL statement, is hard to replicate when using statement-based replication. When using a non-deterministic user-defined function (UDF), it is not possible to replicate the result using statement-based replication, while row-based replication will just replicate the value returned by the UDF.

Statements that use a UDF (user defined function) which is non-deterministic (value depends on other things than the given parameters) cannot be replicated properly.

Statements that use one of the following functions cannot be replicated properly:

All other functions are replicated correctly (including RAND(), NOW(), LOAD DATA INFILE, and so forth).

INSERT … SELECT requires more row-level locks than with row-level replication.

UPDATE statements that require a table scan (that is don't use indexes in the WHERE clause) have to lock more rows than with row-level replication.

For InnoDB: An INSERT statement that uses auto_increment will block other non-conflicting INSERT statements.

Slower to apply data on slave for complex queries.

Stored functions (not stored procedures) will execute with the same NOW() value as the calling statement. (This may be regarded both as a bad and a good thing.)

Deterministic UDFs (user-defined functions) must be applied on the slaves.

When getting something wrong on the slave, the difference between master and slave will grow with time.

Tables have to be (almost) identical on master and slave.

Everything can be replicated; safest form of replication. Note that currently, DDL (data definition language) statements such as CREATE TABLE are replicated using statement-based replication, while DML (data manipulation language) statements, as well as GRANT and REVOKE statements, are replicated using row-based-replication. For statements like CREATE … SELECT, a CREATE statement is generated from the table definition and replicated statement-based, while the row insertions are replicated row-based.

Same technology as most other database management systems (easier to explain to database administrators used to other systems).

In many cases, faster to apply data on the slave on tables with primary keys.

Less locks needed (thus higher concurrency) on the master for:

Less locks on the slave for any INSERT, UPDATE, or DELETE statement.

It's possible to add multiple threads to apply data on the slave in the future (works better on SMP machines).

Bigger log files (much bigger in some cases).

Binary log will contain data for large statements that were rolled back.

When using row-based replication to replicate a statement (for example, an UPDATE or DELETE statement), each changed row has to be written to the binary log. In contrast, when using statement-based replication only the statement is written to the binary log. If the statement changes a lot of rows, row-based replication may write significantly more data to the binary log. In these cases the binary log will be locked for longer times to write the data, which may cause concurrency problems.

Deterministic UDFs that generate big BLOBs will be notably slower to replicate.

One can't examine the logs to see what statements were executed.

One can't see on the slave what statements were received from the master and executed.