MySQL高可用架构之MySQL5.7.19 PXC-白红宇

MySQL高可用架构之MySQL5.7.19 PXC

阅读量：5939 次

发布时间：2019-06-19

本文共 9350 字，大约阅读时间需要 31 分钟。

CentOS7.3下Percona-XtraDB-Cluster-5.7.19集群部署

PXC三节点安装：

node1：10.10.10.11

node2：10.10.10.12

node3：10.10.10.13

软件版本选择：

Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl101

percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt11.tar.gz/percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt11.tar.gz

注意openssl版本,ssl100、ssl101、ssl102分别对应三类linux版本，所以下载ssl101

#node1、node2、node3

操作系统版本

[root@node1 ~]# cat /etc/redhat-release

CentOS Linux release 7.3.1611 (Core)

[root@node1 ~]# systemctl status firewalld.service

* firewalld.service - firewalld - dynamic firewall daemon

Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)

Active: inactive (dead)

Docs: man:firewalld(1)

[root@node1 ~]#

[root@node1 ~]# getenforce

Disabled

[root@node1 ~]#cat /etc/hosts

#for pxc test

10.10.10.13 node3

10.10.10.11 node1

10.10.10.12 node2

安装依赖包：

yum install -y git scons gcc gcc-c++ openssl check cmake bison boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel socat

创建MySQL,basedir和datadir,编辑my.cnf

[root@node1 ~]#ls /opt/source

percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt11.tar.gz

Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl101.tar.gz

[root@node1 ~]#tar -zxf percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt11.tar.gz

[root@node1 ~]#tar -zxf Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl101.tar.gz

[root@node1 ~]#ln -s /opt/source/Percona-XtraDB-Cluster-5.7.14-rel8-26.17.1.Linux.x86_64.ssl100 /usr/local/pxc

ln -s /opt/source/xtrabackup-2.4.11/bin/xtrabackup /opt/source/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl101/bin/xtrabackup

[root@node1 ~]#mkdir -p /data/mysql_33071/{data,logs,run,tmp}

[root@node1 ~]#groupadd -g 1000 mysql

[root@node1 ~]#useradd -r -g mysql -u 1000 -s /bin/false mysql

编辑my.cnf配置文件

[root@node1 ~]#vi /data/mysql_33071/my.cnf

[root@node1 ~]#chown -R mysql:mysql /data/mysql_33071

[root@node1 ~]#chown -R mysql:mysql /usr/local/pxc

node1的操作：

#node1:初始化实例和启动实例方式

/usr/local/pxc/bin/mysqld --defaults-file=/data/mysql_33071/my.cnf --initialize --basedir=/usr/local/pxc --datadir=/data/mysql_33071/data --user=mysql

在错误日志，记录下root初始密码！

启动实例：

/usr/local/pxc/bin/mysqld_safe --defaults-file=/data/mysql_33071/my.cnf --ledir=/usr/local/pxc/bin --wsrep_new_cluster &

#当采用自定义路径的my.cnf初始化及启动实例的时候，需要首先指定defaults-file；而且mysqld采用自定义路径时，需要指定ledir

[root@masterdb ~]#ps -ef |grep mysql

root 27690 1 0 9 ? 00:00:00 /bin/sh /usr/local/pxc/bin/mysqld_safe --defaults-file=/data/mysql_33071/my.cnf --ledir=/usr/local/pxc/bin --wsrep_new_cluster

mysql 28111 27690 0 9 ? 00:11:07 /usr/local/pxc/bin/mysqld --defaults-file=/data/mysql_33071/my.cnf --basedir=/usr/local/pxc --datadir=/data/mysql_33071/data

--plugin-dir=/usr/local/pxc/lib/mysql/plugin --user=mysql --wsrep-provider=/usr/local/pxc/lib/libgalera_smm.so --wsrep-new-cluster --log-error=/data/mysql_33071/logs/mysql_error.log --pid-file=/data/mysql_33071/logs/mysql.pid

