Upgrading WordPress with a really old MySQL database

I know.  I should have upgraded MySQL long ago.  But I didn’t realize how easy it was, so I put it off until… now.   The latest version of WordPress (WordPress 2.9) doesn’t work on really old versions of MySQL like my ancient 4.0.27.  Further complicating things is the need to cut blogs and sites over one at a time so’s to be a little prudent about all this.  “Big bang” upgrades seems to result in just that for me — a big bang and then lots of screaming.

I figured out a pretty tidy way to do this, with tips from Garrick VanBuren and Natn Johnston.  Rather than go into lots of detail about what didn’t work, here’s what did work.  This little recipe is mostly for me, since I’m not going to do all these at once and I’ll likely forget some critical bits if I don’t write them down.

Ingredient 1 — multiple instances on MySQL on the same box

This way, I can move the sites over one at a time rather than crashing them over all at once (and most likely breaking things)

  • Download the non-installer distribution of mySQL 5.1
  • Unzip it into a new directory
  • Set up the “my.ini” or “my.cnf” file (depending on whether you have a Windows server or a non-Windows server) to point at a new port — I used 3307,  one larger than the standard 3306 that’s the mySQL default
  • run the new instance from the command line (paying special attention to specifying the correct ini/cnf file) until things are running right.  Here’s an example for the command-line; 
    • in-the-new-mysql-bin-directory> mysqld --defaults-file="c:\program files\mysql\mysql server 5.1\my.ini" --console
  • Get yer daemon running — on Windows that means setting it up as a service, again making sure to point at the port-3307 version of the ini/cnf file

Ingredient 2 — loading up the new version of the database

  • Create a new empty database (in the new instance of mySQL) with the same name as your existing blog’s database, let’s call it “YourBlogDatabase” in this writup — I did this with MySQL Control Panel but there are lots of ways to do this, including the command line
  • Create a new user (again, with the same username and password as your existing blog, eg YourSQLPassword and YourSQLUserName), also in the new instance of mySQL
  • Dump the existing database (from your old instance of mySQL) — use mysqldump from the command line of the /bin/ directory of the old instance to do this.  Here’s an example; 
    • in the old-mysql/bin directory> mysqldump -uYourMySQLUserName -pYourMySQLPassword YourBlogDatabase > YourBlogDatabaseDumpFile.sql
    • Note — the username and password syntax is purposely without spaces
    • Note — pay attention to directories and paths here.  I did all this from the command line and moving between the /bin/ directory of the mySQL instances.  I didn’t set up path variables because I wanted to be very sure of which versions of programs I was using at any given time.
  • Reload the blog database (in the new instance of mySQL) — shift back to the new mySQL’s /bin/ directory so you’re sure to be using the right version of the command.  Here’s an example; 
    • in the new-mysql/bin directory> mysql -uYourMySQLUserName -pYourMySQLPassword YourBlogDatabase < YourBlogDatabaseDumpFile.sql
    • Note — really use the mysql command here, not mysqldump.
    • Note — I found that sometimes I had to specify the port to get this to work right.   “–port=3307” on the end of the command did the trick
    • Note — be prepared to wait for a little while for this to complete if your database is big.  I’m in hour 5 of a big reload as I type this.
    • Note — if you’re nervous and want to monitor progress, use MySQL Control Panel to watch the tables grow by disconnecting and reconnecting to the database in the control panel and opening up the tables.  See?  They’re getting bigger.  All is well, you just have to wait.

Ingredient 3 — new WordPress Code and new wp-config.php file

After all, this is an upgrade, right?  I was extra-conservative this time through.  Normally, I just dump the new WordPress files right on top of the old ones and it all works fine.  This time, I made backup copies of the directories just in case things needed to be rolled back.  Here are the steps I went through.

  • Make a copy of the blog/Wordpress directory and files
  • Copy the new WordPress 2.9 files on top of the existing directory (not the backup)
  • Replace the old leftover wp-config.php file with a newly created one that’s got all the same info as your old one except:
    • Change the hostname to point at the port of the new mySQL server — here’s an example where I’m pointing at port 3307;
    • define('DB_HOST', 'localhost:3307');
    • Comment out the ‘DB_CHARSET’ line — that avoids the “funny characters in my blog” problem that sometimes arises during this process.  Here’s how it looks if you comment it out (alternatively, you could just delete it);
    • /* define('DB_CHARSET', 'utf8'); */

Try it out!

After all, what could go wrong??   Go to the wp-admin page of your blog.  If things are according to plan, WordPress will want to upgrade the database.  Once that’s done, things should be working normally, except you’re on the new version of WordPress.

What if it breaks?

Oh well, so who’s perfect.  Replace the new WordPress files directory with your old files (I’d rename both directories — so you’ve still got new stuff to play with, but your blog’s running again).  With your old files back in place, your old blog should reappear since it’s now using the old files and the old wp-config file is pointing at the old database.  Then, sit down and figure out what went wrong.