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.