MySQL Replication (Master -Slave 연동) - 1

"https://www.ddhigh.com/en/2024/03/17/understanding-replication-in-mysql/" 인용

 

 

MySQL Replication 연동 실습

• MySQL Master 1대, Slave 1대로 구성된 Replication 환경 구축

 

환경 설정

1. 클라우드 환경

• Naver Cloud (무료 크레딧 활용) 
  • Master 서버 :
    • 공인 IP 
      •  Port 
        • 22(SSH)
        • 3306(MySQL)
  • Slave 서버 :
    • 공인 IP
      • Port
        • 22(SSH)
        • 3306(MySQL)

 

2. 운영체제

• Rocky Linux 8.10

 

3. 소프트웨어 구성

• MySQL : 8.0.40

---

MySQL Replication

1.  Master 서버 설정

# 패키지 업데이트
sudo dnf update --exclude=kernel* -y

# 새 사용자 'mysqladmin'
useradd -m -G wheel mysqladmin
# mysqladmin 비밀 번호 설정
echo "mysqladmin" | passwd --stdin mysqladmin

# MySQL yum 리포지토리 설치
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

• Master 패키지 업데이트, 사용자 추가, 리포지토리 설치를 합니다.

 

 

# MySQL 관련 모듈을 초기화 하여 다른 설정이나 버전이 적용되지 않도록 합니다.
sudo dnf module reset mysql

# MySQL 모듈을 비활성화 합니다.
sudo dnf module disable mysql -y

# mysql-community-server를 설치합니다.
sudo dnf install -y mysql-community-server

# MySQL 서비스를 부팅 시 자동 시작하게 합니다.
sudo systemctl enable --now mysqld

• MySQL 설정을 해줍니다.

 

# MySQL 서버가 처음 생성되고 실행된 후 생성되는 임시 비밀번호를 출력합니다.
sudo grep 'temporary password' /var/log/mysqld.log

# MySQL 서버 설치 후 보안 설정을 강화합니다.
mysql_secure_installation

mysql_secure_installation은 MySQL 서버의 보안을 강화하기 위해 아래 과정을 거칩니다.

1. 루트 비밀번호 설정
2. 익명 사용자 제거
3. 원격 접속 제한
4. 테스트 데이터베이스 삭제

(그냥 저는 비밀번호 설정, y, y, y, y 했습니다.)

 

 

# 방화벽 실행 및 추가
sudo systemctl enable --now firewalld
sudo firewall-cmd --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

• 방화벽을 실행하고 MySQL 송신이 되도록 포트를 추가합니다.

 

# Mysql root 접속
mysql -u root -p

# replica_db라는 이름의 DATABASE 생성
CREATE DATABASE replica_db DEFAULT CHARACTER SET utf8;

# 레플리케이션 전용 계정 생성
CREATE USER 'replica_user'@'%' IDENTIFIED BY '<PWD>';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

# 관리자 계정 생성
CREATE USER 'admin_user'@'%' IDENTIFIED BY '<PWD>';
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

• MySQL에 접속하여 데이터 베이스와 레플리케이션, 관리자 계정을 만들어 줍니다.

sudo vi /etc/my.cnf

#아래 내용을 추가해 줍니다.
# 바이너리 로그 기능을 활성화합니다.
log-bin=mysql-bin

# MySQL 서버의 고유한 식별자(ID)를 설정합니다
server-id=1

# MySQL 설정이 변경된 것을 적용하기 위해 재시작 합니다.
systemctl restart mysqld

• MySQL서버의 전역 설정 파일을 변경합니다.

 

# MySQL 데이터를 백업 합니다.
mysqldump -u admin_user -p replica_db > replica_db.sql1

• admin_user라는 사용자의 MySQL에 접속하여 replica_db라는 데이터베이스를 백업합니다.

 

# 데이터베이스 파일을 Slave 서버로 전송합니다
scp -P 22 replica_db.sql1 mysqladmin@<Slave IP>:/tmp

• 백업파일을 Slave 서버로 전송합니다.

 

# MySQL에서 Master 정보를 확인합니다.
show master status;

 

• Master - Slave 연동을 위해서는 Master의 정보가 필요합니다.

 

---

2.  Slave 서버 설정

 

# 패키지 업데이트
sudo dnf update --exclude=kernel* -y

# 새 사용자 'mysqladmin'
useradd -m -G wheel mysqladmin
# mysqladmin 비밀 번호 설정
echo "mysqladmin" | passwd --stdin mysqladmin

