밤둘레

1. ON DUPLICATE KEY UPDATE

데이터 삽입 시, PRIMERY KEY나 UNIQUE KEY가 중복되었을 경우 지정한 데이터만 UPDATE하는 명령어를 의미한다.
(중복된 키가 없을 경우 INSERT 로직을 수행한다.) 

1) member 테이블 생성

CREATE TABLE member (
	id INT AUTO_INCREMENT primary KEY,
	NAME VARCHAR(50) UNIQUE KEY,
	price INT NOT NULL DEFAULT 0,
	cnt INT NOT NULL DEFAULT 0
);

2) 데이터 삽입

INSERT INTO member (NAME, price, cnt) VALUES ('kim', 1000, 0) 
ON DUPLICATE KEY UPDATE 
  price = price * 2, 
  cnt = cnt + 1;

한번 더 데이터를 삽입 할 경우

새로운 행이 삽입 되지 않고, price와 cnt가 변경된 것을 볼 수 있다.

즉, 데이터 삽입 시, 중복키 제약조건에 위배 되면 ON DUPLICATE KEY UPDATE 아래에 지정한 필드가 수정된다.

위 테이블에 경우 name 값이 중복 되므로 price와 cnt 필드가 지정한 값으로 수정되었다.

2. INSERT IGNORE

중복키 제약조건에 위배되면 Insert를 무시한다.

INSERT IGNORE INTO member (NAME, price, cnt) VALUES ('kim', 1000, 0);

3. REPLACE INTO

중복키 제약조건에 위배되면 해당 레코드를 삭제하고 다시 삽입한다.

REPLACE INTO member (NAME, price, cnt) VALUES ('kim', 1000, 0);

레코드가 삭제되고 다시 삽입되기 때문에 AUTO_INCREMENT로 ID(PK)값을 지정했을 경우 ID값이 변하게 된다.

1. GROUP_CONCAT 명령어

SELECT의 결과로 나온 여러 행의 특정 컬럼을 한줄로 출력할 때 사용한다.
(NULL 값은 제외된다.)

2. 사용 방법

GROUP_CONCAT([DISTINCT] [expr, column] [ORDER BY column DESC] [SEPARATOR '구분자'])

3. 사용 예제

 1) member 테이블

CREATE TABLE `member` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR(30) NULL DEFAULT NULL
	`age` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)

 2) member 데이터

INSERT INTO `member` (`NAME`, `age`) VALUES ('kim', 30);
INSERT INTO `member` (`NAME`, `age`) VALUES ('hong', 30);
INSERT INTO `member` (`NAME`, `age`) VALUES ('go', 31);
INSERT INTO `member` (`NAME`, `age`) VALUES ('lee', 33);
INSERT INTO `member` (`NAME`, `age`) VALUES ('son', 35);
INSERT INTO `member` (`NAME`, `age`) VALUES ('chang', 32);
INSERT INTO `member` (`NAME`, `age`) VALUES ('chei', 33);
INSERT INTO `member` (`NAME`, `age`) VALUES ('ho', 31);
INSERT INTO `member` (`NAME`, `age`) VALUES ('gu', 36);
INSERT INTO `member` (`NAME`, `age`) VALUES ('sang', 38);
INSERT INTO `member` (`NAME`, `age`) VALUES ('jang', 39);

 3) 쿼리

SELECT GROUP_CONCAT(name ORDER BY age DESC SEPARATOR ',') AS 'members'
FROM member

 4) 결과

jang,sang,gu,son,chei,lee,chang,ho,go,hong,kim

1. Mysql Replication이란?

DB의 복제를 의미하며, Master와 Slave로 구성됩니다.

• Master 역할 : 데이터 등록/수정/삭제 시 Binarylog를 생성하여 Slave 서버에 전달합니다. 주로 등록,수정,삭제 용으로 사용합니다.
• Slave 역할 : Master에게 전달받은 Binarylog를 읽어 DB에 반영합니다. 주로 읽기 용으로 사용합니다.

2. 사용 목적

• 실시간 데이터 백업
• DB 서버 부하 분산

3. 주의 사항

• 호환성을 위해 Master와 Slave의 Mysql 버전이 동일하게 맞추는 것이 좋습니다.
• 버전이 다른 경우 Slave DB 서버가 상위 버전이어야 합니다.
• Master DB 서버, Slave DB 서버 순으로 가동시켜야 합니다.

4. Linux 및 Mysql 기본 정보

CentOS7 환경 또는 Mysql 설치가 되지 않았다면 아래 링크를 따라 진행해주세요.

https://bamdule.tistory.com/20?category=365232

https://bamdule.tistory.com/22?category=365232

• 공통 정보
  
  • CentOS7
  • Mysql 5.7.29
• Master
  • IP : 192.168.152.133
• Slave 
  • IP : 192.168.152.132

mysql DB 이중화 작업 진행 시 편의를 위해 root 계정을 이용하겠습니다.

5. Master

1) mysql 접속

• mysql -u root -p

2) 테스트 DB 생성

• create database testdb default character set utf8;

3) Slave에서 접속할 계정 생성

