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

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

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

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

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.

Practice

1. What is the main purpose of MySQL replication?

easy

A. To copy data automatically from one database server to another

B. To create backups manually on the same server

C. To speed up SQL query execution on a single server

D. To encrypt data during transmission

Solution

1. Step 1: Understand replication concept

   Replication means copying data from one database (called master) to another (called slave) automatically.

2. Step 2: Identify the main purpose

   The main goal is to keep data synchronized between servers without manual copying.

3. Final Answer:

   To copy data automatically from one database server to another -> Option A

4. Quick Check:

   Replication = automatic data copy [OK]

Hint: Replication means automatic data copying between servers [OK]

Common Mistakes:

• Confusing replication with backup
• Thinking replication speeds up queries on one server
• Assuming replication encrypts data

2. Which command is used to start replication on a MySQL slave server?

easy

A. START SLAVE

B. START REPLICATION

C. BEGIN SLAVE

D. RUN SLAVE

Solution

1. Step 1: Recall replication start command

   In MySQL, the command to start the slave replication process is START SLAVE.

2. Step 2: Check other options

   Other options like START REPLICATION, BEGIN SLAVE, or RUN SLAVE are not valid MySQL commands.

3. Final Answer:

   START SLAVE -> Option A

4. Quick Check:

   Start replication = START SLAVE [OK]

Hint: Use START SLAVE to begin replication on slave server [OK]

Common Mistakes:

• Using incorrect commands like START REPLICATION
• Confusing with transaction commands
• Forgetting to start the slave after setup

3. Given the command CHANGE MASTER TO MASTER_HOST='192.168.1.10', MASTER_USER='replica', MASTER_PASSWORD='pass'; executed on a slave, what does it do?

medium

A. Deletes the current master configuration

B. Starts replication immediately with given credentials

C. Creates a new master server at 192.168.1.10

D. Sets the slave to connect to the master at IP 192.168.1.10 using user replica

Solution

1. Step 1: Understand CHANGE MASTER TO command

   This command configures the slave server to connect to a master server with specified host and user credentials.

2. Step 2: Analyze given parameters

   It sets the master host IP to 192.168.1.10 and uses the user 'replica' with password 'pass' for connection.

3. Final Answer:

   Sets the slave to connect to the master at IP 192.168.1.10 using user replica -> Option D

4. Quick Check:

   CHANGE MASTER TO = configure slave connection [OK]

Hint: CHANGE MASTER TO sets slave's master connection info [OK]

Common Mistakes:

• Thinking it starts replication immediately
• Assuming it creates a new master server
• Believing it deletes master config

4. You run START SLAVE; but replication does not start. Which of these is a likely cause?

medium

A. The master server is down

B. The slave user password is empty

C. The slave has not been configured with CHANGE MASTER TO

D. The slave server is running MySQL version 8

Solution

1. Step 1: Check slave configuration

   If the slave is not configured with CHANGE MASTER TO, it does not know which master to connect to.

2. Step 2: Understand replication start requirements

   Without proper master info, START SLAVE cannot initiate replication, even if master is up or password is set.

3. Final Answer:

   The slave has not been configured with CHANGE MASTER TO -> Option C

4. Quick Check:

   Missing CHANGE MASTER TO blocks replication start [OK]

Hint: Configure slave with CHANGE MASTER TO before START SLAVE [OK]

Common Mistakes:

• Assuming START SLAVE works without configuration
• Blaming MySQL version without checking config
• Ignoring master server status

5. You want to set up a slave that replicates only the database named sales. Which command helps you filter replication to this database?

hard

A. CHANGE MASTER TO MASTER_DB='sales';

B. CHANGE REPLICATION FILTER REPLICATE_DO_DB = ('sales');

C. SET REPLICATION FILTER TO 'sales';

D. CHANGE REPLICATION FILTER REPLICATE_DO_DB = 'sales';

Solution

1. Step 1: Understand replication filtering

   MySQL uses replication filters like REPLICATE_DO_DB to replicate only specific databases.

2. Step 2: Identify correct syntax

   The correct syntax uses CHANGE REPLICATION FILTER REPLICATE_DO_DB = ('sales'); with parentheses around the database name.

3. Final Answer:

   CHANGE REPLICATION FILTER REPLICATE_DO_DB = ('sales'); -> Option B

4. Quick Check:

   Use REPLICATE_DO_DB with parentheses for filtering [OK]

Hint: Use CHANGE REPLICATION FILTER with REPLICATE_DO_DB and parentheses [OK]

Common Mistakes:

• Using non-existent commands like SET REPLICATION FILTER
• Omitting parentheses around database name
• Trying to filter with CHANGE MASTER TO