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.
- 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, 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
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:
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.
/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.
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.
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;
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.