How to Replace MySQL with Percona on Plesk CentOS 7

This 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 Expectations: Length & Downtime

Suggested Maintenance Duration:  90 minutes
Minimum Expected Downtime: ~30 minutes of service flapping

The expected MySQL service downtime for this procedure is less than 30 minutes of MySQL service availability going up and down repeatedly (flapping). This figure assumes that all goes smoothly and no unforeseen errors are encountered. The suggested maintenance length allows time for troubleshooting should error arise as well as an extra 30 minutes in the unlikely event that all changes need to be reverted and the maintenance plan aborted.

 

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}/

Note:
Very Large databases will take considerably longer to complete so plan accordingly.

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

NOTE:
MariaDB 5.5 is the version installed with Onyx 11 CentOS 7, so this is the starting point.

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.

Error: Please install all available updates for your release before upgrading [SOLVED]

When updating from Ubuntu 16.04 to 18.04 we used the

do-release-upgrade command as one of the steps to get the newer versions.  While using that method the update Ubuntu’s core we came across a known bug that would not allow us to continue with the upgrade. Instead, it would give the following error message:

Checking for a new Ubuntu release
Please install all available updates for your release before upgrading.

 

The Fix

If you experience the error above run each command below.

sudo apt-get update
sudo apt-get upgrade -y
sudo apt-get dist-upgrade
sudo do-release-upgrade

Once you’ve updated your server with the commands above, feel free to continue with our How to Upgrade Ubuntu 16.04 to 18.04 tutorial.

Update MariaDB from 10.0 to 10.3 on CentOS 7

We recently posted an article on Upgrading MariaDB 10.0 to 10.3.9 on Ubuntu 16.04. We are now going to provide insight on upgrading MariaDB 10.0.X to 10.3.X on a CentOS 7 server.

The article assumes that the administrator is already presently working with MariaDB 10.0.X. It is considered best practice to consult the official documentation from MariaDB prior to updating/upgrading the current version. This is pertinent in doing because it is vital in understanding what exactly is going to be changed, updated, and even removed from one version to the next.

We will start with upgrading from version 10.0.X to 10.1.X, as you’ll need to upgrade step-by -tep to reach MySQL’s 10.3 version(10.0 > 10.1 > 10.2 > 10.3). Please note that yum will automatically default you to the most recent stable version, unless a MariaDB repository is configured. More on that here.

If deciding to place all the version repos in one file, one must specify a differing bracket tag, so yum provides the option for the version of the package being searched for. This is what my /etc/yum.repos.d/MariaDB.repo file looks like:

When searching or looking for specific versions of MariaDB, generally speaking, one can start here. We’ll be upgrading to the most current stable version per their releases page. For the sake of this article, we will be installing 10.1.37.

Following this step, query the available packages:

yum list available –showduplicates MariaDB-Server.x86_64

After the specified repo has been configured, save the file and proceed on.

Step 1.  Set innodb_fast_shutdown to 0

mysql

--innodb_fast_shutdown = 0;

Step 2.  Shutdown MariaDB 10.0

Exit out of your MySQL database and stop your MySQL service.

systemctl stop mysql

Verify that MySQL has been stopped:

systemctl status mysql

Step 3. Make a Backup

You have the choice of backing up your MariaDB instance either through the cp or rysnc command. The best way to grab a pristine copy of any MySQL server is to stop the service completely ( i.e., Step 2 ) and make a copy of /var/lib/mysql.

cp -r /var/lib/mysql /var/lib/mysqlbackup

Step 4. Un-install MariaDB 10.0.X

yum remove mariadb-server

Step 5. Install MySQL 10.1

We found that the best way to install the specific version is to issue the following command:

yum --disablerepo=* --enablerepo=mariadb10.1 install mariadb-serverIf this isn’t specified, yum will automatically update you to the most up to date version of the package.  Once return/enter is selected you should then see a preview of what is going to be installed next:

Proceed to ‘y’ if all looks good and has been confirmed that the version being sought after will be installed. Allow the Packages to install. Once completed, it is time to move to Step 6.

Step 6. Run mysql_upgrade

We mentioned in our Ubuntu article that this was not needed on Debian based packages. However on RHEL, CentOS, and Fedora packages do not have this natively built into its update process. First, you’ll need to start the service.

