Detailed steps for installing the Percona Server on CentOS 7

In this tutorial, we will learn how to install and configure the Percona Server in Centos 7. The Percona Server is an open-source MySQL and MariaDB alternative, especially the performance improvement of the InnoDB database engine, let more people choose the Percona service.

The following describes how to install the Percona Server on CentOS 7 and how to back up the current data, configure the steps, and recover the backup.

1. What is Percona and why is it used?

Percona is an open-source alternative to MySQL and MariaDB. It is a branch of MySQL. Many improvements and unique features make it more reliable, more powerful, and faster than MYSQL, it is fully compatible with MYSQL, and you can even use replication between Oracle MySQL and Percona.
Unique features in Percona

Adaptive hash search for partitions
Quick verification algorithm
Buffer pool pre-loading
Support for FlashCache

Special features in MySQL Enterprise Edition and Percona

Import tables from different servers
PAM authentication
Audit log
Thread pool

Now, you are very excited to see these good things integrated, we will show you how to install and do basic configuration for Percona Server.

2. Back up your database

Next, use the SQL command on the command line to create a mydatabases. SQL file to recreate or restore the salesdb and employeedb databases. Replace the database name according to your settings. Skip this step if MySQL is not installed:

Mysqldump-u root-p -- databases employeedb salesdb> mydatabases. SQL

Copy the current configuration file. Skip this step if you have not installed MYSQL:

Cp my. cnf my. cnf. bkp

3. Delete the previous SQL Server

Stop MYSQL/MariaDB if they are still running:

Systemctl stop mysql. service

Uninstall MariaDB and MYSQL:

Yum remove MariaDB-server MariaDB-client MariaDB-shared mysql-server

Move the rename to the MariaDB file in/var/lib/mysql. This is safer and faster than just removing, just like 2-level instant backup. :)

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

4. Use a binary package to install Percona

You can choose among the many Percona installation methods. Using Yum or RPM Package in CentOS is usually a better idea, so these are the methods described in this article, download the source file and install it in this article.

Install from the Yum repository:

First, you need to set the Yum library of Percona:

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

Next, install Percona:

Yum install Percona-Server-client-56 Percona-Server-server-56

The preceding command installs the server and client and shared library of Percona, which may require Perl and Perl modules and other dependent requirements, such as DBI: MySQL. If these are not yet installed, you may need to install more dependent packages.

Install with an RPM Package:

We can use the wget command to download all rpm packages:

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 the rpm packages at one time:

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

Note the backslash '\' at the end of the preceding command statement (for the convenience of line feed ). If you install a separate software package, remember to resolve the dependency. Before installing the client, you must first install the shared package. Before installing the server, install the client.

5. Configure the Percona Server

Restore the previous configuration

When we migrate data from MariaDB, you can restore the backup file of my. cnf.

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

Create a new my. cnf file

If you need a new configuration file that suits your needs or you have not backed up the configuration file, you can use the following method to generate a new configuration file in a few simple steps.

The following is the my. cnf file that comes with the Percona-server software package.

# Percona Server template configuration

[Mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# Cache in MySQL. Start at 70% of total RAM for dedicated servers, else 10%.
# Innodb_buffer_pool_size = 128 M
#
# 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 = 128 M
# Sort_buffer_size = 2 M
# Read_rnd_buffer_size = 2 M
Datadir =/var/lib/mysql
Socket =/var/lib/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. pid

After configuring my. cnf as needed, you can start the service:

Systemctl restart mysql. service

If everything goes well, it is ready to execute the SQL command. You can run the following command to check whether it has been started properly:

Mysql-u root-p-e 'show variables like "version_comment "'

If you cannot start it normally, you can go to/var/log/mysql/mysqld. log to find the cause. This file can be found in my. set in log-error of [mysql_safe] of cnf.

Tail/var/log/mysql/mysqld. log

You can also find the file in the format of [host name]. err in the/var/lib/mysql/folder, as shown in the following example:

Tail/var/lib/mysql/centos7.err

If you still haven't found the cause, try strace:

Yum install strace & systemctl stop mysql. service & strace-f mysqld_safe

The command above is quite long and the output result is relatively simple, but you can find the reason why it cannot be started most of the time.

6. Protect your data

Well, your relational database management system is ready to receive SQL queries, but it is not advisable to put your valuable data on servers without minimum security protection, for security purposes, it is best to install mysqlsecureinstall. This tool helps you delete unused default functions and set the root password to restrict access by this user. You only need to execute this command in shell and follow the instructions on the screen.

Mysql_secure_install

7. Restore Backup

If you can restore the database by referring to the previous settings, you only need to use mysqldump once.

Mysqldump-u root-p <mydatabases. SQL

Congratulations, you have just installed Percona on your CentOS and your server is ready for use. You can use it like using MySQL, your server is fully compatible with it.

Summary

To achieve better performance, you need to make a lot of modifications to the configuration file, but here there are some simple options to improve the performance of the machine. When the InnoDB engine is used, set innodbfileper_table to on. It creates an index table for each table in a file, which means that each table has its own index file, it makes the system much stronger and easier to maintain.

You can modify the innodbbufferpool_size option. InnoDB should have enough cache pools to handle your data set. The size should be 70% to 80% of the available memory.

Set innodb-flush-method to O_DIRECT to disable writing to high-speed cache. If RAID is used, this can improve performance because the cache operation has been completed at the underlying layer.

If your data is not critical and you do not need to be fully compatible with the four basic elements of the correct execution of database transactions, you can set innodbflushlogattrx_commit to 2, which can also improve the system performance.