- PXC介绍
Percona XtraDB Cluster,简称PXC, 是Percona公司推出的基于Galera库的MySQL高可用解决方案, 其特点是同步复制和数据强一致性, 支持多点写入和并行复制，完全兼容Oracle MySQL和Percona Server, 可以2个节点组成集群, 官方推荐是三节点; 在生产环境中建议采用单点写入的方式，避免多节点同时更新同一行记录而发生更新丢失的问题。
三节点主机名和ip规划如下
| Node | Host | IP |
|---|---|---|
| Node 1 | node101 | 172.16.123.101 |
| Node 2 | node102 | 172.16.123.102 |
| Node 3 | node103 | 172.16.123.103 |
三个节点操作系统均为Centos7, 关闭firewalld, 关闭selinux
- 三个节点分别创建mysql用户
shell]# groupadd -g 27 mysql
shell]# useradd -g 27 -u 27 -M -d /usr/local/mysql -s /sbin/nologin mysql -
三个节点分别创建相关目录并授权
shell]# mkdir -p /data/mysql/mysql_3306/{bin_logs,data,error_logs,general_logs,relay_logs,slow_logs,tmp,undo_logs}
shell]# chown mysql.mysql /data/mysql -R -
三个节点分别安装percona源
shell]# yum -y install https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm -
三个节点分别安装Percona XtraDB Cluster, 当前最新版本是5.7.18-29.20.1.el7
shell]# yum -y install Percona-XtraDB-Cluster-57 -
编辑节点node101的/etc/my.cnf, 添加Percon XtraDB Cluster的配置
server_id=3306102
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster #pxc集群的名称
wsrep_cluster_address=gcomm://172.16.123.101,172.16.123.102,172.16.123.103 #pxc集群的节点
wsrep_node_address=172.16.123.101 #当前节点的ip
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:sstp@ssw0rd -
启动第一个节点
shell]# systemctl start mysql@bootstrap.service -
修改密码并创建sst用户
shell]# grep password /data/mysql/mysql_3306/error_logs/error.log
2017-06-12T12:22:32.844324Z 1 [Note] A temporary password is generated for root@localhost: jlXg>vs?W0/(
shell]# mysql --socket=/tmp/mysql_3306.sock --user=root -p
root@localhost:mysql_3306.sock [(none)]> ALTER USER CURRENT_USER() IDENTIFIED BY 'rootP@ss';
Query OK, 0 rows affected (0.14 sec)
root@localhost:mysql_3306.sock [(none)]> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO 'sstuser'@'localhost';
Query OK, 0 rows affected (0.14 sec)
root@localhost:mysql_3306.sock [(none)]> flush privileges;
Query OK, 0 rows affected (0.15 sec)
root@localhost:mysql_3306.sock [(none)]> exit
Bye -
把节点node101的配置文件/etc/my.cnf拷贝到节点node102, 修改server_id和wsrep_node_address
server_id=3306102
wsrep_node_address=172.16.123.102 -
启动节点node102
shell]# systemctl start mysql.service -
按照配置节点node102的方法配置node103(省略)
-
查看集群状态
root@localhost:mysql_3306.sock [(none)]> show global status like 'wsrep_local_state_uuid'; +------------------------+--------------------------------------+ | Variable_name | Value | +------------------------+--------------------------------------+ | wsrep_local_state_uuid | e200fd5f-4f69-11e7-9453-fffe2e4fc356 | +------------------------+--------------------------------------+ 1 row in set (0.00 sec) root@localhost:mysql_3306.sock [(none)]> show global status like 'wsrep_local_state_comment'; +---------------------------+--------+ | Variable_name | Value | +---------------------------+--------+ | wsrep_local_state_comment | Synced | +---------------------------+--------+ 1 row in set (0.00 sec) root@localhost:mysql_3306.sock [(none)]> show global status like 'wsrep_cluster_status'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | wsrep_cluster_status | Primary | +----------------------+---------+ 1 row in set (0.00 sec) root@localhost:mysql_3306.sock [(none)]> show global status like 'wsrep_connected'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | wsrep_connected | ON | +-----------------+-------+ 1 row in set (0.01 sec) root@localhost:mysql_3306.sock [(none)]> show global status like 'wsrep_ready '; Empty set (0.00 sec) root@localhost:mysql_3306.sock [(none)]> show global status like 'wsrep_ready'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wsrep_ready | ON | +---------------+-------+ 1 row in set (0.00 sec)
- 验证集群复制
在节点node102中执行SQL创建percona库
root@localhost:mysql_3306.sock [(none)]> CREATE DATABASE percona; Query OK, 1 row affected (0.38 sec)
在节点node103中执行SQL创建example表
root@localhost:mysql_3306.sock [percona]> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30)); Query OK, 0 rows affected (1.53 sec)
在节点node101中执行SQL写入数据
root@localhost:mysql_3306.sock [percona]> INSERT INTO percona.example VALUES (1, 'percona1'); Query OK, 1 row affected (0.34 sec)
在节点node102中执行SQL查看表数据记录
root@localhost:mysql_3306.sock [percona]> SELECT * FROM percona.example; +---------+-----------+ | node_id | node_name | +---------+-----------+ | 1 | percona1 | +---------+-----------+ 1 row in set (0.00 sec)
- 关于集群节点的启动和关闭
第一个启动的节点，使用的命令是systemctl start mysql@bootstrap.service, 关闭命令是systemctl stop mysql@bootstrap.service, 否则不起作用!
对于其他节点使用systemctl start mysql.service启动，关闭时则使用systemctl stop mysql.service;
如果整个集群都关闭了, 那么下次集群启动的第一个节点依然使用systemctl start mysql@bootstrap.service