mysql 권한생성, 비밀번호변경, 패스워드 분실시 처리방법

1. GRANT 명령어를 사용하여 계정생성 및 DB 권한 주기

mysql> GRANT ALL PRIVILEGES ON *.* to mk@localhost IDENTIFIED BY 'password' WITH GRANT OPTION;
// 모든 DB 접속권한 부여 및 root 권한 부여, mk라는 유저는 localhost 에서만 접속가능 (내부접속)mysql> flush privileges; //권한로드

: 모든곳(외부)에서 접속가능하게 하려면 mk@localhost 부분을 mk@'%' 로 바꿔주면 됨
: mk계정이 특정한 곳에서만 접속 가능하게 하려면 mk@localhost 부분을 mk@'ip주소' 로 바꿔주면 됨
: mk계정이 특정 DB에 접속을 하며(보이며) 해당 DB에'만' root 권한(모든 명령어) 을 줄때는 *.* 부분을 DB명.* 로바꿔주면 됨
: mk계정에 특정 명령어(권한)만 사용하게 할때는 ALL PRIVILEGES 부분을 바꿔주면 됨
ex) GRANT select, insert, delete, update ~~.... (select, insert, delete, update 만 실행 할 수 있음)

* GRANT 명령어가 아닌 mysql DB의 user테이블과 db 테이블을 이용하는 방법도 있지만 번거로우므로 패스..

2. 계정 패스워드 변경

root 권한으로 mysql 접속
mysql> show databases; //모든 db 목록 출력
mysql> use mysql; //mysql 이라는 db를 사용
mysql> show tables; // mysql DB의 모든 table 목록을 출력
mysql> show columns from user; // user 테이블의 구조를 살펴본다. desc user 로 해도 됨
mysql> UPDATE user SET password = password('변경패스워드') WHERE user = '계정';
// 매치된 row 가 있는지, 변경된게 몇개인지에 대한 결과가 나온다. 모든 결과가 0이라면 제대로 적용되지 않은것. 특정 계정만 바꾸려고 했으니 당연히 1이라는 결과값이 나와야 한다.
/* Result Example
 Query OK, 1 row affected (0.02 sec)
 일치하는 Rows : 1개 변경됨 : 1개 경고 : 0개
*/
mysql> flush privileges;

// mysql DB 선택후 바로 UPDATE 명령어를 내리면 된다. 여타 명령어는 DB를 확인하며 찾아가는 과정임..

3. 계정 삭제

root 계정 접속후
mysql> use mysql;
mysql> drop user 계정명;
// 관련 DB 정보 삭제

4. 패스워드 분실시
패스워드 분실시

패스워드 분실시 빠르게 root 패스워드를 변경 해보자

우선 mysql를 stop 한 환경에서 작업을 해야 한다.

<권한 테이블을 사용하지 않겠다고 선언하면서 서버 start>
mysql설치루트/bin> ./mysqld_safe --skip-grant-tables &

[출처] mysql root 패스워드 변경하기(분실)|작성자 푸쉬킨

<mysql 데이터베이스로 바로 접속>
mysql설치루트/bin>./mysql -u root mysql

<update문으로 패스워드 변경>
mysql>update user set password=password('new-passwd') where user = 'root';

<mysql 접속 종료>
mysql>quit

<mysql stop하기>
mysql설치루트/bin>./mysqladmin -u root -p shutdown

<mysql 재시작>
mysql설치루트/bin>./mysqld_safe &

<mysql 접속하기>
mysql설치루트/bin>./mysql -u root -p
password:

mysql>

MyISAM vs InnoDB의 트랜젝션

언제가 될지 모르겠지만..MYSQL 세미나를 하게 될 날이 올 것 같아서 개인적으로 이것 저것 테스트를 해보고 있습니다.
시간적인 여유만 있다면... 처음부터 차근 차근 나만의 정리 공간을 만들고 싶지만... 이놈의 시간은 언제쯤,,......;;;


시간 날때 마다 조금씩 일단 기본 과정 부터 차근 차근 정리를 해보려 합니다.

첫 파트는 MYSQL의 스토리지 엔진 부분입니다.
MYSQL 은 ORACLE 과는 다르게 테이블의 형식 중 Engine 을 선택하여 만들 수 있습니다.
( 디폴트는 MyISAM 입니다.)

