How to build a MySQL/MariaDB primary and replica setup

A second database server needs both a starting copy of the data and a stream of later changes to stay in sync. A MySQL or MariaDB primary and replica setup joins those two parts, leaving application writes on the primary while the replica replays its binary log.

File-and-position replication starts at the binary log coordinates recorded in a consistent dump. The snapshot and its coordinates must come from the same operation, or the replica can miss changes or apply them twice. Replication also copies accidental deletions, so it does not replace an independent backup.

The starting point is two separately installed servers of the same database family and compatible versions, an existing appdb database with InnoDB tables, and an empty replica dataset. Connections without TLS require a trusted private database network. Certificate-verified replication TLS is required before using an untrusted network; automatic failover and mixed MySQL-to-MariaDB replication are separate tasks.

Steps to build a MySQL/MariaDB primary and replica setup:

Back up the active server option file on each host.
```
# MySQL on Debian or Ubuntu
$ sudo cp -p /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.before-replication
```
```
# MariaDB on Debian or Ubuntu
$ sudo cp -p /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.before-replication
```
The primary is 192.0.2.40 and the replica is 192.0.2.41 below. Both documentation addresses stand for your private server addresses. RHEL-family packages commonly read /etc/my.cnf or files under /etc/my.cnf.d/ instead. The backup provides recovery if startup fails.

Changing listeners or restarting a database can interrupt applications. A maintenance window, a current data backup, local administrative access to both hosts, and an existing network rule allowing only the replica to reach primary TCP port 3306 are prerequisites.

Open the active server option file on each host in your text editor.

# MySQL on Debian or Ubuntu
$ sudoedit /etc/mysql/mysql.conf.d/mysqld.cnf

# MariaDB on Debian or Ubuntu
$ sudoedit /etc/mysql/mariadb.conf.d/50-server.cnf

Set the primary's replication options in its existing [mysqld] section.
```
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
bind_address = 192.0.2.40
```
These entries replace matching settings rather than creating duplicates. The listener address must belong to the primary; skip-networking must be absent or disabled.
Set the replica's options in its existing [mysqld] section.
```
server_id = 2
relay_log = replica-relay-bin
read_only = ON
event_scheduler = OFF
replicate_do_db = appdb
```
Every server needs a unique server_id. The primary runs scheduled events; event_scheduler = OFF prevents the replica from running imported events independently. The database filter matches the single database being seeded. With row logging, table changes are filtered by their database; database and routine definition statements depend on the session's default database, so application DDL must run with appdb selected. Other databases and their accounts are not cloned. read_only blocks ordinary client writes, but administrators can still import the seed; MySQL super_read_only must remain off during that import.
Save the option file on each host.
Restart each database server through its service manager.

The restart disconnects clients. If startup fails, the saved option file provides a recovery path.
Related: How to manage MySQL or MariaDB service with systemctl in Linux
Confirm binary logging on the primary from a local administrative connection.
```
# MySQL 8.4
$ mysql --user=root --password --execute "SHOW BINARY LOG STATUS\G"
```
```
# MariaDB
$ mariadb --user=root --password --execute "SHOW MASTER STATUS\G"
```
A row containing File and Position confirms binary logging. No row means the primary cannot supply this stream. The later dump supplies the coordinates to use, rather than this earlier query. The MySQL path uses file positions with GTID mode off; an existing GTID topology belongs to the linked GTID guide.
Open an administrative SQL session on the primary with client history disabled.
```
# MySQL
$ MYSQL_HISTFILE=/dev/null mysql --user=root --password
```
```
# MariaDB
$ MYSQL_HISTFILE=/dev/null mariadb --user=root --password
```
SQL blocks run in the named host's administrative session. Shell commands run in a separate terminal on that host. Password prompts keep administrator passwords out of shell history and process arguments. For socket-authenticated root accounts, a sudo client invocation replaces password-based login, including for the dump and import clients.
Create a replication account restricted to the replica's address on the primary.

The placeholder must be replaced with a unique password before execution. Credentials are stored in replica metadata, so access to that server and its data directory must be restricted.
```
CREATE USER 'repl'@'192.0.2.41' IDENTIFIED BY 'replace-with-a-unique-secret';
```
Grant the account permission to read the primary's binary log.

This privilege can expose other databases' changes even when the replica filters them out.
```
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.0.2.41';
```
Restrict newly created files in the primary's export shell to your account.
```
$ umask 077
```
The dump contains application data. A new, unused dump filename prevents overwriting another backup; restrictive permissions keep other local users from reading the export.
Export a consistent snapshot of appdb with its binary log coordinates on the primary.

