44
Responses
Comments
Trackbacks/Pingbacks
-
[…] Moving Your WordPress Site and Database […]
-
[…] Moving Your WordPress Site and Database […]
-
[…] Moving Your WordPress Site and Database « Weblog Tools Collection – here comes a time you may have to leave one web host for another. No matter the reason the idea of relocating an entire website – especially one that has been online for sometime – can seem very daunting. With a WordPress based site – I found it easi… […]
-
[…] Cleaning Up A WordPress Database After Plugins and Themes Are Uninstalled No responses by Richard Hay on May 28th, 2010 in WordPress Discussions, WordPress Plugins I have been thinking about this post for a while. It really started to come to me after I went through a server move of my own website which I discussed on this site last month – Moving Your WordPress Site and Database. […]
Translate This Page
About the Author
- Richard Hay
I have served for 26 plus years in the Navy and am a Master Chief Petty Officer. My work background in the Navy is telecommunications related so my hobby of computers fit well in what I did for the Navy. I consider myself a tech geek and enjoy most things in that arena. My first website - AnotherWin95.com - came online in 1996. Back then I used GeoCities Web Hosting for it and have all the GeoCities “stuff” to show for it! I have progressed from a single page, link-filled, static html page to using PHP, databases and coding to make my pages.
Categories
- bbpress
- Best of WordPress
- Blog Templates Blog Skins Blog Themes
- Blogging
- Blogging Essays
- Blogging News
- brainstorming
- buddypress
- Business of Blogging
- CMS
- Code
- Cold Fusion
- Community Interviews
- Cool Scripts
- General
- HOW-TO
- LinkyLoo
- Movable Type Plugins
- Photolog Script
- Podcasting
- QuickLinking
- Spam
- SXSW
- TLA
- Tutorials
- User Reviews
- Web Design
- Web Ethics
- Weblog Add-Ons
- Weblog tools blog tools blogging tools
- Weekly Plugin Review
- WLTC Community
- wordcamp
- WordPress
- WordPress Discussions
- WordPress FAQs
- Wordpress for Beginners
- WordPress Hack
- WordPress News
- WordPress Plugin Competition
- WordPress Plugins
- WordPress Polls
- WordPress Security
- WordPress Templates WordPress Skins WordPress Themes
- WordPress Tips
- WordPress Tools
- WordPress Troubleshooting
- WordPress Weekly
- WordPress Widgets
- WordPress WishList
- XHTML Tips
Obviously Powered by WordPress. © 2003-2013
I recently had to go through this when I needed to upgrade to a newer version of MySQL so I could update WordPress. The WP-DBmanager plugin worked as you say, but somehow, instead of period-and-spaces, I got  all over the posts. No idea how to fix all that, so I mostly left it alone and used a plugin to put an explanation at the bottom of each post.
You might try the Search REGEX plugin – might allow to search and replace those characters in the database. Make a backup though just to be safe.
I used the Search REGEX plugin to replace an old ad format shortcode once and it worked well.
Thank you. I’d never heard of that plugin before, and it looks like it might do the job.
I have a feeling this may come in handy one day
Thanks!
I have moved quite a lot SEO technically not smart then again for a private blog: who cares
a) I have moved from other blog system to WordPress (losing all comments)
b) I have moved from different domain names to other domain names
c) I have moved from different domein names to other domain names AND put all the pages on another root+gave them another name
1. I would not use an automated export but a manual one, since this is the most excellent opportunity to finally kill
those dozens of tables left over from long-gone plugins (e.g. the e-commerce things, trackers, etc…).
2. It takes the same approach though you need to scan the exported SQL file for your old domain name and make the necessary changes (assuming you know what you are doing and not kill the options table). Just look for every “hit” (and previous
names if you moved before) and analyze the patterns, you will get a handful of replacements that work overall. Those
handful of replacements might be handy to blog about another time since they are always the same. Maybe you can at the same
time kill some entries in the options table.
3. After loading it in I had many pages on a different location / root, so that was easier done with SQL queries in the wp-post table then by using regex on the sql file (i take the easiest way SQL is much easier) (DreamCoder is FREE
and a real nice SQL tool to run it locally and connect to your external SQL databases) (so for the hardlinks inside
the posts themselves !!)
4. For some remaining “other site + other page + other name of page” things I added some lines to the .htaccess of the current site
5. Because many things ended up at different permalinks I choose to .htaccess redirect EVERYTHING to the ROOT of the new site and then have the special cases handled and do some logging for some time to see if I forgot something.
6. Since the comments are pretty much not recoverable if the old pages are gone (i generate them via xmlrpc from my localhost db) I well… lost all the comments on the pages (but no the posts) before I also lost them when I moved from my own
written blog tool to WordPress (I still have them hoping I have a brilliant regex idea some day).
7. Conversion to unicode tables … takes a 2 step approach
8. I learned that it is mostly done in a hurry while if you think and make a plan before doing it, it will save much time instead of doing it and repairing afterwards.
I now want to move from WPMU/BuddyPress on Apache to WPMU/BuddyPress on Nginx, so let’s see if that goes without any problem (and I HOPE upgrading to WP3.0 FROM WPMU …. will go with not much pain incl. all the self written code).
Just backup the whole database and wp-content, and then restore.
For changing the site URL, u must go to database’s wp_options and edit rows ‘siteurl’ and ‘home’.
Then u’re ready to move everywhere u want.
Yeah, this is the method I’d use. Do a fresh install of WP, just migrate the DB and wp-content folder.
I couldn’t ask for better timing with this piece… just last night I started shopping around for a new host- and thought I’ve moved before my site has gotten a little bigger so it’s nice to see a walk-through and with the same plugin I use. Thanks a million!
I’m a big fan of Lester-chan’s WP-DBManager plugin as well.
Am now working on a local version of a revamp and decided to opt for exporting of posts/comments (in several batches using WordPress Advanced Export) into a new WP installation and exporting the relevant tables for key plugins using phpMyAdmin, so that I could try and get rid of unnecessary ones from old plugins.
Took a long time though. Next time I think I might just do a wholesale export and delete the extra tables.
One of the things I did mot mention that a couple of you commented about is cleaning up the database. I did this during my iniital move to a new host. I got rid of things from old plug-ins in the options table as well as removing tables from those same plug-ins within the database.
It helped a lot – trimmed the database down from 40MB to less than 20MB! Most of that was from stats plugin that really fill your database quickly.
I use the “WP-Optimize” plugin to clean the database and remove post revisions, etc.
I’m always amazed how much the post revisions (created whenever you preview a new or modified post or page) fill your database!
Thanks, dan.
This would have been great a couple of months ago
I managed to move everything but lost my link roll 
(not a huge deal).
One thing I did was to leave the old site up and dns pointed to it, but put in the IP of my new site in my /etc/hosts file (I’m sure you can do that on Windows as well- somehow), which made it easy to compare and tweak.
You can transfer the blogroll by using the wp-links-opml.php file.
http://www.kimwoodbridge.com/h.....o-another/
It doesn’t get descriptions, if they are used, but it does pull the basic information.
Also planning on moving my website to a new host (mediatemple in my case)
How would you handle plugin settings? specifically all-in-oneSEO, Nexgen etc – or is this all handled by the DB manager?
Those are in the database and will be restored when you restore the database.
Invaluable advice, thanks for taking the time to write this. Best regards,
Fred
Ultimate information..just loved it.
Great step-by-step guide Richard. Made sure to bookmark it since I will be transferring my blog to a new host pretty soon. Really need an affordable, reliable VPS that offers more granular control than my shared hosting.
Thanks Dustin – also sent you an email offline about hosting.
I got it. That’s great. Thanks again Richard.
NIce info! PLanning to move some sites to my vps from other shared servers. I feel better about doing it now thanks! =)
This will really be a great resource. A very detailed one at that. Will be bookmarking this, just in case. Thanks for sharing your experience.
This is certainly useful for people like me that have their dev installs on a different server before putting it out on the web. It’s also a thing I love with WordPress. You can show off things on your test site and then easy move it to the final destination on the web.
Just a note to the Plugin. Although it doesn’t really matter for this one but Lester is no longer officially supporting his Plugins.
yup, but if their production server is diff than your dev server . . . Like I recently had with a client with a pre-exisiting GoDaddy account. It can cause problems like mysterious errors that need tracking down and worked around. Sometime it’s best just to back everything up and work live, sometimes.
ust backup the whole database and wp-content, and then restore.
For changing the site URL, u must go to database’s wp_options and edit rows ’siteurl’ and ‘home’.
Then u’re ready to move everywhere u want.
I recently updated an old version (2.1) of a WordPress Blog on a GoDaddy server. Man, I hate to diss on such a big company, but there tools for working with hosting and databases are all screwy, it’ll really slow you down. Trying to work with a database without PHPMyAdmin, is really time consuming.
The point being, know what kind of tools they have AND how well they work before recommending a host for a client, unless you have your own server. What can do a bout someone who has all that purchased before they even contact you. It kinda stinks to have bill them extra because the server took 3.5 hours to back up a database that was less than 1MB in size. (No exaggeration), for example. what am I supposed to say after that? “I told you so?” Nope, gotta spin it as positively as I can . . .
If you use SSH, Godaddy as hosting, and linux commands, moving wordpress site could be done snappier, that’s how I do it. It takes me less than 1 hour for moving to a new server since Godaddy DNS can point to the IP address of the server fairly quickly. But you will need a dedicated server with shell access.
I went through a heck of a lot in my recent WordPress transplant.
One thing to make sure of – check whether your new host has .htaccess files enabled, since WordPress uses them to set up the URL rewriting rules. If they’re not enabled, nothing but your root blog page will load.
To check that, look in your apache.conf/httpd.conf file (if you have access, shared hosts might not). If it says AllowOverride All, you’re set, if it says AllowOverride None, your .htaccess files are going to be ignored. It is frequently set to ignore them because it’s more efficient to just include that stuff in the apache.conf/httpd.conf file, as it will be read once rather than on every request.
If you have root access as with a dedicated hosting account, the whm panel has a one click migrate website feature that is just that easy. If you are on shared hosting, tech support will often do this for you for free when moving to a new provider
I use web hosting providers that use CPanel. That way, the wordpress blog can be moved in a single mouse click, pretty much..
makes all this nonsense in this blog post completely obsolite.
the other thing I do periodically, is run an sqldump of my
database into an ASCII .sql file. That way if something
screws up, I always have a backup, in an ASCII text file format.
I don’t trust FTP to transfer data, because CRC errors are getting more and more common. I just do an sqldump, since everything is in the database for the most part anyway, and just reinstall WP over again, no big deal. *shrugs*
My host also uses cPanel which I like a lot. I do use FTP and trust it – never had an issue with it to this point.
I guess when that bug bytes I will have to look at it then.
I thought there was an “export” function in the dasboard, and subsequently an import function when you load up a new blog.
Also, I didn’t understand flushing the DNS on your machine. What does that do?
Dan
Dan – there is an export and import function that only draws out the posts and comments. All the options data and any data stored by plugins in your database do not get exported in that function.
That means you would have to reinstall all the plugins, themes, etc. and set them up again. I just find it easier to copy the entire directory structure, restore the database and it is up and running.
As for flushing your DNS – when I am connected to COMCAST I use their DNS services and a local copy of some of the stuff I have accessed is kept on the local hard drive to speed up the browsing process. By flushing your DNS on the local machine you clear that cache and force a check of the DNS to your DNS server.
I found that this worked for me to get at th enew site within about 10 minutes of inputting the DNS change at my domain registrar.
Hope this helps.
Rich
Richard
Thanks for posting this, I have bookmarked it so that I’ll have a guide when I need one. I have 2 questions:
I use the WP-DBManager plug-in and get the weekly updates via email, what is it backing up? The whole blog with posts etal or simply the stats and settings for widgets and plugins?
The second question (Yes I’m a newbie)you say to ping the DNS from IPConfig that means from your “home” Computer to the server at whichever host on the net is in charge of the domain at the time of the ping. I have only used ipconfig for local area servers.
Thanks, Mike
Right on Bubazo. Cpanel rocks! Sometimes a client has a client contacts you with a web site on a inferior Web Host and we have to be prepared to do what we gotta do to get them off of there.
Flushing the DNS clears out the cache so that new pages arent loaded from cache
Great Post Here. I actually had to move my wordpress blog from wordpress.com to wordpress.org and have it hosted, etc. I also had to move my hosting from Godaddy (Which lost 6 months of blog posts and charged me $200 to retrieve it!!!) to another hosting company.
Fortunately I have a very good contact that knows wordpress and PHP like the back of her hand so she was able to take care of the migration for me. If your not savvy in this area, I would suggest sourcing it.
Thank you for the very thorough information here.
I actually did move my WP blog from one host to another recently. Rather than go through the learning process, and then not use it for so long that I will forget it anyway the next time, I hired someone to do it for me (thank you Craigslist).
We did it by remote access so I could watch everything, and I was surprised to see that it really wasn’t that difficult. If I should ever need to do it again, I would probably do it myself.
Great Instructions!
I can’t stress how important it is to follow the steps in order and make sure that you get all of your database information correct in the wp-config area. I was stumped for about an hour until I realized that I had fat-fingered a value.
I couldn’t get WP-DBManager working because there was a problem with my MYSQL dump path and my MYSQL path. I wasn’t sure how to sort that out, so I attempted your tutorial using the PHPmyadmin method.
It took me HOURS. I could never get the website working. Eventually I managed to figure out that when I was importing my database, the imported tables had a truewp_ prefix whereas the original tables just had a wp_ prefix.
So, I deleted all the wp_ tables and then renamed my truewp_ tables to wp_xxx.
That left me with a working front page, but the rest of the site didn’t work and I could no longer log in.
After a lot of messing about I sorted the login issues using this tutorial:
http://beconfused.com/2007/how.....wordpress/
Then I could at last log in, but only the front page of my site was working. Any attempt to visit a page or post resulted in a “page not found” error in the browser. I checked the permalink settings, and they seemed to be already correct, but after clicking ‘ok’ the rest of my site was working! So it seems it needed to update the permalink setting.
Then after that I found my author box wasn’t working, but there was a slight difference between the username of the original post and my new username. So I just visited each of my posts and clicked ‘update’ so that the new author was the correct one.
Done. My site now works. Phew!