주석등을 넣어 놓은 저장 프로시져는 유용하게 사용 된다. 그러나 DB 를 복원하고 보니 주석이 사라져 버린 경험이 있을 것이다.

 

이 글에서는 어떻게 하면 mysql 을 복원 햇을 때 저장프로시져와 함수, 이벤트 등에 주석이 남아 있게 할 수 있는 지를 알아 보도록 한다.

 

 

1. 백업을 잘 한다.

 

- 백업 할 때 DB 의 스키마와 데이타만 백업 한다.

 

mysqldump -u계정 -p비밀번호 특정DB명   > 저장할파일명.sql

 

 

- 저장 프로시져와 함수, 이벤트를 별도의 파일로 백업 받는다.

 

mysqldump -u계정 -p비밀번호 --routines --events --no-create-info --no-data --no-create-db --skip-opt 특정DB명 > 함수프로시져트리거.sql

 

 

2. 복원을 잘 한다.

 

- DB 를 복원한다.

 

mysql -u계정 -p비밀번호 특정DB명 < 백업한파일명.sql

 

 

- 저장 프로시져와 함수, 이벤트를 GUI 툴에서 불러 들여 실행 한다.

 

 

3. 결과를 확인하다.

 

- 주석 부분이 그대로 있는 것을 확인 할 수 있다

 

댓글을 달아 주세요

리눅스에 mysql 설치 후 원격으로 로그인 안되는 문제 해결

MySQL 2014. 7. 20. 19:18 Posted by 불가사리 bluemount

mysql 을 리눅스 서버에 설치하고 원격의 클라이언트에서 툴을 이용해서 접근하려 하니 접속이 안되는 경우가 있다. 이때는 두가지를 의심해 봐야 한다. 

첫째,리눅스의 방화벽이 3306 Port 의 접속을 허용하고 있는가 ?   

둘째, mysql 계정이 원격지에서 로그인 할 수 있는가 ?

셋째, 그래도 안되면 ... 

 

I. 리눅스 방화벽 조정 하기 : iptables 를 사용하고 있다면

 

1. 리눅스 방화벽 확인 하기

 

iptables -L

 

- 아래의 그림을 보면 ssh, ftp, http 에 대한 허용을 볼수 있을 뿐 어디에도 mysql 은 없는 것을 확인 할 수 있다.

 

 

 

2. 방화벽에 mysql Port 허용 하기

 

sudo vi /etc/sysconfig/iptables

 

3. 허용 하는 구문 추가하기

 

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

 

※ 방화벽 설정은 순서가 중요하다. 허용하고 난후 거부해야 한다.

 

 

4. 방화벽 재 시작 하기

 

sudo /etc/init.d/iptables restart

 

5. 재 확인 하기

 

iptables -L

 

- 아래 그림과 같이 mysql port 가 살아 있는 것을 알 수 있다.

 

 

 

※ 그런데 ubuntu 에서는 ufw를 이용해서 방화벽을 설정하고 그것이 더 단순하다. 

    아래 IV 을 참고 하면 된다. 

 

 

▶ Cnetos 7 에서 사용한 방화벽 조정 하기

 

1. 포트 추가 하기 : 3306 기본 포트 허용

 

sudo firewall-cmd --permanent --add-port=3306/tcp

 

2. 방화벽 재로딩 하기

 

sudo firewall-cmd --reload

 


 

II. mysql 계정 원격 로그인 권한 부여하기

 

1. mysql 에 접속하기

 

- 로컬서버에서 mysql 에 접속 한다.

mysql -uroot -p

 

2, 권한 부여 하기

 

- IP 주소 대신  '%' 를 넣으면 모든 곳에서의 접속을 허용한다는 의미 임.

 

GRANT ALL PRIVILEGES ON *.* TO '사용자명'@'IP주소' IDENTIFIED BY '비밀번호';

 

3. 권한 적용 하기

 

FLUSH PRIVILEGES;



III. 아무리 해도 안된다. 


1. bind-address 수정 하기 


sudo vi /etc/mysql/my.cnf



#bind-address           = 127.0.0.1


# 아래에 서버의 주소를 입력해 준다. 


bind-address = 192.168.123.100



2. mysql 재시작하기 

sudo service mysql


IV.  방화벽은 사실 ufw 를 사용하는 경우가 많다. 


1. 설정방법은 아래 링크 참조한다. 


http://www.ubuntu-kr.org/wiki/doku.php?id=program:ufw%EC%84%A4%EC%A0%95


댓글을 달아 주세요

mysql의 윈도우에서의 my.ini 파일 위치

