Shulou(Shulou.com)05/31 Report--

Production database MyISAM storage engine to Innodb process is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

When the mysql database storage engine is MyISAM, the data table may be locked in the case of a large number of visits, which will cause users to time out when connecting to the website and return 502. at this time, it is necessary to convert the MySQL database MyISAM storage engine to Innodb.

Today, we mainly share the recent data migration to a database, including storage engine conversion and database coding conversion.

1. Export table structure and table data of CentOS database

Export the table structure (the-d parameter means no data is exported, only the table structure is exported)

Mysqldump-d-h xx.xx.xx-uroot-p zentao > zentao_table.sql

Export table data (- t parameter indicates only data, not table structure)

Mysqldump-t-h xx.xx.xx-uroot-p zentao > zentao_tabledata.sql

two。 Modify the database engine

Here replace MyISAM in zentao_table.sql with INNODB

Sed-I's Universe MyISAM Universe INODB zentao_table.sql

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

-- to avoid too many unstable factors due to coding problems in migration, set it to utf8 first.

Mysql-uroot-p-e 'create database zentao default character set utf8;' mysql-uroot-p zentao

After import, check whether the table engine is INNODB through show table status.

4. Import data

Pay attention to the sql_mode level when importing data here. Some date types may be in the format of 0000-00-00, so you need to remove the no_zero_date parameter.

The final sql_mode level is: sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Mysql-uroot-p zentao

Manually modify the sql code to utf8mb4-- > import the intermediate database (only store the modified table structure)-> synchronize the structure from the intermediate database to the destination database

After the migration, the connection was applied and the function test was successful.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.