systemctl start mysql

Next, run the following to ensure the service is up and running:

systemctl status mysql Once run, we should see a similar output, confirming that the service is up and running:

Now we can finally run mysql_upgrade:

mysql_upgrade

We should then see the different upgrades and fixes that are being done:

Once the ‘OK’ status has been seen, you’re free to continue working with the most current version or continue upgrading to the desired version. Follow the above steps to get to 10.2.x and 10.3.x! We hope this helps and as always check out our database hosting product for you high availibility needs.

Upgrading MariaDB 10.0 to 10.3.9 on Ubuntu 16.04

MariaDB is quickly becoming the de facto open-source database software to use in development, production, and even enterprise environments. Our very own Cloud Sites product uses the newest in MariaDB as it’s mostly known for being a fork and drop-in replacement to MySQL, which is created and maintained by the original MySQL developers.

Continue reading “Upgrading MariaDB 10.0 to 10.3.9 on Ubuntu 16.04”

Upgrade PHP 5.6 to 7


PHP is a programming language that can run with Apache or Microsoft IIS and works with your server to execute the requests that make up your website. 88% of online sites run on, soon to be vulnerable PHP 5.X technology. At the close of this year, scheduled by Dec 31, 2018 security support will end for our dear old friend PHP 5.6, meaning bugs and security fixes will not be tended to and could lead to security vulnerabilities. 
Each PHP version gets supported actively for two years while the third year only gets critical security updates. Luckily, the PHP gods had smiled upon us and extended the life for just a year longer than the typical PHP version before giving us the new year deadline. For all of you developers out there wanting to know exactly what is changing, here’s a helpful migration guide from PHP 5.6 to PHP 7.X.

Continue reading “Upgrade PHP 5.6 to 7”

Upgrading from the Legacy Storm Private Network

Note:
Please note that this article is considered legacy documentation.

The recently announced deprecation of the Legacy Storm Private Network has prompted several questions, the most frequent of which being: How to upgrade and am I affected? Fortunately this announcement only affects a handful of our thousands of clients, those being customers who started using the Private Networking back in 2013. If you’re not sure, you’re welcome to open a ticket and be certain.

Regarding the upgrade process, we’ve made that as easy as possible and accessible to anyone with access to the manage interface. This how-to will walk you through the steps you need to follow to get detach from the current implementation and get connected to the new, improved version.

Continue reading “Upgrading from the Legacy Storm Private Network”

Upgrading PHP on Windows

Performing an upgrade to PHP on Windows Server

Keeping your software and applications up to date is a crucial part of maintaining security and stability in your web hosting systems. Unfortunately, updating system components and back-end software can sometimes be a frustrating and a difficult process. However, thanks to Microsoft’s Web Platform Installer, upgrading PHP on a Windows server with IIS is as simple as a few clicks.

Continue reading “Upgrading PHP on Windows”

How to Upgrade and Patch cPanel / WHM

Pre-Flight Check

  • These instructions are intended specifically for checking your version of cPanel or WHM via the command line or the WHM dashboard.
  • I’ll be working from a Liquid Web Managed CentOS 7 server, and I’ll be logged in as root.

Step #1: Log In to WHM

First, log in to WHM. You’ll arrive at your WHM dashboard:

How to Upgrade and Patch cPanel WHM

Continue reading “How to Upgrade and Patch cPanel / WHM”

DNF (Dandified Yum) Command Examples: Install, Remove, Upgrade, and Downgrade

DNF (Dandified Yum) 101: Basic Package Manager Interaction
I. What is DNF (Dandified Yum)?
II. DNF Examples: Install, Remove, Upgrade, and Downgrade

Continue reading “DNF (Dandified Yum) Command Examples: Install, Remove, Upgrade, and Downgrade”

How to Resize a Server

Upgrading your Storm® server is a simple process, and can be done in just a few satisfying clicks. Upgrades are a necessity. You work hard on your blog, or ecommerce store, and the traffic grows! Once you’ve optimized your WordPress site or Magento store, reward yourself with an easy upgrade process and increase the available resources to your Storm® by following the steps below. This process can be followed for Storm® VPS or Storm® Dedicated servers.

Pre-Flight Check
  • These instructions are intended specifically for resizing your Storm® server.

Continue reading “How to Resize a Server”