Louis.Kim 의 제 3막

MySQL의 버전별 기능(Features) 변경 이력

오랜만에 포스팅한다.

파이썬 스크립트 개발에 틈틈히 php 수정등...

마리아 DB를 공부도 해야된다는 생각에 정신이 없다..

하지만 이게 잘 하고 있는건지...라는 의문의 1패...

잡설은 그만하고..

Script로 백업 도중 (스크립트 뽑아내는 명령어 중) Lock Tables 라는 에러가 발생하였다.

그동안 문제없이 잘 돌아가는 스크립트가 왜 지금 에러가 발생했을까 라는 의문도 들고..

중간에 작업한 적이 없는 것 같은데 ...

하지만 컴퓨터는 거짓말을 하지 않기에... 일단은 백업이 우선이라 해결을 진행해 보았다.

MyISAM 이기에 배치 작업이 있나 확인을 해 보았다. (조회 또한 table lock 을 잡기에....이건 순수히 나만의 생각)

show processlist;

하지만 확인 결과 어떠한 작업도 확인이 되지 않았다.

그렇다면 희박하지만 권한을 부여해 보았다. (권한을 revoke 한 적은 전혀 없기에...)

이 후 다시 진행해 보았다.

말도 안되....정상적으로 진행이 되었다.

하아....누가??아니면 무슨 이유로...??

원인을 찾아 봐야겠다.

[원본] http://randa.tistory.com/

MySQL에서 비교연산자는 1(TRUE), 0(FALSE), NULL값 또는 연산에 대한 결과를 리턴한다. 이 포스팅에서는 비교연산자의 설명 및 예제를 설명하도록 한다.

1. BETWEEN min AND max

해당 연산자는 AND 좌우의 값을 포함한 범위안에 값이 있는지를 체크한다. 이는 수식으로 "expr >= ... AND expr <= ..." 동일한 결과를 나타낸다.

1line - BETWEEN은 반드시 범위 값 중 작은게 왼쪽 큰게 오른쪽에 있어야 한다. 따라서 2는 1~3범위 안에 있음으로 

    2 BETWEEN 1 AND 3은 1을 리턴하고, 2 BETWEEN 3 and 1은 비교대상 중 우측이 더 작음으로 0을 리턴한다.

2line - 1은 2~3 범위안에 없기 때문에 0을 리턴한다.

3line - a~c중에는 b가 있기때문에 1이 리턴된다. BETWEEN은 숫자 뿐만 아니라 문자도 비교 가능한 것을 알 수 있다.

4line - 범위 중 3을 '' 로 묶어 문자열로 치환하였으나, 숫자만이 존재 할 경우 내부적으로 숫자로 취급한다. 따라서 2~3중에는 

          2가 포함되어 있기에 1을 리턴한다.

5line - 숫자와 문자열의 범위를 주었다. 이 경우 정상적으로 값을 취할 수 없기 때문에 0을 리턴한다. 5line의 예제는 비교연산

          을 위해서는 동일한 형태의 데이터 타입끼리 비교를 해야 정상적인 결과를 줌을 알 수 있다.

2. COALESCE(value1,value2 ...)

COALESCE는 범위안에 값 중 NULL이 아닌 첫번째 인수를 리턴한다. 만약 모든 값 이 NULL일 경우는 NULL을 리턴한다.

1line - 좌측부터 시작해서 첫번째로 NULL이 아닌 것은 1이기 때문에 1을 리턴한다.

2line - value 값이 전부 NULL로만 이루어져 있기 때문에 NULL을 리턴한다.

3. GREATEST(value1,value2 ...)

GREATEST는 범위안에 값 중 가장 큰 인수를 리턴한다.

1line - 2, 0중 가장 큰 수인 2를 리턴한다.

2line - 가장 큰 수치인 767.99를 리턴한다.

3line - a, b, c중 가장 뒤인 c를 리턴한다.

4line - 값 중 NULL이 포함될 경우 NULL을 리턴한다.

4. expr [NOT] IN (value1,value2 ...)

IN 구문은 expr에 해당하는 값이 ( ) 안에 존재할 경우 1(TRUE)을 리턴한다. NOT이 붙을 경우 반대로 해당하는 값이 없어야 1을 리턴한다.

1line - 0, 3, 5, 7 중 2가 존재하지 않기때문에 0을 리턴한다.

2line - 'wefwf'는 ( ) 안에 존재하기 때문에 1을 리턴한다.

3line - 1line 결과와 반대로 1을 리턴한다.

4line - 2line 결과와 반대로 0을 리턴한다.

