MySQL 데이터베이스 동기화

1. MySQL 데이터베이스 동기화
2. 소스 시스템 방화벽 구성
3. 소스 데이터베이스 구성
4. 복제 사용자 생성
5. 슬레이브 서버 구성

이 기사는 백업 및 기록 보관 목적으로 데이터베이스 동기화를 달성하는 방법을 설명하는 것을 목표로 합니다.

MySQL 데이터베이스 동기화

많은 양의 데이터를 처리하는 동안 데이터가 손상될 수 있는 시나리오를 고려하는 것이 중요할 수 있습니다.

해킹된 이유는 이 특정 문제를 해결하기 위해 다양한 전략을 설계하거나 계획할 때 고려해야 하는 많은 요인에 따라 달라질 수 있습니다.

검색 조치를 구현하도록 선택하는 것은 매우 중요합니다. 데이터의 가치에 따라 데이터가 영구적으로 손실되어 백업 예비 없이 어떤 기술적 결함으로 데이터가 손실되는 경우 회사 또는 설립에 막대한 손실이 발생할 수 있습니다.

해결책

이 문제에 대한 해결책은 다양한 조건과 제한 사항에 따라 달라질 수 있습니다. 대체로 데이터베이스에 대한 가장 일반적인 접근 방식 중 하나는 다음과 같습니다.

1. 클라이언트/서버 모델 - 데이터베이스를 사용하는 대신 서버와 직접 통신하는 애플리케이션을 사용하여 데이터에 액세스/저장합니다.
2. 마스터/슬레이브 모델 - 한 서버는 데이터 쓰기(마스터)에 할당되고 다른 서버(둘 이상일 수 있음)는 데이터 읽기 전용(슬레이브) 전용입니다.
3. 오프라인 모델 - 일정 시간이 지나면 로컬에서 필요한 작업을 수행하여 서버로 보냅니다.

이 기사에서는 마스터/슬레이브 모델 구현에 대해 설명합니다.

전제 조건

1. 두 대의 시스템이 Ubuntu(20.0+)를 실행하는 서버 역할을 합니다. 각 머신에는 관리자가 아닌 사용자와 sudo 권한이 적절하게 구성되어 있어야 합니다. 또한 방화벽을 UFW로 구성해야 합니다.
2. 두 시스템 모두에 MySQL이 설치되어 있습니다. 가급적이면 최신 버전(8.0+).

소스 시스템 방화벽 구성

UFW를 사용하여 방화벽을 설정하면 방화벽이 복제 서버에서 들어오는 모든 연결을 차단합니다. 이 문제를 해결하려면 소스 시스템에서 다음 명령을 실행하십시오.

sudo ufw allow REPLICA_SERVER_IP_ADDR to any port MySQL_PORT

성공적으로 실행되면 출력이 표시됩니다.

Rule Added

다음 명령에 유의하십시오.

1. REPLICA_SERVER_IP_ADDR - 복제 서버의 IP 주소.
2. MySQL_PORT - MySQL 서버의 포트(기본값은 3306)입니다.

위의 명령에서 UFW에 IP REPLICA_SERVER_IP_ADDR에서 포트 MySQL_PORT로 들어오는 모든 연결을 허용하도록 지시하는 규칙을 추가하고 있습니다.

소스 데이터베이스 구성

데이터 복제를 진행하려면 원본 데이터베이스에 대해 일부 설정을 조정해야 합니다.

이를 위해서는 mysqld.cnf라는 MySQL 서버 구성 파일에 액세스해야 합니다. Ubuntu 20+에서는 /etc/mysql/mysql.conf.d/ 디렉토리에서 찾을 수 있습니다.

원하는 텍스트 편집기를 사용하여 이 파일을 엽니다. 또는 다음 명령을 사용합니다.

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

mysql.cnf 파일에서 다음 줄을 검색합니다.

. . .
bind-address            = 127.0.0.1
. . .

bind-address 속성은 데이터베이스가 localhost를 나타내는 127.0.0.1의 연결을 수락할 위치를 나타냅니다. 즉, 서버는 현재 서버가 설치된 시스템의 연결 요청만 수락합니다.

복제본 서버에서 연결을 수락하려면 bind-address 속성에 지정된 주소를 변경해야 합니다. 원본 서버의 IP 주소로 변경하면 문제가 해결됩니다.

IP 주소를 변경한 후 파일은 다음과 같아야 합니다.

. . .
bind-address            = SRC_SERVER_IP_ADDR
. . .

SRC_SERVER_IP_ADDR은 소스 시스템의 IP 주소입니다.

다음으로 마스터에 고유 ID를 할당해야 합니다. 각 서버는 복제된 환경의 일부이므로 서버 간에 충돌이 발생하지 않도록 각 서버에 고유한 ID를 할당해야 합니다.

이렇게 하려면 mysql.cnf 파일에서 server-id를 검색하십시오. 결과는 다음과 같습니다.

. . .
# server-id             = 1
. . .

# 기호는 이 행이 현재 주석 처리되었음을 나타냅니다. 부호를 삭제하고 서버에 할당할 정수 값을 선택합니다. 변경 후 라인은 다음과 같이 표시됩니다.

. . .
server-id             = 10
. . .

그런 다음 log_bin 지시문을 검색하고 주석을 제거하십시오. log_bin은 복제본 서버가 데이터베이스를 복제하는 방법을 이해하기 위해 읽는 데 필요합니다.

