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
- Configure primary: enable binary logging and set a unique server-id.
- Create a replication user on primary.
- Take a consistent snapshot (dump) of the primary's data (or use LVM/snapshot).
- Configure replica: set server-id, point it at the primary with the correct binlog file and position (or GTID).
- 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).
