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.

Install and Connect to PostgreSQL 10 on Ubuntu 16.04

PostgreSQL (pronounced “post-gress-Q-L”) is a household name for open source relational database management systems. Its object-relational meaning that you’ll be able to use objects, classes database schemas and in the query language.  In this tutorial, we will show you how to install and connect to your PostgreSQL database on Ubuntu 16.04.

 

Step 1: Install PostgreSQL

First, we’ll obtain the authentication keys need to validate packages from the PostgreSQL repo.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/PostgreSQL.list'

As a best practice, we will update our server before installing PostgreSQL.

apt-get -y update

After the update is complete, we’ll run the following command to install PostgreSQL

apt-get install postgresql-10

 

Step 2: Logging into PostgreSQL

Once installed PostgreSQL creates a default user named “postgres”.  This user works in a way different to that of other popular databases like MySQL.  PostgreSQL users can change the method of authentication, but by default, it uses a mode called ident. Ident takes your OS username and compares it with the allowed database usernames.

You must first switch to the default Postgres user

su - postgres

You’ll now see that you are logged in as that user via the prompt change

postgres@host2:~$

Afterward, you can then enter the PostgreSQL terminal by typing:

psql

You’ll know you are connected by the message below:

psql (9.5.14)
Type "help" for help.
postgres=#

 

Step 3: Logging out of PostSQL

To exit out of your postgresql environment use the following command

\q

Now that you’ve created your PostgreSQL world it’s time to stretch your feet!  Let’s start creating and listing databases using our world renown Cloud VPS servers.

 

Troubleshooting: Too Many Redirects

The error “too many redirects” means that the website keeps being redirected between different addresses in a way that will never complete. Often this is the result of competing redirects, one trying to force HTTPS (SSL) and another redirecting back to HTTP (non-SSL), or between www and non-www forms of the URL.

Continue reading “Troubleshooting: Too Many Redirects”

Listing and Switching Databases in PostgreSQL

PostgreSQL (pronounced “post-gress-Q-L”) is a household name for open source relational database management systems. Its object-relational meaning that you’ll be able to use objects, classes in database schemas and the query language. As part of our PostgreSQL series, we’ll show you how to list and switch between databases quickly.

Pre-flight

Log into your Ubuntu 16.04 server

Step 1: Login to your Database
su - postgres

Step 2: Enter the PostgreSQL environment
psql

With the psql command, you’ll be greeted by its current version and command prompt.

psql (9.5.14)
Type "help" for help.
postgres=#

Step 3: List Your PostgreSQL databases
Often, you’ll need to switch from database to database, but first, we will list the available database in PostgreSQL

postgres=# \list

By default, PosgreSQL has 3 databases: postgres, template0 and template1

Step 4: Switching Between Databases in PostgreSQL
Switching between databases is another way of saying you are closing one connection and opening another. When you need to change between databases, you’ll use the “connect” command, which is conveniently shortened to \c, followed by the database name.

\connect dbname

Or:

\c dbname

Creating and Deleting a PostgreSQL Database

PostgreSQL (pronounced “post-gress-Q-L”) is a household name for open source relational database management systems. Its object-relational meaning that you’ll be able to use objects, classes in database schemas and the query language.  In this tutorial, we will be demonstrating some essentials like creating, listing and deleting a database.

If you have already installed PostgreSQL to your Ubuntu 16.04 server using our last tutorial, your next step is to create a database.  You’ll be accomplishing this task by using the default superuser, postgres, to log in.

Step 1: Login as the Postgres User

su - postgres

Step 2: Enter the PostgreSQL Environment

psql

With the psql command, you’ll be greeted by its current version and command prompt.

psql (9.5.14)
Type "help" for help.
postgres=#

Step 3: Creating the PostgreSQL Database

Let’s create our first database by typing in the command below.  Replace dbname with the database name of your choice.

CREATE DATABASE dbname;

 

Verify Creation of PostgreSQL Database

Using the following command allows us to view the databases in our PostgreSQL instance (you can ignore, delete or utilize the default databases: postgres, template0, template1)

postgres=# \list

 

Deleting a PostgreSQL Database

Once you’ve backed up your removing your PostgreSQL database is a cinch!  Its similar to creating a database but we will be using the drop command. In my command line example, the database name is “dbname”. By using the list command in the previous section, you’ll be able to view your databases’ names. Replace dbname with your database’s name in the command below.

DROP DATABASE dbname;

 

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”

Change Primary Domain in WHM

