16.3 Replication Solutions

Replication can be used in many different environments for a range of purposes. This section provides general notes and advice on using replication for specific solution types.

For information on using replication in a backup environment, including notes on the setup, backup procedure, and files to back up, see *note replication-solutions-backups::.

For advice and tips on using different storage engines on the source and replicas, see *note replication-solutions-diffengines::.

Using replication as a scale-out solution requires some changes in the logic and operation of applications that use the solution. See *note replication-solutions-scaleout::.

For performance or data distribution reasons, you may want to replicate different databases to different replicas. See *note replication-solutions-partitioning::

As the number of replicas increases, the load on the source can increase and lead to reduced performance (because of the need to replicate the binary log to each replica). For tips on improving your replication performance, including using a single secondary server as a replication source server, see *note replication-solutions-performance::.

For guidance on switching sources, or converting replicas into sources as part of an emergency failover solution, see *note replication-solutions-switch::.

To secure your replication communication, you can encrypt the communication channel. For step-by-step instructions, see *note replication-encrypted-connections::.

 File: manual.info.tmp, Node: replication-solutions-backups, Next: replication-solutions-unexpected-replica-halt, Prev: replication-solutions, Up: replication-solutions

16.3.1 Using Replication for Backups

To use replication as a backup solution, replicate data from the source to a replica, and then back up the replica. The replica can be paused and shut down without affecting the running operation of the source, so you can produce an effective snapshot of 'live' data that would otherwise require the source to be shut down.

How you back up a database depends on its size and whether you are backing up only the data, or the data and the replica state so that you can rebuild the replica in the event of failure. There are therefore two choices:

Another backup strategy, which can be used for either source or replica servers, is to put the server in a read-only state. The backup is performed against the read-only server, which then is changed back to its usual read/write operational status. See *note replication-solutions-backups-read-only::.

 File: manual.info.tmp, Node: replication-solutions-backups-mysqldump, Next: replication-solutions-backups-rawdata, Prev: replication-solutions-backups, Up: replication-solutions-backups

16.3.1.1 Backing Up a Replica Using mysqldump .............................................

Using note 'mysqldump': mysqldump. to create a copy of a database enables you to capture all of the data in the database in a format that enables the information to be imported into another instance of MySQL Server (see note mysqldump::). Because the format of the information is SQL statements, the file can easily be distributed and applied to running servers in the event that you need access to the data in an emergency. However, if the size of your data set is very large, *note 'mysqldump': mysqldump. may be impractical.

When using *note 'mysqldump': mysqldump, you should stop replication on the replica before starting the dump process to ensure that the dump contains a consistent set of data:

  1. Stop the replica from processing requests. You can stop replication completely on the replica using *note 'mysqladmin': mysqladmin.:

      $> mysqladmin stop-slave

    Alternatively, you can stop only the replication SQL thread to pause event execution:

      $> mysql -e 'STOP SLAVE SQL_THREAD;'

    This enables the replica to continue to receive data change events from the source's binary log and store them in the relay logs using the I/O thread, but prevents the replica from executing these events and changing its data. Within busy replication environments, permitting the I/O thread to run during backup may speed up the catch-up process when you restart the replication SQL thread.

  2. Run *note 'mysqldump': mysqldump. to dump your databases. You may either dump all databases or select databases to be dumped. For example, to dump all databases:

      $> mysqldump --all-databases > fulldb.dump

  3. Once the dump has completed, start replica operations again:

      $> mysqladmin start-slave

In the preceding example, you may want to add login credentials (user name, password) to the commands, and bundle the process up into a script that you can run automatically each day.

If you use this approach, make sure you monitor the replication process to ensure that the time taken to run the backup does not affect the replica's ability to keep up with events from the source. See note replication-administration-status::. If the replica is unable to keep up, you may want to add another replica and distribute the backup process. For an example of how to configure this scenario, see note replication-solutions-partitioning::.

 File: manual.info.tmp, Node: replication-solutions-backups-rawdata, Next: replication-solutions-backups-read-only, Prev: replication-solutions-backups-mysqldump, Up: replication-solutions-backups

16.3.1.2 Backing Up Raw Data from a Replica ...........................................

To guarantee the integrity of the files that are copied, backing up the raw data files on your MySQL replica should take place while your replica server is shut down. If the MySQL server is still running, background tasks may still be updating the database files, particularly those involving storage engines with background processes such as 'InnoDB'. With 'InnoDB', these problems should be resolved during crash recovery, but since the replica server can be shut down during the backup process without affecting the execution of the source it makes sense to take advantage of this capability.

