MySQL replication, specifically MySQL master slave replication, plays a vital role in ensuring data availability by enabling simultaneous copying and replication of data between servers. The MySQL master slave replication proves indispensable for data recovery, offering a reliable backup solution in the face of catastrophes or hardware failures.
This article offers an extensive exploration of the MySQL master slave replication process, providing a comprehensive step-by-step guide to help you successfully implement and achieve replication. Gain insights into the intricacies of MySQL replication setup and equip yourself with the knowledge to safeguard your data effectively.
Steps to Achieve MySQL Master Slave Replication
For this MySQL master-slave tutorial, we will call master as root@repl-master and slave as root@repl-slave.
For this MySQL replication setup, let’s assume the IP address for the master and slave are as follows:
Master server: 22.214.171.124
Slave server: 126.96.36.199
The 7 Steps To Achieve MySQL Master Slave Replication are given below:
- Setting Up The Master
- Create A New User For Slave
- Move Data From Master To Slave
- Configure Slave Server
- Import Data Dump
- Start Slave Server
- Test MySQL Master Slave Replication
Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.
Start for free now!
Get Started with Hevo for Free
1. Setting Up The Master
The first thing you need to accomplish in the MySQL master-slave replication process is to install and configure the master server. If you have not installed MySQL, then you can install MySQL using the following command:
root@repl-master:~# sudo apt-get update
root@repl-master:~# sudo apt-get install mysql-server mysql-client -y
root@repl-master:~# sudo mysql_secure_installation
Read through Installing MySQL on Ubuntu 20.04: 6 Easy Steps for more insights.
Once the MySQL installation process is completed, use the following command to edit the MySQL configuration file:
root@repl-master:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Next, in the same file, find the line containing bind-address = 127.0.0.1 and replace that IP address with the IP address of your master replication server. So, the line will look like:
bind-address = 188.8.131.52
Next, find the following lines in the file:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
You will see that the above lines have been commented, just uncomment these lines and exit the edit interface by clicking CTRL + X. Save the changes and restart the MySQL service for the changes to take effect.
Restart MySQL service using the following command:
root@repl-master:~# sudo service mysql restart
2. Create A New User For Slave
The next step is to create a new user for your slave server. Use the following command to create it:
root@repl-master:~# mysql -uroot -p;
mysql> CREATE USER ‘slave’@’184.108.40.2069‘ IDENTIFIED BY ‘SLAVE_PASSWORD‘;
mysql> GRANT REPLICATION SLAVE ON . TO ‘slave’@’220.127.116.11 ‘;
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
You will use the following command to know the current status of the master server:
mysql> SHOW MASTER STATUS;
This command will also tell the slave to follow the master from this position.
3. Move Data From Master To Slave
Now that you have marked the position, you can start moving the data from the master to the slave. You need to create a MySQL dump file to move the data. Use the following command to create the dump file:
root@repl-master:~# mysqldump -u root -p –all-databases –master-data > data.sql
To copy the dump file to the slave, use the following command:
scp data.sql firstname.lastname@example.org
Unlock the tables using the following command:
mysql> UNLOCK TABLES;
4. Configure Slave Server
Now, all you need to do is configure the slave server and test if replication is working. Ensure MySQL is installed.
Open the configuration file in your slave server and update these lines:
root@repl-slave:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
In the same way that you did for the master server, you need to bind the IP address and uncomment those two lines for the slave server.
Now, restart the MySQL server using the following command:
root@repl-slave:~# sudo service mysql restart
5. Import Data Dump
Use the following command to import the dump file to the slave server:
root@repl-slave:~# mysql -uroot -p < data.sql
Once the data is imported, you need to stop MySQL in the slave server using the following command:
root@repl-slave:~# mysql -uroot -p;
mysql> STOP SLAVE;
You have finally imported the dump files and updated the master IP address, password, log file name, and position, to enable the master to communicate with the slave without any issues.
Download the Ultimate Guide on Database Replication
Learn the 3 ways to replicate databases & which one you should prefer.
6. Start Slave Server
Next, use the “Start Slave” command to start operating the slave server.
7. Test MySQL Master Slave Replication
To test if your MySQL master slave replication works, just create a database in your master server and see if it is replicated in the slave server. If you can see the database in the slave, then it is working fine.
Create a test database in a master server called ‘sampledb’.
CREATE DATABASE sampledb;
Now login to your slave server and list the databases, and if you see the “sampledb” there, then the master slave replication process is working fine.
Login to your slave server and use the following command to list all databases:
Using Hevo’s No-code Data Pipeline for Seamless MySQL Replication
Hevo Data, an automated no-code data pipeline provides a hassle-free solution and helps you replicate your data from MySQL to any database, data warehouse, or destination of your choice within minutes. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code.
Hevo’s pre-built integration with MySQL and 150+ data sources across databases, files, analytics engines, etc. gives users the flexibility to bring in data of all different kinds in a way that’s as smooth as possible, without having to write a single line of code. Hevo takes full charge of your data transfer process and lets you focus your engineering bandwidth on key business activities in a cost-effective way.
With continuous real-time data movement, load your data from MySQL to your destination warehouse with Hevo’s easy-to-setup and No-code interface. Try our 14-day full-access free trial.
Get Started with Hevo for free
Advantages of MySQL Master-Slave Replication
- Data Redundancy and Reduced Downtime: The slave server gives redundancy to your data by maintaining a copy of it. If, in any case, the master server fails, slave servers can take over, ensuring continuous data access and minimizing server downtime.
- Load Balancing: The read-intensive queries can be distributed among multiple slave servers, which creates ample space for write operations to be carried out in the master server, boosting overall performance of the servers.
- Better Read Performance: Since the read queries are offloaded to multiple slave servers so that the master server can focus on write queries, this read and write separation optimizes the performance, especially in a read-heavy workload.
- Horizontal Scaling: The MySQL master-slave replication setup allows for horizontal scaling by adding more slave servers to the architecture. This scalability is useful in cases of increased read loads without altering the database system.
- Disaster Recovery: The slave servers act as hot backups. In the event of data loss or corruption on the master server, a slave server can be promoted to master, minimizing the impact and catalyzing the recovery process.
The above article has provided you with enough information about how to set up a MySQL master slave replication. The step-by-step guide is specially provided to help beginners understand the process and start testing the replication process in their environment. But this process requires extensive hard work as you have to configure many details manually. So, if you want to overlook this hardship and experience a hassle-free process, then give Hevo a try.
Here are some essentials for a deeper dive into MySQL Replication:
Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. You can leverage Hevo to seamlessly replicate your MySQL data in real time without writing a single line of code.
Learn more about Hevo
You can post your experience and comments about the MySQL master slave replication process in the comment section below.