MySQL Master Slave Replication | Complete Guide

Master Slave

In this blog, I will discuss in detail MySQL Master Slave Replication. What is the need for MySQL Replication, Working of MySQL Master Slave Replication and Practical Example of Replicating MySQL Database across the server?

What is MySQL Master Slave replication?

For handling large-scale, high-performance applications built using MySQL then we need MySQL data to be distributed to multiple systems. This distribution mechanism is to copy the data from one host of Mysql to other hosts (slaves) and reImplement it again. During the replication process, one server acts as the master server, and one or more other servers act as slave servers. The master server writes updates to a binary log file and maintains an index of the file to track log cycles. These logs can record updates sent to the slave server. When a slave server connects to the master server, it informs the master server of the location of the last successful update read in the log by the slave server. The slave server receives any updates that have occurred since then, then blocks and waits for the master server to notify the new update.

Why do you need master-slave replication

1. Data Distribution
2. Load Balancing
3. Ensure Data is Secure by creating a Backup.
4. High Availability and Failover
5. Performing Reading and Writing on different DB hence reduce the load on Database.

The advantages of MySQL replication are

Expansion capability: The performance pressure of MySQL can be shared to one or more slaves through the replication function. This requires that all write operations and modification operations must be completed on the Master, and read operations can be allocated to one or more slaves; after the read and write operations are separated to different servers for execution, MySQL’s read and write performance is improved.

Database backup: Since the slave instance synchronizes the data of the master instance, the backup job can be deployed to the slave database.

Data analysis and reports: Similarly, some implementations of data analysis and reports can be executed in the slave instance to reduce the performance impact on the main library.

Disaster tolerance: It is possible to establish a slave in another data centre that is physically far away to ensure rapid recovery in another data centre in the event of a disaster in the area where the primary instance is located.

There are two methods for MySQL replication:

Traditional way: Based on the bin-log of the master library, the log events and event locations are copied to the slave library, and the slave library is applied to achieve the purpose of master-slave synchronization.

Grid method: global transaction identifiers are based on transactions to replicate data, so they do not rely on log files, and at the same time can better ensure the consistency of master-slave database data.

For Types of Replication Visit MySQL Master Slave Replication.

MySQL replication principle

The master server records the data changes in the binary binlog log, and when the data on the master changes, it writes the changes into the binary log;

The slave server will detect whether it has changed in the master binary log within a certain time interval. If it changes, it will start an I/O Thread requesting the master binary event;

At the same time, the master node starts a dump thread for each I/O thread to send binary events to it and save them to the local relay log of the slave node. The slave node will start the SQL thread to read the binary log from the relay log. Replay locally to keep its data consistent with that of the master node. Finally, I/O Thread and SQLThread will enter a sleep state, waiting for the next wake-up.

That is:

Two threads will be generated from the library, one I/O thread and one SQL thread;

The I/O thread will request the binlog of the main library and write the obtained binlog to the local relay-log (relay log) file; the main library will generate a log dump thread for the slave library I/O thread Pass binlog;

The SQL thread will read the log in the relay log file and parse it into SQL statements to execute one by one.

Notice:

The master records the operation statement in the binlog log, and then grants the slave remote connection permission (the master must turn on the binlog binary log function; usually for data security considerations, the slave also turns on the binlog function);

The slave opens two threads: IO thread and SQL thread. Among them: the IO thread is responsible for reading the binlog content of the master to the relay log; the SQL thread is responsible for reading the binlog content from the relay log and updating it to the slave database, so as to ensure that the slave data and master data are kept Agree.

mysql master slave working
mysql master slave working

MySQL replication requires at least two MySQL services. Of course, MySQL services can be distributed on different servers, or multiple services can be started on one server;

The time between the master and slave nodes needs to be synchronized.

By default, MySQL replication is asynchronous. The slave does not need to be connected to the master all the time, even if it is disconnected in the middle, it can continue to copy from the disconnected position.

The copy is based on the position of the binlog, and the position must be consistent before copying. (Note: This is required by the traditional copy method)

The purpose of the binary log is to restore the fixed-point database and master-slave replication, so for security and functional considerations, it is highly recommended not to put the binary log and datadir on the same disk.

Specific steps:

The first step is to record the binary log on the main server. Before each transaction that updates data is completed, the master server records the data changes in the binary log. Even if the transaction is interleaved during execution, MySQL will write the transaction serially to the binary log. After writing the event to the binary log, the primary server tells the storage engine to commit the transaction.

