比较两个 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/225772/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare two MySQL databases
提问by Vincent Ramdhanie
I'm currently developing an application using a MySQL database.
我目前正在使用 MySQL 数据库开发应用程序。
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)
采纳答案by Jared
If you're working with small databases I've found running mysqldump on both databases with the --skip-commentsand --skip-extended-insertoptions to generate SQL scripts, then running diff on the SQL scripts works pretty well.
如果您正在使用小型数据库，我发现在两个数据库上运行 mysqldump 并使用--skip-comments和--skip-extended-insert选项生成 SQL 脚本，然后在 SQL 脚本上运行 diff 效果很好。
By skipping comments you avoid meaningless differences such as the time you ran the mysqldump command. By using the --skip-extended-insertcommand 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-extended-insert命令，您可以确保使用自己的插入语句插入每一行。这消除了单个新记录或修改记录可能导致所有未来插入语句中的连锁反应的情况。使用这些选项运行会产生更大的转储，没有注释，所以这可能不是你想要在生产使用中做的事情，但对于开发来说应该没问题。我在下面列出了我使用的命令的示例：
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
回答by Anson Smith
Toad for MySQLhas data and schema compare features, and I believe it will even create a synchronization script. Best of all, it's freeware.
Toad for MySQL具有数据和模式比较功能，我相信它甚至会创建一个同步脚本。最重要的是，它是免费软件。
回答by seanyboy
I use a piece of software called Navicatto :
我使用一个名为Navicat的软件来：
- Sync Live databases to my test databases.
- Show differences between the two databases.
- 将实时数据库同步到我的测试数据库。
- 显示两个数据库之间的差异。
It costs money, it's windows and mac only, and it's got a whacky UI, but I like it.
它要花钱，仅适用于 windows 和 mac，而且有一个古怪的用户界面，但我喜欢它。
回答by Yury Litvinov
回答by andyhky
From the feature comparison list... MySQL Workbenchoffers Schema Diff and Schema Synchronization in their community edition.
从功能比较列表中... MySQL Workbench在其社区版中提供了 Schema Diff 和 Schema Synchronization。
回答by develCuy
There are many ways certainly, but in my case I prefer the dump and diff command. So here is an script based on Jared's comment:
当然有很多方法，但就我而言，我更喜欢 dump 和 diff 命令。所以这是一个基于 Jared 评论的脚本：
#!/bin/sh
echo "Usage: dbdiff [user1:pass1@dbname1] [user2:pass2@dbname2] [ignore_table1:ignore_table2...]"
dump () {
up=${1%%@*}; user=${up%%:*}; pass=${up##*:}; dbname=${1##*@};
mysqldump --opt --compact --skip-extended-insert -u $user -p$pass $dbname $table >
}
rm -f /tmp/db.diff
# Compare
up=${1%%@*}; user=${up%%:*}; pass=${up##*:}; dbname=${1##*@};
for table in `mysql -u $user -p$pass $dbname -N -e "show tables" --batch`; do
if [ "`echo | grep $table`" = "" ]; then
echo "Comparing '$table'..."
dump /tmp/file1.sql
dump /tmp/file2.sql
diff -up /tmp/file1.sql /tmp/file2.sql >> /tmp/db.diff
else
echo "Ignored '$table'..."
fi
done
less /tmp/db.diff
rm -f /tmp/file1.sql /tmp/file2.sql
Feedback is welcome :)
欢迎反馈:)
回答by jmpeace
dbSolo, it is paid but this feature might be the one you are looking for http://www.dbsolo.com/help/compare.html
dbSolo，它是付费的，但此功能可能是您正在寻找的功能 http://www.dbsolo.com/help/compare.html
It works with Oracle, Microsoft SQL Server, Sybase, DB2, Solid, PostgreSQL, H2 and MySQL
它适用于 Oracle、Microsoft SQL Server、Sybase、DB2、Solid、PostgreSQL、H2 和 MySQL
回答by Zac
If you only need to compare schemas (not data), and have access to Perl, mysqldiff might work. I've used it because it lets you compare local databases to remote databases (via SSH), so you don't need to bother dumping any data.
如果您只需要比较模式（而不是数据），并且可以访问 Perl，那么 mysqldiff 可能会起作用。我使用它是因为它可以让您将本地数据库与远程数据库（通过 SSH）进行比较，因此您无需费心转储任何数据。
http://adamspiers.org/computing/mysqldiff/
http://adamspiers.org/computing/mysqldiff/
It will attempt to generate SQL queries to synchronize two databases, but I don't trust it (or any tool, actually). As far as I know, there's no 100% reliable way to reverse-engineer the changes needed to convert one database schema to another, especially when multiple changes have been made.
它会尝试生成 SQL 查询来同步两个数据库，但我不信任它（实际上也不信任任何工具）。据我所知，没有 100% 可靠的方法可以对将一种数据库模式转换为另一种数据库模式所需的更改进行逆向工程，尤其是在进行了多次更改时。
For example, if you change only a column's type, an automated tool can easily guess how to recreate that. But if you also move the column, rename it, and add or remove other columns, the best any software package can do is guess at what probably happened. And you may end up losing data.
例如，如果您仅更改列的类型，则自动化工具可以轻松猜测如何重新创建该类型。但是，如果您还移动列、重命名它并添加或删除其他列，那么任何软件包所能做的最好的事情就是猜测可能发生了什么。您最终可能会丢失数据。
I'd suggest keeping track of any schema changes you make to the development server, then running those statements by hand on the live server (or rolling them into an upgrade script or migration). It's more tedious, but it'll keep your data safe. And by the time you start allowing end users access to your site, are you really going to be making constant heavy database changes?
我建议跟踪您对开发服务器所做的任何架构更改，然后在实时服务器上手动运行这些语句（或将它们滚动到升级脚本或迁移中）。这更乏味，但它会保证您的数据安全。当您开始允许最终用户访问您的站点时，您真的要不断地对数据库进行大量更改吗？
回答by anders.norgaard
Have a look at http://www.liquibase.org/
回答by likeuclinux
check: http://schemasync.org/the schemasync tool works for me, it is a command line tool works easily in linux command line
检查：http: //schemasync.org/schemasync 工具对我有用，它是一个命令行工具，可以在 linux 命令行中轻松工作
