Percona XtraDB Cluster 集群环境建立与验证指南
Percona XtraDB Cluster 是MySQL数据库的一种集群方案。并且与 MySQL Server 社区版本、Percona Server 和 MariaDB 兼容。
一、在Ubuntu上安装Percona XtraDB Cluster
实验环境:
假设有3台计算机设备安装了ubuntu系统,将被用作3个节点:
Node Host IP
Node1 pxc1 172.16.24.209
Node2 pxc2 172.16.24.208
Node3 pxc3 172.16.24.207
前置条件:
(1) 确保一下端口没被防火墙屏蔽或被其他进程占用:
3306
4444
4567
4568
(2) 卸载 apparmor
sudo apt-get remove apparmor
安装步骤:
在每一台设备上执行下列命令:
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt update
sudo apt install percona-xtradb-cluster-full-57
passord:(设置root用户的密码)
至此,percona-xtradb-cluster已经安装,mysql进程会自动开始运行.
sudo service mysql stop
二、配置节点
1. 初始化集群
以第1台设备作为第1个集群节点。在 /etc/mysql/my.cnf 添加如下配置:
###############################################################
[mysqld]
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://
wsrep_node_name=pxc1
wsrep_node_address=172.16.24.209
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
log_error=/var/log/mysql/error.log
###############################################################
完成修改之后,执行:
sudo /etc/init.d/mysql bootstrap-pxc
数据库将以自举模式启动。至此,集群初始化工作已经完成。登录MySQL,查看初始化结果:
bn@u16:~$ mysql -uroot -p
Enter password: (输入root用户的密码)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.19-17-57-log Percona XtraDB Cluster (GPL), Release rel17, Revision c10027a, WSREP version 29.22, wsrep_29.22
Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, 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> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit
mysql> show status like 'wsrep%';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 0d718de1-a19c-11e7-81e3-127c64915155 |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 4 |
...
| wsrep_local_state_comment | Synced |
...
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 0d718de1-a19c-11e7-81e3-127c64915155 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.22(r8678538) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
67 rows in set (0.00 sec)
2. 添加节点
添加第2台设备,修改其配置文件 /etc/mysql/my.cnf ,添加如下语句:
###############################################################
[mysqld]
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://172.16.24.209,172.16.24.208,172.16.24.207
wsrep_node_name=pxc2
wsrep_node_address=172.16.24.208
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
log_error=/var/log/mysql/error.log
###############################################################
退出重启即可。重启后也登录MySQL,执行“show status like 'wsrep%';”,查看添加结果。
添加第3台设备,修改其配置文件 /etc/mysql/my.cnf ,添加如下语句:
###############################################################
[mysqld]
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://172.16.24.209,172.16.24.208,172.16.24.207
wsrep_node_name=pxc3
wsrep_node_address=172.16.24.207
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
log_error=/var/log/mysql/error.log
###############################################################
退出重启即可。重启后也登录MySQL,执行“show status like 'wsrep%';”,查看添加结果。
三、验证写集复制功能(Write-Set Replication)
1. Create a new database on the second node:
mysql> CREATE DATABASE testDB1;
Query OK, 1 row affected (0.00 sec)
2. Create a table on the third node:
mysql> USE testDB1
Database changed
mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.01 sec)
3. Insert records on the first node:
mysql> INSERT INTO testDB1.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.01 sec)
4. Retrieve rows from that table on the second node:
mysql> SELECT * FROM testDB1.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
| 1 | percona1 |
+---------+-----------+
1 row in set (0.00 sec)
四、PXC集群开机关机顺序
集群的第1个节点启动完成之后,才可以启动其他节点。。
集群的第1个节点必须等待其他节点都关机完成之后才能关机。
参考文档:
Percona-XtraDB-Cluster-5.7.18-29.20.pdf
转载于:https://blog.51cto.com/frankniefaquan/1968548