Compare structures of two databases?

问题:

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?

回答1:

What about http://www.mysqldiff.org/ which is freeware?

回答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

回答3:

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)

回答4:

Red-Gate SQL Compare is a great tool that will do this for you. I have used this for years with great success. It has saved me thousands of hours of work.

They also have a tool that will compare data as well. The product I mentioned above compares schema.

www-red-gate.com

回答5:

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.

回答6:

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

回答7:

You'll need a tool, probably, like Database comparer.

回答8:

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.

回答9:

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.

回答10:

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.

回答11:

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.

回答12:

SchemaCrawler is a free, platform-independent tool that allows you to compare database structures. The two databases do not need to be online at the same time - you can save your database structure into a human-readable text file. Comaprisons are done using standard diff tools.

I would recommend automating the comparison as a part of build and deploy process, to ensure that your production systems are never out of date.

回答13:

I would definitely go with AdeptSQL if you're using MSSQL. It's the least good looking but the most talented db compare tool amongst the ones I've tried. It can compare both the structure and the data. It tells you which tables exist on one db but does not exist on the other, compares the structure and data of the common ones and it can produce the script to synchronize the two. It's not free but has a 30 day trial (as far as I can remember)

回答14:

Try dbForge Scheme Compare for SQL server. It can compare and synchronize any database scheme. Quick, easy, always delivering a correct result. See how it flies on your database!

回答15:

SQL Examiner Suite 2010 compares MySQL databases (both schema and data)

回答16:

I finally used a simple tool called "mysql structure compare" to solve this. It's for windows only, but it's free and it works..

回答17:

If you are using MySQL there is a free tool DB Matcher where you upload 2 .SQL files and it gives you the differences

https://dbmatcher.com