Step-by-Step Guide to Setting up MySQL Master-Slave Replication

MySQL is one of the most widely used relational database management systems. It offers several features and options to manage data efficiently. One such feature is MySQL Master-Slave replication, which is used to copy data from a master database to one or more slave databases. In this blog, we will discuss how to configure MySQL Master-Slave replication with detailed explanations and commands.

Master-Slave Replication

Master-Slave replication is a method of copying data from a master MySQL database to one or more slave databases. It is used to improve the availability and scalability of MySQL databases. The master database is responsible for writing changes to the database, and the slave databases copy those changes. Slave databases can be used for backup, read-only queries, load balancing, or reporting.

MySQL Master-Slave Replication Configuration

To configure MySQL Master-Slave replication, we need to follow these steps:

Step 1: Setting up the Master Database

The first step is to set up the master database. To do this, we need to perform the following steps:

1. Install MySQL: Install MySQL on the server that will be the master database. We can use the following command to install MySQL on Ubuntu:

sudo apt-get install mysql-server

2. Configure MySQL: Once MySQL is installed, we need to configure it by editing the MySQL configuration file. Open the MySQL configuration file with the following command:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following lines to the end of the file to enable binary logging:

log_bin = /var/log/mysql/mysql-bin.log
server_id = 1

Save and close the file.

3. Restart MySQL: After editing the MySQL configuration file, we need to restart MySQL with the following command:

sudo systemctl restart mysql

4. Create a Replication User: We need to create a user that the slave database will use to connect to the master database. To create a replication user, run the following command:

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';

Note: Replace ‘repl_user’ and ‘password’ with the desired username and password.

5. Take a Snapshot: Finally, we need to take a snapshot of the master database to use as a starting point for the slave databases. To take a snapshot, use the following command:

mysqldump --single-transaction --master-data=1 --ignore-table=mysql.event dbname > dbname.sql

Note: Replace ‘dbname’ with the name of the database.

Step 2: Setting up the Slave Database

After setting up the master database, we need to set up the slave database. To do this, we need to perform the following steps:

1. Install MySQL: Install MySQL on the server that will be the slave database. We can use the following command to install MySQL on Ubuntu:

sudo apt-get install mysql-server

2. Configure MySQL: Once MySQL is installed, we need to configure it by editing the MySQL configuration file. Open the MySQL configuration file with the following command:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following lines to the end of the file to enable replication:

server_id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
relay_log_index = /var/log/mysql/mysql-relay-bin.index
log_slave_updates = 1

Save and close the file.

3. Restart MySQL: After editing the MySQL configuration file, we need to restart MySQL with the following command:

sudo systemctl restart mysql

Connect to the Master Database: We need to connect to the master database and get the binary log file name and position to use as a starting point for the slave database. To do this, run the following command on the master database:

SHOW MASTER STATUS;

Note the binary log file name and position.

Set up Replication on the Slave Database: We need to set up replication on the slave database using the binary log file name and position from the master database. Run the following command on the slave database:
makefile

CHANGE MASTER TO
MASTER_HOST='master_host_ip',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=501;

Note: Replace 'master_host_ip', 'repl_user', 'password', 'mysql-bin.000001', and '501' with the appropriate values.

Start Replication on the Slave Database: Finally, we need to start replication on the slave database with the following command:

SHOW SLAVE STATUS\G

To check the replication status, run the following command:

SHOW SLAVE STATUS\G

This will show the replication status and information on the slave database.

Conclusion

In this blog, we discussed how to configure MySQL Master-Slave replication with detailed explanations and commands. Master-Slave replication is a powerful feature of MySQL that can be used to improve the availability and scalability of MySQL databases. By following the steps outlined in this blog, you should be able to successfully set up MySQL Master-Slave replication.