IN절안에는 서로 다른 데이터 타입을 쓰지않는 것이 좋다. 이는 MySQL 내부적으로 비교하는 기준이 다를 수 있기 때문이다. 따라서 만약 숫자와 문자열을 혼합한다면 

5. INTERVAL(N,N1,N2,N3,...)

INTERVAL은 값 N과 그 뒤의 값을 비교하여 결과를 리턴한다. 최소 1개 이상의 N(num) 값이 있어야 한다.

 * N < N1 일 경우 뒤 N과 관계없이 무조건 0을 리턴한다.

 * N1 < N2 < N3 ... 와 같이 N(num)은 뒤로갈 수록 값이 커야 한다. 내부적으로 Binary 형태로 결과를 구하기 때문이 고 만약 

   그렇지 않을 경우 원하는 결과가 나오지 않을 수 있다.

 * N이 NULL 일 경우는 -1을 리턴한다.

 * N > N(num)이 될때까지의 N(num)에 대한 개수를 구한다. 왼쪽부터 읽는다.

1line - N(23)보다 큰 N(num)은 30이다. 그럼 그 사이의 1, 15, 17의 개수 3. 따라서 3을 리턴한다.

2line - N(10)보다 큰 N(num)은 100이다. INTERVAL에서 >= 는 조건에 포함되지 않는다. 따라서 1, 10 2개. 2를 리턴한다.

3line - N(22) < N1(23)이다. 따라서 0을 리턴한다.

4line - N이 NULL값이기 때문에 -1을 리턴한다.

5line - N(100)보다 큰 N(num)은 1, 10, 100, 10 이 있다. N < N(num)이 만족한 1000에서 종료되기 때문에 뒤에 10은 무시된다. 

          따라서 4를 리턴한다.

6. IS [NOT] NULL

값이 NULL인가를 비교하여 값을 리턴한다. IS NULL의 경우 NULL인 경우 1 NULL이 아닌경우 0을 리턴한다. 반대로 NOT이 붙으면 부정의 의미가 되어 NULL이 아닌경우 1, NULL인 경우 1을 리턴한다.

1line - NULL(공백은 NULL이 아니다)은 NULL로 명시할 경우에만 존재함으로 0 / 0 / 1을 리턴한다.

2line - NULL(공백은 NULL이 아니다)인 경우에만 0을 리턴함으로 1 / 1 / 0을 리턴한다.

7.  IS [NOT] boolean_value

불리안 값에 대응해서 값을 테스트 하는데, 여기에서 boolean_value 은 TRUE, FALSE, or UNKNOWN이 될 수 있다. 

IS NOT boolean_value 신텍스는 MySQL 5.0.2에서 추가 되었다.

1line - 1, 0, NULL은 각각 TRUE/FALSE/UNKNOWN과 대응됨으로 전부 1을 리턴한다.

2line - 1, 0은 각각 TRUE/FALSE에 대응함으로 참이다. NULL은 UNKNOWN인데 NOT을 붙여 부정했기에 거짓이 된다. 따라서 

          1/1/0을 리턴한다.

8. ISNULL(expr)

IS NULL과 비슷한 역활을 한다. expr에 대한 결과가 NULL인지 아닌가에 대하여 확인하여 NULL일 경우 1, 아닐경우 0을 리턴한다.

1line - 1+1의 결과는 2다. NULL이 아님으로 0을 리턴한다.

2line - 1을 0으로 나눈다는 것은 불가능하기 때문에 NULL이다. 따라서 1을 리턴한다.

9. LEAST(value1,value2,...)

LEAST는 값 중 가장 작은 인수를 리턴한다. 인수들은 아래의 규칙에 따라서 비교가 된다

 * 만일 리턴 값이 INTEGER 문맥에서 사용되거나 또는 모든 인수가 정수 값이라면, 그 인수들은 정수로 비교가 된다.

 * 만일 리턴 값이 REAL 문맥에서 사용되거나 또는 모든 인수가 실수 값 (real value)이라면, 인수들은 실수(real)로 비교된다.

 * 만일 어떤 인수가 문자 크기에 민감한 스트링이라면, 그 인수는 문자 크기를 구분하는 스트링으로 비교가 된다.

 * 이외의 모든 경우에는, 인수들은 문자 크기와는 상관없는 스트링으로 비교가 된다.

MySQL 5.0.13 이전의 경우, LEAST() 은 모든 인수가 NULL 일 경우에만 NULL을 리턴했다. 하지만 5.0.13 이후에는, 어떤 인수라도 NULL 포함되어 있다면 NULL을 리턴한다.

