Migrate from MyISAM to InnoDB

My topic for today was to migrate a MySQL server with some databases on it from MyISAM to InnoDB. In the end (after having some backups, of course), I wrote the little script below:

#!/bin/bash

for database in $(echo "show databases;" | mysql); do
  case $database in
    Database|information_schema|mysql)
      echo "Skipping database $database"
    ;;    
    *)    
      echo "Converting database $database"
      for i in $(echo "show tables;" | mysql $database;); do 
        case $i in 
          Tables_in_${database})
          ;;
          *)
            echo "....converting $i"
            echo "ALTER TABLE $i ENGINE = INNODB;" | mysql $database;
          ;; 
        esac
      done  
    ;;    
  esac  
done

Just remember to have a file .my.cnf in your home directory containing the username and password of your database administrator.

About these ads

About lvogdt

This is the private blog space of Lars Vogdt, the topics will be in first place work related.
This entry was posted in openSUSE, SUSE Linux Enterprise and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s