MySQL 2014. 6. 27. 15:48 Posted by 불가사리 bluemount

탐색기를 이용해서 검색하면 쉽게 찾을 줄 알앗던 my.ini파일을 찾기가 쉽지 않앗다.

 

그런데 생각해 보니 윈도우에서 왜 그러게 무식하게 찾아야 하는지 고민하게 되었다.

 

1. my.ini 파일 찾기

 

-  mysql에 접속해서 환경 설정 값을 읽어 찾아 본다.

 

SHOW VARIABLES WHERE Variable_Name LIKE "%dir"

 

2. 결과 값 보기

 

 - 아래 내용을 보면 mysql이 설치된 곳을 찾을 수 있다.

- mysql  이 설치된 곳에 보면 my.ini 을 찾을 수 있다.

 

3. 왜 my.ini 파일을 찾아야 하나 ?

 

- mysql의 주요 설정은 이 파일 안에 있다.

 

- strict mode 를 변경할때 또는 event 를 활성화 할 때 이 파일을 설정 해 줘야 컴퓨터를 껏다 켜도 설정이 유지 된다.

 

※  Set the SQL mode to strict 설정하려면 필요하다.

댓글을 달아 주세요

JOIN 을 이용한 다중 행 UPDATE와 DELETE 사용하기

MySQL 2014. 6. 4. 11:28 Posted by 불가사리 bluemount

UPDATE JOIN 을 이용한 다중행 업데이트 하기 


다른 테이블을 기준으로 해서 데이블을 업데이트 할 때가 있다. 

이럴 때 조인문을 이용해서 UPDATE 하게 되는데 아래와 같이 사용하면 된다. 


UPDATE [업데이트할 테이블명2] A INNER JOIN [테이블명-2] B

ON A.[조인할 컬럼명] = B.[조인할 컬럼명]

SET A.[변경할 컬럼명] = 변경할값

( WHERE 절 )


예) UPDATE JOIN 


UPDATE Member AS A INNER JOIN Member_Data AS B

ON A.UserID= B.UserID

SET A.STATUS_CD = B.STATUS_CD

WHERE B.STATUS_CD <> '300'



DELETE FROM JOIN


다른 테이블의 값을 기초로 테이블의 값을 다중으로 삭제해야 하는 경우가 있다. 

이럴 때 위의 UPDATE JOIN 문과 비슷하게 사용하면 될 거 같으나 그렇게 되지 않느다. 


-- 아래와 하면 될 거 같다.. 그런데 안된다. 


DELETE FROM 

  [Table-A]

INNER JOIN 

  [Table-B] ON Table-A.ID = Table-B.ID 

WHERE 

  Table-B.NAME= '홍길동'


- 아래와 같이 해야 된다. 

- 지워지는 데이타는 Member Table 에 있는 데이타임. 


DELETE FROM 

  [Table-A] Using [Table-A]

INNER JOIN 

  [Table-B] ON Table-A.ID = Table-B.ID

WHERE 

  Table-B.NAME= '홍길동'


예) DELETE USING JOIN 


DELETE FROM Member USING Member

INNER JOIN Member_ERROR 

ON Member.UserID= Member_ERROR.UserID

WHERE Member_ERROR.Member_Status   = '200' ; 



  • DELETE와 FROM 절 사이에 삭제할 테이블 명시 : test1(t1)

    DELETE
     t1
    FROM test1 as t1 INNER JOIN test2 t2
    WHERE t1.id=t2.id 
    ;


  • FROM과 USING 절 사이에 삭제할 테이블 명시 : test1(t1)
    DELETE 
    FROM t1
      USING test1 as t1 INNER JOIN test2 t2
    WHERE t1.id=t2.id 
    ;


참고 : http://dev.mysql.com/doc/refman/5.0/en/delete.html

http://intomysql.blogspot.kr/2011/01/join-delete-multiple-table-delete.html

http://moonlighting.tistory.com/99



댓글을 달아 주세요

MySQL Event 확인하기

MySQL 2014. 5. 23. 12:00 Posted by 불가사리 bluemount


-- 이벤트 목록 보기

 

SELECT * FROM information_schema.EVENTS

 

또는

 

SHOW EVENTS ;

 

 

-- 등록 되어 있는 이벤트 내용 보기

SHOW CREATE EVENT `이벤트명` ;

 

 

-- 등록되어 있는 이벤트 수정 하기

 

ALTER EVENT `이벤트명`

ON SCHEDULE EVERY 1 MONTH STARTS '2014-05-27 01:00:00' ;

 

 

