作者在2014oracle数据库嘉年华中有幸听到了关于去哪网的mysql数据库基于pxc的高可用架构设计主题。 MySQL和Oracle是完全不同的两种数据库，Oracle重于管理，而MySQL更重要的是架构设计。笔者对MySQL以及新技术也是充满了好奇，索性回来自己搭建一个测试环境
作者在2014oracle数据库嘉年华中有幸听到了关于去哪网的mysql数据库基于pxc的高可用架构设计主题。
MySQL和Oracle是完全不同的两种数据库，Oracle重于管理，而MySQL更重要的是架构设计。笔者对MySQL以及新技术也是充满了好奇，索性回来自己搭建一个测试环境。
操作系统：CentOS 6.6
软件版本：5.5.39-36.0-55 PerconaXtraDB Cluster (GPL)
节点信息：
| pxc1 | 192.168.0.200 |
| pxc2 | 192.168.0.201 |
| pxc3 | 192.168.0.202 |
安装过程
安装PXC，这里使用的是Percona以及EPEL的官方repositories ：
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.rpm yum installhttp://mirrors.ustc.edu.cn/fedora/epel/6/i386/epel-release-6-8.noarch.rpm yum -y install Percona-XtraDB-Cluster-serverPercona-XtraDB-Cluster-client Percona-Server-shared-compat percona-xtrabackup
每台机器开启PXC，之后添加SST账户：
DELETE FROM mysql.user WHERE user=''; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost' IDENTIFIED BY 'sstuser';; FLUSH PRIVILEGES;
每个节点关闭PXC。编辑第一个节点的PXC设置。
[mysqld] server_id=1 wsrep_provider=/usr/lib64/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.0.200,192.168.0.201,192.168.0.202" wsrep_sst_auth=sstuser:sstuser wsrep_provider_options="gcache.size=4G" wsrep_cluster_name=Percona wsrep_sst_method=xtrabackup wsrep_node_name=pxc1 wsrep_slave_threads=4 log_slave_updates innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2
开启第一个节点（如果集群关闭，每次拥有正确信息的节点使用此命令开启集群）
[root@pxc1 ~]# service mysql bootstrap-pxc Bootstrapping PXC (Percona XtraDBCluster)Starting MySQL (Percona XtraDB Cluster)..[ OK ]
编辑另外两个节点的my.cnf配置文件需要修改的内容：
server_id=2 wsrep_node_name=pxc2
将其他两个节点加入到集群中
service mysql start
至此，集群安装结束。
遇到的问题
以下是配置过程中的报错信息以及解决方法：
现象：
开启PXC节点的时候遇到
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 denied
141120 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)原因：
由于开启了SELINUX。
解决方法：
编辑文件/etc/sysconfig/selinux
把SELINUX设为disabled
重启后就会忽略selinux，或者直接执行命令
setenforce 0
立即生效。
现象：
添加节点到集群中的时候遇到
last inactive check morethan PT1.5S ago (PT3.50529S), skipping check
卡在这里
原因：
iptables
解决方法：
关闭iptables
chkconfig iptables off
service iptables stop
简单测试
[root@pxc1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.5.39-36.0-55 Percona XtraDB Cluster (GPL), Release rel36.0, Revision 824, WSREP version 25.11, wsrep_25.11.r4023 Copyright (c) 2009-2014 Percona LLC and/or its affiliates Copyright (c) 2000, 2014, 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_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 dexdb Database changed mysql> 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 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.39-36.0-55 Percona XtraDB Cluster (GPL), Release rel36.0, Revision 824, WSREP version 25.11, wsrep_25.11.r4023 Copyright (c) 2009-2014 Percona LLC and/or its affiliates Copyright (c) 2000, 2014, 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 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 1 mysql> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | dexdb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec) mysql> use dexdb 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> select * from dextb ; +------+------+ | id | name | +------+------+ | 1 | 22 | | 1 | 22 | +------+------+ 2 rows in set (0.00 sec) [root@pxc3 ~]# service mysql start MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists[FAILED] Starting MySQL (Percona XtraDB Cluster).....[ OK ] [root@pxc3 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.39-36.0-55 Percona XtraDB Cluster (GPL), Release rel36.0, Revision 824, WSREP version 25.11, wsrep_25.11.r4023 Copyright (c) 2009-2014 Percona LLC and/or its affiliates Copyright (c) 2000, 2014, 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> use dexdb 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> select * from dextb ; +------+------+ | id | name | +------+------+ | 1 | 22 | | 1 | 22 | +------+------+ 2 rows in set (0.00 sec)
