Tech Zone

How to upgrade MySQL server 5.5 version to 5.6 on CentOS 7?

5 Mins read

MySQL is the most famous Open Source SQL DBMS (Database Management System) that’s developed, circulated, and supported by the Oracle Corporation. It’s fast, reliable, easy to use, scalable relational DBMS.  Its version MySQL Server 5.5 was made generally available in December 2010.

It had a number of features and enhancements over previous versions, including InnoDB as default storage option, better I/O subsystem, SMP support, semisynchronous replication, user- defined partitioning, support for Unicode character sets utf16, utf32, and utf8mb4 and SIGNAL and RESIGNAL statements complying with the SQL standard.

Version MySQL 5.6 was made generally available in February 2013. It included a number of new features like improved query optimizer performance, higher transactional output in InnoDB, better partitioning to query and manage huge tables, improved replication, new NoSQL-style memcached APIs, TIMESTAMP column type with ability to store milliseconds correctly, and better performance monitoring with PERFORMANCE_SCHEMA. The InnoDB storage engine also had better group commit performance and included full-text search support.

The newest version MySQL 5.7 has been made generally availabile from October 2015. But, it’s not in community edition. So, here we will guide how you can upgrade your MySQL 5.5 version to 5.6 version.

“Before the Acronis implementation, there was a huge risk that malware could take down our company. Now, with Acronis, I can say that we’ve done everything possible to protect our data from an attack.” CIO, high performance manufacturing company.
Read details in a report by IDC.

For installing the MySQL database via yum – the CentOS package manager, please follow the below steps of running commands in command prompt:

Step1: yum install mysql-server

Step2: Check the MySQL version

which is: mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Step3: Need to harden the MySQL server

Run the mysql_secure_installation script to address several security concerns in a default MySQL installation.

Step4: After the secure installation, now enter in MySQL using the below command:

mysql -u root -p

Step5: Use quit to quit from MySQL

Now for upgrading the MySQL from 5.1 to 5.6, first we need to upgrade in MySQL 5.5

For upgrading in 5.5, need the remi and epel repository.

wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm && rpm -Uvh epel-release-latest-6.noarch.rpm

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

vim /etc/yum.repos.d/remi.repo

Under the section that looks like [remi] make the following changes:

[remi]

name=Remi’s RPM repository for Enterprise Linux 6 – $basearch

#baseurl=http://rpms.remirepo.net/enterprise/6/remi/$basearch/

mirrorlist=http://rpms.remirepo.net/enterprise/6/remi/mirror

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi

Save the file.

Step6: yum update mysql* -y

image

Once that’s done, you can verify:

rpm -qa | grep mysql

Now we need to update MySQL 5.5 to 5.6.

First we take backup of mysql configuration file and the mysql directory for the security concerns.

For configuration file backup:

cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`

Database directory backup:

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

Now download the mysql 5.6 rpm:

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

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

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

Now stop the mysql service.

/etc/init.d/mysqld stop

Then remove the existing mysql 5.5.

yum remove mysql*

After removing the mysql, run the below 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%]

2017-09-09 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).

2017-09-09 11:51:26 1908 [Note] InnoDB: The InnoDB memory heap is disabled

2017-09-09 11:51:26 1908 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-09-09 11:51:26 1908 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-09-09 11:51:26 1908 [Note] InnoDB: CPU does not support crc32 instructions

2017-09-09 11:51:26 1908 [Note] InnoDB: Using Linux native AIO

2017-09-09 11:51:26 1908 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2017-09-09 11:51:26 1908 [Note] InnoDB: Completed initialization of buffer pool

2017-09-09 11:51:26 1908 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

2017-09-09 11:51:26 1908 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB

2017-09-09 11:51:26 1908 [Note] InnoDB: Database physically writes the file full: wait…

2017-09-09 11:51:26 1908 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB

2017-09-09 11:51:27 1908 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB

2017-09-09 11:51:27 1908 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2017-09-09 11:51:27 1908 [Warning] InnoDB: New log files created, LSN=45781

2017-09-09 11:51:27 1908 [Note] InnoDB: Doublewrite buffer not found: creating new

2017-09-09 11:51:27 1908 [Note] InnoDB: Doublewrite buffer created

2017-09-09 11:51:27 1908 [Note] InnoDB: 128 rollback segment(s) are active.

2017-09-09 11:51:27 1908 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-09-09 11:51:27 1908 [Note] InnoDB: Foreign key constraint system tables created

2017-09-09 11:51:27 1908 [Note] InnoDB: Creating tablespace and datafile system tables.

2017-09-09 11:51:27 1908 [Note] InnoDB: Tablespace and datafile system tables created.

2017-09-09 11:51:27 1908 [Note] InnoDB: Waiting for purge to start

2017-09-09 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’.

2017-09-09 11:51:28 1908 [Note] Binlog end

2017-09-09 11:51:28 1908 [Note] InnoDB: FTS optimize thread exiting.

2017-09-09 11:51:28 1908 [Note] InnoDB: Starting shutdown…

2017-09-09 11:51:29 1908 [Note] InnoDB: Shutdown completed; log sequence number 1625977

2017-09-09 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).

2017-09-09 11:51:29 1931 [Note] InnoDB: The InnoDB memory heap is disabled

2017-09-09 11:51:29 1931 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-09-09 11:51:29 1931 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-09-09 11:51:29 1931 [Note] InnoDB: CPU does not support crc32 instructions

2017-09-09 11:51:29 1931 [Note] InnoDB: Using Linux native AIO

2017-09-09 11:51:29 1931 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2017-09-09 11:51:29 1931 [Note] InnoDB: Completed initialization of buffer pool

2017-09-09 11:51:29 1931 [Note] InnoDB: Highest supported file format is Barracuda.

2017-09-09 11:51:29 1931 [Note] InnoDB: 128 rollback segment(s) are active.

2017-09-09 11:51:29 1931 [Note] InnoDB: Waiting for purge to start

2017-09-09 11:51:29 1931 [Note] InnoDB: 1.2.10 started; log sequence number 1625977

2017-09-09 11:51:29 1931 [Note] Binlog end

2017-09-09 11:51:29 1931 [Note] InnoDB: FTS optimize thread exiting.

2017-09-09 11:51:29 1931 [Note] InnoDB: Starting shutdown…

2017-09-09 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

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.

Now copy the existing configuration and the database directory

mv /etc/my.cnf_bak_`date +%F` /etc/my.cnf

mv /var/lib/mysql /var/lib/mysql_56_clean

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

Now start the mysql:

/etc/init.d/mysql start

Now copy the temporary password:

vi /root/.mysql_secret

Now run below command to set the password:

mysql -u root -p

Enter the temporary password, and then change the password:

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘enteryourpass’);

Now MySQL 5.6 installed!

You can verify it with:

mysql -V

In case you have any query regarding this writeup or MySQL database, let me know through comments section. I’ll be happy to help.

“Acronis is directly responsible for saving our company 1200 hours per year for IT operations staff in backup and recovery workflows.” CIO, high performance IT company.
Read details in a report by Forrester.

Services ZNetLive offer:

Domain Name Registration

Best Shared Hosting

WordPress Hosting Plan

VPS Hosting Plans

Buy Dedicated Server

2 posts

About author
Lalit Jain is an IT wizard who has a keen interest in all stuff related to the latest technologies. He loves giving back to the society and so contributes for humanitarian causes and is an active volunteer for many NGOs. He believes in the saying that “If you can dream it, you can do it”.
Articles
Related posts
Cloud HostingTech Zone

What is Akamai Connected Cloud? Explained in Detail.

4 Mins read
In recent years, cloud computing has grown significantly, becoming a key component of the infrastructure for modern business and technology initiatives. Its…
Cloud HostingNetwork-as-a-ServiceTech Zone

The role of networking in cloud computing

4 Mins read
Cloud computing has completely revolutionized the way modern businesses operate – by offering agile, scalable, and cost-effective access to resources on demand….
Tech Zone

Top 5 electronic signature apps to sign documents online in 2024

5 Mins read
In a world where everything from your memories to your money can be digitized, it is no surprise that electronic signatures are…