DB Replication
DB서버 이중화
- AWS 인스턴스를 사용하여 DB서버 이중화 진행
- Master서버 1대와 Slave서버 1대를 생성 (ubuntu22.04/프리티어)
DB 이중화(Replication)
- 단순하게 얘기하면 DB의 복제를 의미함
- 이를 Master와 Slave로 구분하여 복제를 진행
역할
- Master : 데이터 등록, 수정, 삭제용으로 사용 (Binarylog 생성)
- Slave : 데이터 읽기(Binarylog 읽기)
이중화 이유?
- 서비스를 진행하고 있는 애플리케이션이 하나의 DB만 바라보고 있을 때, 이 DB의 장애 발생 시 복제해둔 Slave DB로 변경해서 지속해서 운영
- 또한 실시간으로 데이터를 백업하거나, DB서버의 부하를 분산 목적
DB 이중화 진행
인스턴스 생성
- 우선 AWS의 프리티어 인스턴스를 2개 생성하고 1개는 Master, 1개는 Slave로 지정 (각각 프리티어로 진행하였습니다/고정 ip는 설정 안했습니다...)
- master : 15.165.158.196
- slave : 3.34.97.221
1) Master DB
DB설치 및 유저 생성/권한부여
sudo apt-get update
sudo apt-get install mysql-server
mysql -u root -p✅잠깐
- 저는 여기서 mysql비밀번호를 입력하라고 하는데 저는 설정한 적이 없는데 접속이 안됬습니다.
- 해결법은 https://velog.io/@nbac406/SQL-Access-denied-for-user-rootlocalhost-%EC%98%A4%EB%A5%98%EB%B0%9C%EC%83%9D 여기를 참조 부탁드립니다.
create user 'test3'@'%' identified by '123'; # 유저 생성
grant all privileges on *.* to 'test3'@'%'; # test3 권한 부여
flush privileges; 사용자 권한 변경 적용서버 설정
vi /etc/mysql/mysql.conf.d/mysqld.conf수정- 수정이 안되면 sudo 권한으로 해보세요
[mysqld]
server-id = 1
log-bin = mysql-bin- mysql 재시작
sudo systemctl restart mysql- mysql 재접속 후 Master db 상태 확인
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 1863 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)2) Slave DB
- 위에와 똑같이 mysql 설치 후
vi /etc/mysql/mysql.conf.d/mysqld.conf수정
[mysqld]
server-id = 2
log-bin = mysql-bin- mysql 재시작
sudo systemctl restart mysql- mysql 접속 후 master 서버 연결
change master to
master_host='15.165.158.196', # master서버 ip 주소
master_user='test3', #master mysql 계정
master_password='123', # master mysql 계정 비밀번호
master_log_file='mysql-bin.000005', # 바이너리 로그 파일
master_log_pos=1863; # position번호- 이 상태에서 ok가 난 후에 replica를 시작
start replica; # 시작 명령어 / stop replica; #중지- slave 상태 확인
show slave status;+----------------------+----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
> +----------------------+----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Connecting to source | 15.165.158.196 | test3 | 3306 | 60 | mysql-bin.000005 | 157 | ip-172-31-40-77-relay-bin.000001 | 4 | mysql-bin.000005 | Connecting | Yes | | | | | | | 0 | | 0 | 157 | 157 | None | | 0 | No | | | | | | NULL | No | 2003 | Error connecting to source 'test3@15.165.158.196:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Can't connect to MySQL server on '15.165.158.196:3306' (113) | 0 | | | 0 | | mysql.slave_master_info | 0 | NULL | Replica has read all relay log; waiting for more updates | 86400 | | 230828 13:14:33 | | | | | | 0 | | | | | 0 | |
+----------------------+----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+----------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
- slave의 상태를 보니 master서버와 연결이 제대로 되지 않았습니다.
- 연결 테스트를 진행했더니 연결이 오류가 났다고 합니다.
mysql -h15.165.158.196 -utest3 -p
- 블로그를 찾아보니 이는 방확벽에 문제가 있을 수도 있다고 하여 master서버에서 3306포트를 열어줘야 합니다.
Master서버에서 진행
sudo ufw allow out 3306/tcp
sudo ufw allow in 3306/tcp
- sudo ufw allow out 3306/tcp : 외부에서 내부로의 Mysql 트래픽을 허용(즉, 내부 mysql서버에서 외부로의 연결을 허용)
- allow 명령어는 특정 포트 및 프로토콜에 대한 트래픽을 허용하는 규칙 추가(mysql서버가 사용하는 포트번호인 3306/tcp를 허용)
- sudo ufw allow in 3306/tcp : 내부에서 외부로의 mysql트래픽을 허용, 즉 외부에서 내부 mysql서버로의 연결을 허용
그리고서 다시 진행해봤는데 안됬습니다...
생각해보니 저는 AWS 인스턴스를 사용중이라 인스턴스에서 방화벽 규칙을 설정이 필요했습니다.
- AWS 에서 방확벽 규칙을 설정해주고 다시 slave서버에서 연결 실행
mysql -h15.165.158.196 -utest3 -p
성공적으로 연결됬습니다!
3) DB연동 확인
- 이제 master 서버에서 database와 table을 만들고 data를 적재해보겠습니다.
create database testdb3;
create table t_user (id INT AUTO INCREMENT Primary key, name varchar(30));
insert into t_user values(1,'a');
-
slave서버에도 데이터가 잘 들어왔는지 확인합니다.
-
data가 잘 적재된걸 확인 할 수 있었습니다.
후기
- 처음에 어려웠던 점은 권한부여에서 계속 오류가 발생
- 알고보니 권한 부여를 진행한 후에 flush privileges; 를 입력안해서 반영이 안되었던것...
- 두 번째로 어려웠던 점은 slave에서 master연결 문제
- 이는 서버에서 방화벽을 해제하는 명령어를 찾았으나 되지 않아서 머가 문젱였지 했는데 클라우드이다 보니 클라우드 콘솔에서 방화벽 규칙 설정이 필요
잘보고 갑니다