Compare structures of two databases?

Question

I wanted to ask whether it is possible to compare the complete database structure of two huge databases. We have two databases, the one is a development database, the other a production database. I've sometimes forgotten to make changes in to the production database, before we released some parts of our code, which results that the production database doesn't have the same structure, so if we release something we got some errors. Is there a way to compare the two, or synchronize?

I tried all the following tools, most needs to be paid, some could not work on mysql server, and mysqldiff simply does not work. I ended with dumping db strucdture and command line diff. It's odd, as I thought it's supposed to be a simple tool (a little more robust than command line diff)? — Walty Yeung, Apr 18 '12 at 06:46

Dmitry · Answer 1 · 2015-09-07T09:54:41.203

For MySQL database you can compare view and tables (column name and column type) using this query:

SET @firstDatabaseName = '[first database name]';
SET @secondDatabaseName = '[second database name]';

SELECT * FROM  
  (SELECT
      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType
    FROM information_schema.columns cl,  information_schema.TABLES ss
    WHERE
      cl.TABLE_NAME = ss.TABLE_NAME AND
      cl.TABLE_SCHEMA = @firstDatabaseName AND
      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')
    ORDER BY
      cl.table_name ) AS t1
LEFT JOIN                     
  (SELECT
      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType
    FROM information_schema.columns cl,  information_schema.TABLES ss
    WHERE
      cl.TABLE_NAME = ss.TABLE_NAME AND
      cl.TABLE_SCHEMA = @secondDatabaseName AND
      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')
    ORDER BY
      cl.table_name ) AS t2 ON t1.tableRowType = t2.tableRowType
WHERE 
  t2.tableRowType IS NULL        
UNION 
SELECT * FROM  
  (SELECT
      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType
    FROM information_schema.columns cl,  information_schema.TABLES ss
    WHERE
      cl.TABLE_NAME = ss.TABLE_NAME AND
      cl.TABLE_SCHEMA = @firstDatabaseName AND
      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')
    ORDER BY
      cl.table_name ) AS t1
RIGHT JOIN                     
  (SELECT
      CONCAT(cl.TABLE_NAME, ' [', cl.COLUMN_NAME, ', ', cl.COLUMN_TYPE, ']') tableRowType
    FROM information_schema.columns cl,  information_schema.TABLES ss
    WHERE
      cl.TABLE_NAME = ss.TABLE_NAME AND
      cl.TABLE_SCHEMA = @secondDatabaseName AND
      ss.TABLE_TYPE IN('BASE TABLE', 'VIEW')
    ORDER BY
      cl.table_name ) AS t2 ON t1.tableRowType = t2.tableRowType
WHERE 
  t1.tableRowType IS NULL;

If you prefer using tool with UI you can also use this script https://github.com/dlevsha/compalex which can compare tables, views, keys etc.

Compalex is a lightweight script to compare two database schemas. It supports MySQL, MS SQL Server and PostgreSQL.

Screenshot (compare tables)

Compalex is awesome (+1), but does not show indexes. It also shows, but does not highlight differences in, engine (MyISAM vs InnoDB). — TRiG, Feb 06 '20 at 15:26
@TRiG The latest Compalex version shows indexes. http://demo.compalex.net/index.php?action=indexes — Digvijayad, Nov 22 '22 at 11:00

score 22 · Answer 2 · answered Oct 15 '14 at 13:53

22

You can use the command line:

mysqldump --skip-comments --skip-extended-insert -d --no-data -u root -p dbName1>file1.sql
mysqldump --skip-comments --skip-extended-insert -d --no-data -u root -p dbName2>file2.sql
diff file1.sql file2.sql

answered Oct 15 '14 at 13:53

Gere

2,114
24
24

1

Good idea Gere, it was easy to find differences but not table names with the diff command, so I jumped into tkdiff package. I strongly recommend it. – therealbigpepe Dec 23 '16 at 10:38
how to skip auto-increment? – Arun Gowda Apr 30 '18 at 06:29
@ArunGowda maybe adding a regex like | sed 's/ AUTO_INCREMENT=[0-9]*//g' (after dbName) – Gere Apr 30 '18 at 19:38
@suarsenegger Why does diff not show you differences in table names? – kojow7 Apr 14 '19 at 05:28

