Compare two MySQL databases [closed]
I'm currently developing an application using a MySQL database.
The database-structure is still in flux and changes while development progresses (I change my local copy, leaving the one on the test-server alone).
Is there a way to compare the two instances of the database to see if there were any changes?
While currently simply discarding the previous test server database is fine, as testing starts entering test data it could get a bit tricky.
The same though more so will happen again later in production...
1Answer
If you're working with small databases I've found running mysqldump on both databases with the --skip-comments and --skip-extended-insert options to generate SQL scripts, then running diff on the SQL scripts works pretty well.
By skipping comments you avoid meaningless differences such as the time you ran the mysqldump command. By using the --skip-extended-insert command you ensure each row is inserted with its own insert statement. This eliminates the situation where a single new or modified record can cause a chain reaction in all future insert statements. Running with these options produces larger dumps with no comments so this is probably not something you want to do in production use but for development it should be fine. I've put examples of the commands I use below:
mysqldump --skip-comments --skip-extended-insert -u root -p dbName1>file1.sql
mysqldump --skip-comments --skip-extended-insert -u root -p dbName2>file2.sql
diff file1.sql file2.sql
- answered 8 years ago
- Sunny Solu
Your Answer