Chapter 17 Replication Table of Contents Replication enables data from one MySQL database server (known as a source) to be copied to one or more MySQL database servers (known as replicas). Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from a source. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database. 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 source server. Reads, however, may take place on one or more replicas. This model can improve the performance of writes (since the source 3435 17.1 Configuring Replication .......................................................................................................... 3437 17.1.1 Binary Log File Position Based Replication Configuration Overview ............................... 3437 17.1.2 Setting Up Binary Log File Position Based Replication ................................................. 3438 17.1.3 Replication with Global Transaction Identifiers ............................................................. 3450 17.1.4 Changing GTID Mode on Online Servers ..................................................................... 3475 17.1.5 MySQL Multi-Source Replication ................................................................................. 3481 17.1.6 Replication and Binary Logging Options and Variables ................................................. 3488 17.1.7 Common Replication Administration Tasks .................................................................. 3577 17.2 Replication Implementation .................................................................................................... 3584 17.2.1 Replication Formats .................................................................................................... 3584 17.2.2 Replication Channels .................................................................................................. 3592 17.2.3 Replication Threads .................................................................................................... 3596 17.2.4 Relay Log and Replication Metadata Repositories ........................................................ 3599 17.2.5 How Servers Evaluate Replication Filtering Rules ........................................................ 3606 17.3 Replication Security ............................................................................................................... 3614 17.3.1 Setting Up Replication to Use Encrypted Connections .................................................. 3615 17.3.2 Encrypting Binary Log Files and Relay Log Files ......................................................... 3617 17.3.3 Replication Privilege Checks ....................................................................................... 3621 17.4 Replication Solutions ............................................................................................................. 3628 17.4.1 Using Replication for Backups .................................................................................... 3628 17.4.2 Handling an Unexpected Halt of a Replica .................................................................. 3632 17.4.3 Monitoring Row-based Replication .............................................................................. 3635 17.4.4 Using Replication with Different Source and Replica Storage Engines ........................... 3635 17.4.5 Using Replication for Scale-Out .................................................................................. 3636 17.4.6 Replicating Different Databases to Different Replicas ................................................... 3638 17.4.7 Improving Replication Performance ............................................................................. 3639 17.4.8 Switching Sources During Failover .............................................................................. 3641 17.4.9 Switching Sources with Asynchronous Connection Failover .......................................... 3643 17.4.10 Semisynchronous Replication .................................................................................... 3645 17.4.11 Delayed Replication .................................................................................................. 3651 17.5 Replication Notes and Tips .................................................................................................... 3654 17.5.1 Replication Features and Issues ................................................................................. 3654 17.5.2 Replication Compatibility Between MySQL Versions ..................................................... 3681 17.5.3 Upgrading a Replication Setup .................................................................................... 3682 17.5.4 Troubleshooting Replication ........................................................................................ 3684 17.5.5 How to Report Replication Bugs or Problems .............................................................. 3685