Developer SE의 만남

mysqldump 로 DB 및 프로시저 백업시 백업 파일에는 주석이 정상적으로 입력되어 있는데 복원시 주석이 제거된다.

복원시 다음과 같이 옵션을 주고 복원하게 되면 주석도 정상적으로 복원 된다. 

mysql -uroot -p -c < dumpfile.sql

OR

mysql -uroot -p --comments < dumpfile.sql

MySQL 의 꽤 괜찮은 Cluster인 Galera Cluster의 테스트 보고서 입니다.

장비사양: MacBook Pro (Retina, 15-inch, Mid 2014), 프로세스:2.5 GHz Intel Core i7, 메모리: 16GB 1600 MHz DDR3

Virtual Box 로 Linux 2대를 구성하여 MySQL Galera Cluster 를 구성하였습니다.

편의상 Master(최초 Cluster 의 Group을 생성하는 머신, service mysql start --wsrep-new-cluster 로 구동) 가 되는 서버를 galera1 이라 하겠습니다.

다른 하나는 galera2 라고 하고요.

테스트 상황:

1. Galera1 / Galera2 가 동작중인 상황에서 Galera2에 장애 발생  >>  Galera1 에서 Insert 발생  >> Galera2 에서 service mysql start 실행

위 그림과 같이 오류 발생 하지만 다시한번  service mysql start 를 하게되면 정상적으로 접속이 가능하다.

만약 이렇게 해도 해결되지 않으면  cd /var/lib/mysql 로 MySQL 데이터 폴더로 가서 rm -f galera.cache ib_* 하여 총 3개의 파일을 삭제 해준 후

Linux를 Reboot 하고 다시한번 mysql 서비스를 실행해 준다.

2. Galera1 / Galera2 가 동작중인 상황에서 Galera1 에 장애 발생  >>  Galera2 에서 Insert 발생  >> Galera1 을 실행

  Galera1 을 재구동 할때는 Galera1이 Master 였다고 하여   service mysql start --wsrep-new-cluster 로 실행하면 않된다.

  만약 위와 같이 실행하게 되면 Galera1이 새로운 Cluster Group을 생성하게 된다.

  이런 경우 Galera1 에서 service mysql start 로 서비스를 실행하게 되면 Galera2 의 Cluster Group에 들어가게 되고 데이터 동기화도 수행 된다.

내 노트북이 좋아서 인지는 모르겠지만 예전에 사용해본 NDB Cluster에 비해서 성능, 장애처리 부분에서 Galera Cluster가 월등히 좋은것 같다.

다만 한가지 약간의 문제점이라면 ... Auto Increment Column 이 있는 테이블에서 Insert 작업이 이루어질 경우

Galera1 은 1,3,5,7,9 로 Insert가 되고 Galera2에서는 2,4,6,8,10 순으로 증가가 된다. 

예로 Galera1 에서 3건을 insert 하면 1,3,5 가 되고 후에 Galera2 에서 2건을 insert 하면 6,8이 된다.

해보진 않았지만 Node가 3개라면.. 각각의 Node 는 3씩 증가시키는 것으로 알고 있다.

이것은 Auto Increment 의 값을 동기화 시키느라 발생하는 Lock 및 성능저하 문제를 해결하기 위한 Galera만의 방법이고

내 기억으로 NDB Cluster 에서는 Auto Increment Column 에서 값이 비는 경우는 없었던 것으로 알고있다.

DB와 연관되는 프로젝트를 성공적으로 진행하려면 Test Data의 양과 질이 절대적으로 좋아야 한다.

그래야만 개발 단계에서 많은 문제를 찾아낼 수 있고 수정할 수 있게 된다.

하지만 현실은 가가가,나나나,다다다,~~~  형식의 그것도 대략 100건 안쪽의 Test Data 만 넣어놓은 상태에서 개발을 진행하는 경우가 흔하다.

그러다 보니 실제 서비스 하는 환경에서는 Data 가 쌓이면서 Query 시간이 길어지고 Data가 오염되면서 각종 문제가 발생하게 된다.

그것을 줄여 보고자 이 글을 쓴다.

아래의 방법은 가장 일반적인 방법이며 응용을 통하여 더 다양하고 많은 양의 Test Data를 만들어 낼수가 있다.

1. 복제의 대상이 되는 기준 집합을 선택 – MySQL 버전에 따라서 아래 3개중 1개의 집합을 사용할 수 있음

 SELECT count(*) FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES;       #기준집합 277 Row , MySQL 5.6 이하

 SELECT count(*) FROM performance_schema.global_variables;                    #기준집합:486 Row , MySQL 5.7 이상

2. 단순 집합 복제 예 – 1 ~  40000 까지의  1개 Column 을 갖는 집합 생성

SELECT @rownum := @rownum +1 AS NUM

FROM (select 1 from performance_schema.global_variables limit 200) as A,

             (select 1 from performance_schema.global_variables limit 200) as B,

             (select @rownum := 0) as tmp

LIMIT 100000;                                                                                              #4만 Row로 복제 (200 * 200)

<수행시간: 40000 row(s) returned  0.0011 sec / 0.0060 sec(Duration / Fetch Time)>

