Upgrading WordPress and MySQL manually

This morning a friend was having serious dificulties upgrading his WordPress site from 2.something to 3.0.3. His cry for help reminded me I’ve never actually written a proper rundown on how to do WordPress upgrades the manual way, so on this 15th Day of WordPress that’s what we’ll be looking at.

When automatic upgrades don’t work…

His site, like all of mine, was hosted on 1&1 and he was running into two calssic problems: First WordPress wouldn’t update at all – the process just stalled after the files were downloaded. The second problem – far more complicated – was that the database for his installation was running MySQL 4.something which is too low for the newer versions of WordPress. Quite the conundrum. Well, not really: Just a massive headache. But it’s all fixable.

Just so it’s clear: The following information is host independent. I’m just using 1&1 as an example.

None of this comes with any warranties of any sort. Mess with your databases and settings at your own risk. I take no responsibility for any disasters here.

Upgrading the database

If you set up your WordPress site a couple of years ago chances are it is still running on a MySQL 4.x database which is no longer supported by WordPress 3.x. In other words, to keep your site safe and get the use of the latest and greatest WordPress has to offer you have to upgrade your database. Some hosting providers will let you automatically upgrade your 4.x database to 5.x, but a lot of them won’t. In other words you have to set up a new 5.x database, dump the old 4.x database and import the dump to your 5.x database, then rewrite your wp-config.php file to access the new one. Tricky, yes, but it’s a necessary evil of running a WordPress site, so let’s get crackin’.

Step 1: Create a new 5.x database

This step varies greatly and is entirely dependent on what hosting provider you are using. Usually you go to the MySQL admin area and click “Create new database” or something along those lines. Under 1&1 you go to Administration, MySQL Administration and click New Database.

Whichever method you use there are two vital components to this step:

Make sure the new database is running MySQL 5.0 or greater
Set a strong password. I use a random password generator and create 32 to 64 key passes every time. (oh, and remember to store the password somewhere for later retrieval)

Step 2: Create a dump of your old database

Next you need to get all the content from the old WordPress database so you can add it to the new one. To do this you have to go to phpMyAdmin and do a password dump. The WordPress Codex has a full rundown of how to do this but here’s how to do it on 1&1 if you don’t want to follow the link.

Go to phpMyAdmin and find the database you want to back up
Click Export to get to the export page (position of the Export option varies depending on host and phpMyAdmin version)
Under Export you want the following options checked (see grab below):
- Export type: SQL
- Structure: Add DROP TABLE, Add AUTO_INCREMENT value, Enclose table and field names with blockquotes
- Data: Complete Inserts, Use hexadecimal for binary fields
- Save as file
- Compression: None
Click Go to save the file on your computer

The above process will produce a file on your computer that contains your entire WordPress database including all your settings.

Step 3: Import the old database content into the new database

Now you have to move the old database content into the new database. You may or may not encounter a snag here, but I’ll cover that as well.

To import the old database dump you first go to phpMyAdmin for your new 5.x database. From here you have to find the import function. As with the export function it’ll be found in different places depending on hosting provider and phpMyAdmin version. On 1&1 it’s located in the Query Window. To get to it click the SQL icon underneat the 1&1 logo and the query window will open (see grab below).

In the Query Window you’ll find the Import Files tab. That’s where you want to go. From here import the database file you exported earlier and your database should be populated without any difficulties.

Step 3b: I’m getting a SQL error! The sky is falling!

There’s a chance you’ll get a SQL error here. If you do, it’s most likely something along the lines of “Cannot create new database dbXXXXX because dbXXXXX already exists” or some such message. Infuriating, but as always there is a simple solution:

If you’re getting this warning it is because phpMyAdmin was a little too smart and added an entry into your backup telling itself to create a new database with the same name when the file was imported. It makes sense in certain cases, but not in ours so we need to get rid of it for everything to work.

First, make a copy of your backup file just in case. It’s always a good idea to make extra backups. Then open the backup file in your favourite text editor. I use Notepad++. Somewhere towards the top of the file you’ll find something similar to these two lines of code:

phpMyAdmin sees these two lines and goes “Oh, I need to create a new database with this name. But wait, this current database has a different name, and the database name it’s asking me to use is already being used by another database. I’m confused. Error. Error. Error.”

