Linux MySQL 5.7 Master-Slave Replication Setup Guide: One Master Multiple Slaves

Author’s Homepage:Click Here

Linux Basics (Beginner):Click Here

Advanced Linux Management, Protection, and Clustering:Click Here

Firewalld Firewall in Linux:Click Here

️ Creation Time: July 24, 2024 at 2:10 PM

Article Quality: 93 points

Table of Contents

1. All Node Configuration

Establishing Time Synchronization Environment

Main Node

Secondary Node

2.MySQL Database Installation

3.Configuring Traditional Replication with Single Master

Master Server (Master 1)

Slave Server (Master 2)

4.Testing Master-Slave Synchronization

Cool Website Recommendations

A few days ago, I came across an outstanding artificial intelligence learning website. It’s easy to understand, funny and engaging, so I couldn’t help but share it with everyone. Whether you’re interested in AI or machine algorithms, this site is worth checking out. Click here to visit! https://www.captainbed.cn/sis

Office AI Tool Recommendations

BaoYueAI – Your Intelligent AI Reading AssistantBaoYueAI is your intelligent AI reading assistant. It supports one-click summaries, AI Q&A, multi-language translation, and works with web reading, academic papers, legal documents, research reports, product manuals, market analysis, eBooks, and more. BaoYueAI helps you read broadly, understand deeply, and work efficiently.https://baoyueai.com/?utm_id=237582

Click on the above link to redirect to the official website

1. All node configurations

Configure IP address, gateway, DNS, and hostname
Set up network-related information through appropriate configuration files or commands to ensure proper communication between all nodes.
Stop and disable the firewall

setenforce 0 systemctl stop firewalld systemctl disable firewalld

1
2
3

setenforce 0
systemctl stop firewalld
systemctl disable firewalld

These commands are used to shut down SELinux and the firewall to prevent them from blocking communication between nodes.
Download required components

setenforce 0 systemctl stop firewalld systemctl disable firewalld

1
2
3

setenforce 0
systemctl stop firewalld
systemctl disable firewalld

Install development tools and file transfer tools to ensure the system has the necessary development and transfer environment.

Build time-synchronized environment

Main node

Install NTP

setenforce 0 systemctl stop firewalld systemctl disable firewalld

1
2
3

setenforce 0
systemctl stop firewalld
systemctl disable firewalld

Install the NTP service to ensure time synchronization.
Configure NTP

setenforce 0 systemctl stop firewalld systemctl disable firewalld

1
2
3

setenforce 0
systemctl stop firewalld
systemctl disable firewalld

Add the following two lines:

setenforce 0 systemctl stop firewalld systemctl disable firewalld

1
2
3

setenforce 0
systemctl stop firewalld
systemctl disable firewalld

Configure NTP service to use local time as a reference.
Restart service and set to start automatically on boot

systemctl restart ntpd <pre>systemctl enable ntpd

1
2
3

systemctl restart ntpd
<pre>systemctl enable ntpd

Restart the NTP service and set it to start automatically at boot to ensure continuous operation of the time synchronization service.

From Node

Install ntpdate

yum install -y ntpdate

1

yum install –y ntpdate

Install the ntpdate tool to manually synchronize time.
Synchronize Time

yum install -y ntpdate

1

yum install –y ntpdate

Use NTP server IP to synchronize time. For example:

yum install -y ntpdate

1

yum install –y ntpdate

Lingban AI Free ChatGPT4 Artificial Intelligence Browser Plugin (Multi-functional)

Open Method：Click!https://ilingban.com/browser_extension?from=smqnz

(Click to add to Edge browser), very convenient! You can try it!

2.MySQL Database Installation

Download MySQL 5.7.29 installer package

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

1

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

Download the official MySQL installation package from the source.
Extract and create MySQL yum repository

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

1

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

Extract the installation package and create a local YUM repository.
Configure yum repository

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

1

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

Configure the local YUM repository for subsequent installation.
Refresh yum and install MySQL server

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

1

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

Install the MySQL server.

Start and change default management password

[crayon-67f399019e2d9232428287 inline=”true”  class=”language-bash”]systemctl start mysqld
grep “password” /var/log/mysqld.log  //Get default password

1 2	[crayon–67f399019e2d9232428287 inline=“true” class=“language-bash”]systemctl start mysqld grep “password” /var/log/mysqld.log //Get default password

[/crayon]

Start MySQL and change the default management password.

mysqladmin –uroot –p password “Abc-1234” // Change the default password using Abc-1234

Start the MySQL service and change the default root user password.

3. Configure Traditional Replication Single Master

Master Server (master1)

