MySQL database MyISAM storage engine to InnoDB

The company's database storage engine was all MyISAM, data volume and access is not very large, so there is no problem. However, the recent occurrence of the MySQL data table is often locked, directly caused the user to connect the site timeout and return 502, and decided to switch the storage engine to InnoDB to solve the MyISAM table lock problem. The following steps are recorded.

Core code

To modify a table's storage engine:

The code is as follows	Copy Code
ALTER TABLE TableName TYPE=INNODB

cases

1, export the table structure of CentOS database

The code is as follows	Copy Code
1.mysqldump-d-uxxx-p CentOS > Centos_table.sql

Where the-d parameter indicates that no data is exported, only the table structure is exported

2, replace the MyISAM in Centos_table.sql as InnoDB

The code is as follows	Copy Code
1.sed-i ' s/myisam/innodb/g ' Centos_table.sql

3, new database centos_new, and import table structure

The code is as follows	Copy Code
1.mysql > CREATE Database centos_new; 2.mysql-uroot-p Centos_new < Centos_table.sql

You can check whether the table engine is innodb by Show table status.

4, export the CentOS data

The code is as follows	Copy Code
1.mysqldump-t-uroot-p CentOS > Centos_data.sql

Where the-t parameter represents only the data, does not guide the table structure
5, import data to Centos_new

The code is as follows	Copy Code
1.mysql-uroot-p Centos_new < Centos_data.sql

Finally, if you want to change the Centos_new database name to CentOS

Comparison of MyISAM and InnoDB storage engines

Main differences:

MyISAM is not a transaction-safe type, and InnoDB is a transaction-safe type.
The granularity of the MyISAM lock is the table level, while the INNODB supports row-level locking.
MyISAM supports full-text type indexing, while InnoDB does not support Full-text indexing.
MyISAM is relatively simple, so the efficiency is better than INNODB, small applications can consider the use of MyISAM.
myisam tables are stored in the form of files, and using MyISAM storage in Cross-platform data transfer saves a lot of hassle.
InnoDB tables are more secure than MyISAM tables, and you can switch non transaction tables to transaction tables (ALTER TABLE tablename TYPE=INNODB) without the data being lost.

Application Scenario:

MyISAM Manage non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If you need to perform a large number of select queries in your application, then MyISAM is a better choice.
InnoDB is used for transactional applications with a number of features, including ACID transaction support. If you need to perform a large number of insert or update operations in your application, you should use InnoDB, which can improve the performance of multiple-user concurrent operations.