How to upgrade MySQL 5.5 to MySQL 5.6 on CentOS 6.3/Red-hat/Fedora

Standard

As I mentioned in my other post for installing MySQL 5.6 on a new server, first head over here and download MySQL 5.6.10 rpm’s under Oracle & Redhat Linux 6 section of rpm’s.You want to download these rpm’s to your CentOS 6 server [As we will install 64-bit version on MySQL 5.6] :

  1. Red Hat/Oracle Enterprise Linux ver. 6 (x86, 64-bit), RPM Package – MySQL Server : MySQL-server-5.6.10-1.el6.x86_64.rpm
  2. Red Hat/Oracle Enterprise Linux ver. 6 (x86, 64-bit), RPM Package – Shared components : MySQL-shared-5.6.10-1.el6.x86_64.rpm
  3. Red Hat/Oracle Enterprise Linux ver. 6 (x86, 64-bit), RPM Package – Client Utilities : MySQL-client-5.6.10-1.el6.x86_64.rpm

To download on command line [Make sure that you have wget utility installed, if you dont then install it]:

yum install wget

 

Now download the rpm’s:

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-shared-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-client-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-server-5.6.10-1.el6.x86_64.rpm/from/http://cdn.mysql.com/

mysql 5.6 download

We now have our rpm’s to upgrade our server to MySQL 5.6

Before we proceed further, stop existing MySQL Server. Run the following command to stop your server [Assuming your init.d start-up script in named as MySQL, you can check that in /etc/init.d directory]:

service mysql stop

mysql 5.5 stop

Now move the data directory under /var/lib/mysql to /var/lib/mysql_55. Run the following command in order to move. [If you used custom path when you installed MySQL 5.5 then change below command appropriately]

mv /var/lib/mysql/ /var/lib/mysql_55

move mysql 5.5 directory

Now you can securely remove the existing installation of MySQL 5.5 rpm’s from your server, before we install the new MySQL 5.6 database engine. Check what existing packages are installed on our existing database server that correspond to MySQL 5.5 engine. To do that run the following command :

rpm -qa | grep -i mysql

find rpm installed packages

Running the above command returns the following packages as that are currently installed in existing database server. Now remove them one by one from server in order for us to install MySQL 5.6 engine.

To remove we can simply run the following:

yum remove MySQL-server-5.5.30-1.el6.x86_64 MySQL-shared-5.5.30-1.el6.x86_64 MySQL-client-5.5.30-1.el6.x86_64

Image 026

Now you have remove all MySQL 5.5 related libraries that correspond to MySQL 5.5 database engine.

Now lets install MySQL 5.6.10 from the rpm’s that we downloaded earlier. To install them just run the following commands:

rpm -ivh MySQL-shared-5.6.10-1.el6.x86_64.rpm

rpm -ivh MySQL-client-5.6.10-1.el6.x86_64.rpm

rpm -ivh MySQL-server-5.6.10-1.el6.x86_64.rpm

Image 027 Image 028

MySQL 5.6 Server Installation log :

