Combine or merge 2 MySQL databases from 2 different web sites
I have a blog operating under WP 1.2.1. A friend has a blog operating under WP 1.5.x on a different host. We want to create a common blog on my hosting server under WP 1.5.x. I’m a total novice to MySQL databases, though I have installed mine using phpMyAdmin on my hosting server. Is there a way to combine/merge our databases on my server so that neither of us loses previous posts and comments? Thanks.
Basically it’s possible, but…
1. All the databases should be of the same version (= make all the blogs to use the same WP version; upgrade)
2. You should be aware that in both blogs there might be posts with the same ID#, like 1, 2, 3, etc. This would cause a conflict when “merging” the two db’s.
3. Same thing for the comments: comments have their own ID#s plus they are “tied” to the post’s ID.
I have never done this, I just moved a few posts from one blog to another. Actually I made an entry about it, if you are curious you can take a look at it here.
Thanks. I looked at your link. Since I have hundreds of posts on my site, it seems that perhaps the best way to go is to abandon them and start fresh. I know I shouldn’t be in love with my previous posts and not very many people read my blog anyway, but this choice pains my literary ego. Oh well … 🙂
southsou, be sure you keep a backup of the “old site” database. There may eventually be a way to recover the posts….
Though, I have not yet made similar project I think it can be done.
Merging two database might be a smaller problem it could be done with a little sweat.
First of all, I would suggest you to upgrade your blog to the same version that of the other blog.
After that, all depends on your scheme to combine the two blog.
Let us assume a complete merge.
After upgrade, dump both databases through phpmysqladmin.
Study carefully the dumps noting structure, syntax and data.
Your main concern will be the posts and comments tables, all the others will need only slight adjustments.
Decide the sequence you want the merge: 1 + 2 or 2 + 1 . Definitely, you should attach the table with less record to the larger one.
INSERT DELAYED INTOwp_posts
(1, 1, ....
The second entry is the post_author. If your are 1 then your friend is 2. Adjust wp_users data accordingly. Now change all.
The first entry is the post ID. If the first database ends e.g. at 465 then start numbering with 466 and adjust all entries. Do not forget to adjust above the insert the AUTO_INCREMENT VALUE= to the next highest value after the combined total.
Unfortunately, you should do this manually unless you have some other method to automate the process. It may be through SQL-query or macro in the editor. I have no knowledge of them 🙁
Next, your comments:
INSERT DELAYED INTOwp_comments
(1, 1 - 2, 1 - 3 - 1 ...... 1, 2 - 2, 2 - 3, 2 ......... 1, 3 - 2, 3 ......
These mean 3-3 comments in the post 1 and two and two comments in post 3.
That may be a hell of a lot if you have many comments. It may be a day or two hard work.
If you are lucky, some knowledgable people may offer you an sql-query to do the increment from – to automatically. You still need adjust your categories and users tables as well but those are small tasks.
I tried to be thorough but may have missed something. If you people care to revise my post, you can make your corrections.
Thank you. I’m laying in a stash of high calorie and high caffein food for the attempt. Maybe I’ll get lucky.
First, as you’ve seen above, make sure you have a backup of BOTH databases before doing anything. And then after upgrading the older blog and you’ve got everything working, backup AGAIN.
Just some other points to consider:
1. Categories–both blogs may have differently “named” Categories but there’s a good chance they share the same cat_id (or number). I’m referring to the post Categories here, not the Link Categories that would have the same issue but guessing you might manually meld those.
2. Each post has record in the post2cat table that relates that post to the category used for that post.
3. Look at the Codex Importing Content article. Maybe it makes sense to use RSS or modify one of the scripts mentioned in that article. I know, easier said than done.
How did it all go?
Did you successfully merge your blogs?
I’m hoping for good news as I want to do this also.
i want to do the same thing, (merge me+my friend’s blogs) but i’m always on the look out for an easy option.
so i tried ‘simply’ exporting with the MTexport plugin, and importing them via import-mt.php.
and IT WORKED. brought in all the posts, the right author, dates, comments, categories, etc.
but but but… it messed up my japanese. i want to find out how to make this will work with Unicode.
anyone know more about this?
aha- just want to say, it worked fine with my japanese, as soon as i saved the exported text as UTF-8. (duh)
works perfectly for merging blog posts from twwo diff blogs, without all that horrible database play.
- The topic ‘Combine or merge 2 MySQL databases from 2 different web sites’ is closed to new replies.