Sometimes you may run across a scenerio where you have to update MySQL. This is easy enough to do, however you should always test this out on a dev server before applying to production just in case you run into problems.
As a critical note, before performing the update, make sure you have a working MySQLdump of all your databases. This cannot be stressed enough! There are many ways of performing a MySQLdump. Be sure you can actually restore from those backups as well! One possible method of performing the backup of all the databases into a single large file, which locks the tables creating possible downtime, would be:
[root@db01 ~]# mysqldump --all-databases --master-data | gzip -1 > /root/all.sql.gz
On CentOS, I prefer to use the IUS repo’s as they are actively maintained, and they do not overwrite stock packages which is important.
So to get started, first setup the IUS repo if it isn’t already installed on your server:
# CentOS 6 [root@db01 ~]# rpm -ivh http://dl.iuscommunity.org/pub/ius/stable/CentOS/6/x86_64/ius-release-1.0-14.ius.centos6.noarch.rpm # CentOS 7 [root@db01 ~]# rpm -ivh http://dl.iuscommunity.org/pub/ius/stable/CentOS/7/x86_64/ius-release-1.0-14.ius.centos7.noarch.rpm
To upgrade MySQL, yum has a plugin called ‘yum-replace’, which will automatically replace one package with another of your choosing. This simplifies the process of upgrading MySQL.
First, confirm that you are not already running another custom version of MySQL:
[root@db01 ~]# rpm -qa |grep -i mysql mysql55-server-5.5.45-1.ius.el6.6.z.x86_64 mysql55-5.5.45-1.ius.el6.6.z.x86_64 ...
Using the output from above, it looks like we just have MySQL 5.5 installed. I want to upgrade from MySQL 5.5 to MySQL 5.6. Here is how you would run it:
[root@db01 ~]# yum install yum-plugin-replace [root@db01 ~]# yum replace mysql55 --replace-with mysql56u
During the upgrade process, I noticed that I could no longer log in with the root MySQL user. So to reset the root MySQL password:
[root@db01 ~]# service mysqld stop
[root@db01 ~]# mysql -uroot
mysql> use mysql;
mysql> update user set password=PASSWORD("enternewpasswordhere") where User='root';
mysql> flush privileges;
mysql> quit
[root@db01 ~]# service mysqld restart
Once the version has been updated, be sure to run mysql_upgrade. mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.
[root@db01 ~]# mysql_upgrade
If you find that the upgrade is not going to work for your environment, you can roll back to the original version:
[root@db01 ~]# yum replace mysql56u --replace-with mysql55
The yum-replace plugin makes upgrading and downgrading MySQL very fast and simple. But just to reiterate an earlier statement, make sure you test this out on a development server before applying to your production server! It is always possible that something may not be compatible with the new version of MySQL! So always test first so you know what to expect!