Can you explain the process of setting up and configuring master-slave replication in MySQL? — MySQL Interview Question #56

Setting up master-slave replication in MySQL involves the following steps:

1. **Setting up the master server**: To set up the master, you need to ensure that the ‘log-bin‘ setting is enabled in the MySQL configuration file, which is usually located at ‘/etc/mysql/my.cnf‘ in Linux systems. To enable binary logging, add the following line to the ‘[mysqld]‘ section of the configuration file:

log-bin = mysql-bin

This tells MySQL to write all database changes to binary log files with a prefix of ‘mysql-bin‘. You’ll also need to create a user account that the slave servers will use to connect to the master. For example, you can create a user named ‘replication‘ and give it the necessary permissions:

CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

2. **Setting up the slave server(s)**: Once the master server is configured, you’ll need to set up the slave server(s). You can do this by editing the MySQL configuration file on each slave server, which is usually located at ‘/etc/mysql/my.cnf‘. In addition to the usual MySQL settings, you’ll need to add the following lines to the ‘[mysqld]‘ section:

server-id = <unique server ID>
relay-log = mysql-relay-bin
log-slave-updates = 1

The ‘server-id‘ setting should be unique for each slave server, and can be any positive integer up to 23̂2 - 1. The ‘relay-log‘ setting tells MySQL to write relay logs that contain the changes received from the master. The ‘log-slave-updates‘ setting tells MySQL to log any changes that are received from the master to its own binary log.

3. **Configuring the master for replication**: Once the server settings are in place, you’ll need to configure the master to allow replication. You can do this by logging in to the master server as the ‘root‘ user and running the following commands:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The ‘FLUSH TABLES WITH READ LOCK‘ command ensures that no database changes are made while you’re setting up replication. The ‘SHOW MASTER STATUS‘ command displays the ‘File‘ and ‘Position‘ values that you’ll need later when configuring the slave.

4. **Configuring the slave for replication**: With the master configured, you can now set up replication on the slave server(s). Log in to each slave server as the ‘root‘ user and run the following command:

CHANGE MASTER TO
  MASTER_HOST='<ip address or hostname of master>',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='<log file from SHOW MASTER STATUS>',
  MASTER_LOG_POS=<position from SHOW MASTER STATUS>;

This configures the slave to connect to the master server using the ‘replication‘ user and the password you set up earlier. It also specifies the ‘File‘ and ‘Position‘ values obtained from ‘SHOW MASTER STATUS‘.

5. **Starting replication**: Once the master and slave are configured, you can start replication by running the following command on each slave server:

START SLAVE;

You can verify that replication is working by running ‘SHOW SLAVE STATUS;‘ on the slave server. This should display a list of properties related to replication, including a ‘Slave_IO_Running‘ and ‘Slave_SQL_Running‘ field, which should both be set to ‘Yes‘ if replication is working properly.

That’s the general process for setting up master-slave replication in MySQL! Of course, there are many additional configuration options and best practices to consider depending on your specific use case and environment, but this should be a good starting point.