Skip to end of metadata
Go to start of metadata

When Upgrading from MySQL 5.1 to 5.6 Using InnoDB database engine.

Step-by-step guide

Upgrading MySQL from 5.1 to 5.6

Environment: Ubuntu Server 10.04 LTS 

=> The upgrade has to be done in two stages. First upgrade from 5.1 to 5.5 and then from 5.5 to 5.6.

UPGRADING 5.1 to 5.5

Preparation.

STEP 1: BACKING IT UP 

Before Upgrade make sure to backup current mysql installation. The backup should include the following at the least.
  1. Dump of all your databases (using mysqldump backup tool).
  2. Mysql Data Files.
  3. Mysql configuration files Names & Location of these files differ depending on the platform and type of installation used.
     Below are the location for a standard MySQL 5.1 installation on Ubuntu 10.04 LTS (Lucid Lynx).
    1. Data files: /var/lib/mysql
    2. Configuration files: /etc/mysql
Backup processes.
  1. Open terminal, create a backup directory in your home directory and change to it. See below:- 

    $ cd 
    $ mkdir mysqlbackup && cd mysqlbackup
  2. Create dump of all databases.

    $ mkdir dump
    $ mysqldump -u root -p --all-databases --events --exended-insert --flush-privileges --triggers --verbose > dump/alldbs.sql

    3. Stop mysql service, Copy mysql data files and configuration files (You will need root privilege for this).

    $ sudo stop mysql
    $ sudo cp -pr /etc/mysql mysqlconf/
    $ sudo cp -pr /var/lib/mysql datafiles/ 

          

STEP 2: REMOVING 5.1 AND INSTALLING 5.5

  1. Download mysql 5.5 deb package from http://dev.mysql.com/downloads/mysql/. On a drop down menu select debian Linux 
    (Also choose previous versions because by default you will see latest versions, By the time this is being created, it s 5.6.x).
  2. Remove MySQL 5.1 installation by running the following commands. Finally remove mysql from start-up script using update-rc.d command.

 

$ sudo apt-get remove mysql-common mysql-server-5.1 mysql-server-core-5.1 mysql-client-5.1 mysql-client-core-5.1
$ sudo apt-get auto remove
$ sudo update-rc.d -f mysql remove

      3. Change to directory in which the .deb package was downloaded to in STEP 2.1 above. (In this case I use Downloads directory located in home directory of the user). Run the following commands to install. 

(The package name may change depending on the architecture of machine used - Choose appropriate package) - MySQL will be installed in /opt/mysql

$ cd
$ cd Downloads/
$ sudo dpkg --install mysql-5.5-35-debian6.0-x86_64.deb

 

4. Update start-up script to register the new installation. (This is not necessary if the system uses Upstart startup script i.e. /etc/init/mysql.conf)

$ sudo cp /opt/mysql/server-5.5/support-files/mysql.server /etc/init.d/mysql
$ sudo update-rc.d mysql defaults

 

STEP 3: UPDATING MYSQL CONFIGURATION AND  PATH ENVIRONMENTAL VARIABLE.

  1. Open /etc/environment file and append mysql server bin directory to PATH environmental variable.
$ sudo vi /etc/environment
PATH="/usr/local/sbin:/usr/bin:/opt/mysql/server-5.5/bin"
 
$ sudo source /etc/environment

 

      2. Update MySQL's configuration. Open /etc/mysql/my.cnf in any editor of your choice and change basedir variable. {*Also rename the parameter key_buffer to key_buffer_size*}

$ sudo vi /etc/mysql/my.cnf
basedir = /opt/mysql/server-5.5

 

    3. Make appropriate changes to path to mysql plugins directory in /etc/apparmor.d/usr.sbin.mysqld as shown below.

$ sudo vi /etc/apparmor.d/usr.sbin.mysqld
/opt/mysql/server-5.5/lib/plugin/ r,
/opt/mysql/server-5.5/lib/plugin/*.so* mr,
/usr/sbin/mysqld mr,

   

4. Create mysqld symbolic link in /usr/sbin/ directory as shown below.

$ sudo ln -s /opt/mysql/server5.5/bin/mysqld /usr/sbin/mysqld

 

5. Install libaio1 if not already there.

$ sudo apt-get install libaio1 

 

STEP 4: STARTING THE SERVER AND UPGRADING.

Run the following commands to start and upgrade.

Note:**Restarting the system is not necessary but it is good practice. If you restart the system you really do NOT need to start mysql manually).

$ sudo /usr/sbin/mysqld --skip-grant-tables
$ mysql_upgrade -uroot -p --verbose

 

Now restart MySQL and your system is ready for use. (Running mysql 5.5)

UPGRADING FROM 5.5 to 5.6

        STEP 5: Backing up. (Do this as shown in  STEP 1 above).

        STEP 6: REMOVING 5.5 AND INSTALLING 5.6

        1. Download mysql 5.6 deb package from http://dev.mysql.com/downloads/mysql/. On a drop down menu select debian Linux and then select version 5.6.

        2. Install the downloaded package following the instructions in STEP 2.3. (Remember to use 5.6.x package this time (smile)).

            This will automatically take care of removing version 5.5 and install 5.6 in /opt/mysql/server5.6/

        STEP 7:UPDATING MYSQL CONFIGURATION AND  PATH ENVIRONMENTAL VARIABLE.

         1. Edit /etc/environment and append mysql bin directory to PATH variable as shown below.          

$ sudo vi /etc/environment
PATH="/usr/local/sbin:/usr/bin:/opt/mysql/server-5.6/bin"
 
$ sudo source /etc/environment

             

2. Update MySQL's configuration. Open /etc/mysql/my.cnf in any editor of your choice and change basedir variable. Also rename key_buffer to key_buffer_size

$ sudo vi /etc/mysql/my.cnf
basedir = /opt/mysql/server-5.6
innodb_file_per_table     = 0
innodb_checksum_algorithm = INNODB
binlog_checksum           = NONE

         

  3. Make appropriate changes to path to mysql plugins directory in /etc/apparmor.d/usr.sbin.mysqld as shown below.

$sudo vi /etc/apparmor.d/usr.sbin.mysqld
/opt/mysql/server-5.6/lib/plugin/ r,
/opt/mysql/server-5.6/lib/plugin/*.so* mr,
/usr/sbin/mysqld mr,

   

4. Create mysqld symbolic link in /usr/sbin/ directory as shown below.

$sudo ln -s /opt/mysql/server5.6/bin/mysqld /usr/sbin/mysqld

   

STEP 8: STARTING THE SERVER AND UPGRADING.

Run the following commands to start and upgrade.

Note:**Restarting the system is not necessary but it is good practice. If you restart the system you really do NOT need to start mysql manually).

$ sudo start mysql
$ mysql_upgrade -uroot -p --verbose

  

         If you do not get any errors, MySQL 5.6.x is ready for use.