To shut down the server and back up the files:

  1. Shut down the replica MySQL server:

      $> mysqladmin shutdown

  2. Copy the data files. You can use any suitable copying or archive utility, including 'cp', 'tar' or 'WinZip'. For example, assuming that the data directory is located under the current directory, you can archive the entire directory as follows:

      $> tar cf /tmp/dbbackup.tar ./data

  3. Start the MySQL server again. Under Unix:

      $> mysqld_safe &

    Under Windows:

      C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld"

Normally you should back up the entire data directory for the replica MySQL server. If you want to be able to restore the data and operate as a replica (for example, in the event of failure of the replica), then in addition to the replica's data, you should also back up the replica status files, the replication metadata repositories, and the relay log files. These files are needed to resume replication after you restore the replica's data.

If you lose the relay logs but still have the 'relay-log.info' file, you can check it to determine how far the replication SQL thread has executed in the source's binary logs. Then you can use *note 'CHANGE MASTER TO': change-master-to. with the 'MASTER_LOG_FILE' and 'MASTER_LOG_POS' options to tell the replica to re-read the binary logs from that point. This requires that the binary logs still exist on the source server.

If your replica is replicating note 'LOAD DATA': load-data. statements, you should also back up any 'SQL_LOAD-' files that exist in the directory that the replica uses for this purpose. The replica needs these files to resume replication of any interrupted *note 'LOAD DATA': load-data. operations. The location of this directory is the value of the 'slave_load_tmpdir' system variable. If the server was not started with that variable set, the directory location is the value of the 'tmpdir' system variable.

 File: manual.info.tmp, Node: replication-solutions-backups-read-only, Prev: replication-solutions-backups-rawdata, Up: replication-solutions-backups

16.3.1.3 Backing Up a Source or Replica by Making It Read Only ..............................................................

It is possible to back up either source or replica servers in a replication setup by acquiring a global read lock and manipulating the 'read_only' system variable to change the read-only state of the server to be backed up:

  1. Make the server read-only, so that it processes only retrievals and blocks updates.

  2. Perform the backup.

  3. Change the server back to its normal read/write state.

Note:

The instructions in this section place the server to be backed up in a state that is safe for backup methods that get the data from the server, such as note 'mysqldump': mysqldump. (see note mysqldump::). You should not attempt to use these instructions to make a binary backup by copying files directly because the server may still have modified data cached in memory and not flushed to disk.

The following instructions describe how to do this for a source server and for a replica server. For both scenarios discussed here, suppose that you have the following replication setup:

In either scenario, the statements to acquire the global read lock and manipulate the 'read_only' variable are performed on the server to be backed up and do not propagate to any replicas of that server.

Scenario 1: Backup with a Read-Only Source

Put the source S1 in a read-only state by executing these statements on it:

 mysql> FLUSH TABLES WITH READ LOCK;
 mysql> SET GLOBAL read_only = ON;

While S1 is in a read-only state, the following properties are true:

While S1 is read only, perform the backup. For example, you can use *note 'mysqldump': mysqldump.

After the backup operation on S1 completes, restore S1 to its normal operational state by executing these statements:

 mysql> SET GLOBAL read_only = OFF;
 mysql> UNLOCK TABLES;

Although performing the backup on S1 is safe (as far as the backup is concerned), it is not optimal for performance because clients of S1 are blocked from executing updates.

This strategy applies to backing up a source server in a replication setup, but can also be used for a single server in a nonreplication setting.

Scenario 2: Backup with a Read-Only Replica

Put the replica R1 in a read-only state by executing these statements on it:

 mysql> FLUSH TABLES WITH READ LOCK;
 mysql> SET GLOBAL read_only = ON;

While R1 is in a read-only state, the following properties are true:

These properties provide the basis for a popular backup scenario: Having one replica busy performing a backup for a while is not a problem because it does not affect the entire network, and the system is still running during the backup. In particular, clients can still perform updates on the source server, which remains unaffected by backup activity on the replica.

While R1 is read only, perform the backup. For example, you can use *note 'mysqldump': mysqldump.

After the backup operation on R1 completes, restore R1 to its normal operational state by executing these statements:

 mysql> SET GLOBAL read_only = OFF;
 mysql> UNLOCK TABLES;

After the replica is restored to normal operation, it again synchronizes to the source by catching up with any outstanding updates from the binary log of the source.

 File: manual.info.tmp, Node: replication-solutions-unexpected-replica-halt, Next: replication-solutions-diffengines, Prev: replication-solutions-backups, Up: replication-solutions

16.3.2 Handling an Unexpected Halt of a Replica

In order for replication to be resilient to unexpected halts of the server (sometimes described as crash-safe) it must be possible for the replica to recover its state before halting. This section describes the impact of an unexpected halt of a replica during replication, and how to configure a replica for the best chance of recovery to continue replication.

