You are here
Switching from MySQL/MyISAM to Galera Cluster
Tue, 2013-03-12 08:23 — Shinguz
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
Taxonomy upgrade extras:
- Shinguz's blog
- Log in or register to post comments
Comments
Feature differences between InnoDB FTS and MyISAM FTS