はじめに
CentOS6.7にPercona XtraDB Clusterをインストールする手順を記述します。
環境
- CentOS 6.7
- Percona XtraDB Cluster 5.6
サーバ構成
| node | ホスト名 | IPアドレス |
|---|---|---|
| node1 | percona01 | 192.168.33.21 |
| node2 | percona02 | 192.168.33.22 |
| node3 | percona03 | 192.168.33.23 |
リポジトリの設定
$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
読み込んだプラグイン:fastestmirror, security
インストール処理の設定をしています
Loading mirror speeds from cached hostfile
* base: ftp.riken.jp
* extras: ftp.riken.jp
* updates: ftp.riken.jp
パッケージ yum-3.2.29-69.el6.centos.noarch はインストール済みか最新バージョンです
パッケージ install は利用できません。
percona-release-0.1-3.noarch.rpm | 6.4 kB 00:00
/var/tmp/yum-root-bV5j5D/percona-release-0.1-3.noarch.rpm を調べています: percona-release-0.1-3.noarch
/var/tmp/yum-root-bV5j5D/percona-release-0.1-3.noarch.rpm をインストール済みとして設定しています
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> Package percona-release.noarch 0:0.1-3 will be インストール
--> 依存性解決を終了しました。
依存性を解決しました
=============================================================================================================================================================
パッケージ アーキテクチャ バージョン リポジトリー 容量
=============================================================================================================================================================
インストールしています:
percona-release noarch 0.1-3 /percona-release-0.1-3.noarch 5.8 k
トランザクションの要約
=============================================================================================================================================================
インストール 1 パッケージ
合計容量: 5.8 k
インストール済み容量: 5.8 k
これでいいですか? [y/N]y
パッケージをダウンロードしています:
rpm_check_debug を実行しています
トランザクションのテストを実行しています
トランザクションのテストを成功しました
トランザクションを実行しています
インストールしています : percona-release-0.1-3.noarch 1/1
Verifying : percona-release-0.1-3.noarch 1/1
インストール:
percona-release.noarch 0:0.1-3
完了しました!
$ sudo yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
読み込んだプラグイン:fastestmirror, security
インストール処理の設定をしています
epel-release-latest-6.noarch.rpm | 14 kB 00:00
/var/tmp/yum-root-bV5j5D/epel-release-latest-6.noarch.rpm を調べています: epel-release-6-8.noarch
/var/tmp/yum-root-bV5j5D/epel-release-latest-6.noarch.rpm をインストール済みとして設定しています
Loading mirror speeds from cached hostfile
* base: ftp.riken.jp
* extras: ftp.riken.jp
* updates: ftp.riken.jp
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> Package epel-release.noarch 0:6-8 will be インストール
--> 依存性解決を終了しました。
依存性を解決しました
=============================================================================================================================================================
パッケージ アーキテクチャ バージョン リポジトリー 容量
=============================================================================================================================================================
インストールしています:
epel-release noarch 6-8 /epel-release-latest-6.noarch 22 k
トランザクションの要約
=============================================================================================================================================================
インストール 1 パッケージ
合計容量: 22 k
インストール済み容量: 22 k
これでいいですか? [y/N]y
パッケージをダウンロードしています:
rpm_check_debug を実行しています
トランザクションのテストを実行しています
トランザクションのテストを成功しました
トランザクションを実行しています
インストールしています : epel-release-6-8.noarch 1/1
Verifying : epel-release-6-8.noarch 1/1
インストール:
epel-release.noarch 0:6-8
完了しました!
$ yum repolist
読み込んだプラグイン:fastestmirror, security
Loading mirror speeds from cached hostfile
epel/metalink | 4.7 kB 00:00
* base: www.ftp.ne.jp
* epel: ftp.riken.jp
* extras: www.ftp.ne.jp
* updates: www.ftp.ne.jp
epel | 4.3 kB 00:00
epel/primary_db | 5.7 MB 00:02
リポジトリー ID リポジトリー名 状態
base CentOS-6 - Base 6,575
epel Extra Packages for Enterprise Linux 6 - x86_64 11,767
extras CentOS-6 - Extras 35
percona-release-noarch Percona-Release YUM repository - noarch 32
percona-release-x86_64 Percona-Release YUM repository - x86_64 594
updates CentOS-6 - Updates 298
repolist: 19,301
Percona XtraDB Clusterのインストール
$ sudo yum install Percona-XtraDB-Cluster-56
読み込んだプラグイン:fastestmirror, security
インストール処理の設定をしています
Loading mirror speeds from cached hostfile
epel/metalink | 5.5 kB 00:00
* base: ftp.riken.jp
* epel: ftp.riken.jp
* extras: ftp.riken.jp
* updates: ftp.riken.jp
base | 3.7 kB 00:00
epel | 4.3 kB 00:00
epel/primary_db | 5.7 MB 00:04
extras | 3.4 kB 00:00
percona-release-noarch | 951 B 00:00
percona-release-x86_64 | 951 B 00:00
updates | 3.4 kB 00:00
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> Package Percona-XtraDB-Cluster-56.x86_64 1:5.6.24-25.11.1.el6 will be インストール
--> 依存性の処理をしています: Percona-XtraDB-Cluster-server-56 のパッケージ: 1:Percona-XtraDB-Cluster-56-5.6.24-25.11.1.el6.x86_64
--> 依存性の処理をしています: Percona-XtraDB-Cluster-galera-3 のパッケージ: 1:Percona-XtraDB-Cluster-56-5.6.24-25.11.1.el6.x86_64
--> 依存性の処理をしています: Percona-XtraDB-Cluster-client-56 のパッケージ: 1:Percona-XtraDB-Cluster-56-5.6.24-25.11.1.el6.x86_64
--> トランザクションの確認を実行しています。
---> Package Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.24-25.11.1.el6 will be インストール
--> 依存性の処理をしています: perl-DBI のパッケージ: 1:Percona-XtraDB-Cluster-client-56-5.6.24-25.11.1.el6.x86_64
---> Package Percona-XtraDB-Cluster-galera-3.x86_64 0:3.11-1.rhel6 will be インストール
---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.24-25.11.1.el6 will be インストール
--> 依存性の処理をしています: percona-xtrabackup >= 2.2.5 のパッケージ: 1:Percona-XtraDB-Cluster-server-56-5.6.24-25.11.1.el6.x86_64
--> 依存性の処理をしています: perl-DBD-MySQL のパッケージ: 1:Percona-XtraDB-Cluster-server-56-5.6.24-25.11.1.el6.x86_64
--> 依存性の処理をしています: Percona-XtraDB-Cluster-shared-56 のパッケージ: 1:Percona-XtraDB-Cluster-server-56-5.6.24-25.11.1.el6.x86_64
--> 依存性の処理をしています: socat のパッケージ: 1:Percona-XtraDB-Cluster-server-56-5.6.24-25.11.1.el6.x86_64
--> トランザクションの確認を実行しています。
---> Package Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.24-25.11.1.el6 will be インストール
---> Package percona-xtrabackup.x86_64 0:2.2.12-1.el6 will be インストール
--> 依存性の処理をしています: perl(Time::HiRes) のパッケージ: percona-xtrabackup-2.2.12-1.el6.x86_64
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be インストール
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be インストール
---> Package socat.x86_64 0:1.7.2.3-1.el6 will be インストール
--> 依存性の処理をしています: libreadline.so.5()(64bit) のパッケージ: socat-1.7.2.3-1.el6.x86_64
--> トランザクションの確認を実行しています。
---> Package compat-readline5.x86_64 0:5.2-17.1.el6 will be インストール
---> Package perl-Time-HiRes.x86_64 4:1.9721-141.el6 will be インストール
--> 依存性解決を終了しました。
依存性を解決しました
=============================================================================================================================================================
パッケージ アーキテクチャ バージョン リポジトリー 容量
=============================================================================================================================================================
インストールしています:
Percona-XtraDB-Cluster-56 x86_64 1:5.6.24-25.11.1.el6 percona-release-x86_64 26 k
依存性関連でのインストールをします。:
Percona-XtraDB-Cluster-client-56 x86_64 1:5.6.24-25.11.1.el6 percona-release-x86_64 6.1 M
Percona-XtraDB-Cluster-galera-3 x86_64 3.11-1.rhel6 percona-release-x86_64 817 k
Percona-XtraDB-Cluster-server-56 x86_64 1:5.6.24-25.11.1.el6 percona-release-x86_64 19 M
Percona-XtraDB-Cluster-shared-56 x86_64 1:5.6.24-25.11.1.el6 percona-release-x86_64 684 k
compat-readline5 x86_64 5.2-17.1.el6 base 130 k
percona-xtrabackup x86_64 2.2.12-1.el6 percona-release-x86_64 4.8 M
perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k
perl-DBI x86_64 1.609-4.el6 base 705 k
perl-Time-HiRes x86_64 4:1.9721-141.el6 base 49 k
socat x86_64 1.7.2.3-1.el6 epel 246 k
トランザクションの要約
=============================================================================================================================================================
インストール 11 パッケージ
総ダウンロード容量: 33 M
インストール済み容量: 147 M
これでいいですか? [y/N]y
パッケージをダウンロードしています:
(1/11): Percona-XtraDB-Cluster-56-5.6.24-25.11.1.el6.x86_64.rpm | 26 kB 00:00
(2/11): Percona-XtraDB-Cluster-client-56-5.6.24-25.11.1.el6.x86_64.rpm | 6.1 MB 00:27
(3/11): Percona-XtraDB-Cluster-galera-3-3.11-1.rhel6.x86_64.rpm | 817 kB 00:02
(4/11): Percona-XtraDB-Cluster-server-56-5.6.24-25.11.1.el6.x86_64.rpm | 19 MB 01:21
(5/11): Percona-XtraDB-Cluster-shared-56-5.6.24-25.11.1.el6.x86_64.rpm | 684 kB 00:01
(6/11): compat-readline5-5.2-17.1.el6.x86_64.rpm | 130 kB 00:00
(7/11): percona-xtrabackup-2.2.12-1.el6.x86_64.rpm | 4.8 MB 00:18
(8/11): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:00
(9/11): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:00
(10/11): perl-Time-HiRes-1.9721-141.el6.x86_64.rpm | 49 kB 00:00
(11/11): socat-1.7.2.3-1.el6.x86_64.rpm | 246 kB 00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------
合計 244 kB/s | 33 MB 02:18
警告: rpmts_HdrFromFdno: ヘッダ V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
Importing GPG key 0xCD2EFD2A:
Userid : Percona MySQL Development Team <mysql-dev@percona.com>
Package: percona-release-0.1-3.noarch (@/percona-release-0.1-3.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
これでいいですか? [y/N]y
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
Importing GPG key 0x0608B895:
Userid : EPEL (6) <epel@fedoraproject.org>
Package: epel-release-6-8.noarch (@/epel-release-latest-6.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
これでいいですか? [y/N]y
rpm_check_debug を実行しています
トランザクションのテストを実行しています
トランザクションのテストを成功しました
トランザクションを実行しています
インストールしています : perl-DBI-1.609-4.el6.x86_64 1/11
インストールしています : 1:Percona-XtraDB-Cluster-client-56-5.6.24-25.11.1.el6.x86_64 2/11
インストールしています : perl-DBD-MySQL-4.013-3.el6.x86_64 3/11
インストールしています : Percona-XtraDB-Cluster-galera-3-3.11-1.rhel6.x86_64 4/11
インストールしています : compat-readline5-5.2-17.1.el6.x86_64 5/11
インストールしています : socat-1.7.2.3-1.el6.x86_64 6/11
インストールしています : 4:perl-Time-HiRes-1.9721-141.el6.x86_64 7/11
インストールしています : percona-xtrabackup-2.2.12-1.el6.x86_64 8/11
インストールしています : 1:Percona-XtraDB-Cluster-shared-56-5.6.24-25.11.1.el6.x86_64 9/11
インストールしています : 1:Percona-XtraDB-Cluster-server-56-5.6.24-25.11.1.el6.x86_64 10/11
2015-09-16 22:08:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-09-16 22:08:21 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2-56) starting as process 2464 ...
2015-09-16 22:08:21 2464 [Note] WSREP: Read nil XID from storage engines, skipping position init
2015-09-16 22:08:21 2464 [Note] WSREP: wsrep_load(): loading provider library 'none'
2015-09-16 22:08:21 2464 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-09-16 22:08:21 2464 [Note] InnoDB: The InnoDB memory heap is disabled
2015-09-16 22:08:21 2464 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-09-16 22:08:21 2464 [Note] InnoDB: Memory barrier is not used
2015-09-16 22:08:21 2464 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-09-16 22:08:21 2464 [Note] InnoDB: Using Linux native AIO
2015-09-16 22:08:21 2464 [Note] InnoDB: Not using CPU crc32 instructions
2015-09-16 22:08:21 2464 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-09-16 22:08:21 2464 [Note] InnoDB: Completed initialization of buffer pool
2015-09-16 22:08:21 2464 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2015-09-16 22:08:21 2464 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2015-09-16 22:08:21 2464 [Note] InnoDB: Database physically writes the file full: wait...
2015-09-16 22:08:21 2464 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2015-09-16 22:08:21 2464 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2015-09-16 22:08:21 2464 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-09-16 22:08:21 2464 [Warning] InnoDB: New log files created, LSN=45781
2015-09-16 22:08:21 2464 [Note] InnoDB: Doublewrite buffer not found: creating new
2015-09-16 22:08:22 2464 [Note] InnoDB: Doublewrite buffer created
2015-09-16 22:08:22 2464 [Note] InnoDB: 128 rollback segment(s) are active.
2015-09-16 22:08:22 2464 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-09-16 22:08:22 2464 [Note] InnoDB: Foreign key constraint system tables created
2015-09-16 22:08:22 2464 [Note] InnoDB: Creating tablespace and datafile system tables.
2015-09-16 22:08:22 2464 [Note] InnoDB: Tablespace and datafile system tables created.
2015-09-16 22:08:22 2464 [Note] InnoDB: Waiting for purge to start
2015-09-16 22:08:22 2464 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 0
2015-09-16 22:08:22 2464 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-09-16 22:08:22 2464 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-09-16 22:08:22 2464 [Note] WSREP: Service disconnected.
2015-09-16 22:08:23 2464 [Note] WSREP: Some threads may fail to exit.
2015-09-16 22:08:23 2464 [Note] Binlog end
2015-09-16 22:08:23 2464 [Note] InnoDB: FTS optimize thread exiting.
2015-09-16 22:08:23 2464 [Note] InnoDB: Starting shutdown...
2015-09-16 22:08:25 2464 [Note] InnoDB: Shutdown completed; log sequence number 1625977
2015-09-16 22:08:25 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-09-16 22:08:25 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2-56) starting as process 2488 ...
2015-09-16 22:08:25 2488 [Note] WSREP: Read nil XID from storage engines, skipping position init
2015-09-16 22:08:25 2488 [Note] WSREP: wsrep_load(): loading provider library 'none'
2015-09-16 22:08:25 2488 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-09-16 22:08:25 2488 [Note] InnoDB: The InnoDB memory heap is disabled
2015-09-16 22:08:25 2488 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-09-16 22:08:25 2488 [Note] InnoDB: Memory barrier is not used
2015-09-16 22:08:25 2488 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-09-16 22:08:25 2488 [Note] InnoDB: Using Linux native AIO
2015-09-16 22:08:25 2488 [Note] InnoDB: Not using CPU crc32 instructions
2015-09-16 22:08:25 2488 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-09-16 22:08:25 2488 [Note] InnoDB: Completed initialization of buffer pool
2015-09-16 22:08:25 2488 [Note] InnoDB: Highest supported file format is Barracuda.
2015-09-16 22:08:25 2488 [Note] InnoDB: 128 rollback segment(s) are active.
2015-09-16 22:08:25 2488 [Note] InnoDB: Waiting for purge to start
2015-09-16 22:08:25 2488 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.24-72.2 started; log sequence number 1625977
2015-09-16 22:08:25 2488 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-09-16 22:08:25 2488 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-09-16 22:08:25 2488 [Note] WSREP: Service disconnected.
2015-09-16 22:08:26 2488 [Note] WSREP: Some threads may fail to exit.
2015-09-16 22:08:26 2488 [Note] Binlog end
2015-09-16 22:08:26 2488 [Note] InnoDB: FTS optimize thread exiting.
2015-09-16 22:08:26 2488 [Note] InnoDB: Starting shutdown...
2015-09-16 22:08:28 2488 [Note] InnoDB: Shutdown completed; log sequence number 1625987
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with Percona XtraDB Cluster 5.6 at https://bugs.launchpad.net/percona-xtradb-cluster/+filebug
The latest information about Percona XtraDB Cluster 5.6 is available on the web at
http://www.percona.com/doc/percona-xtradb-cluster/5.6/
Support Percona XtraDB Cluster 5.6 by buying support at http://www.percona.com/products/mysql-support
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
Percona XtraDB Cluster is distributed with several useful UDFs from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html for more details
インストールしています : 1:Percona-XtraDB-Cluster-56-5.6.24-25.11.1.el6.x86_64 11/11
Verifying : 1:Percona-XtraDB-Cluster-shared-56-5.6.24-25.11.1.el6.x86_64 1/11
Verifying : 1:Percona-XtraDB-Cluster-client-56-5.6.24-25.11.1.el6.x86_64 2/11
Verifying : socat-1.7.2.3-1.el6.x86_64 3/11
Verifying : 1:Percona-XtraDB-Cluster-server-56-5.6.24-25.11.1.el6.x86_64 4/11
Verifying : 4:perl-Time-HiRes-1.9721-141.el6.x86_64 5/11
Verifying : compat-readline5-5.2-17.1.el6.x86_64 6/11
Verifying : percona-xtrabackup-2.2.12-1.el6.x86_64 7/11
Verifying : perl-DBI-1.609-4.el6.x86_64 8/11
Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 9/11
Verifying : 1:Percona-XtraDB-Cluster-56-5.6.24-25.11.1.el6.x86_64 10/11
Verifying : Percona-XtraDB-Cluster-galera-3-3.11-1.rhel6.x86_64 11/11
インストール:
Percona-XtraDB-Cluster-56.x86_64 1:5.6.24-25.11.1.el6
依存性関連をインストールしました:
Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.24-25.11.1.el6 Percona-XtraDB-Cluster-galera-3.x86_64 0:3.11-1.rhel6
Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.24-25.11.1.el6 Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.24-25.11.1.el6
compat-readline5.x86_64 0:5.2-17.1.el6 percona-xtrabackup.x86_64 0:2.2.12-1.el6
perl-DBD-MySQL.x86_64 0:4.013-3.el6 perl-DBI.x86_64 0:1.609-4.el6
perl-Time-HiRes.x86_64 4:1.9721-141.el6 socat.x86_64 0:1.7.2.3-1.el6
完了しました!
セットアップ
node1(percona01)
- /etc/my.cnf
my.cnf
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.33.21,192.168.33.22,192.168.33.23
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.33.21
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_centos_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
- 起動
$ sudo service mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster). SUCCESS!
- 状態の確認
$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.24-72.2-56 Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11
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> show status like 'wsrep%';
+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid | fe401022-5c79-11e5-87cb-9ac1151f413c |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 158 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.500000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 262144 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_incoming_addresses | 192.168.33.21:3306 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | fe3ef3c3-5c79-11e5-a8a4-cb18a2305a27 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | fe401022-5c79-11e5-87cb-9ac1151f413c |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.11(ra0189ab) |
| wsrep_ready | ON |
+------------------------------+--------------------------------------+
58 rows in set (0.01 sec)
- アカウント設定
mysql> UPDATE mysql.user SET password=PASSWORD("Passw0rd") where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
node2(percona02)
- /etc/my.cnf
my.cnf
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.33.21,192.168.33.22,192.168.33.23
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #2 address
wsrep_node_address=192.168.33.22
# Cluster name
wsrep_cluster_name=my_centos_cluster
# SST method
wsrep_sst_method=xtrabackup-v2
# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
- 起動
$ sudo service mysql start
Starting MySQL (Percona XtraDB Cluster)..State transfer in progress, setting sleep higher
... SUCCESS!
- 状態の確認
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.24-72.2-56 Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11
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> show status like 'wsrep%';
+------------------------------+---------------------------------------+
| Variable_name | Value |
+------------------------------+---------------------------------------+
| wsrep_local_state_uuid | fe401022-5c79-11e5-87cb-9ac1151f413c |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 4 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 3 |
| wsrep_received_bytes | 252 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 4096 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_incoming_addresses | 192.168.33.22:3306,192.168.33.21:3306 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 698e93db-5c7d-11e5-a8d0-4b4a07e7dfdc |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | fe401022-5c79-11e5-87cb-9ac1151f413c |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.11(ra0189ab) |
| wsrep_ready | ON |
+------------------------------+---------------------------------------+
58 rows in set (0.00 sec)
node3(percona03)
- /etc/my.cnf
my.cnf
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.33.21,192.168.33.22,192.168.33.23
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #3 address
wsrep_node_address=192.168.33.23
# Cluster name
wsrep_cluster_name=my_centos_cluster
# SST method
wsrep_sst_method=xtrabackup-v2
# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
- 起動
$ sudo service mysql start
Starting MySQL (Percona XtraDB Cluster)..State transfer in progress, setting sleep higher
... SUCCESS!
- 状態の確認
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.24-72.2-56 Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11
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> show status like 'wsrep%';
+------------------------------+----------------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------------+
| wsrep_local_state_uuid | fe401022-5c79-11e5-87cb-9ac1151f413c |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 4 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 3 |
| wsrep_received_bytes | 338 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 4096 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_incoming_addresses | 192.168.33.23:3306,192.168.33.22:3306,192.168.33.21:3306 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 1d096f60-5c81-11e5-aa27-5628214d872c |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | fe401022-5c79-11e5-87cb-9ac1151f413c |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.11(ra0189ab) |
| wsrep_ready | ON |
+------------------------------+----------------------------------------------------------+
58 rows in set (0.00 sec)
レプリケーション動作確認
* node1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE percona;
Query OK, 1 row affected (0.00 sec)
- node2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| percona |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use percona;
Database changed
mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.05 sec)
- node3
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| percona |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use percona;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_percona |
+-------------------+
| example |
+-------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.01 sec)
- node1
mysql> use percona;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_percona |
+-------------------+
| example |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
| 1 | percona1 |
+---------+-----------+
1 row in set (0.00 sec)
mysql> INSERT INTO percona.example VALUES (2, 'percona2');
Query OK, 1 row affected (0.02 sec)
- node2
mysql> show tables;
+-------------------+
| Tables_in_percona |
+-------------------+
| example |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
| 1 | percona1 |
| 2 | percona2 |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> INSERT INTO percona.example VALUES (3, 'percona3');
Query OK, 1 row affected (0.01 sec)
- node3
mysql> select * from example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
| 1 | percona1 |
| 2 | percona2 |
| 3 | percona3 |
+---------+-----------+
3 rows in set (0.00 sec)