After an unexpected halt of a replica, upon restart the replication SQL thread must recover information about which transactions have been executed already. The information required for recovery is stored in the replica's applier metadata repository. In older MySQL Server versions, this repository could only be created as a file in the data directory that was updated after the transaction had been applied. In MySQL 5.7 you can instead use an note 'InnoDB': innodb-storage-engine. table named 'mysql.slave_relay_log_info' to store the applier metadata repository. As a table, updates to the applier metadata repository are committed together with the transactions, meaning that the replica's progress information recorded in that repository is always consistent with what has been applied to the database, even in the event of an unexpected server halt. To configure MySQL 5.7 to store the applier metadata repository as an note 'InnoDB': innodb-storage-engine. table, set the system variable 'relay_log_info_repository' to 'TABLE'. For more information on the applier metadata repository, see *note replica-logs::.

The recovery process by which a replica recovers from an unexpected halt varies depending on the configuration of the replica. The details of the recovery process are influenced by the chosen method of replication, whether the replica is single-threaded or multithreaded, and the setting of relevant system variables. The overall aim of the recovery process is to identify what transactions had already been applied on the replica's database before the unexpected halt occurred, and retrieve and apply the transactions that the replica missed following the unexpected halt.

Using GTID-based replication makes it easiest to configure replication to be resilient to unexpected halts. GTID auto-positioning means the replica can reliably identify and retrieve missing transactions, even if there are gaps in the sequence of applied transactions.

The following information provides combinations of settings that are appropriate for different types of replica to guarantee recovery as far as this is under the control of replication.

Important:

Some factors outside the control of replication can have an impact on the replication recovery process and the overall state of replication after the recovery process. In particular, the settings that influence the recovery process for individual storage engines might result in transactions being lost in the event of an unexpected halt of a replica, and therefore unavailable to the replication recovery process. The 'innodb_flush_log_at_trx_commit=1' setting mentioned in the list below is a key setting for a replication setup that uses note 'InnoDB': innodb-storage-engine. with transactions. However, other settings specific to note 'InnoDB': innodb-storage-engine. or to other storage engines, especially those relating to flushing or synchronization, can also have an impact. Always check for and apply recommendations made by your chosen storage engines about crash-safe settings.

The following combination of settings on a replica is the most resilient to unexpected halts:

For a multithreaded replica, from MySQL 5.7.13, setting 'relay_log_recovery = ON' automatically handles any inconsistencies and gaps in the sequence of transactions that have been executed from the relay log. These gaps can occur when file position based replication is in use. (For more details, see note replication-features-transaction-inconsistencies::.) The relay log recovery process deals with gaps using the same method as the note 'START SLAVE UNTIL SQL_AFTER_MTS_GAPS': start-slave. statement would. When the replica reaches a consistent gap-free state, the relay log recovery process goes on to fetch further transactions from the source beginning at the replication SQL thread position. In MySQL versions prior to MySQL 5.7.13, this process was not automatic and required starting the server with 'relay_log_recovery = OFF', starting the replica with *note 'START SLAVE UNTIL SQL_AFTER_MTS_GAPS': start-slave. to fix any transaction inconsistencies, and then restarting the replica with 'relay_log_recovery = ON'. When GTID-based replication is in use, from MySQL 5.7.28 a multithreaded replica checks first whether 'MASTER_AUTO_POSITION' is set to 'ON', and if it is, omits the step of calculating the transactions that should be skipped or not skipped, so that the old relay logs are not required for the recovery process.

 File: manual.info.tmp, Node: replication-solutions-diffengines, Next: replication-solutions-scaleout, Prev: replication-solutions-unexpected-replica-halt, Up: replication-solutions

16.3.3 Using Replication with Different Source and Replica Storage Engines

It does not matter for the replication process whether the source table on the source and the replicated table on the replica use different engine types. In fact, the 'default_storage_engine' system variable is not replicated.

This provides a number of benefits in the replication process in that you can take advantage of different engine types for different replication scenarios. For example, in a typical scale-out scenario (see *note replication-solutions-scaleout::), you want to use 'InnoDB' tables on the source to take advantage of the transactional functionality, but use 'MyISAM' on the replicas where transaction support is not required because the data is only read. When using replication in a data-logging environment you may want to use the 'Archive' storage engine on the replica.

Configuring different engines on the source and replica depends on how you set up the initial replication process:

If you are already running a replication solution and want to convert your existing tables to another engine type, follow these steps:

  1. Stop the replica from running replication updates:

      mysql> STOP SLAVE;

    This enables you to change engine types without interruptions.

  2. Execute an 'ALTER TABLE ... ENGINE='ENGINE_TYPE'' for each table to be changed.

  3. Start the replication process again:

      mysql> START SLAVE;

