Skip to content

Databases Updated 2026-05-29

Primary-secondary replication for MySQL/MariaDB/Percona

Set up primary-secondary replication on Stack Harbor PaaS for MySQL, MariaDB, and Percona — automatic via auto-clustering, or manual via two environments.

Primary-secondary replication writes change events on one database (the primary) into a binary log that one or more secondaries read and apply. This page covers the use cases, the automated setup path on Stack Harbor PaaS, and the manual setup procedure for MySQL, MariaDB, and Percona.

The replication can be synchronous (changes are applied on primary and secondary in the same transaction) or asynchronous (the secondary trails the primary by some amount of time). MySQL-family asynchronous replication is the default; synchronous replication is available via Galera (a separate cluster topology).

Use cases

  • Backups. Run the backup against the secondary. The secondary can be paused without affecting the primary, so the primary keeps serving traffic during the backup window.
  • Scale-out reads. Direct read traffic to one or more secondaries; keep writes on the primary. Works well when reads dominate writes.
  • Spreading the load. Replicate different databases (or schemas) to different secondaries — for example, sales data to the analytics secondary, application data to the application secondary.
  • Long-distance distribution. Run a secondary in another region, replicating asynchronously, to serve local reads at lower latency.
  • Failover. Pair a primary with a secondary and a monitor; if the primary fails, promote the secondary.
  • TLS-encrypted replication. Encrypt the binlog stream between primary and secondary with an SSL certificate on each side.

Automatic installation

Stack Harbor PaaS provisions a primary-secondary cluster from the topology wizard in a few clicks:

  1. Click New Environment in the dashboard.
  2. Pick MySQL, MariaDB, or Percona in the topology wizard.
  3. Enable Auto-Clustering.
  4. Pick Primary-Secondary from the Scheme drop-down.
  5. Adjust the other parameters and click Create.

Alternatively, install the pre-packaged MySQL / MariaDB / Percona Cluster application from the platform marketplace. Pick the engine and the replication scheme during install, set environment parameters, click Install.

After a couple of minutes, the cluster is up and configured.

Manual installation

When you want full control over the replication setup — custom binlog format, custom user privileges, custom skip-error policy — set it up by hand against two environments. The procedure works for both MySQL and MariaDB (commands are noted where MySQL 8.0.22+ uses the newer syntax).

1. Create two environments

  1. Log into the dashboard, click New Environment.
  2. Pick the engine (e.g. MariaDB), set the cloudlet limit, name the environment primary-db, click Create.
  3. Repeat for secondary-db, or clone the first environment.

You now have two identical environments.

2. Configure the primary

Click Config on the primary database node, open my.cnf, and add (or uncomment) the following:

server-id = 1
log-bin = mysql-bin
binlog-format = mixed

Use the mixed binlog format — it handles operations with foreign keys cleanly. Do not use statement; it will produce replication errors on common foreign-key workloads.

Save the changes and Restart the database to apply the new configuration.

Open phpMyAdmin via Open in Browser on the primary, sign in with the root credentials from the create-time email, navigate to User accounts, click Add user account. Set the name and password for the replication user, scroll down, and grant the REPLICATION CLIENT and REPLICATION SLAVE administration privileges. Click Go.

Switch to the Status tab and note the log File and Position values — you’ll need them when setting up the secondary.

3. Configure the secondary

Back in the dashboard, click Config on the secondary database node, open my.cnf, and add:

server-id = 2
slave-skip-errors = all

A note on slave-skip-errors = all: this skips every error from the primary. Useful during development to keep replication running while you find and fix bugs. In production, replace it with a list of specific errors to skip, so a real data-corruption issue surfaces instead of being silently swallowed.

Open /etc/phpMyAdmin/config.inc.php and append:

$cfg['AllowArbitraryServer'] = true;

Save and Restart the secondary database.

Open the Web SSH console on the secondary and connect to the database:

mysql -u root -p

4. Point the secondary at the primary

You need these values from the primary setup:

  • Primary host — CNAME or IP of the primary database node (from the dashboard).
  • Replication user name and password (created in step 2).
  • Primary’s log file (from the Status tab — mysql-bin.000001 or similar).
  • Primary’s log position (also from Status).

For MariaDB, and for MySQL versions before 8.0.22 — deprecated but still backward-compatible on current MySQL and Percona:

CHANGE MASTER TO
    MASTER_HOST='node{node_id}-primary-db.{domain}',
    MASTER_USER='replication',
    MASTER_PASSWORD='passw0rd',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=865;

For MySQL 8.0.22+ and current Percona, the modern syntax is preferred:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='node{node_id}-primary-db.{domain}',
    SOURCE_USER='replication',
    SOURCE_PASSWORD='passw0rd',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=865;

5. Start the replica

For MySQL 8.0.22+:

START REPLICA;

For MariaDB before 10.5.1 and MySQL before 8.0.22:

START SLAVE;

To check replication is healthy, open phpMyAdmin on the secondary, go to Status, and confirm replication is running.

6. Verify

Create a test database on the primary (e.g. replication_test) — the same database should appear on the secondary within seconds. If it doesn’t, check the secondary’s replication status via SHOW REPLICA STATUS\G (or SHOW SLAVE STATUS\G on older versions) for errors.

See also