Replace MySQL with Percona Server on CentOS like a pro! This tutorial answers all your doubts and queries. Read on to find out what our in-house experts advise.

Percona offers a plethora of advantages that you cannot say no to. It also makes an excellent drop-in replacement for MySQL that makes the entire task a breeze. Our Support Engineers are here to help you replace your MySQL with a Percona Server without running into any trouble.

Replacing MySQL with Percona Server on CentOS

Did you know that Percona offers better consistency, performance as well as scalability compared to MySQL? It is no wonder that Percona server is preferred by many of our customers over MySQL. In case you already have MySQL installed on CentOS, fear not. This detailed guide by our experienced Support Engineers will help you install and figure out the Percona server configuration.

Why should I replace MySQL with Percona Server on CentOS?

Before we dive into the technical aspects and get ready to replace MySQl with Percona server on CentOS, let’s take a look at some of the advantages it offers:

• Better stability and consistency:  Percona offers better stability by being less likely to cause your applications to slow down or have unexpected intermittent downtime. Additionally, has the ability to perform uniformly even when under a heavy load.
• XtraDB: A primary advantage offered by Eprcona is XtraDB. It is a backward-compatible fork of InnoDB, a general-purpose storage engine. Furthermore, XtraDB offers better query throughput from your hardware. Another advantage offered by XtraDB is that all of your current InnoDB tables are loaded transparently through XtraDB devoid of any migration process.
• PAM Authentication: Although this feature is available in MySQL Enterprise Edition, Percona offers you the chance to tie your authentication scheme with database access.
• Metrics: Another key feature about Percona is that it comes with several built-in performance metrics. This allows you to track down which tables, users, queries or indexes are slowing you down. In other words, you will finally be able to get better performance from your server and rely less on guesswork.
• Compatibility: Since Percona is a replacement for MySQL, you do not have to worry about compatibility issues. Any application designed for MySQL will run smoothly with Percona without requiring any changes.

Prerequisites for Percona server

Now that we have gotten an idea about the various advantages offered by the Percona server, let’s explore the prerequisites for the installation to take place:

• CentOS: You can install the Percona server on Centos 8, 7, 5, or 6 without any worries.
• Root access: Ensure you have root access before embarking on this journey.
• Up-to-date MySQL or MariaDB installation Or a fresh Droplet: Percona can replace a current MariaDB or MySQL installation or be installed on a new Droplet from scratch.
• Memory: A default installation requires around 16GB Droplet to avoid installation failures. Another option would be a 512MB Droplet with swap space allocation. Although the installation will be successful, you can only expect a subpar performance from Percona.
• Configuration Backup: YOu need to create a backup of your current MySQL or MariaDB configuration. Thus file will be located at /etc/my.cnf on CentOS systems and at /etc/mysql/my.cnf on Debian as well as Ubuntu systems. Taking a backup of the configuration is crucial according to our Support Engineers, especially if you are replacing MariaDB with Percona.
• Data Backup: Make a backup of all your current data. Although all the data files will remain in place after the replacement of MySQL or MariaDB, it is ideal to have a backup on hand.

How to Replace MySQL with Percona Server on CentOS

Replacing your current version of MySQL with a compatible version of Percona server comprises of five steps:

1. Checking MySQL versions
2. Removing MySQL
3. Installing Percona Server on CentOS
4. Configuration of Percona Server
5. Checking the installation

Our Support Team recently replaced MySQL with Percona Server on CentOS for a client. Let’s go through each step in detail to find out what we have in store for us.

Step 1: Checking MySQL Versions

It is important to note that Percona Server versions are compatible only with their equivalent MySQL versions. This is why you need to check the MySQL version before going ahead with the installation. A mismatch between the versions will lead to table corruption or even stop the server from starting up.

Hence, run the following command to connect to MySQL  via the current root password:

mysql -u root -p

After that, you can check the version by running this command:

SHOW VARIABLES LIKE "version";

This will return information about which Percona Server version you require. Our Engineers would like to point out that if you are running a version prior to MySQL 5.5, you will have to upgrade MySQL to version 5.5 or above.

Step 2: Removing MySQL

As you cannot run  MySQL and Percona concurrently on the same data, we have to remove the former. This is why we mentioned taking a backup of your configuration and data as a part of the prerequisites earlier.

First, stop the database server with the following command:

service mysql stop

MySQL server and client packages can be removed from Debian and UIbuntu based servers with this command:

apt-get remove mysql-server mysql-client mysql-common
apt-get autoremove

MariaDB is uninstalled from CentOS systems with the command:

yum remove MariaDB-server MariaDB-client MariaDB-shared

Step 3: Installing Percona Server on CentOS

1. First, install the Percona repository packages to yum by running this command for 64-bit CentOS:

   yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

   For 32-bit CentOS:

   yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
2. After this, we will install the compatible Percona version. Use the version number we identified in Step 1 to identify the compatible Percona server package. For instance, if you are installing Percona 5.6, you have to run the following command:

   yum install Percona-Server-client-56 Percona-Server-server-56

   Then, you will be asked to accept the package signing key.
3. Then, we will start the server with the following command: service mysql start

In case you come across an error related to the PID file, it indicates that the server has failed to start. In this instance, we recommend checking the prerequisites and ensuring that there is enough memory. You can also check /var/log/mysqld.log file to narrow down the cause for the error.

Our Support Engineers would like to point out that you need to set up a new password for the root user if you are attempting a fresh installation of the Percona server.

Step 4: Configuration of Percona Server

If you are replacing your MySQL with a compatible version of Percona Server, all you have to do is copy the configuration backup file you had made as a part of the prerequisites.

However, if you installed your Percona server on a free Droplet, it is time to add a configuration file. In order to do that, we have to stop the Percona server since the PID file location will change. The configuration file will be located at /etc/my.cnf for CentOS.

Furthermore, you will have to use Percona Configuration Wizard when dealing with larger droplets. This enables you to create a more suitable base for the configuration file.

After saving the configuration file, restart the Percona Server with the command:

service mysql restart

If the server refuses to start, don’t worry, we have your back. Try lowering innodb-buffer-pool-size and repeat the command.

Step 5: Checking the installation

Once the Percona server is up and running, it is time to check whether everything is working as it should be with a few final checks.

First, let’s connect to the database via the MySQL client:

mysql -u root -p

Then enter the root password to get the following output:

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.5.38-35.2 Percona Server (GPL), Release 35.2, Revision 674

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

You will notice above that the server version now specifies the Percona Server. Furthermore, you can check which version is running with the command:

SHOW VARIABLES LIKE "version%";

This will lead to similar output as seen below:

+-------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------+
| version | 5.5.38-35.2 |
| version_comment | Percona Server (GPL), Release 35.2, Revision 674 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
+-------------------------+--------------------------------------------------+
4 rows in set (0.00 sec)

After that, let’s take a look at the benefits offered by XtraDB with this command:

SHOW STORAGE ENGINES\G

This will result in the following output:

...

*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES

...

9 rows in set (0.00 sec)

As you can see, the comment field shows us that the XTRaDB engine is loaded for the InnoDB based tables. Furthermore, you can run checks to ensure that the tables and databases are read correctly by the new server.

By now, we can be certain that we have successfully replaced MySQL with Percona Server on CentOS. If you run into trouble with any of these steps, let us know in the comments and we will try to help you out.

Looking for experts? We are here 24/7 to help you out. 

Conclusion

To conclude, we went through the in-depth steps involved in replacing MySQL with Percona Server on CentOS under the watchful eyes of the Support Team at Bobcares.