migrating MySQL to Linux in Phpstudy

Purpose of the project

Migrating data from MySQL 5.5.53 built with Phpstudy in the original Windows environment to a new host Linux environment

Environmental situation

新主机系统平台：CentOS release 7.4 (Final)             内核  3.10.0-693.el7.x86_64mysql环境：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 |+----------------------------+--------+

旧主机：系统平台：Windows 2012 R2 SE X64mysql环境：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 |+------------------------+--------+表的存储引擎mysql> show table status from database\G;Engine: InnoDBEngine: MyISAM

Migration Process 1. Export each database using Phpstudy's own tools

I saw it, too, with the mysqldump operation.

2. If you only keep the original table engine, then the following actions can be

mysql> create database zentao;mysql> use zentao;mysql> source zentao20180413161534.sql;mysql> show tables;+-------------------+| Tables_in_zentao  |+-------------------+| zt_action         || zt_bug            || zt_build          |...原表引擎保持原样。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

In the exported table structure Zentao.sql found Engine=myisam, modified to Engine=innodb, as you use what method to replace, see you like.

# vim zentao.sql:%s/ENGINE=MyISAM/ENGINE=InnoDB/g

4. Import data to a specified database

mysql> use zentao;mysql> source zentao.sql;表引擎变更为InnoDBmysql> show table status from zentao\G;*************************** 1. row ***************************           Name: zt_action         Engine: InnoDB        Version: 10     Row_format: Compact

5. There is a problem, however, when viewing the details of a table, it is found that data_free is nonzero, 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. Organize the fragmented tables

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 |+-------------------+----------+----------+------------ -------------------------------------------------------+ Tip The table does not support optimize, but there is a display OK below. In fact, it has been implemented successfully. 5.6.X version, in factInnoDB has been supported 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 do the same.

migrating MySQL to Linux in Phpstudy