변경 후 라인은 다음과 같아야 합니다.

. . .
log_bin                       = /var/log/mysql/mysql-bin.log
. . .

마지막으로 binlog_do_db 지시문을 검색합니다. 이 줄도 주석 처리되며 주석 처리를 제거하고 해당 값을 복제하려는 데이터베이스의 이름으로 설정합니다. 수정 후에는 다음과 같이 표시됩니다.

. . .
binlog_do_db          = DB_NAME

# For multiple DBs
binlog_do_db          = DB_NAME_1
binlog_do_db          = DB_NAME_2
. . .

또는 binlog_ignore_db를 사용하여 특정 데이터베이스를 제외하고 나머지를 복제할 수 있습니다.

. . .
binlog_ignore_db          = DB_NAME_TO_IGNORE
. . .

mysqld.cnf에서 이러한 변경을 수행한 후 파일을 저장하고 mysql 서비스를 다시 시작하십시오.

아래 명령을 사용하여 그렇게 할 수 있습니다.

sudo systemctl restart mysql

복제 사용자 생성

복제본 서버는 원본 서버에 연결하기 위해 사용자 이름과 암호를 제공해야 합니다. 복제 서버는 원본 시스템에서 사용 가능한 모든 사용자 프로필을 사용하여 연결할 수 있지만 이 자습서에서는 복제 전용 사용자를 생성합니다.

단계에 따라 복제 목적으로 사용자를 생성합니다.

소스 시스템에서 다음 명령을 실행합니다.

sudo mysql -u USER -p PASS

어디:

1. USER - 사용자의 사용자 이름
2. PASS - 사용자의 비밀번호

MySQL 프롬프트 내에서 다음 명령을 사용하여 새 사용자를 만들 수 있습니다.

mysql> CREATE USER 'REPLICA_USER'@'REPLICA_IP' IDENTIFIED WITH mysql_native_password BY 'PASS';

어디:

1. REPLICA_USER - 복제 서버 사용자의 사용자 이름
2. REPLICA_IP - 복제 머신의 IP 주소
3. PASS - 사용자의 비밀번호

사용자가 성공적으로 생성되면 적절한 권한을 부여해야 합니다. 사용자는 최소한 REPLICATION SLAVE를 가지고 있어야 합니다.

다음 명령을 사용하여 사용자 애플리케이션을 승인하십시오.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'REPLICA_USER'@'REPLICA_IP';

그런 다음 GRANT 및 CREATE USER 명령을 사용하여 사용된 메모리 및 캐시를 지우기 위해 권한을 플러시해야 합니다.

mysql> FLUSH PRIVILEGES;

마지막으로 데이터베이스의 상태를 확인하고 읽기 잠금을 사용하여 로그 상태 캡처가 진행되는 동안 쓰기 작업이 발생하지 않도록 해야 합니다.

이렇게 하려면 다음 명령을 실행합니다.

mysql> FLUSH TABLES WITH READ LOCK;

그런 다음 다음 명령을 사용하여 서버의 상태를 확인합니다.

mysql> SHOW MASTER STATUS;

다음 형식으로 출력을 반환합니다.

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      899 | db           |                  |                   |
| mysql-bin.000002 |      900 | db2          |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
2 rows in set (0.01 sec)

그런 다음 mysqldump를 사용하여 전체 데이터베이스를 복제할 수 있습니다.

sudo mysqldump -u root db > db.sql

new_db.sql이라는 파일이 생성되며, 이 파일을 슬레이브 시스템으로 전송하여 백업 프로세스를 완료할 수 있습니다.

백업이 완료되면 테이블 잠금을 해제할 수 있습니다.

mysql> UNLOCK TABLES

슬레이브 서버 구성

슬레이브 서버에서는 데이터베이스가 복제될 새 데이터베이스를 생성해야 합니다. 다음과 같이 할 수 있습니다.

mysql> CREATE DATABASE DATABASE_NAME

이제 소스 시스템에서 내보낸 sqldump를 가져옵니다.

sudo mysql db < /PATH/db.sql

데이터베이스를 슬레이브 시스템으로 가져오면 mysqld.cnf에서 다음과 같이 지시문을 구성합니다.

server-id = 11
log_bin = /data/mysql/mysql-bin.log
binlog_do_db = DATABASE_NAME

필요한 사항을 변경한 후 MySQL 서비스를 다시 시작합니다.

sudo systemctl restart mysql

마지막으로 서버에서 데이터 복제를 시작하려면 다음 명령을 실행합니다.

로그인 후 MySQL 프롬프트에서 다음과 같이 하십시오.

mysql> CHANGE REPLICATION SOURCE TO
mysql> SOURCE_HOST='SRC_SERVER_IP_ADDR',
mysql> SOURCE_USER='REP_USER',
mysql> SOURCE_PASSWORD='PASS',
mysql> SOURCE_LOG_FILE='mysql-bin.000001',
mysql> SOURCE_LOG_POS=899;

마지막으로 슬레이브 서버를 시작합니다.

mysql > START REPLICA

이제 서버는 원본 서버에서 변경 사항을 복제하기 시작합니다.

복제본 서버의 상태를 보려면 다음 명령을 사용하십시오.

SHOW REPLICA STATUS\G

\G는 더 나은 가독성을 위해 출력 형식을 다시 지정합니다.

위 명령의 출력은 다음 형식입니다.