Tutorial on migrating mysql from phpstudy to Linux

Project Purpose

Migrate the data in MySQL 5.5.53 built with phpstudy in the original Windows environment to the new host Linux environment

Environmental conditions

New host 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 |
+------------------------+--------+

The storage engine of the table

mysql> show table status from database\G;
Engine: InnoDB
Engine: MyISAM

Migration Process

1. Use phpstudy's own tools to export each database

image

I saw that mysqldump was also used for the operation.

2. If you just want to keep the original table engine, you can do the following

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 table 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 how you replace it, it depends on your preference.

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

4. Import data into the specified database

mysql> use zentao;
mysql> source zentao.sql;

Changed 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. But there is a problem. When checking the detailed information 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. Defragment the 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 |
+-------------------+----------+----------+-------------------------------------------------------------------+

It prompts that the table does not support optimize, but it shows OK below. In fact, it has been executed successfully. 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 |

The same operation can be performed for multiple database methods.