How to modify the default storage engine for MySQL, and modify the storage engine for mysql

How to modify the default storage engine for MySQL, and modify the storage engine for mysql

Mysql storage engine:

The MySQL server adopts a modular style, and each part remains relatively independent, especially in the storage architecture. The storage engine manages data storage and MySQL indexes. Through the defined API, the MySQL server can communicate with the storage engine. Currently, MyISAM and InnoDB are the most used. After InnoDB is acquired by Oracle, Falcon, a new storage engine developed by MySQL, will be introduced in MySQL.

The MyISAM engine is a non-transactional engine that provides high-speed storage and retrieval, as well as full-text search capabilities. It is suitable for applications with frequent queries such as data warehouses. In MyISAM, a table is actually saved as three files, the. frm storage table definition,. MYD storage data, and. MYI storage index.

InnoDB is an engine that supports transactions. Therefore, data is stored in one or more data files and supports Oracle-like locking mechanisms. It is widely used in OLTP applications. If the InnoDB configuration option is not specified, MySQL will create an automatic extended data file named ibdata1 and two log files named ib_logfile0 and ib_logfile1 under the MySQL data directory.

When creating a table, you can use the engine keyword to specify the storage ENGINE to use. If it is omitted, use the default storage engine: create table t (I INT) ENGINE = MYISAM;

View the supported storage engine types 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) 

The standard installer only supports some engines. To use other storage engines, you must use the source code and different parameters to re-compile. Here, DEFAULT indicates the DEFAULT storage engine of the system. You can modify the configuration parameters:

Default-storage-engine = MyISAM

View the details of a storage engine

Mysql> show engine InnoDB status \ G;

The default installation system is INNODB.

Default-storage-engine = INNODB

1. You can add the-default-storage-engine or-default-table-type option after the command line when starting the database server.

2. A more flexible way is to specify the storage engine used when the MySQL client is released along with the MySQL server. The most direct method is to specify the storage engine type when creating a table, as shown below:

Create table mytable (id int, titlechar (20) ENGINE = INNODB

Modify the storage engine of a table:

Alter table engineTest ENGINE = INNODB;

Modify the default storage engine:

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

However, if MyISAM is used to create a table and you want to change the storage engine of the entire database table, you generally need to modify one table and one table, which is cumbersome. You can export the database first to obtain the SQL statement, modify MyISAM to INNODB and then import it.

The above Implementation Method for modifying the default storage engine in MySQL is all the content that I have shared with you. I hope to give you a reference and support for the customer's house.