How to install the Percona server on CentOS

In this article we will learn about the Percona server, an open-source mysql,mariadb alternative. The InnoDB database engine makes the Percona server very attractive, and if you need high-performance, high-reliability and cost-effective solutions, it will be a good choice.

The installation of the Percona server on CentOS 7, as well as the backup of the current data, the steps to configure, and how to recover the backup are described later in this article.

1. What is Percona, why use it

Percona is an open-source alternative to the MYSQL,MARIADB database, a branch of MySQL, with a considerable number of improvements and unique features that make it more reliable, more powerful and faster than MySQL, and is completely compatible with MySQL, You can even use replication between Oracle's MySQL and Percona.

Features that are unique in Percona

Partitioning Adaptive Hash Search

Fast Checkout algorithm

Buffer Pool Pre-loading

Support Flashcache

Unique features in MySQL Enterprise Edition and Percona

Import a table from a different server

Pam Certification

Audit log

Thread pool

Now, you must be excited to see these good things come together and we will show you how to install and do basic configuration on Percona server.

2. Back up your database

Next, use the SQL command at the command line to create a mydatabases.sql file to rebuild or restore the SalesDB and Employeedb databases, replace the database name according to your settings, and skip this step if MySQL is not installed:

The code is as follows:

Mysqldump-u root-p--databases employeedb salesdb > Mydatabases.sql

Copy the current profile, if you do not install MySQL can also skip:

The code is as follows:

CP my.cnf MY.CNF.BKP

3. Delete the previous SQL Server

Stop mysql/mariadb, if they are still running:

The code is as follows:

Systemctl Stop Mysql.service

Uninstall MARIADB and MySQL:

The code is as follows:

Yum Remove mariadb-server mariadb-client mariadb-shared mysql mysql-server

Move rename the mariadb file that is placed in the/var/lib/mysql. This is more secure and faster than just removing, which is like a level 2 instant backup. :)

The code is as follows:

Mv/var/lib/mysql/var/lib/mysql_mariadb

4. Install Percona using binary package

You can choose among the many Percona installation methods, the use of Yum or RPM package installation in CentOS is usually a better idea, so these are the ways described in this article, download the source files compiled after the installation is not described in this article.

To install from the Yum Warehouse:

First, you need to set up the Percona Yum Library:

The code is as follows:

Yum Install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Next Install Percona:

The code is as follows:

Yum Install percona-server-client-56 percona-server-server-56

The above command installs Percona servers and clients, shared libraries, and may require Perl and Perl modules, as well as other dependent needs such as dbi::mysql. If these are not already installed, you may need to install more dependent packages.

Install with RPM Package:

We can download all the RPM packages using the wget command:

The code is as follows:

Wget-r-L 1-nd-a rpm-r "*devel*,*debuginfo*"

http://www.percona.com/downloads/Percona-Server-5.5/Percona-Server-5.5.42-37.1/binary/redhat/7/x86_64/

Use the RPM tool to install all RPM packages at once:

The code is as follows:

RPM-IVH percona-server-server-55-5.5.42-rel37.1.el7.x86_64.rpm

percona-server-client-55-5.5.42-rel37.1.el7.x86_64.rpm

percona-server-shared-55-5.5.42-rel37.1.el7.x86_64.rpm

Notice the last backslash in the command statement above (just for the sake of wrapping). If you are installing a separate package, remember to resolve the dependencies, install the shared package before installing the client, and install the client before installing the server.

5. Configure the Percona server

Pre-Restore Configuration

When we migrate from MARIADB, you can restore the backup files of the previous my.cnf.

The code is as follows:

Cp/etc/my.cnf.bkp/etc/my.cnf

Create a new my.cnf file

If you need a new profile that fits your needs or you don't have a backup profile, you can use the following methods to generate a new configuration file in a few simple steps.

The following is a my.cnf file with the Percona-server package

The code is as follows:

# Percona Server Template Configuration

[Mysqld]

#

# Remove Leading # and set to the amount's RAM for the most important data

# Cache in MySQL. Start at 70% all RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove Leading # to turn on a very important data integrity option:logging

# Changes to the binary log between backups.

# Log_bin

#

# Remove Leading # To set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast selects.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

After you have configured your MY.CNF, you can start the service:

The code is as follows:

Systemctl Restart Mysql.service

If all goes well, it's ready to execute the SQL command, and you can check if it's starting correctly with the following command:

The code is as follows:

Mysql-u root-p-e ' show VARIABLES like "Version_comment"

If you are not able to start it properly, you can find the reason in/var/log/mysql/mysqld.log, which can be set in the Log-error of My.cnf [Mysql_safe].

The code is as follows:

Tail/var/log/mysql/mysqld.log

You can also look for a file in the/var/lib/mysql/folder that has a format of [host name].err, as in the following example:

The code is as follows:

Tail/var/lib/mysql/centos7.err

If you still don't find out why, you can try Strace:

The code is as follows:

Yum Install strace && systemctl stop Mysql.service && strace-f-F Mysqld_safe

The above command is very long, the output is relatively simple, but most of the time you can find the reason for not to start.

6. Protect your data

Well, your relational database management system is ready to receive SQL queries, but it is not advisable to place your valuable data on a server that does not have the minimum security, and to be more secure and use Mysqlsecureinstall to install, this tool can help you remove unused default features, and set the password for root to restrict access by using this user. You only need to execute the command in the shell and refer to the on-screen instructions.

The code is as follows:

Mysql_secure_install

7. Restore Backup

If you refer to previous settings, now you can restore the database, just use mysqldump once.

The code is as follows:

Mysqldump-u Root-p < Mydatabases.sql

Congratulations, you have just successfully installed the Percona on your CentOS, your server is ready to use, you can use it like MySQL, your server is fully compatible with it.

Summarize

For better performance you need to make a lot of changes to the configuration file, but there are also some simple options to improve the performance of the machine. When using the InnoDB engine, the innodbfileper_table is set to ON, which creates an indexed table for each table in a file, which means that each table has its own index file, which makes the system more powerful and easier to maintain.

You can modify the Innodbbufferpool_size option, InnoDB should have enough cache pool to respond to your dataset, the size should be 70% to 80% of the current available memory.

Set Innodb-flush-method to O_direct, turn off write caching, and if you use RAID, this can improve performance because caching is done at the bottom.

If your data is not very critical and you do not need to be fully compatible with the four basic elements of proper database transaction execution, you can set the Innodbflushlogattrx_commit to 2, which also improves the performance of your system.