pxc集群部署

发表于 | 分类于 | 浏览 次

pxc介绍

​ galera产品是以galera cluster方式为mysql提高高可用集群解决方案的。galera cluster就是集成了galera插件的mysql集群。galera replication是codership提供的mysql数据同步方案，具有高可用性，方便扩展，并且可以实现多个mysql节点间的数据同步复制与读写，可保障数据库的服务高可用及数据强一致性。

​ PXC属于一套近乎完美的mysql高可用集群解决方案，相比那些比较传统的基于主从复制模式的集群架构MHA和MM+keepalived，galera cluster最突出特点就是解决了诟病已久的数据复制延迟问题，基本上可以达到实时同步。而且节点与节点之间，他们相互的关系是对等的。本身galera cluster也是一种多主架构。galera cluster最关注的是数据的一致性，对待事物的行为时，要么在所有节点上执行，要么都不执行，它的实现机制决定了它对待一致性的行为非常严格，这也能非常完美的保证MySQL集群的数据一致性；

​ 对galera cluster的封装有两个，虽然名称不同，但实质都是一样的，使用的都是galera cluster。一个MySQL的创始人在自己全新的MariaDB上实现的MAriaDB cluster；一个是著名的MySQL服务和工具提供商percona实现的percona xtradb cluster，简称PXC 。

​ 要搭建PXC架构至少需要3个mysql实例来组成一个集群，三个实例之间不是主从模式，而是各自为主，所以三者是对等关系，不分从属，这就叫multi-master架构。客户端写入和读取数据时，连接哪个实例都是一样的。读取到的数据时相同的，写入任意一个实例之后，集群自己会将新写入的数据同步到其他实例上，这种架构不共享任何数据，是一种高冗余架构。

部署

部署准备工作

主机名 IP
pxc1 192.168.6.201 192.168.6.200
pxc2 192.168.6.202
pxc3 192.168.6.203

关防火墙、selinux、ntp时间同步

安装pxc

rpm安装

1
2
3
4
5
6
7
8
9
##安装yum源
 yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
 ##定位到pxc57的仓库
  percona-release setup pxc57
 #解压
 tar vxf Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
 tar vxf Percona-XtraDB-Cluster-5.7.25-31.35-r463-el7-x86_64-bundle.tar
 ##安装rpm包用yum localinstall 命令率先从本地安装，并根据本地rpm包会在线寻找未安装的依赖(pxc官网下载的安装包会依赖其他包)
 yum localinstall *.rpm

分别在三个节点创建数据存储目录

1
2
3
4
5
6
mkdir -p /data/local/percona-xtradb-cluster/data
chown -R mysql:mysql /data/local/percona-xtradb-cluster/data
mkdir -p /data/local/percona-xtradb-cluster/run
chown -R mysql:mysql /data/local/percona-xtradb-cluster/run
mkdir -p /data/logs/mysql
chown -R mysql:mysql /data/logs/mysql

修改/etc/my.cnf配置文件，其他两台节点需要修改server_id和wsrep_node_address

