A step by step guide to upgrading to MySQL 5.5

MySQL 5.5 has created a lot of hype and its 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 have myself 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

UPDATE: This article is translated to Serbo-Croatian language by Anja Skrba from Webhostinggeeks.com.

  • Anonymous

    Get an Error after :
    mysqld --skip-grant-tables --user=mysql


    [Warning] Can't create test file /usr/local/mysql/data/ubuntu-virtual.lower-test
    [Warning] Can't create test file /usr/local/mysql/data/ubuntu-virtual.lower-test

    Waht’s wrong? I already checked the permission for /usr/local/mysql and re-done the “correct permissions” step…

  • http://www.ovaistariq.net/ Ovais Tariq

    Could you send me the output of the following command:

    ls -l /usr/local/mysql/ | grep data

    and the contents of the /etc/my.cnf file

  • Anonymous

    ..and another question: In which section of /etc/my.cfg should I copy the “configurations”? in [client] or in [server] ?

  • http://www.ovaistariq.net/ Ovais Tariq

    You should copy the configurations in the “[mysqld]” section. The “[client]” sections holds parameter values that the mysql client uses when connecting to the mysql server.

  • Pingback: A Techies' Blog » Blog Archive » Upgrading to MySQL 5.5()

  • Pingback: T客网 ︱ Techpot » Blog Archive » 手把手教你升级到 Mysql 5.5()

  • Pingback: Create multiple WordPress blogs on localhost « Talking IT()

  • Pingback: 存储人生 » Blog Archive » Windows下升级MySQL5.0到5.5()

  • Andre Cornelissen

    things that did not work for me:
    /usr/sbin/mysqld  (the 5.1 daemon) was still there and earlier inthe PATH
    now starting 5.5 daemon but unable to create socket, probably fighting apparmor as I see a /etc/apparmor.d/usr.sbin.msqld (probablu left over) but no usr.local.mysql.bin.mysqld config.

    Boeboe

  • Gare

    Thank you for writing this tutorial.  It is very helpful.  I have an ubuntu-related question:  

    Were you able to install other mysql related components via apt-get in ubuntu repo’s, like php5-mysql ?

    php5-mysql  depends on libmysqlclient16 and mysql-common.    Can we install these from apt as well?

    Thank you again.

  • NicoVS

    Just a few problems i encountered:
    I downloaded  MySQL Community Server 5.5.25When trying to start mysql with mysqld –skip-grant-tables –user=mysql i got this error:1)  [ERROR] Error message file ‘/usr/local/mysql-5.5/share/english/errmsg.sys’ had only 641 error messages, but it should contain at least 728 error messages.=> fixed by: # cd /root/mysql-5.5-install# ar vx mysql-5.5.25-debian6.0-x86_64.deb# tar -xvf data.tar.gz# cp opt/mysql/server-5.5/share/english/errmsg.sys /usr/loca/mysql/share/errmsg.sys2) Then i could start mysql with mysqld –skip-grant-tables –user=mysql but i could not mysql < mysql.sql.I modified /etc/my.cnf the wrong way… i needed to change my /etc/my.cnf like this:# The following options will be passed to all MySQL clients[client]#password       = your_passwordport            = 3306socket          = /var/run/mysqld/mysqld.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port            = 3306user            = mysqlpid-file        = /var/run/mysqld/mysqld.pidsocket          = /var/run/mysqld/mysqld.sockbasedir         = /usr/local/mysqldatadir         = /usr/local/mysql/datatmpdir          = /tmplog_error       = /var/log/mysql/error.logThats it :)

  • http://www.facebook.com/interian87 Jose Interian

    Hello, thxs for your step by step guide. So can you help me with apache 2.4.2 and php 5.4.4 STEP BY STEP? i’m using LAMP Native from UBUNTU SERVER 10.4. i really need a lot of help

  • Gare

    Hi – 

    Consider upgrading Ubuntu to 12.04 , and it will upgrade Apache and PHP and MySQL for you.  currently 12.04 includes MySQL version 5.5.24.  

  • Ray Ban Sunglasses

    Ray Ban
    UK
    have produced its initial appearance about 80years
    in the past and become a hitting item inside the market speedily. These
    Ray Ban
    Sunglasses
    are committed to blocking glares and
    several other dangerous rays. Due to their distinctive traits and effectiveness
    in filtering dangerous rays, raybanuk2012.net
    grows to be widely accepted and famous all over the globe.

     

  • Kushal

    I have just began learning. I have installed MySQL 5.5 working on local host. Can any one suggest me how to connect my own database over the internet

  • http://www.ovaistariq.net/ Ovais Tariq

    To connect to MySQL over the internet, you need to make sure the following things work:
    – The port 3306 is opened up in the firewall and remote machines can connect to the MySQL host
    – MySQL is listening on the public IP interface
    – The MySQL host is connected to the internet
    – A MySQL user exists that allows ppl to login to the MySQL server

  • ara

    I’ve crash my server Step by Step. !!!

  • Pingback: trying to upgrade mysql from 5.1 to 5.5 on debian()

  • Anonymous

    What happens with `service mysql (start|stop|status)`? They seem to be missing after following this procedure

  • Pingback: how to upgrade mysql 5.0 to 5.5 in redhat linux - dBforums()

  • Roman

    Perfectly wroted and everything is working.
    PS: If you are amateur in linux so mysql_upgrade script and mysqld is in /var/lib/mysql/bin directory.

  • Roman

    Sorry. /usr/local/mysql/bin .

  • Trotamundos

    Hi!

    I have gone through all steps except the creation of the socket directory because it already existed.

    Then, on the step when I had to run: mysql < mysql.sql I got the following error:

    "ERROR 1006 (HY000) at line 22: Can't create database 'mysql' (errno: 2)"

    Could you please help me?

    Thanks!

  • http://www.ovaistariq.net/ Ovais Tariq

    Can you check the permissions of the MySQL data directory are correct?

  • Grrr Grrrr Grrrrrr

    Thank you very much for the post. I have done it successfully in Centos, though some commands differ the overall procedure are very same :)

  • Pingback: MySQL 5.5.11 Installation Tutorial - Step One | REDROKK - Interactive Marketing REDROKK – Interactive Marketing()

  • Pingback: Mysql în paralel cu Percona | Tiberiu Contiu()

  • http://www.facebook.com/ihor.mysak.1 Ihor Mysak

    you can write “–all-databases” for mysqldump instead of dump each DBs

    and Thanks :)

  • Pingback: Install MySQL 5.5 On Ubuntu 10.04 | Click & Find Answer !()

  • Michael

    Hi,

    thanks for the detailed guideline.

    It worked fine till I faced the point starting the server without grant-tables.

    $ mysqld –skip-grant-tables –user=mysql
    -bash: /usr/local/mysql/bin/mysqld: No such file or directory

    But $ ls /usr/local/mysql/bin/
    shows the mysqld and others.

    Does anyone know how to overcome that problem?

    Thanks for your help in advance.

  • James Lari

    ERROR 404: Not Found.

    How can I download the latest version of Mysql for my ubuntu 10.04 server ?

  • http://www.ovaistariq.net/ Ovais Tariq
  • Ravi Soni
  • http://www.ovaistariq.net/ Ovais Tariq

    The download link changes as new releases of MySQL 5.5 are made. You can find the latest 5.5 release here:

    – For 32-bit platforms: http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.32-linux2.6-i686.tar.gz/from/http://cdn.mysql.com/

    – For 64-bit platforms: http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.32-linux2.6-x86_64.tar.gz/from/http://cdn.mysql.com/

  • Rohan

    Thanks Ovais! Could you please suggest the steps for RedHat ( RPM Style) same upgradation.

  • http://www.jugarjugar.net/ Jugar Jugar

    Thank you for sharing your this useful. I’m still not ready to update to this new version even though it has many significant improvements. I’m really looking forward to more in it. and I still like the older version, it really suit me

  • Pingback: Bookmark 17 feb 14__PC | maulikblog()

  • http://superhero.com Frederik87k

    Hei, just trying to follow this procedure. But this is strange: Step 12: Copy the sample MySQL configuration file. Then step 14: Remove the MySQL files from the older version. So I copy the config and delete it again!? oO ^^

  • amicns

    Please don’t follow this article. It doesn’t work rather screw up your system.

  • http://www.ovaistariq.net/ Ovais Tariq

    There are hundreds of people who have successfully upgraded to MySQL 5.5 using this article as a reference, since it was written more than 3 years ago. If you have done something wrong and not followed the steps correctly, then it comes down to you and not the article.

  • u2005k

    I found much easier method to upgrade to MySQL 5.5

    1. In my experience I did not find any issue with existing MySQL installation or databases but to be on safer side, please take backup of all databases using instructions given in this tutorial.

    2. Install Remi repository

    Fedora

    ## Remi Dependency on Fedora 18, 17, 16

    rpm -Uvh http://download1.rpmfusion.org/free/fedora/rpmfusion-free-release-stable.noarch.rpm

    rpm -Uvh http://download1.rpmfusion.org/nonfree/fedora/rpmfusion-nonfree-release-stable.noarch.rpm

    ## Fedora 18 ##

    rpm -Uvh http://rpms.famillecollet.com/remi-release-18.rpm

    ## Fedora 17 ##

    rpm -Uvh http://rpms.famillecollet.com/remi-release-17.rpm

    ## Fedora 16 ##

    rpm -Uvh http://rpms.famillecollet.com/remi-release-16.rpm

    ## Fedora 15 ##

    rpm -Uvh http://rpms.famillecollet.com/remi-release-15.rpm

    ## Fedora 14 ##

    rpm -Uvh http://rpms.famillecollet.com/remi-release-14.rpm

    ## Fedora 13 ##

    rpm -Uvh http://rpms.famillecollet.com/remi-release-13.rpm

    ## Fedora 12 ##

    rpm -Uvh http://rpms.famillecollet.com/remi-release-12.rpm

    CentOS and Red Hat (RHEL)

    ## Remi Dependency on CentOS 6 and Red Hat (RHEL) 6 ##

    rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm

    ## CentOS 6 and Red Hat (RHEL) 6 ##

    rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

    3. Check Available MySQL versions

    Fedora 18, 17, 16, 15, 14, 13, 12

    yum –enablerepo=remi list mysql mysql-server

    CentOS 6.4/6.3/6.2/6.1/6/5.9 and Red Hat (RHEL) 6.4/6.3/6.2/6.1/6/5.9

    yum –enablerepo=remi,remi-test list mysql mysql-server

    Output:

    Loaded plugins: changelog, fastestmirror, presto, refresh-packagekit

    remi | 3.0 kB 00:00

    remi/primary_db | 106 kB 00:00

    Available Packages

    mysql.i686 5.5.37-1.fc18.remi @remi

    mysql-server.i686 5.5.37-1.fc18.remi @remi

    ## Remi Dependency on CentOS 5 and Red Hat (RHEL) 5 ##

    rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

    ## CentOS 5 and Red Hat (RHEL) 5 ##

    rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-5.rpm

    4. Update or Install MySQL 5.5.37

    Fedora 18, 17, 16, 15, 14, 13, 12

    yum –enablerepo=remi install mysql mysql-server

    CentOS 6.5/6.4/6.3/6.2/6.1/6/5.10 and Red Hat (RHEL) 6.5/6.4/6.3/6.2/6.1/6/5.10

    yum –enablerepo=remi,remi-test install mysql mysql-server

    It will get all dependencies and install everything that is required. Once done, just start MySQL server, secure it or restore your old settings.

  • http://www.boomplace.com/ Rob de Roy

    Hammer, danke!

  • Pingback: How do I upgrade MySQL to 5.5, am I best off waiting for it to be available through apt-get? | XL-UAT()

  • Pingback: PPA for MySQL 5.5 underway? [duplicate] | XL-UAT()

  • Pingback: Downgrade Mysql di Ubuntu 13.04 Server (Unsolved) | Nothing Personal()

  • Bender Rodriguez

    For anyone stuck in /vim PATH, after you made the edits, move the cursor down past the language line, then hit ESC, then colon, then q then exclamation point. Enter.

    PATH
    LANG
    :q!

  • Pingback: Downgrade Mysql di Ubuntu 13.04 Server | Nothing Personal()

  • Pingback: Downgrade Mysql di Ubuntu 13.04 Server (part 1) | Nothing Personal()

  • Pingback: Downgrade Mysql di Ubuntu 13.04 Server (part 2 – finished) | Nothing Personal()

  • Pingback: MySQL 5.5 starts and then instantly stops | Some Ubuntu Questions and Answers()

  • http://www.rafnet.co.uk Riaz Ahmed

    need help with this. i followed this and now i cant get it to work. I am using centos 5 (maybe thats the problem). I used YUM instead of apt-get.

    Also it hung on this command so i skipped it:

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

    I get to this part:
    mysqld –skip-grant-tables –user=mysql

    and it gives erros:
    sh: mysqld: command not found

    please help.

  • http://www.ovaistariq.net/ Ovais Tariq

    That’s because mysqld in not in your path. I would recommend installing on CentOS using regular package management (yum). You can download the official MySQL yum repositories for CentOS 5 here: http://dev.mysql.com/downloads/repo/yum/

  • Alex

    Thank you for detailed instruction. Helped me to update mysql from 5.1 to 5.5 on debian squeezy.

  • Vlad

    25 times always only one error: ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)