Although the 'default_storage_engine' variable is not replicated, be aware that note 'CREATE TABLE': create-table. and note 'ALTER TABLE': alter-table. statements that include the engine specification are correctly replicated to the replica. For example, if you have a CSV table and you execute:

 mysql> ALTER TABLE csvtable Engine='MyISAM';

The previous statement is replicated to the replica and the engine type on the replica is converted to 'MyISAM', even if you have previously changed the table type on the replica to an engine other than CSV. If you want to retain engine differences on the source and replica, you should be careful to use the 'default_storage_engine' variable on the source when creating a new table. For example, instead of:

 mysql> CREATE TABLE tablea (columna int) Engine=MyISAM;

Use this format:

 mysql> SET default_storage_engine=MyISAM;
 mysql> CREATE TABLE tablea (columna int);

When replicated, the 'default_storage_engine' variable will be ignored, and the *note 'CREATE TABLE': create-table. statement executes on the replica using the replica's default engine.

 File: manual.info.tmp, Node: replication-solutions-scaleout, Next: replication-solutions-partitioning, Prev: replication-solutions-diffengines, Up: replication-solutions

16.3.4 Using Replication for Scale-Out

You can use replication as a scale-out solution; that is, where you want to split up the load of database queries across multiple database servers, within some reasonable limitations.

Because replication works from the distribution of one source to one or more replicas, using replication for scale-out works best in an environment where you have a high number of reads and low number of writes/updates. Most websites fit into this category, where users are browsing the website, reading articles, posts, or viewing products. Updates only occur during session management, or when making a purchase or adding a comment/message to a forum.

Replication in this situation enables you to distribute the reads over the replicas, while still enabling your web servers to communicate with the source when a write is required. You can see a sample replication layout for this scenario in *note figure_replication-scaleout::.

FIGURE GOES HERE: Using Replication to Improve Performance During Scale-Out

If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with a replicated setup should be very smooth and easy. Change the implementation of your database access to send all writes to the source, and to send reads to either the source or a replica. If your code does not have this level of abstraction, setting up a replicated system gives you the opportunity and motivation to clean it up. Start by creating a wrapper library or module that implements the following functions:

'safe_' in each function name means that the function takes care of handling all error conditions. You can use different names for the functions. The important thing is to have a unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

Then convert your client code to use the wrapper library. This may be a painful and scary process at first, but it pays off in the long run. All applications that use the approach just described are able to take advantage of a source/replica configuration, even one involving multiple replicas. The code is much easier to maintain, and adding troubleshooting options is trivial. You need modify only one or two functions (for example, to log how long each statement took, or which statement among those issued gave you an error).

If you have written a lot of code, you may want to automate the conversion task by using the *note 'replace': replace-utility. utility that comes with standard MySQL distributions, or write your own conversion script. Ideally, your code uses consistent programming style conventions. If not, then you are probably better off rewriting it anyway, or at least going through and manually regularizing it to use a consistent style.

 File: manual.info.tmp, Node: replication-solutions-partitioning, Next: replication-solutions-performance, Prev: replication-solutions-scaleout, Up: replication-solutions

16.3.5 Replicating Different Databases to Different Replicas

There may be situations where you have a single source and want to replicate different databases to different replicas. For example, you may want to distribute different sales data to different departments to help spread the load during data analysis. A sample of this layout is shown in *note figure_replication-multi-db::.

FIGURE GOES HERE: Replicating Databases to Separate Replicas

You can achieve this separation by configuring the source and replicas as normal, and then limiting the binary log statements that each replica processes by using the '--replicate-wild-do-table' configuration option on each replica.

Important:

You should not use '--replicate-do-db' for this purpose when using statement-based replication, since statement-based replication causes this option's effects to vary according to the database that is currently selected. This applies to mixed-format replication as well, since this enables some updates to be replicated using the statement-based format.

However, it should be safe to use '--replicate-do-db' for this purpose if you are using row-based replication only, since in this case the currently selected database has no effect on the option's operation.

For example, to support the separation as shown in note figure_replication-multi-db::, you should configure each replica as follows, before executing note 'START SLAVE': start-slave.:

Each replica in this configuration receives the entire binary log from the source, but executes only those events from the binary log that apply to the databases and tables included by the '--replicate-wild-do-table' option in effect on that replica.

If you have data that must be synchronized to the replicas before replication starts, you have a number of choices:

 File: manual.info.tmp, Node: replication-solutions-performance, Next: replication-solutions-switch, Prev: replication-solutions-partitioning, Up: replication-solutions

16.3.6 Improving Replication Performance

As the number of replicas connecting to a source increases, the load, although minimal, also increases, as each replica uses a client connection to the source. Also, as each replica must receive a full copy of the source's binary log, the network load on the source may also increase and create a bottleneck.

