The “Move” – Notes from Mr. Admin

As if moving houses wasn’t enough, therapy Melanie decided to move blog hosts as well. 😀

Unfortunately 1&1 doesn’t have the same WordPress versioning as Dreamhost (Dreamhost is more up-to-date), prescription so that means that Mr. Admin gets to move the database, skincare files, and deal with a not-in-place upgrade at the same time.  Here’s a rough procedure in case anyone else is considering doing this – and trust me – don’t do it if you don’t know anything about databases (or have someone you know who is willing to help you).

There are two parts to a blog:

i) the php (and other) files that contain the website format and general directions for a web browser to display things. This includes templates and hosted content (like images) that you hosted yourself.

ii) the database that contains the users, passwords, posts, comments, options, links, meta-tags, etc.  If either of these is gibbled it will not work.  At all.  The database is by far the most important part however – it has most of what people call “content” on a blog and can be attached to a shell website and still be mostly functional.

(Files – Option A) Migration

  1. Use Filezilla to get the entire contents of your old blog (ex-database) off of there for transfer to a new site.  I’ve tried many different clients, and I really suggest Filezilla for its ease of use, functionality and cross-platform conpatibility (works on Mac, Linux and M$).  Just point it to the hosting address (or IP) of your old site and connect using your username/password.  If you have already altered DNS to point to your new location you will need the root host instead of the CNAME entry.  (In 1&1, this is under “Domains”, and is something that looks like
  2. Use a zipping program to zip up the contents once you have them local and store a copy somewhere.  tar (mac and linux) or IZArc (M$) are recommended.  Most hosts don’t let you ssh into their sites, otherwise you could just ssh onto their site, tar -czf wp_backup.tgz <wordpress directory> and sftp the one tgz file over to your new host.  Ah well, c’est la vie.
  3. Do not touch this backup zip file.  Ever.  Except to make a copy of it.
  4. Use Filezilla to push the files up to your new site.  Some sites make a wordpress subdirectory, others make a <domain name> subdirectory which you put your wordpress files under.  It varies.

(Files – Option B) Start from scratch with files (Recommended)

If you want to upgrade your blog to a new version, your new host may or may not support moving the old files over then upgrading in situ.  In general, they will not, and you will save yourself much grief by creating a new WordPress installation and pulling over the pieces you want to keep.  You will still get to keep your posts, comments, etc.  Just custom web page design and styling etc. may get lost.  (they sometimes get lost in upgrades anyways – that’s why you shouldn’t create weirdo custom templates… cough cough…)

  1. Make a WORDPRESS_OLD directory up on your new site and dump your old stuff up there to start with if you have anything you care about.
  2. Do database stuff below

Database migration

  1. Log into your old host, and export the database *to a file* using the myphpadmin link.  Unless you select this option you will get a screen dump and have to deal with copy/paste issues, character encoding, file formats, etc. – file dump is much easier.  It should be a .sql file (it’s just a plain text file filled with SQL statements).  Use the INSERT option if possible, to warn you of possible conflicts in unique primary keys and make you deal with it below in troubleshooting.
  2. Take that file and make a *copy*.  Save the original somewhere and don’t touch it.  Ever.  Except to make another copy of it.  Zip it up if it’s too big.
  3. Create a new MySQL database on your new hosting site.  This usually involves filling in a couple of fields, setting an admin user, and hitting “OK”.  You can make them the same as the old site or not, it doesn’t matter.  Write down your user names and passwords.
  4. (sort of optional if things are complicated – otherwise this is the last database step) Import the database you exported above.  Click on “Import” in myphpadmin and point it to the copy of the file you created above.  If there were no changes in versioning, database structure or anything else at all, then you are done (hahahahaha….).  Otherwise, you get to get your hands dirty and better brush up your SQL syntax.
  5. If you opted for a clean install of files, run the “Advanced” WordPress quick-install.  Make sure you tell it to use the database that you created in the previous step instead of making a new one.
  6. Log into site after 10 minutes and set up your admin user

Here’s  where things get messy.  Either you have a running website or you don’t.  Chances are you don’t.  Here are some things to do:

i) edit wp-config.php in the root of your wordpress installation to include 4 things:

  1. the new Database Name (from step 3 above)
  2. username you set when you created the database (note: this is different than the username you use to log into your blog – it was set in step 3 above)
  3. the password for that account, and
  4. the new site hosting your MySQL database.

