# Setup MySQL Master-Slave Replication

MySQL replication, specifically, MySQL master slave replication plays a vital role in ensuring data availability by enabling simultaneous copying and replication of data between servers. The MySQL master slave replication proves indispensable for data recovery, offering a reliable backup solution in the face of catastrophes or hardware failures.

## **Prerequisites**

**Master server: 10.128.0.28**

**Slave server: 10.128.0.29**

**Step 1:** Install MySQL on Master and Slave Nodes

```plaintext
sudo apt update
```

To install MySQL on both nodes, execute the command

```plaintext
sudo apt install mysql-server mysql-client
```

Next, Open the mysql configuration file

```plaintext
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
```

On the Master node, scroll and locate the bind-address attribute as

shown below.

```plaintext
bind-address=127.0.0.1
```

Change the loopback address to match the IP address of the Master

node.

```plaintext
bind-address=10.128.0.28
```

Next, specify a value for the server-id attribute in the \[mysqld\] section.

The number you choose should not match any other server-id number.

Let’s assign the value 1.

```plaintext
server-id=1
```

At the very end of the configuration file, copy and paste the lines below.

```plaintext
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
```

Exit the configuration file and restart MySQL service for the changes to

take effect on Master node.

```plaintext
sudo systemctl restart mysql
```

To verify that MySQL server is running as expected, issue the command.

```plaintext
sudo systemctl status mysql
```

Step 2: Create a New User for Replication on Master Node

In this section, we are going to create a replication user in the master

node. To achieve this, log in to the MySQL server as shown.

```plaintext
sudo mysql -u root -p
```

Next, proceed and execute the queries below to create a replica user and

grant access to the replication slave. Remember to use your IP address.

```plaintext
CREATE USER 'replication_user'@'10.128.0.29' IDENTIFIED BY 'replica_password';
```

```plaintext
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'10.128.0.29';
```

Next, run the following command.

```plaintext
SHOW MASTER STATUS\G
```

The output should be similar to what you can see below.

Be keen and note the mysql-bin.000002 value and the Position ID 1643.

These values will be crucial when setting up the slave server.

```plaintext
sudo apt install mysql-server mysql-client
```

Step 3: Configure the MySQL Slave Server

Head out to the slave server and like we did with the Master server,

open the MySQL configuration file.

```plaintext
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
```

Just like the master server, proceed to edit the following lines.

```plaintext
bind-address = 10.128.0.29
```

As before, specify a value for the server-id attribute in

the \[mysqld\] section. This time select a different value. Let’s go with 2.

```plaintext
server-id = 2
```

Again, paste the lines below at the very end of the configuration file.

```plaintext
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
```

Next restart MySQL server on slave node.

```plaintext
sudo systemctl restart mysql
```

Once done, save and exit the text editor

Next, log in to the MySQL shell as shown.

```plaintext
sudo mysql -u root -p
```

In this step, you will need to make some configuration that will allow

the slave server to connect to the master server. But first, stop the

slave threads as shown.

```plaintext
STOP SLAVE;
```

To allow the slave server to replicate the Master server, run the

command.

```plaintext
CHANGE MASTER TO MASTER_HOST ='10.128.0.28', MASTER_USER ='replication_user', MASTER_PASSWORD='Saturn@1234', MASTER_LOG_FILE = 'mysql-bin.000002',MASTER_LOG_POS = 1643;
```

If you are keen enough, you will observe that we’ve used the `mysql-bin.00002` value and `position ID 1643` earlier displayed after creating the slave replication user.

Additionally, the Master server’s IP address, replication user and password have been used.

Later, start the thread you had earlier stopped.

```bash
 mysql> START SLAVE;
```

check slave status :

```plaintext
mysql> show slave status\G
```

Now list the databases using the query.

```plaintext
mysql> SHOW DATABASES;
```

**Step 4:** Verify the MySQL Master-Slave Replication

To check if the setup is indeed working as expected, we are going to create a new database on the master and check to see if it has been replicated on the MySQL Slave server.

Log into MySQL in the Master server.

```plaintext
sudo mysql -u root -p
```

Let’s create a test database. In this case, we will create a database called `replication_db`.

```plaintext
mysql> CREATE DATABASE replication_db;
```

Now, log in to your MySQL instance in the slave server. `sudo mysql -u root -p`

Now list the databases using the query.

```plaintext
mysql> SHOW DATABASES;
```

You will notice the database you created on the master has been replicated on slave. Awesome ! Your MySQL Master-slave replication is working as expected! You can now rest assured that in the event of any failure, copies of database files will be replicated to the slave server.