Tutorial

How To Install a Fresh Percona Server or Replace MySQL

Published on September 18, 2014
How To Install a Fresh Percona Server or Replace MySQL

Introduction

Percona Server is a drop-in replacement fork of the MySQL project. Percona aims to provide better performance, consistency, and scalability on all hardware. This tutorial will guide you through replacing a current MySQL or MariaDB installation with the latest Percona Server version, or installing Percona Server from scratch on a new Droplet.

Benefits

Percona Server has a number of benefits over a basic MySQL installation:

  • XtraDB: One of the key benefits of switching to Percona Server is XtraDB, a backwards compatible fork of the InnoDB engine with great improvements in performance and efficiency, allowing you to get better query throughput from your current hardware. As it is built on top of InnoDB, your current InnoDB tables will be loaded transparently through XtraDB without any migration processes.

  • Stability and Consistency: Percona Server performs more uniformly under load, meaning your applications will be less liable to intermittent downtime or slowdowns.

  • Metrics: Percona Server comes with a number of additional performance metrics built in, allowing you to discover exactly which users, tables, indexes, or queries are slowing you down. Getting great performance from your server becomes more scientific and less dependent on guesswork.

  • PAM Authentication: Usually a feature reserved for MySQL Enterprise Edition, with Percona Server you can tie in your authentication scheme to your database access.

  • Compatibility: As Percona server is a drop-in replacement for MySQL, you also get all of the usual benefits of MySQL’s widespread popularity and large community of users. This means any application designed for MySQL can safely use Percona Server without any changes.

Prerequisites

  • Debian, Ubuntu, or CentOS cloud server: Other distributions are not currently supported. Only CentOS versions 5 & 6 are supported; CentOS 7 is not supported at the time of writing.

  • Either a fresh Droplet or an up-to-date MySQL/MariaDB installation: Percona Server can be installed either from scratch on a new Droplet or as a replacement for a current MySQL/MariaDB installation. Depending on which of these situations you have, some steps in this article may be relevant to only one case and will be marked with (New Only) or (Replacement Only). Any unmarked sections or paragraphs should be used for both cases.

  • Root access: All commands within this article should be executed as root.

  • Memory: For a default installation you will need at least a 1GB Droplet, otherwise you may get installation failures due to insufficient memory for buffer pool allocation. If you have a 512MB Droplet with swap space allocated, you may also get a successful installation, albeit with subpar performance.

  • Data Backup (Replacement Only): Before making any changes to your database server setup, ensure you have a backup of all of your current data. This tutorial will leave all of the data files in place, removing only the MySQL binary and associated tools – but it is always ideal to have a backup in the event something goes awry. We have a number of articles here on DigitalOcean that cover backing up your database files.

  • Configuration Backup (Replacement Only): Likewise, it is recommended that you make a copy of your current MySQL/MariaDB configuration if you are replacing a current installation; this file can be found at /etc/mysql/my.cnf on Debian/Ubuntu systems and /etc/my.cnf on CentOS systems. On CentOS, the MariaDB package will remove the configuration file when uninstalled, so this step is especially important on these systems.

Step One — Checking Versions (Replacement Only)

Percona Server versions are drop-in compatible with their equivalent MySQL versions only. i.e. MySQL 5.6 should be replaced with Percona Server 5.6 only. Attempting to use mismatched versions may lead to table corruption or prevent the server from starting.

To check which version you are currently running, first connect to MySQL with your current root password:

mysql -u root -p

Then find the current installed version:

SHOW VARIABLES LIKE "version";

This should identify if you need to install Percona Server 5.5 or 5.6. The one edge case is if you are running MariaDB 10.0, which should be replaced with Percona Server 5.6. If you are running a version of MySQL older than 5.5, you should first upgrade MySQL to 5.5 or greater before continuing.

Step Two — Removing MySQL (Replacement Only)

Before we install Percona server, we will need to remove any MySQL or MariaDB packages that are currently installed, as you should not attempt to run both concurrently on the same data.

You should have a backup of your data and your configuration files before proceeding.

