Percona: Instalasi mengganti MySQL

Introduction

Percona Server is a drop-in replacement fork of the MySQL project. Percona aims to provide better performance, consistency, and scalability on all hardware. This tutorial will guide you through replacing a current MySQL or MariaDB installation with the latest Percona Server version, or installing Percona Server from scratch on a new Droplet.

Benefits

Percona Server has a number of benefits over a basic MySQL installation:

• XtraDB: One of the key benefits of switching to Percona Server is XtraDB, a backwards compatible fork of the InnoDB engine with great improvements in performance and efficiency, allowing you to get better query throughput from your current hardware. As it is built on top of InnoDB, your current InnoDB tables will be loaded transparently through XtraDB without any migration processes.
• Stability and Consistency: Percona Server performs more uniformly under load, meaning your applications will be less liable to intermittent downtime or slowdowns.
• Metrics: Percona Server comes with a number of additional performance metrics built in, allowing you to discover exactly which users, tables, indexes, or queries are slowing you down. Getting great performance from your server becomes more scientific and less dependent on guesswork.
• PAM Authentication: Usually a feature reserved for MySQL Enterprise Edition, with Percona Server you can tie in your authentication scheme to your database access.
• Compatibility: As Percona server is a drop-in replacement for MySQL, you also get all of the usual benefits of MySQL's widespread popularity and large community of users. This means any application designed for MySQL can safely use Percona Server without any changes.

Prerequisites

• Debian, Ubuntu, or CentOS cloud server: Other distributions are not currently supported. Only CentOS versions 5 & 6 are supported; CentOS 7 is not supported at the time of writing.
• Either a fresh Droplet or an up-to-date MySQL/MariaDB installation: Percona Server can be installed either from scratch on a new Droplet or as a replacement for a current MySQL/MariaDB installation. Depending on which of these situations you have, some steps in this article may be relevant to only one case and will be marked with (New Only) or (Replacement Only). Any unmarked sections or paragraphs should be used for both cases.
• Root access: All commands within this article should be executed as root.
• Memory: For a default installation you will need at least a 1GB Droplet, otherwise you may get installation failures due to insufficient memory for buffer pool allocation. If you have a 512MB Droplet with swap space allocated, you may also get a successful installation, albeit with subpar performance.
• Data Backup (Replacement Only): Before making any changes to your database server setup, ensure you have a backup of all of your current data. This tutorial will leave all of the data files in place, removing only the MySQL binary and associated tools – but it is always ideal to have a backup in the event something goes awry. We have a number of articles here on DigitalOcean that cover backing up your database files.
• Configuration Backup (Replacement Only): Likewise, it is recommended that you make a copy of your current MySQL/MariaDB configuration if you are replacing a current installation; this file can be found at /etc/mysql/my.cnf on Debian/Ubuntu systems and /etc/my.cnf on CentOS systems. On CentOS, the MariaDB package will remove the configuration file when uninstalled, so this step is especially important on these systems.

Step One — Checking Versions (Replacement Only)

Percona Server versions are drop-in compatible with their equivalent MySQL versions only. i.e. MySQL 5.6 should be replaced with Percona Server 5.6 only. Attempting to use mismatched versions may lead to table corruption or prevent the server from starting.

To check which version you are currently running, first connect to MySQL with your current root password:

mysql -u root -p

Then find the current installed version:

SHOW VARIABLES LIKE "version";

This should identify if you need to install Percona Server 5.5 or 5.6. The one edge case is if you are running MariaDB 10.0, which should be replaced with Percona Server 5.6. If you are running a version of MySQL older than 5.5, you should first upgrade MySQL to 5.5 or greater before continuing.

Step Two — Removing MySQL (Replacement Only)

Before we install Percona server, we will need to remove any MySQL or MariaDB packages that are currently installed, as you should not attempt to run both concurrently on the same data.

You should have a backup of your data and your configuration files before proceeding.

Before uninstalling MySQL, it is advised to stop the database server to prevent data corruption in the event the process isn't stopped safely during the package removal:

service mysql stop

For Debian and Ubuntu based servers, the MySQL server and client packages need to be removed:

