We are trying to keep several MySQL development database environments synchronized, but we have developers that sometimes add table structure/definition changes to one, but forget to add them to others. Obviously better processes would help, but that will take time. Note, this is NOT a question about keeping the data in the tables synchronized, but keeping the table definition itself synchronized.
What is the simplest, most accurate and generic way of comparing 2 MySQL Databases/Schema's to see if the table definitions contained in the Database are all LOGICALLY the same (i.e ignore column ordering differences, aliases and INDEX/KEY name differences).
The obvious answer was to perform a DIFF from the output of the mysqldump utility, HOWEVER, this is useless if the column order is different between tables. It is also useless if indexes have different names, even if they represent the same set of columns. Assuming that you are not using select * for any query, the column ordering is not that important when it comes to Database tables.
A second approach was to query the INFORMATION_SCHEMA via SQL, as follows:
SELECT TABLE_NAME,COLUMN_NAME,IS_NULLABLE,COLUMN_TYPE,IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where table_schema = 'myschema' order by 1,2;
using an 'order by' clause to remove the column ordering issue, then performing a compare on the results from the 2 Databases, but this feels very 'hacky' and subject to change, i.e. if the INFORMATION_SCHEMA ever changed, this would break.
