Replication basics in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Identify the Master Server Status

Given the output of SHOW MASTER STATUS; on a MySQL server, what does the File column represent?

MySQL

SHOW MASTER STATUS;

A The name of the binary log file currently being written by the master.

B The name of the relay log file on the slave server.

C The name of the error log file for the master server.

D The name of the configuration file used by the master.

💡 Hint

Think about what the master writes to keep track of changes for replication.

The File column in SHOW MASTER STATUS; shows the current binary log file that the master is writing to. This log contains all changes that slaves will replicate.

🧠 Conceptual

intermediate

Role of Relay Logs in Replication

In MySQL replication, what is the primary purpose of the relay log on the slave server?

A To log errors encountered during replication.

B To store the changes read from the master's binary log before applying them locally.

C To keep a backup of the master's binary logs.

D To store the binary logs generated by the slave server.

💡 Hint

Relay logs act as a temporary storage on the slave.

Relay logs on the slave store the events read from the master's binary log. The slave then applies these events to its own data to stay synchronized.

📝 Syntax

advanced

Correct Syntax to Start Slave Replication

Which of the following commands correctly starts the replication process on a MySQL slave server?

A START SLAVE;

B BEGIN SLAVE;

C RUN SLAVE;

D INITIATE SLAVE;

💡 Hint

The command uses the word 'START'.

The correct command to start replication on a slave is START SLAVE;. Other options are invalid commands.

🔧 Debug

advanced

Troubleshooting Replication Error

A MySQL slave shows the error Last_SQL_Error: Error 'Duplicate entry' for key 'PRIMARY'. What is the most likely cause?

A The relay log on the slave is missing.

B The master server's binary log is corrupted.

C The slave tried to apply a change that conflicts with existing data, causing a duplicate primary key.

D The slave server has lost network connection to the master.

💡 Hint

Think about what a duplicate primary key error means in data replication.

This error means the slave tried to insert a row with a primary key that already exists, indicating data inconsistency or previous partial replication.

❓ optimization

expert

Improving Replication Performance

Which of the following changes is most effective to reduce replication lag on a MySQL slave?

A Increase the size of the master's binary log files.

B Set the slave's <code>read_only</code> variable to OFF.

C Disable the relay log on the slave server.

D Enable multi-threaded replication by setting <code>slave_parallel_workers</code> to a value greater than 0.

💡 Hint

Think about how the slave can apply changes faster.

Enabling multi-threaded replication allows the slave to apply multiple transactions in parallel, reducing lag. Other options either do not affect lag or can cause issues.