apt-get remove mysql-server mysql-client mysql-common
apt-get autoremove

For CentOS systems the default database is now MariaDB, which can be uninstalled as follows:

yum remove MariaDB-server MariaDB-client MariaDB-shared

For other variations, please consult your documentation for the uninstall procedure.

Step Three — Installing Percona Server

Percona memanage repo-nya sendiri. Kemungkinan besar tidak ada di distribusi Ubuntu / Debian anda. kita perlu menambahkan secara manual ke repo kita.

Debian and Ubuntu (Apt)

Untuk Debian dan Ubuntu, kita perlu mengambil key menggunakan perintah

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Akan keluar

Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --homedir /tmp/tmp.kcVwtwI8A0 --no-auto-check-trustdb --trust-model always --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg: requesting key CD2EFD2A from hkp server keys.gnupg.net
gpg: key CD2EFD2A: "Percona MySQL Development Team <mysql-dev@percona.com>" not changed
gpg: Total number processed: 1
gpg:              unchanged: 1

Untuk Ubuntu dan Debian, cek versi-nya menggunakan perintah

lsb_release -c

misalnya keluar

Codename:	trusty

Edit sources.list

vi /etc/apt/sources.list

Tambahkan kalimat berikut dibagian bawah file, pastikan DIST kita ganti dengan nama distribusi yang kita gunakan (dalam hal ini trusty)

deb http://repo.percona.com/apt DIST main
deb-src http://repo.percona.com/apt DIST main

Dalam hal ini

deb http://repo.percona.com/apt trusty main
deb-src http://repo.percona.com/apt trusty main

Selanjutnya kita perlu mengatur agar packages dari Percona di utamakan daripada packages yang lain. Caranya,

touch /etc/apt/preferences.d/00percona.pref

Edit

vi /etc/apt/preferences.d/00percona.pref

Tambahkan kalimat,

Now open this file at /etc/apt/preferences.d/00percona.pref with your chosen text editor (Vim, nano, etc.), add the following lines, and save:

Package: *
Pin: release o=Percona Development Team
Pin-Priority: 1001

(New) kita dapat menginstalasi,

apt-get update
apt-get install percona-server-server

(Replacement) Tergantung versi MySQL atau MariaDB yang kita gunakan. Untuk mengganti versi 5.5 gunakan percona-server-server-5.5 package dan percona-server-server-5.6 untuk 5.6. MariaDB 10.0 digantikan dengan Percona Server 5.6.

apt-get update
apt-get install percona-server-server-5.6

Jika perintah ini berhasil diselesaikan tanpa error, Percona server dapat di instalasi dan berjalan dengan baik. Akan tetapi, jika kita memperoleh error saat instalasi, pastikan kita mempunyai memory yang cukup. Informasi lebih lanjut tentang error saat menjalankan Percona dapat dilihat di log file

/var/log/mysqld.log.

(New Only) When installing on a fresh system, you may be asked during the installation process to set a root database user password. It is also recommended in this situation to run mysqlsecureinstallation to ensure no obvious security issues remain:

/usr/bin/mysql_secure_installation

With the package now installed, the final installation step is to start the server:

service mysql start

If you receive an error regarding the PID file, the server is failing to start. As mentioned in the prerequisites, this often occurs on low RAM servers where the memory limit prevents the XtraDB buffer pool allocation. If this is not the case, check /var/log/mysqld.log for any further error messages, making sure to check that you have properly matched versions between MariaDB and Percona Server.

Note: If you upgrade from MariaDB and receive an error like this:

Can't read dir of '/etc/my.cnf.d' (Errcode: 2 - No such file or directory)
Fatal error in defaults handling. Program aborted
Starting MySQL (Percona Server). ERROR! The server quit without updating PID file (/var/lib/mysql/percona-centos.pid).

You should be able to create the appropriate directory with the command mkdir /etc/my.cnf.d. Then try to start the server.

(New Only) When installing on a fresh system, your Percona Server root user will not have a password assigned and hence your databases will not be secure. It is therefore highly recommended that you use mysqlsecureinstallation to set up a new password and other security options. When asked to enter the current root password, just press Enter.

