8535.com-新浦京娱乐场官网|欢迎您

新浦京娱乐场官网将phpstudy中的mysql迁移至Linux教

来源:http://www.dnamique.com 作者:计算机网络 人气:126 发布时间:2020-04-19
摘要:项目目的 将原来windows环境中使用phpstudy搭建的mysql 5.5.53中的数据迁移至新主机Linux环境中 环境情况 新主机系统平台: CentOS release 7.4 (Final) 内核 3.10.0-693.el7.x86_64 mysql环境: mysql statusS

项目目的

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

环境情况

新主机系统平台:

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

mysql环境:

mysql statusServer version: 5.6.39-log MySQL Community Server (GPL)Server characterset: utf8Db characterset: utf8Client characterset: utf8Conn. 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: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8

新浦京娱乐场官网,mysql show variables like '%storage_engine%';+------------------------+--------+| Variable_name | Value |+------------------------+--------+| default_storage_engine | MyISAM || storage_engine | MyISAM |+------------------------+--------+

表的存储引擎

mysql show table status from databaseG;Engine: InnoDBEngine: 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 zentaoG;*************************** 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 zentaoG;*************************** 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 |

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

本文由8535.com-新浦京娱乐场官网|欢迎您发布于计算机网络,转载请注明出处:新浦京娱乐场官网将phpstudy中的mysql迁移至Linux教

关键词:

最火资讯