'mysql'에 해당되는 글 4건

  1. 2011.05.19 MySQL 데이터베이스 백업 및 복구
  2. 2011.05.19 MySQL EXPLAIN 사용
  3. 2011.05.17 PHP 로 MySQL 접근하기 (1)
  4. 2011.04.02 Mac에서 MySQL 서버 설치하기
I. 데이터베이스 백업하기
    1. 테이블 업데이트를 막고 백업하기
        sql>flush tables;
        sql>lock tables <table명> [read | write]
        #> cp 데이터 파일 복사 하기
 
    2. mysql_dump를 사용하기
        #>mysqladmin shutdown
        #>mysqladmin start --log-bin[=logfile]
        #>mysqldump --opt --all-databases > all.sql

    3. mysqlhotcopy 스크립트 사용
        #>mysqladmin shutdown
        #>mysqlhotcopy database </path/for/backup>
        #>mysqladmin start
         
II. 데이터베이스 복구
    1. 테이블의 문제
        #>myisamchk -r 

    2. 두번째 방법으로 백업했을 경우
        - 백업파일의 sql 실행 : 백업 시점의 데이터베이스를 새로 만듬
        - 그 이후의 저장된 갱신자료가 로그 파일에 저장되므로 그 파일로 데이터베이스를 갱신
           #>mysqlbinlog hostname-bin.[0-9]* | mysql

 
Posted by Kubernetes Korea co-leader seungkyua@gmail.com

MySQL EXPLAIN 사용

MySQL 2011.05.19 15:09
1. 문장 끝에 ; 대신 \G 를 사용하면 결과를 세로로 볼 수 있다.
2. select  문장 앞에 explain  키워드를 사용하면  플랜을 볼 수 있다.
explain
select * from User \G 

I. plan 결과 설명
    ※ http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

    1. select_type 값
        - SIMPLE
           . 평범한 SELECT 문장
        - PRIMARY
           . 하위 쿼리나  UNION을 사용할 때 외부(첫 번째) 쿼리
        - UNION
           . UNION 두 번째나 다음 번 쿼리
        - DEPENDENT UNION
           . UNION에서 첫 번째 쿼리에 따른 두 번째나 다음 번 쿼리
        - UNION RESULT
           . UNION 겨로가
        - SUBQUERY
           . 내부 하위 쿼리
        - DEPENDENT SUBQUERY
           . 첫 번째 쿼리에 따른 내부 하위 쿼리(즉, 상관 하위 쿼리)
        - DERIVED
           . FROM 절에서 사용한 하위 쿼리
        - UNCACHEABLE SUBQUERY
           . 그 결과가 캐쉬될 수 없으며 각 행마다 다시 계산되어야 하는 하위 쿼리
        - UNCACHEABLE UNION
           . UNCACHEABLE SUBQUERY에 속한 UNION에서 두 번째나 그 이후의 SELECT

    2. table
        - 쿼리에 응답하는 테이블 명

    3. type
        - 쿼리에서 테이블이 어떻게 조인되는지 설명
        - const 또는 system
           . 테이블이 한 번만 읽어온다. 테이블에 row가 단 하나만 있을 때 일어날 수 있다.
        - eq_ref
           . 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 한 row씩 읽는다.
             테이블의 인덱스를 사용할 때 조인이 사용되며 인덱스는 UNIQUE하거나 기본 키이다.
        - fulltext
           . fulltext 인덱스를 사용하여 조인한다.
        - ref
           . 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 대응되는 row 집합을 읽어온다.
             조인 조건에 따라서는 한 행만 선택할 수 없을 때, 조인에서 키의 일부분만이 사용되었을 때,
             혹은 키가 UNIQUE 하지 않거나 기본 키가 아닐 때
        - ref_or_null
           . ref 쿼리와 비슷하지만 NULL 인 row 도 찾아본다. (하위 쿼리에서 가장 많이 사용된다)
        - index_merge
           . Index Merge 라는 특별한 최적화 기법이 사용되었다.
        - unique_subquery
           . 유일한 한 줄이 리턴될 때 IN 하위 쿼리에서 ref를 대신하여 사용할 수 있다.
        - index_subquery
           . unique_subquery 와 비숫하지만 인덱스된 유일하지 않은 하위 쿼리에서 사용한다.
        - range
           . 조인하는 테이블에서 row 집합마다 이 테이블 중 특정 범위에 들어가는 row들을 읽어온다.
        - index
           . 모든 인덱스를 읽는다.
        - ALL
           . 이 테이블의 모든 행을 읽는다.

    4. rows
        - 조인을 실행하기 위해 테이블마다 읽어야 하는 row의 수를 대충 계산
          쿼리가 사용하는 총 row의 수를 얻기 위해서는 값들을 곱해야 한다.

    5. possible_keys
        - 테이블을 조인하기 위해 사용할 수 있는 키
        - PRIMARY 는 테이블마다 일반적으로 있기 때문에 대부분 나온다. 

    6. key
        - row 을 조인할 때 실제로 사용하고 있는 키
        - NULL 은 키를 사용하고 있지 않다는 뜻이다.

    7. key_len
        - 사용된 키의 길이를 나타낸다.

    8. ref
        - 테이블에서 row를 선택할 때 사용한 키를 나타낸다.

    9. Extra
        - 어떻게 조인을 실행하는가에 대한 정보
        - Distinct
           . 처음으로 일치하는 줄이 발견되면 끝난다.
        - Not exists
           . LEFT JOIN 을 사용하여 쿼리가 최적화되어 있다.
        - Range checked for each record
           . 조인하는 테이블에서 row 집합을 읽어올 때마다 사용할 수 있는 인덱스를 찾는다.
        - Using filesort
           . 테이터를 정렬하려면 두 번 읽어야 한다. (따라서 시간도 두 배 걸린다)
        - Using index
           . 테이블에 있는 모든 정보를 인덱스에서 얻어왔다. 따라서 실제 row는 읽지 않았다.
        - Using join buffer
           . 조인 버퍼를 사용하여 테이블을 부분적으로 읽어 들인다. 그 후 쿼리를 수행할 때 버퍼에서 각 행을 추출한다.
        - Using temporary
           . 이 쿼리를 실행하는 동안 임시 테이블을 사용하였다.
        - Using where
           . 행을 선택하는 데 where 절을 사용하였다.

