MENU


Replication

Replication is the process by which selected data from one database server (the source, historically called the master) is replicated to one or more other servers (replicas, historically called slaves). It is asynchronous by default, meaning replicas need not be permanently connected to receive updates. Writes normally occur only on the source; reads can take place on replicas.

Replication spreads read load across multiple replicas to improve performance, can run backup services without affecting the source, and can distribute data over long distances to reduce transmission overhead. During replication, a replica reads the source's binary log and executes selected events from it against its own local database. A replica can itself act as the source of another replica, so a replication setup may use tree, linear, or circular topologies.

CHANGE REPLICATION SOURCE TO

CHANGE REPLICATION SOURCE TO changes the parameters a replica uses to connect to its source and read data, and updates the replication metadata repositories.

As of MySQL 8.4, the legacy CHANGE MASTER TO statement and all of its MASTER_* option names (MASTER_HOST, MASTER_LOG_FILE, MASTER_AUTO_POSITION, and the rest) have been removed entirely – only CHANGE REPLICATION SOURCE TO and its SOURCE_* options are recognized. Scripts, tooling, or documentation carried over from MySQL 8.0 that still use the old MASTER/SLAVE statement names will fail against 8.4+ and must be updated to the terminology used throughout this page.

CHANGE REPLICATION SOURCE TO option [, option] ... [channel_option]

option:
    SOURCE_HOST = 'host_name' | SOURCE_USER = 'user_name'
  | SOURCE_PASSWORD = 'password' | SOURCE_PORT = port_num
  | SOURCE_LOG_FILE = 'source_log_name' | SOURCE_LOG_POS = source_log_pos
  | SOURCE_AUTO_POSITION = {0|1} | SOURCE_CONNECT_RETRY = interval
  | SOURCE_SSL = {0|1} | SOURCE_SSL_CA = 'ca_file_name' | ...
  | PRIVILEGE_CHECKS_USER = {NULL | 'account'}
  | REQUIRE_ROW_FORMAT = {0|1} | GTID_ONLY = {0|1}
  | RELAY_LOG_FILE = 'relay_log_name' | RELAY_LOG_POS = relay_log_pos

channel_option:
    FOR CHANNEL channel

A representative subset of the many CHANGE REPLICATION SOURCE TO options; see the full syntax for connection retry counts, heartbeat interval, compression, and TLS options.

Replication privilege checks verify that the replication user on the source has the privileges needed to execute the replicated statements on the replica:

STOP REPLICA FOR CHANNEL 'channel_1';
CHANGE REPLICATION SOURCE TO
    PRIVILEGE_CHECKS_USER = 'priv_repl'@'%.example.com',
    REQUIRE_ROW_FORMAT = 1 FOR CHANNEL 'channel_1';
FLUSH PRIVILEGES;
START REPLICA FOR CHANNEL 'channel_1';

Configures privilege-checked replication on one channel.

CHANGE REPLICATION FILTER sets replication filtering rules on a replica, equivalent to starting the replica's mysqld with options such as --replicate-do-db or --replicate-wild-ignore-table:

CHANGE REPLICATION FILTER filter[, filter][, ...] [FOR CHANNEL channel]

filter:
    REPLICATE_DO_DB = (db_list) | REPLICATE_IGNORE_DB = (db_list)
  | REPLICATE_DO_TABLE = (tbl_list) | REPLICATE_IGNORE_TABLE = (tbl_list)
  | REPLICATE_WILD_DO_TABLE = (wild_tbl_list)
  | REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)
  | REPLICATE_REWRITE_DB = (db_pair_list)

Filters which databases/tables replicate.

START REPLICA [thread_types] [until_option] [connection_options] [channel_option]
STOP REPLICA [thread_types] [channel_option]

thread_types:
    [thread_type [, thread_type] ...]

thread_type:
    IO_THREAD | SQL_THREAD
Starts/stops the replication threads. IO_THREAD reads events from the source; SQL_THREAD executes received events – either can be stopped independently.
SELECT SOURCE_POS_WAIT('source_log_file', source_log_pos [, timeout][, channel]);
RESET REPLICA [ALL] [FOR CHANNEL channel];
SOURCE_POS_WAIT() blocks until the replica has read and executed events up to a given binary log position; RESET REPLICA makes the replica forget its position in the source's binary log.
START GROUP_REPLICATION [USER='user_name'] [, PASSWORD='user_pass'] [, DEFAULT_AUTH='plugin_name'];
STOP GROUP_REPLICATION;

Starts/stops Group Replication (see below).

Setting Up

