部署高可用Mysql PXC(Percona XtraDB Cluster)集群网络

部署高可用Mysql PXC(Percona XtraDB Cluster)集群网络




1 基础环境

我们对3台国外服务器进行配置,集群网络名字是Mysql_Cluster,IP如下:

2 方案介绍

Percona XtraDB Cluster(简称PXC)集群是基于Galera 2.xlibrary,事务型应用下的通用的多主同步复制插件,主要用于解决强一致性问题,使得各个节点之间的数据保持实时同步以及实现多节点同时读写。提高了数据库的可靠性,也可以实现读写分离,是MySQL关系型数据库中大家公认的集群优选方案之一。

3 部署环境

3.1 下载需要的软件

需要安装的软件列表如下,可去官方下载最新的版本,推荐编译安装

3.2 下载解包

执行以下语句获取Percona XtraBackup:

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/source/tarball/percona-xtrabackup-2.4.12.tar.gz
tar xvf percona-xtrabackup-2.4.12.tar.gz

执行以下语句获取Percona XtraDB Cluster:

wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.23-31.31-2/source/tarball/Percona-XtraDB-Cluster-5.7.23-31.31.tar.gz
tar xvf Percona-XtraDB-Cluster-5.7.23-31.31.tar.gz

3.3 安装Percona XtraBackup热备

安装依赖库:

 yum install -y ncurses-devel libev-devel vim bison-devel bison libgcrypt-devel

执行以下指令执行编译:

cd percona-xtrabackup-2.4.12
mkdir build
cd build
cmake .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=./
make
make install

3.4 安装Percona XtraDB Cluster (MYSQL PXC) 集群

3.4.1 安装Percona XtraDB Cluster (MYSQL PXC)的MYSQL部分

安装依赖库:

yum install -y readline-devel pam-devel bison-devel bison

执行以下指令执行编译:

cd Percona-XtraDB-Cluster-5.7.23-31.31
mkdir build
cd build
cmake .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=./ -DENABLE_DOWNLOADS=1 -DWITHOUT_TOKUDB=ON  -DWITHOUT_ROCKSDB=ON -DWITH_SSL=system -DWITH_ZLIB=system -DCMAKE_INSTALL_PREFIX="/usr/local/mysql" -DMYSQL_DATADIR="/www/server/mysql/" -DWITH_INNODB_DISALLOW_WRITES=ON -DWITH_WSREP=ON -DWITH_UNIT_TESTS=0 -DWITH_READLINE=system -DWITH_PAM=ON -DWITH_INNODB_MEMCACHED=ON  -DWITH_SCALABILITY_METRICS=ON
make
make install

3.4.2 安装Percona Xtradb Cluster Galera部分

先解决依赖问题:

yum install -y epel-release python python-scons check check-devel libev libev-devel boost* 

执行以下指令执行编译:

cd Percona-XtraDB-Cluster-5.7.23-31.31/percona-xtradb-cluster-galera/
scons -j4 psi=1 --config=force  revno="b98f92f"  boost_pool=0 libgalera_smm.so
scons -j4 --config=force revno="b98f92f" garb/garbd
cp garb/garbd /usr/local/mysql/bin/
cp libgalera_smm.so /usr/local/mysql/lib/

3.5 配置环境

我们在3台集群节点的服务器上配置这个环境,并配置/etc/my.cnf文件如下:

  • 216.189.158.188
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

[mysqld]
user=mysql
server-id=10056
datadir=/www/server/mysql
log-error=/www/log/mysql/mysqld.log
pid-file=/www/server/mysql/mysqld.pid
user=mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
socket=/tmp/mysql.sock
bind-address=0.0.0.0
log-bin
log_slave_updates
expire_logs_days=7
port=3306
character_set_server = utf8
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Path to Galera library
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so

# 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
wsrep_cluster_address='gcomm://216.189.158.188,216.189.149.229,107.175.145.219'
#wsrep_cluster_address=gcomm://

# 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


wsrep_sst_receive_address=216.189.158.188

wsrep_node_incoming_address=216.189.158.188

wsrep_provider_options = "gmcast.listen_addr=tcp://216.189.158.188;ist.recv_addr=216.189.158.188;"

# 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
wsrep_node_address=216.189.158.188

# Cluster name
wsrep_cluster_name=mysql_cluster
#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=mysql_cluster-node-2

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2
#wsrep_sst_method=rsync

#Authentication for SST methon
wsrep_sst_auth="sst:123456"
  • 216.189.149.229
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

[mysqld]
user=mysql
server-id=10055
datadir=/www/server/mysql
log-error=/www/log/mysql/mysqld.log
pid-file=/www/server/mysql/mysqld.pid
user=mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
socket=/tmp/mysql.sock
bind-address=0.0.0.0
log-bin
log_slave_updates
expire_logs_days=7
port=3306
character_set_server = utf8
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Path to Galera library
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so

