I'm migrating a large production database(s) from mysql 5.5 to 5.6. Used Mysql Workbench Migration Wizard which creates a shell script to run on source and taget machines.
The process worked well but there are major changes to the database:
For example:
Changed order of fields in primary key PRIMARY KEY (
ru_sid
,ru_rule_id
), => PRIMARY KEY (ru_rule_id
,ru_sid
)Messed encoding while the data is expected to be utf8 I did a query of the same data in the original and new server and the output is different: Pré-Home Flash Sales => Pré-Home Flash Sales
Possibly there are other changes.
My question is - how can I tell if the migration is trustable. I cant check every table to see if changes in the index will not break something in my code. Can the workbench migration be trusted ? Is there a better way to do the migration? What would you do?
EDIT:
Create statement before:
(I removed the fields that are the same in the before and after)
CREATE TABLE
rules(
ru_rule_idint(10) unsigned NOT NULL AUTO_INCREMENT,
ru_titlevarchar(254) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
ru_datatext COLLATE utf8_unicode_ci NOT NULL,
ru_namevarchar(64) COLLATE utf8_unicode_ci NOT NULL,
ru_tagsvarchar(64) COLLATE utf8_unicode_ci DEFAULT '',
ru_priorityvarchar(12) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
PRIMARY KEY (
ru_sid,
ru_rule_id),
KEY
num(
ru_rule_id) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=9998 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Create statement after:
CREATE TABLE
rules(
ru_rule_idint(10) unsigned NOT NULL AUTO_INCREMENT,
ru_titlevarchar(254) CHARACTER SET utf8 NOT NULL DEFAULT '',
ru_datatext CHARACTER SET utf8 NOT NULL,
ru_namevarchar(64) CHARACTER SET utf8 NOT NULL,
ru_tagsvarchar(64) CHARACTER SET utf8 DEFAULT '',
ru_priorityvarchar(12) CHARACTER SET utf8 NOT NULL DEFAULT '1',
PRIMARY KEY (
ru_rule_id,
ru_sid),
KEY
num(
ru_rule_id)
) ENGINE=MyISAM AUTO_INCREMENT=9954 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
SHOW CREATE TABLE
before and after.