Developer SE의 만남

얼마전 면접을 보다가, 아래 그림과 같은 데이터 구조에서의 탐색 방법에 대한 DB 설계 및 쿼리 작성을 하시오 라는 질문에

최적의 답변을 하지 못해. 집에 와서 테스트 하였다.

질문의 핵심은 특정 node 를 선택했을때, 최상위 부모까지 올라가는 흐름을 한줄로 표현하는 것이었다.

ex) 그림20 > 그림17 > 그림13 > 그림10 > 그림9 > 그림3 > 그림1

데이터의 구조는 아래와 같다.

테이블로 옮기면,

일반적인 재귀호출 용 테이블의 구조가 된다. 여기에 아래 쿼리를 실행하면 

그림과 같이 우리가 원하는 결과를 얻을 수 있다.

약간만 수정하면 두루 두루 쓸모가 많을것 같다.

이래서 실무자 면접은 참 재미있는것 같다. 내 실력을 평가받는 자리도 되고 그로인해 공부도 되고.

---------------------------------------------------------- ID , PID 가 문자형인경우 ----------------------------------------------------------

ID , PID 문자형일 경우 어떻게 수정해야 하는지에 대한 문의가 있어 관련 내용 공유 드립니다.

위 데이터에서 각 컬럼을 문자형으로 변경 후 앞에 a를 붙여 a1, a2, a3 형식으로 변경 하였습니다.

출력 결과 : 

MSSQL 로 구현하면 >> https://devse.tistory.com/69

https://www.microsoft.com/ko-kr/download/details.aspx?id=42299  << 로그인 없이 다운로드 가능

구글이나 네이버에서 검색하면 대부분 로그인을 해야하는 곳으로 안내한다. 

SQLEXPRADV 라고 되어있는 것이 Management Studio  가 함께 설치되는 파일이다. 

https://www.microsoft.com/ko-kr/download/details.aspx?id=42299  다운로그 경로 다시한번.

삭제시 에는

Microsoft SQL Server 2014(64비트) 를 클릭하여 삭제를 진행 합니다.

mysql 5.6 이후 버전에서는 셀이나 커맨드 라인에서 계정정보를 그대로 노출하면 경고가 발생한다.

Waring: Using a password on the command line interface can be insecure.

작업은 성공하니 문제는 없으나  나중 버전에서는 -p 옵션을 사용할 수 없다는 얘기도 있으니..

그걸 제외 하더라도 스크립트에 계정 정보를 모두가 볼수 있게 적어주는건 좋지 않기 때문에 (보안만 확실하면 큰 문제는 되지 않지만.. 어찌되었건..)

mysql_config_editor 를 사용하여 로그인파일을 생성해 놓고 설정명만을 명시하여 사용할 수 있다.

구문도 짧아지니 이것도 이점이라 할수 있겠다.

ex-1) # mysql_config_editor set --login-path=[접속명칭] --host=[host 정보] --user=[계정명] --password --socket=/tmp/mysql.sock --port=3306

ex-2) # mysql_config_editor set --login-path=[접속명칭] --host=[host 정보] --user=[계정명] --password --port=3306

대부분의 블로그 에서는 ex-1 로 소개하고 있으나 ex-2 처럼 작성 하여도 특별한 문제는 없어 보인다. 

(오늘 처음 써봤고, --socket 옵션에 대해서 특별한 설명은 못 찾겠다 꾀꼬리~)

등록이 완료되면 계정 폴더에 .mylogin.cnf 파일이 생성된다.

열어봐야 읽을수도 없으니 아래 이미지를 보고 궁금증만 푸시길 바란다.

 참고: # chmod 666 .mylogin.cnf 로 권한정보를 수정하게 되면 mysql --login-path=dump_usr 했을때 오류가 발생한다.

        이런 궁금증은 제가 풀어 드릴테니 괜한 시간 낭비는 하지 않기를 바랍니다.