/usr/bin/mysql_secure_installation

Step Four — Configuration

If you replaced an existing MySQL installation, you should have made a copy of your configuration file that can now be copied back. You can probably skip this section, although you may find the example settings useful.

However, if you are installing Percona Server on a fresh Droplet, you will need to add a configuration file, as Percona Server is currently running on default values that may not be optimal for your Droplet. Before we update the configuration for the first time, it is advised to stop Percona Server, as the PID file location may change. For future configuration changes, a simple restart should suffice after changes are made.

service mysql stop

Below is a sample configuration file for a 1GB droplet, with a relatively small buffer pool for compatibility. On Debian/Ubuntu this file should be written to /etc/mysql/my.cnf. On CentOS, this file should be written to /etc/my.cnf. The file contains in-line comments to explain what the different sections are doing.

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
[mysql]

# CLIENT #
# Configure default options for clients
port                           = 3306

[mysqld]

# GENERAL #
# Choose user for execution, default storage engine and location of the PID file
user                           = mysql
default-storage-engine         = InnoDB
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
# Setup MyISAM options with a minimal config, as InnoDB is our default engine
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
# Enforce limits and safety checks
max-allowed-packet             = 16M
max-connect-errors             = 1000000
innodb                         = FORCE

# DATA STORAGE #
# Select location for database files
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
# Enable and setup the binary log
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
# Configure reasonable default limits throughout Percona Server
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
# Setup InnoDB/XtraDB engine a 300MB buffer pool and 32MB log file size
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 300M

# LOGGING #
# Setup log file locations for error log and slow log
# Slow log may be disabled on production setups to prevent extra IO
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

For larger Droplets, or as a starting point for your customized configuration file, you can use the Percona Configuration Wizard to create a suitable base for your configuration file.

Once you have saved the file, you can restart Percona Server:

service mysql restart

If the server fails to start with this config, try reducing innodb-buffer-pool-size to a smaller value and repeating the above command. Step Five — Checking Your Installation

Now that we have Percona Server installed and running, we can ensure that everything has gone as planned by running a few final checks. First, connect to the database using the mysql client, logging in with your database root user password:

mysql -u root -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.5.38-35.2 Percona Server (GPL), Release 35.2, Revision 674

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Immediately we can see that the server version in the connection text is now specifying Percona Server. By using the SHOW VARIABLES command, we can dig into further details about the specific version that has been installed:

SHOW VARIABLES LIKE "version%";

+-------------------------+--------------------------------------------------+
| Variable_name           | Value                                            |
+-------------------------+--------------------------------------------------+
| version                 | 5.5.38-35.2                                      |
| version_comment         | Percona Server (GPL), Release 35.2, Revision 674 |
| version_compile_machine | x86_64                                           |
| version_compile_os      | debian-linux-gnu                                 |
+-------------------------+--------------------------------------------------+
4 rows in set (0.00 sec)

The precise values and versions may vary in your installation, but the key point is that we are now have Percona Server running rather than MySQL.

Next, we can check that we are taking advantage of XtraDB for any InnoDB based tables:

SHOW STORAGE ENGINES\G

The result will show this block among many others:

... 

*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES

...

9 rows in set (0.00 sec)

The comment field within the response shows that the XtraDB engine has been loaded as the engine for InnoDB based tables. As a final check, it is advised that you ensure all of your databases and tables are being read properly in the new server.

If all of these checks were passed, you now have Percona Server running successfully. However, if any of these checks were not successful, please ensure you have properly completed all prior steps of this tutorial, paying particular attention to matching MySQL version numbers to Percona Server version numbers.

Next Steps

As Percona is drop-in compatible with MySQL, all of the DigitalOcean tutorials covering MySQL can safely be used with Percona Server. When connecting to the database, your applications will still function identically, although hopefully now with the performance boosts from both Percona Server and XtraDB.

MySQL commands will work exactly as before.

Referensi

• https://www.digitalocean.com/community/tutorials/how-to-install-a-fresh-percona-server-or-replace-mysql