개발로그 - DevLog

# 기본 개념은
 1. FTP 의 계정을 실계정이 아닌 mysql 의 데이터 형태의 가상계정으로 처리한다.
 2. proftpd.conf 에 mysql 관련 설정한다.(mysql connection 정보, 계정테이블 정보)
 3. 계정별 Quote 도 설정 가능하다.

# 대략적인 과정을 보면
 1. mysql 컨넥션(DB)을 FTP 용으로 만든다.
 2. FTP 용 DB 에 계정정보 테이블을 만들고, 사용자 정보를 입력한다.
 3. proftpd 의 mysql 설정을 추가한다.
 4. 입력한 사용자 정보로 FTP 에 접속한다.

# proftpd 만 되는가?
 pure-ftpd 라는 FTP 서버도 지원하는 듯 싶다.(확인필요)

# mysql 만 되는가?
 비슷하게 ldap , pgsql(postgresql) 도 지원하는 듯 하다.(확인필요)

# 관련문서 : proftpd + mysql 설정
 * http://jeonbuk.lug.or.kr/bbs/view.php?&bbs_id=doc&page=&doc_num=21
 * http://jinsnet.com/12
 * http://www.howtoforge.com/proftpd_mysql_virtual_hosting
  * http://www.debuntu.org/how-to-ftp-virtual-host-with-proftpd-mysql
 * http://www.khoosys.net/single.htm?ipg=848

# 따로 정리할 필요까지는 없을 듯(특이사항에 대한 포스팅을 할지도...)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

예전 업그레이드 관련글에 포함할까 하다가 따로 빼기로 했다.
ubuntu 를 기준으로 설명.

간단한 업그레이드 절차를 살펴보면.
1. 업그레이드 전 데이터 덤프 ( mysqldump --all-databases > db_dump.sql )
2. mysql 서버 업그레이드 ( apt-get install mysql-server-4.1 )

이렇게 업그레이드 후에 오류가 발생해서 실행이 안되는 경우가 있다.

1. /var/log/syslog 를 살펴보면 아래 과 같은 에러메세지가 나올때

mysqld : Character set 'euc_kr' is not a compiled character set is not specified in the ..  .. index.xml

 * 기존 /etc/mysql/my.cnf 의 euc_kr 으로 된 부분을 euckr 로 변경
 * 4.0.x 에서는 euc_kr 을 썼는데,  4.1.x 버전 이상은 euckr 으로 쓴다.
 * my.cnf 에서 바꾸어 주기만 하면 된다.(euc_kr -> euckr)
 * 다시 mysql 서버 시작 하면 정상작동.

2. 다시 로그를 살펴보면. multi-byte
 실행은 되었는데, [Warining] 이 무지 많이 보일 경우가 있을 것이다.

{db_name}  had no  or invalid character set, and default character set is multi-byte, so character column sizes may have changed

라는 메세지를 보고, 다음과 같이

alter table {table_name} convert to character set utf8

처럼 복구하면 한글 같은 2byte character 들의 사이즈가 반으로 줄어버리게 된다.
(게다가 저렇게 복구하려면 힘들다. 테이블이 한두개라면 모를까)

이때는 업그레이드 전에 덤프한 데이터를 다시 복구(restore) 하면 된다.(복구하면서 알아서 charset 을 설정)

3. 복구후에 DB를 덤프할때 '화일을 찾지 못했습니다' 에러를 만난다면

mysqldump: Got error: 1017: 화일을 찾지 못했습니다. './{db_name}/{table_name}.frm' (에러번호: 24) when using LOCK TABLES

또는 영어로  Can't find file  , errno: 24

개별적 해당 테이블을 덤프하면 이상이 없다.

이 메세지가 나오는 이유는  이 숫자때문이다.  바로 '1024'
아! 감이 왔나?  mysql 이 한번에 열수 있는 파일이 제한되어 있다.
해당 옵션은  show variables like '%open%' 으로 확인할 수 있다.
바로 open_files_limit  가 1024 로 되어 있을 것이다.

/etc/my.cnf 의 [mysqld] 탭에  open_files_limit=2048  등으로 설정한다.

