MySQL数据库MyISAM存储引擎转为Innodb

2014-5-5 liyangweb.com 李杨 MySQL

之前公司的数据库存储引擎全部为MyISAM，数据量和访问量都不是很大，所以一直都没什么问题。但是最近出现了MySQL数据表经常被锁的情况，直接导致了用户连接网站时超时而返回502，于是决定把存储引擎转为Innodb的，以解决MyISAM的表锁问题。下面将操作步骤记录一下。
1、导出my_table数据库的表结构

  1. mysqldump -d -uxxx -p my_data > my_table.sql

其中-d参数表示不导出数据，只导出表结构
2、替换my_table.sql里的MyISAM为INNODB

  1. sed -i 's/MyISAM/INNODB/g' my_table.sql

3、新建数据库table_new,并导入表结构

  1. mysql > create database table_new;
  2. mysql -uroot -p table_new < my_table.sql

可以通过show table status来检查表引擎是否为INNODB。
4、导出my_table的数据

  1. mysqldump -t -uroot -p centos > table_data.sql

其中-t参数表示只导数据，不导表结构
5、导入数据到table_new

  1. mysql -uroot -p table_new < table_data.sql

标签: MyISAM转Innodb

« PHP 如何阻止用户上传成人照片或者裸照 | 定时自动启动任务crontab命令用法»

评论：

