Signature Dessert
게시판 DB 데이터의 첨부파일 확장자가 대문자인 레코드 내용을 소문자로 변경해야하는 작업이 있었습니다. 대문자로 된 확장자를 가진 레코드를 조회해야하는데 대소문자를 구분하지 않고 결과를 보여줍니다.
대문자 문자열을 조건으로 조회를 하려면 binary 함수를 사용하면 되더군요.
|
1 |
SELECT * FROM my_table WHERE binary(my_text) LIKE '%.JPG%'; |
이렇게 컬럼명을 binary 함수로 감싸준 뒤 조건절에 대문자를 넣어주면 대문자로.. 소문자를 넣어주면 소문자로 조회 결과를 보여줍니다. 즉, 문자열의 바이너리로 비교하여 대소문자를 명확하게 구분할 수 있습니다.
이 방법은 한 예로 회원가입 시 대소문자를 구분하는 아이디 정책을 사용할때 유용하게 쓰일 것 같습니다.
평소에도 궁금했지만 자세히 찾아보지 못해 이제야 정리를 해봅니다.
테이블의 컬럼 수가 10개 내외라면 문제가 되지 않지만 많은 수의 컬럼을 지닌 테이블의 경우 유용합니다.
예를 들어 컬럼의 수가 30개인 테이블이 있는데 하나의 컬럼만 제외하고 나머지 29개를 셀렉트하고자 합니다. 이때 보통은 29개의 컬럼을 셀렉트 구문에 모두 입력합니다. 분명 방법이 있을텐데 검색을 해보면 방법이 없다라는 글 밖에 보이지 않더군요. 하지만 방법이 있습니다. (없을리가 없죠…)
일반적인 쿼리문으로는 어렵고 사용자 정의 변수(User-Defined Variable)과 몇가지 내장함수를 사용하는 것으로 해결 할 수 있습니다. 처음엔 어려워 보이더라도 하나하나 뜯어보면 간단합니다.
일단 구문은 이렇습니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- @qry 라는 사용자 정의 변수에 구문 작성 SET @qry = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<제외할 컬럼명>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<대상 테이블명>' AND TABLE_SCHEMA = '<대상 데이터베이스명>'), ' FROM <대상 테이블명>'); -- 쿼리문이 올바르게 작성되었는지 확인 SELECT @qry; -- Prepared statement 생성 (예제는 result 라는 이름으로 생성) PREPARE result FROM @qry; -- Prepared statement 실행 EXECUTE result; -- 생성한 Prepared stetement 해제 (클라이언트 세션이 종료되어도 자동으로 해제 됩니다.) DROP PREPARE result; |
위 내용을 요약하자면 qry라는 변수에 쿼리 구문을 작성하고 result라는 이름으로 Prepared statement 를 저장해서 실행 시키고 해제하는 것입니다.
여기서 Prepared statement는 쿼리를 메모리에 저장해두었다가 사용하는 MS-SQL의 Stored Procedure (저장 프로시저)와 그 용도가 비슷합니다.
위 구문에 대해 하나씩 정리해봅니다.
1. SET @qry = ”;
=> qry라는 변수를 생성하는 구문입니다.
2. CONCAT(‘구문1’, ‘구문2’, ‘구문3’, …);
=> 여러개의 구문을 하나로 합치는 용도로 사용됩니다. 여기서는 일반 텍스트 구문과 함수/변수를 연결하는 용도로 사용합니다.
3. SELECT REPLACE(‘<대상 문구>’, ‘<찾을 단어>’, ‘<바꿀 단어>’);
=> REPLACE 함수는 특이점은 없습니다. 대상 문구에서 단어를 찾아 치환해주는 용도입니다. 앞에 SELECT는 결과값을 반환하기 위함입니다.
4. GROUP_CONCAT(컬럼명);
=> 여러 레코드로 출력되는 특정 컬럼의 값들을 하나의 스트링으로 합쳐서 반환합니다.
5. INFORMATION_SCHEMA.COLUMNS 테이블
=> INFORMATION_SCHEMA 테이블은 사용자가 생성한 테이블의 속성이 담겨져 있는 시스템 테이블입니다. 이 테이블을 통해 사용자가 생성한 테이블의 컬럼이나 속성 값을 조회할 수 있습니다. 여기서는 테이블의 컬럼명을 얻어오기 위해 사용합니다. WHERE 절에 테이블명과 데이터베이스명이 있는 이유는 스키마 테이블에 모든 사용자 데이터베이스와 테이블 속성 정보가 레코드 단위로 저장되어있기 때문입니다.
6. 전체 구문 정리
=> INFORMATION_SCHEMA 테이블에서 조회하고자 하는 테이블의 컬럼정보를 GROUP_CONCAT 함수로 한줄의 텍스트로 만듭니다. (컬럼1,컬럼2,컬럼3,..)
이것을 REPLACE를 통해 제외하고자 하는 컬럼을 제거합니다. (쉼표와 싱글쿼트 표기에 유의하세요.)
예) REPLACE(GROUP_CONCAT(COLUMN_NAME), ‘컬럼1,’, ”);
그리고 마지막으로 앞 뒤에 셀렉트 구문을 추가하여 하나의 셀렉트 쿼리문을 완성 시킵니다.
완성이 되었다면 SELECT @qry;를 통해 구문을 확인합니다. 구문오류가 없는지 확인을 위함입니다. 조회된 셀렉트 구문을 그대로 복사하여 사용해도 됩니다만.. 복사, 붙여넣고 실행.. 귀찮습니다..
PREPARE result FROM @qry; 구문으로 Prepared statement를 생성하고 EXECUTE result;로 실행시킵니다. 원하는 결과 내용이 조회됩니다. 그리고 생성된 Prepared statement는 DROP Prepare result;로 메모리에서 해제시킵니다. 클라이언트 세션이 종료되면 같이 해제되지만 그 동안은 계속 메모리를 잡고 있기 때문에 그때 그때 해제하는 것을 권장합니다.
인서트문으로 활용
응용해서 INSERT문에도 적용가능합니다. 대표적인 용도로는 구조가 비슷한 A테이블과 B테이블이 있다는 가정에서 A테이블에 B테이블의 데이터를 주입시키는 경우일 겁니다. 자주 있는 경우는 아니겠지만 데이터 마이그레이션 작업 시 유용합니다.
여기서는 변수가 2개가 필요합니다. 1개도 가능하지만 너무 길어지고 반복되는 구문이 생깁니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- INSERT 할 컬럼 생성 SET @col = (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<제외할 컬럼명>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<대상 테이블명>' AND TABLE_SCHEMA = '<대상 데이터베이스명>'); -- INSERT 쿼리 생성 SET @qry = CONCAT('INSERT INTO <대상 테이블명> (', @col ,') SELECT ', @col, ' from <주입할 데이터가 있는 테이블명>'); -- Prepared statement 생성 PREPARE result FROM @qry; -- Prepared statement 실행 EXECUTE result; -- Prepared statement 해제 DROP PREPARE result; |
적다보니 제법 많은 양을 정리했네요. 이 방법은 테이블에 컬럼의 수가 많으면 많을 수록 유용할 겁니다. 그리고 사용자 정의 변수(User-defined variable)는 동적 쿼리를 작성을 가능하게 하므로 재사용성을 높일 수 있는 쿼리문을 작성할 수 있습니다.
영문, 숫자 인 경우
|
1 |
SELECT LENGTH([대상 필드]) FROM [테이블]; |
한글(멀티바이트 문자)이 포함된 경우
|
1 |
SELECT CHAR_LENGTH([대상 필드]) FROM [테이블]; |
이를 응용해서 조건문에 사용 할 수 있습니다.
|
1 |
SELECT * FROM [테이블] WHERE LENGTH([대상 필드]) = 4; |
자리 수가 여러 종류인 코드 형식의 데이터 중 원하는 길이의 코드만을 셀렉트 해올 때 유용합니다.
두 테이블 간 1:1 매칭이 되는 키값을 비교하여 레코드 차이가 있는지 확인해볼 수 있는 쿼리 입니다.
|
1 2 3 4 5 6 |
/* 원본 테이블 : tableA (비교 필드 : fieldA) 비교 테이블 : tableB (비교 필드 : fieldB) */ SELECT b.fieldB, a.fieldA FROM tableA a RIGHT OUTER JOIN tableB b ON a.fieldA = b.fieldB WHERE a.fieldA IS NULL; |
아마존 웹 서비스(AWS)에서 nginx와 mysql, 그리고 php를 구동하기 위한 php-fpm 설치 방법을 소개합니다.
yum update
|
1 |
$ sudo yum -y update |
Nginx + PHP FPM 설치
|
1 |
$ sudo yum install -y nginx php-fpm |
PHP 확장 모듈 설치
|
1 |
$ sudo yum install -y php-devel php-mysql php-pdo php-pear php-mbstring php-cli php-odbc php-imap php-gd php-xml php-soap |
PHP APC 설치
|
1 2 |
$ sudo yum install -y php-pecl-apc $ sudo yum install -y pcre-devel |
문제 : 위 PHP APC를 설치하면 php-fpm 프로세스의 iowait (입출력 대기 부하)가 50~99% 발생하는 문제가 있습니다. 이 과정에서 PHP APC 설치는 권장하지 않습니다. 자료를 찾아보는 중에는 memcached도 비슷한 문제가 있다고 합니다.
Mcrypt 설치 (Codeigniter framework 사용 시 필요)
|
1 |
$ sudo yum install php-mcrypt |
MySQL 설치
|
1 |
$ sudo yum -y install mysql-server mysql |
Nginx 기본 설정 파일 작성
|
1 |
$ sudo vim /etc/nginx/conf.d/default.conf |
default.conf 기본 내용
|
1 2 3 4 5 6 7 8 9 10 11 12 |
server { location / { root /var/www/html; index index.php index.html index.htm; } location ~ \.php$ { fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock; fastcgi_index index.php; fastcgi_param SCRIPT_FILENAME /usr/share/nginx/html$fastcgi_script_name; include fastcgi_params; } } |
php-fpm 설정
|
1 |
$ sudo vim /etc/php-fpm.d/www.conf |
www.conf 설정 (각 항목을 찾아 아래 내용과 같이 수정)
|
1 2 3 4 5 6 |
listen = /var/run/php-fpm/php-fpm.sock listen.owner = nginx listen.group = nginx listen.mode = 0664 user = nginx group = nginx |
EC2 인스턴스 재 시작 시 nginx + mysql + php fpm 자동 실행 되도록 설정
|
1 2 3 |
$ sudo chkconfig nginx on $ sudo chkconfig mysqld on $ sudo chkconfig php-fpm on |
Nginx + MySQL + php-fpm 서비스 시작
(MySQL은 최초 시작시 root 계정 설정과 패스워드 설정에 대한 멘트가 나오니 참고하셔서 설정해줍니다.)
|
1 2 3 |
$ sudo service php-fpm start $ sudo service nginx start $ sudo service mysqld start |
웹페이지 확인
Welcome to nginx on the Amazon Linux AMI! 화면 출력되면 nginx 설치는 정상
세팅 과정은 여기까지 입니다. 아래부터는 세팅하는 과정에서 발생한 문제나 이슈에 대해서 정리한 내용이니 참고하세요.
– 추가 정보 –
1. nginx reload (Nginx는 서비스를 재시작하지 않고 reload로 무중단 재시작이 가능, 단 모듈이나 설정이 변경된 경우에는 restart 필요)
|
1 2 3 4 5 |
$ sudo /usr/sbin/nginx -s reload 또는 $ sudo service nginx reload |
2. Nginx의 Error / Access log 기본 경로
error log : /var/log/nginx/error.log
access log : /var/log/nginx/access.log
3. 서버 타임존 설정
|
1 |
$ sudo ln -sf /usr/share/zoneinfo/Asia/Seoul /etc/localtime |
현재 타임존은 $ date 명령으로 확인 가능
4. FastCGI sent in stderr: “Primary script unknown” while reading response header from upstream,.. 에러 발생 문제
이 에러는 여러 원인이 있겠지만 저의 경우
/etc/nginx/conf.d/default.conf (호스트 설정 파일)의 location / 블럭에서 index.php가 빠져있어서 에러가 발생.
5. CodeIgnighter 사용 시 mod rewrite 설정 (호스트 설정 파일 location / 블럭 안에 추가)
|
1 2 3 |
if (!-e $request_filename) { rewrite ^(.*)$ /index.php last; } |
6. EC2 인스턴스에서는 CORS(Cross-Origin Resource Sharing) 설정 불가. 이를 해결하려면 S3를 사용하거나 외부 CDN 서비스를 사용해야함.
정규식을 이용한 MySQL 쿼리문 간단하게 정리합니다.
숫자로만 구성된 문자열 찾기
|
1 |
SELECT my_field FROM my_table WHERE my_field regexp '^[0-9]+$'; |
특수문자가 포함된 문자열 찾기
|
1 |
SELECT my_field FROM my_table WHERE my_field regexp '[`~!#$%^&*|\\\'\";:\/?]'; |
이 외의 regexp 함수를 활용해 정규식으로 조건을 걸 수 있습니다.
* 이 포스트는 Ubuntu 14.04 LTS 버전을 기준으로 작성되었습니다.
파이썬에서 MySQL DB를 사용하기 위해서는 당연히 MySQL 드라이버 모듈이 필요합니다. 이 역할을 하는 것이 MySQL-python 모듈입니다. pip를 통해 설치가능합니다.
|
1 |
$ pip install MySQL-python |
바로 설치가 되면 포스팅을 하지 않았겠죠. 여기서 또 문제가 발생.
“python mysql_config not found” 이라는 에러가 발생합니다.
과거 pycurl 설치시 libcurl 모듈이 필요했듯이 MySQL-python 설치시에도 의존 모듈을 설치해줘야 합니다.
|
1 |
$ sudo apt-get install libmysqlclient-dev |
MySQL-python은 libmysqlclient-dev 모듈이 있어야 정상적으로 설치 가능합니다.
예전부터 정리하려고 했던 내용이었으나 이제야 남기게 되네요.
Shell에서 MySQL 계정과 DB를 생성하는 방법을 되도록 간단하게 설명해드리겠습니다.
전체 과정을 순서대로 나열하자면 아래와 같습니다.
1. DB 계정 추가 (권한 설정)
2. 추가된 DB계정의 비밀번호 초기화
3. 추가된 계정이 사용하게 될 DB 생성
4. DB 접속 테스트
1. DB 계정 추가 (권한 설정)
mysql DB의 user테이블에 insert 문으로 계정을 직접 넣는 방법도 있으나 깔끔하지 못해 개인적으로는 잘 사용하지 않는 방법입니다. DB계정 추가 및 권한 설정은 예전에 작성한 포스트를 참고해주세요.
2. 추가된 DB계정의 비밀번호 초기화
1번에서 초기화한 계정의 비밀번호를 변경해주셔야 합니다. 방법은 mysql DB의 user 테이블에서 update 쿼리를 실행하면 됩니다.
|
1 |
UPDATE user SET Password = PASSWORD('설정할 패스워드') WHERE User = 'DB계정명'; |
3. 추가된 계정이 사용하게 될 DB 생성
이제 추가된 DB계정이 사용할 데이터베이스를 생성해줘야 합니다. 간단한 쿼리 한 줄이면 됩니다.
|
1 |
CREATE DATABASE 추가할DB명; |
여기서 주의하실 점은 1번에서 계정 추가시 실행 했던 쿼리 “grant all privileges on DB명.* to”의 DB명과 동일해야 한다는 점입니다. 기존 DB에서 계정만 추가하시는 경우에는 이 과정은 패스합니다.
4. DB 접속 테스트
위 과정이 모두 끝났다면 Shell이나 외부 프로그램(HeidiSQL, SQLYog 등)을 통해서 접속 테스트를 해봅니다. 1번에서 localhost 접속권한만 추가하셨다면 Shell 내부에서만 접속이 가능하게 됩니다. 모든 외부 IP에서 접속이 가능하게 하려면 localhost외에 와일드카드(%)로 추가하셔야 합니다. 또는 일부 IP만 접속 허용하실 경우에는 와일드카드로 추가된 User 테이블의 레코드는 삭제하고 특정 IP로 추가해주시면 됩니다. (자세한 내용은 1번 DB 계정 추가 링크를 참고하세요.)
정리한 내용은 여기까지입니다.
이 내용에 대해서 질문이나 궁금한 점이 있으신분은 아래 코멘트 달아주세요. 최대한 빠르게 답변드리겠습니다.
쉘 환경에서 sql 파일을 실행시키는 방법을 소개합니다.
주로 백업받은 테이블이나 DB 데이터를 복원할때 사용하게 되겠습니다.
|
1 2 3 4 |
# cd /sql (sql 파일이 있는 디렉토리로 이동) # mysql -u 접속계정 -p (mysql 접속) mysql> source ./data.sql |
제 블로그 서버가 하루 정도 장애가 있었습니다. 그나마 캐시 파일 덕분에 장애가 있었음에도 불구하고 일부 정상 동적이 되었지만 반대로 덕분에 문제를 바로 파악하지 못했었습니다.
증상은 이러 했습니다. 서버 재부팅 후 MySQL 데몬이 시작 되지 않음. 아파치는 문제 없음.
MySQL 데몬을 start하면 성공/실패 결과가 출력되지 않고 반응이 없었습니다. 이때 MySQL 로그를 확인해보기로 했습니다.
|
1 |
tail /var/log/mysqld.log |
vim이나 cat으로 확인해봐도 무관합니다.
자, 로그를 확인해보니..
|
1 |
[ERROR] Can't start server: can't create PID file: No space left on device |
이 부분이 눈에 확 들어왔습니다. 하드디스크 공간이 꽉 차있던 것이었습니다.
그렇다면 확실하게 확인하기 위해 df 명령으로 재 확인합니다.
used 100%…
역시 하드디스크 공간이 없었던 것이었습니다. 저 같은 경우 시스템 백업을 위해 주기적으로 크론을 돌리고 있습니다. 전체 백업이다 보니 한번 돌려 압축된 백업 파일의 용량이 만만치 않습니다. 그런데 이것이 4달 가량 관리가 되지 않고 방치되자 쌓이고 있던 것이었죠..
결국 오래된 백업 파일은 삭제 후 MySQL 데몬은 재시작 합니다. 잘 되네요..
섣불리 판단하고 MySQL을 재설치하려 했다면 자칫 코앞에서 삽질을 시작했을지도 모릅니다.