-- 등록되어 있는 이벤트 삭제 하기

DROP event `이벤트명` ;

 

 

  
 

댓글을 달아 주세요

  1. kamui 2016.06.21 16:24  댓글주소  수정/삭제  댓글쓰기

    간략하지만 딱 핵심만 있네요! 감사합니다

mysql 이벤트 스케쥴러 사용하기

MySQL 2014. 5. 23. 11:41 Posted by 불가사리 bluemount

정기적으로 무언가 DBMS에서 하고 싶은 경우가 있다. MS-SQL 은 MS-AGENT 가 있다. 그런데 mysql에서는 그런 기능을 없는 걸로 알고 있었다. 그런데 MySQL 5.16에서 Event Scheduler가 추가되어 이젠  MS-AGENT 가 부럽지 않다.

 

 

1. mysql 접속해서 환경변수 확인

 

- 리눅스에서 mysql에 접속한다.

 

mysql -uroot -p

 

- mysql 접속해서 환경 변수 조회 해 본다.

 

show variables like 'event%' ;

 

※ event_scheduler 이 OFF 면 이벤트스케쥴러가 활성화 되어 있지 않다는 의미다. 이벤트를 만들어 놓아도 정해진 시간에 자동으로 실행되지 않는다.

 

 

 

2. 이벤트가 동작하게 설정 한다.

 

SET GLOBAL event_scheduler = ON ;

 

- 이벤트 프로세스가 실행되고 있는 걸 확인 할 수 있다.

 

SHOW PROCESSLIST \G ;

 

※ \G 옵견은 쿼리결과를 아래로 보기 좋게 나오게 하는 옵션이다.

 

 

3. 그런데 서버를 껏다가 다시 켜면 이벤트가 활성화 안되어 있다면

 

sudo vi /etc/mysql/my.cnf

 

- 아래와 같이 mysqld 영역안에 추가 하고 저장하고 나온다.

 

event_scheduler = ON

 

- mysql 서비스 재시작해 준다. 

 

sudo /etc/init.d/mysql restart

 

※ 이제는 서버를 재부팅해도 이벤트가 항상 살아 있을 것이다.

 

 

 

댓글을 달아 주세요

  1. 한상훈 2015.09.19 11:19  댓글주소  수정/삭제  댓글쓰기

    딱 필요한 정보네요. 감사합니다.

  2. 박진호 2017.09.26 08:24  댓글주소  수정/삭제  댓글쓰기

    재부팅 후 스케줄러가 동작 하지 않는데 혹시 어딜 봐야 할까요?

MySQL Fetch Cursor 문 사용방법

MySQL 2014. 5. 17. 21:27 Posted by 불가사리 bluemount

Mysql에서 커서(Fetch Cursor)를 사용할 때 아래와 같은 경고메세지를 얻는 경우가 있다. 

1329: No data - zero rows fetched, selected, or processed

무슨 이유인지 커서가 다 돌지를 않고 중간에 멈춘거 같은 것을 보고 역시  Mysql 은 믿을 수가 없어 그런 느낌을 갖기도 했지만 역시 믿을 수 없는 건 나의 실력이었다. 


Mysql 의 커서(Cursor)를 충분히 분석하고 기능을 알아보려 한다. 

커서는 기본적으로 커서(Cursor)를 돌면 어떤 데이타를 처리하는 것이 목적이다. 간단한 예제 수준만 알아서 해결이 안되는 경우 아래의 내용을 검토해 보자. 


1. Fetch Cursor 기본 구조  : 아주 기초적 구조다. 이해가 안되면 외어라. 


CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  

  -- 커서로 만들 데이타 값들

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  -- 커서가 마지막에 도착할 때의 상태값

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


  -- 커서를 연다. 

  OPEN cur1;

 

  -- Loop 가 돌아간다. 

  read_loop: LOOP

 

  -- 커서로 만드어진 데이타를 돌린다. 

  FETCH cur1 INTO vUserID ;

 

    SET vRowCount = vRowCount +1 ; 

    

    -- 커서가 마지막 로우면 Loop를 빠져나간다. 

    IF done THEN

      LEAVE read_loop;

    END IF;

 

  END LOOP;


  SELECT vRowCount ; 

  -- 커서를 닫는다. 

  CLOSE cur1;

 

END;


- 예제 테이블 생성 및 데이타 생성 


CREATE TABLE Member (

   userid VARCHAR(20),

   `point` INT

) ENGINE = InnoDB ROW_FORMAT = DEFAULT;


insert into Member (UserID ) VALUES ( 'User01') ; 

