Adding MySQL slave to existing master-slave setup

In this blog, I will walk you over on how to add a new slave to existing Master-Slave replicated databases.  The typical process will be

  • Deploy a new server with MySQL and configure the slave
  • Backup database on the master using ‘mysqldump’
  • Restore all the data on the new machine
  • Start the replication

When organizations are running databases in TBs, adding new slave is going to be very challenging. Backup on the master and restore on the slave can take hours. In the following section, we can see how Nimesa can be leveraged to bring this time from hours to minutes.

Prerequisites

There is a pre-existing master-slave machine. Need to have ‘root’ privileges on all the nodes. For this blog, we will be using Ubuntu 16.04 servers with MySQL database v5.7.

The below steps define how to add a new slave.

Step:1 Protect existing Master/Slave using Nimesa

Using Nimesa create an application policy with MySQL option. This will put the database in a read lock mode while backup.

Nimesa MySQL Policy

Create a backup group with both master and slave nodes and take a backup

Nimesa MySQL Backup

Step:2 Preparing slave node

From the AWS console launch a new ubuntu server and deploy MySQL server. Note down the EC2 instance id.

Step:3 Provision the data from Master to the new server

Select the MySQL backup group and list the backup. Select the master node and corresponding backup. Use the “volume mount” feature of Nimesa to clone and provision the EBS volume with MySQL data directory to the newly provisioned EC2 instance.

Nimesa Volume Mount

Step:4 Configure Replication on the new slave

Log into the new slave and mount the new disk

ubuntu@ip-172-31-32-41:~$ lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvda 202:0 0 8G 0 disk
└─xvda1 202:1 0 8G 0 part /
xvdf 202:80 0 8G 0 disk
└─xvdf1 202:81 0 8G 0 part
loop0 7:0 0 88.7M 1 loop /snap/core/7396
loop1 7:1 0 18M 1 loop /snap/amazon-ssm-agent/1455

ubuntu@ip-172-31-32-41:~$ sudo file -s /dev/xvdf
/dev/xvdf: DOS/MBR boot sector

Create a new mount point under ‘/’ and mount the cloned filesystem

ubuntu@ip-172-31-32-41:~$ sudo mount /dev/xvdf1 /data

Now change the data directory to point to the new mounted copy

ubuntu@ip-172-31-32-41:~$ sudo nano /etc/MySQL/mysql.conf.d/mysqld.cnf

Update the AppArmour Access Control file

ubuntu@ip-172-31-32-41:~$ sudo nano /etc/apparmor.d/tunables/alias

Modify the alias to point to the new mysql datadir and restart the apparmour service

ubuntu@ip-172-31-32-41:~$ sudo systemctl restart apparmor

Now restart the mysql service and connect the database new database from the master will be seen here

ubuntu@ip-172-31-32-41:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+——————–+
5 rows in set (0.01 sec)

Open the mysql.cnf file and set serverid. Remove the auto.cnf from the mysql directory to avoid the server id conflict. Restart the mysql server

On the master, execute the master status command to get the position

mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000002 | 934 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

On the new slave machine login to mysql client and execute the following command

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST = ‘MASTER IP’, MASTER_USER = ‘Replica User’, MASTER_PASSWORD = ‘PWD’, MASTER_LOG_FILE = ‘mysql-bin.000002’, MASTER_LOG_POS = 934;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

Now create a database in master and it should be visible on the slave

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

About Nimesa

Nimesa is an enterprise-class Application Aware data protection, cost management & copy data management solution for applications running on AWS. It uses native AWS capabilities like EBS snapshots capabilities to automatically protect the environment. Nimesa provides simple policy based lifecycle management of snapshots and clones of EC2 instances.

Try Nimesa for free

Commenting is Disabled on Adding MySQL slave to existing master-slave setup