In this series I am going to cover Percona Server, XtraDB Cluster and XtraBackup Toolkit.
Percona Server is a dropin replacement for MySQL server with extra features and stability. It runs more consistently while providing extra engines and improved scalability.
Key features that are available in Percona Server:
XtraDB - It provides better query performance with just switching from InnoDB. Built on top of InnoDB, provides backwards compatibility and theres no migration process.
PAM Authentication - This can be very useful for enterprise grade solutions or integrating with other systems.
Dropin compatibility - You can upgrade from MySQL without any changes to your schema or code.
Operation Metrics - Percons Server provides a number of builtin metrics that will allow you to improve your schema, giving you better understanding of how your users, tables, queries and indexes are performing.
While Percona provide Sources and Binary distributions of all their software, its best to use repositories instead. This method will resolve all software dependencies trough the Operating System package manager (
apt for Debian and Ubuntu,
yum for CentOS) and provide easy way to apply updates and security patches.
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
apt-get install percona-server-server-5.7
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" -p mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" -p mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" -p
Provide the root password you've typed during the install process at the previous step.
To avoid the software from being upgrade from distribution repositories we will pin the package, to do so we need to create a new file /etc/apt/preferences.d/00percona.pref and save the following content in it:
Package: * Pin: release o=Percona Development Team Pin-Priority: 1001
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum list | grep Percona-Server-57
You should see listing that indicates the available version from repository
percona-release-x86 depending on the architecture you are installing on.
yum install Percona-Server-server-57
Note that the default password will be generated automatically, you can check yours by running:
grep "temporary password" /var/log/mysqld.log
The package manager will ask you to confirm the new repository key and to accept the packages to be installed.
For Ubuntu and Debian distributions the default data directory is
/var/lib/mysql. The configuration is stored in
CentOS has the same data directory
/var/lib/mysql by default and the configuration file is in
The server comes with the usual init.d scripts to allow you to start, restart or stop it. By default, on Debian and Ubuntu, it should start automatically once installed, unless it encountered an error. You can still start it by running:
service mysql start
To confirm its running you can use:
service mysql status
You can stop the server with:
service mysql stop
And restart it by running:
service mysql restart
Next we will connect the server, create new database, a simple schema and run a couple of queries.
!(https://static.vpsserver.com/vpsserver_com_frontend/tutorials/lR41Xpn.png) 2. Create new database: ``` CREATE DATABASE testdb; ``` !(https://static.vpsserver.com/vpsserver_com_frontend/tutorials/wE3wn0e.png) 3. Let's create a table: ``` use testdb; ``` !(https://static.vpsserver.com/vpsserver_com_frontend/tutorials/iR2KCiD.png) ``` CREATE TABLE Users ( Id int NOT NULL, Name varchar(255), Email varchar(255), Note varchar(255), PRIMARY KEY (Id) ); ``` !(https://static.vpsserver.com/vpsserver_com_frontend/tutorials/bhOk9a4.png) 4. Inserting and selecting from the table: ``` INSERT INTO Users (Id, Name, Email, Note) VALUES (1, 'John Doe', 'firstname.lastname@example.org', 'Test User'); ``` !(https://static.vpsserver.com/vpsserver_com_frontend/tutorials/5VDtNrv.png) And to retrieve the records, we need to execute: ``` SELECT * FROM Users; ``` !(https://static.vpsserver.com/vpsserver_com_frontend/tutorials/RFU2BaD.png) 5. Once we are done playing, lets drop that `testdb`. ``` DROP DATABASE testdb; ``` !(https://static.vpsserver.com/vpsserver_com_frontend/tutorials/u9ObHNu.png) You can exit mysql prompt with CTRL+D or commando `quit;` ## Create new user, database and grant permissions Let's create new database, a user and grant that user permissions to that database. For this example we will create user `jeff` and his database will be called `blog`. 1. Connect mysql server. Provide the root password you've entered during the installation:
CREATE DATABASE blog;
CREATE USER 'jeff'@'localhost' IDENTIFIED BY 'mysecretone';
jeffall permissions on that database.
GRANT ALL PRIVILEGES ON blog.* TO 'jeff'@'localhost';
You can exit mysql prompt with CTRL+D or commando
mysql -u jeff -pmysecretone
Once authenticated as
jeff we will switch to our new database:
In under 15 minutes we went trough the installation process of Percona Server 5.7 and created new user for use in your application. This can give immediate improvement over using MySQL Server with very little effort.