Home » Random Goodies » Upgrading to MySQL 5.5 Percona (cPanel/WHM)

Upgrading to MySQL 5.5 Percona (cPanel/WHM)

Looking to better track and limit MySQL usage by user, CPU time and other statistics along with a performance improvement? This guide will show you how to upgrade from MySQL 5.0.x to MySQL 5.5 Percona on a cPanel/WHM server with backups of the current configuration.

The following script was written and successfully tested on CentOS 5.8 and 6.2 x64 using cPanel/WHM 11.32.2.25, however it should also work for i686 systems as well.

#!/bin/bash
mkdir /root/percona
cpan install DBI
arch=$(uname -m)
ver=$(awk '{print$3}' /etc/redhat-release)
major="${ver%%.*}"
if [ -n "$(rpm -qa|grep Percona)" ];then echo "Percona server already installed. 10 seconds to quit.";sleep 10;fi
wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.14-20.5/RPM/rhel${major}/${arch}/Percona-Server-devel-55-5.5.14-rel20.5.149.rhel${major}.${arch}.rpm -P /root/percona
wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.14-20.5/RPM/rhel${major}/${arch}/Percona-Server-server-55-5.5.14-rel20.5.149.rhel${major}.${arch}.rpm -P /root/percona
wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.14-20.5/RPM/rhel${major}/${arch}/Percona-Server-client-55-5.5.14-rel20.5.149.rhel${major}.${arch}.rpm -P /root/percona
wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.14-20.5/RPM/rhel${major}/${arch}/Percona-Server-55-debuginfo-5.5.14-rel20.5.149.rhel${major}.${arch}.rpm -P /root/percona
wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.14-20.5/RPM/rhel${major}/${arch}/Percona-Server-shared-55-5.5.14-rel20.5.149.rhel${major}.${arch}.rpm -P /root/percona
wget http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.14-20.5/RPM/rhel${major}/${arch}/Percona-Server-test-55-5.5.14-rel20.5.149.rhel${major}.${arch}.rpm -P /root/percona
mkdir /home/mysqlbackups
for i in `mysql -ss -e "show databases"|grep -v '^[information|performance]_schema$'`;do mysqldump $i > /home/mysqlbackups/$i.sql;done
cp /etc/my.cnf /etc/init.d/mysql /home/mysqlbackups/
rpm -qa|grep -i mysql > /home/mysqlbackups/oldsql.log
rpm -qa|grep -i mysql|xargs -n1 rpm -e
sed -i 's/safe-show-database/#safe-show-database/g' /etc/my.cnf
rpm -iv `ls /root/percona/*.rpm`
service mysql start && mysql_upgrade && sleep 5
/scripts/easyapache --build

Direct download available: here
Bash oneline install: bash <(wget -qO- http://gurutek.biz/scripts/percona.sh)

At this point, you should be all set. The following queries will provide some of the statistics now readily available after the upgrade:

– CPU usage by MySQL user:

mysql -e "SELECT USER,CPU_TIME FROM information_schema.user_statistics ORDER BY CPU_TIME DESC"

– Usage by table rows read:

mysql -e "SELECT USER,CPU_TIME,TOTAL_CONNECTIONS,BYTES_RECEIVED,BYTES_SENT,ROWS_FETCHED,ROWS_UPDATED,TABLE_ROWS_READ FROM information_schema.user_statistics REVERSE ORDER BY TABLE_ROWS_READ DESC"

Note: By default userstat is disabled in Percona, and queries would return blank results. You can add the following to your my.cnf and restart MySQL:

userstat=on

After doing so, you can verify that the change took as follows:

[email protected] [~]# mysql -e "show global variables like 'userstat'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| userstat      | ON    |
+---------------+-------+
[email protected] [~]#

I also suggest reading over the following articles for more info:

http://www.percona.com/docs/wiki/percona-server:features:userstatv2
http://www.percona.com/doc/percona-xtrabackup/xtrabackup_bin/analyzing_table_statistics.html?id=percona-xtrabackup:xtrabackup:statistics

 

Have fun 😉