Shulou(Shulou.com)06/01 Report--

Project purpose

Migrate the data from mysql 5.5.53 built with phpstudy in the original windows environment to the new host Linux environment

New host system platform: CentOS release 7.4 (Final) kernel 3.10.0-693.el7.x86_64mysql environment: mysql > statusServer version: 5.6.39-log MySQL Community Server (GPL) Server characterset: utf8Db characterset: utf8Client characterset: utf8Conn. Characterset: utf8mysql > show variables like'% storage_engine%' +-- +-+ | Variable_name | Value | +-+-+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | storage_engine | InnoDB | +-- + old host: system platform: Windows 2012 R2 SE X64mysql environment: Server version: 5.5.53 MySQL Community Server (GPL) Server characterset: utf8Db characterset: utf8Client characterset: utf8Conn. Characterset: utf8mysql > show variables like'% storage_engine%' +-+-+ | Variable_name | Value | +-+-+ | default_storage_engine | MyISAM | | storage_engine | MyISAM | + -+-+ table storage engine mysql > show table status from database\ G Engine: InnoDBEngine: MyISAM migration process 1. Export each database using the tools that come with phpstudy

I read it, and it is also operated by mysqldump.

two。 If you only keep the original table engine, you can do the following directly: mysql > create database zentao;mysql > use zentao;mysql > source zentao20180413161534.sql;mysql > show tables;+-+ | Tables_in_zentao | +-+ | zt_action | | zt_bug | | zt_build |. The original watch engine remains intact. Mysql > show table status from zentao\ Gbomber * 1. Row * * Name: zt_action Engine: MyISAM Version: 10 Row_format: Dynamic3. Change the table engine in the original database to InnoDB

Find ENGINE=MyISAM in the exported table structure zentao.sql and change it to ENGINE=InnoDB. As for what method you use to replace it, it depends on what you like.

# vim zentao.sql:%s/ENGINE=MyISAM/ENGINE=InnoDB/g4. Import data into the specified database mysql > use zentao;mysql > source zentao.sql; table engine to change to InnoDBmysql > show table status from zentao\ Gradication * 1. Row * * Name: zt_action Engine: InnoDB Version: 10 Row_format: Compact5. However, there is a problem. When viewing the details of the table, it is found that Data_free is not zero, indicating that there is data fragmentation and needs to be optimized mysql > select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema',' mysql') and data_free! = 0 +-+ | table_schema | table_name | data_free | engine | +-+ | zentao | zt_ Bug | 4194304 | InnoDB | | zentao | zt_history | 4194304 | InnoDB | +-+ 6. Defragmented table mysql > use zentao;mysql > optimize table zt_bug,zt_history +-+ | Table | Op | | Msg_type | Msg_text | +-| -- + | zentao.zt_bug | optimize | note | Table does not support optimize Doing recreate + analyze instead | | zentao.zt_bug | optimize | status | OK | | zentao.zt_history | optimize | note | Table does not support optimize Doing recreate + analyze instead | | zentao.zt_history | optimize | status | OK | +- -- + hint that the table does not support optimize But OK is shown below. As a matter of fact, it has been successfully implemented. Version 5.6.X actually supports Innodb mysql > select table_name,engine,table_rows,data_length+index_length length,DATA_FREE from information_schema.tables where TABLE_SCHEMA='zentao' and data_free = 0 +-+ | table_name | engine | table_rows | length | DATA_FREE | +- -+ | zt_bug | InnoDB | 1018 | 1589248 | 0 | | zt_history | InnoDB | 2584 | 1589248 | 0 |

Multiple database methods can be operated in the same way.

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.