MySQL replication is a process that allows you to copy/replicate data from one server to another at the same time. It is done primarily to increase data availability. One of the main reasons people opt for MySQL master-slave replication is for data recovery. In the event of any catastrophe or hardware failure, MySQL replication ensures that an accurate backup is in place all the time.
In this article, you will look in detail at the MySQL master-slave replication process along with a step-by-step guide on how to achieve replication.
Table of Contents
What is
- MySQL
- What is master-slave replication?
- Replication Prerequisites
- master-slave
- using Hevo’s no-code data pipeline for seamless
- ConclusionWhat
?
Purpose of Master-Slave
Steps to achieve MySQL
replication
MySQL replication
is MySQL
? MySQL is
one of the most popular and widely used open source RDBMS (Relational Database Management System). MySQL is available for free under the GNU Public License and is also available as a premium proprietary version. MySQL was originally developed by Michael Widenius at MySQL AB, a Sweden-based company. In 2012, Sun Microsystems acquired MySQL AB, and later Oracle acquired Sun Microsystems.
MySQL is used for a variety of applications and is primarily based on SQL (structured query language). MySQL is widely used in applications built with PHP. It is also being used by some of the popular websites, including Twitter, Facebook, Mediawiki, YouTube and Flickr, etc.
What is master-slave replication?
The master-slave replication process allows database administrators to replicate or copy data stored on more than one server simultaneously. This helps the database administrator create a live backup of the database all the time. During some situations, when the master-slave has any problems, he can instantly change the slave database and keep the application running. The replication process ensures that your application does not face any kind of downtime.
In this replication, there are several types of replication processes. You can have a single master and multiple slaves or multiple masters and multiple slaves, etc.
In this process, it is always a unidirectional or unidirectional data transmission. The data is first stored in the master and then copied to the slaves. Therefore, the write operation is performed only on the master database. The reading operation is performed on both the master and the slave. Slaves can also be used for data accessibility to reduce the load on the master database.
One
of
the primary purposes of opting for a master-slave replication system is to have a standby system with a live backup that can be promoted as master when the original master server fails. Apart from this, there are several benefits as described below
:
- Scalability: All query requests to the database can be routed to multiple database servers to reduce the load on the server and allow faster access. Most web applications and sites found today come loaded with more read operations than write to the database. Therefore, website administrators need to provide the perfect setup for fast loading of details on the website.
- Performance: All database write operations are performed on the master database. Once these changes are made to the master database, they are upgraded from master to slave. But read requests from websites can be shared among multiple slaves to increase website performance.
- : You can replicate the last database snapshot to another database and create a backup in just a couple of minutes. Data corruption is greatly reduced as the master server runs smoothly and provides 99.9% uptime. This allows applications to process large numbers of read or write operations seamlessly.
- Analysis and Bench-Marking: This process allows database analysts to run all kinds of data analysis tests and experiments on slaves without disturbing the master.
Backup
Prerequisites
To set up a MySQL master-slave
replication, you must have the following:
- 2 VM (virtual machine) or VPS (virtual private server) with root access
- Working Internet
.
.
Steps to achieve
MySQL master-slave replication For this demonstration purpose,
you will call master as root@repl-master and slave as root@repl-slave
.
For this demonstration, suppose the IP address for master and slave is as follows
: Master server: 12.34.56.111
Slave server: 12.23.34.222
1. Master Configuration
The first thing you must accomplish in the replication process is to install and configure the master server. If you have not installed
MySQL, 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 After the MySQL
installation process is complete, use the following command to edit the MySQL configuration file
: root@repl-master:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Then, in the same file, locate the line that contains bind-address = 127.0.0.1 and replace that IP address with the IP address of the master replication server. Then, the line will look like this: bind-address=12.34.56.111
Next, look
for 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 out, simply uncomment these lines and exit the editing interface by clicking CTRL+X. Save the changes and restart the MySQL service for the changes to take effect. Restart the MySQL
service
using the following command
: root@repl-master:~# sudo service mysql restart
2. Create
a new slave user
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 THE USER ‘SLAVE’@’12.34.56.789’ IDENTIFIED BY ‘SLAVE_PASSWORD’; mysql> GRANT REPLICATION SLAVE IN . To ‘slave’@’12.34.56.222 ‘; 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 data from master to slave. You must 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 root@12.34.56.222
Unlock the tables with the following command:
mysql> UNLOCK TABLES;
4. Configure
the slave server
Now, all you need to do is configure the slave server and test if replication is working. Make sure MySQL is installed. Open the configuration file on your
slave server and update these lines: root@repl-slave:~# sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
In the same way 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
After the data is imported, you must stop MySQL on the slave server using the following command: root@repl-slave
:~# mysql -uroot -p; mysql> STOP SLAVE;
Finally you have imported the dump files and updated the master IP address, password, log file name and position, to allow the master to communicate with the slave without any problems.
6
. Start Slave Server
Next, use the “Start Slave” command to start operating the slave server.
START SLAVE; 7. Try MySQL
Master Slave
Replication To test if your MySQL master slave
replication works, simply create a database on your master server and see if it replicates to the slave server. If you can see the database on the slave, then it is working fine.
Create a test database on a master server named ‘sampledb’.
CREATE SAMPLEDB DATABASE;
Now log in to your slave server and list the databases, and if you see the “sampledb” there, then the master slave replication process works fine.
Log in to your slave server and use the following command to enumerate all databases: show databases
; Using
Hevo’s
no-code data pipeline for seamless MySQL
,
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 in minutes. Hevo is fully managed and fully automates the process of not only loading data from the desired source, but also enriching the data and transforming it into a form ready for analysis without having to write a single line of code.
Hevo’s pre-built integration with MySQL and 100+ data sources in databases, files, analytics engines, etc. gives users the flexibility to bring in data of all kinds as seamlessly as possible, without having to write a single line of code. Hevo takes over your data transfer process and allows you to focus your engineering bandwidth on key business activities.
More reasons to love
Hevo: Secure: Hevo
- has a fault-tolerant architecture that ensures data is handled securely and consistently without data loss
- Auto Schema Mapping: Hevo eliminates the tedious task of schema management and automatically detects the schema of incoming data from MySQL files and maps it to the target schema.
- Quick setup: Hevo with its automated functions, can be set up in minimal time. In addition, with its simple and interactive user interface, it is extremely easy for new customers to work and perform trades.
- Pre- and post-upload transformations: With Transformations in Hevo, you can prepare data in several ways before uploading it to the destination. Hevo offers numerous data transformations, including cleaning, reexpression, data filtering, normalization, etc.
- Hevo is designed to scale: As the number of sources and volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Live Support: The Hevo team is available twenty-four hours a day to provide exceptional support to their customers via chat, email, and support calls.
.
With continuous real-time data movement, upload your data from MySQL to your target store with Hevo’s easy-to-configure, no-code interface. Try our 14-day all-access free trial.
Get started with Hevo for free
Conclusion
The above article has provided you with enough information on how to set up a MySQL master-slave replication. The step-by-step guide is specially provided to help beginners understand the process and begin testing the replication process in their environment. But this process requires hard and extensive work as you have to manually set up a lot of details. So, if you want to bypass this difficulty and experience a smooth process, try Hevo.
Hevo Data provides an automated code-free data pipeline that allows you to overcome the limitations mentioned above. You can leverage Hevo to seamlessly replicate your MySQL data in real-time without writing a single line of code. Hevo’s Data Pipeline enriches your data and manages the transfer process in a fully automated and secure way. Hevo caters to 100+ data sources (including 40+ free sources) and can transfer data directly to data warehouses, business intelligence tools, or any other destination of your choice seamlessly. It will make your life easier and make data migration seamless.
Learn more about Hevo
You can post your experience and feedback on the MySQL master-slave replication process in the comments section below.
