View and modify MySQL's default engine

1. mysql Storage engine:

A) The MySQL server has a modular style, and the parts remain relatively independent, especially in the storage architecture. The storage engine is responsible for managing the data store, as well as the MySQL index management. The API,MYSQL server is defined to communicate with the storage engine. The most used are MyISAM and InnoDB. After InnoDB was acquired by Oracle, the new storage Engine Falcon, developed by MySQL itself, will be introduced in the mysql6.0 version.

b) The MyISAM engine is a non-transactional engine that provides high-speed storage and retrieval, as well as full-text search capabilities, and is suitable for applications where queries are frequently used in data warehouses. In MyISAM, a table is actually saved as three files, ". frm storage table Definition", ". MyD store Data ",". Myi Storage Index ".

c) InnoDB is an engine that supports transactions. So the data is stored in one or more data files and supports a lock mechanism similar to that of Oracle. Generally used in OLTP applications more widely. If you do not specify the INNODB configuration option, MySQL will create an auto-extended data file named Ibdata1 in the MySQL data directory, along with two log files named Ib_logfile0 and Ib_logfile1.

2. Check the MySQL engine type:

In general, MySQL provides a variety of storage engines by default and can be viewed with the following commands:

A) See what storage engines MySQL has now provided:

Mysql> show engines;

b) View the current default storage engine for MySQL:

Mysql> Show variables like '%storage_engine% ';

c) See what engine is used for a table (the storage engine that is currently used by the parameter engine in the display result):

Mysql> Show create table table name;

3. Modify the MySQL storage engine:

A) Modify the table's storage engine:

ALTER TABLE enginetest ENGINE = INNODB;

b) Modify the default storage engine:

In the MySQL configuration file (Linux for/etc/my.cnf, if you customize the installation, the actual configuration file location, whichever is), add Default-storage-engine=innodb after mysqld. (The MySQL service needs to be restarted after the adjustment is complete)

c) Note: If the table is established when the MyISAM, to change the entire database table storage engine, a table is generally a table modification, more cumbersome. You can use the first to export the database, get SQL, change the MyISAM into InnoDB, and then import the way.

This article is from the "Frozen vs watermelon" blog, so be sure to keep this source http://molewan.blog.51cto.com/287340/1870211

View and modify MySQL's default engine