score 5 · Answer 3 · answered Mar 05 '10 at 23:10

You can just dump them with --no-data and compare the files.

Remember to use the --lock-tables=0 option on your production database to avoid the big nasty global lock.

If you use the same mysqldump version (your dev and production systems should have the same software, right?) then you'll expect to get more-or-less identical files out. The tables will be in alpha order so a simple diff will show discrepancies up easily.

score 5 · Answer 4 · answered Mar 03 '18 at 04:54

To answer this kind of question currently, I've made a script that uses information_schema content to compare column, datatype, and table

SET @database_current = '<production>';
SET @database_dev = '<development>';
-- column and datatype comparison
SELECT a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH,
    b.COLUMN_NAME, b.DATA_TYPE, b.CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS a
    LEFT JOIN information_schema.COLUMNS b ON b.COLUMN_NAME = a.COLUMN_NAME
        AND b.TABLE_NAME = a.TABLE_NAME
        AND b.TABLE_SCHEMA = @database_current
WHERE a.TABLE_SCHEMA = @database_dev
    AND (
        b.COLUMN_NAME IS NULL
        OR b.COLUMN_NAME != a.COLUMN_NAME
        OR b.DATA_TYPE != a.DATA_TYPE
        OR b.CHARACTER_MAXIMUM_LENGTH != a.CHARACTER_MAXIMUM_LENGTH
    );
-- table comparison    
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, b.TABLE_NAME
FROM information_schema.TABLES a
    LEFT JOIN information_schema.TABLES b ON b.TABLE_NAME = a.TABLE_NAME
        AND b.TABLE_SCHEMA = @database_current
WHERE a.TABLE_SCHEMA = @database_dev
    AND (
        b.TABLE_NAME IS NULL
        OR b.TABLE_NAME != a.TABLE_NAME
    );

Hope this script can also help people that looks for a non-application solution, but the usage of script. Cheers

I like your idea of using a mysql-only solution. However, the filter OR b.COLUMN_NAME != a.COLUMN_NAME is redundant because equivalence is assumed within the JOIN condition, similarly OR b.TABLE_NAME != a.TABLE_NAME. — Paul Campbell, Mar 08 '18 at 18:50
@PaulCampbell good point! thanks for the help, well appreciated. Cheers — Avidos, Mar 09 '18 at 00:42
this solution is not taking indices into account, a separate block needs to be added for them — Andrei Rykhalski, May 08 '22 at 11:05

score 2 · Answer 5 · answered Aug 03 '14 at 11:04

I tried mysqldiff without success, so I would like to enrich the future readers by drawing attention to mysqlworkbench's compare function. http://dev.mysql.com/doc/workbench/en/wb-database-diff-report.html#c13030

if you open a model tab, and select the databases menu, you get a compare schemas option, which you can use to compare two different schemas on two different servers, or two schemas on the same server, or a schema and a model, or a lot of other options i haven't tried yet.

score 2 · Answer 6 · answered Dec 14 '16 at 21:54

For mysql on Linux, it is possible via phpmyadmin to export the databases without 'data' and only structure.

Scrolling through the export options for the entire database, just deselect 'data' and set the output to text. Export both databases you wish to compare.

Then in file compare in your preferred program / site, compare the two text file outputs of the databases. Synchronization is still manual in this solution, but this is effective for comparing and finding the structural differences.

For the very simple compare I needed to this, this was a low-overhead and quick way. Thanks! — user1072910, Jul 03 '18 at 07:27

score 1 · Answer 7 · answered Mar 05 '10 at 19:43

Depending on your database, the tools available vary.

I use Embarcadero's ER/Studio for this. It has a Compare and Merge feature.

There are plenty others, such as Toad for MySQL, that also have compare. Also agree on the Red-Gate suggestion, but never used it for MySQL.

score 1 · Answer 8 · answered Mar 15 '11 at 17:27

Check out Gemini Delta - SQL Difference Manager for .NET. A free beta version is available for download, but the full version is only a few days away from public release.

It doesn't compare row-level data differences, but it compares tables, functions, sprocs, etc... and it is lightning fast. (The new version, 1.4, loads and compares 1k Sprocs in under 4 seconds, compared with other tools I've tested which took over 10 seconds.)

Everyone is right though, RedGate does make great tools.

Compare structures of two databases?

8 Answers8

Linked