MySQL Replication

Abstract

This is the MySQL Replication extract from the MySQL 8.0 Reference Manual.

For legal information, see the Legal Notices.

For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with other MySQL

users.

Document generated on: 2024-09-03 (revision: 79536)

Table of Contents

Preface and Legal Notices ................................................................................................................ vii

1 Replication ...................................................................................................................................... 1

2 Configuring Replication .................................................................................................................... 3

2.1 Binary Log File Position Based Replication Configuration Overview ......................................... 4

2.2 Setting Up Binary Log File Position Based Replication ........................................................... 4

2.2.1 Setting the Replication Source Configuration ............................................................... 6

2.2.2 Setting the Replica Configuration ............................................................................... 6

2.2.3 Creating a User for Replication .................................................................................. 7

2.2.4 Obtaining the Replication Source Binary Log Coordinates ............................................ 8

2.2.5 Choosing a Method for Data Snapshots ..................................................................... 9

2.2.6 Setting Up Replicas ................................................................................................. 12

2.2.7 Setting the Source Configuration on the Replica ........................................................ 14

2.2.8 Adding Replicas to a Replication Environment ........................................................... 15

2.3 Replication with Global Transaction Identifiers ..................................................................... 17

2.3.1 GTID Format and Storage ........................................................................................ 18

2.3.2 GTID Life Cycle ....................................................................................................... 22

2.3.3 GTID Auto-Positioning .............................................................................................. 27

2.3.4 Setting Up Replication Using GTIDs ......................................................................... 28

2.3.5 Using GTIDs for Failover and Scaleout ..................................................................... 31

2.3.6 Replication From a Source Without GTIDs to a Replica With GTIDs ............................ 34

2.3.7 Restrictions on Replication with GTIDs ..................................................................... 35

2.3.8 Stored Function Examples to Manipulate GTIDs ........................................................ 37

2.4 Changing GTID Mode on Online Servers ............................................................................. 41

2.4.1 Replication Mode Concepts ...................................................................................... 41

2.4.2 Enabling GTID Transactions Online .......................................................................... 43

2.4.3 Disabling GTID Transactions Online ......................................................................... 45

2.4.4 Verifying Replication of Anonymous Transactions ...................................................... 47

2.5 MySQL Multi-Source Replication ......................................................................................... 48

2.5.1 Configuring Multi-Source Replication ......................................................................... 48

2.5.2 Provisioning a Multi-Source Replica for GTID-Based Replication ................................ 49

2.5.3 Adding GTID-Based Sources to a Multi-Source Replica ............................................. 51

2.5.4 Adding Binary Log Based Replication Sources to a Multi-Source Replica .................... 51

2.5.5 Starting Multi-Source Replicas .................................................................................. 52

2.5.6 Stopping Multi-Source Replicas ................................................................................ 52

2.5.7 Resetting Multi-Source Replicas ............................................................................... 52

2.5.8 Monitoring Multi-Source Replication .......................................................................... 53

2.6 Replication and Binary Logging Options and Variables ......................................................... 54

2.6.1 Replication and Binary Logging Option and Variable Reference .................................. 56

2.6.2 Replication Source Options and Variables ................................................................. 65

2.6.3 Replica Server Options and Variables ....................................................................... 78

2.6.4 Binary Logging Options and Variables .................................................................... 136

2.6.5 Global Transaction ID System Variables ................................................................. 170

2.7 Common Replication Administration Tasks ......................................................................... 177

2.7.1 Checking Replication Status ................................................................................... 178

2.7.2 Pausing Replication on the Replica ......................................................................... 180

2.7.3 Skipping Transactions ............................................................................................ 181

3 Replication Solutions ................................................................................................................... 185

3.1 Using Replication for Backups ........................................................................................... 185

3.1.1 Backing Up a Replica Using mysqldump ................................................................. 186

3.1.2 Backing Up Raw Data from a Replica ..................................................................... 187

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

iii

MySQL Replication

3.2 Handling an Unexpected Halt of a Replica ......................................................................... 189

3.3 Monitoring Row-based Replication ..................................................................................... 192

3.4 Using Replication with Different Source and Replica Storage Engines .................................. 192

3.5 Using Replication for Scale-Out ......................................................................................... 194

3.6 Replicating Different Databases to Different Replicas ......................................................... 195

3.7 Improving Replication Performance .................................................................................... 196

3.8 Switching Sources During Failover .................................................................................... 198

3.9 Switching Sources and Replicas with Asynchronous Connection Failover ............................. 200

3.9.1 Asynchronous Connection Failover for Sources ....................................................... 201

3.9.2 Asynchronous Connection Failover for Replicas ...................................................... 203

3.10 Semisynchronous Replication .......................................................................................... 204

3.10.1 Installing Semisynchronous Replication ................................................................. 206

3.10.2 Configuring Semisynchronous Replication ............................................................. 208

3.10.3 Semisynchronous Replication Monitoring ............................................................... 209

3.11 Delayed Replication ........................................................................................................ 210

4 Replication Notes and Tips .......................................................................................................... 215

4.1 Replication Features and Issues ........................................................................................ 215