Replication in MySQL requires the source and replica to begin with an identical set of data; every subsequent query that modifies data on the source is then applied to the replica as well.

  1. Configure the source – enable binary logging and assign it a unique server ID by editing my.cnf/my.ini: log-bin=mysql-bin, server-id=1. Restart the server.
  2. Configure the replicas – assign each replica a unique server-id (e.g. server-id=2) and restart.
  3. Create a replication user on the source that the replica can connect as: CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
  4. Determine the source's binary log coordinates – run FLUSH TABLES WITH READ LOCK; to block writes, then in a separate session run SHOW MASTER STATUS; to read the current binary log file name and position (use an empty string and position 4 if binary logging was not previously enabled).
  5. Copy a data snapshot – e.g. mysqldump --all-databases --master-data > dbdump.db on the source, copy the file to each replica, and import it there with mysql < dbdump.db (or copy the raw data files for very large databases).
  6. Start replication – release the read lock on the source with UNLOCK TABLES;, then on each replica issue CHANGE REPLICATION SOURCE TO with the source's host, credentials, and logged coordinates, followed by START REPLICA;.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

FilePosition
mysql-bin.00003314665

Using SSL

To use an encrypted connection, specify the certificate and key paths in the [mysqld] section of the source's my.cnf/my.ini: ssl-ca=cacert.pem, ssl-cert=server-cert.pem, ssl-key=server-key.pem, where ssl-ca identifies the Certificate Authority certificate, ssl-cert the server's public key, and ssl-key its private key.

On the replica, corresponding options can be set in the [client] section; restart the replica with --skip-slave-start to prevent it from connecting before SSL is configured, then run CHANGE REPLICATION SOURCE TO ... SOURCE_SSL=1; followed by START REPLICA; (or specify SOURCE_SSL_CA, SOURCE_SSL_CERT, and SOURCE_SSL_KEY directly in the CHANGE REPLICATION SOURCE TO statement itself).

GTID

Transaction-based replication using global transaction identifiers (GTIDs) allows seamless failover, promoting a replica to source when the source crashes. With GTIDs, communication is asynchronous and a replica need not be connected to the source at all times; when it reconnects, it must determine which of the source's modifications it has already applied and which it has not.

A GTID is represented as source_id:transaction_id, uniquely identifying a transaction across the whole replication setup: source_id is usually the originating server's server_uuid, and transaction_id increases by one for each transaction executed on the source, e.g. 3E11FA47-71CA-11E1-9E33-C80AA9429562:23. Each transaction in the binary log is associated with a GTID; a replica retains the same GTID after committing the corresponding transaction rather than generating a new one.

  1. Make source and replica read-only: SET @@global.read_only = ON;, and let the replica catch up.
  2. Stop both servers: mysqladmin -uusername -p shutdown.
  3. Restart both with GTIDs enabled – source: mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &; replica: the same plus --skip-slave-start.
  4. Connect the replica to the source with CHANGE REPLICATION SOURCE TO SOURCE_HOST = host, SOURCE_PORT = port, SOURCE_USER = user, SOURCE_PASSWORD = password, SOURCE_AUTO_POSITION = 1; then START REPLICA;.
  5. Disable read-only on the source: SET @@global.read_only = OFF;.
GTIDs in the binary log can be seen via SHOW MASTER STATUS, SHOW SLAVE STATUS, SHOW BINLOG EVENTS, or the gtid_executed system variable; consecutive GTIDs may be collapsed into a range, e.g. 3E11FA47-71CA-11E1-9E33-C80AA9429562:20-23. During failover, a replica sends the source the range of GTIDs it has committed, and the source sends back any transactions the replica is missing.

Setting up a new replica by executing the source's entire transaction history can be slow; the data and transactions can instead be copied directly (e.g. importing a mysqldump --master-data --set-gtid-purged dump, or copying the source's binary log files directly and pointing the replica at them). When copying entire binary log files is impractical, an empty transaction can be committed on the replica for each GTID already accounted for in the source's gtid_executed:

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

FLUSH LOGS;
PURGE BINARY LOGS TO 'source-bin.00000N';

Reinstating identifiers with empty transactions, then rotating and purging so the replica can catch up. Alternatively, the gtid_purged variable can be set directly on the replica from the source's gtid_executed value.

