PXC (Percona XtraDB Cluster) cluster installation

At the 2014Oracle database carnival, the author was fortunate enough to hear about the Web-based PXC high-availability architecture design theme for MySQL database.

MySQL and Oracle are two different kinds of databases, Oracle is more than management, and MySQL is more important than architecture design. The author of MySQL and new technology is full of curiosity, simply come back to build a test environment.

Operating system: CentOS 6.6

Software version: 5.5.39-36.0-55 perconaxtradb Cluster (GPL)

Node Information:

Pxc1	192.168.0.200
Pxc2	192.168.0.201
Pxc3	192.168.0.202

installation process

Install PXC, which uses the official repositories of Percona and Epel:

Http://www.percona.com/doc/percona-server/5.5/installation/yum_repo.html?id=repositories:yum

Http://fedoraproject.org/wiki/EPEL

Yum Installhttp://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpmyum Installhttp://mirrors.ustc.edu.cn/fedora/epel/6/i386/epel-release-6-8.noarch.rpmyum-y Install Percona-xtradb-cluster-serverpercona-xtradb-cluster-client Percona-server-shared-compat Percona-xtrabackup

Each machine turns on PXC and then adds a SST account:

DELETE from Mysql.user WHERE user= '; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' sstuser ' @ ' localhost ' identified by ' sstuser ';; FLUSH privileges;

Each node shuts down PXC. Edit the PXC setting for the first node.

[mysqld]server_id=1wsrep_provider=/usr/lib64/libgalera_smm.sowsrep_cluster_address= "gcomm:// 192.168.0.200,192.168.0.201,192.168.0.202 "wsrep_sst_auth=sstuser:sstuserwsrep_provider_options=" Gcache.size=4G "Wsrep_cluster_name=perconawsrep_sst_method=xtrabackupwsrep_node_name=pxc1wsrep_slave_threads=4log_slave_ updatesinnodb_locks_unsafe_for_binlog=1innodb_autoinc_lock_mode=2

Turn on the first node (if the cluster shuts down, each node with the correct information uses this command to turn on the cluster)

[[Email protected] ~]# service MySQL bootstrap-pxcbootstrapping PXC (Percona xtradbcluster) starting MySQL (Percona XtraDB Cluster).. [  OK  

Edit the contents of the MY.CNF configuration file for the other two nodes to be modified:

Server_id=2wsrep_node_name=pxc2

Adding two additional nodes to the cluster

Service MySQL Start

This completes the cluster installation.

Problems encountered

Here are the error messages and workarounds during the configuration process:

Phenomenon:

When you open the PXC node, you encounter

Failed to read output of: ' IP addr Show | Grep-e ' ^[]*inet ' | Grep-m1global | awk ' {print $} ' | Sed-e ' s/\/.*//' ..... 141120 22:46:35 [ERROR] wsrep:permission denied141120 22:46:35 [ERROR] wsrep:failed to open Gcomm backend connection:13 : Error while trying to listen ' tcp://0.0.0.0:4567?socket.non_blocking=1 ', Asioerror ' Permission denied ': Permission D enied)

Reason:

Because SELinux is turned on.

Workaround:

Edit File /etc/sysconfig/selinux

put SELINUX set to Disabled

After reboot, the SELinux is ignored or the command is executed directly

Setenforce 0

Effective immediately.

Phenomenon:

When you add a node to the cluster, you encounter

Last inactive check Morethan pt1.5s ago (pt3.50529s), skipping check

Here's the card.

Reason:

Iptables

Workaround:

Close Iptables

Chkconfig iptables off

Service Iptables Stop

Simple test

[[email protected] ~]# mysqlwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 7Server version:5.5.39-36.0-55 Percona XtraDB Cluster (GPL), Release rel36.0, Revision 824, Wsrep version 25.11, Wsrep_25.11.r4023copyright (c) 2009-2014 Percona LLC and/or its affiliatescopyright (c) 2000, 20 Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show global status like ' wsrep_cluster_size '; +------------------- -+-------+| variable_name | Value |+--------------------+-------+| Wsrep_cluster_size | 2 |+--------------------+-------+1 row in Set (0.00 sec) mysql> Show global status like ' wsrep_cluster_size '; +------ --------------+-------+| variable_name | Value |+--------------------+-------+| Wsrep_cluster_size | 3 |+--------------------+-------+1 row in Set (0.00 sec) mysql> CREATE database dexdb;   Query OK, 1 row affected (0.01 sec) mysql> use dexdbdatabase changedmysql> CREATE TABLE DEXTB (id int, name char (10)) Engine=innodb; Query OK, 0 rows affected (0.03 sec) mysql> insert into DEXTB values (1, ' 22 '); Query OK, 1 row affected (0.01 sec) mysql> insert into DEXTB values (1, ' 22 '); Query OK, 1 row Affected (0.00 sec) Mysql> commit; Query OK, 0 rows Affected (0.00 sec) mysql> select * from DEXTB; +------+------+| ID |    Name |+------+------+| 1 |    22 | | 1 |  |+------+------+2 rows in Set (0.00 sec) [[email protected] ~]# mysqlwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 6Server version:5.5.39-36.0-55 Percona XtraDB Cluster (GPL), Release rel36.0, Revision 824, Wsrep version 25.11, Wsrep_25.11.r4023copyright (c) 2009-2014 Percona LLC and/or its affiliatescopyright (c) 2000, 20 Oracle and/or its affiliates. All rights reserved. OrAcle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> show database; Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' database ' at line 1mysql& Gt show databases; +--------------------+| Database |+--------------------+| Information_schema | | Dexdb | | MySQL | | Performance_schema | | Test |+--------------------+5 rows in Set (0.01 sec) mysql> use dexdbreading table information for complet Ion of table and column namesyou can turn off this feature to get a quicker startup With-adatabase changedmysql> Selec T * from DEXTB, +------+------+| ID |    Name |+------+------+| 1 |    22 | | 1 | |+------+------+2 rows in Set (0.00 sec) [[email protected] ~]# ServiceMySQL startmysql (Percona XtraDB Cluster) is isn't running, but lock file (/var/lock/subsys/mysql) exists[failed]starting My SQL (Percona XtraDB Cluster) ..... [OK]  [[email protected] ~]# mysqlwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 6Server version:5.5.39-36.0-55 Percona XtraDB Cluster (GPL), Release rel36.0, Revision 824, Wsrep version 25.11, Wsrep_25.11.r4023copyright (c) 2009-2014 Percona LLC and/or its affiliatescopyright (c) 2000, 20 Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> use dexdbreading table information for completion of table and Co Lumn namesyou can turn off this feature to get a quicker startup With-adatabase changedmysql> select * from DEXTB; +-- ----+------+| ID |    Name |+------+------+|1 |    22 | | 1 | |+------+------+2 rows in Set (0.00 sec)

PXC (Percona XtraDB Cluster) cluster installation