4.1.1 Replication and AUTO_INCREMENT ...................................................................... 216

4.1.2 Replication and BLACKHOLE Tables ...................................................................... 217

4.1.3 Replication and Character Sets .............................................................................. 217

4.1.4 Replication and CHECKSUM TABLE ...................................................................... 217

4.1.5 Replication of CREATE SERVER, ALTER SERVER, and DROP SERVER ................ 217

4.1.6 Replication of CREATE ... IF NOT EXISTS Statements ............................................ 217

4.1.7 Replication of CREATE TABLE ... SELECT Statements ........................................... 218

4.1.8 Replication of CURRENT_USER() .......................................................................... 218

4.1.9 Replication with Differing Table Definitions on Source and Replica ............................ 219

4.1.10 Replication and DIRECTORY Table Options .......................................................... 223

4.1.11 Replication of DROP ... IF EXISTS Statements ...................................................... 224

4.1.12 Replication and Floating-Point Values ................................................................... 224

4.1.13 Replication and FLUSH ........................................................................................ 224

4.1.14 Replication and System Functions ........................................................................ 224

4.1.15 Replication and Fractional Seconds Support .......................................................... 226

4.1.16 Replication of Invoked Features ............................................................................ 226

4.1.17 Replication of JSON Documents ........................................................................... 228

4.1.18 Replication and LIMIT .......................................................................................... 228

4.1.19 Replication and LOAD DATA ................................................................................ 229

4.1.20 Replication and max_allowed_packet .................................................................... 229

4.1.21 Replication and MEMORY Tables ......................................................................... 230

4.1.22 Replication of the mysql System Schema .............................................................. 231

4.1.23 Replication and the Query Optimizer ..................................................................... 231

4.1.24 Replication and Partitioning .................................................................................. 231

4.1.25 Replication and REPAIR TABLE ........................................................................... 231

4.1.26 Replication and Reserved Words .......................................................................... 232

4.1.27 Replication and Row Searches ............................................................................. 232

4.1.28 Replication and Source or Replica Shutdowns ....................................................... 233

4.1.29 Replica Errors During Replication ......................................................................... 234

4.1.30 Replication and Server SQL Mode ........................................................................ 235

4.1.31 Replication and Temporary Tables ........................................................................ 235

4.1.32 Replication Retries and Timeouts .......................................................................... 236

4.1.33 Replication and Time Zones ................................................................................. 236

4.1.34 Replication and Transaction Inconsistencies .......................................................... 237

4.1.35 Replication and Transactions ................................................................................ 239

4.1.36 Replication and Triggers ....................................................................................... 241

4.1.37 Replication and TRUNCATE TABLE ..................................................................... 242

MySQL Replication

4.1.38 Replication and User Name Length ....................................................................... 242

4.1.39 Replication and Variables ..................................................................................... 242

4.1.40 Replication and Views .......................................................................................... 244

4.2 Replication Compatibility Between MySQL Versions ........................................................... 244

4.3 Upgrading a Replication Topology ..................................................................................... 245

4.4 Troubleshooting Replication .............................................................................................. 247

4.5 How to Report Replication Bugs or Problems ..................................................................... 249

5 Replication Implementation .......................................................................................................... 251

5.1 Replication Formats .......................................................................................................... 252

5.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication ...... 253

5.1.2 Usage of Row-Based Logging and Replication ........................................................ 256

5.1.3 Determination of Safe and Unsafe Statements in Binary Logging .............................. 257

5.2 Replication Channels ........................................................................................................ 260

5.2.1 Commands for Operations on a Single Channel ...................................................... 260

5.2.2 Compatibility with Previous Replication Statements .................................................. 261

5.2.3 Startup Options and Replication Channels .............................................................. 262

5.2.4 Replication Channel Naming Conventions ............................................................... 263

5.3 Replication Threads .......................................................................................................... 264

5.3.1 Monitoring Replication Main Threads ...................................................................... 264

5.3.2 Monitoring Replication Applier Worker Threads ........................................................ 265

5.4 Relay Log and Replication Metadata Repositories .............................................................. 266

5.4.1 The Relay Log ....................................................................................................... 267

5.4.2 Replication Metadata Repositories .......................................................................... 268

5.5 How Servers Evaluate Replication Filtering Rules ............................................................... 274

5.5.1 Evaluation of Database-Level Replication and Binary Logging Options ...................... 275

5.5.2 Evaluation of Table-Level Replication Options ......................................................... 276

5.5.3 Interactions Between Replication Filtering Options ................................................... 278

5.5.4 Replication Channel Based Filters .......................................................................... 280

Preface and Legal Notices

This is the MySQL Replication extract from the MySQL 8.0 Reference Manual.

Licensing information—MySQL 8.0. This product may include third-party software, used under

license. If you are using a Commercial release of MySQL 8.0, see the MySQL 8.0 Commercial Release

License Information User Manual for licensing information, including licensing information relating to third-

party software that may be included in this Commercial release. If you are using a Community release

of MySQL 8.0, see the MySQL 8.0 Community Release License Information User Manual for licensing

