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:
[[email protected] ~]# 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 [[email protected] ~]# rpm -ivh http://dl.iuscommunity.org/pub/ius/stable/CentOS/6/x86_64/ius-release-1.0-14.ius.centos6.noarch.rpm # CentOS 7 [[email protected] ~]# 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:
[[email protected] ~]# 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:
[[email protected] ~]# yum install yum-plugin-replace [[email protected] ~]# 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:
[[email protected] ~]# service mysqld stop [[email protected] ~]# mysql -uroot mysql> use mysql; mysql> update user set password=PASSWORD("enternewpasswordhere") where User='root'; mysql> flush privileges; mysql> quit [[email protected] ~]# 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.
[[email protected] ~]# mysql_upgrade
If you find that the upgrade is not going to work for your environment, you can roll back to the original version:
[[email protected] ~]# 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!