Percona XtraDB Cluster Installation

為了存些大量的資料，又不想日後煩惱 DB HA/scale 問題，先來試用一下這套看看。
在安裝上其實還蠻容易的，官方也有寫安裝文件，感覺十分貼心。

參考文件：
http://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/ubuntu_howto.html

1. 事前準備：
Ubuntu 14.04 LTS servers: pxc1(192.168.3.12), pxc2(192.168.3.13), pxc3(192.168.3.14)
注意 SELinux, Apparmor 及 firewall 設定，開放 ports: 3306, 4444, 4567 and 4568

2. Installation:

2.1. 新增簽章金鑰

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

2.2. prepare percona apt repository
add to /etc/apt/sources.list

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

$ apt-get update

2.3. install package

$ apt-get install percona-xtradb-cluster-56

or

$ apt-get install percona-xtradb-cluster-full-56

3. Setup

3.1. stop mysql running on all nodes
$ /etc/init.d/mysql stop

3.2. first node configuration
root@pxc1:~# cat /etc/mysql/my.cnf
===
[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://

# 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.3.12

wsrep_node_name=pxc1

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_PXC

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cretPass"

===

3.3. start first node

root@pxc1:~# /etc/init.d/mysql bootstrap-pxc

3.4. grant privilege

mysql@pxc1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
mysql@pxc1> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql@pxc1> FLUSH PRIVILEGES;

3.5 2nd and 3rd node configuration

===

[mysqld]

datadir=/var/lib/mysql

user=mysql

# Path to Galera library

wsrep_provider=/usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address=gcomm://192.168.3.12  #first node IP

# 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.3.13 #change as you need

wsrep_node_name=pxc2 #change as you need

# SST method

wsrep_sst_method=xtrabackup-v2

# Cluster name

wsrep_cluster_name=my_PXC

# Authentication for SST method

wsrep_sst_auth="sstuser:s3cretPass"

===

3.6. start 2nd and 3rd node mysq service

$ /etc/init.d/mysql start

3.7. check if all three node register

3.8. final configuration

change all nodes cluster address to

wsrep_cluster_address=gcomm://192.168.3.12,192.168.3.13,192.168.3.14