이 엔진의 선택에 따라서  해당 서버의 환경은 크게 변할 수 있습니다.
(빅사이트가 아닌 중소 규모의 서버들은 전문적인 관리자도 없기에 아무 생각 없이 MYISAM만 쓰더군요..)

 스토리지 엔진의 종류에는 여러가지가 있으나, 그 중 실무에서 사용되는 것은 크게 MyISAM, InnoDB, Federate  정도 되겠습니다.
(개인적으로 Federate는 알려지지 않는 에러나, 버그성이 조금 있어.. 안정성 측면에서는 조금 떨어집니다.. ORACLE의 디비 링크 같은 역할)

일단 테스트는 업무 시간에 잠깐 돌려 본 것이라.. 이 글에 대한 정리는 나중에 해야 겠습니다. -_-;

머 테스트를  하게 된 목적은 이것 입니다.

MyISAM 엔진의 테이블은 AUTOCOMMIT입니다. 쿼리가 수행되는 순간 커밋이 이뤄집니다.
하여 따로 커밋이나 롤백 등의 트랜잭션 작업을 진행할 수 없습니다.
또한 테이블의 UPDATE, DELETE 등의 DML 문이 발생했을 경우 해당 테이블 전체를 잠궈 버리기 때문에( 테이블 락)
다른 세션에서의 SELECT 나 DML 작업을 동시에 병행 할 수 없는 문제가 있지요.

InnoDB는 오라클과 매우 유사한 환경을 제공합니다. COMMIT 과 ROLLBACK을 수행 할 수 있으며, (로우 락)
이전 값을 UNDO에 저장 하기 때문에 해당 테이블의 DML이 발생하였을 때, 다른 세션에서 접근 시  이전 값(OLD_DATA)를
보장합니다.

이러한 정의 하에 관련 테스트를 진행하였습니다.




먼저 MYISAM 의  트랜젝션 테스트 입니다.

         Name: mytest
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 42991624
 Avg_row_length: 7
    Data_length: 594542648
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 293601280
 Auto_increment: NULL
    Create_time: 2011-04-20 15:17:25
    Update_time: 2011-04-20 16:04:13
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment:

[db-repl2-db1][changsun]> desc mytest;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

=====================================
업데이트 (테이블 락)

##테이블 카운트
[db-repl2-db1][changsun]> select count(*) from mytest;
+----------+
| count(*) |
+----------+
| 63963143 |
+----------+
1 row in set (0.00 sec)



## 1) Update 시작
[db-repl2-db1][changsun]> update mytest set a=2 where a=1;
Query OK, 42991616 rows affected (4 min 49.40 sec)Rows matched: 42991616  Changed: 42991616  Warnings: 0

## 2) 조회 구문
[db-repl2-db1][changsun]> select * from mytest where a=7;
+------+
| a    |
+------+
|    7 |
+------+
1 row in set (5 min 2.10 sec)   -> 업데이트가 완료 될때까지 대기 상태

## 프로세스 상태
[db-repl2-db1][(none)]> show processlist;
+----+-----------------+-----------+----------+---------+-------+-----------------------------+---------------------------------+
| Id | User            | Host      | db       | Command | Time  | State                       | Info                            |
+----+-----------------+-----------+----------+---------+-------+-----------------------------+---------------------------------+
|  1 | event_scheduler | localhost | NULL     | Daemon  | 78415 | Waiting for next activation | NULL                            |
| 53 | root            | localhost | changsun | Query   |    20 | Updating                    | update mytest set a=2 where a=1 |
| 55 | root            | localhost | changsun | Query   |    17 | Locked                      | select * from mytest where a=7  |
| 57 | root            | localhost | NULL     | Query   |     0 | NULL                        | show processlist                |
+----+-----------------+-----------+----------+---------+-------+-----------------------------+---------------------------------+


===============================================================================================================
인서트 ( 영향 없음 락 X)
 
## 1) Insert 시작
[db-repl2-db1][changsun]> insert into mytest select * from mytest where a=3;
Query OK, 20971520 rows affected (41.42 sec)Records: 20971520  Duplicates: 0  Warnings: 0
## 2) 조회 구문
[db-repl2-db1][changsun]> select * from mytest where a=7;
+------+
| a    |
+------+
|    7 |
+------+
1 rows in set (14.96 sec)   -> 동시 수행됨

