McNelis.biz

Sometimes you are forced to move away from your roots. Usually when you hear something like that you are talking about changing cities or states. This time I am talking about something far more important, changing web servers. My previous hosting company ran Apache, with a cPanel interface. It was nice and suited my needs pretty well.

But because of other circumstances I had to change to a host that runs IIS, this time with a Helm interface. This meant that I needed to migrate my domain, mcnelis.biz, including my Wordpress 2.x installation and all of the articles, comments, and other data contained therein.

The way I looked at it I had two phases of this migration. First was the database information. The second part would be the files themselves.

I started by using cPanel to create a database backup. This created a tarred and gzipped file with the database data and table structure. Next I needed to load the data into MySQL on the new servers. This turned out to be easier said than done.

The first step in getting the database set back up was to create the database through Helm’s MySql administration area. You’ll need to know a little bit about your old installation to make this a little bit easier. If you open your wp-config.php file you will be able to see what your old database name was, as well as the username and password you used to access the database. To save yourself a bit of time later I suggest you create the database on your new host with this same information. That way Wordpress will know how to connect to the db from the get-go.

Unfortunately Helm, it turns out, doesn’t have the easy backup and restore interfaces that cPanel had. However I was able to download the MySQL GUI Tools. While these tools wouldn’t connect to my database on my old host, they would connect to my new host. All I had to do was specify the IP address (since my domain wasn’t forwarding yet), database name, username, and password. Like that I was connected to my still tableless database.

The MySQL Gui tools consist of 4 applications which, to my pleasant surprise, work on Linux, Windows, and OSX. I have been using a Mac, so this made my life much easier. At any rate, I used the MySQL Administrator. There is a toolbar at the top of the screen, one of the tools is “Restore.” Can you guess which tool we want?

Once on the restore screen you can choose you’re backup file. I selected mine and I encountered my first problem. I kept getting an error saying that my file needed to be in UTF-8 encoding. I assumed the file already was, I could open it fine in any basic text editor. I didn’t find anything strange when I went through the file in vi. I ended up finding a Linux based program that you can use to find offending lines in your database file. The program “iconv” is a part of most Linux distro’s and if you are running Windows I believe that CygWin has iconv natively. There may be other solutions for Windows users, I just don’t know them.

After doing a Man on iconv I found that I could pass in the encoding I was wanting to use and the file to look at. The syntax will look something like this:

iconv -utf8 database.sql

When you run this command it will go through your file from beginning to end. When it hits a non-utf8 character it will stop and print the line that the error occurs on. With the line you can open up your favorite text editor (vi for me) and find the line and remove or change the character. “But Dave”, you say, “How will I know what the offending character is?”

If you are looking at the file in vi you’ll be able to tell because there will be a funny looking character where the problem is. In other text editors (and by God you will just end up with more problems if you edit the file with Word), the offending characters might be converted to look like normal characters. The large number of offending characters were intelligent quotes, trademark, copyright, registered trademark, international characters and the like.

My database file was over 15 megs, so this approach is going to end up getting very arduous…there are a couple of shortcuts. First, open up MS Word (yes, I know I said it could screw you earlier, but bear with me), no type a phrase like “mike’s” — the hard lemonade. The idea is that Word is going to replace the quotes with intelligent quotes, the apostrophe with an intelligent one and the two dashes into a long hyphen. Then you can copy those characters (individually) and do a find and replace in your text editor. In vi you would do something like this:

:%s/badcharacter/replacement/g

.

This will do a couple of things. The “:%s/” means search the entire document. Between the first two slashes is the search string. If you are a regular expression guru you probably already know that you can use ReGex patterns in vi like this. Between the second two slashes is the replacement string. The “g” at the end means to do the replacement globally. Otherwise the replacement will only happen on the first occurrence of the search string in a line. Do this for all the special characters you can think of, it will save you some time. After you’ve replaced the special characters you can think of its time to go back to iconv.

Now you can use iconv to find the remaining offending lines and fix them….or if they are spam comments you can delete them…personally I ended up with 80% of my bad characters the result of comment / trackback spam. Spammers should goto hell. But I digress. Eventually iconv will get through the entire file. If you goto deleting spam, your file size might go down quite a bit (I went from 15meg to just over 8meg). The important thing is that once inconv gets all the way through you’ll be able to go back to restoring in the MySQL Administrator.

Its time to restore the DB, which will be pretty obvious how to do in the MySQL Administrator. Once the database has been restored it is time to find out if you were successful. You can do this by going to the “Catalogs” section of the MySQL administrator and clicking “Refresh” at the bottom. Click on your Database, take a look at the number of rows in your posts and comments tables. Does it look about right? You know your site…if it only has 3 records for posts and it should have 200 then you have a problem. Lather. Rinse. Repeat. Keep going until your records appear to all be there.

Next is to upload all of your files to the new host. This is pretty self-explanatory, FTP the files into your wwwroot directory. At this point I changed my domain over to point to the new host. When I went to check it the first time Wordpress told me the db didn’t exist, because I was double and misspelled some things. Nevertheless, after correcting my errors my site appeared! Success right!

Well, almost success. I still had a problem. I had been using Wordpress’s pretty permalinks which do not natively work in IIS, because IIS does not support mod_rewrite. This is a problem for me, after all I had been around for 2 years and my pages are all indexed by a number of search engines. I turned to the web and found an incredibly helpful person in Tom over at KeyboardFace. Tom wrote a slick little ASP script and how-to so that you can get Permalinks for Wordpress working in IIS. The process is relatively simple, though I’ve had some issues that I believe are related to not being able to set my custom error page to be of type “URL” instead of “File” or “Default.” You can comment on your IIS Wordpress Permalinking experiences here.

For the time being I’ve given up on the permalink situation, but hopefully will be able to revisit it later. For sure though, the permalink issue is the biggest reason not to migrate to IIS. If you have to though, you should be able to follow the suggestions above and get it done. If you are having trouble feel free to ask questions in this thread and I’ll try to help you out if I can.