information, including licensing information relating to third-party software that may be included in this

Community release.

Legal Notices

License Restrictions

This software and related documentation are provided under a license agreement containing restrictions

on use and disclosure and are protected by intellectual property laws. Except as expressly permitted

in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast,

modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any

means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for

interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free.

If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software, software documentation, data (as defined in the Federal Acquisition Regulation), or

related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S.

Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated

software, any programs embedded, installed, or activated on delivered hardware, and modifications

of such programs) and Oracle computer documentation or other Oracle data delivered to or accessed

by U.S. Government end users are "commercial computer software," "commercial computer software

documentation," or "limited rights data" pursuant to the applicable Federal Acquisition Regulation and

agency-specific supplemental regulations. As such, the use, reproduction, duplication, release, display,

disclosure, modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including

any operating system, integrated software, any programs embedded, installed, or activated on delivered

hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other Oracle

data, is subject to the rights and limitations specified in the license contained in the applicable contract.

The terms governing the U.S. Government's use of Oracle cloud services are defined by the applicable

contract for such services. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management

applications. It is not developed or intended for use in any inherently dangerous applications, including

applications that may create a risk of personal injury. If you use this software or hardware in dangerous

applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other

vii

Documentation Accessibility

measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages

caused by use of this software or hardware in dangerous applications.

Trademark Notice

Oracle, Java, MySQL, and NetSuite are registered trademarks of Oracle and/or its affiliates. Other names

may be trademarks of their respective owners.

Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks

are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,

Epyc, and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a

registered trademark of The Open Group.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information about content,

products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and

expressly disclaim all warranties of any kind with respect to third-party content, products, and services

unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its

affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of

third-party content, products, or services, except as set forth in an applicable agreement between you and

Oracle.

Use of This Documentation

This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the

following terms:

You may create a printed copy of this documentation solely for your own personal use. Conversion to other

formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish

or distribute this documentation in any form or on any media, except if you distribute the documentation in

a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the

software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated

together with the software on the same medium. Any other use, such as any dissemination of printed

copies or use of this documentation, in whole or in part, in another publication, requires the prior written

consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights

to this documentation not expressly granted above.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support for Accessibility

Oracle customers that have purchased support have access to electronic support through My Oracle

Support. For information, visit

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/

lookup?ctx=acc&id=trs if you are hearing impaired.

viii

Chapter 1 Replication

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

is dedicated to updates), while dramatically increasing read speed across an increasing number of

replicas.

• Data security - because the replica can pause the replication process, it is possible to run backup

services on the replica without corrupting the corresponding source data.

• 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 - you can use replication to create a local copy of data for a remote site

to use, without permanent access to the source.

For information on how to use replication in such scenarios, see Chapter 3, Replication Solutions.

MySQL 8.0 supports different methods of replication. The traditional method is based on replicating events

from the source's binary log, and requires the log files and positions in them to be synchronized between

source and replica. The newer method based on global transaction identifiers (GTIDs) is transactional

and therefore does not require working with log files or positions within these files, which greatly simplifies

many common replication tasks. Replication using GTIDs guarantees consistency between source and

replica 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 in MySQL, see Section 2.3, “Replication with

Global Transaction Identifiers”. For information on using binary log file position based replication, see

Chapter 2, Configuring Replication.

Replication in MySQL supports different types of synchronization. The original type of synchronization

is 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 MySQL NDB Cluster 8.0). In MySQL 8.0, 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 3.10,

“Semisynchronous Replication”. MySQL 8.0 also supports delayed replication such that a replica

deliberately lags behind the source by at least a specified amount of time; see Section 3.11, “Delayed

Replication”. For scenarios where synchronous replication is required, use NDB Cluster (see MySQL NDB

Cluster 8.0).

There are a number of solutions available for setting up replication between servers, and 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 2.2, “Setting Up Binary Log File Position Based 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 can

also use a third variety, Mixed Based Replication (MBR). For more information on the different replication

formats, see Section 5.1, “Replication Formats”.

Replication is controlled through a number of different options and variables. For more information, see

Section 2.6, “Replication and Binary Logging Options and Variables”. Additional security measures can be

applied to a replication topology, as described in Replication Security.

You can use replication to solve a number of different problems, including 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 Chapter 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 potential problems and their resolution,

see Chapter 4, Replication Notes and Tips. For answers to some questions often asked by those who are

new to MySQL Replication, see MySQL 8.0 FAQ: Replication.

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 replicated, see Chapter 5, Replication Implementation.

Chapter 2 Configuring Replication

Table of Contents

2.1 Binary Log File Position Based Replication Configuration Overview ................................................. 4

2.2 Setting Up Binary Log File Position Based Replication ................................................................... 4

2.2.1 Setting the Replication Source Configuration ....................................................................... 6

2.2.2 Setting the Replica Configuration ....................................................................................... 6

2.2.3 Creating a User for Replication .......................................................................................... 7

2.2.4 Obtaining the Replication Source Binary Log Coordinates .................................................... 8

2.2.5 Choosing a Method for Data Snapshots ............................................................................. 9

