Re: Errors with Information_Schema in Create Target Results

[Peter Brawley]

That'll confirm matching rowcounts, nothing more.

It's safe to assume that two dbs on different servers are identical if a complete dump from one was imported without error on the other with identical mysql versibns and my.cnf/ini settings.

If versions or settings might be an issue and absolutely certain identicality is required, them for any db you can export the 2nd db back to the 1st server under a different name and run per-table comparisons on tables in the two dbs using this procedure ...

drop procedure if exists comparetables;
delimiter go
create procedure comparetables( tbla char(64), tblb char(64), columnlist char(255) )
begin
  set @sql = concat( " SELECT MIN(TableName) as TableName, ", columnlist,  
                     " FROM ( ",
                     "  SELECT '", tbla, "' as TableName, ", columnlist,  
                     "  FROM ", tbla, 
                     "  UNION ALL ",
                     "  SELECT '", tblb, "' as TableName, ", columnlist,
                     "  FROM ", tblb, 
                     ") AS tmp ", 
                     " GROUP BY ", columnlist, 
                     " HAVING COUNT(*) = 1 ", 
                     " ORDER BY 1" 
                   );
  prepare stmt from @sql;
  execute stmt;
  drop prepare stmt;
end;
go
delimiter ;

... but with a big db, it'll take a while.

Or see "Compare two databases" at https://www.artfulsoftware.com/infotree/queries.php.



Edited 1 time(s). Last edit at 10/30/2021 09:12AM by Peter Brawley.