Introduction

In cloud computing, master-slave data replication refers to storing the same information on multiple servers. One server controls the group, and the other devices handle the work within the same node.

Replication becomes necessary:

  • When a company needs a regular daily backup that can restore the master MySQL database in case of a storage malfunction.
  • Wherever information must be stored, examined, and modified by several people, on several computers. If a group member identifies a need for a change, it must go to the node’s designated master.

This guide will walk you through how to set up master-slave replication in MySQL.

how to set up master slave in mySQL

Prerequisites

  • A Linux-based system, such as CentOS
  • A user account with sudo privileges
  • A package installer such as yum or apt, typically included by default
  • The cloud server’s private IP (to avoid charges for bandwidth on the public IP during configuring)

Note: The term master-slave serves to distinguish this model from master-master replication—in which any member of the group may update the information.


Steps for Setting Up Master-Slave Database Replication in MySQL

Yum (Yellowdog Updater, Modified) is the package management tool for RedHat Package Manager Linux systems. In .deb packages, the yum tool is generally replaced with the apt tool. Therefore, yum in a given command is generally by apt-get.

For example, an install command is either yum install [package_name] or apt-get install [package_name] for the identical result. This article assumes that you are using an RPM-based Linux distribution. Hence, yum commands are used.

The master MySQL database is run on read-write mode. Meanwhile, the slave server runs as read-only. Now, to the replication.

Step 1: Configure MySQL Database for Replication

Use the mysqldump command to perform a dump-and-restore of information to the slave database. This dumps the data into a file, transfers it, and restores it to the slave node.

Step 2: Create 2 Separate Cloud Servers

In this step, the privileged user needs to log into the cloud control. With CentOS, this is possible through the CentOS 6 base image.

Create two Linux cloud servers with appropriate RAM and assign them distinct names.

Step 3: Update All Software Packages to their Current Version

Users and system administrators can update all software packages on an RPM-based Linux system with the following command:

yum update

Step 4: Install MySQL

For Linux systems based on the RedHat Package Manager, install the MySQL server package on both cloud servers with the command:

yum install mysql-server

Set the service to run from boot with the command:

chkconfig mysqld on

Step 5: Start MySQL

Now, find out if MySQL was installed properly. If it was, you will be able to start the service with the command:

service mysqld start

Step 6: Set Up MySQL Server Root Password

It is a good idea to set an administrative user password for MySQL. This can be done by running the script for a secure installation that accompanies the MySQL package, responding with Y to all Y/n prompts.

Or, use /usr/bin/mysqladmin -u root password followed by a space and your new password; and /usr/bin/mysqladmin -u root -h web01 password followed by a space and, again, your new password.

Step 7: Configure Firewall for Database Access

Applying the TCP Port 3306 rule adds a Firewall Policy that will allow traffic on port 3306, for proper communication between devices.

To save the firewall configuration, use the command:

service iptables save

Be careful whenever opening ports, to prevent hacks.

Step 8: Configure Server for Replication

Each slave server needs a way to connect to the master server by way of a MySQL user name and password. This requires you to set up a MySQL user account on the master server.

master-slave

We recommend establishing a dedicated account for replication. For this function, grant any account the replication slave privilege. You can create the same account or different accounts for each slave. The user name and password on the account can always be found in plain text in the master repository.

To set up the user account repl, which may connect for replication purposes from any host on the yoursite.com domain, execute this command on the master server:

mysql> CREATE USER 'repl'@'%.yoursite.com' IDENTIFIED BY [space followed by password]; then: mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.yoursite.com';

Step 9: Ensure Data on Your Servers Match

Duplicate the data through dumping from the master into the slave server.

Flush tables must be set with read lock. It is vital that the master server’s database does not take in any new changes during this step. If there is information on the master server to coordinate with the slave servers before the replication process starts, take the following actions:

  • Stop processing any commands on the master server.
  • Copy down the file name and current coordinates for the master server within its binary log (see Step 10, below). This will be needed later when completing the replication configuration.
  • Before enabling your master server to continue following commands, dump its data.

Perform all of these actions carefully to ensure that the data dump will match the master server’s current data. Otherwise, the slave databases will be mismatched or corrupted.

ensure-data-on-servers-match

Step 10: Take Snapshot of the Master Database

As you take the following action, note that just one snapshot of the master database is all you need to configure several slave servers.

If you have yet to configure the server-id</> and start a binary log on the master server, you will need to shut it down. You can take a snapshot of the databases now.

Bring the data files to the slave server’s directory:

shell> tar xvf dbdump.tar

If necessary, establish ownership and permissions for the files. You may need to allow the slave server access and the ability to make modifications. Start the slave with --skip-slave-start to avoid beginning replication.

Configure your slave server with the master server’s hostname and login credentials. Use the master server’s replication coordinates for the slave database. You are directing the slave to the point where replication begins, using the binary log file and position.

For a configured master server, you can use mysqldump to take a snapshot.

Now you can update the slave configuration. Again, be sure to start the slave with --skip-slave-start to avoid beginning replication.

Import the dump with the command:

shell> mysql < fulldb.dump

Step 11: Begin Slave Threads

To immediately see updates on the slave server use:

mysql> START SLAVE;

Conclusion

After reading this article you should be able to set up master slave replication in MySQL and begin slave threads. You can now easily do regular backups, as well as store and modify data on multiple computers.