Upgrading MySQL from 5.0.x to 5.1.x is considered a major upgrade and there are some fundamental internal changes. For example, in MySQL 5.1.23 and earlier, the default location for the data files used by MySQL is located within the corresponding MySQL Server installation directory. For MySQL 5.1.24 and later, the default location of the data directory is the AppData directory configured for the user that installed the MySQL application. This means that it won’t pick up the current databases since it sits in a different location.

I followed the steps outlined in this article. In my first attempt, I thought it might be possible to have side-by-side 5.0.x and 5.1.x installations just in case the upgrade process really messed up my system. That didn’t pan out so I decided to follow the above steps exactly and first uninstall 5.0.27 and then install 5.1.34.

Here are the steps I took for a successful upgrade.

1. First do a backup of all the current databases. “mysqldump –all-databases > alldatabases.sql”
2. Set a new system restore point just in case something went horribly wrong.
3. Uninstall MySQL 5.0.27 and remove the service.
4. Download and run mysql-essential.5.1.34.msi.
5. Run the following command to restore the database file from above
   “mysql –u root < alldatabases.sql”

And I’m up and running.

Update: As it turns out, I spoke too soon, I did get an error

The fix is simple enough, simply run “gem install mysql” as instructed.

Note: I read some articles saying that MySQL 5.1 upgrade had some issues with Rails 2.2 and requires an older MySQL client library, here’s an article on how to fix it. Personally, I didn’t encounter the problem.
Update: Again I spoke too soon, “rake db:create” got this error

Applying the solution in the above article fixed it.