# 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
wsrep_cluster_address='gcomm://216.189.158.188,216.189.149.229,107.175.145.219'
#wsrep_cluster_address=gcomm://

# 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


wsrep_sst_receive_address=216.189.149.229

wsrep_node_incoming_address=216.189.149.229

wsrep_provider_options = "gmcast.listen_addr=tcp://216.189.149.229;ist.recv_addr=216.189.149.229;"

# 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
wsrep_node_address=216.189.149.229

# Cluster name
wsrep_cluster_name=mysql_cluster
#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=mysql_cluster-node-1

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2
#wsrep_sst_method=rsync

#Authentication for SST methon
wsrep_sst_auth="sst:123456"
  • 107.175.145.219
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

[mysqld]
user=mysql
server-id=10057
datadir=/www/server/mysql
log-error=/www/log/mysql/mysqld.log
pid-file=/www/server/mysql/mysqld.pid
user=mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
socket=/tmp/mysql.sock
bind-address=0.0.0.0
log-bin
log_slave_updates
expire_logs_days=7
port=3306
character_set_server = utf8
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Path to Galera library
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so

# 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
wsrep_cluster_address='gcomm://216.189.158.188,216.189.149.229,107.175.145.219'
#wsrep_cluster_address=gcomm://

# 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


wsrep_sst_receive_address=107.175.145.219

wsrep_node_incoming_address=107.175.145.219

wsrep_provider_options = "gmcast.listen_addr=tcp://107.175.145.219;ist.recv_addr=107.175.145.219;"

# 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
wsrep_node_address=107.175.145.219

# Cluster name
wsrep_cluster_name=mysql_cluster
#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=mysql_cluster-node-3

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2
#wsrep_sst_method=rsync

#Authentication for SST methon
wsrep_sst_auth="sst:123456"

3.6 数据库初始化

执行以下指令初始化数据库:

mysqld --initialize --user=mysql  --datadir="/www/server/mysql"

执行如下指令获取初始密码:

grep "temporary password" /www/log/mysql/mysqld.log

执行如下指令按照提示执行初始化安全系统和修改默认密码:

/usr/local/mysql/bin/mysql_secure_installation

3.7 首次启动第一个节点

执行如下指令启动第一个节点首次启动:

mysqld_safe --wsrep-new-cluster

对于其他节点则执行以下指令首次进行初始化:

mysqld_safe

3.8 后续正常启动

初始化完成后,可以恢复正常启动,重新启动服务以执行以下指令正常启动:

/usr/local/mysql/bin/mysqld_safe

3.9 测试集群

使用如下命令启动进入控制台

/usr/local/mysql/bin/mysql -uroot -p

输入密码后登陆进入空控制台,输入以下执行语句:

show global status like 'wsrep%';

可查询目前的集群节点数,安装成功。

3.10 注意

  1. PXC集群网络会使用4个端口,分别是:
  • 3306:mysql数据库的数据库默认端口

  • 4444:在新加入节点进行SST(全量数据同步的时候使用的端口)

  • 4567: 组成员之间进行沟通的一个端口号

  • 4568:传输IST用的。相对于SST来说的一个增量。

  1. 我在配置环境的时候,曾经出现找不到xtrabackup等程序的情况,可以使用如下指令做一个软连接:

    ln -s /usr/local/xtrabackup/bin/xtrabackup /usr/local/mysql/bin/xtrabackup
    ln -s /usr/local/xtrabackup/bin/xbstream /usr/local/mysql/bin/xbstream
    
  2. 由于PXC基于socat,根据错误提示可能需要安装socat,按如下方式安装:
    yum install -y socat
    
  3. 集群排查问题的时候,不要只看当前节点,有的时候报错出现在其他节点,造成同步异常,需要合理判断异常原因。

  4. 集群中不要使用显式锁操作,注意每张表一定要有主键。

  5. PXC高可用集群网络强调强一致性,本身是为了解决强一致性问题的高可用集群方案,性能由集群节点中最慢的节点而决定。

  6. 最好不要全部彻底退出后更新配置,然后重新启动,如果要重新开始引导节点,第一节点使用mysqld_safe --wsrep-new-cluster进行引导,可能会发生错误,错误描述明确告诉你,从这个节点引导PXC可能不安全,如果要强制引导,需要修改grastate.dat文件的safe_to_bootstrap: 0修改成safe_to_bootstrap: 1然后使用引导命令即可引导启动第一节点,后续节点可以正常启动。

打赏

说点什么

avatar
  订阅  
提醒

扫码二维码快速访问本页

部署高可用Mysql PXC(Percona XtraDB Cluster)集群网络 – 起航天空