{"id":2356,"date":"2015-11-12T11:40:49","date_gmt":"2015-11-12T11:40:49","guid":{"rendered":"http:\/\/blog.designed79.co.uk\/?p=2356"},"modified":"2015-12-03T07:40:18","modified_gmt":"2015-12-03T07:40:18","slug":"convert-mysql-engine-from-myisam-to-innodb","status":"publish","type":"post","link":"https:\/\/blog.designed79.co.uk\/?p=2356","title":{"rendered":"Convert mySQL engine from myISAM to InnoDB"},"content":{"rendered":"<p>This will output a list of Alter command that you can run in mySQL console<\/p>\n<pre>SET @DATABASE_NAME = 'database_name_to_convert';\r\nSELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements\r\nFROM information_schema.tables AS tb\r\nWHERE table_schema = @DATABASE_NAME\r\nAND `ENGINE` = 'MyISAM'\r\nAND `TABLE_TYPE` = 'BASE TABLE'\r\nORDER BY table_name DESC;<\/pre>\n<p>This is a bash oneliner that will convert the Database, NOTE: you will have to type your password in twice, or change the -p for -p&#8221;password&#8221;<\/p>\n<pre>mysql -u root -p database_name_to_convert -e \"show table status where Engine='MyISAM';\" | awk 'NR&gt;1 {print \"ALTER TABLE \"$1\" ENGINE = InnoDB;\"}' | mysql -u root -p database_name_to_convert<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This will output a list of Alter command that you can run in mySQL console SET @DATABASE_NAME = &#8216;database_name_to_convert&#8217;; SELECT CONCAT(&#8216;ALTER TABLE [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2356","post","type-post","status-publish","format-standard","hentry","category-info-on-tech"],"_links":{"self":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2356","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2356"}],"version-history":[{"count":0,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/2356\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2356"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2356"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.designed79.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2356"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}