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: http://www.theshell.guru).

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


Step 1
Since default Plesk repository does not have Percona, you'll need to add Atomic Turtle Repository. As root or sudo, issue the following command:

wget -q -O - http://www.atomicorp.com/installers/atomic |sh

You should see some sort of a successful message that the repository was added and is available for use.

Step 2
Now that the repository is installed, we need to upgrade MySQL 5.1 that's installed to MySQL 5.5. This step is a little tricky - my server crashed / seg. faulted at this step, immediately after the installation. If you observe faults right after or during this step, you may need to reboot your machine to clear the conflict.

Begin MySQL upgrade by issuing this command as sudo:
yum upgrade mysql

(Did it crash after install? Run: shutdown -r now) If it did not crash, continue.

Now we must run the MySQL upgrade script to upgrade the default mysql user table(s) from MySQL 5.1 to 5.5. There is a schema differences between 5.1 and 5.5 and if this is not executed certain functionality may break.

mysql_upgrade -uadmin -p`< /etc/psa/.psa.shadow `

Step 3
At this time you've upgraded MySQL to 5.5 and you should log into your plesk panel to make sure it's working. Try changing something, like a user account. You should get no errors and everything should be working. If all OK, continue.

Step 4
Now we start with the Percona 5.6 upgrade. If you have databases on your server - you should create a good backup mysql dump - just in case things go wrong. If your databases are large, you will have to modify this command to find a good location for your dump file.
mysqldump -uadmin -p`< /etc/psa/.psa.shadow ` --all-databases | gzip > /root/mysql-backup/mysql.all.dump.sql.gz

Step 5
Stop all mysql processes and make sure they are not running.

service mysqld stop

and check if they stopped, you shouldn't see any:

ps ax | grep mysql

Step 6
Continue removing MySQL 5.5 packages with commands below. Please note that you may not have plesk-mysql package, so if that's the case just delete it from the command.

rpm -e --nodeps `rpm -q --whatprovides mysql-server`
yum remove mysql mysql-server plesk-mysql


Step 7
Install the Percona repository with the command below:

yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Step 8
Install Percona components with the following command:
yum install Percona-Server-client-56 Percona-Server-server-56 Percona-Server-shared-56

(Optionally, I also needed mysql-devel for SphinxSearch, so I installed a fourth component. yum install Percona-Server-devel-56)

Step 9
At this time, Percona services were installed and you should be able to restart your Percona MySQL 5.6 Server with the following command. After restarting the MySQL server, log into your Plesk panel and change something small to make sure MySQL is working.

service mysql start

2 comments:

  1. I got this error message during backup "Warning: Skipping the data of table mysql.event. Specify the --events option explicitly."
    so I had to use this command
    mysqldump -uadmin -p`< /etc/psa/.psa.shadow` --all-databases --events | gzip > /root/mysql-backup/mysql.all.dump.sql.gz

    ReplyDelete