그런데, 4.0 에서는 이상없이 dump 가 가능하다.
4.0 과 4.1 의 mysqldump 의 내부적으로 다르게 처리하는 듯 하다.
이유를 살펴보니, --lock-tables 라는 옵션때문이다.
4.1 에서 --lock-tables=false 로 옵션을 주고 dump 하면 위의 문제는 없어진다.
lock-tables 옵션을 사용하려면 위의 open_files_limit 를 설정해주어야 한다.(선택은 각자 알아서)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

mysqldump --all-databases > db.sql  의 방식으로 전체 database 를 덤프받는다.
옵션에 그런 것이 있는지 모르겠지만,  각 database 별로 개별 파일로 다운받기 위한 스크립트를 만들어 본다.

개별 db 파일로 다운받는 옵션이 이미 있다면 뻘쭘.( 이런 스크립트는 이미 많이 있는듯)

# 초간단 dump 스크립트

#!/bin/sh

db_user='' ;
db_pass='' ;

db_list=`mysql -Bse 'show databases' -u $db_user -p$db_pass`;

NOW=`date +"%Y-%m-%d"`

for db_name in $db_list
do
    [ ! -d $NOW ] && mkdir -p $NOW || :
    mysqldump $db_name -u $db_user -p$db_pass --add-drop-table > $NOW/$db_name.sql

done

이상 간단한 스크립트.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

예전 4.0.x 에서 4.1.x 로 업그레이드 는 조금 복잡하고 주의가 필요했다.(1,2,3)
4.1.x 에서 5.0.x 로의 업그레이드는 그에 비해 간단한 편이다.

우분투에서는

apt-get install mysql-server-5.0

으로 업그레이드 후에 (이때 기존 4.1 버전은 apt-get 이 자동으로 제거)

mysql_fix_privilege_tables

만 해주면 OK

너무 간단한가?

성능향상이 있다고 하는데, 아직은 모르겠다.
4.0.x 에서 5.0.x 로 바로 업그레이드는 어떤지 테스트 해봐야 겠다.
보통 4.0.x -> 4.1.x -> 5.0.x 의 순서를 권장하던데, 상황봐서 진행
현재 4.0.x 를 쓰고 있는 마지막 서버도 마저 업그레이드 예정.


# 메뉴얼
- http://www.mysqlkorea.co.kr/sub.html?mcode=develop&scode=01&m_no=20034&cat1=2&cat2=35&cat3=82

# 참조
- http://www.mysqlkorea.co.kr/gnuboard4/bbs/board.php?bo_table=community_03&wr_id=164
- http://database.sarang.net/?inc=read&aid=23327&criteria=mysql&subcrit=columns
- 성능향상 : http://www.mysqlkorea.co.kr/gnuboard4/bbs/board.php?bo_table=community_06&wr_id=391
- 5.0 새버전 : http://jay2.tistory.com/198

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

http://blogs.mysql.com/kaj/2008/01/
 * http://blogs.mysql.com/kaj/2008/01/16/sun-acquires-mysql

썬이 mysql 을 인수했네요.
음 2008년의 첫 M&A 건이되나요?

과연 mysql 은 어떻게 될지 궁금해지네요.
무료버전이 사라지거나 하는 급격한 변화는 없을 듯 하지만,
새 버전에서 변화가 있을 수도 있을 듯 하네요.( 예를 들어 mysql 6 버전등)
(지금도 mysql 버전의 라이선스가 좀 복잡(?)한 면이 있지만, 더 복잡해 지면 안되는데 ^^)

썬이 DB 회사가 아닌 만큼 직접적인 영향을 주진 않겠죠.
(오라클에 인수되었다면 큰 변화가 있을 수 있었겠지만..)

사실 요즘 mysql 을 벗어서 pgsql 이나 큐브리드 같은 것을 시도해 보려고 했는데,
유료화 같은 변화가 아닌 새로운 변화가 mysql 에 오면 좋겠네요.

자바쪽의 mysql 지원이 좋아지려나? ^^

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

저번에도 비슷한 문제가 있었다.

이번에는 정전으로 인해서 NFS 연결이 비정상적으로 끊겨 mysqld 서버를 시작하지 못했다.
개발서버 용으로 사용하고 있는데,

실서버 --> rsync 로 데이터 백업(백업서버,NFS서버)  --> NFS 로 mysql 데이터 연결(개발서버)

개발서버로 직접 mysql 데이터를 백업받아서 하면 별 문제 없겠지만,
백업서버에 일괄적으로 백업받기 위해 NFS 로 연결해서 사용하고 있다.

