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
Akan men-Download sekitar 15Mbyte data dan akan menggunakan space sekitar 150Mbyte.
(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) Jika menginstalasi fresh system, kita akan ditanya untuk men-set root database password. Sebaiknya kita menjalankan mysqlsecureinstallation untuk memastikan tidak ada masalah security yang tertinggal,
/usr/bin/mysql_secure_installation
Setelah semua package di instalasi, langkah terakhir yang perlu kita lakukan adalah menjalankan 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.
6 /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
Akan keluar
* Starting MySQL (Percona Server) database server mysqld
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 47 Server version: 5.6.24-72.2 Percona Server (GPL), Release 72.2, Revision 8d0f85b Copyright (c) 2009-2015 Percona LLC and/or its affiliates Copyright (c) 2000, 2015, 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.6.24-72.2 | | version_comment | Percona Server (GPL), Release 72.2, Revision 8d0f85b | | 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:
...
*************************** 6. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 7. row ***************************
...
9 rows in set (0.01 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.