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

This article shows you how to migrate the mysql in phpstudy to the Linux system, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Environmental condition

New mainframe

System platform:

CentOS release 7.4 (Final) kernel 3.10.0-693.el7.x86_64

Mysql environment:

Mysql > status

Server version: 5.6.39-log MySQL Community Server (GPL)

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. Characterset: utf8

Mysql > 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 X64

Mysql environment:

Server version: 5.5.53 MySQL Community Server (GPL)

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. Characterset: utf8

Mysql > show variables like'% storage_engine%'

+-+ +

| | Variable_name | Value |

+-+ +

| | default_storage_engine | MyISAM |

| | storage_engine | MyISAM |

+-+ +

Storage engine of the table

Mysql > show table status from database\ G

Engine: InnoDB

Engine: MyISAM

Migration process

1. Export each database using the tools that come with phpstudy

Image

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

two。 If you just 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\ G

* * 1. Row *

Name: zt_action

Engine: MyISAM

Version: 10

Row_format: Dynamic

3. 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/g

4. Import data to the specified database

Mysql > use zentao

Mysql > source zentao.sql

Change the table engine to InnoDB

Mysql > show table status from zentao\ G

* * 1. Row *

Name: zt_action

Engine: InnoDB

Version: 10

Row_format: Compact

5. However, there is a problem. When viewing the details of the table, it is found that the 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. A fragmented watch

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 | |

+-+

It is suggested that the table does not support optimize, but there is an OK. As a matter of fact, it has been successfully implemented. Version 5.6.X already 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 | |

The above content is how to migrate mysql from phpstudy to Linux system. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.