If you are using a large number of replicas connected to one source, and that source is also busy processing requests (for example, as part of a scale-out solution), then you may want to improve the performance of the replication process.

One way to improve the performance of the replication process is to create a deeper replication structure that enables the source to replicate to only one replica, and for the remaining replicas to connect to this primary replica for their individual replication requirements. A sample of this structure is shown in *note figure_replication-performance::.

FIGURE GOES HERE: Using an Additional Replication Source to Improve Performance

For this to work, you must configure the MySQL instances as follows:

The above solution reduces the client load and the network interface load on the primary source, which should improve the overall performance of the primary source when used as a direct database solution.

If your replicas are having trouble keeping up with the replication process on the source, there are a number of options available:

 File: manual.info.tmp, Node: replication-solutions-switch, Next: replication-encrypted-connections, Prev: replication-solutions-performance, Up: replication-solutions

16.3.7 Switching Sources During Failover

You can tell a replica to change to a new source using the *note 'CHANGE MASTER TO': change-master-to. statement. The replica does not check whether the databases on the source are compatible with those on the replica; it simply begins reading and executing events from the specified coordinates in the new source's binary log. In a failover situation, all the servers in the group are typically executing the same events from the same binary log file, so changing the source of the events should not affect the structure or integrity of the database, provided that you exercise care in making the change.

Replicas should be run with binary logging enabled (the '--log-bin' option), which is the default. If you are not using GTIDs for replication, then the replicas should also be run with '--log-slave-updates=OFF' (logging replica updates is the default). In this way, the replica is ready to become a source without restarting the replica note 'mysqld': mysqld. Assume that you have the structure shown in note figure_replication-redundancy-before::.

FIGURE GOES HERE: Redundancy Using Replication, Initial Structure

In this diagram, the 'Source' holds the source database, the 'Replica' hosts are replicas, and the 'Web Client' machines are issuing database reads and writes. Web clients that issue only reads (and would normally be connected to the replicas) are not shown, as they do not need to switch to a new server in the event of failure. For a more detailed example of a read/write scale-out replication structure, see note replication-solutions-scaleout::.

Each MySQL replica ('Replica 1', 'Replica 2', and 'Replica 3') is a replica running with binary logging enabled, and with '--log-slave-updates=OFF'. Because updates received by a replica from the source are not written to the binary log when '--log-slave-updates=OFF' is specified, the binary log on each replica is initially empty. If for some reason 'Source' becomes unavailable, you can pick one of the replicas to become the new source. For example, if you pick 'Replica 1', all 'Web Clients' should be redirected to 'Replica 1', which writes the updates to its binary log. 'Replica 2' and 'Replica 3' should then replicate from 'Replica 1'.

The reason for running the replica with '--log-slave-updates=OFF' is to prevent replicas from receiving updates twice in case you cause one of the replicas to become the new source. If 'Replica 1' has '--log-slave-updates' enabled, which is the default, it writes any updates that it receives from 'Source' in its own binary log. This means that, when 'Replica 2' changes from 'Source' to 'Replica 1' as its source, it may receive updates from 'Replica 1' that it has already received from 'Source'.

Make sure that all replicas have processed any statements in their relay log. On each replica, issue 'STOP SLAVE IO_THREAD', then check the output of note 'SHOW PROCESSLIST': show-processlist. until you see 'Has read all relay log'. When this is true for all replicas, they can be reconfigured to the new setup. On the replica 'Replica 1' being promoted to become the source, issue note 'STOP SLAVE': stop-slave. and *note 'RESET MASTER': reset-master.

On the other replicas 'Replica 2' and 'Replica 3', use note 'STOP SLAVE': stop-slave. and 'CHANGE MASTER TO MASTER_HOST='Replica1'' (where ''Replica1'' represents the real host name of 'Replica 1'). To use 'CHANGE MASTER TO', add all information about how to connect to 'Replica 1' from 'Replica 2' or 'Replica 3' (USER, PASSWORD, PORT). When issuing the statement in this scenario, there is no need to specify the name of the 'Replica 1' binary log file or log position to read from, since the first binary log file and position 4 are the defaults. Finally, execute note 'START SLAVE': start-slave. on 'Replica 2' and 'Replica 3'.

Once the new replication setup is in place, you need to tell each 'Web Client' to direct its statements to 'Replica 1'. From that point on, all updates sent by 'Web Client' to 'Replica 1' are written to the binary log of 'Replica 1', which then contains every update sent to 'Replica 1' since 'Source' became unavailable.

The resulting server structure is shown in *note figure_replication-redundancy-after::.

FIGURE GOES HERE: Redundancy Using Replication, After Source Failure