Edit MySQL configuration file

vim /etc/my.cnf

1

vim /etc/my.cnf

Add the following content:

vim /etc/my.cnf

1

vim /etc/my.cnf

Set server ID and enable binary logging.
Restart MySQL service

vim /etc/my.cnf

1

vim /etc/my.cnf

Restart the MySQL service to apply configuration changes.
Create a user with replication permissions

vim /etc/my.cnf

1

vim /etc/my.cnf

Create a user for replication and grant necessary permissions.
View master server status

vim /etc/my.cnf

1

vim /etc/my.cnf

Obtain binary log file and position to configure the slave server.

From Server (master2)

Edit MySQL Configuration File

vim /etc/my.cnf

1

vim /etc/my.cnf

Add the following content:

vim /etc/my.cnf

1

vim /etc/my.cnf

Set the slave server ID.
Restart MySQL Service

systemctl restart mysqld

1

systemctl restart mysqld

Restart the MySQL service to apply configuration changes.
Connect to Master Server

mysql -u username -p

1

mysql –u username –p

Configure the slave server’s replication information based on the master server status.
Start Slave Server

systemctl start mysqld

1

systemctl start mysqld
Check Slave Status

mysql -u username -p

1

mysql –u username –p

Check the slave server replication status to ensure successful connection and synchronization.

4. Testing Master-Slave Synchronization

Create the database and table on the master server, and insert data:

mysql> create database smqnz;
mysql> use smqnz;
mysql> create table test(age int);
mysql> insert into test values(1);
mysql> select * from test;
+——+
| age  |
+——+
|    1 |
+——+

mysql> create database smqnz;

mysql> use smqnz;

mysql> create table test(age int);

mysql> insert into test values(1);

mysql> select * from test;

+———+

| age |

+———+

| 1 |

+———+

Check if the data is synchronized on the slave server:

mysql> create database smqnz;
mysql> use smqnz;
mysql> create table test(age int);
mysql> insert into test values(1);
mysql> select * from test;
+——+
| age  |
+——+
|    1 |
+——+

mysql> create database smqnz;

mysql> use smqnz;

mysql> create table test(age int);

mysql> insert into test values(1);

mysql> select * from test;

+———+

| age |

+———+

| 1 |

+———+

The above operations ensure that the data on the master server is successfully replicated to the slave server, achieving master-slave synchronization.

Summary

Through this article’s introduction, we have successfully configured MySQL database master-slave replication on a Linux system, ensuring real-time data synchronization and backup. This not only improves data security but also provides convenience for future expansion and maintenance. We hope readers can apply the knowledge learned to further explore and optimize their own database management solutions, laying a solid foundation for more efficient business operations.

There are no shortcuts on the road to success; only continuous effort and perseverance. If you believe, like me, that effort brings rewards, please follow me, give me a thumbs-up, and embrace an even brighter tomorrow together!

“Every creation is a learning process. If there are shortcomings in this article, please understand. Your support and encouragement are my greatest motivation. Welcome to share your valuable opinions and suggestions to help me improve continuously.”

The Enigmatic Crying Man

10 responses to “Linux MySQL 5.7 Master-Slave Replication Setup Guide: One Master Multiple Slaves”

Thanks for sharing such a comprehensive guide! The tips on testing master-slave synchronization were invaluable for ensuring everything works smoothly.

Log in to Reply
The step-by-step approach made the complex process of MySQL replication feel manageable. I’ll definitely refer back to this guide if I run into issues.

Log in to Reply
This guide not only taught me how to set up master-slave replication but also introduced me to useful AI tools like BaoYueAI and Lingban AI.

Log in to Reply
The part about configuring NTP was something I wasn’t sure about, but this guide clarified everything. Highly recommended!

Log in to Reply
I appreciated the emphasis on disabling firewalls and SELinux temporarily during setup. It saved me from a lot of headaches!

Log in to Reply
This article is perfect for Linux beginners like me. The clear instructions and commands helped me avoid common pitfalls during setup.

Log in to Reply
The guide made me realize how important time synchronization is for database replication. I’ll make sure to set that up properly next time.

Log in to Reply
Great resource for anyone looking to implement traditional replication in MySQL 5.7. The section on creating a replication user was particularly useful.

Log in to Reply
Thanks to this article, I successfully configured my first MySQL master-slave setup. The tips on firewall and SELinux settings were crucial and not mentioned elsewhere!

Log in to Reply
This guide was incredibly helpful for setting up MySQL 5.7 master-slave replication. The step-by-step instructions made it easy to follow, especially the part about time synchronization which I initially overlooked.

Log in to Reply

IT Log