Developer SE의 만남

설명에 사용한 MySQL 버전 : 5.7.20

1. Scheduler 확인

show global variables like '%schedule%';

사용 가능할 경우 ON, 그렇지 않을 경우 OFF 로 표시 됩니다.

1-1. Scheduler 사용 설정

   ini를 수정하게 되면 MySQL 을 재시작 해주어야 합니다. 그렇지 않을 경우 ini 수정 후 set global 명령어를 통해서

   수동으로 설정을 변경해 주시면 됩니다. 추후 재시작 하게되면 그때부터는 ini의 설정에 따라서 동작 하게 됩니다.

    ini 에 아래 구문을 추가합니다. 

event_scheduler = 1

혹은 

event_scheduler = ON

   "1" 이나 "ON" 이나 동일하게 동작(설정) 됩니다.

1-2. Process 확인

show processlist;

 Scheduler 설정을 정상적으로 완료하였을 경우 아래와 같이 event_scheduler 데몬이 접속 중임이 확인 됩니다.

2. Schedule 생성

  ◈ Schedule 은 DB에 종속되기에 명령을 실행할 DB가 선택된 상태에서 생성해야 합니다.

DROP EVENT IF EXISTS `calc_ranking`;
DELIMITER $
CREATE EVENT IF NOT EXISTS `calc_ranking`
ON SCHEDULE
	EVERY 5 HOUR                  -- 5시간 마다 실행 (사용 가능 형식: second, minute, day)
	STARTS '2020-01-01 00:00:00'  -- 지정하지 않을 경우 현재 시간부터 5시간 경과 후에 첫 스케줄러가 동작됨
ON COMPLETION PRESERVE
ENABLE
COMMENT 'calc_profile_ranking'
DO 
BEGIN
    call test_procedure(1);
    UPDATE tbl_test set action_status = 1;
END$
DELIMITER ;

  - 매일 5시에 실행되는 작업을 만들고 싶을 경우 ON SCHEDULE 부분을 아래와 같이 수정 합니다.

ON SCHEDULE
	EVERY 1 DAY                   -- 하루에 한번
	STARTS '2020-01-01 05:00:00'  -- 기준 시각이 05시 이기 때문에 매일 5시에 스케줄러 실행

  - 특정 문서의 경우 매일 특정 시간에 실행되는 스케줄을 등록하기 위해 아래와 같은 구문을 사용하는데

그럴 필요 없이 과거 날짜의 특정 시간을 시작시간으로 설정하면 해당 시간 을 기준으로 하루에 한번 실행 됩니다.

STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR)

3. Schedule 확인

  ◈ Schedule 은 DB에 종속되기에 명령을 실행할 DB가 먼저 선택되어 있어야 합니다.

SHOW EVENTS;	-- 현재 선택된 DB에 등록된 Schedule을 조회 합니다.
or
SELECT * FROM information_schema.EVENTS;  -- 모든 DB에 등록된 설정을 조회 합니다.

- Schedule 등록 스크립트를 확인하실 수 있습니다.

SHOW CREATE EVENT `Schedule Name`;
SHOW CREATE EVENT `calc_ranking`;

- Create Event 컬럼의 내용은 아래와 같습니다.

CREATE DEFINER=`root`@`%` EVENT `calc_ranking` 
ON SCHEDULE EVERY 5 HOUR STARTS '2020-01-01 00:00:00' 
ON COMPLETION PRESERVE ENABLE COMMENT 'calc_profile_ranking' 
DO BEGIN
    call test_procedure(1);
    UPDATE tbl_test set action_status = 1;
 END

4. 삭제

DROP event `이벤트명`;
DROP EVENT `calc_ranking`;

이상 입니다.

특정 테이블의 조회 결과(대량 ROW)를 파일로 저장하고

저정된 결과 파일을 다시 DB(Table)로 저장하는 방법 입니다.

대량의 Row(수백 MB)이기 때문에 쿼리를 통한 Insert가 아닌 load data 명령을 통해 수행 합니다.

▼  대상 테이블 구조

▼  다량의 조회 결과를(1000만건) 파일로 저장하는 쿼리 입니다.

select * from tbl_user_mode_score limit 10000000
INTO OUTFILE 'e:\query_result_1000man.txt'
FIELDS TERMINATED BY '\t'
ENCLOSED BY ''
LINES TERMINATED BY '\n';

  - 조회 결과는 아래와 같습니다. (Field 와 Field 는 탭으로 구분)