등록 내역을 보고 싶을때는 # mysql_config_editor print --all

mysql 에 접속               # mysql --login-path=dump_usr

특정 접속 정보만 삭제   # mysql_config_editor remove --login-path=dump_usr

전체 정보를 삭제           # mysql_config_editor remove 

이상 궁금증을 해결하다보니 .. 내용이 길어 졌습니다. 이해하고 넘어가 주세요

MySQL 의 binary_log 를 분석하기 위해  more / cat 등으로 열어 보면 글자가 깨져서 표시됩니다.

정상적으로 읽기 위해서는 변환 작업이 필요합니다.

- 로그파일 전체를 변환             : #mysqlbinlog [log 파일명] > [파일명].sql

- 특정 기간의 로그내용만 변환  : 

     #mysqlbinlog --start-datetime='2016-03-01 00:00:00' --stop-datetime='2016-03-21 23:59:59' [log 파일명]  > [파일명].sql

- 특정 형식의 쿼리 내용만 변환 : 

     #mysqlbinlog [log 파일명] | grep -i -e "^create" -e "^update" | more      // 콘솔로 출력

     #mysqlbinlog [log 파일명] | grep -i -e "^create" -e "^update"  >   [파일명].sql   // 파일로 출력

   * 기타 다양한 조건을 적용할 수 있다 (구글신께 물어 보세요)

하지만 변환 중 아래와 같이 오류가 발생하는 경우가 있습니다.

              -원인은 mysqlbinlog 가 실행될 때 /etc/my.cnf 파일을 읽어 들이는데 mysqlbinlog가  default-character-set 설정을 

                인식할 수 없어 발생한다.

                해결책은 my.cnf 파일에서 [client] 섹션에 있는 default-character-set = utf8 을 주석처리 후 mysqlbinlog 명령을 실행한다.

                작업이 완료된 후 my.cnf 의 default-character-set의 주석은 해제 한다. 

                                 (일부 블로그에서는 mysql을 재 시작하라고 하는데 그럴 필요는 없다.)

                 my.cnf 는 mysql 이 구동시 한번만 읽기 때문에 중간에 변경을 하여도 현재 서비스에는 영향이 없다.

                 mysqlbinlog 또한 구동시에 my.cnf 읽는다.

mysql 에서 분석을 위해 Slow Query log 파일을 초기화 해야할 필요가 있을 때 mysql 을 재시작 하지 않고 초기화 할 수 있는 방법이다.

1. 로그를 다른 이름으로 복사

~]# cp slow.log 20160315.slow.log

2. 원본 로그 파일의 내용을 초기화 한다.

~]# cat /dev/null > slow.log

mysql 서버의 재시작 없이 log 파일의 내용을 초기화 할 수 있다.

mysql 실행 계획을 어떻게 분석해야 하는지에 대한 정리 입니다. 

PDF 파일도 첨부 합니다. 

실행 계획을 출력하는방법은 

EXPLAIN

SELECT * FROM t_table WHERE col='val';

입니다.

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;

[세미나] 최적 데이터 구조 설계

목차: 

http://cyber.dbguide.net 사이트에서 회원 가입후 무료로 시청 가능한 자료 입니다.

개인적으로 스마트폰에 넣어 가지고 다니면서 틈틈히 보는걸 좋아해 다운받았다가.

공유하게 되었습니다.


아래 링크 통해서 바로 보거나 다운 받으실 수 있습니다.

https://drive.google.com/folderview?id=0BzfE8slPbOvbaWZ2cTRYY0xuMlk&usp=sharing

바로가기 앱: https://play.google.com/store/apps/details?id=com.orengineer.android.a209.floatingshotcut

ER Studio 관계선 정리

-옵션에 따른 관계선의 모양 입니다.

-----------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

바로가기 앱: https://play.google.com/store/apps/details?id=com.orengineer.android.a209.floatingshotcut