II. sql 쿼리 튜닝
    1. 조인 최적화를 위해 키 분배를 검사
        - myisamchk 유틸리티 사용
            #>myisamchk --analyze <pathtomysqldatabase/table명>
            #>myisamchk --analyze <pathtomysqldatabase/*.MYI>
            #>myisamchk --analyze <pathtomysqldatadirectory/*/*.MYI>
        - Analyze Table 문을 사용
            sql>analyze table customers, orders, order_items, books;

    2. 테이블 최적화
        - 테이블의 단편화를 없앤다.
            sql>optimize table <table명>;
            #>myisamchk -r table
        - 테이블의 인덱스를 정렬하고 인덱스에 맞게 데이터를 재배치 한다.
            #>myisamchk --sort-index --sort-records=1 <pathtomysqldatadirectory/*/*.MYI>





Posted by Kubernetes Korea co-leader seungkyua@gmail.com
TAG mysql, 플랜
I. 파라미터 체크 및 변환
    1. trim()
        - 문자 앞뒤 공백제거 
$searchTerm = trim($searchTerm);
if (!$searchTerm) {
    echo "검색기간을 입력하세요.";
    exit;

    2. get_magic_quotes_gpc()
        - 서버에서 자동으로 문자열을 escape 해 주도록 세팅되어 있는지 확인하는 함수

    3. addslashes(), doubleval()
        - 데이터베이스 쿼리 조건절의 파라미터로 사용된다면 addslashed() 함수를 이요하여 escape 해줘야 한다.
        - 데이터베이스 숫자 필드의 경우에는 이상한 문자를 제거하기 위해서 doubleval()을 이요한다.
if (!get_magic_quotes_gpc()) {
    $searchTerm = addslashes($searchTerm);
    $prive = doubleval($prive);

    4. stripslashes()
        - 서버가 자동으로 escape 하도록 세팅되어 있으면 데이터베이스에서 추출한 데이터는 stripslashes()를
          호출하여 / 문자를 제거해야 한다.

    5. htmlspecialchars()
        - HTML에서 특별한 기능을 하는 문자를 인코딩한다. ( & < > " )

II. 데이터 베이스 접속 및 쿼리
    1. mysqli()
        - 서버 접속을 위해서 mysqli(서버명, 사용자id, 패스워드, 데이터베이스명) 을 사용한다.

    2. mysql_connect_errno()
        - 서버에 접속해서 유효한 데이터베이스 접속을 얻었는지 확인 한다.
        - 오류가 나며 오류 번호를, 성공했을 경우에는 0 을 리턴한다.
        - 데이터베이스에 접속하려 했을 때 코드 앞에 오류 억제 연산자 @ 를 사용한다.
          이렇게 하면 오류가 났을 때 자신의 코드로 처리할 수 있다.
        - Apache의 maxClients(1.x 버전), ThreadsPerChild(2.x버전) 는 httpd.conf 에서,
          MySQL의 max_connections 는 my.conf 에서 수정한다.

$con = @new mysqli($GLOBALS['gServerName'], $GLOBALS['gUserId'],
                   $GLOBALS['gPassword'], $GLOBALS['gDatabase']);

if ($con->connect_errno) {

    printf("%d : %s <br/>", $con->connect_errno, $con->connect_error);
    exit(); 

}


    3. $con->select_db()
        - 기본 데이터 베이스 변경
$con->select_db($GLOBALS[gDatabase]);

    4. $con->query()
        - 데이터베이스 쿼리를 수행하여 결과 값을 $result 에 저장한다.
        - 아래와 같은 코드는 보안에 문제가 있으니 문법을 이해하는 정도로만 사용

$userId = "skanddh";
$sql =
"select * from User where userId = " . $userId;

$result = $con->query($sql);


    5. $result->num_rows
        - 데이터베이스에서 조회된 결과 row 수를 알 수 있다.

$rowCount = $result->num_rows;


    6. $result->fetch_assoc()
        - 각 row 마다 호출하여 한 row의 값을 배열로 받는다.
        - 결과 값을 출력한다.

for($i=0; $i<$rowCount; $i++) {

    $row = $result->fetch_assoc();

    print("userId = " . htmlspecialchars(stripslashes($row['userId'])) .
          "<br/>");

    print("userName = " . htmlspecialchars(stripslashes($row['userName'])) .
          "<br/>");

    print("age = " . htmlspecialchars(stripslashes($row['age'])) . "<br/>");
}


    7. $result->fetch_object()
        - $result->fetch_assoc() 호출 시의 배열과는 다르게 결과가 object 로 넘어온다.

for($i=0; $i<$rowCount; $i++) {

    $row = $result->fetch_object();

    print("userId = " . htmlspecialchars(stripslashes($row->userId)) .
          "<br/>");

    print("userName = " . htmlspecialchars(stripslashes($row->userName)) .
          "<br/>");

    print("age = " . htmlspecialchars(stripslashes($row->age)) . "<br/>");

}


    8. $result->free(), $db->close();
        - 결과 값 release  및 데이터베이스 접속 끊기

$result->free();

$con->close();


    9. $con->affected_row
        - query 가 insert, delete, update 문일 경우 affected_row 로 영향받은 row의 갯수를 알 수 있다.
        - query() 메소드 보다는 statement 의 execute() 메소드를 사용하는 것이 좋다.

$userId = 'me';

$userName = 'john';

$age = 30;

$sql = " insert into User (userId, userName, age) " .

       "     values ('" . $userId . "', '". $userName . "', ". $age . ") ";

$con->query($sql);

printf("%d user inserted into database.<br/>", $con->affected_rows);

$con->commit(); 


    10. $stmt = $con->prepare($sql),   $stmt->bind_param(),     $stmt->execute()
         - Prepared Statement 를 사용하여 보안성을 높힌다.
         - bind_param() 호출 시 첫번째 전달인자의 의미,    s : string,   i : integer,   d : double

$sql = " insert into User (userId, userName, age) " .

       "           values (?, ?, ?) ";

$stmt = $con->prepare($sql);

$stmt->bind_param("ssi", $userId, $userName, $age);

$stmt->execute();

printf("%d user inserted into database.<br/>", $con->affected_rows);

$stmt->close();


    11. $stmt = $con->prepare($sql),   $stmt->bind_param(),     $stmt->execute(),   $stmt->bind_result(),  $stmt->fetch()
         - bind_result() 를 이용하여 조회되어 넘어오는 값을 변수에 할당할 수 있다. 

$sql = " select userId, userName, age from User ";

if ( $stmt = $con->prepare($sql) ) {

    $stmt->execute();

    if ($stmt->errno) {
        printf(
"execute() >> %d : %s <br/>",$stmt->errno, $stmt->error);
    } 

    $stmt->bind_result($rUserId, $rUserName, $rAge);

    $count = 0;

    while ($stmt->fetch()) {

        $count++;

        print("userId = " . htmlspecialchars(stripslashes($rUserId)) .
              "<br/>");

        print("userName = " . htmlspecialchars(stripslashes($rUserName)) .
              "<br/>");

        print("age = " . htmlspecialchars(stripslashes($rAge)) . "<br/>");

    }
    printf("%d row seleted. <br/>", $count);

    $stmt->close();

}



 



 
Posted by Kubernetes Korea co-leader seungkyua@gmail.com
TAG mysql, php
1. mysql-5.5.10-osx10.6-x86_64.dmg 를 다운받는다. (OS 버전에 맞춰)
2. 마운트하여  mysql-5.5.10-osx10.6-x86_64.pkg 를 설치한다.
3. mysql 설치 디렉토리는 /usr/local/mysql 이다.
4. 부팅시 자동으로 시작되게 하려면 MySQLStartupItem.pkg를 추가로 설치한다.
     /Library/StartupItems/MySQLCOM 디렉토리에 설치됨 
     
/etc/hostconfig 에 MYSQLCOM=-YES- 로 되어 있으며 자동으로 시작
  MYSQLCOM=-NO- 로 되어 있으면 자동으로 시작되지 않음

5.  MySQLStartupItem 이 설치되어 잇을 때 서버 start 및 stop
    shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM start 
    shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop 

6.  MySQL.prefPane 을 더블클릭하면 시스템환경설정에 추가할 수 있음
     Pane 에서도 자동 시작을 설정할 수 있음 

[encoding, characterset 적용]

1. mysql 서버설정을 위한 configuration file 생성
    shell> cd /usr/local/mysql/support-files/ 
    shell> sudo cp my-small.cnf /etc/my.cnf

2. innodb 로 설정

    [mysqld]
     default-storage-engine = INNODB
     # Uncomment the following if you are using InnoDB tables
     innodb_data_home_dir = /usr/local/mysql/data
     innodb_data_file_path = ibdata1:10M:autoextend
     innodb_log_group_home_dir = /usr/local/mysql/data
     # You can set .._buffer_pool_size up to 50 - 80 %
     # of RAM but beware of setting memory usage too high
     innodb_buffer_pool_size = 16M
     innodb_additional_mem_pool_size = 2M
     # Set .._log_file_size to 25 % of buffer pool size
     innodb_log_file_size = 5M
     innodb_log_buffer_size = 8M
     innodb_flush_log_at_trx_commit = 1
     innodb_lock_wait_timeout = 50

3. encoding, characterset 서버 환경 설정에 적용 
    
  [mysqld]
character-set-server=utf8 collation-server=utf8_general_ci

4. 트랜잭션 레벨 변경
    오라클의 기본 설정과 같이 READ-COMMITTED 로 적용한다.

[mysqld]
transaction_isolation = READ-COMMITTED

5. 테이블 대소문자 구분 없이 세팅
    
[mysqld]
lower_case_table_names=1

6. Auto Commit 설정
    클라이언트 접속시 디폴트는 autocommit=1 이다. 이것을 바꾸기 위해서는 설정파일에 다음과 같이 쓴다.
    0 은 autocommit 을 false 로 세팅한다.

[mysqld]
autocommit=0

7. client connection characterset 설정
    클라이언트 컨넥션의 디폴트 characterset을 설정한다.

[client]
default-character-set=utf8


[encoding 에 character set 적용]
- 데이터 베이스 생성시에 적용 - 해당 데이터베이스에 테이블을 생성할 때 모두 적용됨

     CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8 
DEFAULT COLLATE utf8_general_ci; 

[encoding characterset 확인]
mysql> use information_schema;
mysql> select * from GLOBAL_VARIABLES where VARIABLE_NAME like '%CHARACTER%';
mysql> select * from GLOBAL_VARIABLES where VARIABLE_NAME like '%COLLATION%';
mysql> select * from GLOBAL_VARIABLES where VARIABLE_NAME like '%AUTOCOMMIT%';

※ "Can't get hostname for your address" 에러가 나오면 서버 설정을 해주던지 hosts 파일에 등록한다.
1. [mysqld]
    skip-name-resolve

2. mysql 서버 hosts 파일에 mysql 을 호출하는 클라이언트 ip 를 등록

서버옵션확인
shell>mysqladmin -uroot -ppassword variables
Posted by Kubernetes Korea co-leader seungkyua@gmail.com