1line - 0이 가장 작음으로 0을 리턴한다.

2line - 3.0이 가장 작음으로 3.0을 리턴한다.

3line - A가 가장 앞선 문자열임으로 A를 리턴한다.

10. value1 = value2

양쪽의 값을 경우 1 틀릴경우 0을 리턴한다. 단 하나의 값이라도 NULL이 포함되어지면 결과는 NULL을 리턴하게된다.

1line - 1은 0이 아님으로 0을 리턴한다.

2line - 0은 0임으로 1을 리턴한다

3line - NULL이 포함되었음으로 NULL을 리턴한다.

11. value1 <=> value2

<=>는 같은 값을 비교하는 =과 내용은 근본적으로 동일하다. 하지만 차이점은 NULL 하나의 값도 값으로 표시한다는게 다르다.

1line - 1, 1, 0. NULL 값도 하나의 문자로 취급 하기 때문에 NULL을 리턴하지 않는다.

2line - 1, NULL, NULL. 하나라도 비교 대상에 NULL이 포함되면 연산불가로 판정하고 NULL 값을 리턴한다.

12. value1 <> value2, 또는 value1 != value2

=와 반대이다. 일치하지 않을 경우 1을 리턴한다.

1line - .01은 0.01과 같음으로 0을 리턴할 것 같지만 둘다 ' 으로 묶어 문자형 만들었다. 따라서 문자 그대로 비교하여 틀림으로 

           값은 1

2line - .01은 0.01과 같음으로 0을 리턴한다. 숫자의 경우 ' 으로 묶지 않으면 앞의 값이 생략된 경우 0을 추가한다. 

3line - zapp와 zappp는 다름으로 1을 리턴한다.

13. value1 > value2

값 1이 값 2보다 클 경우 1을 리턴한다.

1line - 2가 1보다 큼으로 1을 리턴한다.

14. value1 >= value2

값 1이 값 2보다 크거나 같을 경우 1을 리턴한다. >=의 순서 바꾸면 안된다.

1line - 2보다 크거나 같을 경우에 참인데 2와 2는 같음으로 1을 리턴한다.

15. value1 < value2

값 1이 값 2보다 작을 경우 1을 리턴한다.

1line - 2는 1보다 작음으로 1을 리턴한다.

12. value1 <= value2

값 1이 값 2보다 작거나 같을 경우 1을 리턴한다. <=의 순서 바꾸면 안된다.

1line - 2보다 작거나 같을 경우에 참인데 2와 2는 같음으로 1을 리턴한다.

[원본] http://blog.syszone.co.kr/3333

때로 MySQL 5.1 데이터베이스는 몇가지 관리 작업을 수행해야 한다. 내 경우를 예로 들면, 운영서버에서 겨우 400k 행을 가진 두 개의 테이블을 조인하는 간단한 쿼리문을 실행하는데 너무 오래 걸리는 경우가 있었다. 실제로 이 쿼리문이 실행되는데 약 30초에서 100초 가량이 걸렸다. 테스트와 검수작업 후에 58ms로 처리 시간을 줄였다. 쿼리문의 컬럼은 이미 인덱스가 생성되어 있었기 때문이다. 이 정도 처리속도라면 다행히도 사용자들이 불편해 하는 정도는 아니였지만, 나는 이정도 처리 속도도 괜히 성가시게 느껴졌다. 해결책은 간단했는데, 몇가지 명령문을 실행해서 정리 작업을 하면 되었기 때문이다. 이 정리작업을 하고 나니, 동일한 쿼리문을 운영서버에서 실행하는데 겨우 4.8ms 밖에 걸리지 않게 되었다. 정말이지 만족스러운 결과였다.

내가 한 일들

• Backup - 데이터베이스 백업하기
• Check - 데이터베이스 체크하기
• Optimize - 데이터베이스 최적화하기
• Analyze - 데이터베이스 분석하기

각 수행 단계들에 대한 상세 설명

1. 먼저 mysqldump 명령문을 이용해서 데이터베이스를 백업한다.

프로시져 또는 함수가 있다면 반드시 -routines 인자를 사용해야 한다. 또한 트리거를 사용한다면 반드시 -triggers 인자를 사용해야 한다.

여기에 더해서 다른 시스템에서 백업한 데이터베이스를 실제로 재생성하여 백업이 정확한지 확인해야 한다.

만약 데이터베이스가 너무 커다면 이처럼 데이터베이스 전체를 백업하는 일은 사실 불가능하다. 하지만 데이터베이스가 너무 크다면, 이미 복제 서버를 구축해서 백업 시스템을 활용중일 것이다.

