項目目的

將原來windows環(huán)境中使用phpstudy搭建的mysql 5.5.53 中的數(shù)據(jù)遷移至新主機(jī)Linux環(huán)境中

環(huán)境情況

新主機(jī)

系統(tǒng)平臺：

CentOS release 7.4 (Final)???????????? 內(nèi)核? 3.10.0-693.el7.x86_64

mysql環(huán)境：

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 |

+----------------------------+--------+

舊主機(jī)：

系統(tǒng)平臺：

Windows 2012 R2 SE X64

mysql環(huán)境：

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自帶的工具進(jìn)行每個數(shù)據(jù)庫導(dǎo)出

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.將原有數(shù)據(jù)庫中的表引擎變更為InnoDB

在導(dǎo)出的表結(jié)構(gòu)zentao.sql中找到ENGINE=MyISAM,修改成ENGINE=InnoDB,至于你用什么方法替換，看你喜歡了。

# vim zentao.sql

:%s/ENGINE=MyISAM/ENGINE=InnoDB/g

4.導(dǎo)入數(shù)據(jù)到指定數(shù)據(jù)庫

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.但是有一個問題，查看表的詳細(xì)信息時發(fā)現(xiàn)Data_free不為零，說明存在數(shù)據(jù)碎片，需要進(jìn)行優(yōu)化

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.其實(shí)已經(jīng)執(zhí)行成功了。5.6.X的版本，其實(shí)已經(jīng)支持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 |

多個數(shù)據(jù)庫方法同樣操作即可。

總結(jié)

以上是生活随笔為你收集整理的php mysql 迁移_将phpstudy中的mysql迁移至Linux教程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。