When 'Source' becomes available again, you should make it a replica of 'Replica 1'. To do this, issue on 'Source' the same *note 'CHANGE MASTER TO': change-master-to. statement as that issued on 'Replica 2' and 'Replica 3' previously. 'Source' then becomes a replica of 'Replica 1' and picks up the 'Web Client' writes that it missed while it was offline.

To make 'Source' a source again, use the preceding procedure as if 'Replica 1' were unavailable and 'Source' were to be the new source. During this procedure, do not forget to run *note 'RESET MASTER': reset-master. on 'Source' before making 'Replica 1', 'Replica 2', and 'Replica 3' replicas of 'Source'. If you fail to do this, the replicas may pick up stale writes from the 'Web Client' applications dating from before the point at which 'Source' became unavailable.

You should be aware that there is no synchronization between replicas, even when they share the same source, and thus some replicas might be considerably ahead of others. This means that in some cases the procedure outlined in the previous example might not work as expected. In practice, however, relay logs on all replicas should be relatively close together.

One way to keep applications informed about the location of the source is to have a dynamic DNS entry for the source host. With 'BIND', you can use 'nsupdate' to update the DNS dynamically.

 File: manual.info.tmp, Node: replication-encrypted-connections, Next: replication-semisync, Prev: replication-solutions-switch, Up: replication-solutions

16.3.8 Setting Up Replication to Use Encrypted Connections

To use an encrypted connection for the transfer of the binary log required during replication, both the source and the replica servers must support encrypted network connections. If either server does not support encrypted connections (because it has not been compiled or configured for them), replication through an encrypted connection is not possible.

Setting up encrypted connections for replication is similar to doing so for client/server connections. You must obtain (or create) a suitable security certificate that you can use on the source, and a similar certificate (from the same certificate authority) on each replica. You must also obtain suitable key files.

For more information on setting up a server and client for encrypted connections, see *note using-encrypted-connections::.

To enable encrypted connections on the source, you must create or obtain suitable certificate and key files, and then add the following configuration parameters to the source's configuration within the '[mysqld]' section of the source's 'my.cnf' file, changing the file names as necessary:

 [mysqld]
 ssl_ca=cacert.pem
 ssl_cert=server-cert.pem
 ssl_key=server-key.pem

The paths to the files may be relative or absolute; we recommend that you always use complete paths for this purpose.

The configuration parameters are as follows:

To enable encrypted connections on the replica, use the *note 'CHANGE MASTER TO': change-master-to. statement.

 File: manual.info.tmp, Node: replication-semisync, Next: replication-delayed, Prev: replication-encrypted-connections, Up: replication-solutions

16.3.9 Semisynchronous Replication

In addition to the built-in asynchronous replication, MySQL 5.7 supports an interface to semisynchronous replication that is implemented by plugins. This section discusses what semisynchronous replication is and how it works. The following sections cover the administrative interface to semisynchronous replication and how to install, configure, and monitor it.

MySQL replication by default is asynchronous. The source writes events to its binary log and replicas request them when they are ready. The source does not know whether or when a replica has retrieved and processed the transactions, and there is no guarantee that any event ever reaches any replica. With asynchronous replication, if the source crashes, transactions that it has committed might not have been transmitted to any replica. Failover from source to replica in this case might result in failover to a server that is missing transactions relative to the source.

With fully synchronous replication, when a source commits a transaction, all replicas must also have committed the transaction before the source returns to the session that performed the transaction. Fully synchronous replication means failover from the source to any replica is possible at any time. The drawback of fully synchronous replication is that there might be a lot of delay to complete a transaction.

Semisynchronous replication falls between asynchronous and fully synchronous replication. The source waits until at least one replica has received and logged the events (the required number of replicas is configurable), and then commits the transaction. The source does not wait for all replicas to acknowledge receipt, and it requires only an acknowledgement from the replicas, not that the events have been fully executed and committed on the replica side. Semisynchronous replication therefore guarantees that if the source crashes, all the transactions that it has committed have been transmitted to at least one replica.

Compared to asynchronous replication, semisynchronous replication provides improved data integrity, because when a commit returns successfully, it is known that the data exists in at least two places. Until a semisynchronous source receives acknowledgment from the required number of replicas, the transaction is on hold and not committed.

Compared to fully synchronous replication, semisynchronous replication is faster, because it can be configured to balance your requirements for data integrity (the number of replicas acknowledging receipt of the transaction) with the speed of commits, which are slower due to the need to wait for replicas.

Important:

With semisynchronous replication, if the source crashes and a failover to a replica is carried out, the failed source should not be reused as the replication source server, and should be discarded. It could have transactions that were not acknowledged by any replica, which were therefore not committed before the failover.

If your goal is to implement a fault-tolerant replication topology where all the servers receive the same transactions in the same order, and a server that crashes can rejoin the group and be brought up to date automatically, you can use Group Replication to achieve this. For information, see *note group-replication::.