◈ MariaDB 의 경우 performance_schema 환경 변수가 off 되어 있으면 performance_schema.global_variables 테이블에 접근할 수 없습니다.

   INFORMATION_SCHEMA.GLOBAL_VARIABLES 테이블을 사용해 주시길 바랍니다.

결과 집합: 

3. 4만개의 겹치지 않는 12자리 코드 집합을 생성하는 쿼리

SELECT CONCAT(

        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@rownum)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1)

       ,SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1)) AS RAND_CD

       ,@rownum := @rownum +1 AS NUM

FROM (SELECT 1 FROM performance_schema.global_variables LIMIT 200) as A

            ,(SELECT 1 FROM performance_schema.global_variables LIMIT 200) as B

WHERE (@rownum:=0)=0

LIMIT 100000; 

<수행시간: 40000 row(s) returned  0.0035 sec / 0.131 sec (Duration / Fetch Time)>

결과 집합: 

Insert Query에 위의 결과 집합만 적용해 주면 몇십만건의 Data도  바로 생성해 넣을 수 있다.

PHP 로 개발을 하면서 DB관련 작업을 컴포넌트(cakephp3)를 사용해서 하게되면

테이블 생성시 자동으로 모든 테이블에 Autoincrement 인 컬럼을 PK로 테이블을 생성하게 된다.

그럴경우 구조가 아주 엉망이다...

그럴경우에 유용하게 사용할 수 있는 쿼리 이다.

CREATE TABLE `t_test` ( 

    `idx` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT, 

    `name` varchar(40) NOT NULL COMMENT '코멘트', 

PRIMARY KEY (`idx`)) ENGINE=InnoDB DEFAULT CHARSET=euckr;

쿼리 구조 : REPLACE INTO DB.t_test VALUES([id],[값])

- AutoIncrement 의 자동 증가와는 별도로 값을 입력 수정 할수 있다.  순차적으로 증가하던 ID 값을 몇단계 건너뛰고 지정할 수 있다.

아래는 퀴리 수행시 Table의 변경 사항을 보여준다.

# 계정 정보 확인

--MySQL 5.6 이하

SELECT user,host,password FROM mysql.user;

-- MySQL 5.7 이상

SELECT user,host,authentication_string FROM mysql.user;

* password 라는 Column 이 authentication_string 으로 명칭이 변경 되었다.

#패스워드 변경

-- MySQL 5.6 이하

UPDATE mysql.user SET password = PASSWORD('패스워드') WHERE user = '계정명' AND host = 'host명';

FLUSH PRIVILEGES;

-- MySQL 5.7 이상

SET PASSWORD FOR '계정명'@'host명' = PASSWORD('패스워드');

FLUSH PRIVILEGES;

#계정 등록 및 권한 설정 (모든 버전 동일)

GRANT ALL PRIVILEGES ON *.* TO '계정명'@'host명' IDENTIFIED BY '패스워드'; 

FLUSH PRIVILEGES;

Node.js에서 사용하던 가중치 적용 함수를 SQL Query로 구현 보았습니다.

결론은 로직으로 구현하는것 보다는 Query로 구현하는것이 간단하다 입니다.

------ 테이블 ------ 

****************** 가중치 함수 - Node.js 구현 ******************

rows1[] = SELECT * FROM t_item 집합

var rownum = weightcheck(rows1[0]); //가중치로 선택된 Row의 번호 Get

var selrow = rows1[0][rownum]; //가중치로 선택된 Row Value

/** -----------------------------------------------------------------

 * 가중치 계산, 배열의 크기를 작게하기 위하여 weight 은 1~9 까지만 사용

 ----------------------------------------------------------------- */

function weightcheck(rows){

var point = 0; //가중치

var beforePoint = 0; //이전 가중치

var totalweightn = totalweigh(rows,'weight');             //가중치 Column

var ran = Math.floor((Math.random() * totalweightn)+1);   //1~가중치총합 사이의 랜덤한수

var i = 0;

//직전 가중치 + 현재 row의 가중치는 Band

for(i=0;i < rows.length;i++){

point = beforePoint + rows[i].adweight; 

if(ran > beforePoint && ran <= point ){

break;

};

beforePoint = point; //현재 위치를 이전 위치로 저장 합니다.

};

return i;

};

/** -----------------------------------------------------------------

 * 총 가중치 계산 (배열,가중치값 이름)

 ------------------------------------------------------------------- */

function totalweigh(list,weightnm){

var total=0; //총 가중치

for(var i = 0;i < list.length;i++){

total = total + list[i].adweight;

}

return total;

};

****************** 가중치 계산 - Query 로 구현 ******************

SELECT C.*

FROM (

SELECT A.* FROM t_item as A INNER JOIN t_weight as B

ON A.weight = B.weight                                       //가중치 숫자만큼 Row 복제

) as C

ORDER BY rand() LIMIT 1;                                         //복제된 Row 중 랜덤하게 하나의 Row를 반환

Node.js 에서 코드로 처리하는 것 보다 RDB에서 처리하는것이 더 깔끔합니다.

안드로이드용 바로가기APP https://goo.gl/LjcQgi -- 바로가기G