Install the PXC (PerconaXtraDBCluster) Cluster

The author was lucky enough to hear about the PXC-based high-availability architecture design of MySQL databases on the network. MySQL and Oracle are two completely different databases. Oracle is more important than management, while MySQL is more important to architecture design. I am curious about MySQL and new technologies. I just came back to build a test environment.

The author was lucky enough to hear about the PXC-based high-availability architecture design of MySQL databases on the network. MySQL and Oracle are two completely different databases. Oracle is more important than management, while MySQL is more important to architecture design. I am curious about MySQL and new technologies. I just came back to build a test environment.

The author was lucky enough to hear about the PXC-based high-availability architecture design of MySQL databases on the network.

MySQL and Oracle are two completely different databases. Oracle is more important than management, while MySQL is more important to architecture design. I am curious about MySQL and new technologies. I just came 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. Here Percona and EPEL official repositories are used:

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

Start PXC on each machine and add an SST account:

DELETE FROM mysql.user WHERE user='';GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost' IDENTIFIED BY 'sstuser';;FLUSH PRIVILEGES;

Disable PXC for each node. Edit the PXC settings of 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

Enable the first node (if the cluster is closed, use this command to enable the cluster for each node with the correct information)

[root@pxc1 ~]# service mysql bootstrap-pxcBootstrapping PXC (Percona XtraDBCluster)Starting MySQL (Percona XtraDB Cluster)..[  OK  ] 

Edit the content to be modified in the my. cnf configuration file of the other two nodes:

server_id=2wsrep_node_name=pxc2

Add the other two nodes to the Cluster

service mysql start

Now, the cluster installation is complete.

Problems encountered

The following are error messages during configuration and solutions:

Symptom:

When the PXC node is enabled

Failed to read output of: 'ip addr show | grep -E '^[ ]*inet' | grep -m1global | awk '{ print $2 }' | 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': 13 (Permission denied)

Cause:

SELINUX is enabled.

Solution:

Edit the/etc/sysconfig/selinux File

Set SELINUX to disabled

After the restart, selinux is ignored or the command is directly executed.

Setenforce 0

Effective immediately.

Symptom:

When a node is added to a cluster

Last inactive check morethan PT1.5S ago (PT3.50529S), skipping check

Stuck here

Cause:

Iptables

Solution:

Disable iptables

Chkconfig iptables off

Service iptables stop

Simple Test

[root@pxc1 ~]# 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, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be 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 | 22   |+------+------+2 rows in set (0.00 sec)[root@pxc2 ~]# 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, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be 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 an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1mysql> show databases ;+--------------------+| Database           |+--------------------+| information_schema || dexdb              || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.01 sec)mysql> use dexdbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from dextb ;+------+------+| id   | name |+------+------+|    1 | 22   ||    1 | 22   |+------+------+2 rows in set (0.00 sec)[root@pxc3 ~]# service mysql startMySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists[FAILED]Starting MySQL (Percona XtraDB Cluster).....[  OK  ][root@pxc3 ~]# 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, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be 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 column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from dextb ;+------+------+| id   | name |+------+------+|    1 | 22   ||    1 | 22   |+------+------+2 rows in set (0.00 sec)