nanterrelec
2019-02-09 03:25
<a href="http://www.acforlessllc.com/green-mens-nike-archive-83-skob">green mens nike archive 83</a> <a href="http://www.thehodostore.com/boy-knit-hats-pattern-3d-hata">boy knit hats pattern 3d</a> <a href="http://www.gandoff.com/air-jordan-17-sneaker-politics-reviews-runninge">air jordan 17 sneaker politics reviews</a> <a href="http://www.langbenlang.com/unisexo-nike-roshe-run-floral-plata-rosado-schuhea">unisexo nike roshe run floral plata rosado</a> <a href="http://www.glennloovens.com/nike-air-max-lunar-1-dam%C3%A4nner-rosa-schwarz-scarpeb">nike air max lunar 1 dam盲nner rosa schwarz</a> <a href="http://www.kamilotomotiv.com/nike-air-max-2019-green-uk-shoeso">nike air max 2019 green uk</a>
nanterrelec http://www.nanterrelec.com/
回复
nataliericcio
2019-02-06 03:50
<a href="http://www.placedumusee.com/youth-brandon-brooks-limited-olivecamo-nike-jersey-nfl-philadelphia-eagles-79-super-bowl-lii-2017-salute-to-service-nflz">youth brandon brooks limited olivecamo nike jersey nfl philadelphia eagles 79 super bowl lii 2017 salute to service</a> <a href="http://www.prismbuffalo.com/nike-flyknit-racer-hyper-naranja-tree-kurpesd">nike flyknit racer hyper naranja tree</a> <a href="http://www.surlion.com/womens-nike-new-york-giants-87-sterling-shepard-white-vapor-untouchable-limited-player-nfl-jersey-nflz">womens nike new york giants 87 sterling shepard white vapor untouchable limited player nfl jersey</a> <a href="http://www.tokodanaya.com/nike-tanjun-femminile-athletic-scarpe-houston-obuve">nike tanjun femminile athletic scarpe houston</a> <a href="http://www.damimiyy.com/where-can-i-comprar-barato-nike-air-force-1-scarped">where can i comprar barato nike air force 1</a> <a href="http://www.dinerwareoc.com/salomon-gcs-athletic-trail-p%C3%BArpura-kurpesc">salomon gcs athletic trail p煤rpura</a>
nataliericcio http://www.nataliericcio.com/
回复
jmgtours
2019-01-16 09:54
<a href="http://www.tickbits.com/nobis-abby-vs-meredith-nobisr">nobis abby vs meredith</a> <a href="http://www.jmgtours.com/air-jordan-retro-28-mint-green-white-skod">air jordan retro 28 mint green white</a> <a href="http://www.gregertopsoil.com/hermes-orange-cashmere-scarf-scarfr">hermes orange cashmere scarf</a> <a href="http://www.nedshare.com/toronto-blue-jays-hats-new-era-50-hatc">toronto blue jays hats new era 50</a> <a href="http://www.itmontwitter.com/nike-hypervenom-phinish-orange-skox">nike hypervenom phinish orange</a> <a href="http://www.itsanomadlife.com/air-jordan-retro-13-kvinders-s%C3%B8lv-shoesb">air jordan retro 13 kvinders s酶lv</a>
jmgtours http://www.jmgtours.com/
回复
tinystorygame
2019-01-16 05:23
<a href="http://www.redstonecloud.com/jordan-captain-america-gr%C3%BCn-gelb-schuhec">jordan captain america gr眉n gelb</a> <a href="http://www.thepoolfloats.com/adidas-yeezy-boost-350-triple-sort-mold-schuheg">adidas yeezy boost 350 triple sort mold</a> <a href="http://www.tsgctt.com/air-jordan-retro-30.5-violet-rouge-kurpesc">air jordan retro 30.5 violet rouge</a> <a href="http://www.virginiaflyin.com/nike-air-force-one-negro-high-top-obuvg">nike air force one negro high top</a> <a href="http://www.bandbwhitby.com/nike-lebron-list-of-scarpe-scarpee">nike lebron list of scarpe</a> <a href="http://www.funkymediamx.com/air-jordan-4-brojo-nike.com-butya">air jordan 4 brojo nike.com</a>
tinystorygame http://www.tinystorygame.com/
回复
myserafins
2019-01-06 19:21
<a href="http://www.tailflappers.com/nike-kd-9-negro-edition-chaussuresf">nike kd 9 negro edition</a> <a href="http://www.tranhkhoathan.com/air-jordan-future-premium-glow-weave-rosso-nero-pants-butya">air jordan future premium glow weave rosso nero pants</a> <a href="http://www.beachtimenow.com/dam%C3%A4nner-nike-dunk-sky-hi-trainers-schwarz-schuheg">dam盲nner nike dunk sky hi trainers schwarz</a> <a href="http://www.blogdalata.com/herre-ralph-lauren-mesh-polo-lyser%C3%B8d-bl%C3%A5-polou">herre ralph lauren mesh polo lyser酶d bl氓</a> <a href="http://www.boosnbrews.com/jordan-cp3-light-blau-chaussuresa">jordan cp3 light blau</a> <a href="http://www.eventyrklub.com/air-jordan-6-blanco-and-armada-schuhee">air jordan 6 blanco and armada</a>
myserafins http://www.myserafins.com/
回复
lapthelakes
2019-01-06 04:23
<a href="http://www.heslaatours.com/nike-air-pegasus-92-bianca-rosso-light-scarpeb">nike air pegasus 92 bianca rosso light</a> <a href="http://www.prostekrasna.com/nike-air-max-2019-green-uk-shoeso">nike air max 2019 green uk</a> <a href="http://www.altharmony.com/nike-air-force-1-flyknit-high-bl%C3%A5-oransje-kurpesb">nike air force 1 flyknit high bl氓 oransje</a> <a href="http://www.cosmicupcake.com/blue-green-converse-high-tops-zapatost">blue green converse high tops</a> <a href="http://www.btmtecled.com/converse-femme-all-star-blanche-shoesz">converse femme all star blanche</a> <a href="http://www.torcgunstore.com/herren-nike-air-max-87-sb-schwarz-gold-schuheb">herren nike air max 87 sb schwarz gold</a>
[url=http://www.lapthelakes.com/]lapthelakes[/url]
回复
bancheebakers
2018-12-30 22:14
<a href="http://www.coverixx.com/adidas-pr%C3%B8dator-lethal-zones-weight-obuvf">adidas pr酶dator lethal zones weight</a> <a href="http://www.eiconstrutora.com/nero-and-blu-jordan-9-2012-schuhef">nero and blu jordan 9 2012</a> <a href="http://www.extradimensie.com/air-jordan-ultra-fly-blau-wei%C3%9F-kurpesd">air jordan ultra fly blau wei脽</a> <a href="http://www.funkwood.com/air-jordan-future-turquoise-and-sort-kitchen-butyf">air jordan future turquoise and sort kitchen</a> <a href="http://www.akronconcerts.com/femminile-air-jordan-7-cammello-scarpee">femminile air jordan 7 cammello</a> <a href="http://www.bacononice.com/air-jordan-9-blanc-and-or-weapons-schuhea">air jordan 9 blanc and or weapons</a>
bancheebakers http://www.bancheebakers.com/
回复
树箅子
2014-05-07 13:51
学习了，谢谢博主分享
回复

发表评论：

Powered by emlog 冀ICP备13011830号-1