--socket=/data/mysql_33071/run/mysql.sock --port=33071 --wsrep_start_position=4946b0f0-ad00-11e8-8524-f2562c72b43b:5

修改root密码：

[root@node1 ~]#mysql -uroot -p -S /data/mysql_33071/run/mysql.sock

mysql> set password=password('root');

mysql> flush privileges;

创建sst user，并授予相应权限

mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser';

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';

mysql> FLUSH PRIVILEGES;

备份数据库

[root@node1 ~]#innobackupex --user=root --password="root" --socket=/data/mysql_33071/run/mysql.sock /tmp/

node2、node3的操作：

###node2、node3：###

用node1的备份，在节点2、节点3进行恢复;启动，加入复制集

innobackupex --defaults-file=/etc/my.cnf --user=root --password=xxxxxx --apply-log /data/2019-01-12_22-45-44

innobackupex --defaults-file=/etc/my.cnf --user=root --password=xxxxxx --copy-back /data/2019-01-12_22-45-44

/usr/local/pxc/bin/mysqld_safe --defaults-file=/data/mysql_33071/my.cnf --ledir=/usr/local/pxc/bin &

验证：

node1检查pxc状态

mysql> show global status like 'wsrep_cluster_size';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| wsrep_cluster_size | 3 |

+--------------------+-------+

1 row in set (0.01 sec)

show status like 'wsrep%';

mysql> show global status like 'wsrep_incoming_addresses';

+--------------------------+-----------------------------------------------------------+

| Variable_name | Value |

+--------------------------+-----------------------------------------------------------+

| wsrep_incoming_addresses | 10.10.10.12:33071,10.10.10.13:33071,10.10.10.11:33071 |

+--------------------------+-----------------------------------------------------------+

1 row in set (0.01 sec)

1 [root@node1 ~]#vi /data/mysql_33071/my.cnf 2 [client] 3 socket=/data/mysql_33071/run/mysql.sock 4 port = 33071 5  6 [mysqld] 7 port = 33071 8 server_id = 330711 9 user = mysql10 basedir = /usr/local/pxc11 datadir =/data/mysql_33071/data12 tmpdir  = /data/mysql_33071/tmp13 14 socket = /data/mysql_33071/run/mysql.sock15 pid-file = /data/mysql_33071/logs/mysql.pid16 log-error = /data/mysql_33071/logs/mysql_error.log17 slow_query_log_file = /data/mysql_33071/logs/slow.log18 log_warnings = 219 long_query_time = 120 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES21 22 innodb_buffer_pool_size = 2048M23 24 ## pxc variables ##25 # Path to Galera library26 wsrep_provider=/usr/local/pxc/lib/libgalera_smm.so27 # Cluster connection URL contains the IPs of node#1, node#2 and node#328 wsrep_cluster_address=gcomm://10.10.10.11,10.10.10.12,10.10.10.1329 # In order for Galera to work correctly binlog format should be ROW30 binlog_format=ROW31 # MyISAM storage engine has only experimental support32 default_storage_engine=InnoDB33 # This InnoDB autoincrement locking mode is a requirement for Galera34 innodb_autoinc_lock_mode=235 # Node name36 #wsrep_node_name = pxc137 wsrep_node_name = masterdb38 # Node 1 address39 wsrep_node_address=10.10.10.1140 # SST method默认情况下，Percona XtraDB群集使用Percona XtraBackup进行状态快照传输（SST）41 wsrep_sst_method=xtrabackup-v242 # Cluster name43 wsrep_cluster_name=my-pxc44 # Authentication for SST method45 wsrep_sst_auth="sstuser:sstuser" #将SST的身份验证凭据指定为
      
       ：

node1:my.cnf

