Monday, March 24, 2014

Converting old MySQL passwords to MySQL 5.6 (16 characters to 41 characters)

  • Remove or comment old_passwords = 1 in my.cnf
Restart MySQL. If you don’t, MySQL will keep using the old password format, which will mean that you cannot upgrade the passwords using the builtin PASSWORD() hashing function.
The old password hashes are 16 characters, the new ones are 41 characters.
  • Connect to the database, and run the following query:
    SELECT user, Length(Password) FROM mysql.user;
This will show you which passwords are in the old format, ex:
| user     | Length(`Password`) |
| root     |                 41 |
| root     |                 16 |
| user2    |                 16 |
| user2    |                 16 |
Notice here that each user can have multiple rows (one for each different host specification).
To update the password for each user, run the following:
UPDATE mysql.user SET Password = PASSWORD('password') WHERE user = 'username';
Finally, flush privileges:

Wednesday, March 19, 2014

How to upgrade Plesk MySQL to Percona MySQL 5.6 (latest)

Plesk admin panel usually ships with MySQL 5.1 and if you are looking to upgrade it to 5.6 the process (at least for me) was not quite clear, with lots of little issues along the way. I learned a couple of things that are not immediately obvious and hope to capture them along the way in this quick guide. Here is my version of the steps necessary to upgrade MySQL 5.1 to Percona MySQL Server (and client). (Big shout-out to this blogger as well for clearing things up very nicely:

Quick Note on "Why upgrade to Percona MySQL 5.6"
  • In short, Percona MySQL server is a modified MySQL 5.6 distribution that is optimized for maximum performance and will improve the overall performance of your database services (in theory, in most cases).

Starting Configuration
  • CentOS 64 Bit
  • Plesk 11.5+ with standard default packages installed
  • MySQL 5.1