2.2.6 Setting Up Replicas ......................................................................................................... 12

2.2.7 Setting the Source Configuration on the Replica ................................................................ 14

2.2.8 Adding Replicas to a Replication Environment ................................................................... 15

2.3 Replication with Global Transaction Identifiers ............................................................................. 17

2.3.1 GTID Format and Storage ................................................................................................ 18

2.3.2 GTID Life Cycle ............................................................................................................... 22

2.3.3 GTID Auto-Positioning ...................................................................................................... 27

2.3.4 Setting Up Replication Using GTIDs ................................................................................. 28

2.3.5 Using GTIDs for Failover and Scaleout ............................................................................. 31

2.3.6 Replication From a Source Without GTIDs to a Replica With GTIDs ................................... 34

2.3.7 Restrictions on Replication with GTIDs ............................................................................. 35

2.3.8 Stored Function Examples to Manipulate GTIDs ................................................................ 37

2.4 Changing GTID Mode on Online Servers ..................................................................................... 41

2.4.1 Replication Mode Concepts .............................................................................................. 41

2.4.2 Enabling GTID Transactions Online .................................................................................. 43

2.4.3 Disabling GTID Transactions Online ................................................................................. 45

2.4.4 Verifying Replication of Anonymous Transactions .............................................................. 47

2.5 MySQL Multi-Source Replication ................................................................................................. 48

2.5.1 Configuring Multi-Source Replication ................................................................................. 48

2.5.2 Provisioning a Multi-Source Replica for GTID-Based Replication ........................................ 49

2.5.3 Adding GTID-Based Sources to a Multi-Source Replica ..................................................... 51

2.5.4 Adding Binary Log Based Replication Sources to a Multi-Source Replica ............................ 51

2.5.5 Starting Multi-Source Replicas .......................................................................................... 52

2.5.6 Stopping Multi-Source Replicas ........................................................................................ 52

2.5.7 Resetting Multi-Source Replicas ....................................................................................... 52

2.5.8 Monitoring Multi-Source Replication .................................................................................. 53

2.6 Replication and Binary Logging Options and Variables ................................................................. 54

2.6.1 Replication and Binary Logging Option and Variable Reference .......................................... 56

2.6.2 Replication Source Options and Variables ......................................................................... 65

2.6.3 Replica Server Options and Variables ............................................................................... 78

2.6.4 Binary Logging Options and Variables ............................................................................ 136

2.6.5 Global Transaction ID System Variables ......................................................................... 170

2.7 Common Replication Administration Tasks ................................................................................. 177

2.7.1 Checking Replication Status ........................................................................................... 178

2.7.2 Pausing Replication on the Replica ................................................................................. 180

2.7.3 Skipping Transactions .................................................................................................... 181

This section describes how to configure the different types of replication available in MySQL and includes

the setup and configuration required for a replication environment, including step-by-step instructions for

creating a new replication environment. The major components of this section are:

Binary Log File Position Based Replication Configuration Overview

• For a guide to setting up two or more servers for replication using binary log file positions, Section 2.2,

“Setting Up Binary Log File Position Based Replication”, deals with the configuration of the servers and

provides methods for copying data between the source and replicas.

• For a guide to setting up two or more servers for replication using GTID transactions, Section 2.3,

“Replication with Global Transaction Identifiers”, deals with the configuration of the servers.

• Events in the binary log are recorded using a number of formats. These are referred to as statement-

based replication (SBR) or row-based replication (RBR). A third type, mixed-format replication (MIXED),

uses SBR or RBR replication automatically to take advantage of the benefits of both SBR and RBR

formats when appropriate. The different formats are discussed in Section 5.1, “Replication Formats”.

• Detailed information on the different configuration options and variables that apply to replication is

provided in Section 2.6, “Replication and Binary Logging Options and Variables”.

• Once started, the replication process should require little administration or monitoring. However,

for advice on common tasks that you may want to execute, see Section 2.7, “Common Replication

Administration Tasks”.

2.1 Binary Log File Position Based Replication Configuration

Overview

This section describes replication between MySQL servers based on the binary log file position method,

where the MySQL instance operating as the source (where the database changes take place) writes

updates and changes as “events” to the binary log. The information in the binary log is stored in different

logging formats according to the database changes being recorded. Replicas are configured to read the

binary log from the source and to execute the events in the binary log on the replica's local database.

Each replica receives a copy of the entire contents of the binary log. It is the responsibility of the replica to

decide which statements in the binary log should be executed. Unless you specify otherwise, all events in

the source's binary log are executed on the replica. If required, you can configure the replica to process

only events that apply to particular databases or tables.

Important

You cannot configure the source to log only certain events.

Each replica keeps a record of the binary log coordinates: the file name and position within the file that it

has read and processed from the source. This means that multiple replicas can be connected to the source

and executing different parts of the same binary log. Because the replicas control this process, individual

replicas can be connected and disconnected from the server without affecting the source's operation. Also,

because each replica records the current position within the binary log, it is possible for replicas to be

disconnected, reconnect and then resume processing.