# MySQL yum 리포지토리 설치
sudo dnf install -y https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

 

# MySQL 관련 모듈을 초기화 하여 다른 설정이나 버전이 적용되지 않도록 합니다.
sudo dnf module reset mysql

# MySQL 모듈을 비활성화 합니다.
sudo dnf module disable mysql -y

# mysql-community-server를 설치합니다.
sudo dnf install -y mysql-community-server

# MySQL 서비스를 부팅 시 자동 시작하게 합니다.
sudo systemctl enable --now mysqld

 

# MySQL 서버가 처음 생성되고 실행된 후 생성되는 임시 비밀번호를 출력합니다.
sudo grep 'temporary password' /var/log/mysqld.log

# MySQL 서버 설치 후 보안 설정을 강화합니다.
mysql_secure_installation

 

# MySQL에 root로 접속합니다.
mysql -u root -p

# 데이터베이스를 생성합니다.
CREATE DATABASE replica_db DEFAULT CHARACTER SET utf8;

 

# 방화벽 실행 및 추가
sudo systemctl enable --now firewalld
sudo firewall-cmd --add-port=3306/tcp --permanent
sudo firewall-cmd --reload

• 여기까지는 Master와 Slave가 거의 동일하기에 따로 사진은 남기지 않았습니다.

 

• ls 명령어를 통해 Slave서버 /tmp에 백업파일이 전송되어 있는 것을 확인할 수 있습니다.

 

sudo vim /etc/my.cnf

##아래 내용을 추가합니다.
# slave1은 2, slave2는 3으로 설정
server-id = 2
# 읽기 전용으로 설정 (쓰기 불가능, root예외)
read_only = 1
max_binlog_size = 100M
expire_logs_days = 10

# 설정 파일 적용을 위해 재시작합니다.
sudo systemctl restart mysqld

• Slave서버를 읽기 전용으로 설정합니다.
• 바이너리 로그의 최대 크기를 100MB로 설정합니다.(초과하게 된다면 새 바이너리 로그파일을 생성 합니다.
• 바이너리 보관 기간을 10일로 설정합니다.(10일이 지나면 자동으로 삭제합니다.)

 

# Master한테 받은 replica_db.sql1 백업파일의 내용을 MySQL에 삽입합니다.
mysql -u root -p replica_db < replica_db.sql1

• replica_db라는 데이터 베이스가 만들어져 있어야합니다.(위에서 먼저 만들었기에 문제가 되지 않습니다.)

 

# Master 서버와 Slave 서버를 연결하기 위한 설정입니다.
change master to
master_host='<Master IP>',
master_user='replica_user',
master_password='<Master PW>',
master_log_file='mysql-bin.000002',
master_log_pos=948;

• password는 Master - MySQL 비밀번호 입니다.

 

# Master-Slave 연결상태를 확인하는 명령어 입니다.
show slave status\G;

• Slave_IO_Running, Slave_SQL_Running이 Yes로 되어 있어야 합니다.
• 만약 이부분에서 안될 시 별도의 설정을 추가로 해야합니다.

 

---

Master-Slave 연결 실패 시

1. Master 서버 MySQL에 접속하여 plugin 정보를 확인합니다.

# MySQL에서 replica_user의 관련 정보를 확인하는 쿼리입니다.
SELECT user, host, plugin FROM mysql.user WHERE user = 'replica_user';

• Master 서버 MySQL에서 입력해야합니다.

 

# replica_user의 인증방법과 비밀번호를 수정합니다.
ALTER USER 'replica_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY '<PWD>';

• caching_sha2_password 방법은 mysql_native_password방법 보다 안전한 인증방식이지만 최신버전에 사용하는 방법으로 오류가 생길 수 있습니다.

• 다시 명령어를 입력하여 바뀐 것을 확인합니다.

---

3. Master - Slave 연동 확인

# Master - MySQL 에서 테이블 및 데이터 값을 넣어봅니다.
USE replica_db;
CREATE TABLE test_replication (id INT PRIMARY KEY, value VARCHAR(50));
INSERT INTO test_replication (id, value) VALUES (3, 'Replication Test3');

# 데이터 값 확인하는 명령어 입니다.
select * from test_replication;

• Master - Slave 연동이 되어 있기 때문에 Master에서 입력한 정보가 Slave에서도 나오게 됩니다.

 

• Slave - MySQL에서 확인시 Master에서 입력한 정보가 저장되어 있는 것을 확인할 수 있습니다.

---

이상으로 MySQL Replication, Master -Slave 연동을 마치겠습니다. 감사합니다.