If you use multiple aliases ( previously called parked domains) within a cPanel account, you may find yourself wanting to change the main domain used for the cPanel account containing these domains. Changing the primary domain is desirable for multiple reasons and many times occurs when the site in use switches from one TLD to another (i.e., .net to .com). You may desire to change this if the name of your company or site changes. It can also occur when a domain is no longer used, or when the domain is removed from an account. Sometimes the wrong site name was set to be the primary domain, to begin with (we all make mistakes, right?). Whatever the reason for changing the primary domain, the change is relatively simple to make. However, it does make some potentially significant changes on the account that could create the need for additional changes for site functionality, so it is best to understand what these changes are before making this decision wholeheartedly.

Email & DNS

This change will modify multiple factors of the domain including (if you so choose) the cPanel username (which is usually not advised), the FTP username and MySQL users. Making this change will delete any custom DNS records if you are using custom nameservers setup within WHM and hosting your DNS. If using custom nameservers be sure to go into the DNS editor and take a screenshot of your DNS for the domain or copy the records to a text document before making this change. An additional effect is that all e-mail accounts change to reflect the new domain, for example changing the primary domain from yourdomain.com to newdomain.com will change admin@yourdomain.com to admin@newdomain.com. You may then need to update the e-mail address and username (for both incoming and outgoing servers within your e-mail client) on any devices that e-mail account is set up on.

Aliases

If you already added an alias ( a parked domain which shares content) or addon domain (which has its content apart from the main domain), you need to remove it first. Meaning you may need to save the content and site data for addon domains elsewhere on the server until after this change. You will also want to remove any subdomains of your primary domain name before you can change it as well. The following can be used as a basic guide to remove these as the process for each is the same:

Log into cPanel: yourdomain.com/cpanel
Navigate to and click on ‘Aliases’ (this may be listed as parked domains on older versions of WHM) (or you could locate subdomains or addon domains)
Locate the alias you want to be removed and click remove.

SSL Certificates

If you have an SSL certificate applied to this account, you will end up revoking the SSL installed for the current primary domain by removing it. In these cases a new SSL is necessary. If you are using AutoSSL, you may need to re-run to ensure all sites have an SSL installed appropriately. If you have purchased an SSL, you will need to order a new SSL for the new domain name.

Changing the primary domain may require modifying the siteurl if you are using WordPress and this could break the installation until you change the URL.

Hostname

Often changing the primary domain is confused with the hostname of the server, these are separate changes. However, for clarity, this does not change the hostname of the server (your server name) and does not change the name of the server within your manage.liquidweb.com account either. Changing the primary domain will only change data related to the cPanel account and its associated user. While most changes are made within the cPanel account for the user, this change can not be made within the cPanel account for the domain. Changing the primary domain is done via WHM which requires root level access.

Backups

Before making any significant changes on your server, its advised to have the ability to revert in case of mistakes. Create a full website backup via cPanel for the account you want to modify. You can find instructions on how to do this here.

Ensure that you have available backups for the domain within the backup restoration area in WHM. These will be available if you already have backups configured within WHM. If you use alternate means to back up your accounts, ensure you have available backups before making this change.

To change the primary domain, you will need to do this within WHM.

  1. You can access WHM by using the servers IP followed by the port 2087, or if DNS is set up for the hostname, you can access WHM by using the hostname/whm. Another method is to use a domain name on the server followed by /whm:Examples:
    • 192.168.1.1:2087 (replace the IP with your servers IP)
    • https://hostname.com/whm
    • https://domainname.com/whm
  2. To change the primary domain login to WHM using the root user and root password:To change the primary domain log in to WHM using root.
  3. After logging the top right of your window is a search bar (you may need to expand this menu). Access List Accounts via the search bar and click on the link it displays.Find the primary domain in WHM by searching for "List Accounts".
  4. Find the user you want to modify by typing in the name of the account in the new search bar that opens. Then click the + symbol next to the user:In the WHM account click the "modify account" button to change the primary domain.
  5. Finally, click the Modify Account button:The 'modify account' button changes the primary domain in WHM.
  6. Change the Primary Domain to the domain you want in its place:WHM screen indicates where to change the primary domain.
  7. Decide if you’re going to adjust options. You could decide to modify the databases associated with the domain to include new prefixes, for example, changing the Username.
Note:
If you are not familiar with what these changes mean. It’s highly recommended NOT to change the cPanel username. Since the username is tied to the database name, you may get database errors when altering. Changing the username requires further site coding and configuration by your developer. Your WordPress or CMS configuration file will need to be updated if the username is changed creating new database names.

After making this change, you may find that you want to keep the old e-mail addresses used by the old primary domain. If this is the case, the fix is simple. Park the old domain on the new one via an alias and create new e-mail accounts under the old domain name within CPanel’s e-mail accounts section. This way you can still use your existing e-mail accounts and also change the primary domain.

