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?

Gobetween

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)? – Candescent

Answer 1

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)

Rollandrollaway

This is great. I use this. Thank you. – Gyronny

Compalex is awesome (+1), but does not show indexes. It also shows, but does not highlight differences in, engine (MyISAM vs InnoDB). – Fino

@Fino The latest Compalex version shows indexes. demo.compalex.net/index.php?action=indexes – Transaction

Answer 2

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

Adalai

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. – Teammate

how to skip auto-increment? – Marlanamarlane

@ArunGowda maybe adding a regex like | sed 's/ AUTO_INCREMENT=[0-9]*//g' (after dbName) – Adalai

@suarsenegger Why does diff not show you differences in table names? – Trost

NOw the best gui diff tool is vscode using compare file command – Drinking

Answer 3

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

Missing

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. – Pastose

@PaulCampbell good point! thanks for the help, well appreciated. Cheers – Missing

this solution is not taking indices into account, a separate block needs to be added for them – Tankersley

Answer 4

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.

Weird

Answer 5

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.

Bangup

Answer 6

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.

Salinometer

For the very simple compare I needed to this, this was a low-overhead and quick way. Thanks! – Asymptote

Answer 7

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.

Michelson

Answer 8

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.

Ricoriki

Answer 9

I used PgAdmin of PostgreSQL to compare the two schema. Take dump of both data base you want to compare and restore it in your local PgAdmin. Now using PgAdmin Schema Diff tool you can easily compare both imported db schema.

Shir