How to Replace MySQL with Percona on Plesk CentOS 7

Reading Time: 4 minutesThis article outlines the procedure for replacing the native MySQL®️ or MariaDB®️ service that is preinstalled on any typical Plesk Onyx 11 CentOS 7 server. The procedure outlines removal of the existing MySQL related binaries and replaces them with an adequate version of the Percona binaries. Once these Percona binaries are in place, a typical multistage MySQL Incremental version upgrade is processed to bring the existing databases and Percona binaries to the desired Percona 5.7 version.  

Maintenance Stages Overview

The plan is broken up into preparation tasks and stages. Preparation tasks should be completed prior to the start time of your maintenance window. Stages are conducted throughout the maintenance event window and walk through the entire procedure for accomplishing each important stage of the outline. Preparation – Pre Maintenance Tasks These two important tasks must be completed prior to the scheduled start time of your maintenance window:

• Copy existing MySQL config file
• Stage backup of MySQL database data

We use stage zero in this plan to help expedite the backup process of the MySQL data. Since the amount of data MySQL will hold varies wildly per server, it is a good practice to run an incremental backup solution, like rsync, prior to the maintenance event window. Rsync will backup the majority of static data while MySQL is still running and then will backup the incremental difference once MySQL is shut down for maintenance. Copy existing MySQL config file: 1) Make a backup copy of the existing myself config file. cp -pv /etc/my.cnf{,.pre-percona.bak} Stage backup of MySQL database data: 2) Run the following rsync to clone the MySQL data. [IMPORTANT: trailing slash required] rsync -vaH /var/lib/mysql{,.pre-percona}/ 3) Wait for step 1 to complete before starting Stage 1.  

Stage 1 – Shutdown MySQL & Rerun Rsync Final Sync

The purpose of stage one is to ensure a pristine backup of the MySQL data is in place before proceeding. To accomplish this, it is necessary to completely stop MySQL services so all buffers are written and no additional changes are being written to the database. Once stopped, rsync is executed again to copy the differences between the now stopped pristine data and the target backup data. 1) Shutdown MySQL/MariaDB Service systemctl stop mysql 2) Rerun the following rsync to finalize the cloned data. [IMPORTANT: trailing slash required] rsync -vaH /var/lib/mysql{,.pre-percona}/ 3) Wait for rsync to complete before moving on to Stage 3.  

Stage 2 – Replace MariaDB 5.5 Binaries With Percona 5.5

Replacing the existing binaries involves removing the necessary binary packages from the system using the RPM command line tool instead of yum. This allows the packages to be removed without touching any of the dependencies linking to other system packages. Since Percona, like MariaDB, is a drop-in replacement for MySQL, once the new binaries are in place the linked dependencies won’t know the difference and will function normally. 1) Remove MariaDB packages without dependencies: rpm -e --nodeps mariadb mariadb-libs mariadb-server 2) Remove mariadb-devel package (do this as a separate command from above to avoid removal failure do to missing package) rpm -e --nodeps mariadb-devel 3) Install Percona repository: yum install https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm 4) Install Percona DB server: yum install Percona-Server-client-55 Percona-Server-server-55 Percona-Server-shared-55 5) Execute the following commands (allows InnoDB plugin to load correctly) mv /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.bak cp -a /var/lib/mysql/ibdata1.bak /var/lib/mysql/ibdata1 mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak cp -a /var/lib/mysql/ib_logfile0.bak /var/lib/mysql/ib_logfile0 mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak cp -a /var/lib/mysql/ib_logfile1.bak /var/lib/mysql/ib_logfile1 6) Make sure that there are no processes listening on port 3306 and kill the process if any. netstat -tulpn | grep 3306 7) Start MySQL (now Percona 5.5) service mysql start 8) Run mysql_upgrade script mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow` 9) [Optional]  Enable Percona XtraDB Cluster UDF from Percona Toolkit: plesk db -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" plesk db -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" plesk db -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" 10) Restart MySQL to complete upgrade procedure systemctl start mysql  

Stage 3 – Switch Plesk MySQL Package over to the Plesk MySQL Community package

Running Percona on Plesk is an unsupported configuration. For this reason, to satisfy dependency connections between the Plesk MySQL package and the new Percona binaries, it is necessary to switch the Plesk MySQL release over to using the Plesk MySQL Community release instead. The change is necessary to facilitate upgrading and downgrading Percona versions. 1) Create the necessary repo config file: printf '[PLESK_17_8_11-dist] %s\nname=PLESK_17_5_3 dist %s\nbaseurl=http://autoinstall.plesk.com/PSA_17.8.11/dist-rpm-CentOS-7-x86_64/ %s\nenabled=1 %s\ngpgcheck=1 %s\n' > /etc/yum.repos.d/plesk178.repo 2) Install “plesk-mysql-server-community” package from the new repository: yum install plesk-mysql-server-community 3) Disable /etc/yum.repos.d/plesk178.repo file as it’s no longer needed: mv /etc/yum.repos.d/plesk178.repo{,.disabled} 4) Remove ”plesk-mysql-server” package without touching dependencies. rpm -e --nodeps plesk-mysql-server   Percona should now be installed and working on the base 5.5 version. The next stages are identical to the typical MySQL upgrade process where binaries are upgraded, and then the schema updates are made manually using the mysql_upgrade tool.

Upgrade Percona 5.5 to 5.6

1) Stop MySQL service: service mysql stop 2) Remove Percona 55 packages without dependencies: rpm -e --nodeps Percona-Server-shared-55 Percona-Server-client-55 Percona-Server-server-55 3) Install Percona 56 packages yum install Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56 4) Make sure that there are no processes listening on port 3306 and kill the process if any. netstat -tulpn | grep 3306 5) Start the MySQL service: service mysql start 6) Upgrade MySQL tables: mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`  

Stage 4 – Upgrade Percona 5.6 to 5.7

1) Stop MySQL service: service mysql stop 2) Remove Percona 56 packages without dependencies: rpm -e --nodeps Percona-Server-shared-56 Percona-Server-client-56 Percona-Server-server-56 3) Install Percona 57 packages yum install Percona-Server-shared-57 Percona-Server-client-57 Percona-Server-server-57 4)  Make sure that there are no processes listening on port 3306 and kill the process if any. netstat -tulpn | grep 3306 5) Start the MySQL service: service mysql start 6) Upgrade MySQL tables: mysql_upgrade -uadmin -p`cat /etc/psa/.psa.shadow`  

Stage 5 – Finalizing Upgrade

The system should now be running Percona 5.7 and be fully accessible by applications. At this stage, any sites or application using a database should be tested and any errors investigated.