The performance impact of semisynchronous replication compared to asynchronous replication is the tradeoff for increased data integrity. The amount of slowdown is at least the TCP/IP roundtrip time to send the commit to the replica and wait for the acknowledgment of receipt by the replica. This means that semisynchronous replication works best for close servers communicating over fast networks, and worst for distant servers communicating over slow networks. Semisynchronous replication also places a rate limit on busy sessions by constraining the speed at which binary log events can be sent from source to replica. When one user is too busy, this slows it down, which can be useful in some deployment situations.

Semisynchronous replication between a source and its replicas operates as follows:

While the source is blocking (waiting for acknowledgment from a replica), it does not return to the session that performed the transaction. When the block ends, the source returns to the session, which then can proceed to execute other statements. At this point, the transaction has committed on the source side, and receipt of its events has been acknowledged by at least one replica. The number of replica acknowledgments the source must receive per transaction before returning to the session is configurable using the 'rpl_semi_sync_master_wait_for_slave_count' system variable, for which the default value is 1.

Blocking also occurs after rollbacks that are written to the binary log, which occurs when a transaction that modifies nontransactional tables is rolled back. The rolled-back transaction is logged even though it has no effect for transactional tables because the modifications to the nontransactional tables cannot be rolled back and must be sent to replicas.

For statements that do not occur in transactional context (that is, when no transaction has been started with note 'START TRANSACTION': commit. or note 'SET autocommit = 0': set-variable.), autocommit is enabled and each statement commits implicitly. With semisynchronous replication, the source blocks for each such statement, just as it does for explicit transaction commits.

The 'rpl_semi_sync_master_wait_point' system variable controls the point at which a semisynchronous replication source waits for replica acknowledgment of transaction receipt before returning a status to the client that committed the transaction. These values are permitted:

The replication characteristics of these settings differ as follows:

 File: manual.info.tmp, Node: replication-semisync-interface, Next: replication-semisync-installation, Prev: replication-semisync, Up: replication-semisync

16.3.9.1 Semisynchronous Replication Administrative Interface .............................................................

The administrative interface to semisynchronous replication has several components:

The system and status variables are available only if the appropriate source or replica plugin has been installed with *note 'INSTALL PLUGIN': install-plugin.

 File: manual.info.tmp, Node: replication-semisync-installation, Next: replication-semisync-monitoring, Prev: replication-semisync-interface, Up: replication-semisync

16.3.9.2 Semisynchronous Replication Installation and Configuration ...................................................................

Semisynchronous replication is implemented using plugins, so the plugins must be installed into the server to make them available. After a plugin has been installed, you control it by means of the system variables associated with it. These system variables are unavailable until the associated plugin has been installed.

This section describes how to install the semisynchronous replication plugins. For general information about installing plugins, see *note plugin-loading::.

To use semisynchronous replication, the following requirements must be satisfied:

To set up semisynchronous replication, use the following instructions. The note 'INSTALL PLUGIN': install-plugin, note 'SET GLOBAL': set-variable, note 'STOP SLAVE': stop-slave, and note 'START SLAVE': start-slave. statements mentioned here require the 'SUPER' privilege.

MySQL distributions include semisynchronous replication plugin files for the source side and the replica side.

To be usable by a source or replica server, the appropriate plugin library file must be located in the MySQL plugin directory (the directory named by the 'plugin_dir' system variable). If necessary, configure the plugin directory location by setting the value of 'plugin_dir' at server startup.

The plugin library file base names are 'semisync_master' and 'semisync_slave'. The file name suffix differs per platform (for example, '.so' for Unix and Unix-like systems, '.dll' for Windows).

The source plugin library file must be present in the plugin directory of the source server. The replica plugin library file must be present in the plugin directory of each replica server.

To load the plugins, use the *note 'INSTALL PLUGIN': install-plugin. statement on the source and on each replica that is to be semisynchronous, adjusting the '.so' suffix for your platform as necessary.

On the source:

 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

On each replica:

 INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

If an attempt to install a plugin results in an error on Linux similar to that shown here, you must install 'libimf':

 mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
 ERROR 1126 (HY000): Can't open shared library
 '/usr/local/mysql/lib/plugin/semisync_master.so'
 (errno: 22 libimf.so: cannot open shared object file:
 No such file or directory)

You can obtain 'libimf' from https://dev.mysql.com/downloads/os-linux.html.

To see which plugins are installed, use the note 'SHOW PLUGINS': show-plugins. statement, or query the Information Schema note 'PLUGINS': information-schema-plugins-table. table.

To verify plugin installation, examine the Information Schema note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE '%semi%';
 +----------------------+---------------+
 | PLUGIN_NAME          | PLUGIN_STATUS |
 +----------------------+---------------+
 | rpl_semi_sync_master | ACTIVE        |
 +----------------------+---------------+