The source and each replica must be configured with a unique ID (using the server_id system variable).

In addition, each replica must be configured with information about the source's host name, log file name,

and position within that file. These details can be controlled from within a MySQL session using a CHANGE

REPLICATION SOURCE TO statement (from MySQL 8.0.23) or CHANGE MASTER TO statement (before

MySQL 8.0.23) on the replica. The details are stored within the replica's connection metadata repository

(see Section 5.4, “Relay Log and Replication Metadata Repositories”).

2.2 Setting Up Binary Log File Position Based Replication

This section describes how to set up a MySQL server to use binary log file position based replication.

There are a number of different methods for setting up replication, and the exact method to use depends

Setting Up Binary Log File Position Based Replication

on how you are setting up replication, and whether you already have data in the database on the source

that you want to replicate.

Tip

To deploy multiple instances of MySQL, you can use InnoDB Cluster which enables

you to easily administer a group of MySQL server instances in MySQL Shell.

InnoDB Cluster wraps MySQL Group Replication in a programmatic environment

that enables you easily deploy a cluster of MySQL instances to achieve high

availability. In addition, InnoDB Cluster interfaces seamlessly with MySQL Router,

which enables your applications to connect to the cluster without writing your own

failover process. For similar use cases that do not require high availability, however,

you can use InnoDB ReplicaSet. Installation instructions for MySQL Shell can be

found here.

There are some generic tasks that are common to all setups:

• On the source, you must ensure that binary logging is enabled, and configure a unique server ID. This

might require a server restart. See Section 2.2.1, “Setting the Replication Source Configuration”.

• On each replica that you want to connect to the source, you must configure a unique server ID. This

might require a server restart. See Section 2.2.2, “Setting the Replica Configuration”.

• Optionally, create a separate user for your replicas to use during authentication with the source when

reading the binary log for replication. See Section 2.2.3, “Creating a User for Replication”.

• Before creating a data snapshot or starting the replication process, on the source you should record

the current position in the binary log. You need this information when configuring the replica so that the

replica knows where within the binary log to start executing events. See Section 2.2.4, “Obtaining the

Replication Source Binary Log Coordinates”.

• If you already have data on the source and want to use it to synchronize the replica, you need to create

a data snapshot to copy the data to the replica. The storage engine you are using has an impact on how

you create the snapshot. When you are using MyISAM, you must stop processing statements on the

source to obtain a read-lock, then obtain its current binary log coordinates and dump its data, before

permitting the source to continue executing statements. If you do not stop the execution of statements,

the data dump and the source status information become mismatched, resulting in inconsistent or

corrupted databases on the replicas. For more information on replicating a MyISAM source, see

Section 2.2.4, “Obtaining the Replication Source Binary Log Coordinates”. If you are using InnoDB, you

do not need a read-lock and a transaction that is long enough to transfer the data snapshot is sufficient.

For more information, see InnoDB and MySQL Replication.

• Configure the replica with settings for connecting to the source, such as the host name, login credentials,

and binary log file name and position. See Section 2.2.7, “Setting the Source Configuration on the

Replica”.

• Implement replication-specific security measures on the sources and replicas as appropriate for your

system. See Replication Security.

Note

Certain steps within the setup process require the SUPER privilege. If you do not

have this privilege, it might not be possible to enable replication.

After configuring the basic options, select your scenario:

• To set up replication for a fresh installation of a source and replicas that contain no data, see

Section 2.2.6.1, “Setting Up Replication with New Source and Replicas”.

Setting the Replication Source Configuration

• To set up replication of a new source using the data from an existing MySQL server, see Section 2.2.6.2,

“Setting Up Replication with Existing Data”.

• To add replicas to an existing replication environment, see Section 2.2.8, “Adding Replicas to a

Replication Environment”.

Before administering MySQL replication servers, read this entire chapter and try all statements mentioned

in SQL Statements for Controlling Source Servers, and SQL Statements for Controlling Replica Servers.

Also familiarize yourself with the replication startup options described in Section 2.6, “Replication and

Binary Logging Options and Variables”.

2.2.1 Setting the Replication Source Configuration

To configure a source to use binary log file position based replication, you must ensure that binary logging

is enabled, and establish a unique server ID.

Each server within a replication topology must be configured with a unique server ID, which you can

specify using the server_id system variable. This server ID is used to identify individual servers within

the replication topology, and must be a positive integer between 1 and (2

)−1. The default server_id

value from MySQL 8.0 is 1. You can change the server_id value dynamically by issuing a statement like

this:

SET GLOBAL server_id = 2;

How you organize and select the server IDs is your choice, so long as each server ID is different from

every other server ID in use by any other server in the replication topology. Note that if a value of 0 (which

was the default in earlier releases) was set previously for the server ID, you must restart the server to

initialize the source with your new nonzero server ID. Otherwise, a server restart is not needed when you

change the server ID, unless you make other configuration changes that require it.

Binary logging is required on the source because the binary log is the basis for replicating changes from

the source to its replicas. Binary logging is enabled by default (the log_bin system variable is set to ON).

The --log-bin option tells the server what base name to use for binary log files. It is recommended