이번의 에러메세지는 지난번과 비슷한데

InnoDB: Unable to lock ./ibdata1, error: 11

대신에

InnoDB: Unable to lock ./ibdata1, error: 13

으로 차이가 있었다.

관련 검색을 해보니,
 - http://bugs.mysql.com/bug.php?id=14281
 - http://bugs.mysql.com/bug.php?id=22667
의 글을 찾을 수 있었다. 

위 글의 요지는 mysql 데이터를 NFS 로 연결해서 사용하는 것은 '좋은 생각이 아니다'  라는 것.

이번에 원인을 해결하기 위해 이리저리 설정을 바꿔보고 하던중.
InnoDB 에 대해서 그런 문제가 발생하는 듯 싶다.(MyISAM 은 괜찮은 듯)

그리고, NFS 서버가  nfs-kernel-server 와 nfs-user-server  가 있다.
여기에서 nfs-user-server 로 하면 실행이 되지 않았다.
그런데, nfs-kernel-server 로는 실행의 문제점은 없었다.(우분투 기준)

원래 nfs-kernel-server 였는데 바꿔본다고 nfs-user-server 로 바꾼후 위와 같은 에러가 발생한듯 하다.
바꾼이유가 NFS 서버가 마운트는 되는데, 데이터를 열지 못하는 문제가 있었다.
그 문제는 어이없게도 정전이 되면서 허브에 문제가 발생했던것(허브 restart 로 해결. 허브를 좋은 것으로 바꿔야 겠다.)

즉, mysql 데이터를 NFS 로 연결시 InnoDB 를 쓴다면, nfs-kernel-server 로 NFS 서버를 구동한다.
(nfs-user-server 로 안되는 것을 확인했지만, 좀더 살펴볼 예정)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

mysql 의 데이터를 NFS 로 연결해서 쓰는 분들이 있을 것이다.

구성은 이렇게

mysql 서버  --> NFS 서버(mysql 데이터)

이렇게 쓰는 경우는 별로 없으려나? 아무튼 나의 개발환경은 저렇게 구성되어 있다.
테이블 형식은 InnoDB 를 쓰고 있다.(MyISAM 형식도 같은 문제가 발생하는지는 확인하지 않았다.)

개발서버가 실수로 인해 꺼져버렸다.(Ctrl-Alt-BS ㅜㅜ , DontZap 옵션을 잊었다)

혹시나 해서 DontZap 옵션은 이렇게

그냥 대수롭지 않게 다시 켰다.
그런데, mysql 서버가 정상실행이 되지 않았다.
로그메시지를 보니(/var/log/syslog) 잔뜩 에러메시지가 기록되어 있었다.

NFS 서버가 연결된 방식이 아니라도 마찬가지인지 확인을 해보지는 않았다.

syslog 에 기록된 메세지는 이렇다.

 mysqld[6607]: InnoDB: Check that you do not already have another mysqld process
 mysqld[6607]: InnoDB: using the same InnoDB data or log files.
 mysqld[6607]: InnoDB: Unable to lock ./ibdata1, error: 11
 mysqld[6607]: InnoDB: Check that you do not already have another mysqld process
 mysqld[6607]: InnoDB: using the same InnoDB data or log files.
 mysqld[6607]: InnoDB: Unable to lock ./ibdata1, error: 11
 mysqld[6607]: InnoDB: Check that you do not already have another mysqld process
 mysqld[6607]: InnoDB: using the same InnoDB data or log files.
 mysqld[6607]: 071121 17:12:51  InnoDB: Unable to open the first data file
 mysqld[6607]: InnoDB: Error in opening ./ibdata1
 mysqld[6607]: 071121 17:12:51  InnoDB: Operating system error number 11 in a file operation.
 mysqld[6607]: InnoDB: Error number 11 means 'Resource temporarily unavailable'.
 mysqld[6607]: InnoDB: Some operating system error numbers are described at
 mysqld[6607]: InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html
 mysqld[6607]: InnoDB: Could not open or create data files.
 mysqld[6607]: InnoDB: If you tried to add new data files, and it failed here,
 mysqld[6607]: InnoDB: you should now edit innodb_data_file_path in my.cnf back
 mysqld[6607]: InnoDB: to what it was, and remove the new ibdata files InnoDB created
 mysqld[6607]: InnoDB: in this failed attempt. InnoDB only wrote those files full of
 mysqld[6607]: InnoDB: zeros, but did not yet use them in any way. But be careful: do not
 mysqld[6607]: InnoDB: remove old data files which contain your precious data!
 mysqld[6607]: 071121 17:12:51 [ERROR] Can't init databases
 mysqld[6607]: 071121 17:12:51 [ERROR] Aborting

