Upgrading MySQL 5.0.xx to MySQL 5.1.xx for InstantRails

MySQL 5.1 at this time is in release candidate state, but I thought I'd just check it out for its partitioning feature.

Preparation

1. Take a mysqldump of the existing databases. You don't want to wipe out anything.
   backup_dir> mysqldump --all-databases > backup.sql -u root -p
   Enter password: ********
2. Stop the InstantRails MySQL through the InstantRails Confirguration Interface (where Apache and MySQL server status shows?).
3. Check using task manager, just in case the MySQL application is still running. No MySQL should be running. If you use any MySQL GUI tools such as MySQL Query Browser, close them too.

Installation of MySQL 5.1

1. Downloaded mysql 5.1 setup exe (Windows ZIP/Setup.EXE (x86)) for Windows. I downloaded the 5.1.22 distribution.
2. Cd to mysql-5.1.22-rc-win32 directory and click on setup.exe.
3. This starts the MySQL setup wizard. Select 'typical' setup type and click Next, Next. This will start the installation in C:\Program Files\.
4. The installation will additionally show another popup urging you to register, and that's upto you. In our main installation window, the installation has completed. The 'Configure the MySQL Server now' box is checked, and the next step is to click 'finish'. Click it.
5. In the configuration popup, select Standard Configuration if you're a chicken like me or select Detailed Configuration otherwise. Here we are going with Standard Configuration.
6. The next step shows two options, 'install as windows service' and 'include bin directory in windows path. I chose mySQL to be installed as a service. And checked the 'include bin dir..' box too. (I had encountered certain problems in starting up MySQL server on my Windows Vista when I did not choose to install it as a service.)
7. The next step asks you to create a new root password, which you should do. I did not create an anonymous account, but just set the root password and moved on. I did not check the 'allow remote access'.
8. Click 'execute' in the next screen and voila! Your firewall software may pop up a window asking you to allow the security settings execution to go through.
9. Open Task Manager -> Services. MySQL is already up and running as a service. You can configure it so that it does not start automatically. (manual start).
10. Open a brand new command window (not the one using InstantRails 'open a new console window' but using the start menu. There's a reason for it.) Key in mysql --version and it should show 5.1.xx. Key in the same in a Ruby console window got from InstantRails configuration and it should still show the previous verson of MySQL that your InstantRails is using.

Configure InstantRails to use MySQL 5.1

1. Rename the mysql directory under InstantRails to say, mysql_old. (if you don't want to delete it)
2. I use a program called junction to create a symlink, a la Windows style.
3. Key in:
   C:\Softwares\Junction>junction "C:\InstantRails\mysql" "C:\Program Files\MySQL\MySQL Server 5.1"
   Junction v1.05 - Windows junction creator and reparse point viewerCopyright (C) 2000-2007 Mark RussinovichSystems Internals - http://www.sysinternals.com
   Created: C:\InstantRails\mysqlTargetted at: C:\Program Files\MySQL\MySQL Server 5.1
   C:\Softwares\Junction>
4. Now InstantRails's mysql path points to your MySQL 5.1 installation. Keying in mysql --version in this window now should show the new version of mysql.
5. Modify database.yml for the new root password if you chose differently from your previous root password.
6. In the Ruby console window, just for convenience, cd to directory where your backup.sql. Now start a mysql shell here. We want to recreate the databases from our old copy.
   mysql -u root -p
   password: ********
   mysql> source backup.sql
   ... this will show tons of row creation info etc... hang on...
   mysql> show databases;
   ... this should show all your old databases...
   mysql> quit
7. Start InstantRails. For me, the InstantRails configuration window shows MySQL as stopped, but we know it is running as a service.
8. Running rake db:migrate is not necessary since we have already created the databases with all the data brought in from the old tables.