insert into Member (UserID ) VALUES ( 'User02') ; 

insert into Member (UserID ) VALUES ( 'User03') ; 

insert into Member (UserID ) VALUES ( 'User04') ; 


- 실행 해 본다. 5가 나와야 한다.  → 원하는 데로 나왔다. 


Call curdemo() ;


2. 응용편 - 이 이야기를 하고 싶었다. 


- 아래 같이 만들면 될 거 같지만 커서는 1번 만 돌고 빠져나가 버린다. 


-- 이미 있는 프로시져 삭제한다. 

DROP PROCEDURE IF EXISTS curdemo ;


CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  DECLARE vPointValue int ; 

    

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


  OPEN cur1; 


  read_loop: LOOP

 

  FETCH cur1 INTO vUserID ;

  

    -- Not Found Handler 값 변화 살펴보자.

  SELECT done ;  

 

    -- 포인트 테이블의 값을 읽어 온다. 

    SELECT PointValue into vPointValue FROM pointhistory

    WHERE UserID = vUserID ; 

    

    --  회원테이블의 포인트 값에 업데이트 한다. 

    UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 

    

    -- 커서가 몇번을 도는지 알아 본다. 

    SET vRowCount = vRowCount + 1  ; 

    

    IF done THEN

      LEAVE read_loop;

    END IF;

 

  END LOOP;


  SELECT vRowCount ; 

  CLOSE cur1;

 

END;


- 예제 테이블 생성


CREATE TABLE PointHistory (

   UserID VARCHAR(20),

   PointDate DATE,

   PointValue INT

) ENGINE = InnoDB ROW_FORMAT = DEFAULT;


- 예제 데이타 생성


