Installing Percona Server on CentOS 6 couldn’t be easier using yum and the Percona repository.  We start with a minimal install of CentOS 6. I am using CentOS 6 because I am using RHEL 6 at work and want to stay closer to that environment when I work on testing things at home.

First we need to remove the MySQL libraries that ship with CentOS

[student@percona56 ~]$ sudo yum -y remove mysql-libs

Next import the Percona repository using the following command.

[student@percona56 ~]$ sudo yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Now that we have the repository installed we can verify by running:

[student@percona56 ~]$ sudo yum list | grep percona

This should return a listing of the yum packages in the Percona repository. For this example we will be installing not only the server but the client and some other packages that are helpful to have around when running mysql. We will be installing the 5.6 version of Percona Server as other versions are available at this time in the repository. To install our packages we will run:

[student@percona56 ~]$ sudo yum install Percona-Server-shared-56.x86_64 Percona-Server-client-56.x86_64 Percona-Server-server-56.x86_64 percona-toolkit percona-xtrabackup

Now that we have the binaries laid down we will need to create the data directory and start MySQL. To do that we run:

[student@percona56 ~]$ sudo touch /etc/my.cnf
[student@percona56 ~]$ sudo /usr/bin/mysql_install_db --defaults-file=/etc/my.cnf --force --datadir=/var/lib/mysql --basedir=/usr/ --user=mysql
[student@percona56 ~]$ sudo service mysql start

We now have MySQL running and the first thing I like to do is secure the installation using the built in script provided. We can run the script using the command below. Just hit Enter when it asks for the current root password. We will want to set the root password, remove anonymous users, disallow remote login by root, and remove the test database. We will also want to reload privileges when finished.

[student@percona56 ~]$ /usr/bin/mysql_secure_installation

I also like to install the sys schema. The sys schema is a set of objects that help you analyze data in the Performance Schema. This schema is included by default in 5.7, and is needed by Workbench reports. If you don’t install it now, you can do so through Workbench. I like to go ahead and get it out of the way.

[student@percona56 ~]$ sudo yum -y install git
[student@percona56 ~]$ cd /tmp
[student@percona56 tmp]$ git clone https://github.com/MarkLeith/mysql-sys
[student@percona56 tmp]$ cd mysql-sys
[student@percona56 mysql-sys]$ mysql -u root -p < ./sys_56.sql

But we still can’t use the sys schema because we have not turned on Performance Schema on our instance. In fact we haven’t configured our instance at all. Lets do that now by editing our /etc/my.cnf file. We want our file to look like this when finished.

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
sql-mode                       = STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
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         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1536M     #see https://www.percona.com/blog/2007/11/03/choosing-innodb_buffer_pool_size/ for guidance

# LOGGING #
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

# PERFORMANCE SCHEMA #
performance_schema
performance_schema_instrument = '%=on'
performance_schema_consumer_events_stages_current = ON
performance_schema_consumer_events_stages_history = ON
performance_schema_consumer_events_stages_history_long = ON
performance_schema_consumer_events_statements_current = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_statements_history_long = ON
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
performance_schema_consumer_events_waits_history_long = ON
performance_schema_consumer_global_instrumentation = ON
performance_schema_consumer_thread_instrumentation = ON
performance_schema_consumer_statements_digest = ON

At this point we should be ready to restart MySQL and get started.

sudo service mysql restart

I will use this base image going forward as the starting point for all my MySQL explorations.