Chapter 16 Replication

Table of Contents

16.1 Replication Configuration
16.1.1 How to Set Up Replication
16.1.2 Replication Formats
16.1.3 Replication with Global Transaction Identifiers
16.1.4 Replication and Binary Logging Options and Variables
16.1.5 Common Replication Administration Tasks
16.2 Replication Implementation
16.2.1 Replication Implementation Details
16.2.2 Replication Relay and Status Logs
16.2.3 How Servers Evaluate Replication Filtering Rules
16.3 Replication Solutions
16.3.1 Using Replication for Backups
16.3.2 Using Replication with Different Master and Slave Storage Engines
16.3.3 Using Replication for Scale-Out
16.3.4 Replicating Different Databases to Different Slaves
16.3.5 Improving Replication Performance
16.3.6 Switching Masters During Failover
16.3.7 Setting Up Replication Using SSL
16.3.8 Semisynchronous Replication
16.3.9 Delayed Replication
16.4 Replication Notes and Tips
16.4.1 Replication Features and Issues
16.4.2 Replication Compatibility Between MySQL Versions
16.4.3 Upgrading a Replication Setup
16.4.4 Troubleshooting Replication
16.4.5 How to Report Replication Bugs or Problems

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous by default - slaves need not to connected permanently to receive updates from the master. 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 B.13, “MySQL 5.6 FAQ: Replication”.

The target uses for replication in MySQL include:

Replication in MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster (see Chapter 17, MySQL Cluster NDB 7.3). 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 master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. See Section 16.3.8, “Semisynchronous Replication” MySQL 5.6 also supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time. See Section 16.3.9, “Delayed Replication”.

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 16.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 16.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 master and slave is guaranteed as long as all transactions committed on the master have also been applied on the slave. For more information about GTIDs and GTID-based replication, see Section 16.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 16.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 16.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 16.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 16.2, “Replication Implementation”.