핵심은 바로 이 메시지

 mysqld[6607]: InnoDB: Unable to lock ./ibdata1, error: 11
 mysqld[6607]: InnoDB: Check that you do not already have another mysqld process
 mysqld[6607]: InnoDB: using the same InnoDB data or log files.

이상하다. mysqld 가 떠 있지는 않는데.
NFS 의 버그인지, 그냥 정상적인 것인지는 모르겠다.
그렇게 mysql 서버가 crash 된 후에 다시 켜면, NFS 서버쪽의 파일이 lock 된 상태로 있는 것이었다.

NFS 서버를 다시 재 실행 해주니 정상적으로 mysqld 가 실행되었다.

syslog 를 보면 다음처럼 복구한다는 메시지도 볼 수 있다.

 mysqld[7030]: 071121 17:42:10  InnoDB: Database was not shut down normally!
 mysqld[7030]: InnoDB: Starting crash recovery.
 mysqld[7030]: InnoDB: Reading tablespace information from the .ibd files...
 mysqld[7030]: InnoDB: Restoring possible half-written data pages from the doublewrite
 mysqld[7030]: InnoDB: buffer...
 mysqld[7030]: 071121 17:42:15  InnoDB: Starting log scan based on checkpoint at
 mysqld[7030]: InnoDB: log sequence number 0 123193550.
 mysqld[7030]: InnoDB: Doing recovery: scanned up to log sequence number 0 123193550
 mysqld[7030]: InnoDB: Last MySQL binlog file position 0 79, file name /var/log/mysql/mysql-bin.001118
 mysqld[7030]: 071121 17:42:15  InnoDB: Flushing modified pages from the buffer pool...
 mysqld[7030]: 071121 17:42:16  InnoDB: Started; log sequence number 0 123193550
 mysqld[7030]: /usr/sbin/mysqld: ........ Source distribution
 /etc/mysql/debian-start[7063]: Checking for crashed MySQL tables.

실제 데이터도 이상이 없는지도 확인해봐야 겠다.(mysql 쪽에서 복구를 잘 해주려나?)


ps. 처음에는 안되서, 처음부터 초기세팅하고 받아놓은 dump 로 복구할까도 생각했다.
 비교적 간단하게 해결했다(데이터에 이상이 있는지는 확인이 필요하다)
 DB 에서 신뢰하는 것은 오직 dump 뿐.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

mysql 에서 로그를 남겨두는데,
용량을 많이 차지해서 일정기간이 지난후에 자동으로 삭제하도록 구성해 두었습니다.

기존에는 unix shell(bash) 코드를 이용해 처리했는데, 플랫폼(?) 호환성을 높이기 위해
sql query 로 바꿔보았습니다.

주절주절이야기 했지만, 코드는 간단합니다.

날짜를 구하는 것이 핵심(shell 이냐, sql 이냐 의 차이)

LOGDATE=`/bin/date +%Y/%m/%d --date '1 month ago'`
mysql log_db -e "delete from log where log_date < '$LOGDATE';"

이런 간단한 코드입니다.(bash)

mysql 의 쿼리로만 작성하면 다음과 같습니다.

mysql log_db -e "delete from log where log_date < DATE_FORMAT( CURDATE() + INTERVAL -1 MONTH , '%Y/%m/%d' ) ; "

DATE_FORMAT( CURDATE() + INTERVAL -1 MONTH , '%Y/%m/%d' )

이 부분이 날짜를 구하는 부분입니다. ( MONTH 대신에 DAY 등.. )


워낙 방법이 많아서, 그 중 하나의 방법이다. 라는 의미에서 남깁니다.

1년전, 하루전 등의 옵션은 아래 참조 링크에서 포맷옵션을 바꾸면 됩니다.

# 참조 : http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

mysql 의 데이터를 백업받는 방법은 여러가지가 있다.