Preparing… ########################################### [100%]
1:MySQL-server ########################################### [100%]
2013-02-10 11:51:26 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2013-02-10 11:51:26 1908 [Note] InnoDB: The InnoDB memory heap is disabled
2013-02-10 11:51:26 1908 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-02-10 11:51:26 1908 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-02-10 11:51:26 1908 [Note] InnoDB: CPU does not support crc32 instructions
2013-02-10 11:51:26 1908 [Note] InnoDB: Using Linux native AIO
2013-02-10 11:51:26 1908 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-02-10 11:51:26 1908 [Note] InnoDB: Completed initialization of buffer pool
2013-02-10 11:51:26 1908 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2013-02-10 11:51:26 1908 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2013-02-10 11:51:26 1908 [Note] InnoDB: Database physically writes the file full: wait…
2013-02-10 11:51:26 1908 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2013-02-10 11:51:27 1908 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2013-02-10 11:51:27 1908 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2013-02-10 11:51:27 1908 [Warning] InnoDB: New log files created, LSN=45781
2013-02-10 11:51:27 1908 [Note] InnoDB: Doublewrite buffer not found: creating new
2013-02-10 11:51:27 1908 [Note] InnoDB: Doublewrite buffer created
2013-02-10 11:51:27 1908 [Note] InnoDB: 128 rollback segment(s) are active.
2013-02-10 11:51:27 1908 [Warning] InnoDB: Creating foreign key constraint system tables.
2013-02-10 11:51:27 1908 [Note] InnoDB: Foreign key constraint system tables created
2013-02-10 11:51:27 1908 [Note] InnoDB: Creating tablespace and datafile system tables.
2013-02-10 11:51:27 1908 [Note] InnoDB: Tablespace and datafile system tables created.
2013-02-10 11:51:27 1908 [Note] InnoDB: Waiting for purge to start
2013-02-10 11:51:27 1908 [Note] InnoDB: 1.2.10 started; log sequence number 0
A random root password has been set. You will find it in ‘/root/.mysql_secret’.
2013-02-10 11:51:28 1908 [Note] Binlog end
2013-02-10 11:51:28 1908 [Note] InnoDB: FTS optimize thread exiting.
2013-02-10 11:51:28 1908 [Note] InnoDB: Starting shutdown…
2013-02-10 11:51:29 1908 [Note] InnoDB: Shutdown completed; log sequence number 1625977

2013-02-10 11:51:29 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2013-02-10 11:51:29 1931 [Note] InnoDB: The InnoDB memory heap is disabled
2013-02-10 11:51:29 1931 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-02-10 11:51:29 1931 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-02-10 11:51:29 1931 [Note] InnoDB: CPU does not support crc32 instructions
2013-02-10 11:51:29 1931 [Note] InnoDB: Using Linux native AIO
2013-02-10 11:51:29 1931 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-02-10 11:51:29 1931 [Note] InnoDB: Completed initialization of buffer pool
2013-02-10 11:51:29 1931 [Note] InnoDB: Highest supported file format is Barracuda.
2013-02-10 11:51:29 1931 [Note] InnoDB: 128 rollback segment(s) are active.
2013-02-10 11:51:29 1931 [Note] InnoDB: Waiting for purge to start
2013-02-10 11:51:29 1931 [Note] InnoDB: 1.2.10 started; log sequence number 1625977
2013-02-10 11:51:29 1931 [Note] Binlog end
2013-02-10 11:51:29 1931 [Note] InnoDB: FTS optimize thread exiting.
2013-02-10 11:51:29 1931 [Note] InnoDB: Starting shutdown…
2013-02-10 11:51:31 1931 [Note] InnoDB: Shutdown completed; log sequence number 1625987

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !

You will find that password in ‘/root/.mysql_secret’.

You must change that password on your first connect,
no other statement but ‘SET PASSWORD’ will be accepted.
See the manual for the semantics of the ‘password expired’ flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /usr/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used –defaults-file)
and when you later start the server.
The new default config file was created as /usr/my-new.cnf,
please compare it with your file and take the changes you need.

We are now done with installing MySQL 5.6 server. Now before we start the server we need to move back the data directory that we moved earlier and put back to where it was. I will be copying back rather than moving as a fail safe if the upgrade fails. Then we still have the original data directory in place to fix things up or install MySQL 5.5 server backs to its place for rolling back the upgrade process.

mv /var/lib/mysql /var/lib/mysql_56_clean
cp -R /var/lib/mysql_55 /var/lib/mysql

Image 029

Now we have our data directory back to its place. Right now we are in a state that our engine libraries correspond to MySQL 5.6.10 and our data directory is of MySQL 5.5. We need to upgrade our data directory to be compatible with MySQL 5.6 database engine. In order to proceed further we need to start the database engine now.

service mysql start

error starting mysql

Bam ! there will be an error starting the database engine as shown above:

Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/open sourcedbms.pid).

Database engine will fail to start as permissions on data directory are wrong. To fix that run the following command :

chown -R mysql:mysql /var/lib/mysql

Image 033

