Replication basics in MySQL - Mini Project: Build & Apply

Replication basics

📖 Scenario: You are managing a small online store database. To keep your data safe and available, you want to set up a simple replication system where one database server copies data from another.

🎯 Goal: Build a basic MySQL replication setup by creating a master database with a sample table, configuring replication user credentials, and setting up the slave to replicate data from the master.

📋 What You'll Learn

Create a sample database and table on the master server

Create a replication user with proper privileges

Configure the master server to log binary changes

Set up the slave server to connect and replicate from the master

💡 Why This Matters

🌍 Real World

Replication is used to keep copies of data synchronized across multiple servers for backup, load balancing, and high availability.

💼 Career

Database administrators and backend engineers often set up and maintain replication to ensure data safety and system reliability.

1

Create sample database and table on master

On the master MySQL server, create a database called store and inside it create a table called products with columns id (integer primary key) and name (varchar 50). Insert one row with id 1 and name 'Book'.

MySQL

-- Create database store
-- Create table products with columns id and name
-- Insert one product row
-- Your code here

Need a hint?

Use CREATE DATABASE to make the database, then CREATE TABLE for the products table. Insert a row with INSERT INTO.

2

Create replication user on master

On the master server, create a replication user called repl_user with password 'repl_pass'. Grant this user the REPLICATION SLAVE privilege.

MySQL

CREATE DATABASE store;
USE store;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
INSERT INTO products (id, name) VALUES (1, 'Book');
-- Create replication user repl_user with password 'repl_pass' and grant REPLICATION SLAVE privilege
-- Your code here

Need a hint?

Use CREATE USER with host '%' for any IP, then GRANT REPLICATION SLAVE privilege.

3

Configure master for binary logging

On the master server, enable binary logging by setting log_bin to mysql-bin and set a unique server ID 1. This is done in the MySQL configuration file (my.cnf or my.ini).

MySQL

CREATE DATABASE store;
USE store;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
INSERT INTO products (id, name) VALUES (1, 'Book');
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
-- Add these lines to the master server's my.cnf or my.ini:
-- log_bin=mysql-bin
-- server_id=1
-- Your code here

Need a hint?

Binary logging and server ID must be set in the MySQL config file, not as SQL commands.

4

Set up slave to replicate from master

On the slave server, set a unique server ID 2 in its configuration file. Then run the CHANGE MASTER TO command to connect to the master at host 'master_host' using user 'repl_user' and password 'repl_pass'. Finally, start the slave with START SLAVE;.

MySQL

CREATE DATABASE store;
USE store;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
INSERT INTO products (id, name) VALUES (1, 'Book');
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_pass';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

# In the master server's my.cnf or my.ini configuration file add:
# log_bin=mysql-bin
# server_id=1
-- On the slave server, set server_id=2 in config
-- Run CHANGE MASTER TO with master_host, repl_user, repl_pass
-- Run START SLAVE;
-- Your code here

Need a hint?

Set the slave's server ID in config. Use CHANGE MASTER TO with the master's host and replication user info. Then start the slave.