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

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/

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

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

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

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

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

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

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

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

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]

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

mysql_upgrade utility should start fixing your existing tables.

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.

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