Centos: recover MySQL users access after upgrade to MySQL-5.5 - Server support blog

Centos: recover MySQL users access after upgrade to MySQL-5.5

I had one of my customers asking me for the MySQL server version upgrade (OS provided MySQL 5.1.73 to something above MySQL 5.5) for one of his dedicated servers. Neither of us expected any problems from that, but we’ve ran into some that we had to resolve quickly because of production web sites running on the same server.
For more modern software versions I usually use excellent IUS reposity which in combination with EPEL repository serves about 87% of my needs in 3rd party packages and updates.

I didn’t expect any surprises when I run my server update command, and from the first glance there were none :

I’ve checked that server daemon was running, trying sql root got ‘access denied’ reply and just assumed that customer changed MySQL root password.
In a little while customer contacted me complaining that none of the sites users can access the database server. Also he confirmed that he did not change MySQL root password.
From that moment I figured out that not everything is predictable with this upgrade procedure.

  • /etc/my.cnf got replaced with the package version and the previous version got saved as /etc/my.cnf.rpmsave while it should be the other way around – what if you have non-standard datadir location? Which was the case in our situation.
  • Second – the procedure call for running mysql_upgrade scripts for fixing system and privileges tables but this is impossible because there is no root access

So, the proper cours of action in this situation would be to

  • Stop running daemon.
    service mysqld stop
  • Revert back to your default /etc/my.cnf . Start manually
  • Run the upgrade script /usr/bin/mysql_upgrade
  • Upon success restart mysqld in normal mode

After completing that you should have your users access back with the same passwords and privileges as before.

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">