that you specify this option to give the binary log files a non-default base name, so that if the host name

changes, you can easily continue to use the same binary log file names (see Known Issues in MySQL). If

binary logging was previously disabled on the source using the --skip-log-bin option, you must restart

the server without this option to enable it.

Note

The following options also have an impact on the source:

• For the greatest possible durability and consistency in a

replication setup using InnoDB with transactions, you should use

innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the source's

my.cnf file.

• Ensure that the skip_networking system variable is not enabled on the

source. If networking has been disabled, the replica cannot communicate with the

source and replication fails.

2.2.2 Setting the Replica Configuration

Each replica must have a unique server ID, as specified by the server_id system variable. If you are

setting up multiple replicas, each one must have a unique server_id value that differs from that of the

source and from any of the other replicas. If the replica's server ID is not already set, or the current value

conflicts with the value that you have chosen for the source or another replica, you must change it.

Creating a User for Replication

The default server_id value is 1. You can change the server_id value dynamically by issuing a

statement like this:

SET GLOBAL server_id = 21;

Note that a value of 0 for the server ID prevents a replica from connecting to a source. If that server ID

value (which was the default in earlier releases) was set previously, you must restart the server to initialize

the replica with your new nonzero server ID. Otherwise, a server restart is not needed when you change

the server ID, unless you make other configuration changes that require it. For example, if binary logging

was disabled on the server and you want it enabled for your replica, a server restart is required to enable

this.

If you are shutting down the replica server, you can edit the [mysqld] section of the configuration file to

specify a unique server ID. For example:

[mysqld]

server-id=21

Binary logging is enabled by default on all servers. A replica is not required to have binary logging enabled

for replication to take place. However, binary logging on a replica means that the replica's binary log can

be used for data backups and crash recovery. Replicas that have binary logging enabled can also be used

as part of a more complex replication topology. For example, you might want to set up replication servers

using this chained arrangement:

A -> B -> C

Here, A serves as the source for the replica B, and B serves as the source for the replica C. For this

to work, B must be both a source and a replica. Updates received from A must be logged by B to

its binary log, in order to be passed on to C. In addition to binary logging, this replication topology

requires the system variable log_replica_updates (from MySQL 8.0.26) or log_slave_updates

(before MySQL 8.0.26) to be enabled. With replica updates enabled, the replica writes updates that are

received from a source and performed by the replica's SQL thread to the replica's own binary log. The

log_replica_updates or log_slave_updates system variable is enabled by default.

If you need to disable binary logging or replica update logging on a replica, you can do this by specifying

the --skip-log-bin and --log-replica-updates=OFF or --log-slave-updates=OFF options

for the replica. If you decide to re-enable these features on the replica, remove the relevant options and

restart the server.

2.2.3 Creating a User for Replication

Each replica connects to the source using a MySQL user name and password, so there must be a

user account on the source that the replica can use to connect. The user name is specified by the

SOURCE_USER | MASTER_USER option of the CHANGE REPLICATION SOURCE TO statement (from

MySQL 8.0.23) or CHANGE MASTER TO statement (before MySQL 8.0.23) when you set up a replica. Any

account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege.

You can choose to create a different account for each replica, or connect to the source using the same

account for each replica.

Although you do not have to create an account specifically for replication, you should be aware that the

replication user name and password are stored in plain text in the replica's connection metadata repository

mysql.slave_master_info (see Section 5.4.2, “Replication Metadata Repositories”). Therefore, you

may want to create a separate account that has privileges only for the replication process, to minimize the

possibility of compromise to other accounts.

To create a new account, use CREATE USER. To grant this account the privileges required for replication,

use the GRANT statement. If you create an account solely for the purposes of replication, that account

Obtaining the Replication Source Binary Log Coordinates

needs only the REPLICATION SLAVE privilege. For example, to set up a new user, repl, that can

connect for replication from any host within the example.com domain, issue these statements on the

source:

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

See Account Management Statements, for more information on statements for manipulation of user

accounts.

Important

To connect to the source using a user account that authenticates with the

caching_sha2_password plugin, you must either set up a secure connection

as described in Setting Up Replication to Use Encrypted Connections, or enable

the unencrypted connection to support password exchange using an RSA key pair.

The caching_sha2_password authentication plugin is the default for new users

created from MySQL 8.0 (for details, see Caching SHA-2 Pluggable Authentication).

If the user account that you create or use for replication (as specified by the

MASTER_USER option) uses this authentication plugin, and you are not using a

secure connection, you must enable RSA key pair-based password exchange for a

successful connection.

2.2.4 Obtaining the Replication Source Binary Log Coordinates

To configure the replica to start the replication process at the correct point, you need to note the source's

current coordinates within its binary log.

Warning

This procedure uses FLUSH TABLES WITH READ LOCK, which blocks COMMIT

operations for InnoDB tables.

If you are planning to shut down the source to create a data snapshot, you can optionally skip this

procedure and instead store a copy of the binary log index file along with the data snapshot. In that

situation, the source creates a new binary log file on restart. The source binary log coordinates where the