## 프로세스 상태
[db-repl2-db1][(none)]> show processlist;
+----+-----------------+-----------+----------+---------+-------+------------------------------+---------------------------------------------------+
| Id | User            | Host      | db       | Command | Time  | State                        | Info                                              |
+----+-----------------+-----------+----------+---------+-------+------------------------------+---------------------------------------------------+
|  1 | event_scheduler | localhost | NULL     | Daemon  | 79036 | Waiting for next activation  | NULL                                              |
| 53 | root            | localhost | changsun | Query   |    15 | Copying to tmp table on disk | insert into mytest select * from mytest where a=3 |
| 55 | root            | localhost | changsun | Query   |    13 | Sending data                 | select * from mytest where a=7                    |
| 57 | root            | localhost | NULL     | Query   |     0 | NULL                         | show processlist                                  |
+----+-----------------+-----------+----------+---------+-------+------------------------------+---------------------------------------------------+
4 rows in set (0.00 sec)



===============================================================================================================

델리트 (테이블 락)

## 1) Delete 시작
[db-repl2-db1][changsun]> delete from mytest where a=3;
Query OK, 41943040 rows affected (4 min 14.14 sec)

## 2) 조회 구문
[db-repl2-db1][changsun]> select * from mytest where a=7;
+------+
| a    |
+------+
|    7 |
+------+
2 rows in set (4 min 29.99 sec)  -> 델리트가 완료 될때까지 대기 상태

## 프로세스 상태
[db-repl2-db1][(none)]> show processlist;
+----+-----------------+-----------+----------+---------+-------+-----------------------------+--------------------------------+
| Id | User            | Host      | db       | Command | Time  | State                       | Info                           |
+----+-----------------+-----------+----------+---------+-------+-----------------------------+--------------------------------+
|  1 | event_scheduler | localhost | NULL     | Daemon  | 79214 | Waiting for next activation | NULL                           |
| 53 | root            | localhost | changsun | Query   |    15 | updating                    | delete from mytest where a=3   |
| 55 | root            | localhost | changsun | Query   |    13 | Locked                      | select * from mytest where a=7 |
| 57 | root            | localhost | NULL     | Query   |     0 | NULL                        | show processlist               |
+----+-----------------+-----------+----------+---------+-------+-----------------------------+--------------------------------+

=============================================================================




이노디비의 트랜젝션

# 테이블 조회
[db-repl2-db1][changsun]> select * from test;
+------+------+
| a    |
+------+------+
|  100 |  
|  100 |  
|    3 |  
|    4 |   
|    5 |   
|    6 |   
|  100 | 
|  100 |   
|  100 |  
| NULL |
|    3 |  
|   30 | 
|    5 |  
|  100 | 
+------+
14 rows in set (0.00 sec)

# 업데이트 수행
[db-repl2-db1][changsun]> update test set a=1;
Query OK, 14 rows affected (0.00 sec)
Rows matched: 14  Changed: 14  Warnings: 0

# 해당 세션에서의 조회[db-repl2-db1][changsun]> select * from test;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
+------+
14 rows in set (0.00 sec) -> 자신에게는 변경된 값으로 보인다.



# 다른 세션에서의 COMMIT 전 조회
[db-repl2-db1][changsun]> select * from test;
+------+
| a    |
+------+
|  100 |
|  100 |
|    3 |
|    4 |
|    5 |
|    6 |
|  100 |
|  100 |
|  100 |
| NULL |
|    3 |
|   30 |
|    5 |
|  100 |
+------+
14 rows in set (0.00 sec)  -> 다른 세션에서는 트랜젝션 종료 될때까지, 이전 값 (old_data) 을 보게 된다.


# 트렌젹션 커밋 수행[db-repl2-db1][changsun]> commit;
Query OK, 0 rows affected (0.07 sec)


# 다른 세션에서의 COMMIT 후 조회
[db-repl2-db1][changsun]> select * from test;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
+------+
14 rows in set (0.00 sec) -> commit 이후에 다른 세션에서의 DML 변경 결과를 보게 된다. 

 ===========================================================================================================


 정리 안된  개인 자료

[db-repl2-db1][changsun]> CREATE TABLE `sample` (
      `id` int(11) NOT NULL,
      `heritage_id` int(11) default NULL,
      `review_count` int(11) default NULL,
      `status` char(1) default NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)

