• 批量修改Mysql表引擎为InnoDB的方法

    [FreeBSD]
    post by Holmesian / 2010-10-5 14:56 Tuesday

    一般批量修改MYSQL中某表的数据库引擎可以利用官方工具mysql_convert_table_format来实现， 这里指的是不使用其他工具仅用shell的方法来实现。（以下例子效果是将数据库ecjtubbs中所有引擎不为InnoDB的表修改为使用InnoDB引擎）

    先用shell获取需要更换表引擎：

     
    1. mysql --user=root --password=passwd -e "show table status from ecjtubbs where Engine <> 'InnoDB'G"|grep Name|awk '{print "alter table "$2" engine=innodb;";}' >mysqlchange

     

    这样在mysqlchange文件里面就会有如下的内容（包含了所有ecjtubbs表中所有引擎不为InnoDB的表名）：

     
    1. alter table cdb_activities engine=innodb;
    2. alter table cdb_activityapplies engine=innodb;
    3. alter table cdb_addons engine=innodb;
    4. alter table cdb_adminactions engine=innodb;
    5. alter table cdb_admincustom engine=innodb;
    6. alter table cdb_admingroups engine=innodb;
    7. alter table cdb_adminnotes engine=innodb;
    8. alter table cdb_adminsessions engine=innodb;
    9. alter table cdb_advertisements engine=innodb;
    10. alter table cdb_announcements engine=innodb;
    11. alter table cdb_attachmentfields engine=innodb;
    12. alter table cdb_attachments engine=innodb;
    13. alter table cdb_attachpaymentlog engine=innodb;
    14. alter table cdb_attachtypes engine=innodb;
    15. alter table cdb_banklist engine=innodb;
    16. alter table cdb_banklog engine=innodb;
    17. alter table cdb_bankoperation engine=innodb;
    18. alter table cdb_banned engine=innodb;
    19. alter table cdb_bbcodes engine=innodb;
    20. alter table cdb_caches engine=innodb;
    21. alter table cdb_creditslog engine=innodb;
    22. alter table cdb_crons engine=innodb;
    23. alter table cdb_debateposts engine=innodb;
    24. alter table cdb_debates engine=innodb;
    25. alter table cdb_failedlogins engine=innodb;
    26. alter table cdb_faqs engine=innodb;
    27. alter table cdb_favoriteforums engine=innodb;
    28. alter table cdb_favorites engine=innodb;
    29. alter table cdb_favoritethreads engine=innodb;
    30. alter table cdb_feeds engine=innodb;
    31. alter table cdb_forumfields engine=innodb;
    32. alter table cdb_forumlinks engine=innodb;
    33. alter table cdb_forumrecommend engine=innodb;
    34. alter table cdb_forums engine=innodb;
    35. alter table cdb_gameinfo engine=innodb;
    36. alter table cdb_gjdquiz_counts engine=innodb;
    37. alter table cdb_gjdquiz_questions engine=innodb;
    38. alter table cdb_gjdquiz_series engine=innodb;
    39. alter table cdb_happy28_flow engine=innodb;
    40. alter table cdb_happy28_members engine=innodb;
    41. alter table cdb_happy28_prize engine=innodb;
    42. alter table cdb_happy28_term engine=innodb;
    43. alter table cdb_imagetypes engine=innodb;
    44. alter table cdb_invites engine=innodb;
    45. alter table cdb_itempool engine=innodb;
    46. alter table cdb_league engine=innodb;
    47. alter table cdb_magiclog engine=innodb;
    48. alter table cdb_magicmarket engine=innodb;
    49. alter table cdb_magics engine=innodb;
    50. alter table cdb_marry engine=innodb;
    51. alter table cdb_medallog engine=innodb;
    52. alter table cdb_medals engine=innodb;
    53. alter table cdb_memberfields engine=innodb;
    54. alter table cdb_membermagics engine=innodb;
    55. alter table cdb_memberrecommend engine=innodb;
    56. alter table cdb_members engine=innodb;
    57. alter table cdb_memberspaces engine=innodb;
    58. alter table cdb_moderators engine=innodb;
    59. alter table cdb_modworks engine=innodb;
    60. alter table cdb_myguess engine=innodb;
    61. alter table cdb_myposts engine=innodb;
    62. alter table cdb_mytasks engine=innodb;
    63. alter table cdb_mythreads engine=innodb;
    64. alter table cdb_navs engine=innodb;
    65. alter table cdb_onlinelist engine=innodb;
    66. alter table cdb_onlinetime engine=innodb;
    67. alter table cdb_orders engine=innodb;
    68. alter table cdb_paymentlog engine=innodb;
    69. alter table cdb_pluginhooks engine=innodb;
    70. alter table cdb_plugins engine=innodb;
    71. alter table cdb_pluginvars engine=innodb;
    72. alter table cdb_polloptions engine=innodb;
    73. alter table cdb_polls engine=innodb;
    74. alter table cdb_postban engine=innodb;
    75. alter table cdb_postban_escapelog engine=innodb;
    76. alter table cdb_postposition engine=innodb;
    77. alter table cdb_posts engine=innodb;
    78. alter table cdb_preplies engine=innodb;
    79. alter table cdb_profilefields engine=innodb;
    80. alter table cdb_projects engine=innodb;
    81. alter table cdb_promotions engine=innodb;
    82. alter table cdb_prompt engine=innodb;
    83. alter table cdb_promptmsgs engine=innodb;
    84. alter table cdb_prompttype engine=innodb;
    85. alter table cdb_ranks engine=innodb;
    86. alter table cdb_ratelog engine=innodb;
    87. alter table cdb_regips engine=innodb;
    88. alter table cdb_relatedthreads engine=innodb;
    89. alter table cdb_reportlog engine=innodb;
    90. alter table cdb_request engine=innodb;
    91. alter table cdb_rewardlog engine=innodb;
    92. alter table cdb_rsscaches engine=innodb;
    93. alter table cdb_searchindex engine=innodb;
    94. alter table cdb_sessions engine=innodb;
    95. alter table cdb_settings engine=innodb;
    96. alter table cdb_smilies engine=innodb;
    97. alter table cdb_spacecaches engine=innodb;
    98. alter table cdb_stats engine=innodb;
    99. alter table cdb_statvars engine=innodb;
    100. alter table cdb_styles engine=innodb;
    101. alter table cdb_stylevars engine=innodb;
    102. alter table cdb_tags engine=innodb;
    103. alter table cdb_tasks engine=innodb;
    104. alter table cdb_taskvars engine=innodb;
    105. alter table cdb_team engine=innodb;
    106. alter table cdb_templates engine=innodb;
    107. alter table cdb_threads engine=innodb;
    108. alter table cdb_threadsmod engine=innodb;
    109. alter table cdb_threadtags engine=innodb;
    110. alter table cdb_threadtypes engine=innodb;
    111. alter table cdb_tradecomments engine=innodb;
    112. alter table cdb_tradelog engine=innodb;
    113. alter table cdb_tradeoptionvars engine=innodb;
    114. alter table cdb_trades engine=innodb;
    115. alter table cdb_typemodels engine=innodb;
    116. alter table cdb_typeoptions engine=innodb;
    117. alter table cdb_typeoptionvars engine=innodb;
    118. alter table cdb_typevars engine=innodb;
    119. alter table cdb_usergroups engine=innodb;
    120. alter table cdb_validating engine=innodb;
    121. alter table cdb_warnings engine=innodb;
    122. alter table cdb_words engine=innodb;
    123. alter table ecjtu_ads engine=innodb;
    124. alter table ecjtu_announcements engine=innodb;
    125. alter table ecjtu_attachments engine=innodb;
    126. alter table ecjtu_attachmenttypes engine=innodb;
    127. alter table ecjtu_blocks engine=innodb;
    128. alter table ecjtu_cache engine=innodb;
    129. alter table ecjtu_cache_0 engine=innodb;
    130. alter table ecjtu_cache_1 engine=innodb;
    131. alter table ecjtu_cache_2 engine=innodb;
    132. alter table ecjtu_cache_3 engine=innodb;
    133. alter table ecjtu_cache_4 engine=innodb;
    134. alter table ecjtu_cache_5 engine=innodb;
    135. alter table ecjtu_cache_6 engine=innodb;
    136. alter table ecjtu_cache_7 engine=innodb;
    137. alter table ecjtu_cache_8 engine=innodb;
    138. alter table ecjtu_cache_9 engine=innodb;
    139. alter table ecjtu_cache_a engine=innodb;
    140. alter table ecjtu_cache_b engine=innodb;
    141. alter table ecjtu_cache_c engine=innodb;
    142. alter table ecjtu_cache_d engine=innodb;
    143. alter table ecjtu_cache_e engine=innodb;
    144. alter table ecjtu_cache_f engine=innodb;
    145. alter table ecjtu_categories engine=innodb;
    146. alter table ecjtu_channels engine=innodb;
    147. alter table ecjtu_corpus engine=innodb;
    148. alter table ecjtu_crons engine=innodb;
    149. alter table ecjtu_customfields engine=innodb;
    150. alter table ecjtu_effects engine=innodb;
    151. alter table ecjtu_favorites engine=innodb;
    152. alter table ecjtu_friendlinks engine=innodb;
    153. alter table ecjtu_friends engine=innodb;
    154. alter table ecjtu_goodsprice engine=innodb;
    155. alter table ecjtu_groupfields engine=innodb;
    156. alter table ecjtu_groupinvite engine=innodb;
    157. alter table ecjtu_groupitems engine=innodb;
    158. alter table ecjtu_groups engine=innodb;
    159. alter table ecjtu_groupuid engine=innodb;
    160. alter table ecjtu_guestbooks engine=innodb;
    161. alter table ecjtu_hrcategories engine=innodb;
    162. alter table ecjtu_hrcomments engine=innodb;
    163. alter table ecjtu_hrfolders engine=innodb;
    164. alter table ecjtu_hritems engine=innodb;
    165. alter table ecjtu_hrmessage engine=innodb;
    166. alter table ecjtu_hrrates engine=innodb;
    167. alter table ecjtu_itemtypes engine=innodb;
    168. alter table ecjtu_lovecategories engine=innodb;
    169. alter table ecjtu_lovecomments engine=innodb;
    170. …………

    接下来复制上面的内容，用Putty登录服务器，用“mysql --user=root --password=passwd”登录Mysql在mysql> 界面后直接点击鼠标右键粘贴即可完成数据表的引擎更换（如果数据库非常大可能要等一段时间）。

     

    其实完全可以通过以下shell完成同样功能的操作：

     
    1. mysql --user=root --password=passwd -e "show table status from ecjtubbs where Engine <> 'InnoDB'G"|grep Name|awk '{print "mysql --user=root --password=passwd -p ecjtubbs "alter table "$2" engine=innodb;"";}'|bash

    只是有的环境下mysql --user=root --password=passwd -p ecjtubbs 并不一定会按照预期的想法登录mysql并选择ecjtubbs数据库，只要你能确保mysql --user=root --password=passwd -p ecjtubbs 能够登录到Mysql并选择ecjtubbs数据库就可以使用一行命令实现修改表引擎的效果。

     

     

    标签: mysql innodb 引擎 批量
    « 用Putty实现Linux与Windows互传文件 | 发几条测试推»

    发表评论：