ii) log into the phpadmin administration panel for your new database.  Choose the database you created above, and if you see two sets of tables (i.e. wp_users and wp_2rvidz_users), you have some work to do.  What happened is that you imported the old data into the wp_users table, but the wordpress install detected an old wordpress database and created all of its tables with a different name to have them running in parallel.  Your new wordpress installation, however, will only point to the new database.  To correct this:

  1. Take a backup copy of your whole database.  Do not touch this copy, ever, except to make another copy.  (starting to get the hang of this?) 😉
  2. Take the copy of the old database SQL file you created in step 1 in “Database Migration” (you did create a copy, right?), and do a search-replace on “INSERT INTO ‘wp_” with “INSERT INTO ‘wp_2rvidz_” (double quotes are mine, and use whatever the new tables are called – yours may be called something different)
  3. Data table structure probably changed between versions, so you can’t just import it as it is now.  Sometimes they change the column type, length, or even remove/add columns between versions.  So you have to clean up this old file.  You can try running it and see where it barfs to detect the difference between versions.  But remember that everything before the barf point executed properly, so it can make your life hellish if you don’t split it up a bit.
  4. Delete or comment out all “CREATE TABLE” sections – those tables already exist so they aren’t needed
  5. Try executing SQL “INSERT” comments one table at a time to catch errors.  In my case, the following needed fixing.
  6. The wp_links table removed the `link_category` bigint(20) column, and changed another field from an int(11) to bigint(20).  The field change will be fine as it went from a smaller to a larger data type (both type integer), so no change there for the import.  The removal of the column will cause SQL to barf, however, as you are giving it too many parameters.  Because there were only about 20 entries I went through and manually removed the columns in every line.
  7. Copy the section you just edited into the SQL Execute window, and let ‘er rip.  Worst case scenario – delete all the new stuff and start again.  So play around with it.
  8. Initially I truncated the wp_options table and imported the old one because of all the differences, but in the end this was a bad decision.  Just leave the wp_options table and live with the fact that you will have to set some config parameters over again.  Upgrading versions always causes grief, but searching through 200+ options is more pain than I care for.  (unless work was paying me… lol).   If you do decide to mess with this, there is a linked value in there to one of the table names and you will need to execute UPDATE `wp_2rvidz_options` SET `option_name`=’wp_2rvidz_user_roles’ WHERE `option_name`=’wp_user_roles’
  9. wp_post_meta – no issues
  10. wp_posts – they removed the “post category” column between versions.  As there were over 1400 values to remove that one column for, I wasn’t doing this manually.  And the layout of this particular SQL file is not conducive to using sed, awk or anything else.  Because we imported the old database into the new one, we have the full table structure at our fingertips to play with, so the easiest path is: i) execute ALTER TABLE wp_posts DROP COLUMN post_category to modify the old table.  (ii) “Export to file” in phpmyadmin, and only choose the wp_posts table (i.e. junk.sql) (iii) replace “INSERT INTO ‘wp_” with “INSERT INTO ‘wp_2rvidz_” in junk.sql  (iv) truncate (or “clear”) the wp_2rvidz_posts table (v) Import junk.sql in phpmyadmin.   You should now have all of your posts back!
  11. wp_2rvidz_term_relationships – no issues
  12. wp_2rvidz_term_taxonomy – delete first entry out of new table, then execute INSERT commands
  13. wp_2rvidz_terms – delete first entry out of new table, then execute INSERT commands
  14. wp_2rvidz_usermeta – don’t mess with this table.  It screwed up many things when I played with it, so I just restored it from the snapshot in (1) (you took a backup copy, didn’t you?) and everything started working again
  15. wp_2rvidz_users – another table to leave alone.  Best to just create your admin user, then set up other users.  I don’t think most blogs let people self-register or have complicated user access lists/permissions/controls, etc. so you shouldn’t need to mess with this.  One thing I did notice is that it creates an indexing key and a user key, and many of the parameters changed between versions – and messing with anything causes grief.  If you feel the need to mess with this I suggest you keep your backup from (1) intact otherwise your blog will become unusable.  Ah well, migrating and upgrading – you pays your money and you takes your chances.
  16. After all this, the posts, comments, links, etc. all carried over.

iii)  Themes are saved under /<sitename>/wordpress/wp-content/themes.  If you are having troubles finding an old theme you created try putting it in here from your backup directory.  Beware, however: when you upgrade you break shit.  Always.  And you can’t not upgrade as old version become incompatible with newer technology.  So the short story is: Don’t get too fancy unless you are a full-time web developer.

iv) if you are hosting your own content, and pictures, etc, are missing, try restoring /<sitename>/wordpress/wp-content/uploads with stuff from your backup.  Web pages usually store relative links, so as long as you restore it in there the web page should be able to find it.

  One Reply to “The “Move” – Notes from Mr. Admin”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.