In the second step, the slave server copies the binary log of the master server to its own hard disk and enters the so-called “relay log”. First, it starts a worker thread, called the I/O thread. This I/O thread opens a normal client connection, and then starts a special binary log dump process (it has no corresponding SQL commands). This dump process reads data from the binary log of the master server. It does not poll for events. If 3 keeps up with the main server, it will enter the dormant state and wait for a signal from the main server when a new event occurs. The I/O thread writes data into the relay log of the slave server.

In the third step, the SQL thread reads the relay log, and replays the events in it, and then updates the data from the server. Since this thread can keep up with the I/O thread, the relay log is usually in the cache of the operating system, so the overhead of the relay log is very low. SQL thread execution events can also be written to the slave server’s own binary log, which is very useful for some scenarios.

Configuring master-slave replication can be summarized as the following steps:

1. On the main server, the binary log mechanism must be turned on and an independent ID must be configured

2. On each slave server, configure a unique ID and create an account to specifically copy the data of the master server

3. Before starting the copy process, record the location information of the binary file on the master server

4. If there is already data in the database before starting to copy, you must first create a data snapshot (you can use mysqldump to export the database, or copy the data file directly)

5. Configure the IP address and login authorization of the master server to be connected from the server, the binary log file name and location

Setup MySQL Master Slave

Prepare the environment:

Launch Two EC2 Instance of Type Amazon Linux 2.

Screenshot 2021 09 09 at 5.35.17 PM

MASTER IP: 3.144.121.220

SLAVE IP: 18.118.7.55

NAME="Amazon Linux"
VERSION="2"
ID="amzn"
ID_LIKE="centos rhel fedora"
VERSION_ID="2"
PRETTY_NAME="Amazon Linux 2"
ANSI_COLOR="0;33"
CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
HOME_URL="https://amazonlinux.com/"

Operation on Both Master and Slave Table

Install MySQL on both servers. Use the Below command to Setup MySQL and Reset Password for MySQL.

wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
yum localinstall -y mysql57-community-release-el7-10.noarch.rpm
yum repolist enabled | grep "mysql.*-community.*"
mysql master slave
yum install -y mysql-community-server
systemctl start mysqld
netstat -nutpl | grep mysql
mysql master slave example
To Check if MySQL is running or not – MySQL master slave example
ps -ef | grep mysql
mysql master slave example
mysql master slave example
grep 'temporary password' /var/log/mysqld.log
mysql master slave example
To View Temporary Password of MySQL – MySQL master slave example

In order to reset the password of MySQL uses the below command.

mysql_secure_installation

Operation on Master Table

vi /etc/my.cnf

Add the below content in my.cnf file

server-id = 220
log-bin = master-bin
binlog-do-db = mydb
binlog-ignore-db = mydb1
binlog-format = mixed
sync_binlog = 1
expire_logs_days = 30
skip_name_resolve = ON
LabelDescription
server-idThe unique ID of the master01 server, generally the last segment of the IP, the master and the slave cannot be repeated
log-binopen bin-log, and specify the file directory and prefix
binlog-do-dbThe database that needs to be synchronized; if you need to synchronize multiple libraries, write multiple rows; if you don’t write this row, all libraries will be synchronized by default
binlog-ignore-dbDatabase that does not need to be synchronized; if you do not need to synchronize multiple libraries, you can write multiple lines; it can also be on one line, and each library name is separated by a comma
binlog-format
Binary log format, there are row, statement, mixed three formats.
Row refers to copying the changed content instead of executing the command on the slave server.
The statement refers to the SQL statement executed on the master server, and the same statement is executed on the slave server. MySQL uses statement-based replication by default, which is more efficient.
Mixed refers to the use of statement-based replication by default. Once the statement-based replication cannot be accurately found, row-based replication will be used; it can prevent primary key duplication.
sync_binlogEnsure that the binlog log is synchronized with the hard disk after it is written
expire_logs_daysautomatically clean up the log files 7 days ago, which can be modified as needed
skip_name_resolveskip reverse domain name resolution

Restart MySQL and login to MySQL to perform remaining operation.

systemctl restart mysqld
mysql -uroot -p

Create Sample DB mydb for binlog-do-db and insert sample Row.

