We've been developing for Wordpress for several years and whilst our workflow has been upgraded at several points there's one thing that we've never solved... merging a local Wordpress database with a live database.
我们已经为Wordpress开发了好几年，虽然我们的工作流程已经升级，但有一件事我们从未解决过……将本地Wordpress数据库与实时数据库合并。
So I'm talking about having a local version of the site where files and data are changed, whilst the data on the live site is also changing at the same time.
所以我说的是有一个本地版本的网站文件和数据被改变，同时现场的数据也在同时改变。
All I can find is the perfect world scenario of pulling the site down, nobody (even customers) touching the live site, then pushing the local site back up. I.e copying one thing over the other.
我所能找到的是一个完美的世界场景:把网站拖下，没有人(甚至是顾客)接触到现场，然后把本地网站推回原处。我。把一件东西复制到另一件上。
How can this be done without running a tonne of mysql commands? (it feels like they could fall over if they're not properly checked!) Can this be done via Gulp's (I've seen it mentioned) or a plugin?
如果不运行大量的mysql命令，怎么做呢?(如果不进行适当的检查，他们就会摔倒!)这可以通过Gulp的(我见过)或者插件来实现吗?
Just to be clear, I'm not talking about pushing/pulling data back and forth via something like WP Migrate DB Pro, BackupBuddy or anything similar - this is a merge, not replacing one database with another.
明确地说，我并不是在讨论通过诸如WP Migrate DB Pro、BackupBuddy之类的东西来前后推动或提取数据——这是一种合并，而不是用另一个数据库来替换另一个数据库。
I would love to know how other developers get around this!
我很想知道其他开发人员是如何解决这个问题的!
File changes are fairly simple to get around, it's when there's data changes that it causes the nightmare.
文件更改很容易处理，只有当有数据更改时才会引起噩梦。
WP Stagecoach does do a merge but you can't work locally, it creates a staging site from the live site that you're supposed to work on. The merge works great but it's a killer blow not to be able to work locally.
WP Stagecoach确实做了合并，但是你不能在本地工作，它会从你要工作的现场创建一个临时站点。合并工作很好，但如果不能在本地运行，那将是致命的打击。
I've also been told by the developers that datahawk.io will do what I want but there's no release date on that.
开发人员还告诉我datahawk。io会做我想做的事，但是没有发布日期。
9 个解决方案
#1
5
It sounds like VersionPress might do what you need:
听起来VersionPress可能会做你需要的:
VersionPress分期
A couple of caveats: I haven't used it, so can't vouch for its effectiveness; and it's currently in early access.
有两个注意事项:我没有使用它，所以不能保证它的有效性;目前它还处于早期使用阶段。
#2
4
Important : Take a backup of Live database before merging Local data to it.
重要提示:在将本地数据合并到实时数据库之前进行备份。
Follow these steps might help in migrating the large percentage of data and merging it to live
遵循这些步骤可能有助于迁移大量数据并将其合并为活动数据
- Go to wp back-end of Local site Tools->Export.
- 转到本地站点工具的wp后端——>导出。
- Select All content radio button (if not selected by default).
- 选择所有内容单选按钮(如果不是默认选择)。
- This will bring an Xml file containing all the local data comprised of all default post types and custom post types.
- 这将带来一个包含由所有默认post类型和自定义post类型组成的所有本地数据的Xml文件。
- Open this XML file in notepad++ or any editor and find and replace the Local URL with the Live URL.
- 在notepad+或任何编辑器中打开这个XML文件，并使用Live URL查找并替换本地URL。
- Now visit the Live site and Import the XML under Tools->Import.
- 现在访问Live站点并导入工具—>导入下的XML。
- Upload the files (images) manually.
- 手动上传文件(图像)。
This will bring a large percentage of data from Local to Live .
这将带来大量来自本地的数据。
Rest of the data you will have to write custom scripts.
剩下的数据必须编写自定义脚本。
Risk factors are :
风险因素:
- When uploading the images from Local to Live , images of same name will be overriden.
- 当从本地上传图片到现场时，同名图片将被上传。
- Wordpress saves the images in post_meta generating a serialized data for the images , than should be taken care of when uploading the database.
- Wordpress将图片保存在post_meta中，为图片生成序列化的数据，在上传数据库时应该注意这一点。
- Serialized data in post_meta for
post_type="attachment"saves serialized data for 3 or 4 dimensions of the images. - post_type=“附件”的post_meta中的序列化数据为图像的3或4个维度保存序列化数据。
- Usernames or email ids of users when importing the data , can be same (Or wp performs the function of checking unique usernames and emails) then those users will not be imported (might be possible).
- 用户的用户名或电子邮件id在导入数据时可以是相同的(或者wp执行检查唯一用户名和电子邮件的功能)，那么这些用户将不会被导入(可能)。
#3
3
If I were you I'd do the following (slow but affords you the greatest chance of success)
如果我是你，我会做以下的事(缓慢但给你最大的成功机会)
First off, set up a third database somewhere. Cloud services would probably be ideal, since you could get a powerful server with an SSD for a couple of hours. You'll need that horsepower.
首先，在某处建立第三个数据库。云服务可能是理想的，因为您可以在几个小时内使用SSD获得功能强大的服务器。你需要马力。
Second, we're going to mysqldump the first DB and pipe the output into our cloud DB.
其次，我们将使用mysqldump转储第一个DB并将输出传输到云DB。
mysqldump -u user -ppassword dbname | mysql -u root -ppass -h somecloud.db.internet
Now we have a full copy of DB #1. If your cloud supports snapshotting data, be sure to take one now.
现在我们有了DB #1的完整副本。如果您的云支持快照数据，那么现在一定要使用它。
The last step is to write a PHP script that, slowly but surely, selects the data from the second DB and writes it to the third. We want to do this one record at a time. Why? Well, we need to maintain the relationships between records. So let's take comments and posts. When we pull post #1 from DB #2 it won't be able to keep record #1 because DB #1 already had one. So now post #1 becomes post #132. That means that all the comments for post #1 now need to be written as belonging to post #132. You'll also have to pull the records for the users who made those posts, because their user IDs will also change.
最后一步是编写一个PHP脚本，该脚本缓慢而稳定地从第二个DB中选择数据并将其写到第三个DB中。我们想一次做一个记录。为什么?我们需要维护记录之间的关系。我们来看看评论和帖子。当我们从DB #2中取出post #1时，它将无法保持记录#1，因为DB #1已经有一个了。所以现在post #1变成了#132。这意味着所有关于第1条的评论现在都需要写成属于第132条。您还必须为发布这些帖子的用户提取记录，因为他们的用户id也会改变。
There's no easy fix for this but the WP structure isn't terribly complex. Building a simple loop to pull the data and translate it shouldn't be more then a couple of hours of work.
解决这个问题并不容易，但是WP结构并不复杂。构建一个简单的循环来提取数据并转换它不应该需要花费几个小时的工作。
#4
2
If I understand you, to merge local and live database, until now I'm using other software such as NavicatPremium, it has Data Sycn feature.
如果我理解您的意思，要合并本地和实时数据库，直到现在我还在使用其他软件，比如NavicatPremium，它有数据Sycn功能。
#5
2
This can be achieved live using spring-xd, create a JDBC Stream to pull data from one db and insert into the other. (This acts as streaming so you don't have to disturb any environment)
这可以通过spring-xd实现，创建JDBC流从一个db中提取数据并插入到另一个db中。(这就像流媒体一样，所以你不必去打扰任何环境)
#6
2
The first thing you need to do is asses if it would be easier to do some copy-paste data entry instead of a migration script. Sometimes the best answer is to suck it up and do it manually using the CMS interface. This avoids any potential conflicts with merging primary keys, but you may need to watch for references like the creator of a post or similar data.
您需要做的第一件事是评估，如果做一些复制粘贴数据条目而不是迁移脚本更容易的话。有时最好的解决办法是把它吸起来，然后使用CMS界面手工操作。这避免了合并主键的任何潜在冲突，但是您可能需要注意类似于post或类似数据的创建者的引用。
If it's just outright too much to manually migrate, you're stuck with writing a script or finding one that is already written for you. Assuming there's nothing out there, here's what you do...
如果手工迁移的工作量太大，您就会陷入编写脚本或寻找已经为您编写的脚本的困境。假设外面什么都没有，你要做的是……
ALWAYS MAKE A BACKUP BEFORE RUNNING MIGRATIONS!
在运行迁移之前一定要进行备份!
1) Make a list of what you need to transfer. Do you need users, posts, etc.? Find the database tables and add them to the list.
1)列出你需要转移的东西。你需要用户、帖子等吗?找到数据库表并将它们添加到列表中。
2) Make a note all possible foreign keys in the database tables being merged into the new database. For example, wp_posts has post_author referencing wp_users. These will need specific attention during the migration. Use this documentation to help find them.
2)记录将合并到新数据库中的数据库表中所有可能的外键。例如，wp_posts具有引用wp_users的post_author。在迁移过程中需要特别注意这些问题。使用此文档帮助查找它们。
3) Once you know what tables you need and what they reference, you need to write the script. Start by figuring out what content is new for the other database. The safest way is to do this manually with some kind of side-by-side list. However, you can come up with your own rules on how to automatically match table rows. Maybe to check for $post1->post_content === $post2->post_content in cases the text needs to be the same. The only catch here is the primary/foreign keys are off limits for these rules.
3)一旦知道需要什么表以及它们引用什么表，就需要编写脚本。首先要弄清楚其他数据库的新内容。最安全的方法是使用某种并排的列表来手动执行。但是，您可以针对如何自动匹配表行制定自己的规则。可能要检查$post1->post_content == $post2->post_content，如果文本需要相同的话。这里唯一的问题是主/外键对这些规则是禁止的。
4) How do you merge new content? The general idea is that all primary keys will need to be changed for any new content. You want to use everything except for the id of post and insert that into the new database. There will be an auto-increment to create the new id, so you wont need the previous id (unless you want it for script output/debug).
4)如何合并新内容?一般的想法是，所有主键都需要为任何新内容更改。您希望使用除post id之外的所有内容，并将其插入到新的数据库中。将会有一个自动增量来创建新id，因此您不需要以前的id(除非您希望它用于脚本输出/调试)。
5) The tricky part is handling the foreign keys. This process is going to vary wildly depending on what you plan on migrating. What you need to know is which foreign key goes to which (possibly new) primary key. If you're only migrating posts, you may need to hard-code a user id to user id mapping for the post_author column, then use this to replace the values.
棘手的部分是处理外键。这个过程将根据您计划迁移的内容而变化很大。您需要知道的是哪个外键指向哪个主键(可能是新的)。如果您只是迁移文章，您可能需要为post_author列硬编码用户id到用户id映射，然后使用它来替换值。
But what if I don't know the user ids for the mapping because some users also need to be migrated?
但是如果我不知道映射的用户id，因为有些用户也需要迁移怎么办?
This is where is gets tricky. You will need to first define the merge rules to see if a user already exists. For new users, you need record the id of the newly inserted users. Then after all users are migrated, the post_author value will need to be replaced when it references a newly merged user.
这是一个棘手的问题。您需要首先定义合并规则，以查看用户是否已经存在。对于新用户，需要记录新插入用户的id。然后，在迁移所有用户之后，需要在引用新合并的用户时替换post_author值。
6) Write and test the script! Test it on dummy databases first. And again, make backups before using it on your databases!
6)编写并测试脚本!首先在虚拟数据库上测试它。同样，在您的数据库上使用它之前进行备份!
#7
2
I've done something simillar with ETL (Extract, Transform, Load) process when I was moving data from one CMS to another.
当我将数据从一个CMS移动到另一个CMS时，我用ETL(提取、转换、加载)过程做了一些类似的事情。
Rather than writing a script I used a Pentaho Data Integration (Kettle) tool.
我没有编写脚本，而是使用了Pentaho Data Integration (Kettle)工具。
The Idea of ETL is pretty much straight forward:
ETL的想法非常直接:
- Extract the data (for instance from one database)
- 提取数据(例如从一个数据库)
- Transform it to suit your needs
- 转换它以适应您的需要
- Load it to the final destination (your second database).
- 将它加载到最终目的地(您的第二个数据库)。
The tool is easy to use and it allows you to experiment with various steps and outputs to investigate the data. When you design a right ETL proces, you are ready to merge those databases of yours.
这个工具很容易使用，它允许您尝试不同的步骤和输出来调查数据。当您设计一个正确的ETL过程时，您就可以合并您的数据库了。
#8
1
How can this be done without running a tonne of mysql commands?
如果不运行大量的mysql命令，怎么做呢?
No way. If both local and web sites are running at the same time how can you prevent not having the same ids' with different content?
不可能。如果本地和web站点同时运行，如何防止不具有相同id的不同内容?
#9
-2
so if you want to do this you can use mysql repication.i think it will help you to merge with different database mysql.
如果你想这样做，可以使用mysql repication。我认为这会帮助你合并不同的数据库mysql。
#1
5
It sounds like VersionPress might do what you need:
听起来VersionPress可能会做你需要的:
VersionPress分期
A couple of caveats: I haven't used it, so can't vouch for its effectiveness; and it's currently in early access.
有两个注意事项:我没有使用它，所以不能保证它的有效性;目前它还处于早期使用阶段。
#2
4
Important : Take a backup of Live database before merging Local data to it.
重要提示:在将本地数据合并到实时数据库之前进行备份。
Follow these steps might help in migrating the large percentage of data and merging it to live
遵循这些步骤可能有助于迁移大量数据并将其合并为活动数据
- Go to wp back-end of Local site Tools->Export.
- 转到本地站点工具的wp后端——>导出。
- Select All content radio button (if not selected by default).
- 选择所有内容单选按钮(如果不是默认选择)。
- This will bring an Xml file containing all the local data comprised of all default post types and custom post types.
- 这将带来一个包含由所有默认post类型和自定义post类型组成的所有本地数据的Xml文件。
- Open this XML file in notepad++ or any editor and find and replace the Local URL with the Live URL.
- 在notepad+或任何编辑器中打开这个XML文件，并使用Live URL查找并替换本地URL。
- Now visit the Live site and Import the XML under Tools->Import.
- 现在访问Live站点并导入工具—>导入下的XML。
- Upload the files (images) manually.
- 手动上传文件(图像)。
This will bring a large percentage of data from Local to Live .
这将带来大量来自本地的数据。
Rest of the data you will have to write custom scripts.
剩下的数据必须编写自定义脚本。
Risk factors are :
风险因素:
- When uploading the images from Local to Live , images of same name will be overriden.
- 当从本地上传图片到现场时，同名图片将被上传。
- Wordpress saves the images in post_meta generating a serialized data for the images , than should be taken care of when uploading the database.
- Wordpress将图片保存在post_meta中，为图片生成序列化的数据，在上传数据库时应该注意这一点。
- Serialized data in post_meta for
post_type="attachment"saves serialized data for 3 or 4 dimensions of the images. - post_type=“附件”的post_meta中的序列化数据为图像的3或4个维度保存序列化数据。
- Usernames or email ids of users when importing the data , can be same (Or wp performs the function of checking unique usernames and emails) then those users will not be imported (might be possible).
- 用户的用户名或电子邮件id在导入数据时可以是相同的(或者wp执行检查唯一用户名和电子邮件的功能)，那么这些用户将不会被导入(可能)。
#3
3
If I were you I'd do the following (slow but affords you the greatest chance of success)
如果我是你，我会做以下的事(缓慢但给你最大的成功机会)
First off, set up a third database somewhere. Cloud services would probably be ideal, since you could get a powerful server with an SSD for a couple of hours. You'll need that horsepower.
首先，在某处建立第三个数据库。云服务可能是理想的，因为您可以在几个小时内使用SSD获得功能强大的服务器。你需要马力。
Second, we're going to mysqldump the first DB and pipe the output into our cloud DB.
其次，我们将使用mysqldump转储第一个DB并将输出传输到云DB。
mysqldump -u user -ppassword dbname | mysql -u root -ppass -h somecloud.db.internet
Now we have a full copy of DB #1. If your cloud supports snapshotting data, be sure to take one now.
现在我们有了DB #1的完整副本。如果您的云支持快照数据，那么现在一定要使用它。
The last step is to write a PHP script that, slowly but surely, selects the data from the second DB and writes it to the third. We want to do this one record at a time. Why? Well, we need to maintain the relationships between records. So let's take comments and posts. When we pull post #1 from DB #2 it won't be able to keep record #1 because DB #1 already had one. So now post #1 becomes post #132. That means that all the comments for post #1 now need to be written as belonging to post #132. You'll also have to pull the records for the users who made those posts, because their user IDs will also change.
最后一步是编写一个PHP脚本，该脚本缓慢而稳定地从第二个DB中选择数据并将其写到第三个DB中。我们想一次做一个记录。为什么?我们需要维护记录之间的关系。我们来看看评论和帖子。当我们从DB #2中取出post #1时，它将无法保持记录#1，因为DB #1已经有一个了。所以现在post #1变成了#132。这意味着所有关于第1条的评论现在都需要写成属于第132条。您还必须为发布这些帖子的用户提取记录，因为他们的用户id也会改变。
There's no easy fix for this but the WP structure isn't terribly complex. Building a simple loop to pull the data and translate it shouldn't be more then a couple of hours of work.
解决这个问题并不容易，但是WP结构并不复杂。构建一个简单的循环来提取数据并转换它不应该需要花费几个小时的工作。
#4
2
If I understand you, to merge local and live database, until now I'm using other software such as NavicatPremium, it has Data Sycn feature.
如果我理解您的意思，要合并本地和实时数据库，直到现在我还在使用其他软件，比如NavicatPremium，它有数据Sycn功能。
#5
2
This can be achieved live using spring-xd, create a JDBC Stream to pull data from one db and insert into the other. (This acts as streaming so you don't have to disturb any environment)
这可以通过spring-xd实现，创建JDBC流从一个db中提取数据并插入到另一个db中。(这就像流媒体一样，所以你不必去打扰任何环境)
#6
2
The first thing you need to do is asses if it would be easier to do some copy-paste data entry instead of a migration script. Sometimes the best answer is to suck it up and do it manually using the CMS interface. This avoids any potential conflicts with merging primary keys, but you may need to watch for references like the creator of a post or similar data.
您需要做的第一件事是评估，如果做一些复制粘贴数据条目而不是迁移脚本更容易的话。有时最好的解决办法是把它吸起来，然后使用CMS界面手工操作。这避免了合并主键的任何潜在冲突，但是您可能需要注意类似于post或类似数据的创建者的引用。
If it's just outright too much to manually migrate, you're stuck with writing a script or finding one that is already written for you. Assuming there's nothing out there, here's what you do...
如果手工迁移的工作量太大，您就会陷入编写脚本或寻找已经为您编写的脚本的困境。假设外面什么都没有，你要做的是……
ALWAYS MAKE A BACKUP BEFORE RUNNING MIGRATIONS!
在运行迁移之前一定要进行备份!
1) Make a list of what you need to transfer. Do you need users, posts, etc.? Find the database tables and add them to the list.
1)列出你需要转移的东西。你需要用户、帖子等吗?找到数据库表并将它们添加到列表中。
2) Make a note all possible foreign keys in the database tables being merged into the new database. For example, wp_posts has post_author referencing wp_users. These will need specific attention during the migration. Use this documentation to help find them.
2)记录将合并到新数据库中的数据库表中所有可能的外键。例如，wp_posts具有引用wp_users的post_author。在迁移过程中需要特别注意这些问题。使用此文档帮助查找它们。
3) Once you know what tables you need and what they reference, you need to write the script. Start by figuring out what content is new for the other database. The safest way is to do this manually with some kind of side-by-side list. However, you can come up with your own rules on how to automatically match table rows. Maybe to check for $post1->post_content === $post2->post_content in cases the text needs to be the same. The only catch here is the primary/foreign keys are off limits for these rules.
3)一旦知道需要什么表以及它们引用什么表，就需要编写脚本。首先要弄清楚其他数据库的新内容。最安全的方法是使用某种并排的列表来手动执行。但是，您可以针对如何自动匹配表行制定自己的规则。可能要检查$post1->post_content == $post2->post_content，如果文本需要相同的话。这里唯一的问题是主/外键对这些规则是禁止的。
4) How do you merge new content? The general idea is that all primary keys will need to be changed for any new content. You want to use everything except for the id of post and insert that into the new database. There will be an auto-increment to create the new id, so you wont need the previous id (unless you want it for script output/debug).
4)如何合并新内容?一般的想法是，所有主键都需要为任何新内容更改。您希望使用除post id之外的所有内容，并将其插入到新的数据库中。将会有一个自动增量来创建新id，因此您不需要以前的id(除非您希望它用于脚本输出/调试)。
5) The tricky part is handling the foreign keys. This process is going to vary wildly depending on what you plan on migrating. What you need to know is which foreign key goes to which (possibly new) primary key. If you're only migrating posts, you may need to hard-code a user id to user id mapping for the post_author column, then use this to replace the values.
棘手的部分是处理外键。这个过程将根据您计划迁移的内容而变化很大。您需要知道的是哪个外键指向哪个主键(可能是新的)。如果您只是迁移文章，您可能需要为post_author列硬编码用户id到用户id映射，然后使用它来替换值。
But what if I don't know the user ids for the mapping because some users also need to be migrated?
但是如果我不知道映射的用户id，因为有些用户也需要迁移怎么办?
This is where is gets tricky. You will need to first define the merge rules to see if a user already exists. For new users, you need record the id of the newly inserted users. Then after all users are migrated, the post_author value will need to be replaced when it references a newly merged user.
这是一个棘手的问题。您需要首先定义合并规则，以查看用户是否已经存在。对于新用户，需要记录新插入用户的id。然后，在迁移所有用户之后，需要在引用新合并的用户时替换post_author值。
6) Write and test the script! Test it on dummy databases first. And again, make backups before using it on your databases!
6)编写并测试脚本!首先在虚拟数据库上测试它。同样，在您的数据库上使用它之前进行备份!
#7
2
I've done something simillar with ETL (Extract, Transform, Load) process when I was moving data from one CMS to another.
当我将数据从一个CMS移动到另一个CMS时，我用ETL(提取、转换、加载)过程做了一些类似的事情。
Rather than writing a script I used a Pentaho Data Integration (Kettle) tool.
我没有编写脚本，而是使用了Pentaho Data Integration (Kettle)工具。
The Idea of ETL is pretty much straight forward:
ETL的想法非常直接:
- Extract the data (for instance from one database)
- 提取数据(例如从一个数据库)
- Transform it to suit your needs
- 转换它以适应您的需要
- Load it to the final destination (your second database).
- 将它加载到最终目的地(您的第二个数据库)。
The tool is easy to use and it allows you to experiment with various steps and outputs to investigate the data. When you design a right ETL proces, you are ready to merge those databases of yours.
这个工具很容易使用，它允许您尝试不同的步骤和输出来调查数据。当您设计一个正确的ETL过程时，您就可以合并您的数据库了。
#8
1
How can this be done without running a tonne of mysql commands?
如果不运行大量的mysql命令，怎么做呢?
No way. If both local and web sites are running at the same time how can you prevent not having the same ids' with different content?
不可能。如果本地和web站点同时运行，如何防止不具有相同id的不同内容?
#9
-2
so if you want to do this you can use mysql repication.i think it will help you to merge with different database mysql.
如果你想这样做，可以使用mysql repication。我认为这会帮助你合并不同的数据库mysql。