GTID-based replication does not support non-transactional storage engines such as MyISAM, CREATE TABLE ... SELECT statements, temporary tables, sql_slave_skip_counter (use the source's gtid_executed instead), or importing a mysqldump dump into a GTID-enabled server that already has GTIDs in its binary log.

Statement-based vs. Row-based

Binary log events are recorded in statement-based format, row-based format, or (with mixed-format logging) a real-time mix of the two chosen per event. Statement-based logging (the default) logs the statements that made changes; row-based logging logs the resulting changes to individual rows.

Statement-based replication (SBR) requires much less storage and completes faster, but is unsafe compared to row-based replication (RBR): not every statement that modifies data can be safely replicated with it, it requires more row-level locks for INSERT/UPDATE/DELETE, it must re-evaluate and re-execute complex statements, and it requires deterministic user-defined functions on replicas.

Statements considered unsafe for SBR generate a warning under statement-based logging, and are logged in row-based format under MIXED logging (with other statements still logged in statement-based format). These include statements containing non-deterministic system functions such as FOUND_ROWS(), RAND(), UUID(), and SLEEP(); references to system variables, UDFs, or fulltext plugins; triggers or stored programs that update a table with an AUTO_INCREMENT column; INSERT ... ON DUPLICATE KEY UPDATE on tables with multiple primary/unique keys; updates using LIMIT; accesses to log tables; non-transactional operations following transactional ones; and LOAD DATA INFILE. Functions such as CURRENT_TIMESTAMP(), NOW(), UNIX_TIMESTAMP(), and LAST_INSERT_ID() are, by contrast, safe for SBR despite being non-deterministic in general.

Administration

SHOW SLAVE STATUS reports replication status on a replica; SHOW PROCESSLIST on the source shows connected replicas; and if replicas were started with --report-host, SHOW SLAVE HOSTS on the source reports basic replica information.

[STOP|START] SLAVE [IO_THREAD|SQL_THREAD]

Pauses or resumes replication on a replica, optionally just one thread.

--replicate-wild-do-table=databaseX.%

A replica-side command-line option that restricts replication to a specific database, letting different replicas replicate different databases.

CHANGE REPLICATION SOURCE TO SOURCE_DELAY = N;

Delays replication so the replica lags the source by at least N seconds. (The equivalent old-style statement, CHANGE MASTER TO MASTER_DELAY = N, no longer works as of MySQL 8.4.)

Group Replication

Group Replication is a high-availability, fault-tolerant feature, introduced in MySQL 5.7, that forms a group of MySQL server instances (members) in which each server replicates the others, using synchronous replication and automatic failover.

In single-primary mode, MySQL 8.4 also adds preemptive garbage collection, controlled by the group_replication_preemptive_garbage_collection system variable. When enabled, the primary can reclaim certification information more aggressively instead of always waiting for every secondary to acknowledge a transaction first, which helps limit memory growth from certification data in write-heavy single-primary deployments.

Group Replication requires data to be stored in the transactional InnoDB storage engine. To configure an instance for it:

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="s1:33061"
group_replication_group_seeds="s1:33061,s2:33061,s3:33061"
group_replication_bootstrap_group=off

Configuration options set per instance; use a different server_id on each member.

A replication user with the correct permissions must be set up so Group Replication can establish member-to-member replication channels, and the plugin must be installed:

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user',
    SOURCE_PASSWORD='password'
    FOR CHANNEL 'group_replication_recovery';

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

Setting up the replication user and installing the Group Replication plugin. See also Plugins.

Starting a group for the first time is called bootstrapping, controlled by the group_replication_bootstrap_group system variable, and must be performed by a single server, only once – this value is deliberately not persisted in the option file, since leaving it ON would cause the server to bootstrap a second, conflicting group with the same name on restart.

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='password';
SET GLOBAL group_replication_bootstrap_group=OFF;

SELECT * FROM performance_schema.replication_group_members;

Bootstrapping the group on the first member, then inspecting membership.

Group Replication also exposes built-in management functions, such as group_replication_set_as_primary(member_uuid), group_replication_switch_to_single_primary_mode(), group_replication_switch_to_multi_primary_mode(), and group_replication_set_write_concurrency(instances). 

-- On the source: enable binary logging via my.cnf, then create a replication user
CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

-- On the source: capture binary log coordinates for the snapshot
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
UNLOCK TABLES;

-- On each replica: point it at the source and start replicating
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='source2.example.com',
    SOURCE_USER='replication',
    SOURCE_PASSWORD='password',
    SOURCE_PORT=3306,
    SOURCE_LOG_FILE='source2-bin.001',
    SOURCE_LOG_POS=4,
    SOURCE_CONNECT_RETRY=10;

START REPLICA;
SHOW REPLICA STATUS;

-- GTID-based replication: connect using auto-position instead of file/pos
SET @@global.read_only = ON;

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'source.example.com',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'password',
    SOURCE_AUTO_POSITION = 1;

START REPLICA;
SET @@global.read_only = OFF;

-- Inspecting GTID state
SHOW MASTER STATUS;
SELECT @@GLOBAL.gtid_executed;

-- Create the replication-recovery user
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN, BACKUP_ADMIN, GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE REPLICATION SOURCE TO
    SOURCE_USER='rpl_user',
    SOURCE_PASSWORD='password'
    FOR CHANNEL 'group_replication_recovery';

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- Bootstrap the group from the first member only
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION USER='rpl_user', PASSWORD='password';
SET GLOBAL group_replication_bootstrap_group=OFF;

SELECT * FROM performance_schema.replication_group_members;

-- Single-primary mode only, MySQL 8.4+: reclaim certification info more
-- aggressively to limit memory growth under heavy write load
SET PERSIST group_replication_preemptive_garbage_collection = ON;