Before uninstalling MySQL, it is advised to stop the database server to prevent data corruption in the event the process isn’t stopped safely during the package removal:

service mysql stop

For Debian and Ubuntu based servers, the MySQL server and client packages need to be removed:

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

For CentOS systems the default database is now MariaDB, which can be uninstalled as follows:

yum remove MariaDB-server MariaDB-client MariaDB-shared

For other variations, please consult your documentation for the uninstall procedure.

Step Three — Installing Percona Server

Percona Server is likely not in your Linux distribution’s default repositories, as Percona manage their own repos to ensure updates are pushed to users as quickly as possible. Therefore, we will need to manually add the Percona APT or yum repositories before installation. Follow the instructions below for your server’s OS.

Debian and Ubuntu (Apt)

The Debian and Ubuntu packages released by Percona are signed, meaning APT needs to be informed of the new signing key:

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Before we take the next step, make sure you know the distribution you are currently using. For Debian, this will be one of:

  • squeeze
  • wheezy

Likewise, for Ubuntu the supported distributions are:

  • lucid
  • precise
  • saucy
  • trusty

If you’re uncertain which distribution version you are using, you can execute the following command:

lsb_release -c

Once you are sure which distribution you are running, we can add the new Percona repositories by appending the following lines to the /etc/apt/sources.list file:

nano /etc/apt/sources.list

Add these lines at the bottom of the file, making sure to replace DIST with your distribution name (that is, you would replace DIST with wheezy or trusty, etc.):

deb http://repo.percona.com/apt DIST main
deb-src http://repo.percona.com/apt DIST main

Once you have saved the sources file, the Percona packages should next be pinned to ensure that the packages from Percona will always be prioritized over any packages from your distribution’s default repositories. To do this, we first create a new preference file for APT:

touch /etc/apt/preferences.d/00percona.pref

Now open this file at /etc/apt/preferences.d/00percona.pref with your chosen text editor (Vim, nano, etc.), add the following lines, and save:

Package: *
Pin: release o=Percona Development Team
Pin-Priority: 1001

Finally, once the sources are added and pinned, the package list can be updated and we can install the Percona Server package.

(New Only) For a fresh Droplet, it is advised you install the percona-server-server virtual package, which will install the version of Percona Server recommended by the Percona team:

apt-get update
apt-get install percona-server-server

(Replacement Only) Refer to the MySQL or MariaDB version you located earlier. For replacing version 5.5 use the percona-server-server-5.5 package and percona-server-server-5.6 for 5.6. MariaDB 10.0 should be replaced with Percona Server 5.6.

apt-get update
apt-get install percona-server-server-5.6

If this command completes without errors, Percona Server will be installed and successfully running. However, if you get errors during installation, ensure that you have sufficient free memory, as per the prerequisites section above. More information about any startup errors may be found in Percona Server’s log file at /var/log/mysqld.log.

(New Only) When installing on a fresh system, you may be asked during the installation process to set a root database user password. It is also recommended in this situation to run mysql_secure_installation to ensure no obvious security issues remain:

/usr/bin/mysql_secure_installation

CentOS (Yum)

Only CentOS versions 5 and 6 are currently supported by Percona. At the time of writing, CentOS 7 is not supported.

The first step for CentOS systems is the installation of the Percona repository packages to yum.

For 64-bit CentOS systems:

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

For 32-bit CentOS systems:

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

Once this is complete, we can then install the correct version of the Percona Server package.

(New Only) When installing from scratch, it is recommended you use the Percona Server 5.6 package:

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

(Replacement Only) When replacing a previous installation, use the version number found in the version check section above to choose the correct corresponding package of Percona-Server-server-55 or Percona-Server-server-56. MariaDB 10.0 should be replaced with Percona Server 5.6.

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

You will be asked to accept the packages and then accept the package signing key –agree to both of these. After a short while the installation should complete without any errors. If you receive transaction errors during the installation, ensure that you have completely removed any MySQL/MariaDB packages before retrying.

With the package now installed, the final installation step is to start the server:

service mysql start

