我要评论项目目的
将原来windows环境中使用phpstudy搭建的mysql 5.5.53 中的数据迁移至新主机linux环境中
环境情况
新主机
系统平台：
centos release 7.4 (final) 内核 3.10.0-693.el7.x86_64
mysql环境：
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 |
+----------------------------+--------+
旧主机：
系统平台：
windows 2012 r2 se x64
mysql环境：
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 |
+------------------------+--------+
表的存储引擎
mysql> show table status from database\g;
engine: innodb
engine: myisam
迁移过程
1.使用phpstudy自带的工具进行每个数据库导出
image
我看了，也是用的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;
表引擎变更为innodb
mysql> 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 |
多个数据库方法同样操作即可。
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论