Compare Engines

• Tweet
• 

        // If you have 2 servers with identical database structure, and some of the tables have different engine type, then create a federated table to connect to the original server and compare the engines type with the current table's engine.  (useful while comparing master - slave tables)

CREATE TABLE test.`TABLES2` (
`TABLE_CATALOG` varchar(512) default NULL,
`TABLE_SCHEMA` varchar(64) NOT NULL default '',
`TABLE_NAME` varchar(64) NOT NULL default '',
`TABLE_TYPE` varchar(64) NOT NULL default '',
`ENGINE` varchar(64) default NULL,
`VERSION` bigint(21) default NULL,
`ROW_FORMAT` varchar(10) default NULL,
`TABLE_ROWS` bigint(21) default NULL,
`AVG_ROW_LENGTH` bigint(21) default NULL,
`DATA_LENGTH` bigint(21) default NULL,
`MAX_DATA_LENGTH` bigint(21) default NULL,
`INDEX_LENGTH` bigint(21) default NULL,
`DATA_FREE` bigint(21) default NULL,
`AUTO_INCREMENT` bigint(21) default NULL,
`CREATE_TIME` datetime default NULL,
`UPDATE_TIME` datetime default NULL,
`CHECK_TIME` datetime default NULL,
`TABLE_COLLATION` varchar(64) default NULL,
`CHECKSUM` bigint(21) default NULL,
`CREATE_OPTIONS` varchar(255) default NULL,
`TABLE_COMMENT` varchar(80) NOT NULL default ''
)
ENGINE=FEDERATED DEFAULT CHARSET=latin1
CONNECTION='mysql://root@172.172.172.172/information_schema/TABLES';

SELECT b.TABLE_SCHEMA as remote_database, b.TABLE_NAME as remote_tableName, b.ENGINE as remote_engine, a.ENGINE AS local_engine 
FROM test.TABLES2 AS a INNER JOIN information_schema.TABLES as b 
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME AND a.ENGINE != b.ENGINE;

Tags:
• forge
• MySQL
• oksoft
• shell_script