Upgrading to MySQL 5.5 on Ubuntu 10.10

I was recently asked to upgrade the MySQL install on a Ubuntu 10.10 system.  Normally these kinds of upgrades are simple affairs using built in tools like apt or yum, but in this case I was trying to go from MySQL 5.1 to version 5.5 and there were no pre-built packages to do it.

There were a few tutorials on-line that walked through different methods for the upgrade, but I couldn’t find one that worked perfectly for me.  Maybe some packages we had upgraded or not upgraded were incompatible, or maybe I was just misreading some of the steps, or maybe there was something small missing from the tutorial. Whatever the reason, in each method I tried something always went wrong.  But on the plus side, by having a few resources on-line to show me how it worked for other people gave me all the info I needed to make it work on our system.

To help anyone else in this quest, I wanted to document what worked for me.

First, Backup!

The first thing you should do is backup all your data.  This includes the “mysql” schema and any other schemas you have setup. When following the directions you should replace “mydata” with the name of your actual databases, of course.

  • cd /root
  • mysqldump ‑p mysql > mysql.db.backup.sql
  • mysqldump ‑p mydata > mydata.db.backup.sql

 

Install Pre-Requisites

Next there were a few tools that I needed to install so I could build MySQL from the source files:

  • apt-get install cmake
  • apt-get install libaio-dev

 

Uninstall the MySQL 5.1

Next, I uninstalled the existing MySQL 5.1

  • /etc/init.d/mysql stop
  • cp ‑R /var/lib/mysql /var/lib/mysql‑5.1
  • apt-get remove mysql-server‑5.1
  • apt-get autoremove
  • apt-get remove mysql-client
  • apt-get autoremove
  • mv /var/lib/mysql /var/lib/mysql‑5.1
  • mv /etc/mysql /etc/mysql‑5.1
  • mv /usr/lib/mysql /usr/lib/mysql‑5.1

 You may find that your data and configuration directories have been setup to live somewhere else.  If that’s the case, use the proper locations rather than /var/lib/mysql or /usr/lib/mysql that worked for me.

Build and Install MySQL 5.5

Here are the build steps that I used.  If you don’t have all the dev tools installed, you may need to apt-get the right bits. Hopefully the error messages will give you enough guidance to see what you need. This was the trickiest part for me and I did have to backtrack once or twice, so I hope that I didn’t miss anything in my note taking.  If I did, I apologize in advance!

  • wget ‑O mysql‑5.5.21.tar.gz  http://dev.mysql.com/get/Downloads/MySQL‑5.5/mysql‑5.5.21.tar.gz/from/http://mysql.mirrors.pair.com/
  • tar ‑xvzf mysql‑5.5.21.tar.gz
  • cd mysql‑5.5.21.tar.gz
  • cmake .
  • make
  • make install
  • cd /usr/local/mysql/support-files/
  • cp my-large.cnf /etc/my.cnf
  • cd /usr/local/mysql/support-files/
  • cp mysql.server /etc/init.d/mysql
  • chmod +x /etc/init.d/mysql
  • update-rc.d mysql defaults

 

Configure MySQL 5.5

You will need to configure your installation.  Here is what I did to get it setup the way we need for our projects. It’s possible that you may need to do things a little differently:

edit /etc/my.cnf
   chagne the two socked lines to read:
       socket          = /var/run/mysqld/mysqld.sock
   add a datadir line after [mysqld]        datadir         = /var/lib/mysql
  

Restore the Old Data

At this point I had a working installation with no data.  Hopefully you are in the same position.  The next step is to start mysql and restore whatever databases you backed up previously.  When following the directions you should replace “mydata” with the name of your actual databases, of course.

  • mysqld –skip-grant-tables –user=mysql –datadir=/var/lib/mysql
  • cd /root
  • mysql ‑p mysql  < /root/mysql.db.backup.sql
  • mysql ‑p mydata  < /root/mydata.db.backup.sql
  • kill the running mysqld.  To do this you can find the pid of the process with “ps ‑eaf | grep mysqld” then do a kill ‑9 on the resulting pid if you have to.

Restart the Database and Apache

You should be ready to run the database properly now.  Since I am using this as a backing store for a web server, I’ll restart my Apache instance as well:

  • /etc/init.d/mysqld start
  • /etc/init.d/apache restart

Give it a test and see if it worked.  I hope I was able to save you some time working this all out on your own.