If you receive an error regarding the PID file, the server is failing to start. As mentioned in the prerequisites, this often occurs on low RAM servers where the memory limit prevents the XtraDB buffer pool allocation. If this is not the case, check /var/log/mysqld.log for any further error messages, making sure to check that you have properly matched versions between MariaDB and Percona Server.

Note: If you upgrade from MariaDB and receive an error like this:

Can't read dir of '/etc/my.cnf.d' (Errcode: 2 - No such file or directory)
Fatal error in defaults handling. Program aborted
Starting MySQL (Percona Server). ERROR! The server quit without updating PID file (/var/lib/mysql/percona-centos.pid).

You should be able to create the appropriate directory with the command mkdir /etc/my.cnf.d. Then try to start the server.

(New Only) When installing on a fresh system, your Percona Server root user will not have a password assigned and hence your databases will not be secure. It is therefore highly recommended that you use mysql_secure_installation to set up a new password and other security options. When asked to enter the current root password, just press Enter.

/usr/bin/mysql_secure_installation

Step Four — Configuration

If you replaced an existing MySQL installation, you should have made a copy of your configuration file that can now be copied back. You can probably skip this section, although you may find the example settings useful.

However, if you are installing Percona Server on a fresh Droplet, you will need to add a configuration file, as Percona Server is currently running on default values that may not be optimal for your Droplet. Before we update the configuration for the first time, it is advised to stop Percona Server, as the PID file location may change. For future configuration changes, a simple restart should suffice after changes are made.

service mysql stop

Below is a sample configuration file for a 1GB droplet, with a relatively small buffer pool for compatibility. On Debian/Ubuntu this file should be written to /etc/mysql/my.cnf. On CentOS, this file should be written to /etc/my.cnf. The file contains in-line comments to explain what the different sections are doing.

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
[mysql]

# CLIENT #
# Configure default options for clients
port                           = 3306

[mysqld]

# GENERAL #
# Choose user for execution, default storage engine and location of the PID file
user                           = mysql
default-storage-engine         = InnoDB
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
# Setup MyISAM options with a minimal config, as InnoDB is our default engine
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
# Enforce limits and safety checks
max-allowed-packet             = 16M
max-connect-errors             = 1000000
innodb                         = FORCE

# DATA STORAGE #
# Select location for database files
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
# Enable and setup the binary log
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
# Configure reasonable default limits throughout Percona Server
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
# Setup InnoDB/XtraDB engine a 300MB buffer pool and 32MB log file size
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 300M

# LOGGING #
# Setup log file locations for error log and slow log
# Slow log may be disabled on production setups to prevent extra IO
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

For larger Droplets, or as a starting point for your customized configuration file, you can use the Percona Configuration Wizard to create a suitable base for your configuration file.

Once you have saved the file, you can restart Percona Server:

service mysql restart

If the server fails to start with this config, try reducing innodb-buffer-pool-size to a smaller value and repeating the above command.

Step Five — Checking Your Installation

Now that we have Percona Server installed and running, we can ensure that everything has gone as planned by running a few final checks. First, connect to the database using the mysql client, logging in with your database root user password:

mysql -u root -p
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> 

Immediately we can see that the server version in the connection text is now specifying Percona Server. By using the SHOW VARIABLES command, we can dig into further details about the specific version that has been installed:

SHOW VARIABLES LIKE "version%";
+-------------------------+--------------------------------------------------+
| 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)

The precise values and versions may vary in your installation, but the key point is that we are now have Percona Server running rather than MySQL.

Next, we can check that we are taking advantage of XtraDB for any InnoDB based tables:

SHOW STORAGE ENGINES\G

The result will show this block among many others:


...

*************************** 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)

The comment field within the response shows that the XtraDB engine has been loaded as the engine for InnoDB based tables. As a final check, it is advised that you ensure all of your databases and tables are being read properly in the new server.

If all of these checks were passed, you now have Percona Server running successfully. However, if any of these checks were not successful, please ensure you have properly completed all prior steps of this tutorial, paying particular attention to matching MySQL version numbers to Percona Server version numbers.

Next Steps

