Multi-Primary Replication for MySQL, MariaDB and Percona

Multi-primary replication allows two database servers to replicate changes to each other. Compared with primary-secondary replication, it provides higher availability and write distribution but requires more careful conflict management.

In this article

Replication use cases
Automatic installation
Manual installation
Configure both primaries
Enable replication
Verify replication

Primary-primary replication scheme — Primary-primary replication allows both database servers to accept writes.

Replication Use Cases

	Primary-Secondary	Multi-Primary
Benefits	Simplicity, easier setup and maintenance	Higher availability, load balancing, geographical distribution
Drawbacks	Single point of failure, writes limited to primary	Higher complexity and potential conflicts during concurrent writes
Use cases	Read-heavy workloads, backup/disaster recovery, strict consistency requirements	Write-heavy workloads, high availability, scalability, geo-distributed applications with local writes

Choose Primary-Secondary for simplicity, strong consistency, and read-heavy workloads.
Choose Primary-Primary for high availability, write scalability, and distributed systems that require local writes.

Automatic Installation

Click New Environment.
Select MySQL, MariaDB, or Percona.
Enable Auto-Clustering.
Select Primary-Primary from the Scheme list.
Adjust other environment parameters and click Create.

Database auto-clustering — Choose Primary-Primary replication through Auto-Clustering.

Alternatively, install the preconfigured MySQL/MariaDB/Percona Cluster package from Marketplace, select the database stack and replication type, and click Install.

Database cluster marketplace — Install a preconfigured database cluster from Marketplace.

Manual Installation

Create at least two database servers. The source example creates two MySQL environments named first-primary and second-primary. The same workflow is applicable to MariaDB and Percona with minimal adjustments.

First primary topology wizard — Create the first primary MySQL environment.

Two database environments — Create or clone the second primary environment.

Configure Both Primary Servers

First Primary

Open /etc/my.cnf on first-primary and set:

server-id = 1
binlog-do-db = example
binlog-do-db = teste
log-bin = /var/log/mysql/mysql-bin.log
auto_increment_increment = 1
auto_increment_offset = 1

Configure first primary — Open the first primary database configuration.

Edit first primary my.cnf — Set the first primary server replication parameters.

server-idUnique identifier for each replication server.

binlog-do-dbRestricts binary logging to the specified databases. The source example uses example and teste.

log-binEnables binary logging and defines the log-sequence basename.

auto_increment_incrementControls the interval between successive auto-increment values.

auto_increment_offsetCan help avoid multi-primary conflicts. The source recommends aligning this value with server-id.

Source example databases

The source states that example and teste should not be created at this point. After understanding the setup, replace them with the database names required by your application.

Save the file and restart MySQL.

Restart first MySQL — Restart the first primary database.

Second Primary

Open /etc/my.cnf on second-primary and set unique server and offset values:

server-id = 2
binlog-do-db = example
binlog-do-db = teste
log-bin = /var/log/mysql/mysql-bin.log
auto_increment_increment = 1
auto_increment_offset = 2

Second primary my.cnf — Configure unique server and auto-increment values on the second primary.

Save the file and restart MySQL.

Enable Primary-Primary Replication

Open phpMyAdmin on first-primary, go to User accounts, create a replication user, and grant replication client and replication slave privileges.

Open database in browser — Open the first primary database administration panel.

Add replication user — Add the replication user in phpMyAdmin.

Replication user credentials — Specify the replication username and password.

Replication user privileges — Grant replication client and replication slave privileges.

Open the Status tab and record the binary-log File and Position. Repeat the same steps on the second primary, using the same replication username and recording its log values as well.

First primary status — Record the binary-log File and Position values.

Cloned environment UUID

If the second database environment was cloned, give it a unique database-server UUID. The source suggests changing the server_uuid variable or deleting /var/lib/mysql/auto.cnf and restarting MySQL so a new UUID is generated automatically.

Replication can be configured from the phpMyAdmin Replication tab, the SQL tab, or through SSH. The source example uses SQL commands.

MySQL 8.0.22+

STOP REPLICA;

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'first-primary',
    SOURCE_USER = 'replicator',
    SOURCE_PASSWORD = 'passw0rd',
    SOURCE_LOG_FILE = 'binlog_file_name',
    SOURCE_LOG_POS = binlog_file_position;

START REPLICA;

Legacy MySQL and MariaDB

STOP SLAVE;

CHANGE MASTER TO
    MASTER_HOST = 'first-primary',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'passw0rd',
    MASTER_LOG_FILE = 'binlog_file_name',
    MASTER_LOG_POS = binlog_file_position;

START SLAVE;

Start replication on second primary — Configure the second primary to replicate from the first.

SOURCE_HOST / MASTER_HOST: hostname or IP of the other primary.
SOURCE_USER / MASTER_USER: replication username.
SOURCE_PASSWORD / MASTER_PASSWORD: replication-user password.
SOURCE_LOG_FILE / MASTER_LOG_FILE: binary-log file from the other primary.
SOURCE_LOG_POS / MASTER_LOG_POS: binary-log position from the other primary.

Check the replication status from phpMyAdmin or with SHOW REPLICA STATUS; / SHOW SLAVE STATUS;.

Second primary status — Verify replication status on the second primary.

Repeat the same replication setup on first-primary, this time using the connection and binary-log information from second-primary.

Start replication on first primary — Configure the first primary to replicate from the second.

Verify Replication

Create a new database named example on the first primary. The source example shows it marked as replicated because the same database name is configured through binlog-do-db.

Create example database — Create the replicated example database on the first primary.

Open the second primary and confirm that the database appears. Then create a new table in that database on the second primary.

Add table to replicated database — Create a table on the second primary to test reverse replication.

Return to the first primary and confirm that the new table is present there as well. This verifies replication in both directions.

Replicated table on first primary — Verify that the new table appears on the first primary.

Expected Result

Both database servers replicate changes to each other. A database created on the first primary appears on the second, and a table created on the second appears on the first, confirming bidirectional multi-primary replication.

Important Notes

Use a unique server-id for every primary.
Use different auto_increment_offset values to reduce auto-increment conflicts.
For cloned database nodes, ensure each server has a unique UUID.
Record the correct binary-log File and Position from each primary before configuring the other side.
Primary-primary replication is more complex than primary-secondary and can encounter conflicts during concurrent writes.