pxc1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
[root@pxc1 ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /data/local/percona-xtradb-cluster/run/mysql.sock
default-character-set = utf8mb4
[mysqld]
user =  mysql
innodb_buffer_pool_size = 1024M
character_set_server = utf8mb4
datadir = /data/local/percona-xtradb-cluster/data
port = 3306
server_id = 1
socket = /data/local/percona-xtradb-cluster/run/mysql.sock
pid-file = /data/local/percona-xtradb-cluster/run/mysql.pid
log-error = /data/logs/mysql/error.log
log_warnings = 2
slow_query_log_file = /data/logs/mysql/slow.log
long_query_time = 2
log_timestamps=SYSTEM
lower_case_table_names = 1
key_buffer_size = 1344M
event_scheduler=ON
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.6.201,192.168.6.202,192.168.6.203
binlog_format=ROW
pxc_strict_mode=PERMISSIVE
max_connect_errors=1000
max_allowed_packet = 1024M
default_storage_engine=InnoDB
#Innodb
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 5
innodb_lock_wait_timeout = 50
innodb_log_file_size = 1024M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_thread_concurrency = 8
innodb_buffer_pool_size = 5G
innodb_read_io_threads = 24
innodb_write_io_threads = 24
log_bin_trust_function_creators=1
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = 1000
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 5000
#wsrep
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_node_name = pxc1
wsrep_node_address=192.168.6.201
wsrep_sst_method=xtrabackup-v2
wsrep_cluster_name=pxc
wsrep_sst_auth="sstuser:secret"

pxc2：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
[root@pxc2 ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /data/local/percona-xtradb-cluster/run/mysql.sock
default-character-set = utf8mb4
[mysqld]
user =  mysql
innodb_buffer_pool_size = 1024M
character_set_server = utf8mb4
datadir = /data/local/percona-xtradb-cluster/data
port = 3306
server_id = 2
socket = /data/local/percona-xtradb-cluster/run/mysql.sock
pid-file = /data/local/percona-xtradb-cluster/run/mysql.pid
log-error = /data/logs/mysql/error.log
log_warnings = 2
slow_query_log_file = /data/logs/mysql/slow.log
long_query_time = 2
log_timestamps=SYSTEM
lower_case_table_names = 1
key_buffer_size = 1344M
event_scheduler=ON
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.6.201,192.168.6.202,192.168.6.203
binlog_format=ROW
pxc_strict_mode=PERMISSIVE
max_connect_errors=1000
max_allowed_packet = 1024M
default_storage_engine=InnoDB
#Innodb
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 5
innodb_lock_wait_timeout = 50
innodb_log_file_size = 1024M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_thread_concurrency = 8
innodb_buffer_pool_size = 5G
innodb_read_io_threads = 24
innodb_write_io_threads = 24
log_bin_trust_function_creators=1
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = 1000
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 5000
#wsrep
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_node_name = pxc2
wsrep_node_address=192.168.6.202
wsrep_sst_method=xtrabackup-v2
wsrep_cluster_name=pxc
wsrep_sst_auth="sstuser:secret"

pxc3:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
[root@pxc3 ~]# cat /etc/my.cnf
[client]
port = 3306
socket = /data/local/percona-xtradb-cluster/run/mysql.sock
default-character-set = utf8mb4
[mysqld]
user =  mysql
innodb_buffer_pool_size = 1024M
character_set_server = utf8mb4
datadir = /data/local/percona-xtradb-cluster/data
port = 3306
server_id = 3
socket = /data/local/percona-xtradb-cluster/run/mysql.sock
pid-file = /data/local/percona-xtradb-cluster/run/mysql.pid
log-error = /data/logs/mysql/error.log
log_warnings = 2
slow_query_log_file = /data/logs/mysql/slow.log
long_query_time = 2
log_timestamps=SYSTEM
lower_case_table_names = 1
key_buffer_size = 1344M
event_scheduler=ON
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.6.201,192.168.6.202,192.168.6.203
binlog_format=ROW
pxc_strict_mode=PERMISSIVE
max_connect_errors=1000
max_allowed_packet = 1024M
default_storage_engine=InnoDB
#Innodb
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 5
innodb_lock_wait_timeout = 50
innodb_log_file_size = 1024M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_thread_concurrency = 8
innodb_buffer_pool_size = 5G
innodb_read_io_threads = 24
innodb_write_io_threads = 24
log_bin_trust_function_creators=1
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = 1000
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 5000
#wsrep
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_node_name = pxc3
wsrep_node_address=192.168.6.203
wsrep_sst_method=xtrabackup-v2
wsrep_cluster_name=pxc
wsrep_sst_auth="sstuser:secret"

启动

主节点：

1
service  mysql@bootstrap.service  start

其他节点：

1
service mysql start

启动第二个节点时遇到sst连接失败的问题

1
将wsrep.cnf 配置文件的wsrep_sst_method改为rsync,成功启动集群后停止集群，然后改回xtrabackup-v2,再启动集群节点。

修改密码

1
2
3
4
5
6
7
8
9
10
11
mysql5.7版本日志均在error.log 里面生成
grep "temporary password"  /data/logs/mysql/error.log


使用改密码登陆MySQL，修改成自己想要的密码
mysql> alter user 'root'@'localhost' idnetified by 'pxc';

配置SST认证账号
CREATE USER 'sstuser'@'192.168.%.%' IDENTIFIED BY 'secret';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'192.168.%.%';
FLUSH PRIVILEGES;

查看状态

查看节点数：

1
show global status like 'wsrep_cluster_size';

查看集群状态：

1
show global status like 'wsrep%';

查看当前节点状态：

1
show global status like 'wsrep_cluster_status';

安装keepalive

1
2
3
6.200为虚ip
在192.168.6.201,6.202上
yum -y install keepalived

主：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@pxc1 ~]# cat /etc/keepalived/keepalived.conf 
global_defs {
   router_id LVS_mysql
}

vrrp_script Checkmysql {
    script "curl -k http://192.168.7.201:3306" # vip
    interval 3
    timeout 9
    fall 2
    rise 2
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160 # 本地网卡名称
    virtual_router_id 61
    priority 120 # 权重,要唯一
    advert_int 1
    mcast_src_ip 192.168.7.201 # 本地IP
    nopreempt
    authentication {
        auth_type PASS
        auth_pass sqP05dQgMSlzrxHj
    }
    unicast_peer {
        192.168.7.202
        192.168.7.203
    }
    virtual_ipaddress {
        192.168.7.200/24 # VIP
    }
    track_script {
        Checkmysql
    }
}

从：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@pxc2 ~]# cat /etc/keepalived/keepalived.conf 
global_defs {
   router_id LVS_mysql
}

vrrp_script Checkmysql {
    script "curl -k http://192.168.7.201:3306" # vip
    interval 3
    timeout 9
    fall 2
    rise 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens160 # 本地网卡名称
    virtual_router_id 61
    priority 80 # 权重,要唯一
    advert_int 1
    mcast_src_ip 192.168.7.202 # 本地IP
    nopreempt
    authentication {
        auth_type PASS
        auth_pass sqP05dQgMSlzrxHj
    }
    unicast_peer {
        192.168.7.201
        192.168.7.203
    }
    virtual_ipaddress {
        192.168.7.200/24 # VIP
    }
    track_script {
        Checkmysql
    }
}

登录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@pxc1 ~]# mysql -usstuser -psecret --host 192.168.6.200
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1099
Server version: 5.7.25-28-57 Percona XtraDB Cluster (GPL), Release rel28, Revision a2ef85f, WSREP version 31.35, wsrep_31.35

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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>
Donate