--single-transaction protects InnoDB data, not MyISAM or MEMORY tables. Table definitions must remain unchanged during the dump; concurrent ALTER TABLE, DROP TABLE, or similar DDL can invalidate it. The coordinate option briefly takes a global read lock at the start. Binary logs covering the snapshot must remain available until the replica catches up.
```
# MySQL 8.4
$ mysqldump --user=root --password --databases appdb --single-transaction --routines --triggers --events --source-data=2 --set-gtid-purged=OFF > /var/tmp/appdb-seed.sql
```
```
# MariaDB
$ mariadb-dump --user=root --password --databases appdb --single-transaction --routines --triggers --events --master-data=2 > /var/tmp/appdb-seed.sql
```
Copy the seed dump to the replica over SSH.
```
$ scp -p /var/tmp/appdb-seed.sql admin@192.0.2.41:/var/tmp/appdb-seed.sql
```
The destination is a new filename owned by your SSH account. -p preserves restrictive permissions. The SSH host key must already be trusted, and admin stands for your existing login account.
Import the seed into the replica's empty dataset.

The dump can drop and recreate tables named in appdb. An empty target database and no existing replication channel are required; importing over unrelated data can destroy it. Successful import returns to the shell without query output.
```
# MySQL, on the replica
$ mysql --user=root --password < /var/tmp/appdb-seed.sql
```
```
# MariaDB, on the replica
$ mariadb --user=root --password < /var/tmp/appdb-seed.sql
```
Read the commented coordinate line near the top of the seed dump.
```
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000004', SOURCE_LOG_POS=3351;
```
The commented MySQL line supplies a log filename and byte position. MariaDB writes CHANGE MASTER TO with MASTER_LOG_FILE and MASTER_LOG_POS instead. Your own dump's filename and position must replace the values below.

Open an administrative SQL session on the replica with client history disabled.

# MySQL
$ MYSQL_HISTFILE=/dev/null mysql --user=root --password

# MariaDB
$ MYSQL_HISTFILE=/dev/null mariadb --user=root --password

Configure the replica connection with the primary address, replication password, and snapshot coordinates.

-- MySQL 8.4
CHANGE REPLICATION SOURCE TO
 SOURCE_HOST='192.0.2.40', SOURCE_PORT=3306,
 SOURCE_USER='repl', SOURCE_PASSWORD='replace-with-a-unique-secret',
 SOURCE_LOG_FILE='mysql-bin.000004', SOURCE_LOG_POS=3351,
 SOURCE_AUTO_POSITION=0, GET_SOURCE_PUBLIC_KEY=1;
 
-- MariaDB
CHANGE MASTER TO
 MASTER_HOST='192.0.2.40', MASTER_PORT=3306,
 MASTER_USER='repl', MASTER_PASSWORD='replace-with-a-unique-secret',
 MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=3125,
 MASTER_USE_GTID=no;

Only the statement for your database family applies. GET_SOURCE_PUBLIC_KEY=1 supports MySQL's default caching_sha2_password authentication without TLS, but does not encrypt the replication stream or authenticate the server's identity.

Start replication in the replica's SQL session.
```
START REPLICA;
```
Check the receiver and applier threads on the replica.
```
SHOW REPLICA STATUS\G
```
MySQL must show Replica_IO_Running: Yes and Replica_SQL_Running: Yes; MariaDB uses Slave_IO_Running and Slave_SQL_Running. A connecting receiver or stopped thread needs investigation through Last_IO_Error or Last_SQL_Error before the data test. Zero reported lag alone does not establish that writes arrive.
Related: How to check replication status in MySQL or MariaDB
Create a disposable InnoDB probe table in appdb on the primary.
```
USE appdb;
CREATE TABLE replication_probe (id INT PRIMARY KEY, message VARCHAR(40)) ENGINE=InnoDB;
```
The name replication_probe must be unused. This keeps the test away from application tables and gives cleanup a clearly identified target.

Insert a row into the probe table on the primary.

INSERT INTO appdb.replication_probe VALUES (1, 'Written on primary');

Query the replica for the new primary row.
```
$ mysql --user=root --password --table --execute "SELECT * FROM appdb.replication_probe;"
+----+--------------------+
| id | message            |
+----+--------------------+
|  1 | Written on primary |
+----+--------------------+
```
On MariaDB, the same command uses mariadb instead of mysql and returns the same row. The table was created after the snapshot, so this result shows that new changes crossed the connection. Replication is asynchronous; an initial missing table or empty result can require a short wait, but a persistent failure needs the thread error checks above.
Remove the disposable probe table on the primary with SQL.
```
USE appdb;
DROP TABLE replication_probe;
```
Only the table created for this test is removed. The application database and replication channel remain in place.
Remove the sensitive seed dump from each host.
```
$ rm /var/tmp/appdb-seed.sql
```

Confirm that replication remains running after the probe cleanup.

# MySQL
$ mysql --user=root --password --execute "SHOW REPLICA STATUS\G"
*************************** 1. row ***************************
##### snipped #####
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: appdb
##### snipped #####
        Seconds_Behind_Source: 0
##### snipped #####
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
##### snipped #####

# MariaDB
$ mariadb --user=root --password --execute "SHOW REPLICA STATUS\G"

Both threads should remain Yes with no I/O or SQL error after the create, insert, and drop have replayed. MariaDB reports the corresponding Slave_* fields and Seconds_Behind_Master. The earlier row read proves data flow; the running channel leaves the pair ready for later primary writes.

Written by

Mohd Shakir Zakaria

Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.