Replication basics in MySQL - Step-by-Step Execution

Concept Flow - Replication basics

Master writes data

↓

Binary log records changes

↓

Slave reads binary log

↓

Slave applies changes

↓

Slave data matches Master

Data changes on the master are recorded in a binary log, which the slave reads and applies to keep data synchronized.

Execution Sample

MySQL

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G;

This code sets the slave to start replication from a specific log file and position, then starts the slave and shows its status.

Execution Table

Step	Action	Master State	Slave State	Result
1	Master inserts row into table	Row added	No change	Master data updated
2	Master writes change to binary log	Binary log updated	No change	Change recorded
3	Slave reads binary log event	Binary log unchanged	Event read	Slave ready to apply
4	Slave applies event to its data	No change	Row added	Slave data updated
5	Slave sends acknowledgment	No change	Acknowledged	Replication confirmed
6	Check slave status	No change	Slave running, no errors	Replication active

💡 Replication continues as long as master writes and slave reads without errors

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
Master Data	Empty	Row added	Row added	Row added	Row added	Row added	Row added
Binary Log	Empty	Empty	Change recorded	Change recorded	Change recorded	Change recorded	Change recorded
Slave Data	Empty	Empty	Empty	Event read	Row added	Row added	Row added
Slave Status	Stopped	Stopped	Stopped	Stopped	Running	Running	Running

Key Moments - 2 Insights

Why doesn't the slave data change immediately when the master inserts a row?

Because the slave first reads the change from the master's binary log (Step 3) before applying it (Step 4), as shown in the execution_table.

What happens if the slave cannot read the binary log?

The slave will stop applying changes and replication will pause, indicated by 'Slave Status' not changing to 'Running' in the variable_tracker.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the slave apply the master's data change?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Check the 'Slave State' column in execution_table rows for when 'Row added' appears.

According to variable_tracker, what is the slave status after Step 4?

A Running

B Stopped

C Error

D Paused

💡 Hint

Look at the 'Slave Status' row and the value under 'After Step 4'.

If the master stops writing changes, what happens to the slave's binary log reading step?

A Slave continues reading new events

B Slave deletes its data

C Slave reads no new events and waits

D Slave restarts replication automatically

💡 Hint

Refer to the concept_flow where slave reads master's binary log to get changes.

Concept Snapshot

Replication basics in MySQL:
- Master records changes in binary log
- Slave reads binary log events
- Slave applies changes to its data
- Replication keeps slave data synchronized
- Use START SLAVE and SHOW SLAVE STATUS to manage

Full Transcript

Replication in MySQL works by the master server recording all data changes in a binary log. The slave server reads this binary log and applies the changes to its own data, keeping both databases synchronized. The process involves the master writing data, logging it, the slave reading the log, applying changes, and confirming replication status. Commands like CHANGE MASTER TO, START SLAVE, and SHOW SLAVE STATUS help control and monitor replication. This step-by-step flow ensures data consistency between master and slave servers.

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