Now try starting the database server again with same command:

service mysql start

and you should be able to successfully start it. [If you did not, you might have data directories installed in a different path or custom config file paths etc., check you database logs and see if those tell something about it. Please put that in this blog comments and I will try to solve that if possible based on my understanding]

Image 034

Now lets upgrade the data directories to be compatible with MySQL 5.6 engine to play nice with this new engine. To upgrade we need to run mysql_upgrade. This utility checks your tables, and attempts to repair them if necessary. It also updates your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. Please use an existing super user that you have in your database [5.5] engine to run this utility. Please change the host/user/password according to your parameters/settings.

mysql_upgrade -hopensourcedbms -uroot -p

Image 037

mysql_upgrade utility should start fixing your existing tables.

Image 018

Now you have successfully updated your data directory as well. This completes full upgrade of MySQL 5.5 database server to MySQL 5.6 database server.

Try connecting from any existing client and you should be able to connect successfully to this database server.

sqlyog mysql 5.6

There are certainly a lot of config parameter changes / feature changes in MySQL 5.6 database version. I highly recommend going through those new welcome additions in MySQL 5.6 version and tune your database server accordingly [Also go through Server Administration / Security areas from 5.6 Manual for good understanding ]. Also this upgrade is a lot straight forward as I did not have to deal with existing server config parameters, custom paths etc., I would say think through this tutorial and act according to your environment/needs.

Before you proceed to do this on your production servers make sure that you block 3306 [Or what ever port your database server runs on] port from your iptables and test this process in pre-production environments at-least couple of times.

Last but not least make sure that this new MySQL server is enabled to auto start when server restarts.

chkconfig mysql --level 2345 on
Facebooktwittergoogle_plusredditpinterestlinkedinmail

About Prashanth Goriparthi

Sr. Database Administrator / Architect

19 Comments

  1. I think you forgot to add the line where you run:

    bin/mysql_install_db

    Because the Installation Log dosnt occur until you run that command. Atleast for me it didnt.

  2. Pingback: Keeping your Drupal from Drooping — part 2 | Open Source DBA's Blog

  3. Isn’t there a mysql yum repo? So I just put that in /etc/yum.repos.d/ and then I can do a yum install mysql and it will give me the latest version of mysql 5.6???? I need a repo for mysql 5.6. Do you know if there is a yum repo for mysql 5.6?

  4. So i did upgrade and looks like its working, but the issue is
    in old my.cnf datadir was set to other than original directory and been working file
    after upgrade , I am setting same path to that directory and I am getting error specified in your description (about pid file), but permissions are fine

  5. Very good procedure – thanks
    The mysql directory ownership did not have to be fixed.
    You can also do "yum localinstall" to avoid the "RPMDB altered outside of yum" warning.

  6. Thanks for putting this, however please dont froget these in any system .
    1. Take a backup of your databases first
    2. Take a backup of your mysql config file first /etc/my.cnf

  7. Excellent step-by-step description!
    Worked like a charm after I got the RPMs downloaded – the links didn’t work so I got the RPMs from Oracle D/L 🙂
    Thanks!

  8. Pingback: How To Upgrade MySql Version On Centos(Linux) | Gyankosh – Inspire !! Fun !! Innovate !!

  9. Pingback: How to upgrade MySQL 5.5 to MySQL 5.6 on CentOS 6.3/Red-hat/Fedora | செந்தில்நாதன் ஜெய்கிருஷ்ணன்

  10. Prashanth, thanks a lot for this blog. I am nowhere near a DBA but I just migrated my mysql 5.1 to 5.5 to 5.6. The second part of this is where ur blog was really helpful. I faced some issues while running the rpm ivh commands due to conflicting dependencies. Had to erase them yum remove mysql-server and yum remove mysql-lib. Installation was smooth after that=. Thanks again for this article.

  11. Pingback: Обновление mysql5.5 до 5.6 (centos6) — Сrib for Linux

Leave a Reply

* Captcha [Please fill/solve below] : * Time limit is exhausted. Please reload the CAPTCHA.