replica must start the replication process are therefore the start of that new file, which is the next binary log

file on the source following after the files that are listed in the copied binary log index file.

To obtain the source binary log coordinates, follow these steps:

1. Start a session on the source by connecting to it with the command-line client, and flush all tables and

block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

mysql> FLUSH TABLES WITH READ LOCK;

Warning

Leave the client from which you issued the FLUSH TABLES statement running

so that the read lock remains in effect. If you exit the client, the lock is released.

2. In a different session on the source, use the SHOW MASTER STATUS statement to determine the

current binary log file name and position:

mysql> SHOW MASTER STATUS\G

*************************** 1. row ***************************

File: mysql-bin.000003

Position: 73

Binlog_Do_DB: test

Choosing a Method for Data Snapshots

Binlog_Ignore_DB: manual, mysql

Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

1 row in set (0.00 sec)

The File column shows the name of the log file and the Position column shows the position within

the file. In this example, the binary log file is mysql-bin.000003 and the position is 73. Record

these values. You need them later when you are setting up the replica. They represent the replication

coordinates at which the replica should begin processing new updates from the source.

If the source has been running previously with binary logging disabled, the log file name and position

values displayed by SHOW MASTER STATUS or mysqldump --master-data are empty. In that case,

the values that you need to use later when specifying the source's binary log file and position are the

empty string ('') and 4.

You now have the information you need to enable the replica to start reading from the source's binary log

in the correct place to start replication.

The next step depends on whether you have existing data on the source. Choose one of the following

options:

• If you have existing data that needs be to synchronized with the replica before you start replication, leave

the client running so that the lock remains in place. This prevents any further changes being made, so

that the data copied to the replica is in synchrony with the source. Proceed to Section 2.2.5, “Choosing a

Method for Data Snapshots”.

• If you are setting up a new source and replica combination, you can exit the first session to release

the read lock. See Section 2.2.6.1, “Setting Up Replication with New Source and Replicas” for how to

proceed.

2.2.5 Choosing a Method for Data Snapshots

If the source database contains existing data it is necessary to copy this data to each replica. There are

different ways to dump the data from the source database. The following sections describe possible

options.

To select the appropriate method of dumping the database, choose between these options:

• Use the mysqldump tool to create a dump of all the databases you want to replicate. This is the

recommended method, especially when using InnoDB.

• If your database is stored in binary portable files, you can copy the raw data files to a replica. This can

be more efficient than using mysqldump and importing the file on each replica, because it skips the

overhead of updating indexes as the INSERT statements are replayed. With storage engines such as

InnoDB this is not recommended.

• Use MySQL Server's clone plugin to transfer all the data from an existing replica to a clone. For

instructions to use this method, see Cloning for Replication.

Tip

To deploy multiple instances of MySQL, you can use InnoDB Cluster which enables

you to easily administer a group of MySQL server instances in MySQL Shell.

InnoDB Cluster wraps MySQL Group Replication in a programmatic environment

that enables you easily deploy a cluster of MySQL instances to achieve high

availability. In addition, InnoDB Cluster interfaces seamlessly with MySQL Router,

which enables your applications to connect to the cluster without writing your own

failover process. For similar use cases that do not require high availability, however,

Choosing a Method for Data Snapshots

you can use InnoDB ReplicaSet. Installation instructions for MySQL Shell can be

found here.

2.2.5.1 Creating a Data Snapshot Using mysqldump

To create a snapshot of the data in an existing source database, use the mysqldump tool. Once the data

dump has been completed, import this data into the replica before starting the replication process.

The following example dumps all databases to a file named dbdump.db, and includes the --master-

data option which automatically appends the CHANGE REPLICATION SOURCE TO | CHANGE MASTER

TO statement required on the replica to start the replication process:

$> mysqldump --all-databases --master-data > dbdump.db

Note

If you do not use --master-data, then it is necessary to lock all tables in a

separate session manually. See Section 2.2.4, “Obtaining the Replication Source

Binary Log Coordinates”.

It is possible to exclude certain databases from the dump using the mysqldump tool. If you want to choose

which databases to include in the dump, do not use --all-databases. Choose one of these options:

• Exclude all the tables in the database using --ignore-table option.

• Name only those databases which you want dumped using the --databases option.

Note

By default, if GTIDs are in use on the source (gtid_mode=ON), mysqldump

includes the GTIDs from the gtid_executed set on the source in the dump

output to add them to the gtid_purged set on the replica. If you are dumping only

specific databases or tables, it is important to note that the value that is included

by mysqldump includes the GTIDs of all transactions in the gtid_executed

set on the source, even those that changed suppressed parts of the database, or

other databases on the server that were not included in the partial dump. Check

the description for mysqldump's --set-gtid-purged option to find the outcome

of the default behavior for the MySQL Server versions you are using, and how to

change the behavior if this outcome is not suitable for your situation.

For more information, see mysqldump — A Database Backup Program.

To import the data, either copy the dump file to the replica, or access the file from the source when

connecting remotely to the replica.

2.2.5.2 Creating a Data Snapshot Using Raw Data Files

This section describes how to create a data snapshot using the raw files which make up the database.

