(“master/slave” replication) — clear, practical steps + examples MySQL

DB

Primary → Replica Setup for MySQL

(a.k.a. “master/slave” replication) — clear, practical steps + examples. Note: you cannot change Blogger.com's or any backend database not under your control; see note below.

Important for Blogger.com users

Blogger (blogger.com) is a Google-hosted platform — you cannot access or modify its internal MySQL database. This guide is for:

  • Self-hosted blogs using MySQL (WordPress, Ghost with MySQL adapter, custom apps)
  • Developers who run their own database servers and want replication for redundancy, read-scaling, or backups

Quick overview — What is DB replication?

MySQL replication copies data from one server (the primary, historically called "master") to one or more replicas (historically "slaves"). Common uses: high availability, disaster recovery, analytics offload and zero-downtime backups.

Prerequisites

  • Two MySQL servers (Primary and Replica) reachable over the network
  • MySQL versions should be compatible (replica equal or newer is safest)
  • Root or admin access on both servers and ability to edit MySQL config and restart service
  • Network firewall rules allowing the replica to connect to the primary's MySQL port (default 3306)

High-level steps

  1. Configure primary: enable binary logging and set a unique server-id.
  2. Create a replication user on primary.
  3. Take a consistent snapshot (dump) of the primary's data (or use LVM/snapshot).
  4. Configure replica: set server-id, point it at the primary with the correct binlog file and position (or GTID).
  5. Start replication and verify status.

Step-by-step (commands)

1) Configure the Primary (my.cnf / mysqld)

# /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf (example)
[mysqld]
server-id = 100
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW           # ROW is safest for data consistency
expire_logs_days = 7
max_binlog_size = 100M
# (Optional) For GTID-based replication:
# gtid_mode = ON
# enforce_gtid_consistency = ON

After editing, restart MySQL:

sudo systemctl restart mysql
# or
sudo service mysqld restart

2) Create a replication user on Primary

-- Log in as root on Primary
mysql -u root -p

-- Create a replication user (replace 'repl_password' with a strong password)
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3) Get a consistent snapshot + master status

Option A — logical dump (mysqldump) — easiest for small DBs: 

mysqldump --single-transaction --routines --triggers --events --all-databases \
  -u root -p > all_databases.sql

Right after (or before finishing the dump), record the binary log file and position:

mysql -u root -p -e "FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS\G"
-- Keep the session open (it holds the read lock). In another terminal:
-- Export or copy the dump (or use mysqldump with --single-transaction so lock is short).
-- After snapshot is copied, in the locked session:
UNLOCK TABLES;

You'll get output like: 

File: mysql-bin.000012
Position: 34567
GTID (if enabled): e.g. 3e1c8f2b-...:1-12345

4) Restore snapshot on Replica and configure replica my.cnf

# example /etc/mysql/my.cnf on replica
[mysqld]
server-id = 200
relay-log = /var/log/mysql/mysql-relay-bin
log_bin = /var/log/mysql/mysql-bin.log   # optional for chained replication
# If using GTID: set gtid_mode = ON and enforce_gtid_consistency = ON (both servers)

Import the dump onto the replica:

mysql -u root -p < all_databases.sql

5) Point replica to primary and start replication

Using file/position (non-GTID):

CHANGE MASTER TO
  MASTER_HOST='primary.example.com',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000012',
  MASTER_LOG_POS=34567;

START SLAVE;
-- On MySQL 8 the command is:
-- START REPLICA;

Using GTID (auto-positioning) — simpler once GTID is enabled on both servers:

CHANGE MASTER TO
  MASTER_HOST='primary.example.com',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl_password',
  MASTER_AUTO_POSITION = 1;

START SLAVE;

6) Verify replication

-- On Replica:
SHOW SLAVE STATUS\G
-- Important fields:
-- Slave_IO_State
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: (0 or small number)
-- Last_IO_Error, Last_SQL_Error  (should be empty)

Common pitfalls & troubleshooting

  • server-id: must be unique per server.
  • binlog_format: ROW is safest for data consistency (esp. updates).
  • GTID vs file/position: GTID simplifies failover but requires extra setup (enable GTID on both servers).
  • Replication stops: check SHOW SLAVE STATUS\G for errors; typical causes: missing binary-log file, incompatible SQL, or permission issues.
  • Time drift: keep servers' clocks in sync (use NTP) — helps with logging and monitoring.

Security & hardening

  • Limit replication user host to the replica IP(s) rather than '%'.
  • Use TLS (SSL) between primary and replica if traffic goes over public networks — configure REQUIRE SSL for the replication user and set up --ssl options in CHANGE MASTER.
  • Keep backups of binary logs for the retention period you need for point-in-time recovery.
  • Use firewalls / security groups to restrict MySQL access to known replica hosts only.

Monitoring & failover

For production, add monitoring (Prometheus + exporters, Percona Monitoring and Management, or Cloud monitoring). For automated failover use orchestrators:

  • Orchestrator (github.com/openark/orchestrator)
  • MMM / MHA (older tools)
  • Cloud-managed solutions (RDS/Aurora, Cloud SQL) handle replication + failover for you

Wrap up — Cheatsheet

  • Edit my.cnf: set server-id, log_bin, and binlog_format=ROW.
  • Create replication user on primary with GRANT REPLICATION SLAVE.
  • Take snapshot (mysqldump with single-transaction or filesystem snapshot), record binlog file & pos.
  • Restore on replica, run CHANGE MASTER TO ..., START SLAVE, and verify SHOW SLAVE STATUS\G.
Need a ready-to-run script?

If you want, I can:

  • Generate a bash checklist script that automates the dump, transfer, and CHANGE MASTER commands (you'll still run it manually).
  • Create an example GTID-based configuration for MySQL 8 with exact my.cnf snippets for both servers.
  • Produce a small monitoring dashboard example (Prometheus + Grafana snippets).

Author's note: I use primary/replica in this article for clarity and modern terminology — searches for "master/slave replication" will still find this guide. Want the post adjusted to use strictly "master/slave" wording instead? Tell me and I’ll make that edit.