As Percona is drop-in compatible with MySQL, all of the DigitalOcean tutorials covering MySQL can safely be used with Percona Server. When connecting to the database, your applications will still function identically, although hopefully now with the performance boosts from both Percona Server and XtraDB.

MySQL commands will work exactly as before.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors
Default avatar
lemon

author



Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

In case of 5.6 version, there might be a bug that involves performance_schema. which leads to a bigger RAM usage values, and also prevents percona-server from starting / installing on droplets with less than 1GB RAM.

There are 3 options in fixing this:

  1. Turn off performance_schema by adding performance_schema = off to my.cnf file.

  2. Add a swap file:

  3. Bigger droplet :D .

More info about this: http://yoshinorimatsunobu.blogspot.ro/2013/08/another-reason-to-disable-performance.html

http://www.percona.com/forums/questions-discussions/mysql-and-percona-server/percona-server-5-6/12015-percona-5-6-14-56-very-high-memory-usage

Hi all…

installation for ubuntu 16.04, please add sign key with command below :

**apt-key adv --keyserver keys.gnupg.net --recv-keys 8507EFA5 ** next steps,

deb http://repo.percona.com/apt xenial main deb-src http://repo.percona.com/apt xenial main

Any plans to update this tut for Ubuntu 16.04 (mysql 5.7) ? Pleeease?

If you can’t connect to the .sock file after doing this upgrade on Ubuntu, it’s because apparmor is preventing it from being created and the Percona package replaces the default configuration file that allows it. I copied the following into /etc/apparmor.d/usr.sbin.mysqld from another server and it worked:

/usr/sbin/mysqld { #include <abstractions/base> #include <abstractions/nameservice> #include <abstractions/user-tmp> #include <abstractions/mysql> #include <abstractions/winbind>

capability dac_override, capability sys_resource, capability setgid, capability setuid,

network tcp,

/etc/hosts.allow r, /etc/hosts.deny r,

/etc/mysql/.pem r, /etc/mysql/conf.d/ r, /etc/mysql/conf.d/ r, /etc/mysql/.cnf r, /usr/lib/mysql/plugin/ r, /usr/lib/mysql/plugin/.so* mr, /usr/sbin/mysqld mr, /usr/share/mysql/** r, /var/log/mysql.log rw, /var/log/mysql.err rw, /var/lib/mysql/ r, /var/lib/mysql/** rwk, /var/log/mysql/ r, /var/log/mysql/* rw, /var/run/mysqld/mysqld.pid rw, /var/run/mysqld/mysqld.sock w, /run/mysqld/mysqld.pid rw, /run/mysqld/mysqld.sock w,

/sys/devices/system/cpu/ r,

Site-specific additions and overrides. See local/README for details.

#include <local/usr.sbin.mysqld> }

…will this work for Ubuntu 16.04?

Thank you for the tutorial. I was using MySql 5.5 on an ubuntu 14.04 server. After following all the steps as mentioned, my website gives a 500 error. Any ideas what might be going wrong?

Be careful with “apt-get autoremove” in Step Two. This removed postfix-mysql and dovecot-mysql, which broke my mail server. I had to re-install them after the Percona installation.

Otherwise, everything worked pretty well. I haven’t done any serious benchmarking yet, but things do seem snappier.

Thanks for the guide!

anyone for MariaDB?

I was getting the error “Your PHP installation appears to be missing the MySQL extension which is required by WordPress”.

Turned out I needed to run apt-get install php5-mysql afterwards.

Leaving this here in case someone else runs into that error.

Props to Sathishkumar in the comments.

The my.conf file breaks for me every time. If I replace it with the original file, mysql restarts fine. I’ve also tried the Percona wizard. What is different is the file paths. Replacing them with what was there also isn’t enough.

Through making some changes, I can get to a different error with an attempt to login but every solution on StackExchange says to reinstall mysql, which obviously isn’t what we want to do.

root@Cantech:~# mysql -u root ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) root@Cantech:~# mysql -u root ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)

The first error is if there is no .sock file. The 2nd error is when I restore the .sock file from backup.

Any ideas what’s different from then to now? I have a 1GB droplet, fresh. This is the first thing I’m trying to do after using ServerPilot to connect.

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel