Updating the bava database from MyISAM to InnoDB

Some of our initial explorations in the Reclaim Cloud have been around automagically scaling for larger WordPress Multisite instance. One of the things Tim discovered yesterday is that the Gallera Cluster that scales databases only works with InnoDB type MySQL databases not MyISAM. Turns out both this blog and ds106 run MyISAM, so I found this Stack Exchange post on upgrading from one to the other. Needless to say I got a backup of my database before trying anything, and then ran this code in the general SQL area of phpMyAdmin:

SET @DATABASE_NAME = 'bavatues_wp1';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

That command spit out the following that I then ran in the SQL area of phpMyAdmin for this blog’s database:

ALTER TABLE `wp_wordtube_playlist` ENGINE=InnoDB;
ALTER TABLE `wp_wordtube_med2play` ENGINE=InnoDB;
ALTER TABLE `wp_wordtube` ENGINE=InnoDB;
ALTER TABLE `wp_users` ENGINE=InnoDB;
ALTER TABLE `wp_usermeta` ENGINE=InnoDB;
ALTER TABLE `wp_term_taxonomy` ENGINE=InnoDB;
ALTER TABLE `wp_term_relationships` ENGINE=InnoDB;
ALTER TABLE `wp_terms` ENGINE=InnoDB;
ALTER TABLE `wp_termmeta` ENGINE=InnoDB;
ALTER TABLE `wp_stp_tags` ENGINE=InnoDB;
ALTER TABLE `wp_spamlist` ENGINE=InnoDB;
ALTER TABLE `wp_scaptcha` ENGINE=InnoDB;
ALTER TABLE `wp_richcomments` ENGINE=InnoDB;
ALTER TABLE `wp_ratings` ENGINE=InnoDB;
ALTER TABLE `wp_quotescollection` ENGINE=InnoDB;
ALTER TABLE `wp_posts` ENGINE=InnoDB;
ALTER TABLE `wp_postmeta` ENGINE=InnoDB;
ALTER TABLE `wp_pollsq` ENGINE=InnoDB;
ALTER TABLE `wp_pollsip` ENGINE=InnoDB;
ALTER TABLE `wp_pollsa` ENGINE=InnoDB;
ALTER TABLE `wp_podpress_stats` ENGINE=InnoDB;
ALTER TABLE `wp_podpress_statcounts` ENGINE=InnoDB;
ALTER TABLE `wp_options` ENGINE=InnoDB;
ALTER TABLE `wp_ngg_pictures` ENGINE=InnoDB;
ALTER TABLE `wp_ngg_gallery` ENGINE=InnoDB;
ALTER TABLE `wp_ngg_album` ENGINE=InnoDB;
ALTER TABLE `wp_links` ENGINE=InnoDB;
ALTER TABLE `wp_flickr_post` ENGINE=InnoDB;
ALTER TABLE `wp_comments` ENGINE=InnoDB;
ALTER TABLE `wp_commentmeta` ENGINE=InnoDB;
ALTER TABLE `wp_blc_synch` ENGINE=InnoDB;
ALTER TABLE `wp_blc_links` ENGINE=InnoDB;
ALTER TABLE `wp_blc_instances` ENGINE=InnoDB;
ALTER TABLE `wp_blc_filters` ENGINE=InnoDB;
ALTER TABLE `wp_bibliography` ENGINE=InnoDB;
ALTER TABLE `wp_as3cf_items` ENGINE=InnoDB;
ALTER TABLE `wp_amber_queue` ENGINE=InnoDB;
ALTER TABLE `wp_amber_check` ENGINE=InnoDB;
ALTER TABLE `wp_amber_cache` ENGINE=InnoDB;
ALTER TABLE `wp_amber_activity` ENGINE=InnoDB;
ALTER TABLE `wp_ak_twitter` ENGINE=InnoDB;
ALTER TABLE `wp_ak_404_log` ENGINE=InnoDB;

That was all it took, they were changed successfully. After that  I restarted the Gallera Cluster in Reclaim Cloud, and everything seems to be running smoothly. I’m currently working up the courage to try the same thing for ds106 🙂

This entry was posted in bavatuesdays, Reclaim Cloud, sysadmin, WordPress and tagged , , , , , . Bookmark the permalink.

3 Responses to Updating the bava database from MyISAM to InnoDB

  1. Ed Beck says:

    I’m following the Reclaim.cloud project as closely as can, with as many nuggets as the teams puts out in the open.

    One of my main interests in SUNY is Open Educational Resources, and one of the main tools we use is Pressbooks. With a Pressbooks installation being essentially a WordPress Multisite with custom plugins that convert every child-site into a book, I’m really looking forward to seeing what Reclaim.Cloud can do in auto-scaling, and how you will integrate it in with existing services. I also wonder if the flexibility of a docker based solution will allow you to use all of Pressbooks features, like accessible math with MathJax that Reclaim can’t currently support.

    I’m also excited by the idea that Reclaim.Cloud would extend capabilities beyond LAMP, to allow us to play with tools like Discourse.

    • Reverend says:

      Ed,

      Yeah, it is pretty exciting, and we are still playing but we can definitely hook you up with an invitation. I think scaling is the key reason to explore Pressbooks and WPMS in the Reclaim Cloud given if you have major traffic and usage you would automatically be able to meet the demand, yet only pay for what people use. So, there is definitely an economies of scale, but it is also billing like a utility, if you turn off the lights or use less resources you don’t pay for them.

      The Discourse and other custom applications is really interesting, and Tim is in the process of making installers for many of them. I can confirm Discourse runs cleanly via container, and we also have Ghost, Etherpad, Jitsi, ShinyApps, R Studio, Minecraft, and Manifold is coming (a nice alternative to Pressbooks) and many more. It is pretty exciting, and while we don’t want to over promise and under deliver, it really could be something special.

      So, you excitement could be very well founded 🙂

  2. Pingback: Weekend Notes from Reclaim Cloud: Anth101, downsizing bava, and back to MyISAM | bavatuesdays

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.