[db-repl2-db1][changsun]> create table test(a int, b int) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)

[db-repl2-db1][changsun]> create table mytest (a int);
Query OK, 0 rows affected (0.09 sec)


[db-repl2-db1][changsun]> show table status\G
*************************** 1. row ***************************
           Name: mytest
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2011-04-20 15:17:25
    Update_time: 2011-04-20 15:17:25
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: sample
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 56623104
 Auto_increment: NULL
    Create_time: 2011-04-20 11:38:43
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 3. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 14
 Avg_row_length: 1170
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 56623104
 Auto_increment: NULL
    Create_time: 2011-04-20 11:39:31
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment:

MySQL 엔진의 캐릭터셋 < DATABASE 캐릭터셋 < 테이블의 캐릭터 셋


MY.CNF 이노디비 설정값
innodb_data_home_dir = /data1/mydata/
innodb_data_file_path = ibdata1:10M;ibdata2:10M;ibdata3:10M;ibdata4:10M;ibdata5:10M;ibdata6:10M;
innodb_log_group_home_dir = /data1/mysqllog
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

MyISAM & InnoDB

     http://ojava.tistory.com/25

MySQL의 Storage Engine에는 위에 보시는 그림처럼 여러가지가 존재합니다.
그 중 제일 많이 사용하는 두 가지가 바로, MyISAM과 InnoDB인데요~
오늘은 이 두 가지를 비교 분석하는 포스팅을 해볼까 합니다.
     
 먼저 MyISAM과 InnoDB가 무엇인지와 각각의 특징을 알아봅시당!

      
 

1. MyISAM

 

 MyISAM은 ISAM(Indexed Sequential Access Method) 의 단점을 보완하기 위해 나온 업그레이드 버젼으로,

 이 엔진은 비-트랜젝션-세이프(non-transactional-safe) 테이블을 관리합니다.

       

MyISAM은 후에 소개하는 InnoDB에 비하여 별다른 기능이 없으므로 데이터 모델 디자인이 단순하다는 것이 장점입니다.

따라서 전체적으로 속도가 InnoDB 보다 빠릅니다. 특히 Select 작업 속도가 빠르므로 읽기 작업에 적합합니다!

Full-text 인덱싱이 가능하여 검색하고자 하는 내용에 대한 복합검색이 가능하답니다.

       

But! 그러나 데이터 무결성에 대한 보장이 되지 않습니다. MyISAM을 쓸 때의 무결성은 개발자나 DBA가 해야 합니다 ㅠ_ㅠ
또한 트랜잭션에 대한 지원이 없기 때문에 작업도중 문제가 생겨도 이미 작성된 내용들은 DB로 쏙 들어가버립니다.

가장 강조되는 단점으로는 Table-level Lock을 사용하기 때문에 쓰기 작업(INSERT, UPDATE) 속도가 느립니다.

변경을 많이 요하는 작업이라면 Table 단위의 Lock을 사용하는 MyISAM의 사용을 권하지 않습니다.

이렇게 기능이 없다니… 빠르기라도 해야겠네영!

       

따라서 MyISAM의 사용이 적합한 곳은 트랜잭션 처리가 불필요하며,

위에서 언급한 것 처럼 Select의 속도가 빠르므로 주로 조회작업이 많은 경우에 사용됩니다.

     

   

2. InnoDB

       

MyISAM과는 달리 InnoDB는 트랜잭션을 지원하므로 트랜잭션-세이프 스토리지 엔진에 해당됩니다.

Commit, Rollback, 장애복구, row-level locking, 외래키 등 다양한 기능을 지원합니다!

MyISAM의 부족했던 기능에 실망했던 사람들이라면 InnoDB로 갈아탔겠죠?

        

InnoDB의 장점을 살펴보면, 우선 데이터 무결성에 대한 보장이 됩니다.

제약조건, 외래 키의 생성이 가능하며, 동시성 제어가 가능합니다.

특히 MyISAM이 Table 단위의 Lock이었다면 InnoDB는 Row-level Lock (행 단위 Lock) 을 사용하기 때문에

변경 작업(INSERT, UPDATE, DELETE)에 대한 속도가 빠르다는 것이 큰 장점입니다.

       

