How to compare and find differences of table definitions between two MySQL databases
If you just need a report of the difference between two MySQL databases, use MySQL workbench.
While in MySQL Model mode, from the menu Database -> Compare Schemas..., you can compare by Model Schemata, script file or connect to the servers directly.
To generate a transformation report containing SQL statements for transforming the objects for conformity, you can use mysqldiff which is part of MySQL utilities.
Usage: mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4
For example, to generate the sql statement for transforming demo database into live database in the same server:
$ mysqldiff --server1=user:password@i88server demo:live --difftype=sql --force
The utility stops on the first occurrence of missing objects or when an object does not match. To override this behavior, specify the --force option to cause the utility to attempt to compare all objects listed as arguments.
demo is put before live, so the output is for how to make the definitions of demo like the definitions of the corresponding objects of live. You can also get the reverse transformation by specifying the --show-reverse option. Or just switch their positions.
In some cases the diff output is not really useful. We actually need a SQL query to do the changes on the server. We just need to add --difftype=sql to the command line.
If you want to check data consistency, then you can use pt-table-checkum/pt-table-sync.
While in MySQL Model mode, from the menu Database -> Compare Schemas..., you can compare by Model Schemata, script file or connect to the servers directly.
To generate a transformation report containing SQL statements for transforming the objects for conformity, you can use mysqldiff which is part of MySQL utilities.
Usage: mysqldiff --server1=user:pass@host:port:socket --server2=user:pass@host:port:socket db1.object1:db2.object1 db3:db4
For example, to generate the sql statement for transforming demo database into live database in the same server:
$ mysqldiff --server1=user:password@i88server demo:live --difftype=sql --force
The utility stops on the first occurrence of missing objects or when an object does not match. To override this behavior, specify the --force option to cause the utility to attempt to compare all objects listed as arguments.
demo is put before live, so the output is for how to make the definitions of demo like the definitions of the corresponding objects of live. You can also get the reverse transformation by specifying the --show-reverse option. Or just switch their positions.
In some cases the diff output is not really useful. We actually need a SQL query to do the changes on the server. We just need to add --difftype=sql to the command line.
If you want to check data consistency, then you can use pt-table-checkum/pt-table-sync.
Comments
Post a Comment