Introduction
Setting up MySQL replication is a powerful way to enhance the reliability, scalability, and performance of your database system. In simple terms, MySQL replication is a process where data from one MySQL database server (known as the primary or master) is automatically copied to one or more additional servers (called replicas or slaves). This creates a live backup of your data, ensuring that if the primary server fails, a replica can step in with minimal downtime. Beyond redundancy, replication also allows you to distribute read-heavy workloads across multiple servers, improving efficiency and speed for applications that rely on quick data access.
Importance of DB replication
Why is this important? In today’s world, where uptime and fast performance are non-negotiable, replication provides a safety net against hardware failures, network issues, or unexpected outages. It’s a cornerstone for businesses running critical applications—like e-commerce platforms, content management systems, or analytics tools—that can’t afford to lose data or leave users waiting. Plus, with a well-configured setup, you can scale your database infrastructure as your needs grow, all while keeping everything in sync. In this post, we’ll walk through the steps to set up MySQL replication, so you can unlock these benefits for your own projects.
Inner works of MySQL replication
MySQL replication works by copying and syncing data from a primary server (master) to one or more replica servers (slaves). The primary server logs all changes—like inserts, updates, or deletes—in a binary log. The replicas then read this log and apply those changes to their own databases in near real-time. A key component, the I/O thread, pulls the log data from the primary, while an SQL thread on the replica executes the updates locally. This setup keeps the data consistent across servers, enabling redundancy and load balancing.
Setting up replication with Docker Compose
The source and each replica must be configured with a unique ID (using the server_id system variable).
The binlog_do_db setting in MySQL controls which databases are included in the binary log on the primary server during replication. When you specify a database with this option (e.g., binlog_do_db=my_database), only changes to that specific database—like table updates or inserts—are recorded in the binary log.
On the replicata there are settings that can limit the statements to specifc dbs or tables:
- replicate-do-db: Tells replication SQL thread to restrict replication to specified database.
- replicate-do-table: Tells replication SQL thread to restrict replication to specified table.
- replicate-ignore-db: Tells replication SQL thread not to replicate to specified database.
- replicate-ignore-table: Tells replication SQL thread not to replicate to specified table.
In addition, each replica must be configured with information about the source's host name, log file name, and position within that file.
Here is a repo where I set up replication using Docker. Just by running docker compose up