pxc集群

1.前期检查

检查是否安装有MySQL Server： 在安装percona之前需要将mysql以及mariadb删除

rpm -qa | grep mysql

rpm -qa | grep mariadb 查找是否有mysql或mariadb存在

删除方法：

rpm -e mysql #普通删除模式

rpm -e --nodeps mysql #强行删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其强行删除。

yum remove mariadb-libs-5.5.41-2.el7_0.x86_64

要注意的是mariadb也是不能存在的,有的话就要删除,删除的时候有依赖关系,直接yum卸载

2.安装启动

1、安装percona yum源：# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

2、测试percona yum源：# yum list | grep -i percona

3、安装PXC：# yum -y install Percona-XtraDB-Cluster-57`

3数据库修改密码

MySQL服务器

systemctl restart mysqld

生成初始密码

grep 'temporary password' /var/log/mysqld.log

登录数据库

mysql -u root -p

更改账户密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

停止

systemctl stop mysqld

清理残留内容

rm -rf /var/lib/mysql

重启MySQL服务器

systemctl restart mysqld

重新生成初始密码

grep ‘temporary password’ /var/log/mysqld.log

配置节点

[root@db01 ~]# vim  /etc/percona-xtradb-cluster.conf.d/wsrep.cnf


修改配置文件

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node

#集群中节点的IP地址（本机填最后）
wsrep_cluster_address=gcomm://ip地址,IP地址,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

# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
#当前节点IP
wsrep_node_address=IP地址
# Cluster name
#集群名称
wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
#当前节点名称
wsrep_node_name=pxc-cluster-node-1

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
#不使用实验功能
pxc_strict_mode=ENFORCING

# SST method
#状态快照传输（sst）方法，官方建议
wsrep_sst_method=xtrabackup-v2


#Authentication for SST method
#用户凭证（mysql的用户名和密码）

wsrep_sst_auth="用户名:密码"

剩下的节点修改当前节点名、当前节点IP、集群中的节点IP，其他相同

11、初始化集群节点

其中一个节点使用 systemctl start mysql@bootstrap.service 启动

 ＃systemctl启动mysql@bootstrap.service

登录mysql

mysql@pxc1> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
| ...                        | ...                                  |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| ...                        | ...                                  |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| ...                        | ...                                  |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)



mysql> CREATE USER 'all'@'localhost' IDENTIFIED BY '123456';
mysql>  GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'all'@'localhost';
mysql> FLUSH PRIVILEGES;

增加节点

[root@db02 ~]# systemctl start mysqld
[root@db02 ~]# mysql -uroot -p123456
mysql@pxc2> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
| ...                        | ...                                  |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| ...                        | ...                                  |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| ...                        | ...                                  |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)


......

验证复制

1.在第二个节点上创建一个新数据库：

mysql> CREATE DATABASE percona;
Query OK, 1 row affected (0.01 sec)

2.在第三个节点上创建一个表：

mysql> USE percona;
Database changed
mysql>  CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.02 sec)

3.在第一个节点上插入记录：

mysql> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.02 sec)

4.从第二个节点上的该表中检索行：

mysql> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
1 row in set (0.01 sec)

使用ProxySQL进行负载平衡(https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html#load-balancing-with-proxysql)

Percona监视和管理(https://www.percona.com/software/database-tools/percona-monitoring-and-management)