If the plugin fails to initialize, check the server error log for diagnostic messages.

After a semisynchronous replication plugin has been installed, it is disabled by default. The plugins must be enabled both on the source side and the replica side to enable semisynchronous replication. If only one side is enabled, replication is asynchronous.

To control whether an installed plugin is enabled, set the appropriate system variables. You can set these variables at runtime using *note 'SET GLOBAL': set-variable, or at server startup on the command line or in an option file.

At runtime, these source-side system variables are available:

 SET GLOBAL rpl_semi_sync_master_enabled = {0|1};
 SET GLOBAL rpl_semi_sync_master_timeout = N;

On the replica side, this system variable is available:

 SET GLOBAL rpl_semi_sync_slave_enabled = {0|1};

For 'rpl_semi_sync_master_enabled' or 'rpl_semi_sync_slave_enabled', the value should be 1 to enable semisynchronous replication or 0 to disable it. By default, these variables are set to 0.

For 'rpl_semi_sync_master_timeout', the value N is given in milliseconds. The default value is 10000 (10 seconds).

If you enable semisynchronous replication on a replica at runtime, you must also start the replication I/O thread (stopping it first if it is already running) to cause the replica to connect to the source and register as a semisynchronous replica:

 STOP SLAVE IO_THREAD;
 START SLAVE IO_THREAD;

If the replication I/O thread is already running and you do not restart it, the replica continues to use asynchronous replication.

At server startup, the variables that control semisynchronous replication can be set as command-line options or in an option file. A setting listed in an option file takes effect each time the server starts. For example, you can set the variables in 'my.cnf' files on the source and replica sides as follows.

On the source:

 [mysqld]
 rpl_semi_sync_master_enabled=1
 rpl_semi_sync_master_timeout=1000 # 1 second

On each replica:

 [mysqld]
 rpl_semi_sync_slave_enabled=1

 File: manual.info.tmp, Node: replication-semisync-monitoring, Prev: replication-semisync-installation, Up: replication-semisync

16.3.9.3 Semisynchronous Replication Monitoring ...............................................

The plugins for the semisynchronous replication capability expose several system and status variables that you can examine to determine its configuration and operational state.

The system variable reflect how semisynchronous replication is configured. To check their values, use *note 'SHOW VARIABLES': show-variables.:

 mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';

The status variables enable you to monitor the operation of semisynchronous replication. To check their values, use *note 'SHOW STATUS': show-status.:

 mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';

When the source switches between asynchronous or semisynchronous replication due to commit-blocking timeout or a replica catching up, it sets the value of the 'Rpl_semi_sync_master_status' status variable appropriately. Automatic fallback from semisynchronous to asynchronous replication on the source means that it is possible for the 'rpl_semi_sync_master_enabled' system variable to have a value of 1 on the source side even when semisynchronous replication is in fact not operational at the moment. You can monitor the 'Rpl_semi_sync_master_status' status variable to determine whether the source currently is using asynchronous or semisynchronous replication.

To see how many semisynchronous replicas are connected, check 'Rpl_semi_sync_master_clients'.

The number of commits that have been acknowledged successfully or unsuccessfully by replicas are indicated by the 'Rpl_semi_sync_master_yes_tx' and 'Rpl_semi_sync_master_no_tx' variables.

On the replica side, 'Rpl_semi_sync_slave_status' indicates whether semisynchronous replication currently is operational.

 File: manual.info.tmp, Node: replication-delayed, Prev: replication-semisync, Up: replication-solutions

16.3.10 Delayed Replication

MySQL 5.7 supports delayed replication such that a replica server deliberately lags behind the source by at least a specified amount of time. The default delay is 0 seconds. Use the 'MASTER_DELAY' option for *note 'CHANGE MASTER TO': change-master-to. to set the delay to N seconds:

 CHANGE MASTER TO MASTER_DELAY = N;

An event received from the source is not executed until at least N seconds later than its execution on the source. The exceptions are that there is no delay for format description events or log file rotation events, which affect only the internal state of the SQL thread.

Delayed replication can be used for several purposes:

note 'START SLAVE': start-slave. and note 'STOP SLAVE': stop-slave. take effect immediately and ignore any delay. *note 'RESET SLAVE': reset-slave. resets the delay to 0.

*note 'SHOW SLAVE STATUS': show-slave-status. has three fields that provide information about the delay:

When the replication SQL thread is waiting for the delay to elapse before executing an event, *note 'SHOW PROCESSLIST': show-processlist. displays its 'State' value as 'Waiting until MASTER_DELAY seconds after master executed event'.

 File: manual.info.tmp, Node: replication-notes, Prev: replication-solutions, Up: replication