• grant replication slave on *.* to 'test_slave'@'%' identified by '!@#QWEasd123';

4) 테스트 Table 생성

create table t_user (
  id INT AUTO_INCREMENT Primary key,
  name VARCHAR(30)
);

5) Master 서버 실행 및 설정

• vi /etc/my.cnf

...
[mysqld]
...
log-bin=mysql-bin
server-id=1
...

server-id 와 binarylog 파일 명을 입력해줍니다.

• mysql 재시작
• mysql 접속
• show master status;

• File명과 Position 번호를 메모장에 적어 두세요.
• 주의) DB 재시작 시 File명과 Position 번호가 변경될 수 있습니다.

6. Slave

1) /etc/my.cnf 수정

• vi /etc/my.cnf

...
[mysqld]
...
log-bin=mysql-bin
server-id=2
...

• mysql 재시작
• mysql 접속
• DB 생성
  • create database testdb default character set utf8;
• Table 생성

create table t_user (
  id INT AUTO_INCREMENT Primary key,
  name VARCHAR(30)
);

• master 연동 설정

change master to
  master_host='192.168.152.132',
  master_user='test_slave',
  master_password='!@#QWEasd123',
  master_log_file='mysql-bin.000001',
  master_log_pos=154;

master_host : master host
master_user : master mysql 계정 (만들어둔 slave용 계정을 이용하자)
master_password : master의 slave용 계정 비밀번호 
master_log_file : 바이너리로그 파일
master_log_pos :  master position

• Slave 실행
  • start slave;
• Slave 상태 확인
  • show slave status 

master와 slave의 연결 정보 및 상태정보가 출력됩니다.

하지만 여러가지 이유로 연결이 안되는 경우가 있습니다.

error connecting to master 'test_slave@192.168.152.133:3306' - retry-time: 60  retries: 5

여러 정보 중 위 메시지가 있다면, 60초마다 접속을 시도했고, 5번의 시도에도 성공하지 못했다는 의미입니다.

여러 이유 중 하나는 방화벽 문제입니다. Master 서버에서 3306포트가 열려있지 않다면 Slave 서버에서 접속 할 수 없습니다. 

7. Master서버와 Slave서버 간 연결 테스트

mysql -h마스터서버IP -utest_slave -p

master 서버로 mysql 접속이 되는지 확인해 봅니다.

위와 같이 출력된다면 3306포트가 막혀있을 확률이 높습니다.

Master 서버에서 3306포트를 열어 줍니다.

systemctl status firewalld

방화벽이 실행되고 있는지 확인 합니다.

firewall-cmd --zone=public --add-port=3306/tcp

3306 포트를 열어줍니다. success 라는 문구가 출력되었다면 성공한 것입니다.

mysql -h마스터서버IP -utest_slave -p

Master 서버에 접속된 것을 확인하실 수 있습니다.

위와 같이 했음에도 연동이 되지 않는 다면, Master와 Slave간 설정이 잘되었는지 한번만 더 확인해주시기 바랍니다.

8. DB 이중화 테스트

• Master 서버 Mysql 접속
• use testdb;
• t_user 테이블에 테스트 데이터 삽입

• Slave 서버 Mysql 접속
• use testdb;
• t_user 테이블에 Master DB에서 입력한 데이터가 삽입되었는지 확인

이것으로 DB 이중화 포스팅을 마치겠습니다.

1. MySql 5.7 설치

yum -y install http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum -y install mysql-community-server

위 두개의 파일을 다운로드 합니다.

mysql을 실행하면 임시 비밀번호가 생성되고 mysqld.log 파일 안에서 임시 비밀번호를 확인 할 수 있습니다.

systemctl start mysqld

vi /var/log/mysqld.log

mysql -u root -p

mysql을 실행하고, root계정으로 로그인합니다.

로그인이 완료되면, 비밀번호를 재설정 합니다.

ALTER USER 'root'@'localhost' IDENTIFIED BY '!@#QWEasd123';
FLUSH PRIVILEGES;

비밀번호 보안수준을 높게 해야하기 때문에, 특수문자, 영대소문자, 숫자를 모두 입력해야합니다.

status 명령어를 입력하면 설치된 Mysql 정보를 볼 수 있습니다.

2. CharacterSet UTF8 설정하기

mysql의 기본 charset은 latin1입니다. 이것을 utf8로 바꾸어 보겠습니다.

mysql 설정은 my.cnf 파일에서 변경할 수 있습니다. 

vi /etc/my.cnf 

위 명령어를 입력해 my.cnf 파일을 열고 다음과 같이 수정해줍니다.

[client]
default-character-set = utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

character-set-server=utf8
collation-server=utf8_general_ci
init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8

character-set-client-handshake = FALSE
skip-character-set-client-handshake

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

설정이 완료되었다면, Mysql을 재시작하고 다시 접속합니다.

systemctl restart mysqld

mysql -u root -p

status

status 명령어로 확인하면 모든 charset이 utf8로 변경된 것을 볼 수 있습니다.

이 것으로 포스팅을 마치겠습니다.

참조 - https://www.opentutorials.org/module/1701/10229