In this article we will learn about the Percona server, an open source mysql,mariadb alternative. InnoDB's database engine makes the Percona server very attractive, and if you need a high performance, high reliability and cost-effective solution, it will be a good choice.
The installation of the Percona server on CentOS 7 is described below, as well as the steps to back up the current data, configuration, and how to restore the backup.
1. What is Percona and why use it
Percona is an open source alternative to the MYSQL,MARIADB database, which is a branch of MySQL, with quite a few improvements and unique features that make it more reliable, more powerful, faster, and fully compatible with MySQL. You can even use replication between Oracle's MySQL and Percona.
Unique features in the Percona
Partition Adaptive Hash Search
Fast Check algorithm
Buffer Pool Pre-loading
Support Flashcache
Features specific to 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 together, and we'll 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 based on your settings, and skip this step if no MySQL is installed:
Mysqldump-u root-p--databases employeedb salesdb > Mydatabases.sql
Copy the current profile and skip it if you don't have MySQL installed:
CP my.cnf MY.CNF.BKP
3. Delete the previous SQL Server
Stop the MYSQL/MARIADB if they are still running:
Systemctl Stop Mysql.service
Uninstall MARIADB and MySQL:
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 much safer and faster than just removing it, which is like a level 2 instant backup. :)
Mv/var/lib/mysql/var/lib/mysql_mariadb
4. Installing Percona using a binary package
You can choose among many Percona installation methods, using Yum or RPM package installation in CentOS is usually a better idea, so these are the methods that are described in this article, and the download source files are compiled and installed in this article and are not described.
Install from the Yum repository:
First, you need to set up the Percona Yum Library:
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 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 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 RPM packages at once:
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 line wrapping). If you install a separate package, remember to resolve the dependency, install the shared package before installing the client, and install the client before installing the server.
5. Configuring the Percona Server
Restore the previous configuration
When we migrate from MARIADB, you can restore your previous my.cnf backup files back.
Cp/etc/my.cnf.bkp/etc/my.cnf
Create a new my.cnf file
If you need a new profile that suits your needs or you don't have a backup profile, you can use the following methods to generate a new profile in a few simple steps.
Here is the my.cnf file that comes with the Percona-server 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 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 is 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 the MY.CNF to your needs, you are ready to start the service:
Systemctl Restart Mysql.service
If all goes well, it's ready to execute the SQL command, and you can check to see if it started properly with the following command:
Mysql-u root-p-E ' SHOW VARIABLES like "version_comment" '
If you are not able to start it normally, you can find the reason in/var/log/mysql/mysqld.log, which can be set in the Log-error of My.cnf [Mysql_safe].
Tail/var/log/mysql/mysqld.log
You can also look under the/var/lib/mysql/folder for a file formatted as [hostname].err, as in the following example:
Tail/var/lib/mysql/centos7.err
If you still don't find out why, you can try Strace:
Yum Install strace && systemctl stop Mysql.service && strace-f-F Mysqld_safe
The commands above are quite long and the results of the output are relatively simple, but most of the time you can find the reason why you can't start.
6. Protect your data
Well, your relational database management system is ready to receive SQL queries, but it's not advisable to put your valuable data on a server without minimal security, and to be more secure, preferably using Mysqlsecureinstall to install, this tool can help you remove unused default features. and set the root password to restrict access using this user. Just execute the command in the shell and refer to the instructions on the screen.
Mysql_secure_install
7. Restoring a backup
If you refer to the previous settings, you can now restore the database by simply using mysqldump once.
Mysqldump-u Root-p < Mydatabases.sql
Congratulations, you have just successfully installed the Percona on your CentOS, your server is ready for use, you can use it like MySQL, and 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 some simple options to improve the performance of the machine. When using the InnoDB engine, innodbfileper_table is set to ON, which creates an index 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 pools to handle your data set, which should be 70% to 80% of the current available memory.
Set Innodb-flush-method to O_direct, turn off write cache, and if you use RAID, this can improve performance because the cache operation has been completed at the bottom.
If your data is not very critical and does not need to be fully compatible with the four basic elements that the database transaction performs correctly, you can set the Innodbflushlogattrx_commit to 2, which can also improve the performance of the system.
Free pick up Brother Lian IT Education Original Linux Operations Engineer video/Detailed Linux tutorials, details of the website customer service: http://www.lampbrother.net/linux/
or hooking up with Q2430675018.
Welcome to the Linux Communication Group 478068715
How to install the Percona server on CentOS 7