How To Set Up MySQL Master Slave Replication

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.

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)

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, in a given command apt-get can usually replace yum.

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 runs in a 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 command 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 every time the system boots with the command:

chkconfig mysqld on

Step 5: Start MySQL

Now, find out if MySQL was correctly installed. If it was, you should 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. To set the password, run 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 allows traffic on port 3306. This rule enables proper communication between devices.

To save the firewall configuration, use the command:

service iptables save

Be careful whenever opening ports to make sure you avoid hacks.    

Step 8: Configure Server for Replication  

Each slave server requires a way to connect to the master server. To establish a connection, you need a MySQL user name and password. This requires you to set up a MySQL user account on the master server.

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. You can always find the user name and password on the account in plain text in the master repository.

To set up a new user account called replication, and allow it to connect for replication purposes from any host on the yoursite.com domain, execute this command on the master server:

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

Step 9: Ensure Data on Your Servers Matches

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

Flush tables must be set with a read lock. The master server’s database must 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 the 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.

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 need to shut down the server. 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 the 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 the replication.

Import the dump with the command:

shell> mysql < fulldb.dump

Step 11: Begin Slave Threads

To immediately see the 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.