MySQL 5.5_유저등록

My SQL은 MS SQL과 다르게 DB에 연결 하기 위해서 유저를 등록해줘야 합니다.

DB별로 접근을 제한 할 수도 있고, IP대역 및 특정 IP별로 유저를 제한이 가능 합니다. 

 

문제 원인

1. 잘 되던 툴이 갑자기 안된다고 접수 받음

2. 확인해 보니 서버를 이전하면서, IP정보가 변경 되었음 

 

 

 

문제 해결 방법 

문제는 mysql DB에 user 테이블에 등록된 정보에 IP가 변경되었는데 Update가 되지 않아서 발생 하였습니다.

 

SELECT * FROM mysql.user

 

우선 mysql DB에 user 테이블에서 해당 유저가 등록여부를 확인 하였습니다.

 

(보안상 음영처리)

 

우선 해당 Host와 user에 보니 변경되기 전의 IP가 등록이 되어 있었습니다.

하여, 해당 Host를 새로운 Host로 Update 하였습니다.

 

 

UPDATE USER SET HOST = '10.10.123.%' WHERE USER = 'Test'

 

* Host 업데이트 부분에 %표시는 10.10.123 대역의 모든 IP에 해당 하는 Test계정을 접속 가능 하도록 하겠다는 뜻 입니다.

* 특정 IP가 아닌 IP 대역폭별로 설정 한 것 입니다.

 

업데이트 이후에 적용을 하기 위해 

 

FLUSH PRIVILEGES;

 

위의 명령어를 입력 합니다. 해당 작업은 MSSQL에서 COMMIT의 개념으로 저는 받아 들였습니다. (새로고침이라 합니다.)

* http://blog.daum.net/kimuks/7532350 (FLUSH PRIVILEGES) 참조

 

해당 작업을 모두 완료한 뒤에 다시 시도 해보라고 하였으나, 접속은 가능 하지만 DB의 테이블이 보이지 않았습니다.

여기서 한가지 놓친 부분이 있습니다. Mysql은 DB별로 접근 권한을 또 부여 해야 한다는 것 입니다.

 

그래서 다음의 테이블을 확인 하였습니다.

 

SELECT * FROM mysql.db

 

쭉 권한에 관련된 계정 들이 나옵니다.

 

 

DB에서 사용 가능 한 권한 및 사용 가능 유저들의 정보가 출력됩니다.

 

하여, 해당 테이블에도 위와 같이 변경되어야 합니다.

 

 

그리고 마지막으로, 보통 Real 환경은 Master - Slave 환경으로 구성되어 있으므로, Slave에도 똑같은 작업을 해야 합니다.

하지만 무조건 해야 하는 것은 아니고, 다음의 내용을 확인 한 뒤에 Slave에도 적용 여부를 판단해야 합니다. (현재 저는 Master에 적용)

 

 

SHOW MASTER STATUS

위의 쿼리를 입력하고 실행하면 다음과 같이 출력 됩니다. (Master이므로, MASTER로 입력)

 

위의 스크린샷을 보면 BingLog_Ignore_DB 컬럼에 mysql이 있습니다.

Slave에는 복제를 안하고 있는 것 입니다.

 

이제 Slave로 가서 확인을 해보겠습니다.

Slave에서 다음과 같이 상태를 확인 합니다.

SHOW SLAVE STATUS

 

컬럼 중에 Replicate_Ignore_DB에 만약에 DB명이 있다면, 해당 DB는 복제에서 제외하는 것 입니다. 

즉, master에 Binlog_Ignore_DB에 없다 해서 복제가 되는게 아니고 Master에 Binlog_Ignore_DB에 없으면서 Slave에 Replicate_Ignore_DB에 DB명이 없어야 합니다.

 

이번에 Slave에서는 Master에서 한 방법 이외에 새롭게 만드는 방법을 사용해 보도록 하겠습니다.

 

SHOW GRANTS FOR 'Test'@'10.10.123.%'

 

위의 쿼리를 입력하면 다음과 같이 출력 됩니다.

*FOR 다음부터 '해당 계정'@'해당 서버의 IP'

 

 

 

다음과 같이 나오는 쿼리를 복사하여, 붙여넣기를 해줍니다.

 

GRANT EXECUTE ON `gmtooldb`.* TO 'Test'@'10.10.123.%'; GRANT USAGE ON *.* TO 'Test'@'10.10.123.%' IDENTIFIED BY PASSWORD 'PASSWORD'; 만약에 PASSWORD 부분에 암호화된 PW가 아닌 순수한 패스워드라면 다음과 같이 입력 해야한다. GRANT USAGE ON *.* TO 'Test'@'10.10.123.%' IDENTIFIED BY  'PASSWORD';

 

다음과 같이 되어 있는 부분에서 새롭게 변경된 서버의 IP로 변경 후 실행 시켜주면 똑같이 만들어 집니다.

 

후에 다음 쿼리를 이용하여, 해당 기존의 계정을 삭제 합니다.

 

DROP USER 'Test'@'10.10.123.%'

 

또한 위와 마찬가지로 다음의 쿼리는 필수 입니다.

 

FLUSH PRIVILEGES;

 

만약에 새롭게 계정을 만들거나 할때 위의 방법으로 똑같이 하는 것이 제일 빠를 것 입니다.

 

 

Mysql 첫 글인지라 부족한 부분이 매우 많습니다. ^^;