A step by step guide to upgrading to MySQL 5.5     April 27, 2011
December 18, 2010
Ovars Tariq (ovais.tariq)
Spelling, Grammar, Punctuation, and Format Fixes for Text by CW Cyrix

Original link
http://www.ovaistariq.net/490/a-step-by-step-guide-to-upgrading-to-mysql-5-5/


MySQL 5.5 has created a lot of hype, and it's not just hype! There are major
performance enhancements not only in the MySQL server itself, but in the newer
InnoDB plugin shipped with MySQL 5.5. That’s exactly the reason why I myself
have upgraded to MySQL 5.5 (The server running this blog run MySQL 5.5). Now,
since I haven’t come across a guide to help in upgrading to MySQL 5.5, I
thought why not make one myself. So here goes nothing!


Download the binary

$ cd /root/
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.11-linux2.6-i686.
  tar.gz/from/http://mysql.llarian.net/
$ mv index.html mysql-5.5.8-linux2.6-i686.tar.gz


Backup the MySQL configuration

$ mkdir /root/mysql-5.1-conf
$ cp -R /etc/mysql/ /root/mysql-5.1-conf


Backup the data directory

We will be backing up the data in the form of SQL dump as well as by copying
the data files over to a safe place, just to be 100% sure about the data not
getting lost.

$ mkdir /root/mysql-5.1-data
$ cp -R /var/lib/mysql/ /root/mysql-5.1-data


Backup the data as SQL dump

Backup the mysql database separately and not with all the other databases,
because we are going to need it before we restore all the databases.

$ mkdir /root/mysql-5.1-dump
$ mysqldump -u user_name -p --databases mysql > /root/mysql-5.1-dump/mysql.sql
$ mysqldump -u user_name -p --databases db_name > /root/mysql-5.1-dump/db_name.
  sql


Install the asynchronous I/O library

This is so that we can take advantage of the asynchronous I/O capability in the
new InnoDB plugin that ships with MySQL 5.5

$ apt-get install libaio-dev


Untar the archive

$ tar xzvf mysql-5.5.8-linux2.6-i686.tar.gz


Copy or move the untarred MySQL directory to the installation directory

$ cp -R mysql-5.5.8-linux2.6-i686 /usr/local/
$ cd /usr/local/
$ ln -s mysql-5.5.8-linux2.6-i686 mysql


Remove the older version of MySQL

Now is the time to remove the older version of MySQL, in this case I assume the
older version to be MySQL 5.1

$ apt-get remove mysql-server-5.1
$ apt-get autoremove
$ apt-get remove mysql-client
$ apt-get autoremove


Add the path to MySQL bin directory to the PATH variable

$ vim /etc/environment
PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:
/usr/local/mysql/bin"


Set the correct file and directory permissions on the MySQL installation
directory

Setting correct permissions is very important, make sure that all the files
except those under the data directory are owned by root. The data directory has
to be owned by the user mysql.

$ cd /usr/local/mysql
$ chown -R mysql:mysql data


Create the socket directory

Here again, setting the correct permissions on the socket directory is very
important, otherwise MySQL would not run.

$ mkdir /var/run/mysqld/
$ chown -R mysql:mysql /var/run/mysqld/


Copy the sample MySQL configuration file to the etc directory and setup the
paths

$ cd /usr/local/mysql/support-files/
$ cp my-large.cnf /etc/my.cnf

Now edit /etc/my.cnf so that it has the following values:

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr/local/mysql
datadir         = /usr/local/mysql/data
tmpdir          = /tmp
log_error       = /var/log/mysql/error.log


Copy the MySQL server startup script to the startup directory

The MySQL startup script has to be placed in the directory where all the
startup scripts reside, so that MySQL starts on system startup. Make sure that
you make the startup script executable, and update the rc.d database to notify
the system about the presence of a new startup script.

$ cd /usr/local/mysql/support-files/
$ cp mysql.server /etc/init.d/mysql
$ chmod +x /etc/init.d/mysql
$ update-rc.d mysql defaults


Remove the MySQL files from the older version

Make sure you don’t delete files belonging to the new version we are
installing.

$ rm -R /var/lib/mysql
$ rm -R /etc/mysql
$ rm -R /usr/lib/mysql


When starting the MySQL server for the first time after the new installation,
it has to be started without the grants table for two reasons. Firstly,
because we want to retain the users and privileges data from the previous
install of MySQL and secondly, because the schema of the grants table in MySQL
5.5 has changed.

So what we will do is start MySQL without the grants table, import the users
and privileges data we backed up earlier in this guide and run the
mysql_upgrade script that modifies the schema of the grants table to be in sync
with that in MySQL 5.5. After that we will be able to run MySQL normally and
have all the users and privileges same as in the previous version we had.


Start MySQL server without grants table

$ mysqld --skip-grant-tables --user=mysql


Load the MySQL users and privileges data we backed up earlier

$ cd /root/mysql-5.1-backup/dump/
$ mysql < mysql.sql


Run the upgrade script so that everything gets upgraded to the version 5.5

$ mysql_upgrade


Stop the server and start it normally

$ /etc/init.d/mysql stop
$ /etc/init.d/mysql start


There you go, you have a MySQL 5.5 server up and running in no time! Do share
your thoughts if you try out MySQL 5.5.