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.
Create a backup group with both master and slave nodes and take a 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.
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.