Implementation of MySQL Modifying Default Storage Engine

mysql storage engine:

The MySQL server adopts a modular style, and each part remains relatively independent, especially in the storage architecture. The storage engine is responsible for managing data storage and index management of MySQL. With the defined API, the MySQL server can communicate with the storage engine. At present, MyISAM and InnoDB are most used. After InnoDB is acquired by Oracle, Falcon, a new storage engine developed by MySQL, will be introduced in MySQL version 6.0.

MyISAM engine is a non-transactional engine, which provides high-speed storage and retrieval, as well as full-text search capability, and is suitable for applications with frequent queries such as data warehouse. In MyISAM, one table is actually saved as three files,. frm stores table definitions,. MYD stores data, and. MYI stores indexes.

InnoDB is an engine that supports transactions. All data is stored in one or more data files and supports a locking mechanism similar to Oracle. 1 is widely used in OLTP applications. If the InnoDB configuration option is not specified, MySQL creates one auto-extended data file named ibdata1 and two log files named ib_logfile0 and ib_logfile1 under the MySQL data directory.

When creating table, you can specify the storage engine to use by engine keyword. If omitted, use the system default storage engine: CREATE TABLE t (i INT) ENGINE = MYISAM;

View the types of storage engines supported in the system:

mysql> show engines;| Engine | Support | Comment || MyISAM | YES | Default engine as of MySQL 3.23 with greatperformance | 

 | MEMORY | YES | Hash based, stored in memory, useful for temporarytables | 

 | InnoDB | DEFAULT | Supports transactions, row-level locking, andforeign keys | 

 | BerkeleyDB | NO | Supports transactions and page-level locking| 

 | BLACKHOLE | NO | /dev/null storage engine (anything you write toit disappears) | 

 | EXAMPLE | NO | Example storage engine | 

 | ARCHIVE | YES | Archive storage engine | 

 | CSV | NO | CSV storage engine | 

 | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables| 

 | FEDERATED | NO | Federated MySQL storage engine | 

 | MRG_MYISAM | YES | Collection of identical MyISAM tables | 

 | ISAM | NO | Obsolete storage engine |12 rows in set (0.00 sec) 

Only partial engine support is provided in the standard installer. If you need to use other storage engines, you need to recompile with source code and different parameters. Where DEFAULT indicates the default storage engine of the system, which can be changed by modifying configuration parameters:

default-storage-engine=MyISAM

View specific information about a storage engine

mysql > show engine InnoDB status\G;

1 Installation system defaults to INNODB

default-storage-engine=INNODB

1. You can add the default-storage-engine or default-table-type options to the command line when you start the database server.

2. A more flexible approach is to specify the storage engine to use when the MySQL client is shipped with the MySQL server. The most direct way is to specify the type of storage engine when creating the table, as follows:

CREATE TABLE mytable (id int, titlechar(20)) ENGINE = INNODB

Modify the storage engine of the table:

ALTER TABLE engineTest ENGINE = INNODB;

Modify the default storage engine:

In the mysql configuration file (is/etc/my. cnf under linux), add default-storage-engine=INNODB after mysqld.

However, if the table is MyISAM when it is established, to change the storage engine of the whole database table, it is generally complicated to modify one table and one table. You can export the database first to get SQL, modify MyISAM to INNODB, and then import it.