Percona MySQL installation tutorial

How to install

• Install the Percona repository

 rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.$(uname -i).rpm

• If the above RPM repo is not found you can check for up-to-date documentation here

 http://www.percona.com/docs/wiki/repositories:yum

• Check the current MySQL version installed on the server

 root@grumpy [~]# mysql -V
 mysql  Ver 14.12 Distrib 5.0.92, for unknown-linux-gnu (x86_64) using readline 5.1
 root@grumpy [~]# 

• Stop mysql server on the machine. Make sure that guardian will not restart it after stop!

 root@grumpy [~]# if [ -d /svcstop ]; then touch /svcstop/mysql; fi
 root@grumpy [~]# /etc/init.d/mysql stop
 Shutting down MySQL.                                       [  OK  ]
 root@grumpy [~]# 

• Backup the raw files of mysql

 root@grumpy [~]# cp -a /var/lib/mysql /var/lib/mysql.backup
 root@grumpy [~]# 

• Generate a list of your current databases

 root@grumpy [~]# mysql -A -sN -e "show databases" > active.db.list.log

• Remove MySQL-server installed on this machine. While removing it with yum make sure that it will not remove OTHER critical system packages. If it does so use rpm -e --nodeps instead!

 root@grumpy [~]# rpm -qa | grep -i "mysql.*server"
 MySQL-server-5.0.92-0.glibc23
 root@grumpy [~]# yum remove MySQL-server-5.0.92-0.glibc23

• Make sure to install Percona with the same ${major}.${minor} version as the old MySQL version. Use the following command to install PerconaSQL server. Disable excludes are added because of cPanel /etc/yum.conf config.

 yum --disableexcludes=main install Percona-SQL-server-SET-CORRECT-VERSION-HERE

• Monitor closely the response of the installation process for any errors
• Usually MySQL will be automatically started after the above yum install command. If not make sure to start it.
• Try to connect to the MySQL server

 root@grumpy [~]# mysql -A

• Check the mysql error log for any errors very carefully!

 root@sgded-bizserver [~]# tail -f /var/lib/mysql/$(hostname).err

• Make sure that all clients databases are present:

 mysql -A -sN -e "show databases" > new.db.list.log
 for dbname in $(> /dev/null ); then
               echo "Database $dbname is missing from show databases"
       fi
 done

• Enable User Statistics in /etc/my.cnf

 sed -i '/userstat_running/D' /etc/my.cnf && echo "userstat_running=1" >> /etc/my.cnf && /etc/init.d/mysql restart

Keep in mind that the userstat_running variable should be after [mysqld] definition and before any other [] definitions.

• Make sure that the user statistics are enabled for the MySQL server

 root@grumpy [~]# mysql -e 'show variables like "userstat_running"'
 +------------------+-------+
 | Variable_name    | Value |
 +------------------+-------+
 | userstat_running | ON    | 
 +------------------+-------+

If in the above output you see OFF, this means that the userstat_running variable is in the wrong place in /etc/my.cnf.

• You can verify that the statistics are working by executing the following command:

 root@grumpy [~]# mysql -A -sN information_schema -e "select * from user_statistics;"

• Enable CPUstats for percona by executing:

 /usr/local/1h/bin/detect_mysql.sh

• If you are using cPanel - In order to prevent cPanel upgrades from automatically restarting MySQL create the following files:

touch /etc/mysqldisable
touch /etc/mysqlupdisable

• Remove guardian lock file

 root@grumpy [~]# if [ -f /svcstop/mysql ]; then rm -f /svcstop/mysql; fi

How to revert

• Stop MySQL-Percona
• Remove userstat_running from /etc/my.cnf
• Install the same version of MySQL as percona was
• Start MySQL