2 Check

테이블 무결성을 검사한다.

http://dev.mysql.com/doc/refman/5.1/en/check-table.html

단일 테이블에 대해 검사를 하려면:

콘솔에서 데이터베이스의 전체 테이블을 검사하려면 :

테이블 무결성 검사는 정기적으로 하는 것이 좋다.

3. Optimize

단편화 제거 작업(defrag operation)과 같이, optimize table 명령문을 사용하면 사용하지 않은 공간을 회수할 수 있다. MyISAM 엔진에서는 optimize 명령문은 말그대로 단편화 제거 작업만을 수행한다. 반면 InnoDB 엔진의 경우 내부적으로 ALTER TABLE문을 실행하여, MySQL 서버에 대해 테이블과 인덱스를 재생성하도록 요청한다.

http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

단일 테이블에 대해 optimize를 하려면:

콘솔에서 데이터베이스의 전체 테이블을 optimize를 하려면:

만약 InnoDB라면 결과에 "Table does not support optimize, doing recreate + analyze instead” 메시지가 나온다.

4. Analyze

analyze를 실행하면 인덱스를 재생성하여 성능을 최적화하는데, 키를 재분배하기 때문이다. 만약 인덱스가 제대로 생성되어 있음에도 slow query가 발생한다면, analyze를 실행하는 것을 고려해 볼만하다. analyze를 실행하면 read 락이 걸린다. 만약 모든 테이블이 InnoDB 엔진을 사용한다면, optimize 과정에 이미 analyze가 포함되어 있다.

http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

단일 테이블에 대해 analyze를 하려면:

콘솔에서 데이터베이스의 전체 테이블을 analyze를 하려면:

InnoDB에서 analyze를 하는 경우, 몇가지 특이점이 있다. 특히 analyzer가 취하는 샘플의 갯수가 다를 수 있다는 점이다(샘플의 갯수는 innodb_stats_sample_pages 옵션으로 설정할 수 있다). 기본값이 매우 작기 때문에, analyze를 여러번 시도하게 될 때, 그때마다 결과가 달라질 수도 있다.

더 자세한 내용은 아래를 참조하라.

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

--너무 성의 없어 보이는군....하지만 탭으로 나누어서 데이터가 export 된 것을 확인 가능하다.

2. Mysqldump 를 이용하여 데이터 export

-- 캡쳐는 회사 디비 내용을 가지고 테스트 하느라 비공개....

Import는 다시 진행 후 업로드 해야겠다.

또 한번의 삽질 추가.

임시 DB를 설치 한 이후 외부에서 접속을 진행 하였더니,

접속이 안되어서 확인.

처음에는 쉽게 port로 생각하여 Open 하였지만 연결 실패..

유저도 제대로 생각했다고 했지만 여기서 함정..

역시...난 멍청한 것인가...

1. Linux Port Open

하지만...failed...

2. User 생성 확인

확인을 했더니 IP가 제대로 입력이 안되었음...OTL...단순히 local host로만 생성을 한 것임...

아래와 같이 추가로 생성하여 해결

참조 : http://imdsoho.tistory.com/entry/mysql-%EC%82%AC%EC%9A%A9%EC%9E%90-%EC%B6%94%EA%B0%80-%EB%B0%8F-error-1130-%EC%B2%98%EB%A6%AC

OS 재기동이 필요한 시점에 문제가 발생할 소지가 있기 때문에

이럴 때를 대비해서 백업용 DB가 필요하다.

문서로 정리 하였지만, 

작성한 것을 복-붙 이다.

임시 MySQL DB 구축

1. zip 파일을 임시 DB에 복사 및 압축해제

ex) tar -xvzf 파일명

2. 압축 해제한 mysql 서버 폴더에 install.sh 파일을 생성 및 아래내용 복사 (make 파일생성)

참고로 본 서버 참고하여 생성

cf) localstatedir 의 경우 기본적으로 설치되는 경로이기에, 경로가 변경된다면 해당 경로 수정

   혹여나 변경을 못했다면 my.cnf 에서 수정 가능

3. .bashsrc 수정

vi ~/.bashrc 한 후 아래 alias 추가

. ~/.bashrc 또는 source ~/.bashrc 로 설정을 다시 로딩

4. .bash_profile 수정

/usr/local/mysql/bin 를 path에 추가 (vi ~/.bash_profile)

. ~/.bash_profile 또는 source ~/.bash_profile 로 설정을 다시 로딩

5. install.sh 파일 실행

$root > sh install.sh

