안승규의 블로그 (Stay hungry, stay foolish)

MySQL 데이터베이스 백업 및 복구

MySQL 2011. 5. 19. 15:16

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 seungkyua@gmail.com

,

MySQL EXPLAIN 사용

MySQL 2011. 5. 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 seungkyua@gmail.com

,

PHP 로 MySQL 접근하기

프로그래밍/php 2011. 5. 17. 18:24

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 ", $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'])) .
" ");

print("userName = " . htmlspecialchars(stripslashes($row['userName'])) .
" ");

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

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

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

$row = $result->fetch_object();

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

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

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

}

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. ", $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. ", $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 ",$stmt->errno, $stmt->error);
 }

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

$count = 0;

while ($stmt->fetch()) {

$count++;

print("userId = " . htmlspecialchars(stripslashes($rUserId)) .
" ");

print("userName = " . htmlspecialchars(stripslashes($rUserName)) .
" ");

print("age = " . htmlspecialchars(stripslashes($rAge)) . " ");

}
printf("%d row seleted. ", $count);

$stmt->close();

}

저작자표시 변경금지

Posted by seungkyua@gmail.com

,

Mac에서 MySQL 서버 설치하기

Mac 2011. 4. 2. 21:57

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