問題描述
我目前正在使用 MySQL 數據庫開發應用程序.
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?
答案中提到的工具:
- Red-Gate 的 MySQL Schema &數據比較(商業)
- Maatkit(現為 Percona)
- liquibase
- 蟾蜍
- Nob Hill 數據庫比較(商業)
- MySQL 差異
- SQL EDT(商業)
- Red-Gate's MySQL Schema & Data Compare (Commercial)
- Maatkit (now Percona)
- liquibase
- Toad
- Nob Hill Database Compare (Commercial)
- MySQL Diff
- SQL EDT (Commercial)
推薦答案
如果您使用的是小型數據庫，我發現在兩個數據庫上都使用 --skip-comments 和 --skip-extended-insert 選項來生成 SQL 腳本，然后在 SQL 腳本上運行 diff 效果很好.
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.
通過跳過注釋可以避免無意義的差異，例如運行 mysqldump 命令的時間.通過使用 --skip-extended-insert 命令，您可以確保使用自己的插入語句插入每一行.這消除了單個新記錄或修改記錄可能導致所有未來插入語句中的連鎖反應的情況.使用這些選項運行會產生更大的轉儲，沒有注釋，所以這可能不是你想要在生產使用中做的事情，但對于開發來說應該沒問題.我在下面列出了我使用的命令示例:
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
這篇關于比較兩個 MySQL 數據庫的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！