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.
Go to:
PREV : Replication and Clustering Exercises Home.
NEXT : Setup Master-Slave Replication.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.