Hello Folks,
Today I’ll be writing an article on how to do schema comparison of MySQL database between 2 servers(For eg. : PROD and UAT or PROD and DEV).
Let us consider Production server as A and UAT server as B.
Steps are as follows:
- Take the dump of all the tables present in Database A
- Take the dump of all the tables present in Database B
- Compare the above 2 .SQLs files using DIFF command in Unix. You can also use other tools like navicat or Toad edge, here I am using DIFF command on terminal.
While taking dump of the database, make sure you are taking only the schema and not the table data so the command for that will be :
|
1 2 3 |
mysqldump --no-data --single-transaction -h hostname -u mbisht -p --databases Prod_DB > Prod_DB.sql mysqldump --no-data --single-transaction -h hostname -u mbisht -p --databases UAT_DB > UAT_DB.sql |
The reason being we are taking the dump without data is because we need to compare the schema only.
Once you have the 2 .SQLs file, you can use the DIFF command on the terminal:
|
1 |
diff -y Prod_DB.sql UAT_DB.sql > Difference.txt |
The -y flag that we are using will show the difference beside each other and it will be easy to compare the files.