You have successfully changed the primary domain for this account! Our Support Teams are filled with talented admins with an intimate knowledge of multiple web hosting technologies, especially those discussed in this article. If you are Fully Managed VPS customer and you are uncomfortable with performing the outlined steps, we are a phone call, chat or ticket away from assisting you with this process

MySQL Performance: Converting MySQL to MariaDB

As we explored in our previous article of our MySQL Perfomance Series: MySQL vs. MariaDB there are very few downsides to using MariaDB over standard MySQL. Our high-availbility MariaDBs have proven itself to be a worthy successor with easily migitated drawbacks.  As the last article in our series we will focus on upgrading to various MySQL and MariaDB version on the following servers:

CentOS 6/7

Ubuntu 14.04/16.04

Continue reading “MySQL Performance: Converting MySQL to MariaDB”

MySQL Performance: MySQL vs. MariaDB

MariaDB and MySQL logos.

We continue our series on MySQL Optimization by focusing on the differences and similarities between MariaDB and MySQL. MariaDB is a popular alternative to using traditional MySQL for database hosting. The free market of hosting allows additional actors to compete with the long-standing database solution, MySQL. MariaDB is the most prominent alternative to MySQL and is a favorite among industry giants such as Google, Craigslist, Wikipedia, Arch Linux, RedHat, CentOS, Fedora, and cPanel. In a direct performance comparison, MariaDB leaps to the head of the pack through performance optimizations, easy of conversion and compatibility.

Why Switch to MariaDB?

MariaDB is mostly a clone of MySQL, but it also provides several improvements over running the standard MySQL instance. MariaDB aims for a drop-in replacement to MySQL, which makes the switch painless and straightforward. Combine the simplicity of switching with a long list of improvements, MariaDB brings both performance and cutting-edge features to the table. Below are some of its selling points over standard MySQL.

More Storage Engines Options: There is 12 new storage engine built into MariaDB. Among these are CONNECT, Spider, and SphinxSE. Visit their Storage Engines page for a complete list of these engines, how they function, and ways to leverage them to optimize your database.

Speed improvements: MariaDB sports many new speed improvements compared directly with standard MySQL. This improved performance makes MariaDB stand apart from the baseline performance of traditional MySQL servers. Like MySQL, MariaDB has dozens of features for speed optimization including disk access, JOIN and EXPLAIN improvements, subquery, derived tables/views, execution control, and optimizer control.

Faster Indexes/Cache: When using the MEMORY storage engine, MariaDB can complete INSERT statements up to 24% faster than traditional MySQL servers, along with CHECKSUM TABLE and MyISAM Segment Key Cache being 4x faster.

Speedier and Larger Connection Pool: MariaDB benefits from an improved pool of threads that run faster and support up to 200,000+ connections where standard MySQL falls short.

Improved Replication: MariaDB sports faster and safer replication with updates being up to 2x faster than with traditional MySQL Replication setups. Now possible, parallel replication allows the existence of Active/Active or Master/Master configurations. MariaDB replication is backward compatible with MySQL servers, so migrating your cluster to MariaDB is possible by utilizing one node at a time.

New Extensions/Features: There are several new extension and features, to name a few, the WITH, JSON and KILL statements. DECIMAL sees an increased from 30 to 38 decimals while KILL ALL queries for a specified user.

List of Features: Available MariaDB’s website is a comprehensive list of improvements and features, located here: MariaDB versus MySQL-Features.

 

What is the Downside to Using MariaDB?

The MariaDB project is entirely open-source and free, unlike MySQL which uses dual licensing to keep its Enterprise edition specific features proprietary. The MariaDB developers actively update and enhance with cutting-edge features that standard MySQL does not carry.

Missing Features: MySQL uses some proprietary code in its Enterprise Edition. MariaDB does not have access to this propriety content and is a closed source, meaning the features are only available to MySQL Enterprise users.

Mitigation: MariaDB has addressed this concern through allowing viable open-source alternative plugins for MariaDB, providing the same functionality that the MySQL Enterprise Edition offers. This allows the vast majority of standard MySQL and MySQL Enterprise Edition users the option to switch to MariaDB, taking advantage of its robust performance enhancing features.

Delayed MySQL Compatibility: MariaDB originally forked from MySQL 5.5, so this is the base starting point for the MariaDB source code. Meaning that newer features and bug fixes developed for standard MySQL after version 5.5 are not part of the existing MariaDB source code.

Mitigation: MariaDB conducts monthly merges of the standard MySQL source code to ensure both compatibility and feature/bug-fix adoption. This kind of regular code merging, allows MariaDB to maintain its allure as a drop-in replacement for practically any existing versions of standard MySQL. However, there will always be a delay causing MariaDB to lag behind MySQL’s newer features and bug patches.