How to replace MySQL with Percona in 11.36

Important: This setup is considered to be unsupported by the cPanel/WHM team. Please note that once cPanel/WHM no longer maintains MySQL on your system it is on the Systems Administrator to manage and maintain any updates and maintenance on the database software.  We recommend only experienced systems administrators perform the above and we are not responsible for any possible data loss.

Below you will find some steps on how to affectively stop cPanel/WHM from maintaining MySQL and begin utilizing Percona for any and all database activity on your server. You can find the Percona website at www.percona.com.The guide found below is based around utilizing the new RPM tools we provide in 11.36.
You will find that the steps provided here are similar to that of my last weeks blog Replacing MySQL with MariaDB in 11.36.
Important Links

Percona versus MySQL Benchmarks
Percona versus MySQL Features

Documentation on the RPM system provided by cPanel & WHM

Step 1: Backup existing MySQL data

Make sure to save all existing data just in case there are any issues.

cp -Rf /var/lib/mysql /var/lib/mysql-old
mv /etc/my.cnf /etc/my.cnf-old

 

Step 2: Disable the targets so cPanel no longer handles MySQL updates

The following will mark the versions of MySQL we distribute as uninstalled so they are no longer maintained by cPanel/WHM

/scripts/update_local_rpm_versions --edit target_settings.MySQL50 uninstalled
/scripts/update_local_rpm_versions --edit target_settings.MySQL51 uninstalled
/scripts/update_local_rpm_versions --edit target_settings.MySQL55 uninstalled

 

Step 3: Remove existing MySQL RPM’s so theres a clean slate for MariaDB

Important: The below command will uninstall the MySQL RPM’s!
/scripts/check_cpanel_rpms --fix --targets=MySQL50,MySQL51,MySQL55
[20130218.235953]    
[20130218.235953]   The following RPMs are unneeded on your system and should be uninstalled:
[20130218.235953]   MySQL51-devel.5.1.68-1.cp1136
[20130218.235953]   MySQL51-test.5.1.68-1.cp1136
[20130218.235953]   MySQL51-shared.5.1.68-1.cp1136
[20130218.235953]   MySQL51-client.5.1.68-1.cp1136
[20130218.235953]   MySQL51-server.5.1.68-1.cp1136
[20130218.235953]   Removing 0 broken rpms: 
[20130218.235953]   rpm: no packages given for erase
[20130218.235953]   No new RPMS needed for install
[20130218.235953]   Uninstalling unneeded rpms: MySQL51-devel MySQL51-test MySQL51-shared MySQL51-client MySQL51-server
[20130219.000004]   Shutting down MySQL........ SUCCESS!

 

Step 4: Create a yum repository for MariaDB

vi /etc/yum.repos.d/percona.repo

place the following inside of it (it will recognize your OS/arch);

[percona]
name = CentOS $releasever - Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = http://www.percona.com/redir/downloads/percona-release/RPM-GPG-KEY-percona
gpgcheck = 1

 

Step 5: Remove php from the /etc/yum.conf file then run the following commands

yum install Percona-Server-client-55 Percona-Server-server-55 Percona-Server-devel-55
/etc/init.d/mysql start
mysql_upgrade
/etc/init.d/mysql restart

 

Step 5: Add php back to the /etc/yum.conf file to ensure future php updates don’t get clobbered

 

Final Step: Rebuild easyapache/php to ensure modules are intact/working

/scripts/easyapache --build

 

