將原來windows環(huán)境中使用phpstudy搭建的MySQL 5.5.53 中的數(shù)據(jù)遷移至新主機Linux環(huán)境中
讓客戶滿意是我們工作的目標(biāo),不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶,將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴,公司提供的服務(wù)項目有:主機域名、虛擬空間、營銷軟件、網(wǎng)站建設(shè)、五蓮網(wǎng)站維護、網(wǎng)站推廣。
新主機
系統(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 |
+----------------------------+--------+
舊主機:
系統(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
我看了,也是用的mysqldump操作的。
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
在導(dǎo)出的表結(jié)構(gòu)zentao.sql中找到ENGINE=MyISAM,修改成ENGINE=InnoDB,至于你用什么方法替換,看你喜歡了。
# vim zentao.sql
:%s/ENGINE=MyISAM/ENGINE=InnoDB/g
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
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 |
+--------------+------------+-----------+--------+
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.其實已經(jīng)執(zhí)行成功了。5.6.X的版本,其實已經(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ù)庫方法同樣操作即可。
網(wǎng)頁題目:遷移phpstudy中的mysql至Linux
分享網(wǎng)址:http://muchs.cn/article16/pihhdg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供外貿(mào)網(wǎng)站建設(shè)、手機網(wǎng)站建設(shè)、用戶體驗、Google、虛擬主機、定制開發(fā)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)