오류 발생 시 하단에 오류 발생 찾아서 확인

6. 설치가 완료 되면 my.cnf 생성

root$> vi /etc/my.cnf

7. Python 설치 (MySQL 에서 사용하기 위한 라이브러리 설치

   - README 보면서 진행)

8. DB 설치

필히 설치할 필요 없으나, 아래 폴더에서 설치가 가능

/usr/local/mysql/var 에 설치가 되었는지 확인이 가능

9. 해당 데이터 및 권한 가져오기

- 데이터는 일간 백업에서 가져오면 가능(FTP 서버 내 Daily_BK) -> 데이터

 - $ tar -cvzf mysql_back1.tgz /usr/local/mysql/var/mysql  -> 권한

   해당 압축 파일을 FTP서버로 전송 후 다시 임시 DB로 전송

10. 임시 DB에서 권한 및 데이터 복구

- 권한의 경우 기존 mysql 해 놓은곳에 덮어 쓸것 (기존은 백업)

- 일간 백업한 것은 DARKEDEN 에 생성 (권한 확인 / 소유자 확인)

- 각각의 폴더의 유저 소유 권한을 확인(/usr/local/mysql/var 의 경우 아래로 mysql으로 해야함)

11. MySQL open

 - $>safe_mysqld&  로 실행 후 $>ps -ef | grep mysql 확인

오류 발생 시

1. sh install.sh 를 돌렸는데 발생하는 오류

해결방법

참고 : http://faq.hostway.co.kr/Linux_DB/1303

MYSQL 을 linux 상에서 접속을 할줄 몰라 열심히 삽질 하다가 겨우 찾아냈다.

사실 말하면 그동안 동료가 알려줬지만 제대로 못 외워서 삽질한게 더 맞는 것 같다.

역시 삽질하면서 배워야지 자기 것이 되는게 맞는 말인듯....

일반적으로 mysql 접속은 아래와 같다.

-u 뒤에는 유저명

-p 뒤에는 패스워드를 작성하면 된다.

하지만 나는 진행할려고 할때마다 아래와 같은 에러가 발생했다.

왜이리 접속이 안되서 열심히 찾아보이 socket 을 이용해서 접속하는 것이기에,

해당 socket파일에 명시가 되어 있지 않다고 되어 있다.

Oracle 과 비교하면 리스너와 같은것으로 보면 될까?? 리스너 포트가 변경되었을 때

리스너 포트를 명시하는 경우다.

아래와 같이 진행하면 된다.

해당 개발DB의 경우 cnf 파일에 명시되어 있는데 cnf 파일을 보면 아래와 같다

그래서 아래와 같이 시도를 하면 접속이 된다.

참고로 -p 뒤에 비밀번호를 붙이지 않으면 비밀번호를 묻게 된다.

Oracle 하다가 Mysql 를 하려고 하니 참 낯설다-ㅋㅋㅋ

어떤 DBMS이든지 마찬가지겠지만, DBMS를 효율적으로 적은 리소스로 가장 좋은 성능을 내게끔 사용하기 위해서 가장 먼저 해야 하는 일은 어떠한 구조로 만들어 졌는지 확인하는 것이다. DBMS의 특성을 확인하여, 그 DBMS의 장점과 단점을 파악하고, 내가 원하는 서비스의 성격에 맞게 DBMS를 선택하여 사용하면, 훨씬 더 효율적으로 데이터를 관리 할 수 있다. 

MySQL도 DBMS로서 다른 DBMS와 비교되는 구조와 특성을 가지고 있다. 이러한 특성을 미리 나열하기 전에 MySQL의 내부 구조에 대해 먼저 알아보자. 

[그림 1-1]

[그림 1-1]은 MySQL Conference에서 MySQL 내부 구조 설명시 MySQLAB에서 사용했던 그림이다. 여기에서 알수 있는 것은 MySQL Server의 내부의 모듈들은 컴포넌트 형태로 되어있어서 여러 기능들이 퍼즐 형식으로 엮여져 있다는 것이다. 

퍼즐 형태로 만들어진 내부 모듈들로 인해 내부적으로 다른 스토리지 엔진을 추가하거나, 기능을 추가할때, 다른 모듈에 크게 영향을 주거나 하는 일이 적은 것을 확인할 수 있다. 

맨 위의 오른쪽에 표시된 SQL Control interface는 MySQL Client와 MySQL Server 사이에 존재하는 Interface로서 백업을 진행할 때나, 상태 정보를 보고 싶을때와 같은 경우에 컨트롤 하는 문장들을 의미한다.