如题所示，本文详细呈现部署MySQL集群：Percona XtraDB Cluster 的技术细节。
环境说明：
mysql1，192.168.232.147；
mysql2，192.168.232.148；
mysql3，192.168.232.149；
操作系统均为：RHEL 6.8，64 Bit。
MySQL版本：Percona server 5.7。
——————————————————————
（在所有三个节点上安装Percona）
系统检查与配置：
[root@mysql1 ~]# cat /etc/redhat-release CentOS release 6.8 (Final) [root@mysql1 ~]# [root@mysql1 ~]# sestatus SELinux status: disabled [root@mysql1 ~]# [root@mysql1 ~]# service iptables status iptables: Firewall is not running. [root@mysql1 ~]# [root@mysql1 ~]# route -n Kernel IP routing table Destination Gateway Genmask Flags Metric Ref Use Iface 192.168.232.0 0.0.0.0 255.255.255.0 U 1 0 0 eth0 0.0.0.0 192.168.232.2 0.0.0.0 UG 0 0 0 eth0 [root@mysql1 ~]# [root@mysql1 ~]# nslookup baidu.com Server: 192.168.232.2 Address: 192.168.232.2#53 Non-authoritative answer: Name: baidu.com Address: 111.13.101.208 Name: baidu.com Address: 220.181.57.217 Name: baidu.com Address: 180.149.132.47 Name: baidu.com Address: 123.125.114.144 [root@mysql1 ~]# [root@mysql1 ~]# ping -c 3 baidu.com PING baidu.com (123.125.114.144) 56(84) bytes of data. 64 bytes from 123.125.114.144: icmp_seq=1 ttl=128 time=246 ms 64 bytes from 123.125.114.144: icmp_seq=2 ttl=128 time=108 ms 64 bytes from 123.125.114.144: icmp_seq=3 ttl=128 time=208 ms --- baidu.com ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2212ms rtt min/avg/max/mdev = 108.031/187.621/246.688/58.438 ms [root@mysql1 ~]# [root@mysql1 ~]# cat /etc/hosts # Localhost 127.0.0.1 localhost # Pub 192.168.232.147 mysql1 192.168.232.148 mysql2 192.168.232.149 mysql3 [root@mysql1 ~]#
使用：Percona YUM源：
[root@mysql1 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Install Process percona-release-0.1-3.noarch.rpm | 6.4 kB 00:00 Examining /var/tmp/yum-root-rYUFt2/percona-release-0.1-3.noarch.rpm: percona-release-0.1-3.noarch Marking /var/tmp/yum-root-rYUFt2/percona-release-0.1-3.noarch.rpm to be installed Loading mirror speeds from cached hostfile * base: mirrors.163.com * extras: mirrors.163.com * updates: ftp.sjtu.edu.cn Resolving Dependencies --> Running transaction check ---> Package percona-release.noarch 0:0.1-3 will be installed --> Finished Dependency Resolution Dependencies Resolved ========================================================================================== Package Arch Version Repository Size ========================================================================================== Installing: percona-release noarch 0.1-3 /percona-release-0.1-3.noarch 5.8 k Transaction Summary ========================================================================================== Install 1 Package(s) Total size: 5.8 k Installed size: 5.8 k Is this ok [y/N]: y Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : percona-release-0.1-3.noarch 1/1 Verifying : percona-release-0.1-3.noarch 1/1 Installed: percona-release.noarch 0:0.1-3 Complete! [root@mysql1 ~]#
查看YUM源：
[root@mysql1 ~]# ls -ltr /etc/yum.repos.d/ total 28 -rw-r--r-- 1 root root 2501 Sep 22 2014 percona-release.repo -rw-r--r--. 1 root root 6259 May 18 12:47 CentOS-Vault.repo -rw-r--r--. 1 root root 630 May 18 12:47 CentOS-Media.repo -rw-r--r--. 1 root root 289 May 18 12:47 CentOS-fasttrack.repo -rw-r--r--. 1 root root 647 May 18 12:47 CentOS-Debuginfo.repo -rw-r--r--. 1 root root 1991 May 18 12:47 CentOS-Base.repo [root@mysql1 ~]# [root@mysql1 ~]# yum repolist Loaded plugins: fastestmirror, refresh-packagekit, security Loading mirror speeds from cached hostfile * base: mirrors.163.com * extras: mirrors.163.com * updates: ftp.sjtu.edu.cn repo id repo name status base CentOS-6 - Base 6,696 extras CentOS-6 - Extras 62 percona-release-noarch Percona-Release YUM repository - noarch 40 percona-release-x86_64 Percona-Release YUM repository - x86_64 886 updates CentOS-6 - Updates 245 repolist: 7,929 [root@mysql1 ~]#
安装：Percona XtraDB Cluster。
确保之前没有安装Percona的产品：
[root@mysql1 ~]# rpm -qa | grep --color Percona [root@mysql1 ~]#
确保之前没有安装MySQL的相关产品：
[root@mysql1 ~]# rpm -qa | grep --color mysql mysql-libs-5.1.73-7.el6.x86_64 [root@mysql1 ~]# [root@mysql1 ~]# yum remove -y mysql-libs-5.1.73-7.el6.x86_64 Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Remove Process Resolving Dependencies --> Running transaction check ---> Package mysql-libs.x86_64 0:5.1.73-7.el6 will be erased --> Processing Dependency: libmysqlclient.so.16()(64bit) for package: 2:postfix-2.6.6-6.el6_7.1.x86_64 --> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: 2:postfix-2.6.6-6.el6_7.1.x86_64 --> Processing Dependency: mysql-libs for package: 2:postfix-2.6.6-6.el6_7.1.x86_64 --> Running transaction check ---> Package postfix.x86_64 2:2.6.6-6.el6_7.1 will be erased --> Processing Dependency: /usr/sbin/sendmail for package: cronie-1.4.4-15.el6_7.1.x86_64 --> Running transaction check ---> Package cronie.x86_64 0:1.4.4-15.el6_7.1 will be erased --> Processing Dependency: cronie = 1.4.4-15.el6_7.1 for package: cronie-anacron-1.4.4-15.el6_7.1.x86_64 --> Running transaction check ---> Package cronie-anacron.x86_64 0:1.4.4-15.el6_7.1 will be erased --> Processing Dependency: /etc/cron.d for package: crontabs-1.10-33.el6.noarch --> Processing Dependency: /etc/cron.d for package: sysstat-9.0.4-31.el6.x86_64 --> Restarting Dependency Resolution with new changes. --> Running transaction check ---> Package crontabs.noarch 0:1.10-33.el6 will be erased ---> Package sysstat.x86_64 0:9.0.4-31.el6 will be erased --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================== Package Arch Version Repository Size ======================================================================================================================== Removing: mysql-libs x86_64 5.1.73-7.el6 @anaconda-CentOS-201605220104.x86_64/6.8 4.0 M Removing for dependencies: cronie x86_64 1.4.4-15.el6_7.1 @anaconda-CentOS-201605220104.x86_64/6.8 174 k cronie-anacron x86_64 1.4.4-15.el6_7.1 @anaconda-CentOS-201605220104.x86_64/6.8 43 k crontabs noarch 1.10-33.el6 @anaconda-CentOS-201605220104.x86_64/6.8 2.4 k postfix x86_64 2:2.6.6-6.el6_7.1 @anaconda-CentOS-201605220104.x86_64/6.8 9.7 M sysstat x86_64 9.0.4-31.el6 @anaconda-CentOS-201605220104.x86_64/6.8 826 k Transaction Summary ======================================================================================================================== Remove 6 Package(s) Installed size: 15 M Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Erasing : sysstat-9.0.4-31.el6.x86_64 1/6 Erasing : crontabs-1.10-33.el6.noarch 2/6 Erasing : cronie-anacron-1.4.4-15.el6_7.1.x86_64 3/6 Erasing : cronie-1.4.4-15.el6_7.1.x86_64 4/6 Erasing : 2:postfix-2.6.6-6.el6_7.1.x86_64 5/6 Erasing : mysql-libs-5.1.73-7.el6.x86_64 6/6 Verifying : cronie-1.4.4-15.el6_7.1.x86_64 1/6 Verifying : mysql-libs-5.1.73-7.el6.x86_64 2/6 Verifying : cronie-anacron-1.4.4-15.el6_7.1.x86_64 3/6 Verifying : crontabs-1.10-33.el6.noarch 4/6 Verifying : sysstat-9.0.4-31.el6.x86_64 5/6 Verifying : 2:postfix-2.6.6-6.el6_7.1.x86_64 6/6 Removed: mysql-libs.x86_64 0:5.1.73-7.el6 Dependency Removed: cronie.x86_64 0:1.4.4-15.el6_7.1 cronie-anacron.x86_64 0:1.4.4-15.el6_7.1 crontabs.noarch 0:1.10-33.el6 postfix.x86_64 2:2.6.6-6.el6_7.1 sysstat.x86_64 0:9.0.4-31.el6 Complete! [root@mysql1 ~]#
或者：
rpm -e mysql-libs-5.1.73-7.el6.x86_64
安装依赖包：
[root@mysql1 ~]# wget http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
--2016-07-16 01:05:01-- http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
Resolving download.fedoraproject.org... 140.211.169.196, 174.141.234.172, 5.175.150.50, ...
Connecting to download.fedoraproject.org|140.211.169.196|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://mirrors.neusoft.edu.cn/epel/6/i386/epel-release-6-8.noarch.rpm [following]
--2016-07-16 01:05:04-- http://mirrors.neusoft.edu.cn/epel/6/i386/epel-release-6-8.noarch.rpm
Resolving mirrors.neusoft.edu.cn... 219.216.128.25, 2001:da8:a807::25
Connecting to mirrors.neusoft.edu.cn|219.216.128.25|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 14540 (14K) [application/x-redhat-package-manager]
Saving to: “epel-release-6-8.noarch.rpm”
100%[==============================================================================>] 14,540 --.-K/s in 0.01s
2016-07-16 01:05:04 (1.35 MB/s) - “epel-release-6-8.noarch.rpm” saved [14540/14540]
[root@mysql1 ~]#
[root@mysql1 ~]# rpm -ivh epel-release-6-8.noarch.rpm
warning: epel-release-6-8.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing... ########################################### [100%]
1:epel-release ########################################### [100%]
[root@mysql1 ~]#
[root@mysql1 ~]# yum repolist
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* epel: mirrors.neusoft.edu.cn
* extras: mirrors.163.com
* updates: ftp.sjtu.edu.cn
repo id repo name status
base CentOS-6 - Base 6,696
epel Extra Packages for Enterprise Linux 6 - x86_64 12,151
extras CentOS-6 - Extras 62
percona-release-noarch Percona-Release YUM repository - noarch 40
percona-release-x86_64 Percona-Release YUM repository - x86_64 886
updates CentOS-6 - Updates 245
repolist: 20,080
[root@mysql1 ~]#
[root@mysql1 ~]# yum list | grep --color socat
socat.x86_64 1.7.2.3-1.el6 epel
[root@mysql1 ~]#
[root@mysql1 ~]# yum install -y socat
Loaded plugins: fastestmirror, refresh-packagekit, security
Setting up Install Process
Loading mirror speeds from cached hostfile
* base: mirrors.163.com
* epel: mirrors.neusoft.edu.cn
* extras: mirrors.163.com
* updates: ftp.sjtu.edu.cn
Resolving Dependencies
--> Running transaction check
---> Package socat.x86_64 0:1.7.2.3-1.el6 will be installed
--> Processing Dependency: libreadline.so.5()(64bit) for package: socat-1.7.2.3-1.el6.x86_64
--> Running transaction check
---> Package compat-readline5.x86_64 0:5.2-17.1.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=======================================================================================
Package Arch Version Repository Size
=======================================================================================
Installing:
socat x86_64 1.7.2.3-1.el6 epel 246 k
Installing for dependencies:
compat-readline5 x86_64 5.2-17.1.el6 base 130 k
Transaction Summary
=======================================================================================
Install 2 Package(s)
Total download size: 375 k
Installed size: 1.2 M
Downloading Packages:
(1/2): compat-readline5-5.2-17.1.el6.x86_64.rpm | 130 kB 00:02
(2/2): socat-1.7.2.3-1.el6.x86_64.rpm | 246 kB 00:07
---------------------------------------------------------------------------------------
Total 36 kB/s | 375 kB 00:10
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)
Package: epel-release-6-8.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID c105b9de: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Importing GPG key 0xC105B9DE:
Userid : CentOS-6 Key (CentOS 6 Official Signing Key)
Package: centos-release-6-8.el6.centos.12.3.x86_64 (@anaconda-CentOS-201605220104.x86_64/6.8)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Installing : compat-readline5-5.2-17.1.el6.x86_64 1/2
Installing : socat-1.7.2.3-1.el6.x86_64 2/2
Verifying : socat-1.7.2.3-1.el6.x86_64 1/2
Verifying : compat-readline5-5.2-17.1.el6.x86_64 2/2
Installed:
socat.x86_64 0:1.7.2.3-1.el6
Dependency Installed:
compat-readline5.x86_64 0:5.2-17.1.el6
Complete!
[root@mysql1 ~]#
[root@mysql1 ~]# wget ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/rudi_m/CentOS_CentOS-6/x86_64/libev4-4.15-7.1.x86_64.rpm
--2016-07-16 01:29:57-- ftp://ftp.pbone.net/mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/rudi_m/CentOS_CentOS-6/x86_64/libev4-4.15-7.1.x86_64.rpm
=> “libev4-4.15-7.1.x86_64.rpm.1”
Resolving ftp.pbone.net... 85.14.85.4
Connecting to ftp.pbone.net|85.14.85.4|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done. ==> PWD ... done.
==> TYPE I ... done. ==> CWD (1) /mirror/ftp5.gwdg.de/pub/opensuse/repositories/home:/rudi_m/CentOS_CentOS-6/x86_64 ... done.
==> SIZE libev4-4.15-7.1.x86_64.rpm ... 36560
==> PASV ... done. ==> RETR libev4-4.15-7.1.x86_64.rpm ... done.
Length: 36560 (36K) (unauthoritative)
100%[=============================================>] 36,560 45.4K/s in 0.8s
2016-07-16 01:31:00 (45.4 KB/s) - “libev4-4.15-7.1.x86_64.rpm.1” saved [36560]
[root@mysql1 ~]#
[root@mysql1 ~]# rpm -ivh libev*
warning: libev4-4.15-7.1.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID b611872d: NOKEY
Preparing... ########################################### [100%]
1:libev4 ########################################### [100%]
[root@mysql1 ~]#
使用YUM，安装Percona XtraDB Cluster：
[root@mysql1 ~]# yum list | grep --color Percona-XtraDB-Cluster Percona-XtraDB-Cluster-55.x86_64 1:5.5.41-25.11.853.el6 percona-release-x86_64 Percona-XtraDB-Cluster-55-debuginfo.x86_64 1:5.5.41-25.11.853.el6 percona-release-x86_64 Percona-XtraDB-Cluster-56.x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 Percona-XtraDB-Cluster-56-debuginfo.x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 Percona-XtraDB-Cluster-client.x86_64 1:5.5.34-23.7.6.565.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-client-55.x86_64 1:5.5.41-25.11.853.el6 percona-release-x86_64 Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 Percona-XtraDB-Cluster-debuginfo.x86_64 1:5.5.34-23.7.6.565.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-devel.x86_64 1:5.5.34-23.7.6.565.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-devel-55.x86_64 1:5.5.41-25.11.853.el6 percona-release-x86_64 Percona-XtraDB-Cluster-devel-56.x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 Percona-XtraDB-Cluster-full-55.x86_64 1:5.5.41-25.11.853.el6 percona-release-x86_64 Percona-XtraDB-Cluster-full-56.x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 Percona-XtraDB-Cluster-galera.x86_64 2.8-1.162.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-galera-2.x86_64 2.12-1.2682.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-galera-2-debuginfo.x86_64 Percona-XtraDB-Cluster-galera-3.x86_64 3.16-1.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-galera-3-debuginfo.x86_64 Percona-XtraDB-Cluster-galera-56.x86_64 3.1-1.169.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-galera-56-debuginfo.x86_64 Percona-XtraDB-Cluster-galera-debuginfo.x86_64 Percona-XtraDB-Cluster-garbd-2.x86_64 2.12-1.2682.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-garbd-3.x86_64 3.16-1.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-server.x86_64 1:5.5.34-23.7.6.565.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-server-55.x86_64 1:5.5.41-25.11.853.el6 percona-release-x86_64 Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 Percona-XtraDB-Cluster-shared.x86_64 1:5.5.34-23.7.6.565.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-shared-55.x86_64 1:5.5.41-25.11.853.el6 percona-release-x86_64 Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 Percona-XtraDB-Cluster-test.x86_64 1:5.5.34-23.7.6.565.rhel6 percona-release-x86_64 Percona-XtraDB-Cluster-test-55.x86_64 1:5.5.41-25.11.853.el6 percona-release-x86_64 Percona-XtraDB-Cluster-test-56.x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 [root@mysql1 ~]# [root@mysql1 ~]# yum install -y Percona-XtraDB-Cluster-56 Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Install Process Loading mirror speeds from cached hostfile * base: mirrors.163.com * epel: mirror.premi.st * extras: mirrors.163.com * updates: ftp.sjtu.edu.cn Resolving Dependencies --> Running transaction check ---> Package Percona-XtraDB-Cluster-56.x86_64 1:5.6.30-25.16.1.el6 will be installed --> Processing Dependency: Percona-XtraDB-Cluster-client-56 for package: 1:Percona-XtraDB-Cluster-56-5.6.30-25.16.1.el6.x86_64 --> Processing Dependency: Percona-XtraDB-Cluster-galera-3 for package: 1:Percona-XtraDB-Cluster-56-5.6.30-25.16.1.el6.x86_64 --> Processing Dependency: Percona-XtraDB-Cluster-server-56 for package: 1:Percona-XtraDB-Cluster-56-5.6.30-25.16.1.el6.x86_64 --> Running transaction check ---> Package Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.30-25.16.1.el6 will be installed --> Processing Dependency: perl-DBI for package: 1:Percona-XtraDB-Cluster-client-56-5.6.30-25.16.1.el6.x86_64 ---> Package Percona-XtraDB-Cluster-galera-3.x86_64 0:3.16-1.rhel6 will be installed ---> Package Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.30-25.16.1.el6 will be installed --> Processing Dependency: percona-xtrabackup >= 2.2.5 for package: 1:Percona-XtraDB-Cluster-server-56-5.6.30-25.16.1.el6.x86_64 --> Processing Dependency: Percona-XtraDB-Cluster-shared-56 for package: 1:Percona-XtraDB-Cluster-server-56-5.6.30-25.16.1.el6.x86_64 --> Processing Dependency: perl-DBD-MySQL for package: 1:Percona-XtraDB-Cluster-server-56-5.6.30-25.16.1.el6.x86_64 --> Running transaction check ---> Package Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.30-25.16.1.el6 will be installed ---> Package percona-xtrabackup.x86_64 0:2.3.5-1.el6 will be installed --> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-2.3.5-1.el6.x86_64 ---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed --> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64 --> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64 ---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed --> Running transaction check ---> Package Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6 will be installed ---> Package libev.x86_64 0:4.03-3.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================== Package Arch Version Repository Size ======================================================================================================== Installing: Percona-XtraDB-Cluster-56 x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 26 k Installing for dependencies: Percona-Server-shared-51 x86_64 5.1.73-rel14.12.625.rhel6 percona-release-x86_64 2.1 M Percona-XtraDB-Cluster-client-56 x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 6.4 M Percona-XtraDB-Cluster-galera-3 x86_64 3.16-1.rhel6 percona-release-x86_64 827 k Percona-XtraDB-Cluster-server-56 x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 20 M Percona-XtraDB-Cluster-shared-56 x86_64 1:5.6.30-25.16.1.el6 percona-release-x86_64 685 k libev x86_64 4.03-3.el6 epel 113 k percona-xtrabackup x86_64 2.3.5-1.el6 percona-release-x86_64 5.4 M perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k perl-DBI x86_64 1.609-4.el6 base 705 k Transaction Summary ======================================================================================================== Install 10 Package(s) Total download size: 36 M Installed size: 37 M Downloading Packages: (1/10): Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64.rpm | 2.1 MB 01:09 (2/10): Percona-XtraDB-Cluster-56-5.6.30-25.16.1.el6.x86_64.rpm | 26 kB 00:00 (3/10): Percona-XtraDB-Cluster-client-56-5.6.30-25.16.1.el6.x86_64.rpm | 6.4 MB 02:09 (4/10): Percona-XtraDB-Cluster-galera-3-3.16-1.rhel6.x86_64.rpm | 827 kB 00:17 (5/10): Percona-XtraDB-Cluster-server-56-5.6.30-25.16.1.el6.x86_64.rpm | 20 MB 14:03 (6/10): Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64.rpm | 685 kB 00:09 (7/10): libev-4.03-3.el6.x86_64.rpm | 113 kB 00:01 (8/10): percona-xtrabackup-2.3.5-1.el6.x86_64.rpm | 5.4 MB 03:51 (9/10): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:01 (10/10): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:02 -------------------------------------------------------------------------------------------------------- Total 28 kB/s | 36 MB 22:08 warning: rpmts_HdrFromFdno: Header 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 Package: percona-release-0.1-3.noarch (@/percona-release-0.1-3.noarch) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-Percona Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : perl-DBI-1.609-4.el6.x86_64 1/10 Installing : 1:Percona-XtraDB-Cluster-client-56-5.6.30-25.16.1.el6.x86_64 2/10 Installing : Percona-XtraDB-Cluster-galera-3-3.16-1.rhel6.x86_64 3/10 Installing : libev-4.03-3.el6.x86_64 4/10 Installing : 1:Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64 5/10 Installing : Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 6/10 Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 7/10 Installing : percona-xtrabackup-2.3.5-1.el6.x86_64 8/10 Installing : 1:Percona-XtraDB-Cluster-server-56-5.6.30-25.16.1.el6.x86_64 9/10 2016-07-16 01:56:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-07-16 01:56:53 0 [Note] /usr/sbin/mysqld (mysqld 5.6.30-76.3-56) starting as process 2820 ... 2016-07-16 01:56:53 2820 [Note] WSREP: Read nil XID from storage engines, skipping position init 2016-07-16 01:56:53 2820 [Note] WSREP: wsrep_load(): loading provider library 'none' 2016-07-16 01:56:53 2820 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-07-16 01:56:53 2820 [Note] InnoDB: The InnoDB memory heap is disabled 2016-07-16 01:56:53 2820 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-07-16 01:56:53 2820 [Note] InnoDB: Memory barrier is not used 2016-07-16 01:56:53 2820 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-07-16 01:56:53 2820 [Note] InnoDB: Using Linux native AIO 2016-07-16 01:56:53 2820 [Note] InnoDB: Using CPU crc32 instructions 2016-07-16 01:56:53 2820 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-07-16 01:56:53 2820 [Note] InnoDB: Completed initialization of buffer pool 2016-07-16 01:56:53 2820 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2016-07-16 01:56:53 2820 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2016-07-16 01:56:53 2820 [Note] InnoDB: Database physically writes the file full: wait... 2016-07-16 01:56:53 2820 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2016-07-16 01:56:54 2820 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2016-07-16 01:56:56 2820 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2016-07-16 01:56:56 2820 [Warning] InnoDB: New log files created, LSN=45781 2016-07-16 01:56:56 2820 [Note] InnoDB: Doublewrite buffer not found: creating new 2016-07-16 01:56:56 2820 [Note] InnoDB: Doublewrite buffer created 2016-07-16 01:56:56 2820 [Note] InnoDB: 128 rollback segment(s) are active. 2016-07-16 01:56:56 2820 [Warning] InnoDB: Creating foreign key constraint system tables. 2016-07-16 01:56:56 2820 [Note] InnoDB: Foreign key constraint system tables created 2016-07-16 01:56:56 2820 [Note] InnoDB: Creating tablespace and datafile system tables. 2016-07-16 01:56:56 2820 [Note] InnoDB: Tablespace and datafile system tables created. 2016-07-16 01:56:56 2820 [Note] InnoDB: Waiting for purge to start 2016-07-16 01:56:56 2820 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.30-76.3 started; log sequence number 0 2016-07-16 01:56:56 2820 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work. 2016-07-16 01:56:56 2820 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work. 2016-07-16 01:56:57 2820 [Note] WSREP: Service disconnected. 2016-07-16 01:56:58 2820 [Note] WSREP: Some threads may fail to exit. 2016-07-16 01:56:58 2820 [Note] Binlog end 2016-07-16 01:56:58 2820 [Note] InnoDB: FTS optimize thread exiting. 2016-07-16 01:56:58 2820 [Note] InnoDB: Starting shutdown... 2016-07-16 01:57:00 2820 [Note] InnoDB: Shutdown completed; log sequence number 1625977 2016-07-16 01:57:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2016-07-16 01:57:00 0 [Note] /usr/sbin/mysqld (mysqld 5.6.30-76.3-56) starting as process 2845 ... 2016-07-16 01:57:00 2845 [Note] WSREP: Read nil XID from storage engines, skipping position init 2016-07-16 01:57:00 2845 [Note] WSREP: wsrep_load(): loading provider library 'none' 2016-07-16 01:57:00 2845 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-07-16 01:57:00 2845 [Note] InnoDB: The InnoDB memory heap is disabled 2016-07-16 01:57:00 2845 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-07-16 01:57:00 2845 [Note] InnoDB: Memory barrier is not used 2016-07-16 01:57:00 2845 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-07-16 01:57:00 2845 [Note] InnoDB: Using Linux native AIO 2016-07-16 01:57:00 2845 [Note] InnoDB: Using CPU crc32 instructions 2016-07-16 01:57:00 2845 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2016-07-16 01:57:00 2845 [Note] InnoDB: Completed initialization of buffer pool 2016-07-16 01:57:00 2845 [Note] InnoDB: Highest supported file format is Barracuda. 2016-07-16 01:57:00 2845 [Note] InnoDB: 128 rollback segment(s) are active. 2016-07-16 01:57:00 2845 [Note] InnoDB: Waiting for purge to start 2016-07-16 01:57:00 2845 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.30-76.3 started; log sequence number 1625977 2016-07-16 01:57:00 2845 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work. 2016-07-16 01:57:00 2845 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work. 2016-07-16 01:57:00 2845 [Note] WSREP: Service disconnected. 2016-07-16 01:57:01 2845 [Note] WSREP: Some threads may fail to exit. 2016-07-16 01:57:01 2845 [Note] Binlog end 2016-07-16 01:57:01 2845 [Note] InnoDB: FTS optimize thread exiting. 2016-07-16 01:57:01 2845 [Note] InnoDB: Starting shutdown... 2016-07-16 01:57:03 2845 [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 mysql1 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 Installing : 1:Percona-XtraDB-Cluster-56-5.6.30-25.16.1.el6.x86_64 10/10 Verifying : Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 1/10 Verifying : 1:Percona-XtraDB-Cluster-server-56-5.6.30-25.16.1.el6.x86_64 2/10 Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 3/10 Verifying : 1:Percona-XtraDB-Cluster-56-5.6.30-25.16.1.el6.x86_64 4/10 Verifying : Percona-XtraDB-Cluster-galera-3-3.16-1.rhel6.x86_64 5/10 Verifying : 1:Percona-XtraDB-Cluster-shared-56-5.6.30-25.16.1.el6.x86_64 6/10 Verifying : perl-DBI-1.609-4.el6.x86_64 7/10 Verifying : 1:Percona-XtraDB-Cluster-client-56-5.6.30-25.16.1.el6.x86_64 8/10 Verifying : libev-4.03-3.el6.x86_64 9/10 Verifying : percona-xtrabackup-2.3.5-1.el6.x86_64 10/10 Installed: Percona-XtraDB-Cluster-56.x86_64 1:5.6.30-25.16.1.el6 Dependency Installed: Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6 Percona-XtraDB-Cluster-client-56.x86_64 1:5.6.30-25.16.1.el6 Percona-XtraDB-Cluster-galera-3.x86_64 0:3.16-1.rhel6 Percona-XtraDB-Cluster-server-56.x86_64 1:5.6.30-25.16.1.el6 Percona-XtraDB-Cluster-shared-56.x86_64 1:5.6.30-25.16.1.el6 libev.x86_64 0:4.03-3.el6 percona-xtrabackup.x86_64 0:2.3.5-1.el6 perl-DBD-MySQL.x86_64 0:4.013-3.el6 perl-DBI.x86_64 0:1.609-4.el6 Complete! [root@mysql1 ~]#
这样，安装就完成了。
接下来是配置部分。
节点一配置：
[root@mysql1 ~]# cat /etc/my.cnf [mysqld] #skip-grant-tables 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.232.147,192.168.232.148,192.168.232.149 # 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.232.147 # SST method wsrep_sst_method=xtrabackup-v2 #wsrep_sst_method=rsync # Cluster name wsrep_cluster_name=cluster_me # Authentication for SST method wsrep_sst_auth="sstuser:s3cret" [root@mysql1 ~]#
节点一，启动服务：
[root@mysql1 ~]# /etc/init.d/mysql bootstrap-pxc Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster). SUCCESS! [root@mysql1 ~]# [root@mysql1 ~]# ps -ef | grep mysql root 2973 1 0 02:33 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql1.pid --wsrep-new-cluster mysql 3217 2973 0 02:33 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/libgalera_smm.so --wsrep-new-cluster --log-error=/var/lib/mysql/mysql1.err --pid-file=/var/lib/mysql/mysql1.pid --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1 root 3254 2460 0 02:34 pts/0 00:00:00 grep mysql [root@mysql1 ~]# [root@mysql1 ~]# netstat -tupln | grep mysql tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 3217/mysqld tcp 0 0 :::3306 :::* LISTEN 3217/mysqld [root@mysql1 ~]#
节点一，查看状态：
[root@mysql1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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 | 65e1f0de-4b38-11e6-83b9-6797d791458e | | 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 | 145 | | 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 | 1320 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_incoming_addresses | 192.168.232.147:3306 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0/0/0/0/0 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | 65e14a42-4b38-11e6-923f-0bb1539c3b40 | | wsrep_cluster_conf_id | 1 | | wsrep_cluster_size | 1 | | wsrep_cluster_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | 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 | | wsrep_provider_version | 3.16(r5c765eb) | | wsrep_ready | ON | +------------------------------+--------------------------------------+ 59 rows in set (0.01 sec) mysql>
上面的MySQL是没有设置口令的，为了安全考虑，你可以设置密码：
mysql> update mysql.user set password=('Passw0rd') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql>
在节点一上，创建用于SST（State Snapshot Transfer）的账户，并分配权限：
mysql> create user 'sstuser'@'localhost' identified by 's3cret'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +---------+-----------+ | user | host | +---------+-----------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | sstuser | localhost | | | mysql1 | | root | mysql1 | +---------+-----------+ 7 rows in set (0.00 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) mysql>
创建用于SST的用户，不是必须的，你也可以用root用户执行。
但是建议用不同的用户去操作SST。
配置root用户的权限：
mysql> select user,host,authentication_string from mysql.user;
+---------+-----------+-----------------------+
| user | host | authentication_string |
+---------+-----------+-----------------------+
| root | localhost | |
| root | mysql1 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | NULL |
| | mysql1 | NULL |
| sstuser | localhost | |
+---------+-----------+-----------------------+
7 rows in set (0.00 sec)
mysql>
mysql> update user set password=password('Passw0rd') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql>
mysql> grant all privileges on *.* to root@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql>
节点二配置：
[root@mysql2 ~]# cat /etc/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.232.147,192.168.232.148,192.168.232.149 # 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.232.148 # Cluster name wsrep_cluster_name=cluster_me # SST method wsrep_sst_method=xtrabackup-v2 #wsrep_sst_method=rsync #Authentication for SST method wsrep_sst_auth="sstuser:s3cret" [root@mysql2 ~]#
然后，节点二启动服务：
[root@mysql2 ~]# ps -ef | grep mysql root 2739 2293 0 02:48 pts/0 00:00:00 grep mysql [root@mysql2 ~]# [root@mysql2 ~]# /etc/init.d/mysql start Starting MySQL (Percona XtraDB Cluster)..State transfer in progress, setting sleep higher .. SUCCESS! [root@mysql2 ~]# [root@mysql2 ~]# ps -ef | grep mysql root 2752 1 0 02:49 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql2.pid mysql 2981 2752 2 02:49 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/libgalera_smm.so --log-error=/var/lib/mysql/mysql2.err --pid-file=/var/lib/mysql/mysql2.pid --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1 root 3398 2293 0 02:49 pts/0 00:00:00 grep mysql [root@mysql2 ~]# [root@mysql2 ~]# netstat -tupln | grep --color mysql tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2981/mysqld tcp 0 0 :::3306 :::* LISTEN 2981/mysqld [root@mysql2 ~]#
查看节点二的状态：
[root@mysql2 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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> mysql> show status like 'wsrep%'; +------------------------------+-------------------------------------------+ | Variable_name | Value | +------------------------------+-------------------------------------------+ | wsrep_local_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | 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 | 226 | | 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 | 1454 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_incoming_addresses | 192.168.232.147:3306,192.168.232.148:3306 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0/0/0/0/0 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | 8774d07c-4b3a-11e6-a054-6ab104129edb | | wsrep_cluster_conf_id | 2 | | wsrep_cluster_size | 2 | | wsrep_cluster_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 1 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy | | wsrep_provider_version | 3.16(r5c765eb) | | wsrep_ready | ON | +------------------------------+-------------------------------------------+ 59 rows in set (0.00 sec) mysql>
这样，节点二就配置好了。
节点三配置：
[[root@mysql3 ~]# cat /etc/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.232.147,192.168.232.148,192.168.232.149 # 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.232.149 # Cluster name wsrep_cluster_name=cluster_me # SST method wsrep_sst_method=xtrabackup-v2 #wsrep_sst_method=rsync #Authentication for SST method wsrep_sst_auth="sstuser:s3cret" [root@mysql3 ~]#
节点三，启动服务：
[root@mysql3 ~]# ps -ef | grep mysql root 2747 2210 0 03:04 pts/0 00:00:00 grep mysql [root@mysql3 ~]# [root@mysql3 ~]# /etc/init.d/mysql start Starting MySQL (Percona XtraDB Cluster)...State transfer in progress, setting sleep higher .. SUCCESS! [root@mysql3 ~]# [root@mysql3 ~]# ps -ef | grep mysql root 2760 1 0 03:05 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql3.pid mysql 2989 2760 2 03:05 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/libgalera_smm.so --log-error=/var/lib/mysql/mysql3.err --pid-file=/var/lib/mysql/mysql3.pid --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1 root 3409 2210 0 03:05 pts/0 00:00:00 grep mysql [root@mysql3 ~]# [root@mysql3 ~]# netstat -tupln | grep --color mysql tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2989/mysqld tcp 0 0 :::3306 :::* LISTEN 2989/mysqld [root@mysql3 ~]#
节点三，查看服务状态：
[root@mysql3 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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 | 65e1f0de-4b38-11e6-83b9-6797d791458e | | 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 | 299 | | 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 | 1454 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_incoming_addresses | 192.168.232.147:3306,192.168.232.148:3306,192.168.232.149:3306 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0/0/0/0/0 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | c61da911-4b3c-11e6-b207-c3e2f9e6f5cb | | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 2 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy | | wsrep_provider_version | 3.16(r5c765eb) | | wsrep_ready | ON | +------------------------------+----------------------------------------------------------------+ 59 rows in set (0.00 sec) mysql>
到这里，Percona XtraDB Cluster的所有配置都完成了。
查询下状态：
节点一：
[root@mysql1 ~]# mysql -u root -p'Passw0rd' Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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 global status like 'wsrep_cluster%'; +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | wsrep_cluster_status | Primary | +--------------------------+--------------------------------------+ 4 rows in set (0.00 sec) mysql> mysql> show global status like 'wsrep_local_%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | wsrep_local_commits | 2 | | 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 | 7 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 0 | +----------------------------+--------------------------------------+ 17 rows in set (0.00 sec) mysql> show global status like 'wsrep_ready'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wsrep_ready | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql>
节点二：
[root@mysql2 ~]# mysql -u root -p'Passw0rd' Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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 global status like 'wsrep_cluster%'; +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | wsrep_cluster_status | Primary | +--------------------------+--------------------------------------+ 4 rows in set (0.00 sec) mysql> mysql> show global status like 'wsrep_local_%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | 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.142857 | | wsrep_local_cached_downto | 7 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 1 | +----------------------------+--------------------------------------+ 17 rows in set (0.00 sec) mysql> show global status like 'wsrep_ready'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wsrep_ready | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql>
节点三：
[root@mysql3 ~]# mysql -u root -p'Passw0rd' Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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 global status like 'wsrep_cluster%'; +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_conf_id | 3 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | wsrep_cluster_status | Primary | +--------------------------+--------------------------------------+ 4 rows in set (0.00 sec) mysql> mysql> show global status like 'wsrep_local_%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | 65e1f0de-4b38-11e6-83b9-6797d791458e | | 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 | 7 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 2 | +----------------------------+--------------------------------------+ 17 rows in set (0.00 sec) mysql> show global status like 'wsrep_ready'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wsrep_ready | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql>
测试：
这三个节点的MySQL现在是集群。
所以，我们的测试：
1. 在第二个节点上，创建数据库
2. 在第三个节点上，新建表
3. 在第一个节点上，插入数据
4. 然后在所有节点，查询数据
创建数据库。
节点二：
[root@mysql2 ~]# mysql -u root -p'Passw0rd' Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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> mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> mysql> create database dilraba; Query OK, 1 row affected (0.01 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dilraba | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql>
节点三，新建表：
[root@mysql3 ~]# mysql -u root -p'Passw0rd' Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dilraba | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> mysql> create table dilraba.people(username varchar(30),location varchar(60)); Query OK, 0 rows affected (0.10 sec) mysql> mysql> use dilraba; 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> mysql> show tables; +-------------------+ | Tables_in_dilraba | +-------------------+ | people | +-------------------+ 1 row in set (0.00 sec) mysql> desc people; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | username | varchar(30) | YES | | NULL | | | location | varchar(60) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql>
节点一，插入数据：
[root@mysql1 ~]# mysql -u root -p'Passw0rd'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dilraba |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.03 sec)
mysql>
mysql> desc dilraba.people;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(30) | YES | | NULL | |
| location | varchar(60) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>
mysql> insert into dilraba.people value ('Dilraba','China');
Query OK, 1 row affected (0.01 sec)
mysql> insert into dilraba.people values ('Gulnazar','China');
Query OK, 1 row affected (0.07 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dilraba.people;
+----------+----------+
| username | location |
+----------+----------+
| Dilraba | China |
| Gulnazar | China |
+----------+----------+
2 rows in set (0.00 sec)
mysql>
其他节点查询：
节点二：
[root@mysql2 ~]# mysql -u root -p'Passw0rd' Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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> select * from dilraba.people; +----------+----------+ | username | location | +----------+----------+ | Dilraba | China | | Gulnazar | China | +----------+----------+ 2 rows in set (0.00 sec) mysql>
节点三：
[root@mysql3 ~]# mysql -u root -p'Passw0rd' Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.30-76.3-56 Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, 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> select * from dilraba.people; +----------+----------+ | username | location | +----------+----------+ | Dilraba | China | | Gulnazar | China | +----------+----------+ 2 rows in set (0.00 sec) mysql>
至此，Percona XtraDB Cluster的搭建与测试，全部完成。
——————————————————
本文参考：
https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/centos_howto.html
——————————————————
Done。