This entry was posted in Tips & Tricks and tagged , , , , , . Bookmark the permalink.
  • Abdullah salahaldin

    Percona-Server-client-55.x86_64 0:5.5.29-rel30.0.451.rhel5 Percona-Server-server-55.x86_64 0:5.5.29-rel30.0.451.rhel5

    Dependency Installed:
    Percona-Server-shared-55.x86_64 0:5.5.29-rel30.0.451.rhel5

    Complete!
    root@server [~]#
    root@server [~]# mysql_upgrade
    Looking for ‘mysql’ as: mysql
    Looking for ‘mysqlcheck’ as: mysqlcheck
    Running ‘mysqlcheck with default connection arguments
    mysqlcheck: Got error: 2002: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) when trying to connect
    FATAL ERROR: Upgrade failed

  • Abdullah salahaldin

    Hello there if you get this errors after upgrade

    Complete! root@server [~]# root@server [~]# mysql_upgrade Looking for ‘mysql’ as: mysql Looking for ‘mysqlcheck’ as: mysqlcheck Running ‘mysqlcheck with default connection arguments mysqlcheck: Got error: 2002: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) when trying to connect FATAL ERROR: Upgrade failed

    fix :)

    35 killall -9 mysqld
    36 service mysql stop
    37 rm -f /var/lib/mysql/mysql.sock
    39 service mysql start
    40 mysql_upgrade

    Arabvps.net
    Abdullah Salahaldin

  • http://www.webhost.uk.net Webhost UK

    Thank you for your help Abdullah .. while testing it on one of our server we did get its error

    Regards
    Max
    Webhost UK

  • Tyler Beckett

    Is this going to affect client (end-user) usage of MySQL at all? Will they notice any sort of change?

    • Tyler Beckett

      Hold on, is this required or is WHM/cPanel going to continue supporting MySQL and this is an optional thing?

      • Jesse

        Please read the “Important” portion at the top of the article, it states that it’s a replacement for MySQL and is *optional*. MySQL is provided by default by cPanel/WHM and will not be changed. The guide is for users who wish to change their SQL server and/or learn how the new RPM system introduced in 11.36 works.

    • Jesse

      I apologize for missing this one – they should not notice much of a difference as it’s a drop-in replacement. They may see the version difference in their cPanel interface or while they are logged into MySQL itself.

  • wshyang

    I am not sure if these instructions really work, easyapache crooks for me after following the steps.

    In particular, this Perl module is broken:

    /var/cpanel/perl/easy/Cpanel/Easy/Utils/PHP.pm

    It is no longer able to parse the MySQL version correctly once you’ve done the `update_local_rpm_versions` call.

    • Jesse

      Could you provide more information on the error your getting in easyapache?

  • Felipe

    Removing PHP from /etc/yum.conf exclude line won’t cause issues later on if ‘yum update’ is performed or EasyApache?

    • Felipe

      Were these steps actually tested????

      EasyApache errors. It can’t pick up MySQL version:

      The last time Cpanel::Easy::PHP5::Mysql was run; step returned 0.

      That just caused Cpanel::Easy::PHP5::Mysql to be skipped.

      The message it gave was:
      Could not compile a simple mysql.h program: -compile- -output-You might want to take that into consideration when choosing options and troubleshooting.

      The last time Cpanel::Easy::PHP5::MysqlOfSystem was run; step returned 0.

      That just caused Cpanel::Easy::PHP5::MysqlOfSystem to be skipped.

      The message it gave was:
      Could not compile a simple mysql.h program: -compile- -output-You might want to take that into consideration when choosing options and troubleshooting.

      The last time Cpanel::Easy::PHP5::Mysqli was run; step returned 0.

      That just caused Cpanel::Easy::PHP5::Mysqli to be skipped.

      The message it gave was:
      Could not compile a simple mysql.h program: -compile- -output-You might want to take that into consideration when choosing options and troubleshooting.

      • Felipe

        If you get the above errors and mysql_config is not found you need to install the devel package as well.

        yum install Percona-Server-devel-55

        It solves the above

        • Jesse

          Thanks for pointing this out, it’s been added to the blog entry.

        • Jesse

          Thanks for pointing this out, it’s been added to the blog entry.

    • Jesse

      @disqus_VhvParcGLD:disqus sorry I missed this question – i’ve added an extra step which includes adding the php back to the /etc/yum.conf!

  • James Oakley

    Jesse – thanks for that post. Just a quick question on what the next update procedures would be. The yum package names include the 55 version label in them, so presumably an upgrade to 5.6 (once cPanel supports it) would need yum install Percona-Server-Client-56, etc. But wouldn’t the 5.5 need removing first?

    • Jesse

      @b799d4e713f036ded3ffc37755d4b41f:disqus actually since you’re using the percona mirrors, yum should handle any updates that they push out automatically. You shouldn’t need to remove anything.

  • http://www.cloudforth.co.uk/ SEO Company

    Thanks for the advice Felipe I will install and see Percona to solve my problem.

  • Anand Gupta

    Thanks for the guide, few questions:

    1. When percona updates via yum, do we need to re-compile php+apache again each time ?

    2. I tried to install Munin plugin after installing percona, and got the below error:

    [20131215.035602] Warning: Unable to set access on database tables in “munin_innodb” for MySQL InnoDB plugin usage.
    [20131215.035602]
    [20131215.035602] Encountered problem while setting MySQL InnoDB access. No further InnoDB access was performed.
    [20131215.035602] Suggestion:
    [20131215.035602] Verify input data, enable InnoDB, check config file ‘/etc/my.cnf’, restart MySQL and then re-run /usr/local/cpanel/bin/build_munin_conf
    [20131215.035602] or InnoDB chart may not function as expected.

    Seems like its facing problems working with the percona db. Any ideas ?

  • Anand Gupta

    Until recently this was working on a server for me, however with the latest update, it seems to be broken. Now when i execute a php script, it gives me the below error.

    php: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

    I have recompiled apache using easyapache already twice, but it doesn’t seem to fix the issue. Any ideas ?

    Thanks

    • Anand Gupta

      As per thread -> http://forums.cpanel.net/f354/issue-percona-396262.html, the following needs to be done otherwise you will keep on getting the mysql error as mentioned above.

      ln -s /usr/lib64/libperconaserverclient.so /usr/lib64/libmysqlclient.so

      Maybe someone can edit the article ?

      Thanks

    • Garani

      I have a ticket opened about this issue.

      The problem is that the latest Percona 5.6 gets shipped without the libmysqlclient.so libraries (http://www.mysqlperformanceblog.com/2014/03/10/percona-server-5-6-16-64-0-now-available/)

      So you will have to provide your own libmysqlclient libraries. To do so you can easily install cPanel’s RPM via console:

      rpm -Uvh http://httpupdate.cpanel.net/RPM/11.42/centos/6/x86_64/MySQL56-server-5.6.16-3.cp1142.x86_64.rpm

      Now you have to “hack” cPanel a bit until a better solution is found, you will have to turn off upcp. Log in as root in console, run:

      crontab -e

      Locate the ucpc line and put a hash (#) in front of it to comment it out. Exit and save the file.

      This means that you will NOT update cPanel anymore. If you were to run ucpc it would uninstall the MySQL5.6 shared package every time. This is why I am trying to understand with cPanel how we can keep the package every time upcp is run.

      • Garani

        Got a solution up and running, compatible with the current state of the art. I’ll write it tomorrow.