그러나, 이렇게 여러가지 기능을 제공하다보니 InnoDB의 데이터 모델 디자인에는 많은 시간이 필요합니다.

또한 시스템 자원을 많이 사용한다는 단점 또한 가지고 있습니다. Full-text 인덱싱이 불가능하다는 것 역시 단점으로 보여집니다.

       

InnoDB의 경우는 트랜잭션 처리가 필요한 작업을 수행하며, 데이터 입력 및 수정과 같이 변경이 빈번한!

높은 퍼포먼스를 요구하는 대용량 사이트 등에서 효율적으로 사용될 수 있습니다.

       

      
 

여기서 질문! 이 두 종류의 DB를 함께 사용할 수 있을까요?

물론 함께 사용할 수는 있습니다. 하지만 백업 방법에 차이가 있어,

백업이 번거로워지고 Lock에 대한 Level이 다르기 때문에 사용에 문제가 생길 수 있다고 봅니다.

(MyISAM은 Table 단위로 Lock이 걸리고, InnoDB는 Row 단위로 Lock이 걸리니까요~)

      


1. MyISAM
 1) 예전의 MySQL 의 Storage Engines 으로 MyISAM 을 사용했었습니다.
예를 들자면 블로그라던지, 게시판 처럼 한사람이 글을 쓰면 다른 많은 사람들이 글을 읽는 방식에 최적의 성능을 발휘를 하지요. 지금도 많이 사용하고 있는 방식입니다.
 2) 제공하는 웹서비스다 그닥 크지 않다면 이것을 사용해도 괜찮다고 생각을 합니다.
2. InnoDB

 1) 트랜잭션-세이프 스토리지 엔진입니다.
 2) MyISAM 과 비슷하지만 ORACLE 처럼 많은 기능을 지원을 합니다.
   (* commit, rollback, 장애복구, row-level locking, 외래키 등)
 3) 다수의 사용자 동시접속과 퍼포먼스가 증가하여 대용량 데이터를 처리할 때 최대의 퍼포먼스를 내도록 설계되었습니다. CPU효율은 어느 디스크 기반의 데이터 베이스와 비교해도 손색이 없고 자체적으로 메인 메모리 안에 데이터 캐싱과 인덱싱을 위한 버퍼 풀(pool)을 관리합니다.

 4) 테이블과 인덱스를 테이블 스페이스에 저장을 하고 테이블 스페이스는 몇개의 서버파일이나 디스크 파티션으로 구성되어있습니다. 이것은 MyISAM 과 다른 점인데, MyISAM은 테이블과 인덱스를 각각 분리된 파일로 관리합니다. 여기서 중요한것이 이제 InnoDB 를 제대로 사용을 하기 위해서는 테이블 스페이스 라는 개념을 파악을 하셔야합니다. 이것에 대해서는 밑에서 따로 언급을 하겠습니다.
 5) InnoDB 테이블은 OS의 파일 사이즈 한계가 2GB이더라도 상관없이 어느 크기나 가질 수 있습니다.

 6) InnoDB는 높은 퍼포먼스가 필요한 대용량 사이트에 적합합니다.
