Promoting a Slave to Master in MySQL

Promote Slave to Master

Write SQL queries to promote a slave to a new master in case of failover.

Solution:

-- Stop slave replication on the slave being promoted
STOP SLAVE;

-- Reset slave configuration
RESET SLAVE;

-- Enable binary logging if not already enabled
SET GLOBAL binlog_format = 'ROW';

-- Create a replication user for new slaves
CREATE USER 'new_repl_user'@'%' IDENTIFIED BY 'new_password';

-- Grant privileges to the new replication user
GRANT REPLICATION SLAVE ON *.* TO 'new_repl_user'@'%';

-- Show master status to inform other slaves about new master
SHOW MASTER STATUS;

Explanation:

• Purpose of the Query:
• Converts a slave server into a new master in case the original master fails.
• Key Components:
• STOP SLAVE and RESET SLAVE ensure the server no longer acts as a slave.
• Binary logging setup and user creation for new replication setup.
• Real-World Application:
• Critical in disaster recovery scenarios to minimize downtime.

Notes:

• Ensure all other slaves are reconfigured to point to the new master

For more Practice: Solve these Related Problems:

• Write MySQL queries to promote a slave to master while maintaining existing replication users.
• Write a query to promote a slave server to master with a new replication user group.

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

Previous MySQL Exercise: Configure Slave Server.
Next MySQL Exercise: Check Replication Lag.