▼ 위 조회 결과를 별도 Table에 저장하는 방법 입니다.

    1. 데이터를 저장할 DB에 조회 내용과 동일한 구조의 테이블을 생성 합니다.

    2. cmd 창에서 mysql 콘솔에 접속 합니다.

    3. 아래 명령어를 실행하여 데이터를 입력 합니다.

load data infile 'e:/query_result_1000man.txt' into table tbl_user_mode_score ( usn, mode_idx, win_cnt, lose_cnt, last_play_date ) ;

    - 입력 완료시 메시지 : 입력 건수 및 오류 상황이 표시 됩니다.

이상 입니다.

Oracle 과 MSSQL에 있는 DB Link 서비스를 MySQL에서 지원하는 FEDERATED 엔진을 이용하여 구현하는 방법 입니다.

1. 설치되어 있는 엔진 확인

  - CentOS에서 yum 으로 설치했을때 기본적으로 사용 가능한 엔지들의 목록 입니다.

2. 엔진을 사용할 수 있도록 설치

3. FEDERATED 엔진의 설치여부 확인

  - FEDERATED 엔진이 목록에 표시되지만 사용 가능하지 않은 상태 입니다.

4. 엔진 사용 가능 하도록 my.cnf 수정

  - [mysqld] 하위에  federated 항목을 추가 합니다.

5. 엔진의 상태를 확인 합니다.

 - FEDERATED 엔진의 Support 상태가 YES 로 변경 되었습니다.

----------------------------- FEDERATED 엔진을 사용하여 테이블을 생성하여 원격지 테이블과 연결 -------------------------

1. 원격지에 테이블을 생성 합니다.

- 실제 데이터가 입력되는 테이블 입니다.

- 데이터 폴더에는 .frm(구조) / .ibd(데이터) 파일이 생성 됩니다.

◈ 만약 원격지 DB의 비밀번호에 특수문자'@' 를 사용하는 경우 2번 방식으로는 테이블을 생성할 수 없으니

     2번을 제외하고 3번 항목부터 진행해 주시면 됩니다.

2. 로컬에 테이블을 생성 합니다.

 - 원격지의 테이블을 참조하기 위한 테이블 입니다

 - 데이터 폴더에는 .frm(구조) 파일만 생성 됩니다.

   - CONNECTION 옵션의 구성 = 'mysql://[계정]:[비밀번호]@[서버ip]:[port]/[원격지DB명]/[원격지 테이블명]'

   - Pass 에서 특수문자 '@' 를 사용하는 경우 아래와 같이 테이블 생성시 오류가 발생합니다.

3. 테이블 생성시 사용할 SERVER 개체를 생성 합니다.

      - 생성된 서버 정보를 조회합니다.

4. 생성된 서버로 테이블을 생성 합니다.

- 로컬에 생성하는 테이블과 원격지 테이블이 동일하다면 CONNECTION 옵션에서 테이블명은 생략 가능합니다.

>> . 이상 모든 설정을 완료 했다면 로컬에서 원격지의 Table을 로컬 테이블 처럼 사용 가능합니다. select, insert, update, delete 모두 가능 합니다.

현재 활성화된 창을 특정 위치로 이동하는 명령 입니다.

사용해본 클램프 중에서는 Dewalt 제품이 가장 만족도가 높았습니다.

그중에서 M사이즈의 12인치 DWHT83140-KR 제품이 활용도가 높습니다.

MySQL / MariaDB에서 Slow Query Log를 활성화하기 위한 방법과 실시간으로 변경 가능한 Variable에 대해서 설명드리겠습니다. 

개인적으로는 Slow Query Log를 테이블로 관리하는것을 좋아합니다. 실행된 쿼리 검색 및 통계내가가 편리하기 때문입니다.

아래 3개의 쿼리를 실행하여 설정 가능한 변수를 확인할 수 있습니다.	쿼리 결과
show global variables like '%slow%'; show global variables like '%long%'; show global variables like '%log_output%';

>> my.ini 파일 수정 : 노란색 영역의 3개 항목을 추가하신 후 MySQL / MariaDB 서비스를 재시작해주시면 됩니다.

set global 쿼리를 사용해서 설정을 변경할 수 있습니다.

하지만 long_query_time 변수는, 쿼리로는 변경은 가능하지만 실제 반영은 안 되고 있기에 ini 파일에서 설정해 주셔야 합니다.

	slow_query_log, log_output 은 운용 중인 상태에서 서비스 재시작 없이 설정 변경이 가능하며 즉시 해당 설정이 적용됩니다.

>> 테이블에 기록된 Slow Query 결과입니다.