Switching from MySQL/MyISAM to Galera Cluster

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 < /tmp/alter_table_all.sql

cat /tmp/alter_table_all.sql
-- Commented (--) lines means that these tables are already using the wanted Storage Engine.

warnings
ALTER TABLE `foodmart`.`account` ENGINE=InnoDB;
ALTER TABLE `foodmart`.`category` ENGINE=InnoDB;
...
ALTER TABLE `foodmart`.`warehouse` ENGINE=InnoDB;
ALTER TABLE `foodmart`.`warehouse_class` ENGINE=InnoDB;
-- ALTER TABLE `test`.`contacts` ENGINE=InnoDB;
-- ALTER TABLE `test`.`demo_test` ENGINE=InnoDB;
-- ALTER TABLE `test`.`email_address` ENGINE=InnoDB;
-- ALTER TABLE `test`.`leads` ENGINE=InnoDB;
-- ALTER TABLE `test`.`location` ENGINE=InnoDB;
-- ALTER TABLE `test`.`member` ENGINE=InnoDB;
-- ALTER TABLE `test`.`ptn` ENGINE=InnoDB;
-- ALTER TABLE `test`.`t1` ENGINE=InnoDB;
ALTER TABLE `test`.`test` ENGINE=InnoDB;
ALTER TABLE `test`.`test2` ENGINE=InnoDB;
ALTER TABLE `test`.`user` ENGINE=InnoDB;
-- ALTER TABLE `test`.`users` ENGINE=InnoDB;