MySQL database MyISAM storage engine to InnoDB

MySQL database MyISAM storage engine to InnoDB before the company's database storage engine is all MyISAM, the amount of data and access is not very large, so there is no problem. But recently the MySQL data table is often locked, directly caused the user to connect to the site time-out and return 502, so decided to switch the storage engine to InnoDB to solve the MyISAM table lock problem. The procedure is documented below.

1. Export the CentOS database table structure

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 centos_table.sql in the MyISAM for InnoDB

1. Sed-i ' s/myisam/innodb/g ' Centos_table.sql

3. Create a new database centos_new and import the table structure

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. Exporting CentOS Data

1. Mysqldump-t-uroot-p CentOS > Centos_data.sql

Where-t parameter indicates only data, non-guided table structure
5. Import data to Centos_new

1. Mysql-uroot-p Centos_new < Centos_data.sql

Finally, if you want to change the Centos_new database name to CentOS, you can refer to changing the MySQL database name

Reprint please indicate the article source: "https://www.centos.bz/2013/09/mysql-myisam-convert-to-innodb/"

MySQL database MyISAM storage engine to InnoDB