迁移phpstudy中的mysql至Linux

项目目的

将原来windows环境中使用phpstudy搭建的mysql 5.5.53 中的数据迁移至新主机Linux环境中

环境情况

新主机系统平台：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

迁移过程

1.使用phpstudy自带的工具进行每个数据库导出

我看了，也是用的mysqldump操作的。

2.如果只是保留原本的表引擎，那么直接以下操作即可

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.将原有数据库中的表引擎变更为InnoDB

在导出的表结构zentao.sql中找到ENGINE=MyISAM,修改成ENGINE=InnoDB,至于你用什么方法替换，看你喜欢了。

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

4.导入数据到指定数据库

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.但是有一个问题，查看表的详细信息时发现Data_free不为零，说明存在数据碎片，需要进行优化

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.整理有碎片的表

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                                                                |+-------------------+----------+----------+-------------------------------------------------------------------+提示该表不支持 optimize,但是下边有显示OK.其实已经执行成功了。5.6.X的版本，其实已经支持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 |

多个数据库方法同样操作即可。