CREATE DATABASE IF NOT EXISTS mydb;
use mydb;
CREATE TABLE IF NOT EXISTS tt1 (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
INSERT INTO mydb.tt1 VALUES(1,"Durgesh"),(2,"Rajan");
SELECT * FROM mydb.tt1;
mysql master slave example
View Entry of binlog allowed database – MySQL master slave example

Create Sample DB mydb1 for binlog-ignore-db and insert sample Row.

CREATE DATABASE IF NOT EXISTS mydb1;
use mydb1;
CREATE TABLE IF NOT EXISTS tt2 (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
INSERT INTO mydb1.tt2 VALUES(1,"Game"),(2,"Blogging");
SELECT * FROM mydb1.tt2;
mysql master slave example
View Entry of binlog ignore database – MySQL master slave example

To check whether the log Bin is On or Off run the below query in MySQL.

show variables like '%log_bin%' ;    
mysql master slave example
To Check, bin log is enable or not – MySQL master slave example
show master logs;
mysql master slave example
mysql master slave example

Create a Backup account and authorize the slave.

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'18.118.%.%' IDENTIFIED BY '[email protected]';
  1. Replication slave is a necessary permission for mysql synchronization, do not authorize all permissions here
  2. *.* means all tables in all libraries, and you can also specify specific libraries and tables to copy. For example, in mydb.tt1, mydb is the library name and tt1 is the table name.
  3. ‘backup’@’18.118.%.%’ : backup is the synchronization account. 18.118.%.% is the authorized host network segment, using% means that the entire 172.16.0.0 network segment is allowed to use the backup user to access the database
  4. [email protected] is the password, set a complex password in the actual environment
flush privileges; -- Refresh permissions
select user,host from mysql.user where user='backup';
mysql master slave example
To View user and host of user backup – MySQL master slave example

View Backup User Authorization

show grants for [email protected]'18.118.%.%';
mysql master slave example
To View grant of user – mysql master slave example

Lock the table in the main database as read-only to prevent data from being written when the database is exported. unlock tables command to unlock

flush table with read lock;
show variables like '%timeout%';
mysql master slave example
To view all the timeout operation – MySQL master slave example

In Order to see master status run the below query in MySQL.

show master status;    
mysql master slave example
To View-Master Status in master server- MySQL master slave example

After locking the table, be sure to open a new SSH window, export the mydb database, and send it to the slave

mysqldump -uroot mydb -p > /tmp/mydb.sql
rsync -avz /tmp/mydb.sql [email protected]:/tmp/
mysql master slave example
send file to slave server using rsync(To keep sync with slave server) – mysql master slave example

After Transfering unlock the table in MySQL.

unlock tables;

Operation on Slave

vi /etc/my.cnf

Add Below content in my.cnf file.

server-id = 55
log-bin = slave-bin
replicate-do-db = mydb
replicate-ignore-db = mydb1
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
LabelDescription
server-idThe unique ID of the slave01 server, generally the last segment of IP, master and slave cannot be repeated
log-binopen bin-log, and specify the file directory and prefix
replicate-do-dbThe name of the database is to be synchronized. If you do not specify which libraries to synchronize, remove this line to indicate that all libraries are synchronized (except for the libraries ignored by ignoring)
replicate-ignore-dbdoes not need to synchronize the database
relay-logrecord the synchronization log file on the main server to the local
relay-log-indexdefines the location and name of the relay-log
systemctl restart mysqld
show variables like 'server_id';
mysql master slave example
show variables like '%log_bin%';
mysql master slave example

creates mydb empty library, otherwise, an error will be reported when importing data below that this library does not exist

CREATE DATABASE IF NOT EXISTS mydb;
source /tmp/mydb.sql

Configure master-slave synchronization instructions

stop slave;

Run Below Query to change master to master_host

change master to master_host='3.144.121.220',master_port=3306,master_user='backup',master_password='[email protected]',master_log_file='master-bin.000001',master_log_pos=863;
Label Description
master_hostMaster IP
master_portMaster Port
master_userCreate a user for copying on the master
master_passwordCopy the user’s password
master_log_fileThe name of the binary log file – show master status
master_log_posPosition of the log file – show master status

The principle of the above operation is actually to write the user password and other information into a new master.info file from the library. To View use the below command.

cat /var/lib/mysql/master.info
Screenshot 2021 09 09 at 7.12.40 PM

Now When you perform Insert Operation on Master it will be replicated on Slave.

mysql master slave example
the final output of mysql master slave example

All of the above is the master-slave replication based on binlog implemented between the Mysql-Master master database and the Mysql-Slave01 slave database, that is, the “one master, one slave” architecture. For Creating multiple slave repeat slave process.

Hope you like our MySQL Master Slave Replication Blog. Please subscribe to our blog for upcoming blogs.

Happy Coding!

Read More: Migrate data from Postgres to MySQL, Deploys a code to Production | Frontend | Backend

Be the first to comment on "MySQL Master Slave Replication | Complete Guide"

Leave a comment

Your email address will not be published.


*