Primary-Secondary Replication

MySQL / MariaDB / Percona Primary-Secondary Replication

Primary-secondary replication copies updates from one primary database server to one or more secondary servers. It can support scale-out reads, backups, analytics, data distribution, security, and failover-oriented architectures.

In this article
Primary-secondary replication scheme
Primary-secondary replication copies updates from the primary to one or more secondary databases.

Replication Use Cases

  • Backups: back up a secondary without stopping the primary.
  • Scale-out: distribute read-heavy workloads across secondary servers.
  • Analytics: run analysis on replicated data away from the primary workload.
  • Data distribution: replicate selected data to different locations or teams.
  • Failover alleviation: monitor the primary and redirect applications if it fails.
  • Security: SSL can encrypt replication traffic when both ends support SSL.

Automatic Installation

  • Click New Environment.
  • Select MySQL, MariaDB, or Percona.
  • Enable Auto-Clustering.
  • Select Primary-Secondary from the Scheme list.
  • Adjust the remaining environment parameters and click Create.
Database auto-clustering
Select Primary-Secondary replication through the Auto-Clustering option.

You can also install a preconfigured MySQL/MariaDB/Percona Cluster from Marketplace and choose the required replication type during installation.

Database cluster marketplace
Install a preconfigured database cluster from Marketplace.

Manual Installation

The manual workflow below is applicable to both MySQL and MariaDB. Create two identical environments: one for the primary and one for the secondary. The source example names them primary-db and secondary-db.

Create new environment
Create the first database environment.
Environment topology wizard
Select MariaDB and configure the primary database environment.
Primary and secondary database environments
Create or clone a second environment for the secondary database.

Configure the Primary Database

Open Config for the primary database and update my.cnf:

server-id = 1
log-bin = mysql-bin
binlog-format = mixed
Important: use the mixed binlog format. The source guide specifically warns against the statement format for this setup because it can cause replication errors with foreign-key operations.
Primary database Config button
Open the primary database configuration.
Primary database my.cnf
Add the primary replication settings to my.cnf.

Save the configuration and restart MariaDB.

Restart primary database
Restart the primary database to apply the settings.

Open phpMyAdmin, go to User accounts, create a replication user, and grant the replication client and replication slave administration privileges.

Add replication user
Create a user for replication.
Replication user credentials
Set the replication username and password.
Replication user privileges
Grant the required replication privileges.

Open the Status tab and record the binary-log File and Position values. These values are required on the secondary.

Primary database replication status
Record the binary-log File and Position values.

Configure the Secondary Database

Open my.cnf on the secondary database and add:

server-id = 2
slave-skip-errors = all
Production caution: the source guide uses slave-skip-errors = all for its example but explicitly says this is generally not recommended. In production, ignore only specific errors when necessary so critical replication problems are not hidden.
Secondary database my.cnf
Configure the secondary server ID and error-handling rule.

Then add this option to /etc/phpMyAdmin/config.inc.php:

$cfg['AllowArbitraryServer'] = true;
Allow arbitrary server
Enable arbitrary-server access in phpMyAdmin on the secondary.

Save the changes, restart the secondary database, open Web SSH, and connect to MySQL/MariaDB:

mysql -u root -p
SSH access to secondary database
Open Web SSH and connect with the database root account.

Set the Replication Source

Use the primary database host, replication username/password, binary-log file, and log position recorded earlier.

MariaDB

CHANGE MASTER TO
    MASTER_HOST='node410337-primary-db.domain.com',
    MASTER_USER='replication',
    MASTER_PASSWORD='passw0rd',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=865;

MySQL 8.0.22+ and Percona

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='node410337-primary-db.domain.com',
    SOURCE_USER='replication',
    SOURCE_PASSWORD='passw0rd',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=865;
Set replication source
Supply the primary database connection and log-position details.

Start replication with:

START REPLICA;

For MySQL versions before 8.0.22 and MariaDB before 10.5.1, the source guide uses:

START SLAVE;
Start database replication
Start the replication process on the secondary.

Open the secondary database Status tab and confirm that replication is running.

Secondary database replication status
Verify the running replication state on the secondary.

Check the Replication

Create a new database such as replication_test on the primary.

Create database on primary
Create a test database on the primary server.

Open the secondary database. The same database should appear there after replication.

Replicated database on secondary
The test database appears on the secondary after replication.

Expected Result

The primary database records updates in its binary log and the secondary receives and applies those updates. A database created on the primary is reproduced on the secondary, confirming that replication is working.