CentOS5にPercona Tool Kitをインストールする方法

August 20, 2016

約5,000万件のテーブルにインデックスを張らなくてはいけなくなりました。明らかに時間がかかることが明白でしたので、Percona Tool Kitの pt-online-schema-change を使うことにしました。これを使うことで安全かつ高速にインデックス追加ができるようになります。今回はCentOS5にPercona Tool Kitをインストールする方法をまとめます。(なんで今さらCentOS5なの？ということはスルーして下さい・・・)##pt-olnine-schema-change とは

Percona社が提供しているMySQL用の便利ツール集 Percona Tool Kitの一つです。pt-online-schema-changeを利用することでサービスを無停止でカラム、インデックス追加等の操作が実行できます。詳しくはこちらをご覧ください。図付きの解説が分かりやすくまとまっています。

インストール方法

英語ですが手順は本家のサイトにまとまっています。それを参考にインストールを進めます。

Percona Yum Repositoryのインストール

まずはpercona-release-0.1-3.noarch.rpmをダウンロードします。CentOS5ではリモートのyumを直接インストールすることが出来ないのでwgetする必要があります。

[root@www ~]# wget http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm —2016-08-20 18:58:37— http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm Resolving www.percona.com… 74.121.199.234 Connecting to www.percona.com|74.121.199.234|:80… connected. HTTP request sent, awaiting response… 301 Moved Permanently Location: https://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm [following] —2016-08-20 18:58:38— https://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm Connecting to www.percona.com|74.121.199.234|:443… connected. HTTP request sent, awaiting response… 200 OK Length: 6566 (6.4K) [application/x-redhat-package-manager] Saving to: `percona-release-0.1-3.noarch.rpm’

100%[===========================================================================================================================================>] 6,566 —.-K/s in 0s

