Hands-On MySQL Replication: Building a Two-Node Cluster with Podman
In this tutorial, we'll build a small MySQL replication environment locally using Podman.
We'll create two independent MySQL servers:
Replication
┌─────────────────────┐
│ │
▼ │
┌───────────┐ ┌───────────┐
│ MySQL 1 │ ──────►│ MySQL 2 │
│ Primary │ │ Replica │
│ :3307 │ │ :3308 │
└───────────┘ └───────────┘
By the end, we'll be able to:
- Run two independent MySQL instances using Podman
- Configure MySQL binary logging
- Create a replication user
- Configure GTID-based replication
- Verify replication
- Simulate replication failure
- Recover the replica and let it catch up
- Understand the basic MySQL replication architecture
Note: This tutorial demonstrates traditional asynchronous source/replica replication. It is not a production-ready high-availability cluster. We'll use the terms source/primary and replica throughout the tutorial.
1. Prerequisites
You need:
- macOS/Linux
- Podman
- MySQL client
Verify Podman:
podman --version
For this tutorial we'll use:
MySQL 8.4
Podman
2. Create a Podman Network
The two MySQL containers need to communicate with each other.
Create a dedicated network:
podman network create mysql-repl
Verify it:
podman network ls
You should see:
mysql-repl
3. Create Persistent Volumes
We'll use separate volumes so that each MySQL instance has its own persistent database storage.
Create the volumes:
podman volume create mysql1-data
podman volume create mysql2-data
Verify:
podman volume ls
You should see:
mysql1-data
mysql2-data
4. Start MySQL Node 1
Node 1 will be our primary/source.
Run:
podman run -d \
--name mysql-1 \
--network mysql-repl \
-p 3307:3306 \
-v mysql1-data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
mysql:8.4 \
--server-id=1 \
--log-bin=mysql-bin \
--binlog-format=ROW \
--gtid-mode=ON \
--enforce-gtid-consistency=ON
Let's break down the important options.
--server-id=1
Every MySQL server participating in replication needs a unique server ID.
Our topology will therefore be:
mysql-1 → server-id 1
mysql-2 → server-id 2
--log-bin=mysql-bin
Enables the binary log.
The binary log is one of the fundamental pieces of MySQL replication. Changes made on the source are recorded in the binary log and then transferred to replicas.
--binlog-format=ROW
Use row-based binary logging.
--gtid-mode=ON
Enables Global Transaction Identifiers (GTIDs).
GTIDs make replication management considerably easier because transactions can be identified independently of a particular binary-log filename and position.
--enforce-gtid-consistency=ON
Prevents operations that could cause problems with GTID-based replication.
5. Start MySQL Node 2
Node 2 will be our replica.
Run:
podman run -d \
--name mysql-2 \
--network mysql-repl \
-p 3308:3306 \
-v mysql2-data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
mysql:8.4 \
--server-id=2 \
--log-bin=mysql-bin \
--relay-log=mysql-relay-bin \
--binlog-format=ROW \
--gtid-mode=ON \
--enforce-gtid-consistency=ON
Notice the different server ID:
mysql-1 → 1
mysql-2 → 2
The containers also expose different ports on the host:
mysql-1 → localhost:3307
mysql-2 → localhost:3308
Inside the Podman network, however, the containers can communicate using their container names:
mysql-1:3306
mysql-2:3306
6. Verify Both Containers
Run:
podman ps
You should see both containers:
mysql-1
mysql-2
If a container isn't running, inspect its logs:
podman logs mysql-1
or:
podman logs mysql-2
Wait until MySQL has finished initializing before connecting.
7. Connect to MySQL Node 1
Connect using the MySQL client:
mysql -h 127.0.0.1 -P 3307 -u root -p
Enter:
root
Check the server ID:
SELECT @@server_id;
Expected:
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
Also check GTID mode:
SELECT @@gtid_mode;
Expected:
ON
8. Connect to MySQL Node 2
Open another terminal:
mysql -h 127.0.0.1 -P 3308 -u root -p
Again, the password is:
root
Check:
SELECT @@server_id;
Expected:
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
We now have two completely independent MySQL servers.
┌───────────────────┐
│ MySQL 1 │
│ │
│ server-id = 1 │
└───────────────────┘
independent
┌───────────────────┐
│ MySQL 2 │
│ │
│ server-id = 2 │
└───────────────────┘
They are not replicating anything yet.
9. Create a Database on Node 1
Go back to the MySQL 1 session.
Create a test database:
CREATE DATABASE demo;
Select it:
USE demo;
Create a table:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
Insert some data:
INSERT INTO users(name)
VALUES
('Alice'),
('Bob');
Verify:
SELECT * FROM users;
Expected:
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
| 2 | Bob |
+----+-------+
10. Verify Node 2 Doesn't Have the Database
Switch to the MySQL 2 session:
SHOW DATABASES;
You should not see:
demo
This is important.
At this point:
MySQL 1 MySQL 2
demo no demo
└── users
├── Alice
└── Bob
Now we're going to connect these two servers.
11. Create the Replication User
On MySQL 1, create a dedicated replication account:
CREATE USER 'repl'@'%' IDENTIFIED BY 'replpass';
Grant the required replication privilege:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
The replication user will be used by MySQL 2 to connect to MySQL 1.
The topology will become:
mysql-2
│
│ repl / replpass
│
▼
mysql-1
12. Configure Node 2 as the Replica
Switch to MySQL 2.
Before configuring replication, make sure any previous replication configuration is stopped:
STOP REPLICA;
Now configure the source:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-1',
SOURCE_PORT=3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='replpass',
SOURCE_AUTO_POSITION=1,
GET_SOURCE_PUBLIC_KEY=1;
The important settings are:
SOURCE_HOST
mysql-1
Because both containers are on the mysql-repl Podman network, MySQL 2 can resolve mysql-1 by its container name.
SOURCE_PORT
Inside the Podman network, MySQL is listening on its normal port:
3306
The host port 3307 is only needed when connecting from the Mac.
SOURCE_USER
repl
This is the replication account we created earlier.
SOURCE_AUTO_POSITION=1
This enables GTID-based automatic positioning.
Instead of manually specifying something like:
mysql-bin.000003
position 1234
MySQL uses GTIDs to determine which transactions the replica needs.
GET_SOURCE_PUBLIC_KEY=1
This setting is important when using MySQL's default caching_sha2_password authentication without TLS.
Without it, you may encounter:
Authentication plugin 'caching_sha2_password'
reported error:
Authentication requires secure connection
GET_SOURCE_PUBLIC_KEY=1 allows the replica to obtain the source's RSA public key for authentication.
For production deployments, TLS should be configured rather than relying on this local-development setup.
13. Start Replication
On MySQL 2:
START REPLICA;
Now check the replication status:
SHOW REPLICA STATUS\G
This command produces a large amount of information.
The two fields we're most interested in initially are:
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
When both are Yes, replication is running.
Conceptually:
MySQL 1
┌─────────┐
│ Source │
└────┬────┘
│
│ binary log
▼
┌─────────┐
│ MySQL 2 │
│ Replica │
└─────── ──┘
14. Test Replication
Now let's see whether it actually works.
On MySQL 1:
USE demo;
INSERT INTO users(name)
VALUES ('Charlie');
Now switch to MySQL 2.
Run:
SELECT * FROM demo.users;
You should see:
+----+---------+
| id | name |
+----+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
+----+---------+
The database was created on MySQL 1, and the original rows were copied during the initial replication setup. The new transaction containing Charlie was then replicated to MySQL 2.
We have successfully built MySQL replication.
15. Understand What Just Happened
The important thing is to understand the mechanism rather than simply memorizing commands.
The simplified architecture looks like this:
MySQL 1
┌────────────┐
│ │
INSERT ───────►│ Database │
│ │
│ Binary Log │
└─────┬──────┘
│
│ replication
▼
┌────────────┐
│ Relay Log │
│ │
│ MySQL 2 │
└────────────┘
A transaction on MySQL 1 roughly follows this path:
Application
│
▼
MySQL 1
│
├── Update database
│
└── Write binary log
│
▼
MySQL 2 receives
│
▼
Relay log
│
▼
Apply transaction
│
▼
MySQL 2 database
This is why the binary log is so important for replication.
16. Experiment: Stop Replication
Now let's deliberately break replication.
On MySQL 2:
STOP REPLICA;
Check:
SHOW REPLICA STATUS\G
Replication should no longer be running.
Now go to MySQL 1 and insert more rows:
USE demo;
INSERT INTO users(name)
VALUES ('David');
INSERT INTO users(name)
VALUES ('Eve');
INSERT INTO users(name)
VALUES ('Frank');
Check MySQL 1:
SELECT * FROM users;
You should have:
Alice
Bob
Charlie
David
Eve
Frank
Now check MySQL 2:
SELECT * FROM demo.users;
You should only see:
Alice
Bob
Charlie
The replica has fallen behind.
17. Resume Replication
Start replication again:
START REPLICA;
Wait a moment and check:
SELECT * FROM demo.users;
You should now see:
+----+---------+
| id | name |
+----+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
| 5 | Eve |
| 6 | Frank |
+----+---------+
The replica caught up automatically.
This is one of the key benefits of GTID-based replication.
18. What Happens When the Source Goes Down?
Now let's try something more interesting.
Stop MySQL 1:
podman stop mysql-1
MySQL 2 will lose its connection to the source.
Check:
SHOW REPLICA STATUS\G
You'll see that the replication I/O side is no longer healthy.
However, the important point is:
MySQL 2 does not automatically become the primary.
Our architecture is still:
MySQL 1
PRIMARY
│
▼
MySQL 2
REPLICA
Stopping MySQL 1 does not magically turn this into:
MySQL 2
PRIMARY
Automatic failover is a separate high-availability problem.
19. Replication vs High Availability
This distinction is important.
We have built:
┌──────────┐
│ MySQL 1 │
│ PRIMARY │
└────┬─────┘
│
▼
┌──────────┐
│ MySQL 2 │
│ REPLICA │
└──────────┘
This provides data replication.
But it does not automatically provide:
- Automatic failover
- Automatic leader election
- Automatic application redirection
- Conflict resolution
- Multi-primary writes
Those are additional distributed-systems problems.
A more complete MySQL high-availability architecture can be built using MySQL Group Replication and InnoDB Cluster.
20. Useful Commands
Here are some commands worth remembering.
Check server ID
SELECT @@server_id;
Check GTID mode
SELECT @@gtid_mode;
Check replication status
SHOW REPLICA STATUS\G
Start replication
START REPLICA;
Stop replication
STOP REPLICA;
Check the source binary logs
On MySQL 1:
SHOW BINARY LOG STATUS;
Check GTIDs
SELECT @@GLOBAL.gtid_executed;
21. Clean Up
When you're finished experimenting, stop the containers:
podman stop mysql-1 mysql-2
Remove the containers:
podman rm mysql-1 mysql-2
If you also want to remove the persistent data:
podman volume rm mysql1-data mysql2-data
Remove the network:
podman network rm mysql-repl
22. What We Built
At the end of this lab, our environment looks like:
Podman Network
mysql-repl
│
┌────────────┴────────────┐
│ │
▼ ▼
┌───────────┐ ┌───────────┐
│ MySQL 1 │ │ MySQL 2 │
│ │ │ │
│ server-id │ │ server-id │
│ 1 │ │ 2 │
│ │ │ │
│ PRIMARY │────────────►│ REPLICA │
│ :3307 │ replication │ :3308 │
└───────────┘ └───────────┘
The replication flow is:
MySQL 1
│
│ transaction
▼
Binary Log
│
│
▼
MySQL 2
│
▼
Relay Log
│
▼
Apply Transaction
│
▼
MySQL 2 Database
23. What's Next?
This two-node setup is a good starting point for understanding MySQL replication, but there's much more to explore.
The natural next experiments are:
1. Replication lag
Generate a large number of transactions and measure how quickly the replica catches up.
2. GTIDs
Inspect GTIDs on both servers and understand exactly how MySQL knows which transactions have already been applied.
3. Failover
Stop the primary and manually promote the replica.
MySQL 1
X
│
▼
MySQL 2
PRIMARY
Then reconnect MySQL 1 as the new replica.
4. Two-way replication
Configure both nodes to replicate from each other and investigate what happens when both nodes receive writes.
┌──────────┐
│ MySQL 1 │
└────┬─────┘
│
┌────┴────┐
│ │
▼ │
MySQL 2 ◄─────┘
This introduces an important problem: write conflicts.
5. MySQL Group Replication
Finally, move from simple source/replica replication to a real multi-node topology:
┌──────────┐
│ MySQL 1 │
└────┬─────┘
│
┌────────┴────────┐
│ │
▼ ▼
┌──────────┐ ┌──────────┐
│ MySQL 2 │◄────►│ MySQL 3 │
└──────────┘ └──────────┘
That will let us explore concepts such as group membership, distributed consensus, automatic primary election, failover, and multi-primary replication.
Conclusion
We've built a complete two-node MySQL replication environment locally using Podman.
More importantly, we've seen the fundamental replication flow:
Source
│
▼
Binary Log
│
▼
Replica
│
▼
Relay Log
│
▼
Apply Transaction
Once this mental model is clear, concepts such as GTIDs, replication lag, failover, semi-synchronous replication, Group Replication, and InnoDB Cluster become much easier to understand.
The next step is to intentionally break this setup and investigate what happens. That's where replication starts getting really interesting.