MySQL 5.6 Release Notes
Table of Contents
- 17.1 Replication Configuration
- 17.2 Replication Implementation
- 17.3 Replication Solutions
- 17.3.1 Using Replication for Backups
- 17.3.2 Handling an Unexpected Halt of a Replica Server
- 17.3.3 Using Replication with Different Source and Replica Storage Engines
- 17.3.4 Using Replication for Scale-Out
- 17.3.5 Replicating Different Databases to Different Replicas
- 17.3.6 Improving Replication Performance
- 17.3.7 Switching Sources During Failover
- 17.3.8 Setting Up Replication to Use Encrypted Connections
- 17.3.9 Semisynchronous Replication
- 17.3.10 Delayed Replication
- 17.4 Replication Notes and Tips
Replication enables data from one MySQL database server (the source) to be replicated to one or more MySQL database servers (the replicas). Replication is asynchronous by default, therefore replicas do not need to be connected permanently to receive updates from the source. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
For answers to some questions often asked by those who are new to MySQL Replication, see Section A.14, “MySQL 5.6 FAQ: Replication”.
Advantages of replication in MySQL include:
Scale-out solutions - spreading the load among multiple replicas to improve performance. In this environment, all writes and updates must take place on the replication source server. Reads, however, may take place on one or more replicas. This model can improve the performance of writes (since the source is dedicated to updates), while dramatically increasing read speed across an increasing number of replicas.
Data security - because data is replicated to the replica, and the replica can pause the replication process, it is possible to run backup services on the replica without corrupting the corresponding data on the source.
Analytics - live data can be created on the source, while the analysis of the information can take place on the replica without affecting the performance of the source.
Long-distance data distribution - if a branch office would like to work with a copy of your main data, you can use replication to create a local copy of the data for their use without requiring permanent access to the source.
Replication in MySQL features support for one-way, asynchronous replication, in which one server acts as the source, while one or more other servers act as replicas. This is in contrast to the synchronous replication which is a characteristic of NDB Cluster (see Chapter 18, MySQL NDB Cluster 7.3 and NDB Cluster 7.4). In MySQL 5.6, an interface to semisynchronous replication is supported in addition to the built-in asynchronous replication. With semisynchronous replication, a commit performed on the source blocks before returning to the session that performed the transaction until at least one replica acknowledges that it has received and logged the events for the transaction. See Section 17.3.9, “Semisynchronous Replication” MySQL 5.6 also supports delayed replication such that a replica server deliberately lags behind the source by at least a specified amount of time. See Section 17.3.10, “Delayed Replication”. For scenarios where synchronous replication is required, use NDB Cluster (see Chapter 18, MySQL NDB Cluster 7.3 and NDB Cluster 7.4).
There are a number of solutions available for setting up replication between two servers, but the best method to use depends on the presence of data and the engine types you are using. For more information on the available options, see Section 17.1.1, “How to Set Up Replication”.
There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You may also use a third variety, Mixed Based Replication (MBR). For more information on the different replication formats, see Section 17.1.2, “Replication Formats”. In MySQL 5.6, statement-based format is the default.
MySQL 5.6.5 and later supports transactional replication based on global transaction identifiers (GTIDs). When using this type of replication, it is not necessary to work directly with log files or positions within these files, which greatly simplifies many common replication tasks. Because replication using GTIDs is entirely transactional, consistency between source and replica is guaranteed as long as all transactions committed on the source have also been applied on the replica. For more information about GTIDs and GTID-based replication, see Section 17.1.3, “Replication with Global Transaction Identifiers”.
Replication is controlled through a number of different options and variables. These control the core operation of the replication, timeouts, and the databases and filters that can be applied on databases and tables. For more information on the available options, see Section 17.1.4, “Replication and Binary Logging Options and Variables”.
You can use replication to solve a number of different problems, including problems with performance, supporting the backup of different databases, and as part of a larger solution to alleviate system failures. For information on how to address these issues, see Section 17.3, “Replication Solutions”.
For notes and tips on how different data types and statements are treated during replication, including details of replication features, version compatibility, upgrades, and problems and their resolution, including an FAQ, see Section 17.4, “Replication Notes and Tips”.
For detailed information on the implementation of replication, how replication works, the process and contents of the binary log, background threads and the rules used to decide how statements are recorded and replication, see Section 17.2, “Replication Implementation”.