# mysql 에서 제공하는 mysqldump 를 이용하는 방법
phpMyAdmin 같은 툴을 이용하는 방법
mysql console 에서 INTO OUTFILE 등으로 백업하는 방법

# mysqldump 같은 명령도 php 내의 system 함수등을 이용해 할 수도 있다.
또한 INTO OUTFILE  mysql console 명령도 php 에서 쿼리로 날려서 할 수도 있다.
( http://www.php-mysql-tutorial.com/perform-mysql-backup-php.php )

# 여기에서 언급하고자 하는 것은
PHP 에서 mysql_query 명령으로  INTO OUTFILE 쿼리를 이용해 백업받는 부분이다.

이 방법을 사용할 때 주의할 부분을 정리하고자 한다.

1. 먼저 mysql console 접속할 USER 의 File_Priv 권한이 'Y' 이어야 한다.
  update user set File_Priv = 'Y' where User = '{유저이름}' ;  등으로 바꿔준다.
  설정을 바꾼 후 바로 적용이 되지 않고, # mysqladmin reload  등으로 설정을 적용시킨다.

2. 파일이 저장되는 경로 및 퍼미션(권한)을 확인한다. 확실한 방법은 절대경로를 써준다.
 예) /data/backup/xxxx.sql
 %주의% 절대경로를 써주지 않으면, mysql 데이터가 있는 경로에 저장이 된다.( /var/lib/mysql/{DB명} )
권한(퍼미션) 부분은 파일이 mysql 의 user/group 으로 저장이 되기 때문에
보통(nobody , www-data) 의 권한으로 된 경로는 other 권한에 쓰기 권한이 있어야 한다.
안되어 있다면, # chmod 777 {경로}  처럼 바꾸어 준다.

3. 적당한 쿼리값을 INTO OUTFILE '{저장할 파일명}'  형식으로 저장한다.
  mysql connection 을 생성 후에 (in PHP)

$query = " SELECT * INTO OUTFILE '$backupFile'  fields terminated by '|'  FROM $tableName ; " ;
mysql_query( $query ) ;

$backupFile , $tableName 은 적당한 값으로 설정.

4. 해당 백업파일로 복구할때는 LOAD DATA INFILE
  백업시   fields ...  에 써 넣은 옵션을 그대로 써준다.

$query = " LOAD DATA INFILE '$backupFile'  INTO TABLE $tableName  fields terminated by '|' ; " ;
mysql_query( $query ) ;

이때도 절대경로를 써주는 것이 좋다.
그냥 xxx.sql 등의 파일이름만 적으면 /var/lib/mysql/{DB}/xxx.sql 을 찾게 된다.


# 참조 :
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

아는 사람은 다 아는 시리즈 ^^

# mysql 의 검색. 날짜형식 범위(between) 검색
select * from DateSample where bDate between 'A' and 'B' ;
와 같이 쿼리할때.

bDate 필드가 datetime 같은 날짜 형식으로 되어 있으면 별 문제없이 검색이 됨.
DB 설계할때 미리 처리하는 것이 정석이겠죠.

그런데, 예상치 않은 혹은 확장하게되는 경우에 임시적으로 처리하는 방법.

예를 들어 bDate 가  07/05/12 , 07/5/12 , 2007/5/5 , 2007.5.7   이런식으로 섞여 있다면
어떻게 할까?
하나의 형식에 맞게 전부 고쳐준다. (싫어! 네가 해라!)

그냥 모두 검색할 수 없을까?

# 이런 쿼리로 어떤가?
select * from DateSample where  STR_TO_DATE(CONCAT_WS('-', YEAR (bDate), MONTH(bDate), DAY(bDate)), '%Y-%m-%d') between "2007-05-21" and "2007-06-19" ;

이런식으로 
1. YEAR , MONTH , DAY 로 각 날짜별 년월일을 구한다.
2. CONCAT_WS 로  각 년월일을 '-' 로 연결(?)한다.
3. 마지막으로 STR_TO_DATE 로 날짜형식으로 바꾼다.

그래서 between 으로 날짜 범위 검색을 하면 정상적으로 검색이 된다.

# 물론 임시적인 조치 이므로 Date 필드는 정확한 형식으로 바꾸어 주는 것이 좋다.
꼭 테스트해보고 사용하시길(책임지지 않습니다. ^^)

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.