Quick guide to upgrade MySQL 5.5 to MySQL 5.6

In this article, we are going to upgrade mysql version with 5 easy steps.

Benefits of Mysql 5.6 Version:

MySQL 5.6 has been out for a few months, and it provides a lot of enhancements, specifically with regards to replication:

  • Global transaction identifiers offer an efficient way to deal with the hassles of maintaining a replication topology when a master crashes.
  • Multi-threaded replication is an excellent solution to overcome the performance limitations of the normal single-threaded replication.
  • Crash-safe replication provides new configuration to make replication more resilient in case of a crash.
  • Binlog checksums aim at avoiding data corruption.

Steps To Upgrade Mysql Version 5.5 To 5.6

1. Check Current version and dependency packages

Before start with the upgrade, first, check which MySQL version and it's related packages installed currently on the server. To check run below commands,

$ mysql -V
MySQL Ver 14.14 Distrib 5.5.46, for debian-linux-gnu (x86_64) using readline 6.3
$ dpkg -l | grep mysql

ii libdbd-mysql-perl 4.028-2+b1 amd64 Perl5 database interface to the MySQL database
ii libmysqlclient18:amd64 5.5.46-0+deb8u1 amd64 MySQL database client library
ii mysql-client 5.5.46-0+deb8u1 all MySQL database client (metapackage depending on the latest version)
ii mysql-client-5.5 5.5.46-0+deb8u1 amd64 MySQL database client binaries
ii mysql-common 5.5.46-0+deb8u1 all MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.5.46-0+deb8u1 all MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.5 5.5.46-0+deb8u1 amd64 MySQL database server binaries and system database setup
ii mysql-server-core-5.5 5.5.46-0+deb8u1 amd64 MySQL database server binaries
ii php5-mysql 5.6.29+dfsg-0+deb8u1 amd64 MySQL module for php5
ii php7.0-mysql 7.0.14-1~dotdeb+8.1 amd64 MySQL module for PHP

2. Backup of Database

Once you get the current mysql version and dependency, packages information. We will start the steps to upgrade mysql-server. Upgrading MySQL version is an easy task. But before doing this, we will take the backup of current databases. This upgradations steps will not affect your current database or its settings. Just for the safer side, we will make one backup copy of available databases.

Below command is used to dump single database in .gz format. It will save your storage on the system.

$ mysqldump -u root -p dbname | gzip -9 dbname.sql.gz

You can also take dump of all database in single file using below command,

$ mysqldump -u root -p --all-databases | gzip -9 > alldatabases.sql.gz


3. Add Repository for Mysql 5.6

Now we are going to add the repository for latest MySQL version. Run below command in the terminal which helps you to download the repository file on the system.


$ wget -O mysql-apt-config.deb https://dev.mysql.com/get/mysql-apt-config_0.3.7-1debian8_all.deb

After downloading the file, use below "dpkg" command to install .deb file.

$ dpkg -i mysql-apt-config.deb

When you run above command, it will prompt you to choose MySQL version from 5.6 and 5.7. Choose any one as per your requirement. Refer below screens, so you will get the idea to choose MySQL version.

upgrade mysql
mysql-upgrade2
mysql-upgrade3

4. Upgrade Mysql

Now run below commands which helps you to upgrade the MySQL version.

$ apt-get update

When you run below install command, it will remove the older MySQL version and installed the latest mysql version on the system.

$ apt-get install mysql-server

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following package was automatically installed and is no longer required:
redis-tools
Use 'apt-get autoremove' to remove it.
The following extra packages will be installed:
libnuma1 mysql-client mysql-common mysql-community-client mysql-community-server
The following packages will be REMOVED:
mysql-client-5.5 mysql-server-5.5 mysql-server-core-5.5
The following NEW packages will be installed:
libnuma1 mysql-community-client mysql-community-server
The following packages will be upgraded:
mysql-client mysql-common mysql-server
3 upgraded, 3 newly installed, 3 to remove and 193 not upgraded.
Need to get 23.2 MB of archives.
After this operation, 75.6 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y

5. Restart Mysql Service

After completion of upgrade command. Restart the mysql service and check the mysql version using below commands.

$ /etc/init.d/mysql restart

$ mysql -V

That is it. Upgradation of mysql version is completing here. 

I hope you like the article if you find any difficulties then please do comment your queries or problem via the comment section, till then stay tuned to techthings.org for more such valuable articles.

Read More:

 

Leave a Reply

Your email address will not be published. Required fields are marked *