比较两个 MySQL 数据库[关闭]

Question

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

Is there an easy way to incrementally make changes to the production database, preferably by automatically creating a script to modify it?

---

Tools mentioned in the answers:

• Red-Gate's MySQL Schema & Data Compare (Commercial)
• Maatkit (now Percona)
• liquibase
• Toad
• Nob Hill Database Compare (Commercial)
• MySQL Diff
• SQL EDT (Commercial)

转载于:https://stackoverflow.com/questions/225772/compare-two-mysql-databases

Answer 1

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