Employing this method with a table using a storage engine that has complex caching or logging algorithms

requires extra steps to produce a perfect “point in time” snapshot: the initial copy command could leave

out cache information and logging updates, even if you have acquired a global read lock. How the storage

engine responds to this depends on its crash recovery abilities.

If you use InnoDB tables, you can use the mysqlbackup command from the MySQL Enterprise

Backup component to produce a consistent snapshot. This command records the log name and offset

corresponding to the snapshot to be used on the replica. MySQL Enterprise Backup is a commercial

product that is included as part of a MySQL Enterprise subscription. See MySQL Enterprise Backup

Overview for detailed information.

Choosing a Method for Data Snapshots

This method also does not work reliably if the source and replica have different values for

ft_stopword_file, ft_min_word_len, or ft_max_word_len and you are copying tables having full-

text indexes.

Assuming the above exceptions do not apply to your database, use the cold backup technique to obtain a

reliable binary snapshot of InnoDB tables: do a slow shutdown of the MySQL Server, then copy the data

files manually.

To create a raw data snapshot of MyISAM tables when your MySQL data files exist on a single file system,

you can use standard file copy tools such as cp or copy, a remote copy tool such as scp or rsync, an

archiving tool such as zip or tar, or a file system snapshot tool such as dump. If you are replicating only

certain databases, copy only those files that relate to those tables. For InnoDB, all tables in all databases

are stored in the system tablespace files, unless you have the innodb_file_per_table option enabled.

The following files are not required for replication:

• Files relating to the mysql database.

• The replica's connection metadata repository file master.info, if used; the use of this file is now

deprecated (see Section 5.4, “Relay Log and Replication Metadata Repositories”).

• The source's binary log files, with the exception of the binary log index file if you are going to use this to

locate the source binary log coordinates for the replica.

• Any relay log files.

Depending on whether you are using InnoDB tables or not, choose one of the following:

If you are using InnoDB tables, and also to get the most consistent results with a raw data snapshot, shut

down the source server during the process, as follows:

1. Acquire a read lock and get the source's status. See Section 2.2.4, “Obtaining the Replication Source

Binary Log Coordinates”.

2. In a separate session, shut down the source server:

$> mysqladmin shutdown

3. Make a copy of the MySQL data files. The following examples show common ways to do this. You need

to choose only one of them:

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

$> zip -r /tmp/db.zip ./data

$> rsync --recursive ./data /tmp/dbdata

4. Restart the source server.

If you are not using InnoDB tables, you can get a snapshot of the system from a source without shutting

down the server as described in the following steps:

1. Acquire a read lock and get the source's status. See Section 2.2.4, “Obtaining the Replication Source

Binary Log Coordinates”.

2. Make a copy of the MySQL data files. The following examples show common ways to do this. You need

to choose only one of them:

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

$> zip -r /tmp/db.zip ./data

$> rsync --recursive ./data /tmp/dbdata

3. In the client where you acquired the read lock, release the lock:

Setting Up Replicas

mysql> UNLOCK TABLES;

Once you have created the archive or copy of the database, copy the files to each replica before starting

the replication process.

2.2.6 Setting Up Replicas

The following sections describe how to set up replicas. Before you proceed, ensure that you have:

• Configured the source with the necessary configuration properties. See Section 2.2.1, “Setting the

Replication Source Configuration”.

• Obtained the source status information, or a copy of the source's binary log index file made during

a shutdown for the data snapshot. See Section 2.2.4, “Obtaining the Replication Source Binary Log

Coordinates”.

• On the source, released the read lock:

mysql> UNLOCK TABLES;

• On the replica, edited the MySQL configuration. See Section 2.2.2, “Setting the Replica Configuration”.

The next steps depend on whether you have existing data to import to the replica or not. See Section 2.2.5,

“Choosing a Method for Data Snapshots” for more information. Choose one of the following:

• If you do not have a snapshot of a database to import, see Section 2.2.6.1, “Setting Up Replication with

New Source and Replicas”.

• If you have a snapshot of a database to import, see Section 2.2.6.2, “Setting Up Replication with Existing

Data”.

2.2.6.1 Setting Up Replication with New Source and Replicas

When there is no snapshot of a previous database to import, configure the replica to start replication from

the new source.

To set up replication between a source and a new replica:

1. Start up the replica.

2. Execute a CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO statement on the replica to set

the source configuration. See Section 2.2.7, “Setting the Source Configuration on the Replica”.

Perform these replica setup steps on each replica.

This method can also be used if you are setting up new servers but have an existing dump of the

databases from a different server that you want to load into your replication configuration. By loading the

data into a new source, the data is automatically replicated to the replicas.

If you are setting up a new replication environment using the data from a different existing database server

to create a new source, run the dump file generated from that server on the new source. The database

updates are automatically propagated to the replicas:

$> mysql -h source < fulldb.dump

2.2.6.2 Setting Up Replication with Existing Data

When setting up replication with existing data, transfer the snapshot from the source to the replica before

starting replication. The process for importing data to the replica depends on how you created the snapshot

of data on the source.