2016-08-20 18:58:39 (88.2 MB/s) - `percona-release-0.1-3.noarch.rpm’ saved [6566/6566]

ダウンロードしたらrpmコマンドでインストールします。

[root@www ~]# ll total 56 -rw------- 1 root root 1126 Aug 31 2012 anaconda-ks.cfg -rw-r—r— 1 root root 23614 Aug 31 2012 install.log -rw-r—r— 1 root root 3536 Aug 31 2012 install.log.syslog -rw-rw-r— 1 root root 6566 Sep 22 2014 percona-release-0.1-3.noarch.rpm [root@www ~]# rpm -ivh percona-release-0.1-3.noarch.rpm warning: percona-release-0.1-3.noarch.rpm: Header V4 DSA signature: NOKEY, key ID cd2efd2a Preparing… ### ### ### ### ### ### ### ### ### ### ### ### ### ### # [100%] 1:percona-release ### ### ### ### ### ### ### ### ### ### ### ### ### ### # [100%]

これで完了。

Perlcona Tool Kitのインストール

このようにインストール可能な状態となります。

[root@www ~]# yum list | grep percona percona-release.noarch 0.1-3 installed Percona-SQL-50-debuginfo.x86_64 5.0.92-b23.89.rhel5 percona-release-x86_64 Percona-SQL-client-50.x86_64 5.0.92-b23.89.rhel5 percona-release-x86_64 Percona-SQL-devel-50.x86_64 5.0.92-b23.89.rhel5 percona-release-x86_64 Percona-SQL-server-50.x86_64 5.0.92-b23.89.rhel5 percona-release-x86_64 Percona-SQL-shared-50.x86_64 5.0.92-b23.89.rhel5 percona-release-x86_64 Percona-SQL-shared-compat.x86_64 5.0.92-b23.85.rhel5 percona-release-x86_64 Percona-SQL-test-50.x86_64 5.0.92-b23.89.rhel5 percona-release-x86_64 Percona-Server-51-debuginfo.x86_64 5.1.73-rel14.12.624.rhel5 percona-release-x86_64 Percona-Server-55-debuginfo.x86_64 5.5.51-rel38.1.el5 percona-release-x86_64 Percona-Server-56-debuginfo.x86_64 5.6.31-rel77.0.el5 percona-release-x86_64 Percona-Server-MongoDB.x86_64 3.0.12-1.8.el5 percona-release-x86_64 Percona-Server-MongoDB-32.x86_64 3.2.8-2.0.el5 percona-release-x86_64 3.2.8-2.0.el5 percona-release-x86_64 Percona-Server-MongoDB-32-mongos.x86_64 3.2.8-2.0.el5 percona-release-x86_64 Percona-Server-MongoDB-32-server.x86_64 3.2.8-2.0.el5 percona-release-x86_64 Percona-Server-MongoDB-32-shell.x86_64 3.2.8-2.0.el5 percona-release-x86_64 Percona-Server-MongoDB-32-tools.x86_64 3.2.8-2.0.el5 percona-release-x86_64 Percona-Server-MongoDB-debuginfo.x86_64 3.0.12-1.8.el5 percona-release-x86_64 Percona-Server-MongoDB-mongos.x86_64 3.0.12-1.8.el5 percona-release-x86_64 Percona-Server-MongoDB-server.x86_64 3.0.12-1.8.el5 percona-release-x86_64 Percona-Server-MongoDB-shell.x86_64 3.0.12-1.8.el5 percona-release-x86_64 Percona-Server-MongoDB-tools.x86_64 3.0.12-1.8.el5 percona-release-x86_64 Percona-Server-client-51.x86_64 5.1.73-rel14.12.624.rhel5 percona-release-x86_64 Percona-Server-client-55.x86_64 5.5.51-rel38.1.el5 percona-release-x86_64 Percona-Server-client-56.x86_64 5.6.31-rel77.0.el5 percona-release-x86_64 Percona-Server-devel-51.x86_64 5.1.73-rel14.12.624.rhel5 percona-release-x86_64 Percona-Server-devel-55.x86_64 5.5.51-rel38.1.el5 percona-release-x86_64 Percona-Server-devel-56.x86_64 5.6.31-rel77.0.el5 percona-release-x86_64 Percona-Server-server-51.x86_64 5.1.73-rel14.12.624.rhel5 percona-release-x86_64 Percona-Server-server-55.x86_64 5.5.51-rel38.1.el5 percona-release-x86_64 Percona-Server-server-56.x86_64 5.6.31-rel77.0.el5 percona-release-x86_64 Percona-Server-shared-51.x86_64 5.1.73-rel14.12.624.rhel5 percona-release-x86_64 Percona-Server-shared-55.x86_64 5.5.51-rel38.1.el5 percona-release-x86_64 Percona-Server-shared-56.x86_64 5.6.31-rel77.0.el5 percona-release-x86_64 Percona-Server-shared-compat.x86_64 5.1.68-rel14.6.551.rhel5 percona-release-x86_64 Percona-Server-shared-compat-51.x86_64 5.1.73-rel14.12.624.rhel5 percona-release-x86_64 Percona-Server-test-51.x86_64 5.1.73-rel14.12.624.rhel5 percona-release-x86_64 Percona-Server-test-55.x86_64 5.5.51-rel38.1.el5 percona-release-x86_64 Percona-Server-test-56.x86_64 5.6.31-rel77.0.el5 percona-release-x86_64 Percona-Server-tokudb-56.x86_64 5.6.31-rel77.0.el5 percona-release-x86_64 Percona-XtraDB-Cluster-55.x86_64 1:5.5.41-25.11.853.el5 percona-release-x86_64 1:5.5.41-25.11.853.el5 percona-release-x86_64 Percona-XtraDB-Cluster-56.x86_64 1:5.6.30-25.16.1.el5 percona-release-x86_64 1:5.6.30-25.16.1.el5 percona-release-x86_64 Percona-XtraDB-Cluster-client.x86_64 1:5.5.34-23.7.6.565.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-client-55.x86_64 1:5.5.41-25.11.853.el5 percona-release-x86_64 Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.30-25.16.1.el5 percona-release-x86_64 Percona-XtraDB-Cluster-debuginfo.x86_64 1:5.5.34-23.7.6.565.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-devel.x86_64 1:5.5.34-23.7.6.565.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-devel-55.x86_64 1:5.5.41-25.11.853.el5 percona-release-x86_64 Percona-XtraDB-Cluster-devel-56.x86_64 1:5.6.30-25.16.1.el5 percona-release-x86_64 Percona-XtraDB-Cluster-full-55.x86_64 1:5.5.41-25.11.853.el5 percona-release-x86_64 Percona-XtraDB-Cluster-full-56.x86_64 1:5.6.30-25.16.1.el5 percona-release-x86_64 Percona-XtraDB-Cluster-galera.x86_64 2.8-1.162.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-galera-2.x86_64 2.12-1.2682.rhel5 percona-release-x86_64 2.12-1.2682.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-galera-3.x86_64 3.16-1.rhel5 percona-release-x86_64 3.16-1.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-galera-56.x86_64 3.1-1.169.rhel5 percona-release-x86_64 3.1-1.169.rhel5 percona-release-x86_64 2.8-1.162.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-garbd-2.x86_64 2.12-1.2682.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-garbd-3.x86_64 3.16-1.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-server.x86_64 1:5.5.34-23.7.6.565.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-server-55.x86_64 1:5.5.41-25.11.853.el5 percona-release-x86_64 Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.30-25.16.1.el5 percona-release-x86_64 Percona-XtraDB-Cluster-shared.x86_64 1:5.5.34-23.7.6.565.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-shared-55.x86_64 1:5.5.41-25.11.853.el5 percona-release-x86_64 Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.30-25.16.1.el5 percona-release-x86_64 Percona-XtraDB-Cluster-test.x86_64 1:5.5.34-23.7.6.565.rhel5 percona-release-x86_64 Percona-XtraDB-Cluster-test-55.x86_64 1:5.5.41-25.11.853.el5 percona-release-x86_64 Percona-XtraDB-Cluster-test-56.x86_64 1:5.6.30-25.16.1.el5 percona-release-x86_64 jemalloc.x86_64 3.6.0-2.el5 percona-release-x86_64 jemalloc-devel.x86_64 3.6.0-2.el5 percona-release-x86_64 libev.x86_64 4.15-1.el5.rf percona-release-x86_64 libev-devel.x86_64 4.15-1.el5.rf percona-release-x86_64 libtokumx-enterprise.x86_64 2.0.2-1.el5 percona-release-x86_64 libtokumx-enterprise-devel.x86_64 2.0.2-1.el5 percona-release-x86_64 percona-cacti-templates.noarch 1.1.6-1 percona-release-noarch percona-nagios-plugins.noarch 1.1.6-1 percona-release-noarch percona-playback.x86_64 0.7-2.el5.centos percona-release-x86_64 percona-playback-debuginfo.x86_64 0.7-2.el5.centos percona-release-x86_64 percona-playback-devel.x86_64 0.7-2.el5.centos percona-release-x86_64 percona-toolkit.noarch 2.2.19-1 percona-release-noarch percona-xtrabackup.x86_64 2.3.5-1.el5 percona-release-x86_64 percona-xtrabackup-20.x86_64 2.0.8-587.rhel5 percona-release-x86_64 percona-xtrabackup-20-debuginfo.x86_64 2.0.8-587.rhel5 percona-release-x86_64 percona-xtrabackup-20-test.x86_64 2.0.8-587.rhel5 percona-release-x86_64 percona-xtrabackup-21.x86_64 2.1.9-746.rhel5 percona-release-x86_64 percona-xtrabackup-21-debuginfo.x86_64 2.1.9-746.rhel5 percona-release-x86_64 percona-xtrabackup-22.x86_64 2.2.13-1.el5 percona-release-x86_64 percona-xtrabackup-22-debuginfo.x86_64 2.2.13-1.el5 percona-release-x86_64 percona-xtrabackup-24.x86_64 2.4.4-1.el5 percona-release-x86_64 percona-xtrabackup-24-debuginfo.x86_64 2.4.4-1.el5 percona-release-x86_64 percona-xtrabackup-debuginfo.x86_64 2.3.5-1.el5 percona-release-x86_64 percona-xtrabackup-test.x86_64 2.3.5-1.el5 percona-release-x86_64 percona-xtrabackup-test-21.x86_64 2.1.9-746.rhel5 percona-release-x86_64 percona-xtrabackup-test-22.x86_64 2.2.13-1.el5 percona-release-x86_64 percona-xtrabackup-test-24.x86_64 2.4.4-1.el5 percona-release-x86_64 percona-zabbix-templates.noarch 1.1.6-1 percona-release-noarch qpress.x86_64 11-1.el5.centos percona-release-x86_64 qpress-debuginfo.x86_64 11-1.el5.centos percona-release-x86_64 sysbench.x86_64 0.5-6.el5 percona-release-x86_64 sysbench-debuginfo.x86_64 0.5-6.el5 percona-release-x86_64 tokumx-enterprise.x86_64 2.0.2-1.el5 percona-release-x86_64 tokumx-enterprise-common.x86_64 2.0.2-1.el5 percona-release-x86_64 tokumx-enterprise-server.x86_64 2.0.2-1.el5 percona-release-x86_64

必要なのは Percona Tool Kit だけなので、それだけをインストールします。

[root@www ~]# yum install percona-toolkit.noarch Loaded plugins: fastestmirror, security Loading mirror speeds from cached hostfile

base: mirror.fairway.ne.jp
extras: mirror.nus.edu.sg
rpmforge: mirror.oscc.org.my
updates: centos.usonyx.net

Setting up Install Process Resolving Dependencies —> Running transaction check ---> Package percona-toolkit.noarch 0:2.2.19-1 set to be updated —> Processing Dependency: perl(DBD::mysql) >= 1.0 for package: percona-toolkit —> Processing Dependency: perl(Term::ReadKey) for package: percona-toolkit —> Processing Dependency: perl(IO::Socket::SSL) for package: percona-toolkit —> Running transaction check ---> Package perl-DBD-MySQL.x86_64 0:3.0007-2.el5 set to be updated —> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: perl-DBD-MySQL —> Processing Dependency: libmysqlclient.so.15()(64bit) for package: perl-DBD-MySQL ---> Package perl-IO-Socket-SSL.noarch 0:1.01-2.el5 set to be updated —> Processing Dependency: perl(Net::SSLeay) >= 1.21 for package: perl-IO-Socket-SSL ---> Package perl-TermReadKey.x86_64 0:2.30-3.el5.rf set to be updated —> Running transaction check ---> Package mysql.x86_64 0:5.0.95-5.el5_9 set to be updated ---> Package perl-Net-SSLeay.x86_64 0:1.30-4.fc6 set to be updated —> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================================================================================================== Package Arch Version Repository Size

Installing: percona-toolkit noarch 2.2.19-1 percona-release-noarch 1.7 M Installing for dependencies: mysql x86_64 5.0.95-5.el5_9 base 4.9 M perl-DBD-MySQL x86_64 3.0007-2.el5 base 148 k perl-IO-Socket-SSL noarch 1.01-2.el5 base 50 k perl-Net-SSLeay x86_64 1.30-4.fc6 base 192 k perl-TermReadKey x86_64 2.30-3.el5.rf rpmforge 57 k

Transaction Summary

Install 6 Package(s) Upgrade 0 Package(s)

Total download size: 7.0 M Is this ok [y/N]: y Downloading Packages: (1/6): perl-IO-Socket-SSL-1.01-2.el5.noarch.rpm | 50 kB 00:00 (2/6): perl-TermReadKey-2.30-3.el5.rf.x86_64.rpm | 57 kB 00:00 (3/6): perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm | 148 kB 00:00 (4/6): perl-Net-SSLeay-1.30-4.fc6.x86_64.rpm | 192 kB 00:00 (5/6): percona-toolkit-2.2.19-1.noarch.rpm | 1.7 MB 00:01 (6/6): mysql-5.0.95-5.el5_9.x86_64.rpm | 4.9 MB 00:28

Total 209 kB/s | 7.0 MB 00:34 warning: rpmts_HdrFromFdno: Header V4 DSA signature: NOKEY, key ID cd2efd2a percona-release-noarch/gpgkey | 1.7 kB 00:00 Importing GPG key 0xCD2EFD2A “Percona MySQL Development Team <mysql-dev@percona.com>” from /etc/pki/rpm-gpg/RPM-GPG-KEY-Percona Is this ok [y/N]: y Running rpm_check_debug Running Transaction Test Finished Transaction Test Transaction Test Succeeded Running Transaction Installing : perl-TermReadKey 1/6 Installing : perl-Net-SSLeay 2/6 Installing : mysql 3/6 Installing : perl-DBD-MySQL 4/6 Installing : perl-IO-Socket-SSL 5/6 Installing : percona-toolkit 6/6

Installed: percona-toolkit.noarch 0:2.2.19-1

Dependency Installed: mysql.x86_64 0:5.0.95-5.el5_9 perl-DBD-MySQL.x86_64 0:3.0007-2.el5 perl-IO-Socket-SSL.noarch 0:1.01-2.el5 perl-Net-SSLeay.x86_64 0:1.30-4.fc6 perl-TermReadKey.x86_64 0:2.30-3.el5.rf

Complete!

これでインストール完了です。コマンドを叩くと使えるようになっているのが分かります。

[root@www ~]# pt-online-schema-change Usage: pt-online-schema-change [OPTIONS] DSN

Errors in command-line arguments:

A DSN must be specified
The DSN must specify a database (D) and a table (t)

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully. For more details, please use the —help option, or try ‘perldoc /usr/bin/pt-online-schema-change’ for complete documentation.

実際 pt-online-schema-changeしか使ったこと無いので他のコマンドを試してみたいですね。