Master Server Configuration for MySQL Replication

Setup Master-Slave Replication

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

Solution:

-- Enable binary logging on the master server
SET GLOBAL binlog_format = 'ROW';

-- Create a replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';

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

-- Flush tables to ensure all changes are written to binary log
FLUSH TABLES WITH READ LOCK;

-- Show master status to get the current binary log file and position
SHOW MASTER STATUS;

Explanation:

• Purpose of the Query:
• Configures MySQL server to act as a master for replication, enabling binary logging and setting up a replication user.
• Key Components:
• binlog_format = 'ROW' ensures row-based replication for better consistency.
• CREATE USER and GRANT commands set up a user with replication privileges.
• FLUSH TABLES WITH READ LOCK ensures data consistency before noting the replication status.
• SHOW MASTER STATUS provides the binary log file and position needed for slave setup.
• Real-World Application:
• Essential for setting up replication from one server to another, ensuring data redundancy and load distribution.

Notes:

• Ensure the binary log format is appropriate for your replication needs.
• The master status must be recorded to configure the slave correctly.

For more Practice: Solve these Related Problems:

• Write a SQL query to configure a MySQL server as a master for replication with mixed binary logging format.
• Write SQL to set up a master server for replication with specific binary log expiration settings.

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: Setup Master-Slave Replication.