Example tutorial of converting myisam to innodb in MySQL

Converting myisam to innodb requires modifying the storage engine of the table. If you do not modify myisam to innodb according to the standard method, it may cause database damage and data loss. Let’s look at the correct myisam conversion. innodb method.

<script>ec(2);</script>

Here is a brief introduction to the differences and conversion methods between the two:

MyISAM: MyISAM is the default database storage engine for versions prior to MySQL5.5. MYISAM provides high-speed storage and retrieval, as well as full-text search capabilities, and is suitable for applications with frequent queries such as data warehouses. But it does not support transactions or foreign keys. An important flaw of the MyISAM format is that data cannot be restored after the table is damaged.

InnoDB: InnoDB is the default database storage engine of MySQL version 5.5. However, InnoDB has been acquired by Oracle. Falcon, a new storage engine developed by MySQL, will be introduced in MySQL version 6.0. InnoDB is transaction safe with commit, rollback and crash recovery capabilities. However, compared to the MyISAM storage engine, InnoDB writes less efficiently and takes up more disk space to retain data and indexes. Nonetheless, InnoDB includes support for transactions and foreign keys, both of which are not available in the MyISAM engine.

MyISAM is suitable for: (1) Doing a lot of count calculations; (2) Insertions are infrequent and queries are very frequent; (3) There are no transactions.
InnoDB is suitable for: (1) situations where reliability requirements are relatively high, or transactions are required; (2) table updates and queries are quite frequent, and the chance of table locking is relatively high. (4) Servers with better performance, such as separate database servers, such as Alibaba Cloud's relational databaseRDS, recommend the InnoDB engine.

MySQL generally provides a variety of storage engines, which can be viewed by executing the following instructions:

First enter MySQLCommand line mode

View what storage MySQL provides Engine:
mysql> show engines;

View MySQL's current default storage engine:
mysql> show variables like '%storage_engine%';

Query wpsql library
mysql> use wpsql;

List all table names in the current library
mysql> show tables;

You need to see what engine is used for the wp_posts table (in the display results, the parameter after the engine It indicates the storage engine currently used by the table):
mysql> show create table wp_posts;

Modify the wp_posts table to the InnoDB storage engine (you can also use this command to replace InnoDB with MyISAM):
mysql> ALTER TABLE wp_posts ENGINE=INNODB;
If you want to change the storage engine of the entire database table, you generally need to modify one table by one, which is relatively cumbersome. You can export the database first, get SQL, and replace all MyISAM with INNODB. , and thenimport the database way.

Restart mysql after the conversion is completed
> service mysqld restart

The above is the detailed content of Example tutorial of converting myisam to innodb in MySQL. For more information, please follow other related articles on the PHP Chinese website!