Configuring a Slave Server in MySQL Replication

Configure Slave Server

Write a MySQL query to configure a MySQL server as a slave for replication.

Solution:

-- Stop the slave if it's already running
STOP SLAVE;

-- Configure the slave to connect to the master
CHANGE MASTER TO 
    MASTER_HOST='master_host_ip',
    MASTER_PORT=3306,
    MASTER_USER='repl_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- Start the slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G;
Explanation

Explanation:

• Purpose of the Query:
• Configures a MySQL server to replicate data from a master server
• Key Components:
• CHANGE MASTER TO sets up the connection parameters to the master server.
• START SLAVE initiates the replication process.
• SHOW SLAVE STATUS is crucial for monitoring replication health.
• Real-World Application:
• Used to scale out read operations or for failover scenarios

Notes:

• The log file and position should match what was obtained from the master server.
• Always verify replication status after setup.

For more Practice: Solve these Related Problems:

• Write a SQL query to configure a slave server to skip specific errors during replication.
• Write SQL to change the replication method from statement-based to row-based on a slave.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Replication and Clustering Exercises Home.
Next MySQL Exercise: Promote Slave to Master.