1) MariaDB install & 한글 깨짐 해결
MariaDB install
yum -y install wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
chmod +x mariadb_repo_setup
./mariadb_repo_setup
yum -y install MariaDB-server
systemctl start mariadb && systemctl enable mariadb
한글 설정
vi /etc/my.cnf
[client]
default-character-set=utf8
[mysqld]
character-set-client-handshake=FALSE
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
systemctl restart mariadb
mysql_secure_installation
Enter current password for root (enter for none): enter
Set root password? [Y/n] y
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] n
Remove test database and access to it? [Y/n] n
Reload privilege tables now? [Y/n] y
2) DB Modeling
create database repl_db; #DB 생성
create table hosts(id int not null auto_increment, hostName varchar(20) not null, hostIp varchar(20) not null, primary key(id)); #hosts table 생성
create table vms(id int not null auto_increment, image varchar(30) not null, instanceName varchar(30) not null, instanceIp varchar(30), cpu int not null, ram int not null, hostId int not null, primary key(id), foreign key(hostId) references hosts(id) on delete cascade on update cascade); #vms 테이블 생성
3) DB Replication
Replication은 복제를 뜻하며 2대 이상의 DBMS를 나눠서 데이터를 저장하는 방식이다. 사용하기 위한 최소 구성은 Master / Slave 이다.
목적: 데이터 백업, DBMS 부하 분산
구성: Master(storage: 172.16.1.100) - slave(DB: 172.16.1.101)
역할
구현
Master Server
mysql>
create user master_user1@'%' identified by 'test123'; #master 계정 생성
grant all privileges on repl_db * to master_user1@'%' identified by 'test123'; #권한 부여
grant replication slave on *.* to 'repl_user'@'%' identified by 'test123'; #replication 계정 생성
flush privileges;
vi /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
systemctl restart mariadb (mariadb 재시작)
show master status(binarylog 파일 확인)
Slave Server
replication할 db 및 table 생성
create database repl_db; #DB 생성
create table hosts(id int not null auto_increment, hostName varchar(20) not null, hostIp varchar(20) not null, primary key(id)); #hosts table 생성
create table vms(id int not null auto_increment, image varchar(30) not null, instanceName varchar(30) not null, instanceIp varchar(30), cpu int not null, ram int not null, hostId int not null, primary key(id), foreign key(hostId) references hosts(id) on delete cascade on update cascade); #vms 테이블 생성
mysql> create user user1@'%' identified by 'test123';
mysql> grant all privileges on repl_db.* to user1@'%' identified by 'test123';
flush privileges;
vi /etc/my.cnf
[mysqld]
server-id=2
replicate-do-db='repl_db'
mysql> change master to
master_host='172.16.1.100',#master 서버 IP
master_user='repl_user',#레플리케이션 ID
master_password='test123',#레플리케이션 PW
master_log_file='mysql-bin.000002',#MASTER STATUS 로그파일명
master_log_pos=4354;#MASTER STATUS Position 값
systemctl restart mariadb
mysql> show slave status\G;
Master-Slave replication 완료
동기화 확인
<그림3. storage server>
<그림4. db server>
주의할 점