하나씩 설치하다가 잘안되서...
깔끔하게 APM_Setup 으로 설치하였다 ~! (XP환경)
[InnoDB]
1. InnoDB가 사용가능한지 확인해본다.
mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | YES |
+---------------+-------+
1 row in set (0.00 sec)
!!! 여기서 Value에 Disable라고 뜬다면, my.ini를 열어서...
# BDB타입을 사용할지 하지 않을지
#skip-bdb <- 이부분 앞에 #으로 바꿔주고 mysql 재시작 하면 된다 ~
2. 설정 상태 확인한다.
mysql> show status like '%innodb%';
+-----------------------------------+---------+
| Variable_name | Value |
+-----------------------------------+---------+
| Com_show_innodb_status | 0 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 29 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
+-----------------------------------+---------+
43 rows in set (0.00 sec)
Value에 설정된 값은 아마 my-innodb-heavy-4G.ini 를 편집하면 바뀔것 같다.
지금은 그냥 해보는게 중요해서..ㅎㅎ 건너뜀 ~ㅎ
3. 기존의 테이블 엔진 바꾸기
먼저 테이블 속성을 보자 ~
mysql> use testdb;
mysql> show table status where name='inno_test';
+-----------+--------+---------+------------+------+----------------+-----------
--+-------------------+--------------+-----------+----------------+-------------
--------+---------------------+------------+-----------------+----------+-------
---------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_lengt
h | Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Collation | Checksum | Create
_options | Comment |
+-----------+--------+---------+------------+------+----------------+-----------
--+-------------------+--------------+-----------+----------------+-------------
--------+---------------------+------------+-----------------+----------+-------
---------+---------+
| inno_test | MyISAM | 10 | Fixed | 1 | 65 | 6
5 | 18295873486192639 | 2048 | 0 | NULL | 2009-11-20 1
5:20:35 | 2009-11-20 15:24:25 | NULL | utf8_general_ci | NULL |
| |
+-----------+--------+---------+------------+------+----------------+-----------
--+-------------------+--------------+-----------+----------------+-------------
--------+---------------------+------------+-----------------+----------+-------
---------+---------+
1 row in set (0.00 sec)
MyISAM 엔진이다. 이제 InnoDB로 변경 !!!
mysql> alter table inno_test type=innodb;
Query OK, 1 row affected, 1 warning (0.11 sec)
레코드: 1개 중복: 0개 경고: 0개
mysql> show table status where name='inno_test';
+-----------+--------+---------+------------+------+----------------+-----------
--+-----------------+--------------+-----------+----------------+---------------
------+-------------+------------+-----------------+----------+----------------+
----------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_lengt
h | Max_data_length | Index_length | Data_free | Auto_increment | Create_time
| Update_time | Check_time | Collation | Checksum | Create_options |
Comment |
+-----------+--------+---------+------------+------+----------------+-----------
--+-----------------+--------------+-----------+----------------+---------------
------+-------------+------------+-----------------+----------+----------------+
----------------------+
| inno_test | InnoDB | 10 | Compact | 1 | 16384 | 1638
4 | 0 | 16384 | 0 | NULL | 2009-11-20 15:
20:35 | NULL | NULL | utf8_general_ci | NULL | |
InnoDB free: 4096 kB |
+-----------+--------+---------+------------+------+----------------+-----------
--+-----------------+--------------+-----------+----------------+---------------
------+-------------+------------+-----------------+----------+----------------+
----------------------+
1 row in set (0.00 sec)
InnoDB로 변경이 되었다 ~
4. 트랜잭션. rollback, commit 해보기
mysql> select * from inno_test;
+------+------+
| a | b |
+------+------+
| 2 | cho |
+------+------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update inno_test set a=3;
Query OK, 1 row affected (0.00 sec)
일치하는 Rows : 1개 변경됨: 1개 경고: 0개
mysql> select * from inno_test;
+------+------+
| a | b |
+------+------+
| 3 | cho |
+------+------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from inno_test;
+------+------+
| a | b |
+------+------+
| 2 | cho |
+------+------+
1 row in set (0.00 sec)
업데이트 쿼리를 하기전에 begin;을 입력하고 쿼리를 수행하면,
실제 데이터가 바로 바뀌지 않는다.
commit; 이 들어오면 실제로 바뀌게 되고 rollback;이 들어오면 적용하지 않게 된다.
이제는 commit;을 해보자.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update inno_test set a=3;
Query OK, 1 row affected (0.00 sec)
일치하는 Rows : 1개 변경됨: 1개 경고: 0개
mysql> select * from inno_test;
+------+------+
| a | b |
+------+------+
| 3 | cho |
+------+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from inno_test;
+------+------+
| a | b |
+------+------+
| 3 | cho |
+------+------+
1 row in set (0.00 sec)
적용이 되었다 ~
트랜잭션에 대해서 좀 더 공부해야겠다 ~~~!