So, to unconfuse the server simply delete these two lines and try importing the database again. Unless there is some other unknown issue it should work just fine now. If it does, we can move on. If not, call your host and ask for help.

Step 4: Setting up WordPress to talk to the new database

With the old database content running on a fresh new 5.x database we can do what we set out to do initially: Upgrade WordPress manually. This is where the comparmentalized nature of WordPress truly shines. You see, you can upgrade WordPress manually by simply deleting all the old files of the installation and replacing them with new ones (with the exception of the wp-content folder – I’ll get to that in a second). Since all your content and configurations is housed in the database, WordPress itself is replaceable.

First off, get the newest version of WordPress from WordPress.org. WordPress’ upgrade frequency is pretty high these days so don’t trust even a week-old copy. Next do a standard WordPress setup by configuring wp-config.php:

Open wp-config-sample.php in your text editor
Go to line 19 and insert the database name of your new database
Go to line 22 and insert your database username
Go to line 25 and insert that long password we created towards the beginning of this tutorial
Go to line 28 and insert the database host name if necessary (your host will tell you if it is)
Visit https://api.wordpress.org/secret-key/1.1/salt/ to get fresh Salt for your installation
Copy the entire bulk of code you find on the Salt page
Highlight lines 45 to 52 in wp-config-sample.php, delete the code and paste in the Salt you just copied in step 7
Save the file as wp-config.php

Now the new version of WordPress will talk to your new database. All that’s left is updating WordPress itself. We’ll do this through FTP.

Step 5: Upgrade WordPress through FTP

Before you follow the next steps make sure you make a backup of the entire wp-content folder on your computer. This folder contains your themes, plugins and all uploaded files (images, music etc). During the upgrade process this folder is off limits.

Launch your favourite FTP application and log in to where you have your WordPress installation running
For safety, create a new folder called OLD in which we’ll dump a backup of your old WordPress install
Check if you have a file called .htaccess or php.ini in the root folder. If so, leave them alone
Highlight all the files as well as the wp-admin and wp-config folders
Move the highlighted files and folders to the OLD folder
You should be left with 2 folders: OLD and wp-content plus possibly the .htaccess and php.ini files
Find your freshly downloaded WordPress verison with the new wp-config.php file on your computer
Highlight all the files and folders except wp-config-sample.php and the wp-content folder
Copy the highlighted files to your host through FTP

When all the files are uploaded to your host, WordPress should work again.

Step 6: Logging in for the first time

If you visit the front page of your site now it should be working as before. If you try loging in through the backend (www.yoursite.com/wp-admin) you’re likely to get a message that you need to upgrade the database. That’s normal and automatic so just click the upgrade database button. When the upgrade is completed you can log in and everything should be working fine.

Upgrade and plugin problems with 1&1 hosting

If you’re hosted on 1&1 you have likely run into two problems: First off, you can’t seem to get automatic upgrade to work. It just stalls. Secondly you may be getting some really annoying error messages when you use plugins like NextGEN Gallery. These problems are caused by RAM restrictions and security settings, and both can be fixed by adding two files to your root folder:

First you need to add a single line of text to the .htaccess file:

AddType x-mapp-php5 .php

This tells the server that it is indeed running MySQL 5. I have no idea why this is necessary, but it is. You may already have a .htaccess file with a bunch of content in it on your host. In that case download the file and add the line above to it before uploading it again.

Step two is to add a php.ini file to the root of your installation. Create a file called “php.ini”, open it in your text editor and paste in the following lines of code:

allow_url_fopen = on
register_globals = Off
memory_limit = 32M

This allows WordPress to write to itself and ups the memory limit so automatic upgrades and other functions can run properly.

With the .htaccess and php.ini files uploaded to your site everything should finally be working. If it’s not, give your host a call and get them to help you out.

This tutorial is part of the 24 Days of WordPress series. If you want to learn more about WordPress and Expression Web check out the Sams Teach Yourself Microsoft Expression Web in 24 Hours series (version 2, 3 and 4), Lynda.com’s WordPress 3.0 Essential Training course and Microsoft Expression Web 4 LiveLessons.