3. InnoDB 사용하기
 1) InnoDB 는 MyISAM 과 공유하는 메모리도 있지만 별도의 Buffer pool을 가지고 있으니까 InnoDB 전용 DB를 구성한다면 MyISAM 이 사용하는 record_boffer 과 key_buffer 에 너무 많은 메모리를 할당하지 마세요
 2) InnoDB 설정
   ㄱ) MySql 을 설치한 폴더 아래에 ibdata 와 iblogs 폴더를 생성합니다.
   ㄴ) my.ini 파일 설정을 변경
      * innodb_buffer_pool_size
        - 현재 자신의 시스템 메모리의 50~80% 사이로 만듭니다.
           x86 시스템에서는 2G 이상 설정을 할 수 없습니다.
      * innodb_additional_mem_pool_size
        - 데이터 사전정보 나 내부의 데이터 구조 정보를 담는 메모리 입니다.
           보통 2M 정도 잡아주면 아주 많은 테이블을 사용한다면 좀 늘려주시면 됩니다.
           만약 메모리공장이 부족하면 error log 에 warning 메서지를 남기니 그때 늘려주세요
      * innodb_flush_log_at_trx_commit
        - insert, update 등 데이터 삽입과 관계가 있습니다.
           commit 을 하였을때 그 즉시 commit 된 데이터를 log file 에 기록할지 안할지를 설정합니다.
           로그파일을 기록할 경우 갑작스러운 경우 데이터 손실을 막을 수 있지만 매번 로그를 기록하므로 속도가 저하됩니다.
           1 일경우 기록을 하는것이고, 0일 경우 기록을 안하는것입니다.
      * innodb_log_file_size
        - 트랜잭션을 기록하는 로그 파일의 크기를 결정하는 옵션입니다.
           inno_buffer_pool_size 옵션은 성능을 위한것이지만 시스템이 다운되었을 경우 데이터가 손실이 되므로
           이것을 방지하기 위해 log file 을 만들어서 commit 될때마다 로그에 기억을 하고 자동복구를 합니다.
           로그파일은 무한정 계속 커지는 것이 아니라 일정한 크기와 갯수를 가지고 순환식으로 처리되므로 크기는
           inno_buffer_pool_size 의 15% 정도로 설정을 합니다.
           만약 메모리가 1기가이면 inno_buffer_pool_size = 512M 이고, innodb_log_file_size = 80M 가 됩니다.
      * innodb_log_buffer_size
        - 로그 파일을 기록하기 위한 버퍼 사이즈입니다.
          트랜잭션이 작거나 거의 없다면 크게 잡는것은 낭비이므로 보통 1M~8M 사이로 설정을 합니다.
      [mysqld]
      innodb_data_home_dir="C:/MySQL/MySQL Server 5.0/ibdata/"
      innodb_log_group_home_dir="C:/MySQL/MySQL Server 5.0/iblogs"
      innodb_data_file_path=ibdata1:10M:autoextend:max:1000M
      innodb_additional_mem_pool_size=3469K
      innodb_flush_log_at_trx_commit=1
      innodb_log_buffer_size=2M
      innodb_buffer_pool_size=256M
      innodb_log_file_size=40M
      innodb_thread_concurrency=8
      innodb_log_archive=0

   ㄷ) my.ini 을 수정했으면 mysql 서버를 재시작합니다.

 3) InnoDB 테이블 만들기   create table test_inno (
   ~
   )type=innodb;
   으로 맨 마지막에 type=innodb; 라고 명시해주시면 됩니다.

 4) InnoDB 트랜잭션 사용
  ㄱ) 트랜잭션을 사용하기 위해서는 처음에 set autocommit=0; 이나 begin; 을 선언해야 합니다.
      선언 후 데이터 변경이 있을 때, 이상이 없을 경우는 commit를 하고, 이상이 있을 경우 rollback을 실행합니다.
      오라클이랑 비슷하다고 보시면 되요
      mysql>set autocommit=0; //begin; 같음      mysql>insert into test_inno values (1,'aaa');      mysql>select * from test_inno; //현재창에서는 입력한 내용이 보이지만 다른창에서는 보이지않음      mysql>commit; //다른창에서 select를 할경우 입력한 값이 보임
4. InnoDB 테이블 스페이스
 좀전에 MyISAM 과 차이점이 InnoDB 는 테이블과 인덱스를 테이블 스페이스 라는곳에 저장을 한다고 하였습니다.
 그런데 사용을 하다보니 점차 DB가 늘어나서 테이블 스페이스가 FULL 이 발생하는 경우가 생깁니다.
 4.0.x 버전부터는 일일이 할 필요가 없다는 글이 있는데 그래도 FULL이 발생하였을 때 대처방법을 알면 좋겠죠
 my.ini 에서 아래와 같이 추가를 해주면 안됩니다.
 innodb_data_file_path = /ibdata/ibdata1:1000M:autoextend 라고 할 경우
 innodb_data_file_path = /ibdata/ibdata1:1000M;/ibdata/ibdata2:1000M:autoextend 라고 ibdata2를 추가하면 안됩니다.
 아래와 같은 과정대로 하세요
 1. Use mysqldump to dump all your InnoDB tables.
 2. Stop the server.
 3. Remove all the existing tablespace files.
 4. Configure a new tablespace.
 5. Restart the server.
 6. Import the dump files.
 즉, 일단 mysqldump 로 InnoDB 테이블의 전체 덤프뜬 다음 MySQL 서버를 중지시킵니다.
 그다음 테이블스페이스 파일을 모두 지우고 테이블스페이스를 아래와 같이 추가를 합니다.
 innodb_data_file_path = /ibdata/ibdata1:1000M;/ibdata/ibdata2:1000M:autoextend
 그다음 MySQL 서버를 구동시킨다음 처음에 덤프뜬 파일을 import 하시면 됩니다.
