첫번째는 클러스터링의 과반수보다 낮은 수의 컴퓨터가 다운되었거나 새로 합류할 때이고, 두번째는 과분수보다 높은 수의 컴퓨터가 다운되었을 대이다.
첫번째인 경우에는 IST, SST가 작동하여 클러스터링으로 서버를 유지할 수 있고, 두번째 경우는 쿼럼 유지를 실패한 경우이다.
Incremental State Transfer(IST) : 클러스터에서 이탈한 컴퓨터가 복귀할 때, 변경된 데이터만 복사받는 방식을 의미한다.
State Snapshot Transfer(SST) : 클러스터에 새로운 컴퓨터가 합류할 때, 전체 데이터를 스냅샷(Snap Shot)처럼 복사해서 보내주거나 받는 과정을 의미한다.
Quorum(쿼럼) : 몇대 이상의 컴퓨터가 살아있어야 클러스터가 안정성이 유지된다고 판단하는 기준이다. 기본 값으로 과반이 기준이다. 따라서 기본적으로 Clustering은 홀수개의 컴퓨터만 설정할 수 있다.
Clustering 구조
MariaDB Clustering 구축
1. 서버 중지 (Clustering 구축할 컴퓨터)
systemctl stop mariadb
2. Clustering 설정 추가
vi /etc/mysql/mariadb.conf.d/50-server.cnf
VIM 편집기로 설정창 들어가자.
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://" # 클러스터링 주소입력 / 처음 클러스터링하고 있어서 빈칸이다.
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="[클러스터링 이름]" # 3대의 컴퓨터를 묶었을 때 내가 지어줄 이름
wsrep_node_address="[현재 컴퓨터 IP 주소]" # 현재 컴퓨터의 IP 주소
Clustering 설정
위에서 설정한 설정 코드를 적용시키기 위해서 프로그램을 재시작 및 Clustering을 시작한다.
VRRP를 통해서 KeepAlived가 같은 공유기에 연결되어있는 모든 기기에게 가상 IP 주소를 가지고 있다고 통신을 제공한다
통신중
VRRP란?
VRRP(Virtual Router Redundancy Protocol)로, 가상 라우터 다중화 프로토콜임과 동시에 게이트워이 장애 복구를 위한 프로토콜이다.
게이트웨이 이중화 구성은 크게 2가지 목적으로 구현된다.
Load balancing : 똑같은 기능을 수행하는 장비를 여러 개 구성하여 네트워크 부하를 분산
Failover : 하나의 장비가 죽었을 때 다른 장비로 전환되어 서비스 단절 최소화
VRRP의 이중화 구성의 경우 Filaover 목적으로 Master / Slave 전환을 위해 사용한다.
Master와 Slave는 하나의 VIP(Vitural IP)로 묶여 있으며, 각각 RIP(Real IP)를 가지고 있다. (Master: Active / Slave: Stand-By 상태)
만약 Master 장비 장애 발생 시 Slave 장비가 VIP를 가져와 Master 역할 수행하게 된다.
Hot Site 구축 상황
MariaDB 설정 방법
Master Computer 설정 방법
리플리케이션을 구축하기 위해서는 DB에서 Master 및 Slave 설정을 진행해야 한다.
mysqldump -u root -p --all-databases > backup.sql
위 명령어를 이용해서 백업 파일을 만든다.
그 후 Slave 컴퓨터에게 이 백업 파일을 전송한 후, 데이터를 삽입시킨다.
scp [백업 파일 경로]/backup.sql [Linux ID]@[Slave IP]:[백업 파일 경로]/backup.sql // 데이터 전송 명령어
mariadb -u root -p < [파일 경로]/backup.sql
이 후에 master 컴퓨터에서 master 설정을 진행해야 한다.
vi /etc/mysql/mariadb.conf.d/50-server.cnf
이렇게 vim 편집기를 이용해서 '50-server.cnf'를 열고 109번 라인에 가면 다음 화면처럼 나온다.
109번째 라인
여기서 다음 코드를 복사해서 붙여넣으면 된다.
log-bin # Log 작성을 시작한다는 명령어
server_id=1 # 서버 Id를 나타내는 명령어
log-basename=master1 # 이 로그 파일 이름을 지정하는 명령어
binlog-format=mixed # 로그 파일이 2진수와 언어로 섞이도록 작성한다는 명령어
이렇게 한 후, 저장하면 된다.
설정을 적용시키기 위해서 재시작을 한번 하자.
systemctl restart mariadb
그 후에 mariadb 클라이언트 프로그램에 접속해서 실제로 master 설정이 적용되었는지 확인하자. (다음 명령어는 mariadb 클라이언트 프로그램에서 실행해야 한다.)
CREATE USER '[Slave에서 접속할 ID]'@'%' IDENTIFIED BY '[비밀번호]'; # MariaDB ID 생성
GRANT REPLICATION SLAVE ON *.* TO '[Slave에서 접속할 ID]'@'%'; # 전 권한 부여
FLUSH PRIVILEGES; # 권한 적용
CREATE USER '[WorkBench에서 접속할 ID]'@'%' IDENTIFIED BY '[비밀번호]'; # MariaDB ID 생성
GRANT REPLICATION SLAVE ON *.* TO '[WorkBench에서 접속할 ID]'@'%'; # 전 권한 부여
FLUSH PRIVILEGES; # 권한 적용
Slave Computer 설정 방법
Master 컴퓨터에서 보낸 데이터를 Slave 컴퓨터에 복사해야 한다.
mariadb -u root -p < [Master에서 보낸 파일 경로]
그 후에 Slave 컴퓨터에서 Slave 설정을 진행해야 한다.
vi /etc/mysql/mariadb.conf.d/50-server.cnf
이렇게 Vim 편집기를 통해서 내부 설정을 다음처럼 설정하자. 위와 같이 109번 줄이다.
[mariadb]
server_id=2
109번 줄
이제 MariaDB에 들어가서 master 컴퓨터를 연동해야 한다.
mariadb -u root -p // MariaDB Client 프로그램에 접속하자.
CHANGE MASTER TO
MASTER_HOST='[마스터 서버 IP]',
MASTER_USER='[마스터에서 생성한 ID]',
MASTER_PASSWORD='[마스터에서 생성한 PW]',
MASTER_PORT=3306,
MASTER_LOG_FILE='[마스터에서 show master status 했을 때 File 이름]',
MASTER_LOG_POS=[마스터에서 show master status 했을 때 position 번호],
MASTER_CONNECT_RETRY=10; # 마스터에게 정보를 받는 주기
만약 START SLAVE 명령어에서 에러 로그가 출력되거나 위 로그가 NO 혹은 다른 영어가 존재한다면 Slave 컴퓨터에서 Master 설정을 다시 해주면 된다.
STOP SLAVE;
RESET SLAVE;
KeepAlived 설정 방법 (with Ubuntu)
KeepAlived를 설치하자 (각 DB 컴퓨터에 설치해야 한다.)
apt install -y keepalived
그 후에 keepAlived 설정파일을 생성하고, 설정코드를 입력하자.
vi /etc/keepalived/keepalived.conf
global_defs {
router_id node01 # 이 설정 컴퓨터 이름이다, Master와 Slave에서 달라야 한다.
enable_script_security
script_user root
}
vrrp_script track_mariadb { # 공유기를 통해 내부 컴퓨터들에게 보내는 메시지를 설정한다.
script "systemctl is-active mariadb" # MariaDB에 연결하고 있기 때문에 이렇게 작성한다.
interval 5 # 5초에 한번씩 메시지를 날린다.
fall 4 # 4번 이상 실패하면 컴퓨터가 다운되었다고 가정한다.
rise 2 # 2번 이상 성공하면 컴퓨터가 작동한다고 가정한다.
}
vrrp_instance VRRP1 {
state MASTER # backup 용 설정은 state BACKUP이라고 작성하면 된다.
interface ens33
virtual_router_id 101
priority 200 # 우선순위를 말한다. 우선순위가 높으면 높을수록 Master 컴퓨터이다.
advert_int 1
virtual_ipaddress {
[설정한 가상 IP 주소]/24
}
track_script {
track_mariadb
}
}
Slave 컴퓨터에도 위와 같이 설치 및 설정을 진행하면 끝난다.
그 후에 Master나 Slave가 아닌 컴퓨터에서 MariaDB Client로 접속을 시도하자. (With Mysql Workbench)
workbench 사용해서 접속Master DB가 다운된 후, Slave DB에 연결되었다.
Haproxy는 고가용성, 로드 밸런서 및 프록시 기능을 제공하는 오픈소스 라이브러리입니다.
고가용성이란?
서버, 네트워크, 프로그램등의 정보 시스템이 상당히 오랜 기간 동안 지속적으로 정상 운영이 가능한 성질을 말합니다.
로드 밸런서란?
로드 밸런싱은 애플리케이션 가용성을 최적화하고 긍정적인 최종 사용자 경험을 제공하기 위해 여러 서버에 네트워크 트래픽을 효율적으로 분산하는 프로세스를 말합니다.
프록시란?
클라이언트가 자신을 통해서 다른 네트워크 서비스에 간접적으로 접속할 수 있게 해주는 컴퓨터 시스템이나 응용 프로그램을 의미합니다.
따라서 서버와 클라이언트 사이에서 존재하는 중계기입니다.
이런 Haproxy를 활용해서 리플리케이션 중 미러 사이트를 구축할 수 있다.
오늘 구축할 컴퓨터 형태
MariaDB 설정 방법
Master Computer 설정 방법
리플리케이션을 구축하기 위해서는 DB에서 Master 및 Slave 설정을 진행해야 한다.
mysqldump -u root -p --all-databases > backup.sql
위 명령어를 이용해서 백업 파일을 만든다.
그 후 Slave 컴퓨터에게 이 백업 파일을 전송한 후, 데이터를 삽입시킨다.
scp [백업 파일 경로]/backup.sql [Linux ID]@[Slave IP]:[백업 파일 경로]/backup.sql // 데이터 전송 명령어
mariadb -u root -p < [파일 경로]/backup.sql
이 후에 master 컴퓨터에서 master 설정을 진행해야 한다.
vi /etc/mysql/mariadb.conf.d/50-server.cnf
이렇게 vim 편집기를 이용해서 '50-server.cnf'를 열고 109번 라인에 가면 다음 화면처럼 나온다.
109번째 라인
여기서 다음 코드를 복사해서 붙여넣으면 된다.
log-bin # Log 작성을 시작한다는 명령어
server_id=1 # 서버 Id를 나타내는 명령어
log-basename=master1 # 이 로그 파일 이름을 지정하는 명령어
binlog-format=mixed # 로그 파일이 2진수와 언어로 섞이도록 작성한다는 명령어
이렇게 한 후, 저장하면 된다.
설정을 적용시키기 위해서 재시작을 한번 하자.
systemctl restart mariadb
그 후에 mariadb 클라이언트 프로그램에 접속해서 실제로 master 설정이 적용되었는지 확인하자. (다음 명령어는 mariadb 클라이언트 프로그램에서 실행해야 한다.)
CREATE USER '[Slave에서 접속할 ID]'@'%' IDENTIFIED BY '[비밀번호]'; # MariaDB ID 생성
GRANT REPLICATION SLAVE ON *.* TO '[Slave에서 접속할 ID]'@'%'; # 전 권한 부여
FLUSH PRIVILEGES; # 권한 적용
CREATE USER '[WorkBench에서 접속할 ID]'@'%' IDENTIFIED BY '[비밀번호]'; # MariaDB ID 생성
GRANT REPLICATION SLAVE ON *.* TO '[WorkBench에서 접속할 ID]'@'%'; # 전 권한 부여
FLUSH PRIVILEGES; # 권한 적용
Slave Computer 설정 방법
Master 컴퓨터에서 보낸 데이터를 Slave 컴퓨터에 복사해야 한다.
mariadb -u root -p < [Master에서 보낸 파일 경로]
그 후에 Slave 컴퓨터에서 Slave 설정을 진행해야 한다.
vi /etc/mysql/mariadb.conf.d/50-server.cnf
이렇게 Vim 편집기를 통해서 내부 설정을 다음처럼 설정하자. 위와 같이 109번 줄이다.
[mariadb]
server_id=2
109번 줄
이제 MariaDB에 들어가서 master 컴퓨터를 연동해야 한다.
mariadb -u root -p // MariaDB Client 프로그램에 접속하자.
CHANGE MASTER TO
MASTER_HOST='[마스터 서버 IP]',
MASTER_USER='[마스터에서 생성한 ID]',
MASTER_PASSWORD='[마스터에서 생성한 PW]',
MASTER_PORT=3306,
MASTER_LOG_FILE='[마스터에서 show master status 했을 때 File 이름]',
MASTER_LOG_POS=[마스터에서 show master status 했을 때 position 번호],
MASTER_CONNECT_RETRY=10; # 마스터에게 정보를 받는 주기
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.17.2/mysqld_exporter-0.17.2.linux-amd64.tar.gz
tar zxvf mysqld_exporter-0.17.2.linux-amd64.tar.gz
cd mysqld_exporter-0.17.2.linux-amd64
vi .my.cnf
[client]
user=exporter
password=qwer1234
host=127.0.0.1
후에 Status→ Targets 화면에 들어가면 다음과 같은 화면이 나온다.
Targets 화면
여기서 하나의 데이터를 클릭한다면 다음처럼 계속 데이터를 수집하고 있다는 것을 알 수 있다.
데이터 수집중...
Targets 추가하기
vi /etc/prometheus/prometheus.yml
위 명령어를 통해 접근하면 다음처럼 나온다.
여기서 - job_name이 Prometheus의 큰 선택 메뉴이다.
또한 targets에 내가 데이터를 수집하고 싶은 컴퓨터 IP주소를 입력하고, exporter의 포트 번호를 입력하면 된다.
node-exporter는 기본 포트가 9100번이다.
또한 다른 job_name을 하나의 데이터로 보기 위해서는 밑에 Lables를 붙여서 동시에 데이터를 처리할 수도 있다.
위 설정을 다 한 후에 다음 명령어로 재시작을 하면 적용된다.
systemctl restart prometheus
Grafana
Grafana란?
Prometheus같은 프로그램이 수집한 데이터를 개발자가 보기 좋게 변환시켜주는 프로그램을 의미한다.
DML(Data Manipulation Laguage) : 실제 데이터를 조회하거나 삽입, 삭제, 수정하는 명령어를 말한다.(Select, Insert, Update, Delete)
DCL(Data Control Laguage) : 데이터베이스에 접근하고 객체들을 사용하도록 권한을 제공하고 회수하는 명령어를 말한다.(Grant, Revoke)
쿼리(Query)
Create
CREATE DATABASE [데이터베이스 이름]; #데이터베이스 생성
CREATE TABLE [테이블 이름] (
[속성 이름] [타입]
[속성 이름] [타입]
); #테이블 생성
위처럼 작성하면 된다. 예시는 다음과 같다.
use user; # 데이터베이스 선택
create table user(
id int primary key auto_increment, # 양의 정수, 기본키 지정, 자동 증가 옵션
email varchar(50) not null unique key, # 50자 글자, null값 불가
name varchar(20) default '기본 이름', # 20자 글자, null이라면 '기본 이름'을 자동으로 추가함
password varchar(200) not null # 200자 글자, null값 불가
);
Table이 만들어졌고, 컬럼과 제약조건이 생성되었다.
Alter
ALTER TABLE [테이블 이름] [명령어] [해당 컬럼] [추가 명령어];
여기서 명령어는 다음과 같다.
ADD : 컬럼 추가
DROP : 컬럼 삭제
RENAME : 컬럼 이름 변경
MODIFY : 컬럼 제약 조건 수정
alter table user add profile_image varchar(100) default 'default.png'; #profile_image라는 컬럼 추가
alter add 후 새로운 컬럼 추가
alter table user drop profile_image; #profile_image 컬럼 삭제
alter drop 후 데이터 컬럼 삭제
alter table user rename column name to nickname; # name이라는 컬럼 이름을 nickname으로 변경
alter rename 후 name 컬럼이 nickname 컬럼으로 변경alter modify 변경 전
alter table user modify nickname varchar(50); # nickname이라는 컬럼 20자 제약 조건을 50자로 변경
alter modify 후 제약 조건 변경 완료
Drop
drop table user; # user라는 테이블을 삭제
Drop table 후 테이블이 삭제됨
drop database dbex; # 데이터베이스 삭제
Drop database 후 데이터베이스 자체가 삭제됨
Select-From
select문은 데이터베이스에서 데이터를 조회할 때, 사용한다.
대부분의 성능이슈는 데이터를 조회할 때 발생한다.
따라서 select 쿼리문이 성능 최적화가 가장 많이 요구된다.
select 작성 방법은 다음과 같다.
select [조회할 컬럼1], [조회할 컬럼2], ... [조회할 마지막 컬럼]
from [조회할 테이블]
where [조회 조건];
select *
from [조회할 테이블]
where [조회 조건];
이때, *은 모든 컬럼을 의미한다.
또한 where은 조회 조건을 의미하지만, 아예 작성을 안하면 테이블에 존재하는 모든 튜플을 조회한다.
select * from payment; # *은 모든것을 의미한다.
select *로 모든 컬럼 조회select 컬럼 및 where 조건넣은 조회
Where
where은 조회 조건을 달아서 특정 데이터만 읽어오고 싶을 때 사용한다.
이때, 여러개의 조건을 동시에 달기 위해서 AND, LIKE, IN같은 문법이 추가적으로 사용한다.
And
여러개의 조건을 동시에 사용할 때 사용한다.
select [조회할 컬럼]
from [테이블 이름]
where [튜플 조건] and [튜플 조건];
where의 and 조건으로 조회
Like
문자열 중 일부만 알고 있을 때 사용한다.
select [조회할 컬럼]
from [테이블 이름]
where [문자열 컬럼] like [비슷한 문자열%]; # 앞 문장은 완전히 동일하고 뒷 문장은 달라도 된다.
# %는 *를 의미한다.
select [조회할 컬럼]
from [테이블 이름]
where [문자열 컬럼] like [%비슷한 문자열]; # 뒷 문장은 완전히 동일하고 앞 문장은 달라도 된다.
select [조회할 컬럼]
from [테이블 이름]
where [문자열 컬럼] like [%비슷한 문자열%]; # 앞 문장, 뒷 문장은 달라도 되고, 중간 문장은 같다.
LIKE로 문자열 조회
In
문법 안에 배열을 통해서 조회하고 싶을 때 사용한다.
select [조회할 컬럼]
from [테이블 이름]
where [조회할 컬럼] in [튜플 조건들]
where의 In 문법으로 배열 조건으로 조회
Join
2개 이상의 테이블을 동시에 조회할 때 사용한다.
Inner Join
2개의 테이블 중 조건에 맞는 교집합만 데이터로 인식한다.(만약 조건에 맞지 않는다면 데이터 조회 자체가 안된다.)
select [조회할 컬럼]
from [테이블 이름]
where [튜플 조건]
inner join [테이블 이름] on [join 조건];
Inner Join 조회
left Join
2개의 테이블 중 기준 테이블과
select [조회할 컬럼]
from [테이블 이름] # 기준 테이블이 들어가야 한다.
where [튜플 조건]
left join [테이블 이름] on [join 조건];
Left Join 조회
as
테이블 이름이 너무 길 때, 간단한 별명으로 설정하고 코드를 작성할 때 사용한다.
select [조회할 컬럼]
from [테이블 이름] as [별명]
where [튜플 조건];
as로 SQL 읽기 편하게 작성
Order by
데이터를 정렬시킬 때 사용한다. (오름차순 : asc, 내림차순 : desc)
select [조회할 컬럼]
from [테이블 이름]
where [튜플 조건]
order by [기준 컬럼] [내림차순, 오름차순];
Order by로 ID순으로 정렬
is null
데이터가 null 값인지 확인하기 위해서 사용한다.
select [조회할 컬럼]
from [테이블 이름]
where [튜플 조건] is null;
Null값 조회
group by
여러개의 데이터 중 하나의 데이터를 기준으로 잡을 때 사용한다.(대표적으로 나라별 사람을 세고 싶을 때, 나라를 기준으로 데이터를 정렬해야 한다. 이럴때 사용한다.)
select [조회할 컬럼]
from [테이블 이름]
where [튜플 조건]
inner join [테이블 이름] on [join 조건]
group by [기준 컬럼];
기준 튜플로 데이터 조회
having
Group By에서 조건을 추가적으로 더 걸고 싶을 때 사용한다.
select [조회할 컬럼]
from [테이블 이름]
where [튜플 조건]
inner join [테이블 이름] on [join 조건]
group by [기준 컬럼]
having [튜플 조건];
기준 튜플의 조건 추가 검색
Limit
조회한 데이터 중 몇개만 보고 싶을 때 사용한다.
select [조회할 컬럼]
from [테이블 이름]
where [튜플 조건]
limit [개수]
select [조회할 컬럼]
from [테이블 이름]
where [튜플 조건]
limit [offset], [개수]
이때, offset은 앞에서 몇개를 제외한다는 것을 의미한다. (예를들어 10, 10이면 위에서 11번째부터 20번째 데이터만 읽어온다.)
데이터의 최대값 지정
Distinct
중복되는 데이터를 제거해서 보고 싶을 때 사용한다.
select distinct [조회할 컬럼 이름]
from [테이블 이름]
where [조회할 튜플 조건]
select [조회할 컬럼 이름]
from [테이블 이름]
where ([select [조회할 컬럼 이름]
from [테이블 이름]
where [조회할 튜플 조건]
);
서브 쿼리 이용해서 데이터 조회
Update
데이터를 수정할 때 사용한다.
update [테이블 이름] set [변경할 컬럼] = [변경 값] where [변경할 튜플 조건]
Update 전 상태Update 후 상태
Insert
데이터를 새롭게 추가할 때 사용한다.
INSERT INTO [테이블 이름] [(테이블 조건1, 테이블 조건 2, ..., 테이블 조건 n)]
VALUE [데이터 1, 데이터 2, ..., 데이터 n)];
INSERT INTO [테이블 이름] [(테이블 조건1, 테이블 조건 2, ..., 테이블 조건 n)]
VALUES
[데이터 1, 데이터 2, ..., 데이터 n)],
[데이터 1, 데이터 2, ..., 데이터 n)],
[데이터 1, 데이터 2, ..., 데이터 n)],
[데이터 1, 데이터 2, ..., 데이터 n)],
[데이터 1, 데이터 2, ..., 데이터 n)];
