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.
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.
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 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.
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.
MASTER IP: 220.127.116.11
SLAVE IP: 18.104.22.168
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.*"
yum install -y mysql-community-server systemctl start mysqld netstat -nutpl | grep mysql
ps -ef | grep mysql
grep 'temporary password' /var/log/mysqld.log
In order to reset the password of MySQL uses the below command.
Operation on Master Table
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
|server-id||The unique ID of the master01 server, generally the last segment of the IP, the master and the slave cannot be repeated|
|log-bin||open bin-log, and specify the file directory and prefix|
|binlog-do-db||The 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-db||Database 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|
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_binlog||Ensure that the binlog log is synchronized with the hard disk after it is written|
|expire_logs_days||automatically clean up the log files 7 days ago, which can be modified as needed|
|skip_name_resolve||skip reverse domain name resolution|
Restart MySQL and login to MySQL to perform remaining operation.
systemctl restart mysqld mysql -uroot -p
Create Sample DB
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;
Create Sample DB
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;
To check whether the log Bin is On or Off run the below query in MySQL.
show variables like '%log_bin%' ;
show master logs;
Create a Backup account and authorize the slave.
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'18.118.%.%' IDENTIFIED BY '[email protected]';
- Replication slave is a necessary permission for mysql synchronization, do not authorize all permissions here
- *.* 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.
- ‘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
- [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';
View Backup User Authorization
show grants for [email protected]'18.118.%.%';
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%';
In Order to see master status run the below query in MySQL.
show master status;
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/
After Transfering unlock the table in MySQL.
Operation on Slave
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
|server-id||The unique ID of the slave01 server, generally the last segment of IP, master and slave cannot be repeated|
|log-bin||open bin-log, and specify the file directory and prefix|
|replicate-do-db||The 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-db||does not need to synchronize the database|
|relay-log||record the synchronization log file on the main server to the local|
|relay-log-index||defines the location and name of the relay-log|
systemctl restart mysqld
show variables like 'server_id';
show variables like '%log_bin%';
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
Run Below Query to change master to master_host
change master to master_host='22.214.171.124',master_port=3306,master_user='backup',master_password='[email protected]',master_log_file='master-bin.000001',master_log_pos=863;
|master_user||Create a user for copying on the master|
|master_password||Copy the user’s password|
|master_log_file||The name of the binary log file – |
|master_log_pos||Position of the log file – |
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.
Now When you perform Insert Operation on Master it will be replicated on Slave.
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.