You are here

Switching from MySQL/MyISAM to Galera Cluster

Taxonomy upgrade extras: 

Switching from MySQL/MyISAM to Galera Cluster requires that all tables (except those from the mysql, information_schema and performance_schema) are using the InnoDB Storage Engine.

For altering the Storage Engine of the tables we wrote a script (alter_engine.pl) long time ago already. Because we have made many of those switches recently we have extended its functionality.

New features

  • Recognizes VIEW's and does NOT try to alter their Storage Engine (bug).
  • Script is MySQL version aware. Complain if too old MySQL version is used.
  • Find tables without a Primary Key.
  • Check for too long InnoDB Primary Keys
  • Check for FULLTEXT indexes in MySQL 5.1 and 5.5 and write a note if version is older.

Example

./alter_engine.pl
User                              [root] : 
Password                              [] : secret
Schema from (or all)              [test] : all
Engine to                       [InnoDB] : 

Version is   : 5.6.10
MR Version is: 050610

The following tables might not have a Primary Key:
+--------------+----------------------+
| table_schema | table_name           |
+--------------+----------------------+
| test         | innodb_table_monitor |
| test         | log_event            |
| test         | parent               |
| test         | t                    |
+--------------+----------------------+
The tables above not having a Primary Key will negatively affect perfor-
mance and data consistency in MySQL Master/Slave replication and Galera
Cluster replication.

The following tables might have a too long Primary Key for InnoDB (> 767 bytes):
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| test         | test       | data        |
+--------------+------------+-------------+

The following tables might have a FULLTEXT index (which is only supported
in MySQL 5.6 and newer):
+--------------+------------+-------------+
| table_schema | table_name | column_name |
+--------------+------------+-------------+
| test         | test       | data        |
+--------------+------------+-------------+

Output written to /tmp/alter_table_all.sql
After reviewing it you can apply it with mysql --user=root --password=secret 

Comments

There are some important differences between InnoDB FTS and MyISAM FTS. For example parser plugins won't be used (but can be configured) (Bug #62004) and some defaults are not the same (InnoDB fulltext search in MySQL 5.6 part 2 at MySQL Performance Blog) So only checking for the MySQL version might not be sufficient.
dveedencomment