Upgrading WordPress and MySql on 1and1

Upgrading WordPress is trivial now. I wanted to upgrade from 2.8 to 2.9, so I went to my dashboard and pressed the “Please update now” link. I got a few warnings, a message to make sure I did a backup first, and then finally a button I could press to do the upgrade.

Only it didn’t work. WordPress 2.9 needs MySql 5.0 or higher. Crap. I had a 4.something MySql version. I went to the 1and1 control panel. No way to upgrade. No way migrate. S.O.L. When I had created my blog and the database for it, which didn’t seem like all that long ago, the default on 1and1 was to create a 4.x database. Now I was in trouble.

Hey, no problem I thought. I was told to do a backup before the upgrade, and I had done that. I used the phpMyAdmin tool to do the backup, I could do a restore to a new 5.x table the same way, right? Not exactly. I had a LOT of data in my database. 1and1 will only let you import 2meg. I had 4 times that. I needed to reduce some data.

I know, compression! First I tried to do the data dump with zip compression. Then I found out that the import tool didn’t support it. Sheesh. Then I tried the gzip compression. For me, this resulted in exactly the same file size. This wasn’t working at all. I just need to get rid of some data.

First I created a new 5.x MySql table using 1and1.

Next, I thought that I would eliminate some cache data that I didn’t really need. I use a flickr plugin that caches a lot of data. Also, I use a twitter plugin that stores all the tweets I make there. This adds up! So I decided to delete the data. The plugins didn’t have a delete function, so using phpMyAdmin, I went to the sql tab and entered the following command:

TRUNCATE TABLE tablename;

Replace tablename with the flickr or twitter, or what ever table you need to remove all rows from. Doing it this way instead of deleteing rows is much faster.

Still too much data. Hmmmm… how about all the revisions. I never need to go back to the revisions I had before, so lets delete them:

DELETE FROM wp_posts WHERE post_type = "revision";

That’s working pretty well. I am down to 3.5meg. Still not near the 2meg limit though.

There is now a time consuming way, and an easy way. The time consuming way is if you are afraid of the command line. You can download a backup file, open it in a text editor (not word), chop it into pieces, and upload these pieces, hoping you don’t make a mistake. Or, you can do a little command line magic. Just a couple of lines.

First, you need to use ssh to get into your 1and1 account. From a Mac or Linux box, this isn’t too difficult. It is built right in. From a windows machine, you will need to use a program called putty. I use a Mac, so I will show you the command line (Terminal) commands to use. First you need to find out the URL, userid, and password to use. If you go to the Administration panel, select “Web Space & Access”, and then “Secure Shell (SSH) Access”. Here you will find the URL, user and password to use.

ssh userid@sxxxxxxxx.onlinehome.us (or what ever your URL is)

Then enter your password at the prompt.

Now you are in your 1and1 shell account. Now, lets save a backup of our database without the phpMyAdmin tool.

mysqldump -h dbxx.perfora.net -u dboxxxxxxxx -p dbxxxxxxxx > backup.20100101.sql

Give your file a date so it is easy to remember what the backup is from.

The -h part is for host. The -u for user. The -p is prompt for password, then the last part is the database.

That command will take a few seconds depending on how big your database is. Isn’t is weird that 1and1 will let you have a 100meg database, but your can’t use any of their tools to import something that big?

Ok, now lets restore that data into a new 5.0 database that you created. Make sure you use the new database for this command.

mysql -h dbxxx.perfora.net -u dboxxxxxxxx -p dbxxxxxxxx < backup.20100101.sql

Again, this will take a couple of seconds, and then you are done. That’s it.

Ok, I have to confess. I did this the hard way the first time. I thought the text after the -p was my password, instead of the database name, and I couldn’t get the command to work. This way is much easier though. Trust me.

Now, remember why I was doing all this? I wanted to upgrade my WordPress install. So next I went and edited my wp-config.php file to the updated database info. I have no idea what happened, but after I edited this file, my WordPress site became all text, and just dumped out this file to the browser. Oops. So I then deleted the file, and started the install over again. (That’s what happens if the file is missing) I entered the database info and then got a message about an existing install. Yea, yea, I know, so I exited out, and just went to the site normally, and everything was fine. Phew. Not sure what to tell you, as that was a little bit more stressful than I thought I would be.

Next I logged in, went the the dashboard, gleefully passed the screen telling me to backup, and upgraded to 2.9 in a couple of seconds. Piece of cake.

2 thoughts on “Upgrading WordPress and MySql on 1and1

  1. Thanks so much! I was getting a headache trying to figure this out for a client, and your clear directions made my life much easier.

  2. Pingback: Wordpress upgrades and 1and1 hosting | The blog of whall

Comments are closed.