🟦 MySQL DB 싱크
Docker 내의 모비우스 MySQL에서 로컬 MySQL로의 데이터 실시간 전송
🔵 필요성
1. 본 프로젝트의 주제는 스마트 양식으로 5가지 수질 센서를 사용함
2. 수질 센서로 측정한 데이터는 Mobius 플랫폼의 DB(MySQL)로 들어옴
3. 서비스 구현을 위한 별도의 DB 사용
➡️ 플랫폼 DB로 들어온 데이터를 서비스 DB에 전달해줘야 함
🔵 Replication 이란?
Replication(복제)는 데이터베이스 시스템에서 사용되는 개념
복제는 주로 데이터의 가용성, 내구성, 성능 향상, 데이터 분석 및 로드 분산 등의 목적으로 사용
데이터베이스의 변경 사항을 한 서버에서 다른 서버 또는 여러 서버로 자동으로 복사하는 프로세스
➡️ 데이터베이스 시스템의 성능, 안정성, 확장성을 향상시키는 강력한 기능이며, 데이터베이스 관리자들에게 많은 이점을 제공함
🔵 Replication 과정
- Docker Mobius MySQL 실행 방법
// Mobius 컨테이너 접속
docker exec -it 컨테이너명(ex:mobius) bash
// Mobius 내의 MySQL 실행
mysql -u MySQL사용자(ex:root) -p
password : 비밀번호입력
//데이터베이스 생성
CREATE DATABASE 스키마명;
//데이터베이스 선택
USE 스키마명;🔹 1. Slave MySQL 서버의 my.cnf 파일 수정 or 추가
[mysql] 섹션 아래에 다음 라인 추가
server-id = <유니크한 서버 아이디>
유니크한 서버 아이디는 사용자가 임의로 설정하며 중복되어서는 안된다
Slave 서버는 로컬 서버이고 ELK는 local MySQL을 사용함
MySQL의 설치 파일 경로에 my.cnf 파일이 없어 별도의 공간에 my.txt 파일을 생성한 후
[mysqld]
server-id = 1
log_bin = mysql-bin // 로그 옵션
을 입력, 저장한 후에 파일 확장자를 cnf로 바꿨음
my.cnf를 MySQL 설치파일 경로로 이동 (MySQL 서버/bin)
🔹 2. Master MySQL 서버의 my.cnf 파일 수정
Master 서버인 mobius의 my.cnf 파일 수정
vi /etc/mysql/my.cnf
my.cnf 파일 내에 [mysqld] 섹션이 없어 만듦
[mysql] 섹션을 만들고 아래에 다음과 같은 내용을 추가
[mysqld]
server-id = 2
log_bin = mysql-bin
🔹 3. Master 서버의 사용자 생성
: Master MySQL 서버에서 Slave MySQL 서버가 사용할 복제용 사용자를 생성한 후 적절한 권한을 부여
CREATE USER '사용자이름'@'SlaveMySQL호스트' IDENTIFYED BY '비밀번호';
GRANT 권한 ON 데이터베이스이름.* TO '사용자이름'@'SlaveMySQL호스트';
🔹 4. Master MySQL 서버의 데이터베이스 백업 및 복원
: Master MySQL 서버에서 데이터베이스를 백업하여 Slave MySQL 서버로 복원해야 함.
이를 통해 초기 데이터 동기화를 수행할 수 있음
mysqldump -u <사용자> -p <데이터베이스> > <백업파일명>.sql;
백업파일명은 임의로 설정
Access denied; you need (at least one of) the PROCESS privilege(s) for this operation" Error 발생
➡️ mysqldump 명령 실행에 필요한 PROCESS 권한이 부족하다는 것을 의미
➡️ 사용자에게 PROCESS 권한을 부여해야 함
GRANT PROCESS ON.* TO '사용자이름'@'SlaveMySQL호스트';
그러나 여전히 백업 명령어를 입력하였으나 아래와 같이
mysqldump: Got error: 1044: Access denied for user 'replication_aqua'@'localhost' to database 'aquafarm' when using LOCK TABLES Error 발생
➡️사용자가 데이터베이스의 테이블을 LOCK하는 작업에 대한 권한이 없기 때문에 발생
➡️ LOCK TABLES 권한이 필요한 경우, 해당 사용자에게 LOCK TABLES 권한을 부여해야 함
GRANT LOCK TABLES ON 데이터베이스.* TO '사용자이름'@'SlaveMySQL호스트';
이후 다시 백업 명령어를 수행
에러가 발생하지 않으면 다음과 같은 명령어로 파일 생성을 확인
ls -l <백업파일명>.sql // 파일 생성 확인
*️⃣ 권한 부여 및 백업/복원 요약
GRANT SELECT, INSERT, UPDATE, DELETE ON aquafarm.* TO replication_aqua@localhost;
GRANT PROCESS ON *.* TO '사용자이름'@'Slave_MySQL_호스트';
GRANT LOCK TABLES ON 데이터베이스.* TO '사용자이름'@'Slave_MySQL_호스트';
exit
mysqldump -u <사용자> -p <데이터베이스> > <백업파일명>.sql;🔹5. Slave MySQL 서버의 복제 설정
: Slave MySQL 서버에서 Master MySQL 서버를 마스터로 설정
- Slave 서버의 my.cnf 수정
server-id = <로컬 MySQL 서버의 고유한 서버 ID>
log_bin = mysql-bin
binlog_do_db = <복제할 데이터베이스 이름> // 추가
- Slave MySQL 쿼리문 수행
CHANGE MASTER TO MASTER_HOST='<모비우스 MySQL 서버의 IP 주소>',
MASTER_USER='<복제를 위한 사용자 이름>',
MASTER_PASSWORD='<복제를 위한 사용자 비밀번호>',
MASTER_LOG_FILE='<모비우스 MySQL 서버의 로그 파일>',
MASTER_LOG_POS=<모비우스 MySQL 서버의 로그 위치>;
- Dokcer Container IP주소 확인
cmd에서 아래 명령어 입력
docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 컨테이너명- LOG_FILE, LOG_POS 확인
Mobius mysql 아래 쿼리문 실행
SHOW MASTER STATUS;FILE 및 POSTION 열의 값이 각각 FILE, POS
(혹은 Mobius MySQL 서버의 my.cnf 또는 my.ini 열어서 log.bin 옵션을 찾음
해당 옵션의 값이 FILE, log-bin-index 옵션의 값이 POS)
🔹 6. 복제 시작
: 로컬 MySQL 서버에서 START SLAVE 문을 실행하여 복제를 시작합니다. 로그 파일과 위치 정보를 기반으로 데이터가 복제됩니다.
START SLAVE; // 복제 시작
SHOW SLAVE STATUS // 복제 상태 확인
DDeo99