insert into PointHistory (  UserID  ,PointDate  ,PointValue

VALUES (   'User02'   ,'2014-01-01'  , 10  )


- 실행 결과 없다.  → 우리가 원하는 바가 아니다. vRowCount 가 1이다. 1번 돌았다. 


Call curdemo() ; 




3. 해결 방법 


- 커서의 DECLARE CONTINUE HANDLER FOR NOT FOUND 는 커서의 집합이 없을 때이기도 하지만 커서안에서 다른 쿼리문의 집합이 없을 때도 True 을 반환한다. 

- 그래서 Mysql 은 커서가 이상해 이런 얘기가 나오는 거다. 

- MSSQL은 커서의 집합만을 비교하여 마지막 커서행인지 판단해 주는데 mysql 의 경우 커서뿐만 아니라 커서안의 select 의 집합도 NOT FOUND로 판단하고 있다. 

- 그래서 커서안의 select  의 집합의  NOT FOUND와 Curosor 의 NOT FOUND을 구분하여 줄 필요가 있다. 


DROP PROCEDURE IF EXISTS curdemo ;


CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  DECLARE vPointValue int ;   

  

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE ;


  OPEN cur1; 


  REPEAT

 

  FETCH cur1 INTO vUserID ;


  -- Not Found Handler 값 변화 살펴보자.

  SELECT done ;  


    -- 커서가 마지막이 아니라면    

    IF NOT done THEN      

    

      SELECT PointValue into vPointValue FROM pointhistory

      WHERE UserID = vUserID ; 

    

      UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 

      -- SELECT concat(vUserID, '', vPointValue)  ; 

    

      SET vPointValue = 0 ; 


      -- 위의 select 가 조회 데이타가 없어서 not found 되어

      -- fetch 문을 빠져나가는 걸 방지한다.  

      SET done = False ;        


    END IF;  

  

  UNTIL DONE END REPEAT;

  CLOSE cur1;

END;


- 위의 예제는 커서가 마지막행을 만나기 전에 SELECT 문에서 조회값이 없는 경우 Not Found 도 발생하는 걸 인위적으로  SET done = False 으로 해결 하고 있다. 




- 아래와 같은 방법을 사용해도 된다. 

- 아래의 예는 Handler 의 Scope(영역)을 이용한 방법이다. 


-- 이미 있는 프로시져 삭제한다. 

DROP PROCEDURE IF EXISTS curdemo ;


CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  DECLARE vPointValue int ; 

    

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


  OPEN cur1; 


  read_loop: LOOP

 

  FETCH cur1 INTO vUserID ;

 

   SELECT done ;  

 

    BEGIN

    DECLARE CONTINUE HANDLER FOR NOT FOUND   

    

      -- 이건 왜 넣어야 하는지 모르겠다. 그런데 넣어야 한다.   

      -- 아래와 같이 안하면 null 값이 들어간다.   

      SET vPointValue = 0 ; 

    

       -- 포인트 테이블의 값을 읽어 온다. 

      SELECT PointValue INTO vPointValue FROM pointhistory

      WHERE UserID = vUserID ;     

      

      -- SELECT CONCAT(vPointValue) ; 

      -- SELECT CONCAT(vUserID) ; 

      --  회원테이블의 포인트 값에 업데이트 한다. 

      UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 

    END;        

        

    IF done THEN

      LEAVE read_loop;

    END IF;

    

  END LOOP;

  

  CLOSE cur1;

 

END;




참고 사이트 : 

http://stackoverflow.com/questions/12017869/mysql-cursor-fetching-only-one-row


http://stackoverflow.com/questions/3463283/how-to-get-rid-of-error-1329-no-data-zero-rows-fetched-selected-or-process


http://www.devshed.com/c/a/mysql/error-handling/


http://www.info-system.eu/en/knowledge-exchange/37-java/96-alternatywna-metoda-iterowania-po-kursorze-w-mysql


http://www.devshed.com/c/a/mysql/error-handling/


http://intomysql.blogspot.kr/2010/12/stored-routine.html

댓글을 달아 주세요

mysql 암호분실 시

MySQL 2014. 4. 29. 16:35 Posted by 불가사리 bluemount

윈도우 mysql 사용을 테스트용으로 사용하고 있다. 그런데 갑자기 root 계정이 로그인이 안된다. 머지..하다가 내가 바꾼거 같지는 않은데... 고민하다. 인터넷을 찾아 보니  관리자 암호 분실 시 재 설정하는 방법이 나와 있어 정리해 본다. 리눅스용도 있지만 내가 사용하는 윈도우 mysql 이라 아래와 같이 설정하고 암호 재설정했다. 

 

 

1. mysql 서비스 중단

 

- 윈도우 서비스 관리자에서 mysql 서비스 중단해 준다.

 

※ 이단계가 가장 중요하다. 서비스 중단하지 않고 2단계 실행시 3단계실행이 안된다.

 

 

2. 승인 절차 거치지 않고 MySql 데몬 실행 하기

 

- 명령프롬프트를 열어 MySQL 이 설치된 디렉토리에서아래 명령어 실행

 

mysqld.exe --skip-grant


※ 명령프롬프트 멈춘것처럼 보여도 멈춘게 아니라 실행하고 있는 상태니 안심하고 다음단계 진행  


 

3. mysql 접속

 

또 다른 MS-DOS 창을 열어서 MySQL 이 설치된 디렉토리에서 아래와 같이 실행 합니다.

 

mysql -uroot

 

 

4. 암호 변경 하기

 

- 사용자 계정 정보는 mysql 데이타베이스에 있으니까요. 

use mysql ;

 

- 사용자 계정의 비밀번호를 재설정 합니다. 

update user set password = password('신규암호') where user = 'root' ;

 

- 권한을 적용해 줍니다. 

flush privileges ;

 

 

5. mysql 서비스 재 시작

 

- 프로세스 리스트 에서 mysql  서비스 중단한다.


6. 이제 정상적으로 계정과 암호를 사용해서 로그인 하면 됩니다. 

'MySQL' 카테고리의 다른 글

mysql 이벤트 스케쥴러 사용하기  (2) 2014.05.23
MySQL Fetch Cursor 문 사용방법  (0) 2014.05.17
mysql 암호분실 시  (0) 2014.04.29
mysql Process 보기 및 Kill 하기  (0) 2014.02.27
MYSQL 에서 SHOW PROCESSLIST로 쓰레드 정보 보기  (0) 2014.02.09
MySQL 논리함수  (0) 2014.02.01

댓글을 달아 주세요

MySQL Prepared Statement

MySQL/저장프로시져 2014. 3. 25. 09:44 Posted by 불가사리 bluemount

Introduction to MySQL Prepared Statement


http://www.mysqltutorial.org/mysql-prepared-statement.aspx

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html


사용 예시 

1. 인자 받아 처리 하는 경우


2. 쿼리문 조합해서 실행하는 경우 

'MySQL > 저장프로시져' 카테고리의 다른 글

MySQL Prepared Statement  (0) 2014.03.25
mysql 매개 변수 없는 저장 프로시져 만들기  (0) 2014.01.14

댓글을 달아 주세요

mysql Process 보기 및 Kill 하기

MySQL 2014. 2. 27. 18:54 Posted by 불가사리 bluemount

1. mysql 프로세스 현황 보기

 

show processlist ;

 

2.

댓글을 달아 주세요