テーブルを作成する時に、明示的にストレージエンジンを毎回指定
して作成しているか、既定のストレージエンジンを意図したものに設定して
いれば問題は無いが、他のDBと違いMYSQLには色々なストレージエンジン
があるのでオブジェクト作成する時には少しだけ考慮する事が必要。

+—————-+——–+
| Variable_name | Value |
+—————-+——–+
| storage_engine | MyISAM |
+—————-+——–+


mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> CREATE TABLE default_engine (id INT);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'default_engine'\G
*************************** 1. row ***************************
Name: default_engine
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: 2009-07-22 14:18:46
Update_time: 2009-07-22 14:18:46
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> SET storage_engine = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE set_default_engine (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLE STATUS LIKE 'set_default_engine'\G
*************************** 1. row ***************************
Name: set_default_engine
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: 11534336
Auto_increment: NULL
Create_time: 2009-07-22 14:19:14
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql>

set_engine


mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

mysql> CREATE TABLE set_default_engine_manu (id INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'set_default_engine_manu'\G
*************************** 1. row ***************************
Name: set_default_engine_manu
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: 2009-07-22 14:24:17
Update_time: 2009-07-22 14:24:17
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)

mysql>

set_engine_man

※ 稼動中に以下のコマンドでディフォルトストレージエンジンを変更可能。


mysql> set global storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> set @@global.storage_engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2009-07-23 00:14:31 |
+---------------------+
1 row in set (0.00 sec)

設定はDBに接続しなおすか、新規接続から有効になります。
しかしオプションファイルに記入しておかないと、
再起動と同時にディフォルトエンジンは元に戻ります。
コマンド:      mysqld –default-storage-engine=InnoDB
オプションファイル:  default-storage-engine=InnoDB
特定セッション: SET SESSION storage_engine=InnoDB;

※新規セッションから有効
set_global1
※再起動後の確認

[root@colinux ~]# /etc/init.d/mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@colinux ~]#
[root@colinux ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.01 sec)

mysql>

Comments are closed.

Post Navigation