Percona XtraDB Cluster|Mysql 集群部署
HA|LB运维,备份 | 存储运维,数据库运维
|
61
|
0

简介

Percona XtraDB Cluster (PXC) 是一个开源的高可用 MySQL 集群解决方案。
它通过集成 Percona Server for MySQL 和 Percona XtraBackup，并使用 Galera 库实现同步多源复制。

主要特点

  1. 同步复制：事务要么在所有节点上提交，要么都不提交，确保数据一致性。
  2. 多主复制：任何节点都可以进行读写操作，消除了单点故障。
  3. 自动节点配置：新增节点时，数据会自动从现有节点复制，无需手动备份。
  4. 高可用性：即使某个节点失效，集群仍能继续运行，不会丢失数据。

优点

  • 高一致性：确保所有节点的数据一致性。
  • 无复制延迟：基本实现实时同步。
  • 扩展性：适合扩展读操作负载。

局限性

  • 仅支持 InnoDB 引擎：其他存储引擎的更改不复制。
  • 写入效率受限：写入效率取决于最慢的节点。
  • 硬件要求高：需要高性能硬件支持。

实验环境

主机名/IP系统角色
server04/192.168.1.204ubuntu 22.04主节点
server05/192.168.1.205ubuntu 22.04节点
server06/192.168.1.206ubuntu 22.04节点
server07/192.168.1.207ubuntu 22.04仲裁节点

server04 & server05 &server06 配置

添加hosts映射

vim /etc/hosts

192.168.1.204 server04
192.168.1.205 server05
192.168.1.206 server06
192.168.1.207 server07

安装软件

sudo apt install -y wget gnupg2 lsb-release curl
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt update -y
sudo percona-release setup pxc80
sudo apt install -y percona-xtradb-cluster

安装过程中需要设置mysql密码

server04配置

生成CA密钥和服务器证书

mkdir /etc/mysql/certs/
cd /etc/mysql/certs/
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
chown -R mysql:mysql /etc/mysql/certs/

验证证书链

openssl verify -CAfile /etc/mysql/certs/ca.pem /etc/mysql/certs/server-cert.pem

停止mysql服务

systemctl stop mysql

更改配置文件部分参数

 vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id=1
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.204,192.168.1.205,192.168.1.206
wsrep_node_address=192.168.1.204
wsrep_cluster_name=pxc-cluster
wsrep_node_name=server04
wsrep_provider_options="socket.ssl_key=/etc/mysql/certs/server-key.pem;socket.ssl_cert=/etc/mysql/certs/server-cert.pem;socket.ssl_ca=/etc/mysql/certs/ca.pem"
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

[sst]
encrypt=4
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

引导集群

systemctl start mysql@bootstrap.service

查看集群信息

mysql -u root -p
show status like 'wsrep%';
SHOW STATUS WHERE Variable_name IN (    'wsrep_cluster_size',    'wsrep_cluster_status',    'wsrep_connected',    'wsrep_ready',    'wsrep_local_state_comment',    'wsrep_local_recv_queue',    'wsrep_flow_control_paused',    'wsrep_cert_deps_distance',    'wsrep_commit_window');
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| wsrep_cert_deps_distance  | 1       |
| wsrep_cluster_size        | 1       |
| wsrep_cluster_status      | Primary |
| wsrep_commit_window       | 1       |
| wsrep_connected           | ON      |
| wsrep_flow_control_paused | 0       |
| wsrep_local_recv_queue    | 0       |
| wsrep_local_state_comment | Synced  |
| wsrep_ready               | ON      |
+---------------------------+---------+

输出字段解释：

  • wsrep_cluster_size: 当前集群中的节点数量。
  • wsrep_cluster_status: 集群的当前状态，通常为 Primary。
  • wsrep_connected: 节点是否已连接到集群，ON 表示已连接。
  • wsrep_ready: 节点是否已准备好处理事务，ON 表示已准备好。
  • wsrep_local_state_comment: 节点的当前状态，常见状态包括 Synced、Donor/Desynced 等。
  • wsrep_local_recv_queue: 本地接收队列的长度，较高的值可能表示网络延迟或性能问题。
  • wsrep_flow_control_paused: 流量控制暂停的时间比例，较高的值可能表示流量控制问题。
  • wsrep_cert_deps_distance: 事务依赖关系的平均距离，较高的值可能表示事务冲突较多。
  • wsrep_commit_window: 提交窗口的大小，较高的值可能表示系统负载较高。

server05配置

拷贝server04的ssl证书

scp -r root@192.168.1.204:/etc/mysql/certs /etc/mysql
chown -R mysql:mysql /etc/mysql/certs/

停止mysql服务

systemctl stop mysql

更改配置文件部分参数

 vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id=2
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.204,192.168.1.205,192.168.1.206
wsrep_node_address=192.168.1.205
wsrep_cluster_name=pxc-cluster
wsrep_node_name=server05
wsrep_provider_options="socket.ssl_key=/etc/mysql/certs/server-key.pem;socket.ssl_cert=/etc/mysql/certs/server-cert.pem;socket.ssl_ca=/etc/mysql/certs/ca.pem"
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

[sst]
encrypt=4
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

加入集群

systemctl start mysql

查看集群信息

mysql -u root -p
show status like 'wsrep%';

server06配置

拷贝server04的ssl证书

scp -r root@192.168.1.204:/etc/mysql/certs /etc/mysql
chown -R mysql:mysql /etc/mysql/certs/

停止mysql服务

systemctl stop mysql

更改配置文件部分参数

 vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id=3
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.204,192.168.1.205,192.168.1.206
wsrep_node_address=192.168.1.206
wsrep_cluster_name=pxc-cluster
wsrep_node_name=server06
wsrep_provider_options="socket.ssl_key=/etc/mysql/certs/server-key.pem;socket.ssl_cert=/etc/mysql/certs/server-cert.pem;socket.ssl_ca=/etc/mysql/certs/ca.pem"
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

[sst]
encrypt=4
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

加入集群

systemctl start mysql

查看集群信息

mysql -u root -p
show status like 'wsrep%';

server07配置

添加hosts映射

vim /etc/hosts

192.168.1.204 server04
192.168.1.205 server05
192.168.1.206 server06
192.168.1.207 server07

安装软件

sudo apt install -y wget gnupg2 lsb-release curl
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt update -y
sudo percona-release setup pxc80
apt install percona-xtradb-cluster-garbd

拷贝server04的ssl证书

scp -r root@192.168.1.204:/etc/mysql/certs /etc/mysql
chown -R nobody:nogroup /etc/mysql/certs/

配置文件

vim /etc/default/garb
GALERA_NODES="192.168.1.204:4567,192.168.1.205:4567,192.168.1.206:4567"
GALERA_GROUP="pxc-cluster"
GALERA_OPTIONS="socket.ssl_key=/etc/mysql/certs/server-key.pem;socket.ssl_cert=/etc/mysql/certs/server-cert.pem;socket.ssl_ca=/etc/mysql/certs/ca.pem;socket.ssl_cipher=AES128-SHA256"

TIPs：这个配置文件的#注释说明都需要删除，不然无法启动服务

启动服务

systemctl start garb

可以在其他节点查看状态，该仲裁节点已经加入到集群中

show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 4     |
+--------------------+-------+
mysql数据库负载均衡高可用
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																					

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!