Restore posts and comments from an old mysql backup of WordPress
I was talking to my wife about her blog, Poots and Pans, and she was asking about the old blog I used to do with some friends, so I thought I would get my old posts back from old backups to look at my old content. I looked through old emails to find the latest backup and found it was a mysql backup and not an actual wordpress backup. So, what does that mean? It means that it is a little more difficult to get your content back up and running without having the same domain name under your supervision. If I still owned the domain name, it would be pretty easy, but I don’t, so I had to be creative.
I was running a Xampp installation on my computer using the newest version of WordPress, version 3.1. It had PHP, MySQL, all the good stuff already up and running. I use this install for testing wordpress in all its glory for themes and plugins because it is a lot easier to use than doing it on a remote server for things like customizing a theme and then uploading the changes.
I thought, well, I can just install another wordpress site and have it all back. If only it were that simple. I imported the sql.gz file using phpmyadmin, then pointed the new wordpress install to the existing database and thought that should do it. Two things I didn’t think about were that the database was using the old version of wordpress, somewhere around 2.6, and I would have to have access to the actual URL in order to get logged in to the site. I tried that, but upon trying to login to the site to get my posts, it sent me to the old URL which I no longer have access to.
I decided that since the content is in the database and I can see the content, there has to be an easy way to get the posts out because that is really all I care about.
I looked for converters and different methods but couldn’t find a way to get wp_posts to turn into a wordpress export file.
But then I thought about the structure of the wordpress database and that there were common themes between the database of the existing test I was running on localhost, and the new database I just imported, they both have a wp_posts table.
So, I thought it was worth a shot just to send the new wp_posts table to the current, running database. So I stayed in phpmyadmin and went to work.
I went to the existing database, called wordpress, and selected the wp_posts table from the left-hand nav and went up to the top of the page and selected “Drop”. This will drop all existing content in the wp_posts area, so all posts are gone for good.
Then I went to the new database and selected the wp_posts table and went to the operations tab.
From there I chose to move the table to my existing wordpress database with the same name for the table of wp_posts.
I hit Go, and everything is set. Now all I have to do is login to my existing wordpress site, and I can export my posts very easily. I can do the same thing for comments if I would like, using the wp_comments table.
Hope this helps some of you. Let me know if you have any questions.