1 [client] 2 socket=/data/mysql_33071/run/mysql.sock 3 port = 33071 4  5 [mysqld] 6 port = 33071 7 server_id = 330712 8 user = mysql 9 basedir = /usr/local/pxc10 datadir =/data/mysql_33071/data11 tmpdir  = /data/mysql_33071/tmp12 13 socket = /data/mysql_33071/run/mysql.sock14 pid-file = /data/mysql_33071/logs/mysql.pid15 log-error = /data/mysql_33071/logs/mysql_error.log16 slow_query_log_file = /data/mysql_33071/logs/slow.log17 log_warnings = 218 long_query_time = 119 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES20 21 innodb_buffer_pool_size = 2048M22 23 ## pxc variables ##24 # Path to Galera library25 wsrep_provider=/usr/local/pxc/lib/libgalera_smm.so26 # Cluster connection URL contains the IPs of node#1, node#2 and node#327 wsrep_cluster_address=gcomm://10.10.10.11,10.10.10.12,10.10.10.1328 # In order for Galera to work correctly binlog format should be ROW29 binlog_format=ROW30 # MyISAM storage engine has only experimental support31 default_storage_engine=InnoDB32 # This InnoDB autoincrement locking mode is a requirement for Galera33 innodb_autoinc_lock_mode=234 # Node name35 #wsrep_node_name = pxc236 wsrep_node_name = qa06.010150020097.yz37 # Node 1 address38 wsrep_node_address=10.10.10.1239 # SST method40 wsrep_sst_method=xtrabackup-v241 # Cluster name42 wsrep_cluster_name=my-pxc43 # Authentication for SST method44 wsrep_sst_auth="sstuser:sstuser"45 46 pxc_strict_mode=ENFORCING

node2:my.cnf

1 [client] 2 socket=/data/mysql_33071/run/mysql.sock 3 port = 33071 4  5 [mysqld] 6 port = 33071 7 server_id = 330713 8 user = mysql 9 basedir = /usr/local/pxc10 datadir =/data/mysql_33071/data11 tmpdir  = /data/mysql_33071/tmp12 13 socket = /data/mysql_33071/run/mysql.sock14 pid-file = /data/mysql_33071/logs/mysql.pid15 log-error = /data/mysql_33071/logs/mysql_error.log16 slow_query_log_file = /data/mysql_33071/logs/slow.log17 log_warnings = 218 long_query_time = 119 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES20 21 innodb_buffer_pool_size = 2048M22 23 ## pxc variables ##24 # Path to Galera library25 wsrep_provider=/usr/local/pxc/lib/libgalera_smm.so26 # Cluster connection URL contains the IPs of node#1, node#2 and node#327 wsrep_cluster_address=gcomm://10.10.10.11,10.10.10.12,10.10.10.1328 # In order for Galera to work correctly binlog format should be ROW29 binlog_format=ROW30 # MyISAM storage engine has only experimental support31 default_storage_engine=InnoDB32 # This InnoDB autoincrement locking mode is a requirement for Galera33 innodb_autoinc_lock_mode=234 # Node name35 wsrep_node_name = cgdb.010150020132.yz36 # Node 1 address37 wsrep_node_address=10.10.10.1338 # SST method39 wsrep_sst_method=xtrabackup-v240 # Cluster name41 wsrep_cluster_name=my-pxc42 # Authentication for SST method43 wsrep_sst_auth="sstuser:sstuser"44 45 pxc_strict_mode=ENFORCING

node3:my.cnf

Percona XtraDB Cluster与MySQL Replication区别在于：

分布式系统的CAP理论：

C— 一致性，所有节点的数据一致；

A— 可用性，一个或多个节点失效，不影响服务请求；

P— 分区容忍性，节点间的连接失效，仍然可以处理请求；

任何一个分布式系统，需要满足这三个中的两个。

MySQL Replication: 可用性和分区容忍性；

Percona XtraDBCluster: 一致性和可用性。

因此MySQL Replication并不保证数据的一致性，而Percona XtraDB Cluster提供数据一致性

阅读：

https://www.cnblogs.com/kevingrace/p/5685371.html