5. 마지막으로
 위의 단계처럼 참 어렵게 설정을 하였지만, mysql.com 사이트에 들러보시면 MySQL GUI Tools 라는 프로그램이 있습니다.
 물론 무료로 다운로드 가능하고요, 이 프로그램을 설치를 하면 MySQL Administrator 라는 것이 있는데 이것을 통해서
 아주 쉽게 MySQL 의 설정을 변경하실 수 있습니다.

MySQL : InnoDB 테이블을 OPTIMIZE 하려고? 잠깐~!

MySQL : InnoDB 테이블을 OPTIMIZE 하려고? 잠깐~! (번역) 무제 2010/12/29 17:21 http://blog.naver.com/ez_/140120667237 원문 : Thinking about running OPTIMIZE on your Innodb Table ? Stop! / Posted by Peter Zaitsev 링크 : http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/ 출처 : MySQL Performance Blog ( http://www.mysqlperformanceblog.com ) 번역 : 박현우 (ez.amiryo@gmail.com / @lqez) InnoDB/XTraDB 테이블은 수시로 재구성을 함으로써 성능적인 혜택을 얻는다. 데이터를 물리적으로 Primary Key 순서에 맞게 나열할 뿐 아니라, Primary Key와 인덱스의 페이지도 재배열하여 더 나은 성능과 더 적은 공간 소비를 가능하게 한다. 허나, 무작정 OPTIMIZE TABLE을 수행하는 것은 최선의 방법이 아니다. 만약 Percona Server with XtraDB에서 InnoDB 플러그인을 사용중이라면, '삽입' 방식이 아닌 '정렬' 방식의 인덱스를 생성할 수 있는 '멋진' 기능을 통해 이득을 볼 수 있다. 이 작업은 UUID와 같은 무작위 순으로 입력된 대용량의 인덱스에 대해 특별히 빠르게 수행될 수 있을 뿐 아니라, 더 효율적인 공간 활용을 가능하게 한다. 문제는... InnoDB 테이블에 대한 OPTIMIZE TABLE 명령은 이유를 불문하고 그러한 장점을 얻을 수 없다. 버퍼 풀에 할당된 메모리보다 10배 정도 큰 테이블에 대해 최적화 작업을 수행한 아래 벤치마크를 살펴보자. SQL: CREATE TABLE `a` (   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,   `c` char(64) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `c` (`c`) ) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1   mysql> SELECT * FROM a ORDER BY id LIMIT 10; +----+------------------------------------------+ | id | c                                        | +----+------------------------------------------+ |  1 | 813cf02d7d65de2639014dd1fb574d4c481ecac7 | |  2 | 62960f5d5d50651e5a5983dacaedfa9a73a9ee87 | |  3 | cea33998792ffe28b16b9272b950102a9633439f | |  4 | 8346a7afa0a0791693338d96a07a944874340a1c | |  5 | b00faaa432f507a0d16d2940ca8ec36699f141c8 | |  6 | 8e00926cf6c9b13dc8e0664a744b7116c5c61036 | |  7 | f151fe34b66fd4d28521d5e7ccb68b0d5d81f21b | |  8 | 7fceb5afa200a27b81cab45f94903ce04d6f24db | |  9 | 0397562dc35b5242842d68de424aa9f0b409d60f | | 10 | af8efbaef7010a1a3bfdff6609e5c233c897e1d5 | +----+------------------------------------------+ 10 rows IN SET (0.04 sec)   # This is just random SHA(1) hashes   mysql> OPTIMIZE TABLE a; +--------+----------+----------+-------------------------------------------------------------------+ | TABLE  | Op       | Msg_type | Msg_text                                                          | +--------+----------+----------+-------------------------------------------------------------------+ | test.a | OPTIMIZE | note     | TABLE does NOT support OPTIMIZE, doing recreate + analyze instead | | test.a | OPTIMIZE | STATUS   | OK                                                                | +--------+----------+----------+-------------------------------------------------------------------+ 2 rows IN SET (3 hours 3 min 35.15 sec)   mysql> ALTER TABLE a DROP KEY c; Query OK, 0 rows affected (0.46 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> OPTIMIZE TABLE a; +--------+----------+----------+-------------------------------------------------------------------+ | TABLE  | Op       | Msg_type | Msg_text                                                          | +--------+----------+----------+-------------------------------------------------------------------+ | test.a | OPTIMIZE | note     | TABLE does NOT support OPTIMIZE, doing recreate + analyze instead | | test.a | OPTIMIZE | STATUS   | OK                                                                | +--------+----------+----------+-------------------------------------------------------------------+ 2 rows IN SET (4 min 5.52 sec)   mysql> ALTER TABLE a ADD KEY(c); Query OK, 0 rows affected (5 min 51.83 sec) Records: 0  Duplicates: 0  Warnings: 0 어떤가?! 테이블 최적화를 그냥 수행하면 3시간도 넘게 걸리던 것에 비해, Primary Key를 제외한 인덱스를 내리고 테이블 최적화를 진행한 후에 다시 인덱스를 복원하는 작업은 10분 밖에 걸리지 않는다. 거의 20배나 빠를 뿐 아니라, 최종적으로 더 작은 크기의 인덱스를 얻을 수 있다. 따라 서, 만약 이 트릭을 이용해 테이블 최적화를 수행하고자 한다면, 테이블이 인덱스 없이 노출되어도 괜찮은 슬레이브 단에서 작업하는 것이 좋다. 단, InnoDB 테이블에 락을 걸 때에, 인덱스 없이 테이블을 읽는 많은 수의 쿼리들로 인해 컴퓨터(box)가 멈추는 일이 없어야 함을 보장해야 함을 유의하라. 이 트릭을 테이블 재생성을 필요로 하는 ALTER TABLE 시에도 활용할 수 있다. (역주:MySQL은 ALTER TABLE 작업을 새로운 스키마를 가지는 임시 테이블 생성 > 임시 테이블로 복제 > 기존 테이블 DROP > 임시 테이블의 이름을 기존 테이블의 이름으로 변환하는 식으로 수행한다) 모든 인덱스를 제거하고, ALTER를 수행한 후에 인덱스를 복원하는 것이 그냥 ALTER TABLE을 수행하는 것보다 훨씬 빠르다. 덧) 왜 이런 경우에 정렬을 통해 인덱스를 생성하는 기능이 지원되지 않는지 모르겠다. 고수준 명령어나 도구들(예를 들어 mysqldump)이 왜 인덱스를 만드는데 느리디 느린 '삽입 방식'을 사용해, 이러한 장점을 가질수 없게 만들어졌는지 이해할 수 없다. ---- 과거 MySQL로 MMORPG의 대용량 로그 데이터를 다루던 경험에 비추어 보아도, 아래의 동작들은 정말 이해가 가지 않았다. (1) Row의 용량이 줄어드는 ALTER TABLE의 경우에도 위에서 언급한 것과 같이 전체 rebuilding을 하는 것. (2) dump된 데이터를 다시 복원할 때에, CREATE INDEX를 나중에 수행하지 않아 느린 것. -> 생각해보면 MyISAM에 대해서는  /*!40000 ALTER TABLE [table] DISABLE KEYS */; 을 통해서 이미 하고 있었네.

MYSQL 한글입력

MySQL-5.0 설치후 터미널에서 한글입력이 안되거나 한글이 깨져보이는 경우 다음과 같이 해결했습니다. 저와 같은 문제에 있었던 분에게 도움이 될지 모르겠네요.
* 테스트환경 - 리눅스 Fedora core3, mysql: 5.0.18 버전
1. 소스컴파일시 옵션 ( euckr, readline 추가해줌)
./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --with-charset=euckr --with-readline

2. 홈 디렉토리의 .bashrc 파일에 다음 두줄을 추가합니다.
export LANG=ko_KR.EUC-KR
export LANGUAGE=ko_KR:ko:en_GB:en

3. 저장후 쉘> source .bashrc 를 수행합니다.
4. mysql데몬실행후 접속해서 콘솔에서 한글 입력하면 됩니다.

Network site

http://www.joinc.co.kr/modules/moniwiki/wiki.php/FrontPage

Mysql - explain

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>